the excel 2007 xlsx rows is limlited in 65535 when i use epplus3.o to export data from access db to excel

May 14, 2012 at 5:51 AM

hi,all, this is a issue i encounted, but i don't know how to resolve it,  who can help me ?

the record number in acccess db is more than 110000, but the result exported to excel is 65535, i don't know why? the excel format i defined is xlsx. i think it should be excel 2007?

i got some knowledge , the rows in excel2007 should be more than 65535. why there is only 65535 rows exported to excel in this case. who can help me ???

 

the code is as following:

using OfficeOpenXml;


namespace Parse
{
    public class parsedata
    {
        string sEntryDir = "D:/downloadv2/";
        string month = "201205";
        public void ExportToexcel2007xlsx(DataTable table, string filen, string sheetn)
        {
            MemoryStream rmemstream = CreateByExcelLibrary(table, sheetn);
            FileStream fs = new FileStream(filen, FileMode.OpenOrCreate);
            BinaryWriter w = new BinaryWriter(fs);
            w.Write(rmemstream.ToArray());
            fs.Close();
            rmemstream.Close();
        }

        MemoryStream CreateByExcelLibrary(DataTable table, string sheetn)
        {
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetn);

                int colCount = table.Columns.Count;
                Console.WriteLine("column: " + colCount);
                for (int i = 0; i < colCount; i++)
                {
                    sheet.Cells[1, i + 1].Value = table.Columns[i].Caption;
                }

                int k = 2;

                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < colCount; i++)
                    {
                        sheet.Cells[k, i + 1].Value = row[i];
                    }
                    k++;
                    Console.WriteLine("  --->  "+k);
                }

                MemoryStream ms = new MemoryStream();
                package.SaveAs(ms);
                return ms;
            }
        }
        private void HandleExport()
        {
            Console.WriteLine("start to load excel...");
            int tablepage = 1;
            month = "201204";
            Thread.Sleep(10);
            {
                string tablen = "ParseLP" + month + tablepage.ToString();
                DataTable souraccd = dbAccessInstance.GetParseFromAccessDb(tablen);
                if (souraccd != null)
                {
                    Console.WriteLine("return records num: " + souraccd.Rows.Count); //the records is 110000
                    string filename = sEntryDir + month + tablepage.ToString() + ".xlsx";
                    string sheetn = "sheet1";
                    Console.WriteLine("start to load..:" + tablen);
                    ExportToexcel2007xlsx(souraccd, filename, sheetn);
                    Console.WriteLine("succeed to load :" + tablen);                 
                }
                Thread.Sleep(1);
            }
        }
    }
}