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

 

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

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

C#数据库数据导入导出系列之一 ASP.NET Excel导入Sql Server数据库

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

C#数据库数据导入导出系列之三 数据库导出到Excel下

C#数据库数据导入导出系列之四 WinForm数据库导入导出到Excel

（注意 这里四篇文章只是基础的方法，若有更高的要求，可以参考

http://www.cnblogs.com/atao/archive/2009/11/15/1603528.html

http://www.cnblogs.com/tonyqus/category/182110.html 

http://www.yongfa365.com/Item/NPOI-MyXls-DataTable-To-Excel-From-Excel.html .net 通过NPOI或MyXls把DataTable导出到Excel

）

1，使用DataGird生成Excel

       基本思想：

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

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

       代码：

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

具体实现

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

        注意事项：

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

        public override void VerifyRenderingInServerForm(Control control)        {            //base.VerifyRenderingInServerForm(control);        }

              （2）在UpdatePanel的Triggers节点下注册按钮

        <Triggers>            <asp:PostBackTrigger ControlID="ibtnExport" />        </Triggers>

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

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI.WebControls;using System.Web.UI;using System.Data;using System.Text;using System.Globalization;using System.IO;namespace VMS.Test.Classes{    public class ExcelHelper {         #region Fields          string _fileName;         DataTable _dataSource;                 string[] _titles = null;         string[] _fields = null;         int _maxRecords = 1000;          #endregion          #region Properties          /**//// <summary>         /// 限制输出到 Excel 的最大记录数。超出则抛出异常         /// </summary>         public int MaxRecords {             set { _maxRecords = value; }             get { return _maxRecords; }         }          /**//// <summary>         /// 输出到浏览器的 Excel 文件名         /// </summary>         public string FileName {             set { _fileName = value; }             get { return _fileName; }         }          #endregion          #region .ctor          /**//// <summary>         /// 构造函数         /// </summary>         /// <param name="titles">要输出到 Excel 的列标题的数组</param>         /// <param name="fields">要输出到 Excel 的字段名称数组</param>         /// <param name="dataSource">数据源</param>         public ExcelHelper(string[] titles, string[] fields, DataTable dataSource): this(titles, dataSource)        {             if (fields == null || fields.Length == 0)                 throw new ArgumentNullException("fields");              if (titles.Length != fields.Length)                 throw new ArgumentException("titles.Length != fields.Length", "fields");                          _fields = fields;                     }          /**//// <summary>         /// 构造函数         /// </summary>         /// <param name="titles">要输出到 Excel 的列标题的数组</param>         /// <param name="dataSource">数据源</param>         public ExcelHelper(string[] titles, DataTable dataSource): this(dataSource) {             if (titles == null || titles.Length == 0)                 throw new ArgumentNullException("titles");             //if (titles.Length != dataSource.Columns.Count)             //    throw new ArgumentException("titles.Length != dataSource.Columns.Count", "dataSource");              _titles = titles;                     }          /**//// <summary>         /// 构造函数         /// </summary>         /// <param name="dataSource">数据源</param>         public ExcelHelper(DataTable dataSource) {             if (dataSource == null)                 throw new ArgumentNullException("dataSource");             // maybe more checks needed here (IEnumerable, IList, IListSource, ) ???             // 很难判断，先简单的使用 DataTable              _dataSource = dataSource;         }                  public ExcelHelper() {}          #endregion                  #region public Methods                  /**//// <summary>         /// 导出到 Excel 并提示下载         /// </summary>         /// <param name="dg">DataGrid</param>         public void Export(DataGrid dg) {             if (dg == null)                 throw new ArgumentNullException("dg");             if (dg.AllowPaging || dg.PageCount > 1)                 throw new ArgumentException("paged DataGrid can't be exported.", "dg");              // 添加标题样式             dg.HeaderStyle.Font.Bold = true;             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;              RenderExcel(dg);         }          ///**//// <summary>         ///// 导出到 Excel 并提示下载         ///// </summary>         ///// <param name="xgrid">ASPxGrid</param>         //public void Export(DataGrid xgrid) {          //    if (xgrid == null)         //        throw new ArgumentNullException("xgrid");         //    if (xgrid.PageCount > 1)         //        throw new ArgumentException("paged xgird not can't be exported.", "xgrid");          //    // 添加标题样式         //    xgrid.HeaderStyle.Font.Bold = true;         //    xgrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;          //    RenderExcel(xgrid);         //}          /**//// <summary>         /// 导出到 Excel 并提示下载         /// </summary>         public void Export() {             if (_dataSource == null)                 throw new Exception("数据源尚未初始化");              if (_fields == null && _titles != null && _titles.Length != _dataSource.Columns.Count)                  throw new Exception("_titles.Length != _dataSource.Columns.Count");                          if (_dataSource.Rows.Count > _maxRecords)                 throw new Exception("导出数据条数超过限制。请设置 MaxRecords 属性以定义导出的最多记录数。");              DataGrid dg = new DataGrid();             dg.DataSource = _dataSource;              if (_titles == null) {                 dg.AutoGenerateColumns = true;             }              else {                 dg.AutoGenerateColumns = false;                 int cnt = _titles.Length;                  System.Web.UI.WebControls.BoundColumn col;                  if (_fields == null) {                     for (int i=0; i<cnt; i++) {                         col = new System.Web.UI.WebControls.BoundColumn();                         col.HeaderText = _titles[i];                         col.DataField = _dataSource.Columns[i].ColumnName;                         dg.Columns.Add(col);                     }                 }                 else {                     for (int i=0; i<cnt; i++) {                         col = new System.Web.UI.WebControls.BoundColumn();                         col.HeaderText = _titles[i];                         col.DataField = _fields[i];                         dg.Columns.Add(col);                     }                 }             }              // 添加标题样式             dg.HeaderStyle.Font.Bold = true;             dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;             dg.ItemDataBound += new DataGridItemEventHandler(DataGridItemDataBound);              dg.DataBind();             RenderExcel(dg);         }          #endregion          #region private Methods                  private void RenderExcel(Control c) {             // 确保有一个合法的输出文件名             if (_fileName == null || _fileName == string.Empty || !(_fileName.ToLower().EndsWith(".xls")))                 _fileName = GetRandomFileName();              HttpResponse response = HttpContext.Current.Response;                          response.Charset = "GB2312";             response.ContentEncoding = Encoding.GetEncoding("GB2312");             response.ContentType = "application/ms-excel/msword";             response.AppendHeader("Content-Disposition", "attachment;filename=" +                  HttpUtility.UrlEncode(_fileName));              CultureInfo cult = new CultureInfo("zh-CN", true);             StringWriter sw = new StringWriter(cult);                         HtmlTextWriter writer = new HtmlTextWriter(sw);              writer.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">");              DataGrid dg = c as DataGrid;                          if (dg != null) {                 dg.RenderControl(writer);             }             else {                DataGrid xgrid = c as DataGrid;                  if (xgrid != null)                     xgrid.RenderControl(writer);                 else                     throw new ArgumentException("only supports DataGrid or ASPxGrid.", "c");                 }             c.Dispose();              response.Write(sw.ToString());             response.End();         }           /**//// <summary>         /// 得到一个随意的文件名         /// </summary>         /// <returns></returns>         private string GetRandomFileName() {             Random rnd = new Random((int) (DateTime.Now.Ticks));             string s = rnd.Next(Int32.MaxValue).ToString();             return DateTime.Now.ToShortDateString() + "_" + s + ".xls";         }          private void DataGridItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) {             if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) {                 e.Item.Attributes.Add("style", "vnd.ms-excel.numberformat:@");                 //e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");             }         }         #endregion     } }