[数据访问] C#将数据导出到Excel的各种方法汇总

1.使用流导入的方法

1.   string html = "@006|销售代表|4010200@008|客户代表|4010200@011";
2.         string[] list = html.ToLower().Split('|');
3.         string result = "";
4.         for (int i = 0; i < list.Length; i++)
5.         {
6.             if (list.Trim().Contains("@") || list.Trim().Contains("其他"))
7.             {
8.             }
9.             else
10.             {
11.                 result += list.Trim() + "\r\n";
12.             }
13.         }
14.         StreamWriter sw = new StreamWriter("D:\\abc.xls", false, System.Text.Encoding.UTF8);
15.         sw.WriteLine(result);
16.         sw.Close();
17.        

普通浏览复制代码保存代码打印代码

1.   string html = "@006|销售代表|4010200@008|客户代表|4010200@011";
2.         string[] list = html.ToLower().Split('|');
3.         string result = "";
4.         for (int i = 0; i < list.Length; i++)
5.         {
6.             if (list.Trim().Contains("@") || list.Trim().Contains("其他"))
7.             {
8. 
9.             }
10.             else
11.             {
12.                 result += list.Trim() + "\r\n";
13.             }
14.         }
15.         StreamWriter sw = new StreamWriter("D:\\abc.xls", false, System.Text.Encoding.UTF8);
16.         sw.WriteLine(result);
17.         sw.Close();
18.        

string html = "@006|销售代表|4010200@008|客户代表|4010200@011";<br /> string[] list = html.ToLower().Split('|');<br /> string result = "";<br /> for (int i = 0; i < list.Length; i++)<br /> {<br /> if (list.Trim().Contains("@") || list.Trim().Contains("其他"))<br /> {<br /> <br /> }<br /> else<br /> {<br /> result += list.Trim() + "\r\n";<br /> }<br /> }<br /> StreamWriter sw = new StreamWriter("D:\\abc.xls", false, System.Text.Encoding.UTF8);<br /> sw.WriteLine(result);<br /> sw.Close();<br />

        正在更新中，，， -------------------------------------------------------------------------------------------------------- 一、asp.net中导出Excel的方法： 在asp.net中导出Excel有两种方法，一种是将导出的文件存放在服务器某个文件夹下面，然后将文件地址输出在浏览器上；一种是将文件直接将文件输出流写给浏览器。在Response输出时，t分隔的数据，导出Excel时，等价于分列，n等价于换行。 1、将整个html全部输出Excel
此法将html中所有的内容，如按钮，表格，图片等全部输出到Excel中。

1.    Response.Clear();    
2.    Response.Buffer=   true;    
3.    Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls");         
4.    Response.ContentEncoding=System.Text.Encoding.UTF8;  
5.    Response.ContentType   =   "application/vnd.ms-excel";  
6.    this.EnableViewState   =   false;  

普通浏览复制代码保存代码打印代码

1.    Response.Clear();    
2.    Response.Buffer=   true;    
3.    Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls");         
4.    Response.ContentEncoding=System.Text.Encoding.UTF8;  
5.    Response.ContentType   =   "application/vnd.ms-excel";  
6.    this.EnableViewState   =   false;  

Response.Clear(); <br /> Response.Buffer= true; <br /> Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls"); <br /> Response.ContentEncoding=System.Text.Encoding.UTF8; <br /> Response.ContentType = "application/vnd.ms-excel"; <br /> this.EnableViewState = false;

这里我们利用了ContentType属性，它默认的属性为text/html，这时将输出为超文本，即我们常见的网页格式到客户端，如果改为ms-excel将将输出excel格式，也就是说以电子表格的格式输出到客户端，这时浏览器将提示你下载保存。ContentType的属性还包括：image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword 。同理，我们也可以输出(导出)图片、word文档等。下面的方法，也均用了这个属性。
2、将DataGrid控件中的数据导出Excel 上述方法虽然实现了导出的功能，但同时把按钮、分页框等html中的所有输出信息导了进去。而我们一般要导出的是数据，DataGrid控件上的数据。

1.  
2. System.Web.UI.Control ctl=this.DataGrid1;
3. //DataGrid1是你在窗体中拖放的控件
4. HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
5. HttpContext.Current.Response.Charset ="UTF-8";    
6. HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
7. HttpContext.Current.Response.ContentType ="application/ms-excel";
8. ctl.Page.EnableViewState =false;   
9. System.IO.StringWriter  tw = new System.IO.StringWriter() ;
10. System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
11. ctl.RenderControl(hw);
12. HttpContext.Current.Response.Write(tw.ToString());
13. HttpContext.Current.Response.End();

