Convert datatable to excel XML format for download

When creating a website project, you often need to convert the table into an Excel file for users to download. There are many implementation methods. For example:

(1) serialize datatable to text directly in XML format

(2) convert datatable to HTML table format

(3) convert datatable to excel XML format

(4) use Microsoft. Office. InterOP. Excel. DLL to create an Excel file, and then fill in the cell data in sequence.

After testing these methods, we found that the 3rd methods are simple and stable, and the other methods have such little trouble (such as garbled code and File Permission Issues). I will not provide implementation of these methods here.Code. The code for the following 3rd methods is described in this article.Article: Bytes. I hope it will be useful to you.

The Excel XML format is as follows:

Code
<? XML version = "1.0"?>
<? MSO-application progid = "Excel. Sheet"?>
<Workbook
Xmlns = "urn: Schemas-Microsoft-com: Office: spreadsheet"
Xmlns: Ss = "urn: Schemas-Microsoft-com: Office: spreadsheet">
<Worksheet SS: Name = "sheet1">
<Table>
<Row>
<Cell SS: merge1_ss = "1"> <data SS: TYPE = "string"> Excel XML </data> </cell>
</Row>
<Row>
<Cell> <data SS: TYPE = "string"> A2 </data> </cell>
<Cell> <data SS: TYPE = "Number"> 0.112 </data> </cell>
</Row>
</Table>
</Worksheet>
</Workbook>

Which of the following statements is used to generate an Excel XML file?Source code:

Code
// Datatable-> Excel
Public static void exportexcel (datatable DT, string filename, string [] displaycolumnnames, string [] displaycolumncaptions)
{
Response. clearcontent ();
Response. contentencoding = system. Text. encoding. utf8;
Response. contenttype = "application/vnd. MS-Excel; charset = UTF-8 ";
Response. addheader ("content-disposition", "attachment; filename =" + filename );
Response. Write (datatabletoexceltable (DT, displaycolumnnames, displaycolumncaptions ));
Response. End ();
}
// Convert datatable to excelxml
Public static string datatabletoexceltable (datatable DT, string [] displaycolumnnames, string [] displaycolumncaptions)
{
// Start the table
Stringbuilder sb = new stringbuilder ();
SB. appendline ("<? XML version = \ "1.0 \"?> ");
SB. appendline ("<? MSO-application progid = \ "Excel. Sheet \"?> ");
SB. appendline ("<workbook xmlns = \" urn: Schemas-Microsoft-com: Office: spreadsheet \ "xmlns: Ss = \" urn: Schemas-Microsoft-com: Office: spreadsheet \ "> ");
SB. appendline ("<worksheet SS: Name = \" sheet1 \ "> ");
SB. appendline ("<Table> ");
//
// Output the title
//
SB. appendline ("<row> ");
If (displaycolumncaptions! = NULL)
{
// Output the specified Column Title
For (INT I = 0; I <displaycolumncaptions. length; I ++)
SB. appendline ("<cell> <data SS: TYPE = \" string \ ">" + displaycolumncaptions [I] + "</data> </cell> ");
}
Else if (displaycolumnnames! = NULL)
{
// Output the title of the display Column
For (INT I = 0; I <displaycolumnnames. length; I ++)
SB. appendline ("<cell> <data SS: TYPE = \" string \ ">" + displaycolumnnames [I] + "</data> </cell> ");
}
Else
{
// Output all column headers
For (INT I = 0; I <DT. Columns. Count; I ++)
SB. appendline ("<cell> <data SS: TYPE = \" string \ ">" + dt. columns [I]. caption + "</data> </cell> ");
}
SB. appendline ("</row> ");
//
// Output data
//
If (displaycolumnnames! = NULL)
{
// Output specified column data
Foreach (datarow DR in DT. Rows)
{
SB. appendline ("<row> ");
Foreach (string colname in displaycolumnnames)
SB. appendline ("<cell> <data SS: TYPE = \" string \ ">" + Dr [colname]. tostring () + "</data> </cell> ");
SB. appendline ("</row> ");
}
}
Else
{
// Output all column data
Foreach (datarow DR in DT. Rows)
{
SB. appendline ("<row> ");
Object [] ary = dr. itemarray;
For (INT I = 0; I <= ary. getupperbound (0); I ++)
SB. appendline ("<cell> <data SS: TYPE = \" string \ ">" + ary [I]. tostring () + "</data> </cell> ");
SB. appendline ("</row> ");
}
}
// End the table
SB. appendline ("</table> ");
SB. appendline ("</worksheet> ");
SB. appendline ("</workbook> ");
Return sb. tostring ();
}

In addition, you must export XML in UTF-8 format, otherwise Excel cannot open the file, an error will be reported ~