学步园

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. Export Data to Excel through 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();<br /> Response.Buffer= true;<br /> Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.<br /> Now.ToString("yyyyMMdd")+".xls");<br /> Response.ContentEncoding=System.Text.Encoding.UTF8;<br /> Response.ContentType = "application/vnd.ms-excel";<br /> 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)<br /> {<br /> HttpContext.Current.Response.AppendHeader<br /> ("Content-Disposition","attachment;filename=Excel.xls");<br /> HttpContext.Current.Response.Charset ="UTF-8";<br /> HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;<br /> HttpContext.Current.Response.ContentType ="application/ms-excel";<br /> ctl.Page.EnableViewState =false;<br /> System.IO.StringWriter tw = new System.IO.StringWriter() ;<br /> System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);<br /> ctl.RenderControl(hw);<br /> HttpContext.Current.Response.Write(tw.ToString());<br /> HttpContext.Current.Response.End();<br /> }

Method：DGToExcel(datagrid1);

Solution 3, 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)<br /> {<br /> HttpResponse resp;<br /> resp = Page.Response;<br /> resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");<br /> resp.AppendHeader("Content-Disposition",<br /> "attachment;filename="+FileName);<br /> string colHeaders= "", ls_item="";<br /> // Define table object and row object, and at the same time use DataSet initialize value.<br /> DataTable dt=ds.Tables[0];<br /> DataRow[] myRow=dt.Select();//dt.Select("id>10")<br /> Data Filer can be used as: dt.Select("id>10")<br /> int i=0;<br /> int cl=dt.Columns.Count;<br /> //Get column titles of each DataTable and divided by<br /> “t”. Press “enter” after the last column title.<br /> for(i=0;i<cl;i++)<br /> 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 4, Export Data from Datview 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)<br /> {<br /> //dv presents data which will be exported to Excel,<br /> str is the name of title<br /> GC.Collect();<br /> Application excel;// = new Application();<br /> int rowIndex=4;<br /> int colIndex=1;<br /> _Workbook xBk;<br /> _Worksheet xSt;<br /> excel= new ApplicationClass();<br /> xBk = excel.Workbooks.Add(true);<br /> xSt = (_Worksheet)xBk.ActiveSheet;<br /> //<br /> // Acquire Title<br /> //<br /> foreach(DataColumn col in dv.Table.Columns)<br /> {<br /> colIndex++;<br /> excel.Cells[4,colIndex] = col.ColumnName;<br /> xSt.get_Range(excel.Cells[4,colIndex],excel.Cells<br /> [4,colIndex]).HorizontalAlignment<br /> = XlVAlign.xlVAlignCenter;//Set title format as middle<br /> } </p> <p> //<br /> //Obtain data from table<br /> //<br /> foreach(DataRowView row in dv)<br /> {<br /> rowIndex ++;<br /> colIndex = 1;<br /> foreach(DataColumn col in dv.Table.Columns)<br /> {<br /> colIndex ++;<br /> if(col.DataType == System.Type.GetType("System.DateTime"))<br /> {<br /> excel.Cells[rowIndex,colIndex]<br /> = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");<br /> xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells<br /> [rowIndex,colIndex]).HorizontalAlignment<br /> = XlVAlign.xlVAlignCenter;// Set the style as middle<br /> }<br /> else<br /> if(col.DataType == System.Type.GetType("System.String"))<br /> {<br /> excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();<br /> xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells<br /> [rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;<br /> // Set the style as middle<br /> }<br /> else<br /> {<br /> excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();<br /> }<br /> }<br /> }<br /> //<br /> //load a Aggregate line<br /> //<br /> int rowSum = rowIndex + 1;<br /> int colSum = 2;<br /> excel.Cells[rowSum,2] = " Aggregate ";<br /> xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment<br /> = XlHAlign.xlHAlignCenter;<br /> //<br /> //Set color for the selected content<br /> //<br /> xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();<br /> xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells<br /> [rowSum,colIndex]).Interior.ColorIndex<br /> = 19;//more than 50 types of color for you to choose<br /> //<br /> //obtain title of the whole excelsheet<br /> //<br /> excel.Cells[2,2] = str;<br /> //<br /> //Set title format for the whole excelsheet<br /> //<br /> xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;<br /> xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;<br /> //<br /> //Set fittest width<br /> //<br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();<br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();<br /> //<br /> //Set the tile as Cross and Middle<br /> //<br /> xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();<br /> xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment<br /> = XlHAlign.xlHAlignCenterAcrossSelection;<br /> //<br /> //Draw borders<br /> //<br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;<br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders<br /> [XlBordersIndex.xlEdgeLeft].Weight<br /> = XlBorderWeight.xlThick;// Set left line as bold<br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders<br /> [XlBordersIndex.xlEdgeTop].Weight<br /> = XlBorderWeight.xlThick;// Set upper line as bold<br /> xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders<br /> [XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//Set right line as bold<br /> xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders<br /> [XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//Set bottom line as bold<br /> //<br /> //Display effect<br /> //<br /> excel.Visible=true;<br /> //xSt.Export(Server.MapPath(".")+");<br /> xBk.SaveCopyAs(Server.MapPath(".")+"");<br /> ds = null;<br /> xBk.Close(false, null,null);<br /> excel.Quit();<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);<br /> xBk = null;<br /> excel = null;<br /> xSt = null;<br /> GC.Collect();<br /> string path = Server.MapPath(this.xlfile.Text+".xls");<br /> System.IO.FileInfo file = new System.IO.FileInfo(path);<br /> Response.Clear();<br /> Response.Charset="UTF-8";<br /> Response.ContentEncoding=System.Text.Encoding.UTF8;<br /> //Add header, give a default file name for “File Download/Store as”<br /> Response.AddHeader("Content-Disposition", "attachment; filename="<br /> + Server.UrlEncode(file.Name));<br /> //Add header, set file size to enable browser display download progress<br /> Response.AddHeader("Content-Length", file.Length.ToString());<br /> //Set the return string is unavailable reading for client, and must be downloaded<br /> Response.ContentType = "application/ms-excel";<br /> //Send file string to client<br /> Response.WriteFile(file.FullName);<br /> //Stop execute<br /> Response.End();<br /> }

