正常访问状态! 设为首页 | 加入收藏夹 | 浏览历史  
  http://www.guosp.com
 碧海澜涛居
  海纳百川,有容乃大。壁立千刃,无欲则刚!
 
 
关键词:
  网站首页 | 关于本站 | 技术资料 | 美文日志 | 读书收藏 | 影视收藏 | 软件收藏 | 摄影相册| 留言板 
  技术资料 >> ASP.Net资料库 关闭(快捷键alt+C)
搜索标签: Excel 导出
Asp.Net 导出数据格式为Excel 数据的9种方案
[阅读次数:1178次]  [发布时间:2013年12月26日]

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

这是一个非常便捷的库,它包含多种格式、字体、颜色订制,你也不需要安装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://www.cr173.com/html/15602_1.html
返回顶部 关闭(快捷键alt+C)
评论统计(0条)| 我要评论
暂无评论内容!
我要评论 
我要评论: 带*部分需要填写
 姓名称呼: * 请填写您的姓名或呢称
联系方式: QQ,MSN,Email都可以,方便交流 (仅管理员可见)
 评论内容: * 不超过100字符,50汉字
验证码:
    
  推荐链接
·html页面表格导出到excel总...
·如何去除Access字段中的空格...
·以Word,Excl等各种格式保存...
·.net教程:反编译工具Reflec...
·从网页导出数据生成Excel后...
·asp遍历IIS站点,并导出域名...
·用HTML 格式导出Excel 时,...
·C#读取Excel遇到无法读取的...
·ASP中导入Excel数据时的连接...
·ASP学习摘要,VBscript函数汇...
·关于HTML导出到excel中的问...
·C#读取Excel的简单实现
·【为WIN2000系统添加远程桌...
·asp.net导出为pdf文件
·mssql数据导入导出详解 企业...
·MySql的使用
·asp.net生成pdf文件
·Err.number错误号和错误说明...
·excel2003函数大全
·PHP网站更换服务器遇到的问...
  最近更新  
·Host 'XXX' is not allowed...
·Win2008或IIS7的文件上传大...
·IIS7.0上传文件限制的解决方...
·测试信息2015-03-11
·asp.net中处理图片
·ASP.NET之Web打印-终极解决...
·Asp.net下C#调用Word模版实...
·asp.net下将页面内容导入到...
·asp.net导出为pdf文件
·asp.net生成pdf文件
·FCKeditor 文本编辑器的使用...
·ASP.NET 将数据生成PDF
·asp.net2.0导出pdf文件完美...
·AspJpeg的安装与测试
·JS验证浏览器版本对IE11的支...
  热门浏览  
·IE8和IE9出现“此网页上的问...
·无线路由器密码破解,教你断...
·js替换所有回车换行符
·QQ/MSN在线交流代码
·IE弹出“中国工商银行防钓鱼...
·如何取消键盘上的一些快捷键...
·win7声音小的解决方法
·webdav漏洞的利用
·强制两端对齐的函数或者CSS...
·win7下成功安装sql server ...
·显示器分辨率调的过高导致电...
·天诺时空技术技术论坛
·js验证手机号码格式
·JS展开和收缩效果(二)
·本地计算机上的 MSSQLSERVE...
  碧海澜涛居
网站首页关于本站站长简介开发案例技术资料美文日志摄影相册读书收藏影视收藏留言板
版权所有:碧海澜涛 QQ:410436434 Email:shaopo_guo@163.com 苏ICP备15000526号
免责声明:本站为个人网站,站内所有文字、图片等各类资料均为个人兴趣爱好所收集,不用作任何商业用途,亦不保证资料的真实性,若有因浏览本站内容而导致的各类纠纷,本站也不承担任何责任。本站部分内容来自互联网,如有涉及到您的权益或隐私请联系站长解决。