.net中将DataSet导入到Execl的几种办法（自己学习备用）
using System;2
using System.Data;3
using System.Configuration;4
using System.Collections;5
using System.Web;6
using System.Web.Security;7
using System.Web.UI;8
using System.Web.UI.WebControls;9
using System.Web.UI.WebControls.WebParts;10
using System.Web.UI.HtmlControls;11
using System.Data;12
using System.Data.SqlClient;13
14
public partial class SubPage_DatasetTOexcel : System.Web.UI.Page15
{16
string OutPutName = DateTime.Now.ToString("yyyyMMdd");17
// string OutPutName = DateTime.ToString("yyyyMMdd", DATETIMEF.InvariantInfo);18
19
protected void Page_Load(object sender, EventArgs e)20
{21
22
}23
//DATASET to excel24
public void CreateExcel(DataSet ds, string typeid, string FileName)25
{26
HttpResponse resp;27
resp = Page.Response;28
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");29
resp.ContentType = "application/ms-excel";30
31
resp.AddHeader("Content-Disposition",32
"attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");33
34
this.EnableViewState = false;35
36
string colHeaders = "", Is_item = "";37
int i = 0;38
39
//定义表对象与行对象，同时使用DataSet对其值进行初始化40
DataTable dt = ds.Tables[0];41
DataRow[] myRow = dt.Select("");42
//typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件43
if (typeid == "1")44
{45
//取得数据表各列标题，标题之间以\t分割，最后一个列标题后加回车符46
for (i = 0; i < dt.Columns.Count; i++)47
{48
colHeaders += dt.Columns[i].Caption.ToString() + "\t";49
}50
colHeaders += "\n";51
52
resp.Write(colHeaders);53
//逐行处理数据54
foreach (DataRow row in myRow)55
{56
//在当前行中，逐列取得数据，数据之间以\t分割，结束时加回车符\n57
for (i = 0; i < dt.Columns.Count; i++)58
{59
Is_item += row[i].ToString() + "\t";60
}61
Is_item += "\n";62
resp.Write(Is_item);63
Is_item = "";64
}65
}66
else67
{68
if (typeid == "2")69
{70
//从DataSet中直接导出XML数据并且写到HTTP输出流中71
resp.Write(ds.GetXml());72
}73
}74
//写缓冲区中的数据到HTTP头文件中75
resp.End();76
77
}78
79
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)80
{81
// SqlConnection con = new SqlConnection("server=DAIJING\\SQLEXPRESS;uid=sa;pwd=sa;database=pubs");82
SqlConnection con = new SqlConnection("server=DAIJING\\SQLEXPRESS;database=KJESMS_PSI;uid=sa;pwd=sa");83
con.Open();84
85
SqlDataAdapter cmd = new SqlDataAdapter("SELECT * FROM tb_bl_city", con);86
87
DataSet ds = new DataSet();88
cmd.Fill(ds);89
90
CreateExcel(ds, "1", OutPutName);91
con.Close();92
93
}94
}95
96
二、将DataSet导出到Excel文件中
#region 导出 private void cmdExport_ServerClick(object sender, System.EventArgs e) { try { string DownloadPath=Server.MapPath("../DataFolder/FileExport"); //副本的文件夹路径。 //副本的文件名。 string TempFileName = DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS"; this.txtTempFileName.Text=TempFileName; object missing = System.Reflection.Missing.Value; object missing2 = System.Reflection.Missing.Value; Excel.Application myExcel=new Excel.Application(); Excel.Workbook myBook=(Excel.Workbook)myExcel.Workbooks.Add(missing); Excel.Worksheet curSheet = (Excel.Worksheet)myBook.Sheets[1]; //设置Excel样式 Range r1=(Range)myExcel.Cells[1,2]; Range r2=(Range)myExcel.Cells[3,4]; //Range r10=(Range)myExcel.Cells[9,10]; //r10.AutoOutline(); //r10.FillLeft(); r1.Font.Bold=true; r1.Font.Color=Information.RGB(0,255,0); r2.Font.Bold=true; string DownloadFilePath=DownloadPath+"\\"+TempFileName; DataSet ds=this.GetDataSet(); int rc=ds.Tables[0].Rows.Count; //绘制边框 Range rBorders=(Range)curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7+rc+1,10]); rBorders.Borders.LineStyle=1; curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7+rc+1,1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[7,10]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置设置上边线加粗 curSheet.get_Range(myExcel.Cells[7+rc+1,1],myExcel.Cells[7+rc+1,10]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 curSheet.get_Range(myExcel.Cells[7,10],myExcel.Cells[7+rc+1,10]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 //Excel的表头信息 myExcel.Cells[1,2]=this.lblExcelHead.Text; myExcel.Cells[2,2]="New Added:" + DateTime.Now.ToString(); myExcel.Cells[3,4]="VENDOR CODE LIST - BY PRODUCTS."; myExcel.Cells[4,4]="****************************************"; myExcel.Cells[5,9]="DATE From:" + this.txtCreateDate.Text; myExcel.Cells[6,9]="DATE To:" + this.txtDateNow.Text; myExcel.Cells[7,5]="PARTS SUPPLIER"; //设置Excel表列头 myExcel.Cells[8,1]="Item"; myExcel.Cells[8,2]="OrgCode"; myExcel.Cells[8,3]="VendorCode"; myExcel.Cells[8,4]="VendorName"; myExcel.Cells[8,5]="A"; myExcel.Cells[8,6]="B"; myExcel.Cells[8,7]="C"; myExcel.Cells[8,8]="PayMentType"; myExcel.Cells[8,9]="TermsCode"; myExcel.Cells[8,10]="CreateTime"; //设置表头字体风格 curSheet.get_Range(myExcel.Cells[7,1],myExcel.Cells[8,10]).Font.Bold=true; int j=1;//j为总结的Item数目的变量 int i=9; while (i-8<=ds.Tables[0].Rows.Count) { myExcel.Cells[i,1]=j.ToString(); myExcel.Cells[i,2]=ds.Tables[0].Rows[i-9]["OrgCode"].ToString().Trim(); myExcel.Cells[i,3]=ds.Tables[0].Rows[i-9]["VendorCode"].ToString().Trim(); myExcel.Cells[i,4]=ds.Tables[0].Rows[i-9]["VendorName"].ToString().Trim(); myExcel.Cells[i,5]=""; myExcel.Cells[i,6]=""; myExcel.Cells[i,7]=""; myExcel.Cells[i,8]=ds.Tables[0].Rows[i-9]["PayType"].ToString().Trim(); myExcel.Cells[i,9]=ds.Tables[0].Rows[i-9]["TermsCode"].ToString().Trim(); myExcel.Cells[i,10]=Convert.ToDateTime(ds.Tables[0].Rows[i-9]["CreateTime"].ToString()).ToShortDateString().Trim(); if(Convert.ToDateTime(ds.Tables[0].Rows[i-9]["CreateTime"].ToString()).Month==Convert.ToDateTime(this.txtDateNow.Text).Month) { for(int qq=1;qq<=10;qq++) { Range r=(Range)myExcel.Cells[i,qq]; r.Font.Color=Information.RGB(0,255,0); r=null; } } //设置颜色，否则日期显示成"######"格式。 Range rCol10=(Range)myExcel.Cells[i,10]; rCol10.Font.Color=Information.RGB(0,0,0); rCol10=null; //从1开始循环 j++; i++; } myBook.Saved=true; myBook.SaveAs(DownloadFilePath,missing2,"","",false,false,Excel.XlSaveAsAccessMode.xlNoChange,1,false,missing,missing); //myBook.PrintPreview(0); //myBook.PrintOut(missing,missing,missing,missing,missing,missing,missing,missing); myBook.Close(false, null,null); myExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel); myBook = null; myExcel = null; GC.Collect(); //Response.Redirect(DownloadFilePath);//下载文件 try { BomUploadController BUC=new BomUploadController(); BUC.KillProcess("Excel"); } //错误信息的处理 catch (BomUploadController.FileException ex) { string[] errCase=ex.ErrorString.Split("!".ToCharArray()); string script = "<Script language=javascript>"; if(errCase[1].Trim()=="6") { script+="alert('" + errCase[0] + this.lblProcErr.Text.Trim()+ "');"; } script += "</Script>"; Page.RegisterStartupScript("fileException", script); } Session["OVSDownloadFilePath"]=DownloadFilePath; Session["OVSTempFileName"]=TempFileName; Response.Write("<Script language=\"javascript\">window.open(\"TempShow.aspx\",\"\",\"\");</Script>"); this.BindWebGridColumn(); } catch(Exception Ex) { throw Ex; } /*finally { BomUploadController BUC=new BomUploadController(); BUC.KillProcess("Excel"); }*/ } #endregion//TempShow.aspx.cs页面文件public class TempShow : System.Web.UI.Page { private void Page_Load(object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 if(!this.IsPostBack) { try { string DownloadFilePath=""; string TempFileName=""; if(Session["OVSDownloadFilePath"]!=null && Session["OVSTempFileName"]!=null) { DownloadFilePath=Session["OVSDownloadFilePath"].ToString(); TempFileName=Session["OVSTempFileName"].ToString(); } else { Response.Write("<script>window.close();</script>"); return; } System.IO.FileInfo fleInfo = new System.IO.FileInfo(DownloadFilePath); Response.Clear(); Response.Buffer=true; Response.Charset = "GB2312"; Response.AddHeader("Content-Disposition","attachment; filename="+TempFileName.Trim()+""); //'------------------------------------------inline(在线打开)，attachment（下载） Response.AddHeader("Content-Length", fleInfo.Length.ToString()); Response.ContentType = "application/ms-excel"; Response.WriteFile(fleInfo.FullName); Session.Remove("OVSDownloadFilePath"); Session.Remove("OVSTempFileName"); //Response.Write("<script>window.close();</script>"); //Response.End(); } catch(Exception Ex) { throw Ex; } finally { //Response.Write("<script>alert('haha');</script>"); Response.Write("<Script>window.close();</Script>"); Response.End(); } } }三、把指定的DataSet中的内容导出到Excel中
/// <summary> /// 把指定的DataSet中的内容导出到Excel中. /// </summary> /// <param name="ds">ds参数为DateSet对象</param> /// <param name="ImpStr">该字符串用来指定一些字段来导出,其使用格式为:aaa|AAA,bbb|BBB 小写字符串为在Excel表中显示该字段的列标题,大写表示字段名.不同字段使用逗号分隔.</param> /// <param name="locR">数据导出到Excel中的开始行</param> /// <param name="locC">数据导出到Excel中的开始列</param> /// <param name="TitleStr">该字符串用来指定要导出的表和表的标题其格式为:aaa|AAA,bbb|BBB 小写为该表中显示的标题,大写为表名称.不同表使用逗号分隔</param> /// <param name="TilteLocR">显示标题的行</param> /// <param name="TitleLocC">显示标题的列</param> /// <param name="ColTitleColorIndex">列标题颜色索引</param> /// <example>Import2Excel(this.dsData ,"用户名|USERNAME,登录名|LOGINNAME",3,1,"用户名列表|BILL_USER",1,2 ,12);</example> private void Import2Excel(System.Data.DataSet ds,string ImpStr ,int locR,int locC,string TitleStr,int TilteLocR, int TitleLocC,int ColTitleColorIndex ) { //加载Excel Microsoft.Office.Interop.Excel.ApplicationClass ex=new Microsoft.Office.Interop.Excel.ApplicationClass(); ex.Visible=true ;//显示Excel if (ex==null) { //无法显示时报错 MessageBox.Show("无法启动 Microsoft Office Excel,导出失败!","Import2Excel", MessageBoxButtons.OK,MessageBoxIcon.Stop ); return ; } string[] tles=TitleStr.Split(",".ToCharArray()); string[] Imps=ImpStr.Split(",".ToCharArray()) ; string[] tlbs=TitleStr.Split(",".ToCharArray ()); //获得工作薄 Microsoft.Office.Interop.Excel.Workbooks wbs =ex.Workbooks; Microsoft.Office.Interop.Excel.Workbook wb= wbs.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); int z=0;//Sheet索引 foreach (string tblname in tlbs) { z+=1; //得到表和工作表一个工作表对应一个表, string[] tbn=tblname.Split("|".ToCharArray()); System.Data.DataTable dt=ds.Tables[tbn[1].ToUpper() ]; Microsoft.Office.Interop.Excel.Worksheet ws= (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[z]; //工作表的第一行标题设置为该表的表名. //从格式字符串中去表名对应的名称.// int tm1=TitleStr.ToUpper().IndexOf(dt.TableName+"|")+dt.TableName.Length +1;// string tit=TitleStr.Substring(tm1,TitleStr.IndexOf(",",tm1)-tm1); ws.Cells[TilteLocR, TitleLocC]=tbn[0] ;//设置该单元格显示的标题 ws.Name=dt.TableName ;//设置该Sheet的名称为表名. int n1=locC; //设置字段名称 foreach (string imp in Imps) { string[] im=imp.Split("|".ToCharArray()); n1+=1 ; ws.Cells[locR, n1]=im[0];//'字段名称 Microsoft.Office.Interop.Excel.Range rg= (Microsoft.Office.Interop.Excel.Range)ws.Cells[locR, n1]; rg.Interior.ColorIndex=ColTitleColorIndex; rg.Font.Bold=true; } for (int y=0;y<=dt.Rows.Count -1;y+=1) { System.Data.DataRow dr=dt.Rows[y]; int n2=1; foreach (string imp in Imps) { string[] im=imp.Split("|".ToCharArray()); ws.Cells[locR+1+y,locC+n2]=dr[im[1].ToUpper()]; n2+=1; } } } } 四、高速从DataSet导出到Excel的方案
// 将当前查询的数据导入Excel中 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["sqlcon"]); // 设置为true,则在当前的数据上追加数据,否则,重写当前数据 StreamWriter w = new StreamWriter(Server.MapPath("数据库.xls"), false, Encoding.Default); con.Open(); try { SqlCommand SqlCmd = con.CreateCommand(); SqlCmd.CommandText = "select * from flysoblog"; SqlDataReader Reader = SqlCmd.ExecuteReader(); for (int i = 0; i < Reader.FieldCount; ++i) { w.Write(Reader.GetName(i)); w.Write('\t'); } w.Write("\r\n"); object[] values = new object[Reader.FieldCount]; while (Reader.Read()) { Reader.GetValues(values); for (int i = 0; i < values.Length; ++i) { w.Write(values[i].ToString()); w.Write('\t'); } w.Write("\r\n"); } w.Flush(); w.Close(); Reader.Close(); con.Close(); Response.Redirect("数据库.xls"); } catch { w.Close(); con.Close(); return; }
五、DataGrid内容导出标准的Excel格式文件
前言：
用传统的导出方法：只是将DataGrid信息用html输出，文件名后辍是.xls而已。如果想将这个方法导入到Sql Server 中，会提示出错。因为它不是标准的Excel格式文件。
用本例中的导出方法：会输出标准的Excel格式文件，非常稳定，不会死锁Excel进程，支持中文文件名，支持表头导出，支持大多数数据库导入。
实现算法：
利用Excel组件将DataGrid控件内容生成Excel临时文件，并存放在服务器上，并用Response方法将生成的Excel文件下载到客户端，再将生成的临时文件删除。
具体步骤：
1.在项目中引用Excel组件
Interop.Excel.dll 文件版本1.3.0.0
2.项目中应有一个目录（本例中Template目录），以便存放Excel文件（名字自己定）
3.导入方法类
/// <summary> /// 将DataGrid中数据导出至Excel，生成标准的Excel文件 /// </summary> /// <param name="grid">DataGrid控件ID</param> /// <param name="fileName">导出文件名</param> protected void ExportToExcel(System.Web.UI.WebControls.DataGrid grid,string fileName) { string templetFilePath; templetFilePath = Server.MapPath("../").ToString() + @"Template\"; object missing = Missing.Value; Excel.Application app; Excel.Workbook workBook; Excel.Worksheet workSheet; Excel.Range range; //创建一个Application对象并使其不可见 app = new Excel.ApplicationClass(); app.Visible=false; //打开模板文件，得到WorkBook对象 //workBook = app.Workbooks.Open(templetFilePath + "SuperTemplet.xls", missing, missing, missing, missing, missing, // missing, missing, missing, missing, missing, missing, missing); //创建一个WorkBook对象 workBook = app.Workbooks.Add(missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); int rowCount = grid.Items.Count + 1; //DataTable行数+GirdHead int colCount = grid.Columns.Count; //DataTable列数 //利用二维数组批量写入 string[,] arr = new string[rowCount, colCount]; for (int j = 0; j < rowCount; j++) { for (int k = 0; k < colCount; k++) { if (j == 0) { arr[j, k] = grid.Columns[k].HeaderText; } else { arr[j, k] = grid.Items[j - 1].Cells[k].Text.ToString(); } } } range = (Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标 range = range.get_Resize(rowCount, colCount); range.Value = arr; workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing,Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing); if (workBook.Saved) { workBook.Close(null, null, null); app.Workbooks.Close(); app.Quit(); } if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect();//强制代码垃圾回收 //下载文件 DownLoadFile(templetFilePath,fileName); }4.下载文件方法类
/// <summary> /// 下载服务器文件到客户端 /// </summary> /// <param name="_FilePath">文件路径</param> /// <param name="_FileName">文件名</param> /// <returns>返回 bool型</returns> private bool DownLoadFile(string _FilePath,string _FileName) { try { System.IO.FileStream fs = System.IO.File.OpenRead(_FilePath+_FileName); byte[] FileData = new byte[fs.Length]; fs.Read(FileData, 0, (int)fs.Length); Response.Clear(); Response.AddHeader("Content-Type", "application/ms-excel"); string FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_FileName)); Response.AddHeader("Content-Disposition", "inline;filename=" + System.Convert.ToChar(34) + FileName + System.Convert.ToChar(34)); Response.AddHeader("Content-Length", fs.Length.ToString()); Response.BinaryWrite(FileData); fs.Close(); //删除服务器临时文件 System.IO.File.Delete(_FilePath+_FileName); Response.Flush(); Response.End(); return true; } catch(Exception ex) { ex.Message.ToString(); return false; } }
5.应用方法
protected void btnExportToExcel_Click(object sender, EventArgs e) { this.ExportToExcel(grdBudget,"XXXXX.xls");//grdBudget 是DataGrid的ID }
六、datagrid数据导出到excel文件给客户端下载的几种方法
方法一：导出到csv文件，存放在服务器端任一路径，然后给客户下载
优点：
1、可以进行身份认证后给客户下载，如果放到非web目录就没有对应的url，客户无法随时下载。
2、也是因为生成了文件，所以占用了服务器的空间，但是可以把文件名存放到数据库，再次给客户下载的时候不需要重复生成文件。
3、csv文件是文本文件，逗号隔开字段，回车隔开行，易于数据导入导出。
实现方法：
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 name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数 FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); sw.WriteLine("自动编号,姓名,年龄"); 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";// 指定返回的是一个不能被客户端读取的流，必须被下载 Response.WriteFile(name); // 把文件流发送到客户端 Response.End(); 方法二：导出到csv文件，不存放到服务器，直接给浏览器输出文件流
优点：
1、随时生成，不需要占用资源
2、可以结合身份认证
3、同样利于数据交换
实现方法：
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"]; StringWriter sw=new StringWriter(); sw.WriteLine("自动编号,姓名,年龄"); foreach(DataRow dr in dt.Rows) { sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); } sw.Close(); Response.AddHeader("Content-Disposition", "attachment; filename=test.csv"); Response.ContentType = "application/ms-excel"; Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); Response.Write(sw); Response.End(); 对方法一，二补充一点，如果你希望导出的是xls文件分隔符用\t就可以了，不要用逗号
代码修改如下：
sw.WriteLine("自动编号\t姓名\t年龄");
foreach(DataRow dr in dt.Rows)
{
sw.WriteLine(dr["ID"]+"\t"+dr["vName"]+"\t"+dr["iAge"]);
}
另外，修改输出的文件扩展名为xls即可。
方法三：从datagrid导出html代码，生成excel文件，给客户端下载
优点：
1、有固定的格式，样子好看（datagrid的样子）
局限性：
1、不适合数据交换，里面有html代码，比较乱，没有固定格式
2、datagrid不能有分页、排序等，否则出错
实现方法：
Response.Clear(); Response.Buffer= false; Response.Charset="GB2312"; Response.AppendHeader("Content-Disposition","attachment;filename=test.xls"); Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); Response.ContentType = "application/ms-excel"; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.DataGrid1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); 
