9 Solutions to Export Data to Excel for ASP.NET

• Download method 4 source - 2.67 KB

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;
//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.

• Download full code - 2.67 KB

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="";   
 // 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.

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()

Solutions from Others

This soulution is from rahul_kumar111, thanks a lot!

Export data to excel with enabling excel's default sorting and filtering on columns. We have the control to set worksheet name. And all this can be achieved without using Interop.

More detaisl can be couslted at this blog post: http://blog.weareon.net/export-datatable-to-excel-in-asp-net-without-using-excel-interop-part-ii/[^]