DataTable Export to Excel

Simple Export to Excel:

The code is as follows:

usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Linq;usingSystem.Text;usingsystem.web;namespacespace{/// <summary>    ///title: Exporting a DataSet, DataTable to Excel///Description: The previous export of Excel was adjusted to support the export of datasets and DataTable;///DataSet Export can specify a DataTable to export///The DataTable export can specify the DataColumn to export and the name of the custom exported column/// </summary>     Public Static classExcelexportprovider { Public Static stringbuildexporthtml (System.Data.DataTable dt) {stringresult =string.            Empty; intreadcnt =dt.            Rows.Count; intColCount =dt.            Columns.count; intPagerecords =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: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\" >"; stringStrtitlerow =""; //set the header row for each rowStrtitlerow ="<row ss:autofitheight= ' 0 ' >";  for(intj =0; J < ColCount; J + +)            {                varTempcolname =dt. COLUMNS[J].                ColumnName; if(Tempcolname.indexof ("@@@@@@@@@@@@") >=0)//The "" is preceded by a separator symbol as the display, followed by the primary key ID. {tempcolname= Tempcolname.split (New string[] {"@@@@@@@@@@@@"}, Stringsplitoptions.none) [0]; } Strtitlerow+="<cell><data ss:type=\ "string\" >"+ Tempcolname +"</Data></Cell>"; } Strtitlerow+="</Row>"; StringBuilder strrows=NewStringBuilder (); //StringBuilder is much more efficient in variable-length character manipulation than string            intpage =1;//number of sheet divided into            intCNT =1;//number of records entered            intSheetcolnum =0;//the number of rows per sheet is actually equal to cnt+1             for(inti =0; i < readcnt; i++) {strrows.append ("<row ss:autofitheight=\ "0\" >");  for(intj =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 ()). ToShortDateString () +"</Data></Cell>"); }                        ElseStrrows.append ("<cell><data ss:type=\ "string\" ></Data></Cell>"); }                    //alter by Taomin 2012-11-13 new processing method for decimal type data avoid decimal type data import Excel is not converted to a number of fonts, not conducive to statistical calculation in Excel                    Else if(dt. COLUMNS[J]. Datatype.name = ="Int32"|| Dt. COLUMNS[J]. Datatype.name = ="Int64"|| Dt. COLUMNS[J]. DataType.Name.ToLower () = ="decimal") {strrows.append ("<cell><data ss:type= \ "Number\" >"+ dt. ROWS[I][J]. ToString (). Trim () +"</Data></Cell>"); }                    Else{strrows.append ("<cell><data ss:type=\ "string\" >"+ dt. ROWS[I][J]. ToString (). Trim () +"</Data></Cell>"); }} strrows.append ("</Row>"); CNT++; //To set the number of rows, to output a page to prevent office from opening, but also to note the length of string and StringBuilder limit                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:defaultcolumnwidth=\" 104\ "ss:defaultrowheight=\" 13.5\ ">"+ strtitlerow.tostring () + strrows.tostring () +"</Table></Worksheet>"; Strrows.remove (0, strrows.length); CNT=1;//re-count the next sheetpage++; }} 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= ' 104 ' ss:defaultrowheight= ' 13.5 ' >"+ strtitlerow.tostring () + strrows.tostring () +"</Table></Worksheet></Workbook>"; returnresult; }        /// <summary>        ///Export Excel///example controller inside call Excelexportprovider.datatable2excel (DT, "Clickexcel.xls"); /// </summary>         Public Static voidDatatable2excel (DataTable DT,stringfileName) {            stringOutputFileName =NULL; HttpContext Curcontext=System.Web.HttpContext.Current; stringBrowser =CurContext.Request.UserAgent.ToUpper (); if(Browser. Contains ("FIREFOX") ==true) {OutputFileName="\""+ FileName +"\""; }            Else{outputfilename=Httputility.urlencode (fileName); } CurContext.Response.ContentType="Application/ms-excel"; CurContext.Response.ContentEncoding= System.Text.Encoding.GetEncoding ("GB2312"); CurContext.Response.AppendHeader ("content-disposition", ("attachment;filename="+outputfilename)); CurContext.Response.Charset="";            CurContext.Response.Write (buildexporthtml (DT));            CurContext.Response.Flush ();        CurContext.Response.End (); }    }}

View Code How to use:Front Desk: Eg:<button id= "exportToExcel" > Export to Excel</button>$("#exportToExcel "). Click (function () { location.href= "/exporttoexcel.do"});    Background: public void exportToExcel (DataTable dt) {string fileName = "Test.xls"; Excelexportprovider.datatable2excel (dt,filename);}

Clicking the button invokes the Explorer's Path selection dialog box, where you select the file storage path

PS: Reprint please indicate the source.

DataTable Export to Excel