Asp. NET export Excel Table method summary

Summary of methods for exporting Excel tables in ASP.

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;

Defines a Table object as a row pair, and initializes its value with a dataset.
DataTable Dt=ds. Tables[0];
Datarow[] Myrow=dt. Select ("");
typeid== "1" When exporting to an Excel format file; typeid== "2" when exporting to an XML format file
if (typeid== "1")
{
Gets the column headings of the data table, with a \ t split between headings, and a carriage return after the last column header
for (I=0;i Colheaders+=dt. Columns[i]. Caption.tostring () + "\ T";
Colheaders +=dt. Columns[i]. Caption.tostring () + "\ n";
Writes the obtained data information to the HTTP output stream
Resp. Write (colheaders);
Process data line by row
foreach (DataRow row in myrow)
{
In the current row, the data is obtained by column, the data is divided by \ t, and the end is added with a carriage return \ n
For (I=0;i Ls_item +=row[i]. ToString () + "\ T";
Ls_item + = Row[i]. ToString () + "\ n";
The current row data is written to the HTTP output stream, and an empty ls_item is placed for downstream data
Resp. Write (Ls_item);
Ls_item= "";
}
}
Else
{
if (typeid== "2")
{
Export XML data directly from the dataset and write to the HTTP output stream
Resp. Write (ds. GETXML ());
}
}
Write the data in the buffer to the HTTP header file
Resp. End ();

}

2. ActiveX controls written using Microsoft's C + +: 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, this with DataView, code is very long

public void Outputexcel (DataView dv,string str)
{
//
TODO: Add constructor logic here
//
DV is the data to be exported to Excel, Str is 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;

//
Get 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;//Sets the title format to be center aligned
}

//
Get 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 for the date type to be 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;//Set the field format for the character type to be center aligned
}
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;
//
Sets 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;//is set to light yellow, Total has 56 kinds
//
Get the title of the entire report
//
Excel. cells[2,2] = str;
//
Formatting the title of an 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 fit the 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 be centered 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.xlhaligncenteracrossselection;
//
Draw Border
//
Xst.get_range (Excel. Cells[4,2],excel. Cells[rowsum,colindex]). Borders.LineStyle = 1;
Xst.get_range (Excel. Cells[4,2],excel. cells[rowsum,2]). Borders[xlbordersindex.xledgeleft]. Weight = xlborderweight.xlthick;//Set the left line bold
Xst.get_range (Excel. Cells[4,2],excel. Cells[4,colindex]). Borders[xlbordersindex.xledgetop]. Weight = xlborderweight.xlthick;//Set upper Edge Bold
Xst.get_range (Excel. Cells[4,colindex],excel. Cells[rowsum,colindex]). Borders[xlbordersindex.xledgeright]. Weight = xlborderweight.xlthick;//Set the right line bold
Xst.get_range (Excel. Cells[rowsum,2],excel. Cells[rowsum,colindex]). Borders[xlbordersindex.xledgebottom]. Weight = xlborderweight.xlthick;//Set Lower edge Bold
//
Show effect
//
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 header information, specify a default file name for the file download/Save As dialog box
Response.AddHeader ("Content-disposition", "attachment; Filename= "+ server.urlencode (file. Name));
Add header information, specify file size, and allow the browser to display download progress
Response.AddHeader ("Content-length", file. Length.tostring ());

Specifies that a stream is returned 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 the execution of a page

Response.End ();
}

Asp. NET export Excel Table method summary