普通浏览复制代码保存代码打印代码

1.  
2. System.Web.UI.Control ctl=this.DataGrid1;
3. //DataGrid1是你在窗体中拖放的控件
4. HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
5. HttpContext.Current.Response.Charset ="UTF-8";    
6. HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
7. HttpContext.Current.Response.ContentType ="application/ms-excel";
8. ctl.Page.EnableViewState =false;   
9. System.IO.StringWriter  tw = new System.IO.StringWriter() ;
10. System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
11. ctl.RenderControl(hw);
12. HttpContext.Current.Response.Write(tw.ToString());
13. HttpContext.Current.Response.End();

<br /> System.Web.UI.Control ctl=this.DataGrid1;<br /> //DataGrid1是你在窗体中拖放的控件<br /> HttpContext.Current.Response.AppendHeader("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 />

如果你的DataGrid用了分页，它导出的是当前页的信息，也就是它导出的是DataGrid中显示的信息。而不是你select语句的全部信息。 为方便使用,写成方法如下：

1.  
2. public void DGToExcel(System.Web.UI.Control ctl)  
3.   {
4.    HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
5.    HttpContext.Current.Response.Charset ="UTF-8";    
6.    HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
7.    HttpContext.Current.Response.ContentType ="application/ms-excel";
8.     ctl.Page.EnableViewState =false;   
9.    System.IO.StringWriter  tw = new System.IO.StringWriter() ;
10.    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
11.    ctl.RenderControl(hw);
12.    HttpContext.Current.Response.Write(tw.ToString());
13.    HttpContext.Current.Response.End();
14.   }

普通浏览复制代码保存代码打印代码

1.  
2. public void DGToExcel(System.Web.UI.Control ctl)  
3.   {
4.    HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
5.    HttpContext.Current.Response.Charset ="UTF-8";    
6.    HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
7.    HttpContext.Current.Response.ContentType ="application/ms-excel";
8.     ctl.Page.EnableViewState =false;   
9.    System.IO.StringWriter  tw = new System.IO.StringWriter() ;
10.    System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
11.    ctl.RenderControl(hw);
12.    HttpContext.Current.Response.Write(tw.ToString());
13.    HttpContext.Current.Response.End();
14.   }

