C # two ways to export able to Excel

Project, you need to export data from the query results to the client, the previous solution is to generate an Excel file on the server through the excel component, and then download it. It can be divided into sheet, but the export is very slow due to memory consumption. It takes 40 minutes to export data from 60 thousand rows and 10 columns. I have never tried it. The following two solutions were obtained after improvement. Both solutions can generate the above data stream within several seconds.

Solution 1:

For versions earlier than office2007, only 65535 rows are supported. excel cannot be created or opened after the number is exceeded. Therefore, sheet needs to be split.

Private static string BuildExportHTML (System. DaTa. DataTable dt)
{
String result = string. Empty;
Int readCnt = dt. Rows. Count;
Int colCount = dt. Columns. Count;

Int pagerecords = 50000;
Result = "<? Xml version = \ "1.0 \" encoding = \ "gb2312 \"?> ";
Result + = "<? Mso-application progid = \ "Excel. Sheet \"?> ";
Result + = "<Workbook xmlns = \" urn: schemas-microsoft-com: office: spreadsheet \"";
Result + = "xmlns: o = \" urn: schemas-microsoft-com: office \"";
Result + = "xmlns: x = \" urn: schemas-microsoft-com: office: excel \"";
Result + = "xmlns: ss = \" urn: schemas-microsoft-com: office: spreadsheet \"";
Result + = "xmlns: html = \" http://www.w3.org/TR/REC-html40\ "> ";
// The following two parts are optional
// Result + = "<DocumentProperties xmlns = \" urn: schemas-microsoft-com: office \ "> ";
// Result + = "<Author> User </Author> ";
// Result + = "<LastAuthor> User </LastAuthor> ";
// Result + = "<Created> 2009-03-20T02: 15: 12Z </Created> ";
// Result + = "<Company> Microsoft </Company> ";
// Result + = "<Version> 12.00 </Version> ";
// Result + = "</DocumentProperties> ";

// Result + = "<ExcelWorkbook xmlns = \" urn: schemas-microsoft-com: office: excel \ "> ";
// Result + = "<WindowHeight> 7815 </WindowHeight> ";
// Result + = "<shortwwidth> 14880 </shortwwidth> ";
// Result + = "<WindowTopX> 240 </WindowTopX> ";
// Result + = "<WindowTopY> 75 </WindowTopY> ";
// Result + = "<ProtectStructure> False </ProtectStructure> ";
// Result + = "<ProtectWindows> False </ProtectWindows> ";
// Result + = "</ExcelWorkbook> ";
String strTitleRow = "";

// Set the title line of each row
StrTitleRow = "<Row ss: AutoFitHeight = '0'> ";
For (int j = 0; j <colCount; j ++)
{
StrTitleRow + = "<Cell> <DaTa ss: Type = \ "String \"> "+ dt. Columns [j]. ColumnName +" </DaTa> </Cell> ";
}
StrTitleRow + = "</Row> ";

StringBuilder strRows = new StringBuilder ();

// Stringbuilder is much more efficient than string in variable-length character operations.
Int page = 1; // Number of sheet shards
Int cnt = 1; // number of input records
Int sheetcolnum = 0; // The number of rows in each sheet, which is equal to cnt + 1
For (int I = 0; I <readCnt; I ++)
{
StrRows. Append ("<Row ss: AutoFitHeight = \" 0 \ "> ");
For (int j = 0; j <colCount; j ++)
{

If (dt. Columns [j]. DataType. Name = "DateTime" | dt. Columns [j]. DataType. Name = "SmallDateTime ")
{
If (dt. Rows [I] [j]. ToString ()! = String. Empty)
{
StrRows. Append ("<Cell> <DaTa ss: Type = \ "String \"> "+ Convert. toDateTime (dt. rows [I] [j]. toString ()). toString ("MM dd, yyyy") + "</DaTa> </Cell> ");
}
Else
StrRows. Append ("<Cell> <DaTa ss: Type = \ "String \"> </DaTa> </Cell> ");
}
Else
{
StrRows. Append ("<Cell> <DaTa ss: Type = \ "String \"> "+ dt. Rows [I] [j]. ToString (). Trim () +" </DaTa> </Cell> ");
}
}
StrRows. Append ("</Row> ");
Cnt ++;

// When setting the number of rows, you must output a page to prevent the office from being opened. Pay attention to the length restrictions of string and stringbuilder.
If (cnt> = pagerecords + 1)
{
Sheetcolnum = cnt + 1;
Result + = "<Worksheet ss: Name = \" Sheet "+ page. toString () + "\"> <Table ss: ExpandedColumnCount = \ "" + colCount. toString () + "\" ss: ExpandedRowCount = \ "" + sheetcolnum. toString () + "\" x: FullColumns = \ "1 \" x: FullRows = \ "1 \" ss: defacolumcolumnwidth = \ "104 \" ss: defaultRowHeight = \ "13.5 \"> "+ strTitleRow. toString () + strRows. toString () + "</Table> </Worksheet> ";
StrRows. Remove (0, strRows. Length );
Cnt = 1; // re-count the next sheet
Page ++;

}
}
Sheetcolnum = cnt + 1;
Result = result + "<Worksheet ss: Name = 'sheet" + page. toString () + "'> <Table ss: ExpandedColumnCount ='" + colCount. toString () + "'ss: ExpandedRowCount = '" + sheetcolnum. toString () + "'X: FullColumns = '1' x: FullRows = '1' ss: DefaultColumnWidth = '000000' ss: DefaultRowHeight = '13. 5 '> "+ strTitleRow. toString () + strRows. toString () + "</Table> </Worksheet> </Workbook> ";
Return result;
}

Problem: Because this scheme is an excel file generated in xml format, the generated file is very large, and the data size of 60 thousand rows and 10 columns is about 30 MB, which is detrimental to network transmission, it is good to use it in the LAN.

Solution 2: data is generated in a few formats, with a small amount of data, which is convenient for downloading and transmission over the network. Likewise, there are only about 6 MB of data in the above 60 thousand rows and 10 columns. The disadvantage is that it cannot be opened on office2003 if it exceeds 65535.

Private static string BuildExportHTML (System. DaTa. DataTable dt)
{
String result = string. Empty;
Int readCnt = dt. Rows. Count;
Int colCount = dt. Columns. Count;

Int pagerecords = 5000;
String strTitleRow = "";
For (int j = 0; j <colCount; j ++)
{
StrTitleRow + = dt. Columns [j]. ColumnName + "\ t ";
}
StrTitleRow + = "\ r \ n ";

StringBuilder strRows = new StringBuilder ();
Int cnt = 1;
For (int I = 0; I <readCnt; I ++)
{
// StrRows. Append ("");
For (int j = 0; j <colCount; j ++)
{
If (dt. Columns [j]. DataType. Name = "DateTime" | dt. Columns [j]. DataType. Name = "SmallDateTime ")
{
If (dt. Rows [I] [j]. ToString ()! = String. Empty)
{
StrRows. append (Convert. toDateTime (dt. rows [I] [j]. toString ()). toString ("MM dd, yyyy") + "\ t ");
}
Else
StrRows. Append ("\ t ");
}
Else
{
StrRows. Append (dt. Rows [I] [j]. ToString (). Trim () + "\ t ");
}
}
StrRows. Append ("\ r \ n ");
Cnt ++;
If (cnt> = pagerecords)
{
Result + = strRows. ToString ();
StrRows. Remove (0, strRows. Length );
Cnt = 1;
}
}
Result = strTitleRow + result + strRows. ToString ();
Return result;
}

If anyone can combine the two solutions, let me know. Thank you.

Send a call method that can be output to the client.

Public void DataTable2Excel (DataTable dt)
{
String fileName = DateTime. Now. ToString ("yyyyMMddhhmmss") + ". xls"; // you can specify the name of the exported file.
HttpContext curContext = System. Web. HttpContext. Current;
CurContext. Response. ContentType = "application/vnd. ms-excel ";
CurContext. Response. ContentEncoding = System. Text. Encoding. Default;
CurContext. Response. AppendHeader ("Content-Disposition", ("attachment; filename =" + fileName ));
CurContext. Response. Charset = "";

CurContext. Response. Write (BuildExportHTML (dt ));
CurContext. Response. Flush ();
CurContext. Response. End ();
}