Asp.net Excel table export method summary

1. generated by Dataset

Public void createexcel (Dataset ds, string typeid, string filename)
{
Httpresponse resp;
Resp = page. response;
Resp. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Resp. appendheader ("content-disposition", "attachment; filename =" + filename );
String colheaders = "", ls_item = "";
Int I = 0;

// Define the table object and row object, and use dataset to initialize its value
Datatable dt = Ds. Tables [0];
Datarow [] myrow = DT. Select ("");
// When typeid = "1", the exported file is in Excel format; When typeid = "2", the exported file is in XML format.
If (typeid = "1 ")
{
// Obtain the titles of each column in the data table, separated by \ t, and a carriage return is added after the title of the last column.
For (I = 0; I colheaders + = DT. Columns [I]. Caption. tostring () + "\ t ";
Colheaders + = DT. Columns [I]. Caption. tostring () + "\ n ";
// Write the obtained data to the HTTP output stream
Resp. Write (colheaders );
// Process data row by row
Foreach (datarow row in myrow)
{
// In the current row, data is obtained one by one, separated by \ t, and carriage return \ n is added at the end
For (I = 0; I ls_item + = row [I]. tostring () + "\ t ";
Ls_item + = row [I]. tostring () + "\ n ";
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
Resp. Write (ls_item );
Ls_item = "";
}
}
Else
{
If (typeid = "2 ")
{
// Export XML data directly from dataset and write it to the HTTP output stream
Resp. Write (Ds. getxml ());
}
}
// Write the data in the buffer to the HTTP header file
Resp. End ();

}

 

2, the use of Microsoft C ++ write ActiveX Control: http://download.microsoft.com/download/OfficeXPDev/sample/1.0/WIN98MeXP/EN-US/Dsoframerctl.exe
3. generated by the DataGrid:

Public void toexcel (system. Web. UI. Control CTL)
{
Httpcontext. Current. response. appendheader ("content-disposition", "attachment?filename=excel.xls ");
Httpcontext. Current. response. charset = "UTF-8 ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. default;
Httpcontext. Current. response. contenttype = "application/MS-excel"; // image/JPEG; text/html; image/GIF; VND. MS-Excel/MSWord
CTL. Page. enableviewstate = false;
System. Io. stringwriter Tw = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter hW = new system. Web. UI. htmltextwriter (TW );
CTL. rendercontrol (HW );
Httpcontext. Current. response. Write (TW. tostring ());
Httpcontext. Current. response. End ();
}

Usage: toexcel (datagrid1 );

4. Use dataview. The code is long.

Public void outputexcel (dataview DV, string Str)
{
//
// Todo: add the constructor logic here
//
// DV indicates the data to be output to excel, and STR indicates the title name.
GC. Collect ();
Application Excel; // = new application ();
Int rowindex = 4;
Int colindex = 1;

_ Workbook xbk;
_ Worksheet XST;

Excel = new applicationclass ();

Xbk = excel. workbooks. Add (true );

XST = (_ worksheet) xbk. activesheet;

//
// Obtain the title
//
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
Excel. cells [4, colindex] = col. columnname;
XST. get_range (Excel. cells [4, colindex], Excel. cells [4, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the title format to center and align
}

//
// Obtain the data in the table
//
Foreach (datarowview row in DV)
{
Rowindex ++;
Colindex = 1;
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
If (Col. datatype = system. type. GetType ("system. datetime "))
{
Excel. cells [rowindex, colindex] = (convert. todatetime (row [col. columnname]. tostring (). tostring ("yyyy-mm-dd ");
XST. get_range (Excel. cells [rowindex, colindex], Excel. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the Field Format of the date type to center-aligned.
}
Else
If (Col. datatype = system. type. GetType ("system. String "))
{
Excel. cells [rowindex, colindex] = "'' "+ row [col. columnname]. tostring ();
XST. get_range (Excel. cells [rowindex, colindex], Excel. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // you can specify center-aligned fields.
}
Else
{
Excel. cells [rowindex, colindex] = row [col. columnname]. tostring ();
}
}
}
//
// Load a total row
//
Int rowsum = rowindex + 1;
Int colsum = 2;
Excel. cells [rowsum, 2] = "Total ";
XST. get_range (Excel. cells [rowsum, 2], Excel. cells [rowsum, 2]). horizontalalignment = xlhalign. xlhaligncenter;
//
// Set the color of the selected part
//
XST. get_range (Excel. cells [rowsum, colsum], Excel. cells [rowsum, colindex]). Select ();
XST. get_range (Excel. cells [rowsum, colsum], Excel. cells [rowsum, colindex]). Interior. colorindex = 19; // set to light yellow. There are 56 in total
//
// Obtain the title of the entire report
//
Excel. cells [2, 2] = STR;
//
// Set the title format of the entire report
//
XST. get_range (Excel. cells [2, 2], Excel. cells [2, 2]). Font. Bold = true;
XST. get_range (Excel. cells [2, 2], Excel. cells [2, 2]). Font. size = 22;
//
// Set the report table to the optimum width
//
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Select ();
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Columns. autofit ();
//
// Set the title of the entire report to center across Columns
//
XST. get_range (Excel. cells [2, 2], Excel. cells [2, colindex]). Select ();
XST. get_range (Excel. cells [2, 2], Excel. cells [2, colindex]). horizontalalignment = xlhalign. xlhaligncenter1_ssselection;
//
// Draw a border
//
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Borders. linestyle = 1;
XST. get_range (Excel. cells [], Excel. cells [rowsum, 2]). Borders [xlbordersindex. xledgeleft]. Weight = xlborderweight. xlthick; // set the left line to bold
XST. get_range (Excel. cells [], Excel. cells [4, colindex]). Borders [xlbordersindex. xledgetop]. Weight = xlborderweight. xlthick; // set the upper line to bold
XST. get_range (Excel. cells [4, colindex], Excel. cells [rowsum, colindex]). borders [xlbordersindex. xledgeright]. weight = xlborderweight. xlthick; // you can specify the width of the right line.
XST. get_range (Excel. cells [rowsum, 2], Excel. cells [rowsum, colindex]). borders [xlbordersindex. xledgebottom]. weight = xlborderweight. xlthick; // you can specify the width of the lower line.
//
// Display Results
//
Excel. Visible = true;

// XST. export (server. mappath (". ") +" \ "+ this. xlfile. text + ". xls ", sheetexportactionenum. ssexportactionnone, Microsoft. office. interOP. owc. sheetexportformat. ssexporthtml );
Xbk. savecopyas (server. mappath (".") + "\" + this. xlfile. Text + ". xls ");

DS = NULL;
Xbk. Close (false, null, null );

Excel. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xbk );
System. runtime. interopservices. Marshal. releasecomobject (Excel );
System. runtime. interopservices. Marshal. releasecomobject (XST );
Xbk = NULL;
Excel = NULL;
XST = NULL;
GC. Collect ();
String Path = server. mappath (this. xlfile. Text + ". xls ");

System. Io. fileinfo file = new system. Io. fileinfo (PATH );
Response. Clear ();
Response. charset = "gb2312 ";
Response. contentencoding = system. Text. encoding. utf8;
// Add the header information and specify the default file name for the "download/Save as" dialog box
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (file. Name ));
// Add header information and specify the file size so that the browser can display the download progress.
Response. addheader ("Content-Length", file. length. tostring ());

// Specify a stream that cannot be read by the client and must be downloaded.
Response. contenttype = "application/MS-excel ";

// Send the file stream to the client
Response. writefile (file. fullname );
// Stop page execution

Response. End ();
}