<br /> public void DGToExcel(System.Web.UI.Control ctl) <br /> { <br /> HttpContext.Current.Response.AppendHeader("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 /> }

用法：DGToExcel(datagrid1); 3、将DataSet中的数据导出Excel 有了上边的思路，就是将在导出的信息，输出（Response）客户端，这样就可以导出了。那么把DataSet中的数据导出，也就是把DataSet中的表中的各行信息，以ms-excel的格式Response到http流，这样就OK了。说明：参数ds应为填充有数据表的DataSet，文件名是全名，包括后缀名，如Excel2006.xls

1. public  void CreateExcel(DataSet ds,string FileName) 
2. {
3. HttpResponse resp;
4. resp = Page.Response;
5. resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
6. resp.AppendHeader("Content-Disposition", "attachment;filename="+FileName);   
7. string colHeaders= "", ls_item="";

普通浏览复制代码保存代码打印代码

1. public  void CreateExcel(DataSet ds,string FileName) 
2. {
3. HttpResponse resp;
4. resp = Page.Response;
5. resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
6. resp.AppendHeader("Content-Disposition", "attachment;filename="+FileName);   
7. string colHeaders= "", ls_item="";

public void CreateExcel(DataSet ds,string FileName) <br /> { <br /> HttpResponse resp; <br /> resp = Page.Response; <br /> resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); <br /> resp.AppendHeader("Content-Disposition", "attachment;filename="+FileName); <br /> string colHeaders= "", ls_item="";

//定义表对象与行对象，同时用DataSet对其值进行初始化

1. DataTable dt=ds.Tables[0];
2. DataRow[] myRow=dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
3.          int i=0;
4.         int cl=dt.Columns.Count;

复制代码

//取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符

1. for(i=0;i<cl;i++)
2.   {
3.   if(i==(cl-1))//最后一列，加n
4.   {
5.   colHeaders +=dt.Columns[i].Caption.ToString() +"n";
6. }
7.   else
8.   {
9.   colHeaders+=dt.Columns.Caption.ToString()+"t";
10. }
11.        
12. }
13.   resp.Write(colHeaders);
14. //向HTTP输出流中写入取得的数据信息
15.   
16.   //逐行处理数据  
17. foreach(DataRow row in myRow)
18. {    
19.   //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据    
20. for(i=0;i<cl;i++)
21.   {
22.   if(i==(cl-1))//最后一列，加n
23.   {
24.   ls_item +=row.ToString()+"n";
25. }
26.   else
27.   {
28.   ls_item+=row.ToString()+"t";
29. }
30.   
31.   }
32.   resp.Write(ls_item);
33. ls_item="";
34.    
35.   }   
36.   resp.End(); 
37. }

复制代码

4、将dataview导出excel 若想实现更加富于变化或者行列不规则的excel导出时，可用本法。

1. public void OutputExcel(DataView dv,string str)
2. {
3.    //dv为要输出到Excel的数据，str为标题名称
4.    GC.Collect();
5.    Application excel;// = new Application();
6.    int rowIndex=4;
7.    int colIndex=1;
8.     _Workbook xBk;
9.    _Worksheet xSt;
10.     excel= new ApplicationClass();
11.   
12.    xBk = excel.Workbooks.Add(true);
13.    
14.    xSt = (_Worksheet)xBk.ActiveSheet;
15.     //
16.    //取得标题
17.    //
18.    foreach(DataColumn col in dv.Table.Columns)
19.    {
20.     colIndex++;
21.     excel.Cells[4,colIndex] = col.ColumnName;
22.     xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
23.    }
24.     //
25.    //取得表格中的数据
26.    //
27.    foreach(DataRowView row in dv)
28.    {
29.     rowIndex ++;
30.     colIndex = 1;
31.     foreach(DataColumn col in dv.Table.Columns)
32.     {
33.      colIndex ++;
34.      if(col.DataType == System.Type.GetType("System.DateTime"))
35.      {
36.       excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
37.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
38.      }
39.      else
40.       if(col.DataType == System.Type.GetType("System.String"))
41.      {
42.       excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
43.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
44.      }
45.      else
46.      {
47.       excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
48.      }
49.     }
50.    }
51.    //
52.    //加载一个合计行
53.    //
54.    int rowSum = rowIndex + 1;
55.    int colSum = 2;
56.    excel.Cells[rowSum,2] = "合计";
57.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
58.    //
59.    //设置选中的部分的颜色
60.    //
61.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
62.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
63.    //
64.    //取得整个报表的标题
65.    //
66.    excel.Cells[2,2] = str;
67.    //
68.    //设置整个报表的标题格式
69.    //
70.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
71.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
72.    //
73.    //设置报表表格为最适应宽度
74.    //
75.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
76.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
77.    //
78.    //设置整个报表的标题为跨列居中
79.    //
80.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
81.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
82.    //
83.    //绘制边框
84.    //
85.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
86.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
87.    xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
88.    xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
89.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
90.    //
91.    //显示效果
92.    //
93.    excel.Visible=true;
94.     //xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
95.    xBk.SaveCopyAs(Server.MapPath(".")+""+this.xlfile.Text+".xls");
96.     ds = null;
97.             xBk.Close(false, null,null);
98.    
99.             excel.Quit();
100.             System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
101.             System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
102.     System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
103.             xBk = null;
104.             excel = null;
105.    xSt = null;
106.             GC.Collect();
107.    string path = Server.MapPath(this.xlfile.Text+".xls");
108.     System.IO.FileInfo file = new System.IO.FileInfo(path);
109.    Response.Clear();
110.    Response.Charset="GB2312";
111.    Response.ContentEncoding=System.Text.Encoding.UTF8;
112.    // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
113.    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
114.    // 添加头信息，指定文件大小，让浏览器能够显示下载进度
115.    Response.AddHeader("Content-Length", file.Length.ToString());
116.    
117.    // 指定返回的是一个不能被客户端读取的流，必须被下载
118.    Response.ContentType = "application/ms-excel";
119.    
120.    // 把文件流发送到客户端
121.    Response.WriteFile(file.FullName);
122.    // 停止页面的执行
123.   
124.    Response.End();
125. }

普通浏览复制代码保存代码打印代码

1. public void OutputExcel(DataView dv,string str)
2. {
3.    //dv为要输出到Excel的数据，str为标题名称
4.    GC.Collect();
5.    Application excel;// = new Application();
6.    int rowIndex=4;
7.    int colIndex=1;
8. 
9.     _Workbook xBk;
10.    _Worksheet xSt;
11. 
12.     excel= new ApplicationClass();
13.   
14.    xBk = excel.Workbooks.Add(true);
15.    
16.    xSt = (_Worksheet)xBk.ActiveSheet;
17. 
18.     //
19.    //取得标题
20.    //
21.    foreach(DataColumn col in dv.Table.Columns)
22.    {
23.     colIndex++;
24.     excel.Cells[4,colIndex] = col.ColumnName;
25.     xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
26.    }
27. 
28.     //
29.    //取得表格中的数据
30.    //
31.    foreach(DataRowView row in dv)
32.    {
33.     rowIndex ++;
34.     colIndex = 1;
35.     foreach(DataColumn col in dv.Table.Columns)
36.     {
37.      colIndex ++;
38.      if(col.DataType == System.Type.GetType("System.DateTime"))
39.      {
40.       excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
41.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
42.      }
43.      else
44.       if(col.DataType == System.Type.GetType("System.String"))
45.      {
46.       excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
47.       xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
48.      }
49.      else
50.      {
51.       excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
52.      }
53.     }
54.    }
55.    //
56.    //加载一个合计行
57.    //
58.    int rowSum = rowIndex + 1;
59.    int colSum = 2;
60.    excel.Cells[rowSum,2] = "合计";
61.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
62.    //
63.    //设置选中的部分的颜色
64.    //
65.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
66.    xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
67.    //
68.    //取得整个报表的标题
69.    //
70.    excel.Cells[2,2] = str;
71.    //
72.    //设置整个报表的标题格式
73.    //
74.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
75.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
76.    //
77.    //设置报表表格为最适应宽度
78.    //
79.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
80.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
81.    //
82.    //设置整个报表的标题为跨列居中
83.    //
84.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
85.    xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
86.    //
87.    //绘制边框
88.    //
89.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
90.    xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
91.    xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
92.    xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
93.    xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
94.    //
95.    //显示效果
96.    //
97.    excel.Visible=true;
98. 
99.     //xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
100.    xBk.SaveCopyAs(Server.MapPath(".")+""+this.xlfile.Text+".xls");
101. 
102.     ds = null;
103.             xBk.Close(false, null,null);
104.    
105.             excel.Quit();
106.             System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
107.             System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
108.     System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
109.             xBk = null;
110.             excel = null;
111.    xSt = null;
112.             GC.Collect();
113.    string path = Server.MapPath(this.xlfile.Text+".xls");
114. 
115.     System.IO.FileInfo file = new System.IO.FileInfo(path);
116.    Response.Clear();
117.    Response.Charset="GB2312";
118.    Response.ContentEncoding=System.Text.Encoding.UTF8;
119.    // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
120.    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
121.    // 添加头信息，指定文件大小，让浏览器能够显示下载进度
122.    Response.AddHeader("Content-Length", file.Length.ToString());
123.    
124.    // 指定返回的是一个不能被客户端读取的流，必须被下载
125.    Response.ContentType = "application/ms-excel";
126.    
127.    // 把文件流发送到客户端
128.    Response.WriteFile(file.FullName);
129.    // 停止页面的执行
130.   
131.    Response.End();
132. }

public void OutputExcel(DataView dv,string str) <br /> { <br /> //dv为要输出到Excel的数据，str为标题名称 <br /> GC.Collect(); <br /> Application excel;// = new Application(); <br /> int rowIndex=4; <br /> int colIndex=1; <br /> <br /> _Workbook xBk; <br /> _Worksheet xSt; <br /> <br /> excel= new ApplicationClass(); <br /> <br /> xBk = excel.Workbooks.Add(true); <br /> <br /> xSt = (_Worksheet)xBk.ActiveSheet; <br /> <br /> // <br /> //取得标题 <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[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 <br /> } <br /> <br /> // <br /> //取得表格中的数据 <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] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); <br /> xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 <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[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 <br /> } <br /> else <br /> { <br /> excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString(); <br /> } <br /> } <br /> } <br /> // <br /> //加载一个合计行 <br /> // <br /> int rowSum = rowIndex + 1; <br /> int colSum = 2; <br /> excel.Cells[rowSum,2] = "合计"; <br /> xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; <br /> // <br /> //设置选中的部分的颜色 <br /> // <br /> xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select(); <br /> xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种 <br /> // <br /> //取得整个报表的标题 <br /> // <br /> excel.Cells[2,2] = str; <br /> // <br /> //设置整个报表的标题格式 <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 /> //设置报表表格为最适应宽度 <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 /> //设置整个报表的标题为跨列居中 <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 = XlHAlign.xlHAlignCenterAcrossSelection; <br /> // <br /> //绘制边框 <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[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 <br /> xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗 <br /> xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 <br /> xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 <br /> // <br /> //显示效果 <br /> // <br /> excel.Visible=true; <br /> <br /> //xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML); <br /> xBk.SaveCopyAs(Server.MapPath(".")+""+this.xlfile.Text+".xls"); <br /> <br /> ds = null; <br /> xBk.Close(false, null,null); <br /> <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 /> <br /> System.IO.FileInfo file = new System.IO.FileInfo(path); <br /> Response.Clear(); <br /> Response.Charset="GB2312"; <br /> Response.ContentEncoding=System.Text.Encoding.UTF8; <br /> // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 <br /> Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); <br /> // 添加头信息，指定文件大小，让浏览器能够显示下载进度 <br /> Response.AddHeader("Content-Length", file.Length.ToString()); <br /> <br /> // 指定返回的是一个不能被客户端读取的流，必须被下载 <br /> Response.ContentType = "application/ms-excel"; <br /> <br /> // 把文件流发送到客户端 <br /> Response.WriteFile(file.FullName); <br /> // 停止页面的执行 <br /> <br /> Response.End(); <br /> }

   上面的方面，均将要导出的excel数据，直接给浏览器输出文件流，下面的方法是首先将其存到服务器的某个文件夹中，然后把文件发送到客户端。这样可以持久的把导出的文件存起来，以便实现其它功能。 5、将excel文件导出到服务器上，再下载。 二、winForm中导出Excel的方法： 1、方法1：   

1. SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
2.    SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
3.    DataSet ds=new DataSet();
4.    da.Fill(ds,"table1");
5.    DataTable dt=ds.Tables["table1"];
6.    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位随机数
7.    FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
8.    StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
9.    sw.WriteLine("自动编号,姓名,年龄");
10.    foreach(DataRow dr in dt.Rows)
11.    {
12.     sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
13.    }
14.    sw.Close();
15.    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
16.    Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
17.    Response.WriteFile(name); // 把文件流发送到客户端
18.    Response.End();
19. public void Out2Excel(string sTableName,string url)
20. {
21. Excel.Application oExcel=new Excel.Application();
22. Workbooks oBooks;
23. Workbook oBook;
24. Sheets oSheets;
25. Worksheet oSheet;
26. Range oCells;
27. string sFile="",sTemplate="";
28. //
29. System.Data.DataTable dt=TableOut(sTableName).Tables[0];
30. sFile=url+"myExcel.xls";
31. sTemplate=url+"MyTemplate.xls";
32. //
33. oExcel.Visible=false;
34. oExcel.DisplayAlerts=false;
35. //定义一个新的工作簿
36. oBooks=oExcel.Workbooks;
37. 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);
38. oBook=oBooks.get_Item(1);
39. oSheets=oBook.Worksheets;
40. oSheet=(Worksheet)oSheets.get_Item(1);
41. //命名该sheet
42. oSheet.Name="Sheet1";
43. oCells=oSheet.Cells;
44. //调用dumpdata过程，将数据导入到Excel中去
45. DumpData(dt,oCells);
46. //保存
47. oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
48. oBook.Close(false, Type.Missing,Type.Missing);
49. //退出Excel，并且释放调用的COM资源
50. oExcel.Quit();
51. GC.Collect();
52. KillProcess("Excel");
53. }
54. private void KillProcess(string processName)
55. {
56. System.Diagnostics.Process myproc= new System.Diagnostics.Process();
57. //得到所有打开的进程
58. try
59. {
60. foreach (Process thisproc in Process.GetProcessesByName(processName))
61. {
62. if(!thisproc.CloseMainWindow())
63. {
64. thisproc.Kill();
65. }
66. }
67. }
68. catch(Exception Exc)
69. {
70. throw new Exception("",Exc);
71. }
72. }

普通浏览复制代码保存代码打印代码

1. SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
2.    SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
3.    DataSet ds=new DataSet();
4.    da.Fill(ds,"table1");
5.    DataTable dt=ds.Tables["table1"];
6.    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位随机数
7.    FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
8.    StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
9.    sw.WriteLine("自动编号,姓名,年龄");
10.    foreach(DataRow dr in dt.Rows)
11.    {
12.     sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
13.    }
14.    sw.Close();
15.    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
16.    Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
17.    Response.WriteFile(name); // 把文件流发送到客户端
18.    Response.End();
19. public void Out2Excel(string sTableName,string url)
20. {
21. Excel.Application oExcel=new Excel.Application();
22. Workbooks oBooks;
23. Workbook oBook;
24. Sheets oSheets;
25. Worksheet oSheet;
26. Range oCells;
27. string sFile="",sTemplate="";
28. //
29. System.Data.DataTable dt=TableOut(sTableName).Tables[0];
30. 
31. sFile=url+"myExcel.xls";
32. sTemplate=url+"MyTemplate.xls";
33. //
34. oExcel.Visible=false;
35. oExcel.DisplayAlerts=false;
36. //定义一个新的工作簿
37. oBooks=oExcel.Workbooks;
38. 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);
39. oBook=oBooks.get_Item(1);
40. oSheets=oBook.Worksheets;
41. oSheet=(Worksheet)oSheets.get_Item(1);
42. //命名该sheet
43. oSheet.Name="Sheet1";
44. 
45. oCells=oSheet.Cells;
46. //调用dumpdata过程，将数据导入到Excel中去
47. DumpData(dt,oCells);
48. //保存
49. oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
50. oBook.Close(false, Type.Missing,Type.Missing);
51. //退出Excel，并且释放调用的COM资源
52. oExcel.Quit();
53. 
54. GC.Collect();
55. KillProcess("Excel");
56. }
57. 
58. private void KillProcess(string processName)
59. {
60. System.Diagnostics.Process myproc= new System.Diagnostics.Process();
61. //得到所有打开的进程
62. try
63. {
64. foreach (Process thisproc in Process.GetProcessesByName(processName))
65. {
66. if(!thisproc.CloseMainWindow())
67. {
68. thisproc.Kill();
69. }
70. }
71. }
72. catch(Exception Exc)
73. {
74. throw new Exception("",Exc);
75. }
76. }

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); <br /> SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); <br /> DataSet ds=new DataSet(); <br /> da.Fill(ds,"table1"); <br /> DataTable dt=ds.Tables["table1"]; <br /> 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位随机数 <br /> FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); <br /> StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); <br /> sw.WriteLine("自动编号,姓名,年龄"); <br /> foreach(DataRow dr in dt.Rows) <br /> { <br /> sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); <br /> } <br /> sw.Close(); <br /> Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); <br /> Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载 <br /> Response.WriteFile(name); // 把文件流发送到客户端 <br /> Response.End();<br /> public void Out2Excel(string sTableName,string url)<br /> {<br /> Excel.Application oExcel=new Excel.Application();<br /> Workbooks oBooks;<br /> Workbook oBook;<br /> Sheets oSheets;<br /> Worksheet oSheet;<br /> Range oCells;<br /> string sFile="",sTemplate="";<br /> //<br /> System.Data.DataTable dt=TableOut(sTableName).Tables[0];<br /> <br /> sFile=url+"myExcel.xls";<br /> sTemplate=url+"MyTemplate.xls";<br /> //<br /> oExcel.Visible=false;<br /> oExcel.DisplayAlerts=false;<br /> //定义一个新的工作簿<br /> oBooks=oExcel.Workbooks;<br /> 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);<br /> oBook=oBooks.get_Item(1);<br /> oSheets=oBook.Worksheets;<br /> oSheet=(Worksheet)oSheets.get_Item(1);<br /> //命名该sheet<br /> oSheet.Name="Sheet1";<br /> <br /> oCells=oSheet.Cells;<br /> //调用dumpdata过程，将数据导入到Excel中去<br /> DumpData(dt,oCells);<br /> //保存<br /> oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);<br /> oBook.Close(false, Type.Missing,Type.Missing);<br /> //退出Excel，并且释放调用的COM资源<br /> oExcel.Quit();<br /> <br /> GC.Collect();<br /> KillProcess("Excel");<br /> }<br /> <br /> private void KillProcess(string processName)<br /> {<br /> System.Diagnostics.Process myproc= new System.Diagnostics.Process();<br /> //得到所有打开的进程<br /> try<br /> {<br /> foreach (Process thisproc in Process.GetProcessesByName(processName))<br /> {<br /> if(!thisproc.CloseMainWindow())<br /> {<br /> thisproc.Kill();<br /> }<br /> }<br /> }<br /> catch(Exception Exc)<br /> {<br /> throw new Exception("",Exc);<br /> }<br /> }

