Asp.net Excel export method summary

1. This method Concatenates the data in the DataSet able into an html <table> </table> Format for output.
Disadvantages: (1) This is a pseudo-Excel format. When you open it in Excel, the system prompts "The format is different from the file extension format". You must manually confirm to open the file every time.
(2) This one-by-one cycle is inefficient
Advantage: output directly from the browser. You can select the Save path.
View Code
Public string GetExportString (DataSet ds)
{
StringBuilder data = new StringBuilder (); // because there are many loops, using StringBuilder will be much faster
// Data = ds. DataSetName + "\ n ";
 
Foreach (DataTable tb in ds. Tables) // multiple Tables www.2cto.com
{
// Data + = tb. TableName + "\ n ";
Data. Append ("<table cellspacing = \" 0 \ "cellpadding = \" 5 \ "rules = \" all \ "border = \" 1 \ "> ");
// Write the column name
Data. Append ("<tr style = \" font-weight: bold; white-space: nowrap; \ "> ");
Foreach (DataColumn column in tb. Columns)
{
Data. Append ("<td> ");
Data. Append (column. ColumnName );
Data. Append ("</td> ");
}
Data. Append ("</tr> ");
 
// Write data
Foreach (DataRow row in tb. Rows)
{
Data. Append ("<tr> ");
Foreach (DataColumn column in tb. Columns)
{
// If (column. ColumnName. Equals ("credential No.") | column. ColumnName. Equals ("Registration No "))
// Data. Append ("<td style = \" vnd. ms-excel.numberformat: @ \ ">" + row [column]. ToString () + "</td> ");
// Else
Data. Append ("<td> ");
Data. Append (row [column]. ToString ());
Data. Append ("</td> ");
}
Data. Append ("</tr> ");
}
Data. Append ("</table> ");
}
Return data. ToString ();
}

Public void ExportDsToXls (Page page, string fileName, DataSet ds)
{
Page. Response. Clear ();
Page. Response. Buffer = true;
Page. Response. Charset = "GB2312 ";
// Page. response. charset = "UTF-8"; <BR> // If the output Chinese name is garbled, You can encode the file name HttpUtility. urlEncode (fileName, System. text. encoding. UTF8)
Page. response. appendHeader ("Content-Disposition", "attachment; filename =" + fileName + System. dateTime. now. toString ("_ yyMMdd_hhmm") + ". xls ");
Page. Response. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312"); // set the output stream to simplified Chinese
Page. Response. ContentType = "application/ms-excel"; // set the output file type to excel.
Page. EnableViewState = false;
Page. Response. Write (GetExportString (ds ));
Page. Response. End ();
}
// Style = "vnd. ms-excel.numberformat: @" can remove the trouble of automatic scientific notation
// The output is Table, which can minimize the impact of data in the field on the generated file format. In this case, I have not processed HTML tags in the data on the background of the page, in this way, you can use

Ii. Microsoft. office. interop. excel dll, output on the server, and output Excel on the server (if it is a B/s architecture, you have to think about how to get the exported file back ), in addition, if an exception or interruption occurs, there are many excel processes on the server that need to be manually closed. It will not take long before the server crashes, therefore, this method is suitable for WinForm development and not for web applications.

 

From jiekk