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 exporting Response, data is 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 contents 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 to 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 to export data stored in DatGrid.
System.Web.UI.Control ctl=this.DataGrid1;
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 code above, you will get:
This solution can help you export data to Excel file which enables you to 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="";
DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select();Data Filer can be used as: dt.Select("id>10")
int i=0;
int cl=dt.Columns.Count;
"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)
{
str is the name of title
GC.Collect();
Application excel; int rowIndex=4;
int colIndex=1;
_Workbook xBk;
_Worksheet xSt;
excel= new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
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; }
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; }
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;
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
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;
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; excel.Cells[2,2] = str;
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;
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();
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;
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; xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders
[XlBordersIndex.xlEdgeTop].Weight
= XlBorderWeight.xlThick; xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders
[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick; xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders
[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; excel.Visible=true;
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;
Response.AddHeader("Content-Disposition", "attachment; filename="
+ Server.UrlEncode(file.Name));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(file.FullName);
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="";
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;
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]; 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 ();
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.
Solution 9 (from Sergelp)
Using the OOXML format with the opensource library http://simpleooxml.codeplex.com/.
This is extremely fast, has lots of formatting/font/color possibilities and you don't need Excel.
You can create your Excel on the serverside with this library and then perform a download with the following:
Dim ms As MemoryStream = ArticleDAL.GetStreamFromDataSet()
Response.Clear()
Response.AddHeader("content-disposition",
String.Format("attachment;filename={0}", strFile))
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
ms.WriteTo(Response.OutputStream)
Response.End()
.....
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin