8 Solutions to Export Data to Excel for ASP.NET
Introduction
Excel is so powerful that it can easily successfully open not only excel itself,
but also CSV format files, Tab format, website table format and other file formats.
It’s so smart that it can automatically recognize number, character string and can
easily automatically transfer 11+ digital natural numbers to scientific notation.
For example, if you input number "123456789012" in a cell, the number will be changed
as "1.23457E+11".
Background
Because Excel is powerful and useful, people always export data to Excel for future use. Here I am introducing several methods of exporting data to Excel via ASP.NET. Store the output file in a folder of server and put the file address on browser. Or directly write the file string on browser. When Response exporting, data divided by "t" equals columns and "n" means rows. Now let’s enjoy the show!
Using the code
Solution 1: Export all HTML data to Excel
This method will transfer all the content in HTML, such as button, table, images and others to Excel.
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/ms-excel";;
this.EnableViewState = false;
Here we make use of "ContentType" property which the default set is text/HTML.
And hypertext will be exported to client. If we change it to ms-excel, it will be
exported as Excel format and your browser will remind you download and store it.
The property of "ContentType" includes image/JPEG, text/HTML, image/GIF and vnd.ms-excel/msword.
Solution 2: Export Data from DataGrid to Excel
Although the method above can help you export data, it exports all data information
such as button, images from HTML which we may not want to export. Usually, we only
need export data stored in DatGrid.
System.Web.UI.Control ctl=this.DataGrid1;
//DataGrid1 (you created in the windowForm)
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 you have pages in your DataGrid, it only exports the information of the first
page, maybe not all information you select.
Actually we can write like this:
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.UTF8;
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();
}
Method：DGToExcel(datagrid1);
Solution 3: Export Data to Excel without Automation
By using this method you need download a free .net component and then do as the following codes showing(part) below:
private void button1_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection oleDbConnection1
= new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Database\demo.mdb";
System.Data.OleDb.OleDbCommand oleDbCommand1
= new System.Data.OleDb.OleDbCommand();
oleDbCommand1.CommandText = "select * from parts";
oleDbCommand1.Connection = oleDbConnection1;
System.Data.OleDb.OleDbCommand oleDbCommand2
= new System.Data.OleDb.OleDbCommand();
oleDbCommand2.CommandText = "select * from country";
oleDbCommand2.Connection = oleDbConnection1;
Spire.DataExport.Delegates.DataParamsEventHandler
(this.cellExport3_GetDataParams);
oleDbConnection1.Open();
try
{
cellExport3.SaveToFile();
}
finally
{
oleDbConnection1.Close();
}
}
private void cellExport3_GetDataParams
(object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)
{
if ((e.Sheet == 0) && (e.Col == 6))
{
e.FormatText =
(sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency;
}
}
Full code taken from here
When you execute the codes above, you will get:
This solution can help you export data to Excel file which enables you open, write and modify with excel 2010. Although some special features of excel 2010 are still not available, it's useful for Excel 2010.
Solution 4: Export Data from DataSet to Excel
According to the methods above, we can easily export data from DataSet to Excel.
We just need response rows information of DataSet table as ms-excel format to http
string. Note: ds should be DataSet with information filled Datatable. File name
should be full including suffix. For example, execl2006.xls
public void CreateExcel(DataSet ds,string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
resp.AppendHeader("Content-Disposition",
"attachment;filename="+FileName);
string colHeaders= "", ls_item="";
// Define table object and row object, and at the same time use DataSet initialize value.
DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select();//dt.Select("id>10")
Data Filer can be used as: dt.Select("id>10")
int i=0;
int cl=dt.Columns.Count;
//Get column titles of each DataTable and divided by
"t". Press "enter" after the last column title.
for(i=0;i<cl;i++) colheaders+="dt.Columns[i].Caption.ToString()+"t";" for(i="0;i<cl;i++)" if(i="=(cl-1))//(last" +="dt.Columns[i].Caption.ToString()" ls_item+="row[i].ToString()+"t";" />
Solution 5: Export Data from Dataview to Excel
If you want to export data to Excel with irregular rows or columns, you can use this method.
public void OutputExcel(DataView dv,string str)
{
//dv presents data which will be exported to Excel,
str is the name of title
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;
//
// Acquire 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 title format as middle
}
//
//Obtain data from 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 style as middle
}
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 style as middle
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//load a Aggregate line
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum,2] = " Aggregate ";
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment
= XlHAlign.xlHAlignCenter;
//
//Set color for the selected content
//
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;//more than 50 types of color for you to choose
//
//obtain title of the whole excelsheet
//
excel.Cells[2,2] = str;
//
//Set title format for the whole excelsheet
//
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 fittest 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 tile as Cross and Middle
//
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 borders
//
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 as bold
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders
[XlBordersIndex.xlEdgeTop].Weight
= XlBorderWeight.xlThick;// Set upper line as bold
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders
[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//Set right line as bold
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders
[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//Set bottom line as bold
//
//Display effect
//
excel.Visible=true;
//xSt.Export(Server.MapPath(".")+");
xBk.SaveCopyAs(Server.MapPath(".")+"");
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="UTF-8";
Response.ContentEncoding=System.Text.Encoding.UTF8;
//Add header, give a default file name for "File Download/Store as"
Response.AddHeader("Content-Disposition", "attachment; filename="
+ Server.UrlEncode(file.Name));
//Add header, set file size to enable browser display download progress
Response.AddHeader("Content-Length", file.Length.ToString());
//Set the return string is unavailable reading for client, and must be downloaded
Response.ContentType = "application/ms-excel";
//Send file string to client
Response.WriteFile(file.FullName);
//Stop execute
Response.End();
}
Two more solutions via WinForms
Solution 6
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 downloadurl"].ToString()+DateTime.Today.ToString
("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next
(10000).ToString()+".csv";//Store the path of downloadurl
in web.config and the format should be set as "date + 4 random number "
FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
StreamWriter sw=new StreamWriter
(fs,System.Text.Encoding.GetEncoding("utf-8"));("utf-8")
sw.WriteLine("Auto number, name, age");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment;
filename=" + Server.UrlEncode(name));
Response.ContentType = "application/ms-excel";
//Set the return string is unavailable reading for client, and must be downloaded
Response.WriteFile(name); //Send file string to 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);
//Give the sheet a name
oSheet.Name="Sheet1";
oCells=oSheet.Cells;
//Call dumpdata process and export to Excel
DumpData(dt,oCells);
//Store
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);
//Exit Excel and free invoking COM resource
oExcel.Quit();
GC.Collect();
KillProcess("Excel");
}
private void KillProcess(string processName)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process();
//get all opened progresses
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if(!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch(Exception Exc)
{
throw new Exception("",Exc);
}
}
Solution 7
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; // Cancelled
//excelapp.Workbooks.Open (App.path & Progress table.xls)
Excel.Application xlApp=new Excel.Application();
object missing=System.Reflection.Missing.Value;
if(xlApp==null)
{
MessageBox.Show("Create Excel object failed, maybe you dont install Excel ");
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 text
for(int i=0;i<ds1.tables[0].columns.count;i++) worksheet.cells[2,i+1]="ds1.Tables[0].Columns.ColumnName;" range.interior.colorindex="15;" range.font.bold="true;" .visible="true;" r="0;r<ds1.Tables[0].Rows.Count;r++)" i="0;i<ds1.Tables[0].Columns.Count;i++)" worksheet.cells[r+3,i+1]="ds1.Tables[0].Rows[r];" percent="((float)(100*rowRead))/totalCount;" this.caption.visible="false;" this.caption.text=" Exporting Data [" range="(Excel.Range)worksheet.Cells[2,i+1];" 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;">1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex
=Excel.XlColorIndex.xlColorIndexAutomatic;
}
workbook.Close(missing,missing,missing);
xlApp.Quit();
}
Solution 8 (from Cipherlad)
Use the GetXml method of a DataSet, and then use XSLT to transform the Xml into
the Excel standard. You can use different style sheets for different versions of
Excel, or even other exportable documents.
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架