Asp.Net 导出 Excel 数据的9种方案
简介
Excel 的强大之处在于它不仅仅只能打开Excel格式的文档，它还能打开CSV格式、Tab格式、website table 等多钟格式的文档。它具备自动识别行号，字符，格式化数字等功能，例如：如果你在Excel 单元格中输入数字 "123456789012" 会自动转化为"1.23457E+11"。
背景介绍正因为Excel的强大和易用，大家都喜欢将数据导出为 Excel 备用。这里我会介绍一系列通过Asp.Net导出Excel数据的方法。将导出文件存储到服务器并提供地址给客户端下载，或重定向到文件下载页面：当Response时，数据列以 "\t" 分隔，行以"\n"分隔。好了，现在给大家展示这是怎么做的。
使用代码导出方案1：导出全部HTML 数据到 Excel
这种方法是将Html中的所有文档内容，包括按钮，表格，图片等所有页面内容导出为 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 ;
这里我们使用了Page的"ContentType" 属性，它默认为"text/Html"，输出到客户端即为Html。如果我们将它改为"ms-excel"，页面将输出Excel格式的内容，客户端就可以下载并存储它了。
页面property 还包括：image/JPEG, text/HTML, image/GIF and vnd.ms-excel/msword.
方案2：从DataGrid导出数据到Excel
尽管上面的方法能帮你导出Excel数据，但它导出了所有的HTML内容，包括按钮、图片等，这并不是我们所需要的。通常，我们仅仅需要导出DataGrid中的数据。
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();
如果你有多个包含DataGrid 并需导出数据的页面，我们可以封装方法：
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();
}
调用方法：DGToExcel(datagrid1);
方案3:自动导出Excel数据
使用此方法，你需要 下载免费的.NET组件 ，并使用如下代码（部分）导出数据：
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;
}
}
点击这里下载完整示例代码
执行上面的代码，你将得到：
此方案导出的Excel文件可以直接在Excel 2010 中打开、编辑和修改。虽然一些特定的功能不可用，但它能够被Excel 2010使用。
方案4:从DataSet导出Excel数据
依照上面都的方法，我么能很容易的导出DataSet数据到Excel，我们只需要在页面Response 时将DataSet 表中的数据组装为"ms-excel" 格式的数据，并通过Http发送出去。
注：ds 代表Dataset，用它来填充DataTable，文件名包含后缀，例如：excel2006.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 " ; " />
方案5:从DataView导出Excel数据
如果你想导出不规则的行和列到Excel，你可以使用一下方法:
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();
}
两种 WinForms 导出Excel 数据的解决方案
方案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);
}
}
方案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();
}
方案8 (from Cipherlad):
使用DataSet 的GetXml方法，并且使用XSLT将XML转化为标准的Excel格式，你可以使用不同样式模版对应不容版本的Excel，甚至可以用于导出其它文档。
方案9 (from Sergelp):
使用 OOXML 格式的开源库： http://simpleooxml.codeplex测试数据/
这是一个非常便捷的库，它包含多种格式、字体、颜色订制，你也不需要安装Excel软件，你可以在服务端创建Excel，然后实现下载，如下代码所示：
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()
原文地址： http://HdhCmsTestcodeproject测试数据/Articles/164582/8-Solutions-to-Export-Data-to-Excel-for-ASP-NET
标签: Asp.net , excel export , excel 数据导出 , .net excel 数据导出 , asp.net excel 数据导出
作者： Leo_wl
出处： http://HdhCmsTestcnblogs测试数据/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于Asp.Net 导出 Excel 数据的9种方案的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did48912