2、方法2：

1. protected void ExportExcel()
2.    {
3.     gridbind();
4.    if(ds1==null) return;
5.  
6.    string saveFileName="";
7. //   bool fileSaved=false;
8.     SaveFileDialog saveDialog=new SaveFileDialog();
9.     saveDialog.DefaultExt ="xls";
10.     saveDialog.Filter="Excel文件|*.xls";
11.     saveDialog.FileName ="Sheet1";
12.     saveDialog.ShowDialog();
13.     saveFileName=saveDialog.FileName;
14.     if(saveFileName.IndexOf(":")<0) return; //被点了取消
15. //   excelapp.Workbooks.Open   (App.path & 工程进度表.xls)
16.   
17.    Excel.Application xlApp=new Excel.Application();
18.     object missing=System.Reflection.Missing.Value;
19.  
20.    if(xlApp==null)
21.     {
22.      MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
23.      return;
24.     }
25.     Excel.Workbooks workbooks=xlApp.Workbooks;
26.     Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
27.     Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
28.     Excel.Range range;
29.     
30.  
31.    string oldCaption=Title_label .Text.Trim ();
32.     long totalCount=ds1.Tables[0].Rows.Count;
33.     long rowRead=0;
34.     float percent=0;
35.  
36.    worksheet.Cells[1,1]=Title_label .Text.Trim ();
37.     //写入字段
38.     for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
39.     {
40.      worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; 
41.     range=(Excel.Range)worksheet.Cells[2,i+1];
42.      range.Interior.ColorIndex = 15;
43.      range.Font.Bold = true;
44.   
45.    }
46.     //写入数值
47.     Caption .Visible = true;
48.     for(int r=0;r<ds1.Tables[0].Rows.Count;r++)
49.     {
50.      for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
51.      {
52.       worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r];    
53.     }
54.      rowRead++;
55.      percent=((float)(100*rowRead))/totalCount;   
56.     this.Caption.Text= "正在导出数据["+ percent.ToString("0.00")  +"%]...";
57.      Application.DoEvents();
58.     }
59.     worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
60.    
61.     this.Caption.Visible= false;
62.     this.Caption.Text= oldCaption;
63.  
64.    range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]);
65.     range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
66.    
67.    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
68.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
69.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
70.  
71.    if(ds1.Tables[0].Columns.Count>1)
72.     {
73.      range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
74.      }
75.     workbook.Close(missing,missing,missing);
76.     xlApp.Quit();
77.    }

