C#数据库数据导入导出系列之二数据库导出到Excel上

在日常的项目中，Excel，Word，txt等格式的数据导入到数据库中是很常见的，我在这里做一下总结

这里将分为Asp.net导入Sql Server，Oracle数据库和WinForm导入Sql Server，Oracle数据库。

1，使用DataGird生成Excel

基本思想：

（1）将数据从数据库中查询出来，绑定到DataGrid控件中，这个DataGirdle控件知识作为数据的一个承载，不需要显示在页面中

（2）使用StringWriter将DataGrid读出来，在使用Response的另存为功能，将html页存为Xls格式的Excel文件。

代码：

1 //导出按钮
2 protected void ibtnExport_Click(object sender, ImageClickEventArgs e)
3 {
4     ExportDataGrid("application/ms-excel", "test.xls"); //导到Excel
5 }

具体实现

View Code

 1 #region 使用DataGrid生成Excel
 2         /// <summary>
 3         /// 使用DataGrid生成Excel
 4         /// </summary>
 5         /// <param name="FileType">文件类型 MIME类型</param>
 6         /// <param name="FileName">文件名</param>
 7         private void ExportDataGrid(string FileType, string FileName) //从DataGrid导出
 8         {
 9             System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
10 
11             //这里使用的是IBatis与数据库通信，大家可以使用ADO或者别的方式查询数据
12             dg.DataSource = Helper.ContactExport().ExportDataIntoExcel();
13             dg.DataBind();
14 
15             //定义文档类型、字符编码　　 
16             Response.Clear();
17             Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
18             Response.Charset = "UTF-8";
19             Response.ContentEncoding = Encoding.Default;
20             Response.ContentType = FileType;
21             dg.EnableViewState = false;
22             //定义一个输入流　　 
23             StringWriter tw = new StringWriter();
24             HtmlTextWriter hw = new HtmlTextWriter(tw);
25             //目标数据绑定到输入流输出　
26             dg.RenderControl(hw);
27             //GvContract 绑定datagrid,或其他支持obj.RenderControl()属性的控件　　 
28             //ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "info", tw.ToString(), false);
29             Response.Write(tw.ToString());
30             Response.End();
31         }
32         #endregion

注意事项：

（1）由于我的页面中有Ajax的UpdatePanel控件，所以在代码中需要加入如下代码：

1         <Triggers>
2             <asp:PostBackTrigger ControlID="ibtnExport" />
3         </Triggers>

下面给出一个在网上下载的一个已经封装好的类

