[Original] How to export a large amount of data into an Excel file (C #, ASP. NET)

According to a friend of SZW, The excelfile mentioned in this document refers to the. xls file, excluding the default file format saved by excel2007.

Exporting a large amount of data to an Excel file may cause the following problems:
1. Export execution speed should be faster; otherwise, HTTP request timeout may occur;
Of course, this column is not used if you use winform.
2. A maximum of 65535 rows of data are allowed in an Excel file;
3. The format of a number may become exponential;
For example, after the ID card number is saved as a number, the last few digits of the information are lost.

Solution:
1. oledb is used to process Excel files as databases, which is relatively more efficient than other export solutions. Although not the highest, the obtained Excel file is a real binary Excel file. If you want to sort the exported data and export it back to the system, congratulations, this method is your best choice.
2. each sheet can have up to 65535 rows of data, but a single file can have many sheet files. If you want to export more than 65535 data, try to put the extra data in the second and third .... sheet.
3. A sheet can be seen as a database table. can we ensure the correct data format by defining the data attributes of the table fields?

Key Code :
Note: The complete code is an even-written project code, which is copyrighted. However, you can complete the desired functions based on my ideas and key code prompts.
Code snippet 1 Sheet creation: // Generate the table creation script
Stringbuilder sb =   New Stringbuilder ();
SB. append ( " Create Table " );
SB. append ( " [ "   + Tablename +   " ] ( " );

For ( Int I =   0 ; I < Headers. length; I ++ )
{
String Datatype;
Switch (Dt. Columns [columns [I]. datatype. Name. tolower ())
{
Case   " Float " : Datatype =   " Float " ; Break ;
Case   " Int32 " : Datatype =   " Int " ; Break ;
Case   " Double " : Datatype =   " Double " ; Break ;
Case   " Decimal " : Datatype =   " Float " ; Break ;
Default : Datatype =   " Text " ; Break ;
}
If (I < Headers. Length -   1 )
{
SB. append (String. Format ("[{0}] {1 },", Headers [I], datatype ));
}
Else
{
SB. append (String. Format ("[{0}] {1 })", Headers [I], datatype ));
}
}
Return SB. tostring ();

As you can see, we can use the create statement to create a sheet. The name of the table we use will be changed to the sheet name.
We recommend that you export the following names in sequence: for example, product information data, table names can be product information (1st pages), product information (2nd pages), and so on.

Code snippet 2 automatically creates multiple sheets as needed: For ( Int I =   0 ; I < DT. Rows. Count; I ++ )
{
// Create Sheet
If (I %   65535   =   0 )
{
Tablename =   String . Format ( " {0} page {1} " , DT. tablename, (I /   65535 ) +   1 );
Objcmd. commandtext = Getcreatesheetsql (DT, headers, columns, tablename );
Objcmd. executenonquery ();
}
Insert statement # Region Insert statement
SB. append ( " Insert " );
SB. append ( " [ "   + Tablename +   " ] ( " );
.

Finally, I would like to remind you that excel can be used as a database to support transaction processing. Don't forget to use the transaction mechanism.

If the data volume processed is small and the required format is very complex, such as multiple headers and statistical graphs, use other Excel export methods. The applicability of this solution has been mentioned earlier. For more information, see.
Http://piedpiper.cnblogs.com
This article is an original article. If you want to share a post, you can write a post with me. Haha