普通浏览复制代码保存代码打印代码

1. protected void ExportExcel()
2.    {
3.     gridbind();
4.    if(ds1==null) return;
5.  
6.    string saveFileName="";
7. //   bool fileSaved=false;
8.     SaveFileDialog saveDialog=new SaveFileDialog();
9.     saveDialog.DefaultExt ="xls";
10.     saveDialog.Filter="Excel文件|*.xls";
11.     saveDialog.FileName ="Sheet1";
12.     saveDialog.ShowDialog();
13.     saveFileName=saveDialog.FileName;
14.     if(saveFileName.IndexOf(":")<0) return; //被点了取消
15. //   excelapp.Workbooks.Open   (App.path & 工程进度表.xls)
16.   
17.    Excel.Application xlApp=new Excel.Application();
18.     object missing=System.Reflection.Missing.Value;
19.  
20. 
21.    if(xlApp==null)
22.     {
23.      MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
24.      return;
25.     }
26.     Excel.Workbooks workbooks=xlApp.Workbooks;
27.     Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
28.     Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
29.     Excel.Range range;
30.     
31.  
32.    string oldCaption=Title_label .Text.Trim ();
33.     long totalCount=ds1.Tables[0].Rows.Count;
34.     long rowRead=0;
35.     float percent=0;
36.  
37.    worksheet.Cells[1,1]=Title_label .Text.Trim ();
38.     //写入字段
39.     for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
40.     {
41.      worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; 
42.     range=(Excel.Range)worksheet.Cells[2,i+1];
43.      range.Interior.ColorIndex = 15;
44.      range.Font.Bold = true;
45.   
46.    }
47.     //写入数值
48.     Caption .Visible = true;
49.     for(int r=0;r<ds1.Tables[0].Rows.Count;r++)
50.     {
51.      for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
52.      {
53.       worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r];    
54.     }
55.      rowRead++;
56.      percent=((float)(100*rowRead))/totalCount;   
57.     this.Caption.Text= "正在导出数据["+ percent.ToString("0.00")  +"%]...";
58.      Application.DoEvents();
59.     }
60.     worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
61.    
62.     this.Caption.Visible= false;
63.     this.Caption.Text= oldCaption;
64.  
65.    range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]);
66.     range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
67.    
68.    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
69.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
70.     range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
71.  
72.    if(ds1.Tables[0].Columns.Count>1)
73.     {
74.      range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
75.      }
76.     workbook.Close(missing,missing,missing);
77.     xlApp.Quit();
78.    }

