How to export excel in ASP.net：
There are two ways to export excel in ASP.net, which is to store the exported file under a server folder, and then export the file address to the browser, one of which is to direct the file output stream to the browser. When response output, T-delimited data, when exporting Excel, is equivalent to a column, n is equivalent to wrapping.
1, the entire HTML output Excel
This method prints all the content in HTML, such as buttons, tables, pictures, and so on to excel.
Copy Code code as follows:
Response.Clear ();
Response.buffer= true;
Response.appendheader ("Content-disposition", "Attachment;filename=" +datetime.now.tostring ("yyyyMMdd") + ". xls");
Response.contentencoding=system.text.encoding.utf8;
Response.ContentType = "application/vnd.ms-excel";
This. EnableViewState = false;
Here we take advantage of the ContentType property, its default property is text/html, then output to hypertext, that is, our common Web page format to the client, if changed to Ms-excel will output Excel format, that is, in the format of the spreadsheet output to the client, Then the browser will prompt you to download the save. The ContentType properties also include: Image/jpeg;text/html;image/gif;vnd.ms-excel/msword. Similarly, we can export (export) pictures, Word documents, and so on. This property is also used for the following methods.
2. Export data from the DataGrid control to Excel
Although the above method realizes the function of export, it also guides all the output information in the HTML such as button, page box and so on. What we typically export is data, data on the DataGrid control.
Copy Code code as follows:
System.Web.UI.Control Ctl=this. DATAGRID1;
DATAGRID1 is the control you drag and drop on the form
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";
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 ();
If your DataGrid uses pagination, it exports the information from the current page, which is the information displayed in the DataGrid. Instead of all the information for your SELECT statement.
For ease of use, write the following methods:
Copy Code code as follows:
public void Dgtoexcel (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";
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: dgtoexcel (DATAGRID1);
The following empty method needs to be added to the page.
public override void Verifyrenderinginserverform (Control control)
{
}
3. Export the data in the DataSet to Excel
With the above idea, is to be exported in the information, output (Response) client, so that it can be exported. Then it is OK to export the data in the dataset, that is, to response the rows in the dataset to the HTTP stream in ms-excel format. Note: The parameter DS should be a dataset populated with a datasheet with a full name, including a suffix name, such as Excel2006.xls
Copy Code code as follows:
public void Createexcel (DataSet ds,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= "";
Defines a Table object and a row object, and initializes its value with a dataset.
DataTable Dt=ds. Tables[0];
Datarow[] Myrow=dt. Select ();//can resemble dt. Select ("id>10") in the form of data filtering purposes
int i=0;
int CL=DT. Columns.count;
Get the column headings of the datasheet, divide the headings by T, and add the carriage return after the last column heading
for (i=0;i<cl;i++)
{
if (i== (CL-1))//last column, plus n
{
Colheaders +=dt. Columns[i]. Caption.tostring () + "n";
}
Else
{
Colheaders+=dt. Columns[i]. Caption.tostring () + "T";
}
}
Resp. Write (colheaders);
Writes the obtained data information to the HTTP output stream
Process data line by row
foreach (DataRow row in myrow)
{
The current row data is written to the HTTP output stream and the null Ls_item is placed so that the downlink data
for (i=0;i<cl;i++)
{
if (i== (CL-1))//last column, plus n
{
Ls_item +=row[i]. ToString () + "n";
}
Else
{
Ls_item+=row[i]. ToString () + "T";
}
}
Resp. Write (Ls_item);
Ls_item= "";
}
Resp. End ();
}
4, will DataView export Excel
This method is useful if you want to achieve more varied or irregular Excel exports.
Copy Code code as follows:
public void Outputexcel (DataView dv,string str)
{
DV for 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;//formatting headings in center alignment
}
//
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;//The field format of the date type is centered
}
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;//The field format of the character type is centered
}
Else
{
Excel. Cells[rowindex,colindex] = Row[col. ColumnName]. ToString ();
}
}
}
//
Load an aggregate 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 section
//
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 with a total of 56
//
Get the title of the entire report
//
Excel. cells[2,2] = str;
//
Set the title format for 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 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 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.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 left line bold
Xst.get_range (Excel. Cells[4,2],excel. Cells[4,colindex]). Borders[xlbordersindex.xledgetop]. Weight = xlborderweight.xlthick;//Set top 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 Bottom edge Bold
//
Display effects
//
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 filename 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 browser to display download progress
Response.AddHeader ("Content-length", file. Length.tostring ());
Specifies that a stream that cannot be read by the client is returned 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 ();
}
Above, you will export the Excel data directly to the browser output file stream, the following method is to first save it to a folder in the server, and then send the file to the client. This allows you to persist the exported files for additional functionality.
5, the Excel file exported to the server, and then download.
methods of exporting Excel in WinForm：
1, Method 1:
Copy Code code as follows:
SqlConnection conn=new SqlConnection (system.configuration.configurationsettings.appsettings["Conn"));
SqlDataAdapter Da=new SqlDataAdapter ("select * from TB1", conn);
DataSet ds=new DataSet ();
Da. Fill (ds, "Table1");
DataTable Dt=ds. tables["Table1"];
String name=system.configuration.configurationsettings.appsettings["DownloadURL"]. ToString () +datetime.today.tostring ("YyyyMMdd") +new Random (DateTime.Now.Millisecond). Next (10000). ToString () + ". csv";//stored in Web.config downloadurl specified path, file format is current date + 4-bit random number
FileStream fs=new FileStream (name,filemode.create,fileaccess.write);
StreamWriter Sw=new StreamWriter (fs,system.text.encoding.getencoding ("gb2312"));
Sw. WriteLine ("Automatic numbering, name, age");
foreach (DataRow dr in Dt. Rows)
{
Sw. WriteLine (dr["ID"]+ "," +dr["VName"]+ "," +dr["]);
}
Sw. Close ();
Response.AddHeader ("Content-disposition", "attachment; Filename= "+ server.urlencode (name));
Response.ContentType = "Application/ms-excel";//specifies that a stream that cannot be read by the client is returned and must be downloaded
Response.WriteFile (name); Send the file stream to the client
Response.End ();
public void Out2excel (string stablename,string URL)
{
Excel.Application oexcel=new Excel.Application ();
Workbooks obooks;
Workbook obook;
Sheets osheets;
Worksheet osheet;
Range Ocells;
String Sfile= "", stemplate= "";
//
System.Data.DataTable dt=tableout (sTableName). Tables[0];
sfile=url+ "Myexcel.xls";
stemplate=url+ "Mytemplate.xls";
//
Oexcel.visible=false;
Oexcel.displayalerts=false;
Define a new workbook
Obooks=oexcel.workbooks;
oBooks.Open (Stemplate,type.missing,type.missing,type.missing,type.missing,type.missing,type.missing, Type.missing,type.missing,type.missing,type.missing,type.missing,type.missing, Type.Missing, Type.Missing);
Obook=obooks.get_item (1);
Osheets=obook.worksheets;
osheet= (Worksheet) Osheets.get_item (1);
Name the sheet
Osheet.name= "Sheet1";
Ocells=osheet.cells;
Call the DumpData procedure to import data into Excel
DumpData (Dt,ocells);
Save
Osheet.saveas (sfile,excel.xlfileformat.xltemplate,type.missing,type.missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
Obook.close (False, type.missing,type.missing);
Quit Excel and release the invoked COM resource
oExcel.Quit ();
Gc. Collect ();
KillProcess ("Excel");
}
private void KillProcess (string processname)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process ();
Get all the open processes
Try
{
foreach (Process thisproc in Process.getprocessesbyname (processname))
{
if (!thisproc. CloseMainWindow ())
{
Thisproc. Kill ();
}
}
}
catch (Exception exc)
{
throw new Exception ("", exc);
}
}
2, Method 2:
Copy Code code as follows:
protected void Exportexcel ()
{
Gridbind ();
if (ds1==null) return;
String Savefilename= "";
BOOL Filesaved=false;
SaveFileDialog savedialog=new SaveFileDialog ();
Savedialog.defaultext = "xls";
savedialog.filter= "Excel file |*.xls";
Savedialog.filename = "Sheet1";
Savedialog.showdialog ();
Savefilename=savedialog.filename;
if (Savefilename.indexof (":") <0) return; It was ordered to cancel.
Excelapp. Workbooks.Open (App.Path & Project schedule. xls)
Excel.Application xlapp=new Excel.Application ();
Object Missing=system.reflection.missing.value;
if (xlapp==null)
{
MessageBox.Show ("Cannot create Excel object, you may not have Excel installed on your machine");
Return
}
Excel.Workbooks Workbooks=xlapp.workbooks;
Excel.Workbook Workbook=workbooks. ADD (Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet= (excel.worksheet) workbook. worksheets[1];//get Sheet1
Excel.Range Range;
String Oldcaption=title_label. Text.trim ();
Long totalcount=ds1. Tables[0]. Rows.Count;
Long rowread=0;
float percent=0;
Worksheet. Cells[1,1]=title_label. Text.trim ();
Write fields
for (int i=0;i<ds1. Tables[0]. columns.count;i++)
{
Worksheet. Cells[2,i+1]=ds1. Tables[0]. Columns.columnname;
Range= (excel.range) worksheet. CELLS[2,I+1];
Range. Interior.ColorIndex = 15;
Range. Font.Bold = true;
}
Write value
Caption. Visible = true;
for (int r=0;r<ds1. Tables[0]. rows.count;r++)
{
for (int i=0;i<ds1. Tables[0]. columns.count;i++)
{
Worksheet. Cells[r+3,i+1]=ds1. Tables[0]. ROWS[R];
}
rowread++;
Percent= ((float) (100*rowread))/totalcount;
This. caption.text= "Exporting data [" + percent. ToString ("0.00") + "%] ...";
Application.doevents ();
}
Worksheet. SaveAs (savefilename,missing,missing,missing,missing,missing,missing,missing,missing);
This. Caption.visible= false;
This. Caption.text= oldcaption;
Range=worksheet.get_range (worksheet. Cells[2,1],worksheet. Cells[ds1. Tables[0]. Rows.count+2,ds1. Tables[0]. Columns.count]);
Range. Borderaround (Excel.xllinestyle.xlcontinuous,excel.xlborderweight.xlthin, Excel.xlcolorindex.xlcolorindexautomatic,null);
Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. LineStyle =excel.xllinestyle.xlcontinuous;
Range. Borders[excel.xlbordersindex.xlinsidehorizontal]. Weight =excel.xlborderweight.xlthin;
if (DS1. Tables[0]. COLUMNS.COUNT>1)
{
Range. Borders[excel.xlbordersindex.xlinsidevertical]. colorindex=excel.xlcolorindex.xlcolorindexautomatic;
}
Workbook. Close (missing,missing,missing);
xlApp.Quit ();
}
Iii. Notes：
Although all are the functions of exporting Excel, in asp.net and WinForm programs, the code implemented is different. In asp.net, the data is read on the server side, the data is ms-excel in the server end to response output to the browser (client), and in WinForm, the data is read to the client (because the WinForm runtime is the client), Then call the Office component of the client installation and write the data that you read in Excel