Solution 5, 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)<br /> {<br /> System.Data.OleDb.OleDbConnection oleDbConnection1<br /> = new System.Data.OleDb.OleDbConnection();<br /> oleDbConnection1.ConnectionString<br /> = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../../../Database/demo.mdb";</p> <p> System.Data.OleDb.OleDbCommand oleDbCommand1<br /> = new System.Data.OleDb.OleDbCommand();<br /> oleDbCommand1.CommandText = "select * from parts";<br /> oleDbCommand1.Connection = oleDbConnection1;</p> <p> System.Data.OleDb.OleDbCommand oleDbCommand2<br /> = new System.Data.OleDb.OleDbCommand();<br /> oleDbCommand2.CommandText = "select * from country";<br /> oleDbCommand2.Connection = oleDbConnection1;</p> <p>Spire.DataExport.Delegates.DataParamsEventHandler<br /> (this.cellExport3_GetDataParams);</p> <p> oleDbConnection1.Open();<br /> try<br /> {<br /> cellExport3.SaveToFile();<br /> }<br /> finally<br /> {<br /> oleDbConnection1.Close();<br /> }<br /> }</p> <p>private void cellExport3_GetDataParams<br /> (object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e)<br /> {<br /> if ((e.Sheet == 0) && (e.Col == 6))<br /> {<br /> e.FormatText =<br /> (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency;<br /> }<br /> }

Full Code 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 available, it's useful for Excel 2010.

2 more solutions via WinForm

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++) range.borders[excel.xlbordersindex.xlinsidehorizontal].weight="Excel.XlBorderWeight.xlThin;" range.borders[excel.xlbordersindex.xlinsidehorizontal].linestyle="Excel.XlLineStyle.xlContinuous;" range.borders[excel.xlbordersindex.xlinsidehorizontal].colorindex="Excel.XlColorIndex.xlColorIndexAutomatic;" range="(Excel.Range)worksheet.Cells[2,i+1];" this.caption.text=" Exporting Data [" this.caption.visible="false;" percent="((float)(100*rowRead))/totalCount;" worksheet.cells[r+3,i+1]="ds1.Tables[0].Rows[r];" i="0;i<ds1.Tables[0].Columns.Count;i++)" r="0;r<ds1.Tables[0].Rows.Count;r++)" .visible="true;" range.font.bold="true;" range.interior.colorindex="15;" worksheet.cells[2,i+1]="ds1.Tables[0].Columns.ColumnName;">1)
   {
    range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex
=Excel.XlColorIndex.xlColorIndexAutomatic;
    }
   workbook.Close(missing,missing,missing);
   xlApp.Quit();
}
</ds1.tables[0].columns.count;i++)>

Points of Interest  

You may need some time to read this story

History

Keep a running update of any changes or improvements you've made here.

License

About the Author