protected void ExportExcel()<br /> {<br /> gridbind(); <br /> if(ds1==null) return; <br /> <br /> string saveFileName="";<br /> // bool fileSaved=false;<br /> SaveFileDialog saveDialog=new SaveFileDialog();<br /> saveDialog.DefaultExt ="xls";<br /> saveDialog.Filter="Excel文件|*.xls";<br /> saveDialog.FileName ="Sheet1";<br /> saveDialog.ShowDialog();<br /> saveFileName=saveDialog.FileName;<br /> if(saveFileName.IndexOf(":")<0) return; //被点了取消<br /> // excelapp.Workbooks.Open (App.path & 工程进度表.xls) <br /> <br /> Excel.Application xlApp=new Excel.Application();<br /> object missing=System.Reflection.Missing.Value; <br /> <br /> <br /> if(xlApp==null)<br /> {<br /> MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");<br /> return;<br /> }<br /> Excel.Workbooks workbooks=xlApp.Workbooks;<br /> Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);<br /> Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1<br /> Excel.Range range;<br /> <br /> <br /> string oldCaption=Title_label .Text.Trim ();<br /> long totalCount=ds1.Tables[0].Rows.Count;<br /> long rowRead=0;<br /> float percent=0; <br /> <br /> worksheet.Cells[1,1]=Title_label .Text.Trim ();<br /> //写入字段<br /> for(int i=0;i<ds1.Tables[0].Columns.Count;i++)<br /> {<br /> worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; <br /> range=(Excel.Range)worksheet.Cells[2,i+1];<br /> range.Interior.ColorIndex = 15;<br /> range.Font.Bold = true;<br /> <br /> }<br /> //写入数值<br /> Caption .Visible = true;<br /> for(int r=0;r<ds1.Tables[0].Rows.Count;r++)<br /> {<br /> for(int i=0;i<ds1.Tables[0].Columns.Count;i++)<br /> {<br /> worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r]; <br /> }<br /> rowRead++;<br /> percent=((float)(100*rowRead))/totalCount; <br /> this.Caption.Text= "正在导出数据["+ percent.ToString("0.00") +"%]...";<br /> Application.DoEvents();<br /> }<br /> worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);<br /> <br /> this.Caption.Visible= false;<br /> this.Caption.Text= oldCaption; <br /> <br /> range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]);<br /> range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);<br /> <br /> range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;<br /> range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;<br /> range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; <br /> <br /> if(ds1.Tables[0].Columns.Count>1)<br /> {<br /> range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;<br /> }<br /> workbook.Close(missing,missing,missing);<br /> xlApp.Quit();<br /> }

三、附注： 虽然都是实现导出excel的功能，但在asp.net和winform的程序中，实现的代码是各不相同的。在asp.net中，是在服务器端读取数据，在服务器端把数据以ms-excel的格式，以Response输出到浏览器（客户端)；而在winform中，是把数据读到客户端（因为winform运行端就是客户端），然后调用客户端安装的office组件，将读到的数据写在excel