0

I have more than 2 million rows of data and I want to dump this data in Excel file but as given in this specification that Excel file can contains only 1,048,576 rows.

Consider that I have 40 million rows in the database and I want to dump this data in excel file.

I did 1 test but got the same result that is successfully got 1,048,576 rows and after that got error:

Exception from HRESULT: 0x800A03EC Error

Code:

for (int i = 1; i <= 1200000; i++)
            {
                oSheet.Cells[i, 1] = i;
            }

I think of CSV file but I can't use it as because we cant give colors and styles to CSV file as per this Answer and my Excel file is going to contain many colors and styles.

Is there any third party tool or whatever through which I can dump more than 2 millions rows in Excel file? I am not concerned if it is paid or free.

halfer
  • 19,824
  • 17
  • 99
  • 186
I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216

1 Answers1

3

Like you said the current excel specification Link has a maximum of 1,048,576 rows. But the amount of Sheets is only limited by the memory.

Maybe the seperation of the content on multiple sheets would be a solution for this.

or if you want to do some analysis on the data for instance you could maybe aggregat the information before loading them into the excel file.

Constantin Treiber
  • 420
  • 1
  • 7
  • 18