View Code

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI.WebControls;
  6 using System.Web.UI;
  7 using System.Data;
  8 using System.Text;
  9 using System.Globalization;
 10 using System.IO;
 11 
 12 namespace VMS.Test.Classes
 13 {
 14     public class ExcelHelper { 
 15 
 16         #region Fields 
 17  
 18         string _fileName; 
 19         DataTable _dataSource;         
 20         string[] _titles = null; 
 21         string[] _fields = null; 
 22         int _maxRecords = 1000; 
 23  
 24         #endregion 
 25  
 26         #region Properties 
 27  
 28         /**//// <summary> 
 29         /// 限制输出到 Excel 的最大记录数。超出则抛出异常 
 30         /// </summary> 
 31         public int MaxRecords { 
 32             set { _maxRecords = value; } 
 33             get { return _maxRecords; } 
 34         } 
 35  
 36         /**//// <summary> 
 37         /// 输出到浏览器的 Excel 文件名 
 38         /// </summary> 
 39         public string FileName { 
 40             set { _fileName = value; } 
 41             get { return _fileName; } 
 42         } 
 43  
 44         #endregion 
 45  
 46         #region .ctor 
 47  
 48         /**//// <summary> 
 49         /// 构造函数 
 50         /// </summary> 
 51         /// <param name="titles">要输出到 Excel 的列标题的数组</param> 
 52         /// <param name="fields">要输出到 Excel 的字段名称数组</param> 
 53         /// <param name="dataSource">数据源</param> 
 54         public ExcelHelper(string[] titles, string[] fields, DataTable dataSource): this(titles, dataSource)        { 
 55             if (fields == null || fields.Length == 0) 
 56                 throw new ArgumentNullException("fields"); 
 57  
 58             if (titles.Length != fields.Length) 
 59                 throw new ArgumentException("titles.Length != fields.Length", "fields"); 
 60              
 61             _fields = fields;             
 62         } 
 63  
 64         /**//// <summary> 
 65         /// 构造函数 
 66         /// </summary> 
 67         /// <param name="titles">要输出到 Excel 的列标题的数组</param> 
 68         /// <param name="dataSource">数据源</param> 
 69         public ExcelHelper(string[] titles, DataTable dataSource): this(dataSource) { 
 70             if (titles == null || titles.Length == 0) 
 71                 throw new ArgumentNullException("titles"); 
 72             //if (titles.Length != dataSource.Columns.Count) 
 73             //    throw new ArgumentException("titles.Length != dataSource.Columns.Count", "dataSource"); 
 74  
 75             _titles = titles;             
 76         } 
 77  
 78         /**//// <summary> 
 79         /// 构造函数 
 80         /// </summary> 
 81         /// <param name="dataSource">数据源</param> 
 82         public ExcelHelper(DataTable dataSource) { 
 83             if (dataSource == null) 
 84                 throw new ArgumentNullException("dataSource"); 
 85             // maybe more checks needed here (IEnumerable, IList, IListSource, ) ??? 
 86             // 很难判断，先简单的使用 DataTable 
 87  
 88             _dataSource = dataSource; 
 89         } 
 90          
 91         public ExcelHelper() {} 
 92  
 93         #endregion 
 94          
 95         #region public Methods 
 96          
 97         /**//// <summary> 
 98         /// 导出到 Excel 并提示下载 
 99         /// </summary> 
100         /// <param name="dg">DataGrid</param> 
101         public void Export(DataGrid dg) { 
102             if (dg == null) 
103                 throw new ArgumentNullException("dg"); 
104             if (dg.AllowPaging || dg.PageCount > 1) 
105                 throw new ArgumentException("paged DataGrid can't be exported.", "dg"); 
106  
107             // 添加标题样式 
108             dg.HeaderStyle.Font.Bold = true; 
109             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray; 
110  
111             RenderExcel(dg); 
112         } 
113  
114         ///**//// <summary> 
115         ///// 导出到 Excel 并提示下载 
116         ///// </summary> 
117         ///// <param name="xgrid">ASPxGrid</param> 
118         //public void Export(DataGrid xgrid) {  
119         //    if (xgrid == null) 
120         //        throw new ArgumentNullException("xgrid"); 
121         //    if (xgrid.PageCount > 1) 
122         //        throw new ArgumentException("paged xgird not can't be exported.", "xgrid"); 
123  
124         //    // 添加标题样式 
125         //    xgrid.HeaderStyle.Font.Bold = true; 
126         //    xgrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray; 
127  
128         //    RenderExcel(xgrid); 
129         //} 
130  
131         /**//// <summary> 
132         /// 导出到 Excel 并提示下载 
133         /// </summary> 
134         public void Export() { 
135             if (_dataSource == null) 
136                 throw new Exception("数据源尚未初始化"); 
137  
138             if (_fields == null && _titles != null && _titles.Length != _dataSource.Columns.Count)  
139                 throw new Exception("_titles.Length != _dataSource.Columns.Count"); 
140              
141             if (_dataSource.Rows.Count > _maxRecords) 
142                 throw new Exception("导出数据条数超过限制。请设置 MaxRecords 属性以定义导出的最多记录数。"); 
143  
144             DataGrid dg = new DataGrid(); 
145             dg.DataSource = _dataSource; 
146  
147             if (_titles == null) { 
148                 dg.AutoGenerateColumns = true; 
149             }  
150             else { 
151                 dg.AutoGenerateColumns = false; 
152                 int cnt = _titles.Length; 
153  
154                 System.Web.UI.WebControls.BoundColumn col; 
155  
156                 if (_fields == null) { 
157                     for (int i=0; i<cnt; i++) { 
158                         col = new System.Web.UI.WebControls.BoundColumn(); 
159                         col.HeaderText = _titles[i]; 
160                         col.DataField = _dataSource.Columns[i].ColumnName; 
161                         dg.Columns.Add(col); 
162                     } 
163                 } 
164                 else { 
165                     for (int i=0; i<cnt; i++) { 
166                         col = new System.Web.UI.WebControls.BoundColumn(); 
167                         col.HeaderText = _titles[i]; 
168                         col.DataField = _fields[i]; 
169                         dg.Columns.Add(col); 
170                     } 
171                 } 
172             } 
173  
174             // 添加标题样式 
175             dg.HeaderStyle.Font.Bold = true; 
176             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray; 
177             dg.ItemDataBound += new DataGridItemEventHandler(DataGridItemDataBound); 
178  
179             dg.DataBind(); 
180             RenderExcel(dg); 
181         } 
182  
183         #endregion 
184  
185         #region private Methods 
186          
187         private void RenderExcel(Control c) { 
188             // 确保有一个合法的输出文件名 
189             if (_fileName == null || _fileName == string.Empty || !(_fileName.ToLower().EndsWith(".xls"))) 
190                 _fileName = GetRandomFileName(); 
191  
192             HttpResponse response = HttpContext.Current.Response; 
193              
194             response.Charset = "GB2312"; 
195             response.ContentEncoding = Encoding.GetEncoding("GB2312"); 
196             response.ContentType = "application/ms-excel/msword"; 
197             response.AppendHeader("Content-Disposition", "attachment;filename=" +  
198                 HttpUtility.UrlEncode(_fileName)); 
199  
200             CultureInfo cult = new CultureInfo("zh-CN", true); 
201             StringWriter sw = new StringWriter(cult);             
202             HtmlTextWriter writer = new HtmlTextWriter(sw); 
203  
204             writer.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">"); 
205  
206             DataGrid dg = c as DataGrid; 
207              
208             if (dg != null) { 
209                 dg.RenderControl(writer); 
210             } 
211             else {
212                 DataGrid xgrid = c as DataGrid; 
213  
214                 if (xgrid != null) 
215                     xgrid.RenderControl(writer); 
216                 else 
217                     throw new ArgumentException("only supports DataGrid or ASPxGrid.", "c");     
218             } 
219             c.Dispose(); 
220  
221             response.Write(sw.ToString()); 
222             response.End(); 
223         } 
224  
225  
226         /**//// <summary> 
227         /// 得到一个随意的文件名 
228         /// </summary> 
229         /// <returns></returns> 
230         private string GetRandomFileName() { 
231             Random rnd = new Random((int) (DateTime.Now.Ticks)); 
232             string s = rnd.Next(Int32.MaxValue).ToString(); 
233             return DateTime.Now.ToShortDateString() + "_" + s + ".xls"; 
234         } 
235  
236         private void DataGridItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { 
237             if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { 
238                 e.Item.Attributes.Add("style", "vnd.ms-excel.numberformat:@"); 
239                 //e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00"); 
240             } 
241         } 
242         #endregion 
243     } 
244 }

C#数据库数据导入导出系列之二 数据库导出到Excel上

C#数据库数据导入导出系列之二数据库导出到Excel上