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.

Public String getexportstring (Dataset DS) {stringbuilder DATA = new stringbuilder (); // because there are many loops, the speed of using stringbuilder is much faster. // data = Ds. datasetname + "\ n"; foreach (datatable TB in DS. tables) // multiple tables {// 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 ("ID number") | column. columnname. equals ("registration number") // 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, string filename, dataset DS) {page. response. clear (); page. response. buffer = true; page. response. charset = "gb2312"; // page. response. charset = "UTF-8 ";
// If the 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 a simplified Chinese page. response. contenttype = "application/MS-excel"; // set the output file type to an Excel file. Page. enableviewstate = false; page. response. write (getexportstring (DS); page. response. end ();} // style = "Vnd. ms-excel.numberformat: @ "can remove the trouble of automatic scientific notation // output as a table, can minimize the impact of data in the field on the generated file format, here I am not processing data that contains HTML tags in the background of the page, so you can use it:

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.

Public void datatabletoexcel (system. data. datatable tmpdatatable, string strfilename) {If (tmpdatatable = NULL) return; int rownum = tmpdatatable. rows. count; int columnnum = tmpdatatable. columns. count; int rowindex = 1; int columnindex = 0; Microsoft. office. interOP. excel. application xlapp = new Microsoft. office. interOP. excel. applicationclass (); xlapp. defaultfilepath = ""; xlapp. displayalerts = true; xlapp. sheetsinnewworkbook = 1; Microsoft. office. interOP. excel. workbook xlbook = xlapp. workbooks. add (true); // import the datatable column name to the first row of the Excel table, foreach (datacolumn DC in tmpdatatable. columns) {columnindex ++; xlapp. cells [rowindex, columnindex] = dc. columnname;} // import the data in the able to excel for (INT I = 0; I <rownum; I ++) {rowindex ++; columnindex = 0; for (Int J = 0; j <columnnum; j ++) {columnindex ++; xlapp. cells [rowindex, columnindex] = tmpdatatable. rows [I] [J]. tostring () ;}// xlbook. savecopyas (httputility. urldecode (strfilename, system. text. encoding. utf8); xlbook. savecopyas (strfilename );}