7in10笔记：.net导出excel（无需office控件) - 十分之七

公告

无需office控件导出excel报表。DatagridToExcel导出和datagrid一样的样式。dataset则可以有也可以没有。
还有个配置文件和例子代码！
using System;。
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

    /// <summary>
    /// DatagridToExcel 的摘要说明。
    /// </summary>
    public class DatagridToExcel
    {
        Page page;
        HttpResponse Response;

        public DatagridToExcel(Page page)
        {
            this.page = page;
            this.Response = page.Response;
        }

        private void ClearControls(Control control)
        {
            for (int i = control.Controls.Count - 1; i >= 0; i--)
            {
                ClearControls(control.Controls[i]);
            }

            if (!(control is TableCell))
            {
                if (control.GetType().GetProperty("SelectedItem") != null)
                {
                    LiteralControl literal = new LiteralControl();
                    control.Parent.Controls.Add(literal);
                    try
                    {
                        literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
                    }
                    catch
                    {
                    }
                    control.Parent.Controls.Remove(control);
                }
                else if (control.GetType().GetProperty("Text") != null)
                {
                    LiteralControl literal = new LiteralControl();
                    control.Parent.Controls.Add(literal);
                    literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
                    control.Parent.Controls.Remove(control);
                }
            }
            return;
        }

        public void ExpertToExcel(DataGrid datagrid, string filename)
        {
            Response.Clear();
            Response.Buffer = false;
            Response.Charset = "utf-8"; // 非常重要
            Response.ContentEncoding = Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            //   Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
            page.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

this.ClearControls(datagrid);
datagrid.RenderControl(oHtmlTextWriter);

            Response.Write(oStringWriter.ToString());
            Response.End();
        }
    }
    public class ColumnInfo
    {
        public ColumnInfo(string fieldName)
            : this(fieldName, fieldName)
        {
        }

        public ColumnInfo(string fieldName, string headerText)
            : this(fieldName, headerText, "{0}")
        {
        }

        public ColumnInfo(string fieldName, string headerText, string dataFormat)
        {
            this._fieldName = fieldName;
            this._headerText = headerText;
            //this._dataFormat = StringUtil.IsNullOrEmpty(dataFormat) ? "{0}" : dataFormat;
            string format="";
            if (dataFormat.Trim() == string.Empty)
            {
                format="{0}";
            }
            else
            {
                format = dataFormat;
            }
            this._dataFormat = format;
        }

        string _fieldName;
        public string FieldName
        {
            get { return this._fieldName; }
        }

        string _headerText;
        public string HeaderText
        {
            get { return this._headerText; }
        }

        string _dataFormat = "{0}";
        public string DataFormat
        {
            get { return this._dataFormat; }
        }
    }

    /// <summary>
    /// DatasetToExcel 的摘要说明。
    /// </summary>
    public class DatasetToExcel
    {
        Page page;
        HttpResponse Response;

        public DatasetToExcel(Page page)
        {
            this.page = page;
            this.Response = page.Response;
        }

        public string GetExcelString(DataSet dataset, Hashtable columnInfos)
        {
            return this.GetExcelString(dataset.Tables[0], columnInfos);
        }

        public string GetExcelString(DataTable dt, Hashtable columnInfos)
        {
            // 获取Excel文档格式模版
            StreamReader sr = File.OpenText(page.Server.MapPath("\\xlsFormat.config"));
            StringBuilder xls = new StringBuilder(sr.ReadToEnd());
            sr.Close();

StringBuilder sb = new StringBuilder();

// 表
sb.AppendFormat("<table x:str border=0 cellpadding=0 cellspacing=0 width={0} style='border-collapse:collapse;table-layout:fixed;width:{1}pt'>", 72 * columnInfos.Count, 54 * columnInfos.Count);

            // 表头，没有在columnInfos中的列不被导出。
            sb.Append("\r\n\t<tr height=19 style='height:14.25pt'>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (columnInfos.Contains(dt.Columns[i].ColumnName))
                    sb.AppendFormat("\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>{0}</td>", ((ColumnInfo)columnInfos[dt.Columns[i].ColumnName]).HeaderText);
            }
            sb.Append("\r\n\t</tr>");

            // 表体，没有在columnInfos中的列不被导出。
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                // 行
                sb.Append("\r\n\t<tr height=19 style='height:14.25pt'>");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!columnInfos.Contains(dt.Columns[j].ColumnName)) continue;

ColumnInfo info = (ColumnInfo)columnInfos[dt.Columns[j].ColumnName];
string colType = dt.Columns[j].DataType.ToString().ToLower();

                    // 列
                    string format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>{＄DataFormat＄}</td>";
                    switch (colType)
                    {
                        case "system.datetime":
                            format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xShortDate align=right>{＄DataFormat＄}</td>";
                            break;
                        case "system.decimal":
                        case "system.double":
                        case "system.int32":
                        case "system.int64":
                            format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xNumeric align=right x:num='{0}'>{＄DataFormat＄}</td>";
                            break;
                        case "system.string":
                            format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xText>{＄DataFormat＄}</td>";
                            break;
                        default:
                            format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>" + colType + ":{0}</td>";
                            break;
                    }
                    // 根据columnInfos中定义的数据显示格式重新格式化导出的数据
                    format = format.Replace("{＄DataFormat＄}", info.DataFormat);
                    sb.AppendFormat(format, dt.Rows[i][j]);
                }
                // 行结束
                sb.Append("\r\n\t</tr>");
            }
            // 表结束
            sb.Append("\r\n</table>");

            // 最终的Excel数据
            xls.Replace("{＄Author＄}", this.ToString());
            xls.Replace("{＄LastAuthor＄}", this.ToString());
            xls.Replace("{＄Created＄}", DateTime.Now.ToLongDateString());
            xls.Replace("{＄LastSaved＄}", DateTime.Now.ToString("U"));
            xls.Replace("{＄Company＄}", "NSS");
            xls.Replace("{＄Body＄}", sb.ToString());

return xls.ToString();
}

        /// <summary>
        /// 根据DataTable以及对应的DataGrid构造列标题和数据显示格式。
        /// </summary>
        /// <remarks>
        /// 只能处理BoundColumn，并且Visible=true
        /// </remarks>
        /// <param name="dt"></param>
        /// <param name="grid"></param>
        /// <returns></returns>
        public Hashtable SetColumnInfo(DataTable dt, DataGrid grid)
        {
            Hashtable columnInfos = new Hashtable();
            for (int i = 0; i < grid.Columns.Count; i++)
            {
                try
                {
                    BoundColumn col = (BoundColumn)grid.Columns[i];
                    if (col.Visible)
                    {
                        dt.Columns[col.DataField].Caption = col.HeaderText;
                        columnInfos.Add(col.DataField, new ColumnInfo(col.DataField, col.HeaderText, col.DataFormatString));
                    }
                }
                catch // 忽略其他类型的列
                {
                }
            }

return columnInfos;
}

        public Hashtable SetColumnInfo(DataTable dt)
        {
            Hashtable columnInfos = new Hashtable();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                DataColumn col = dt.Columns[i];
                //string header = StringUtil.IsNullOrEmpty(col.Caption) ? col.ColumnName : col.Caption;
                string header = "";
                if ((col.Caption == null) || (col.Caption.Trim() == string.Empty))
                {
                    header = col.ColumnName;
                }
                else
                {
                    header = col.Caption;
                }
                columnInfos.Add(col.ColumnName, new ColumnInfo(col.ColumnName, header));
            }

return columnInfos;
}

        public void ExpertToExcel(DataSet dataset, Hashtable columnInfos, string filename)
        {
            this.ExpertToExcel(dataset.Tables[0], columnInfos, filename);
        }

        /// <summary>
        /// 将DataTable数据导出到Excel文件。
        /// </summary>
        /// <remarks>
        /// 没有在header中指明列标题的列不被导出。
        /// </remarks>
        /// <param name="dt"></param>
        /// <param name="header"></param>
        /// <param name="filename"></param>
        public void ExpertToExcel(DataTable dt, Hashtable columnInfos, string filename)
        {
            Response.Clear();
            Response.Buffer = false;
            Response.Charset = "utf-8"; // 非常重要
            Response.ContentEncoding = Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
            page.EnableViewState = false;

            Response.Write(GetExcelString(dt, columnInfos));
            Response.End();
        }

        //public void ExperToExcelNoInfo(DataTable dt, string filename)
        //{
        //    Response.Clear();
        //    Response.Buffer = false;
        //    Response.Charset = "utf-8"; // 非常重要
        //    Response.ContentEncoding = Encoding.UTF8;
        //    Response.ContentType = "application/vnd.ms-excel";
        //    Response.AddHeader("Content-Disposition", "attachment;FileName=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
        //    page.EnableViewState = false;

        //    Response.Write(GetExcelString(dt));
        //    Response.End();
        //}

        //public string GetExcelString(DataTable dt)
        //{
        //    // 获取Excel文档格式模版
        //    StreamReader sr = File.OpenText(page.Server.MapPath("\\xlsFormat.config"));
        //    StringBuilder xls = new StringBuilder(sr.ReadToEnd());
        //    sr.Close();

// StringBuilder sb = new StringBuilder();

// // 表
// sb.AppendFormat("<table x:str border=0 cellpadding=0 cellspacing=0 width={0} style='border-collapse:collapse;table-layout:fixed;width:{1}pt'>", 72 * dt.Columns.Count, 54 * dt.Columns.Count);

        //    // 表头，没有在columnInfos中的列不被导出。
        //    sb.Append("\r\n\t<tr height=19 style='height:14.25pt'>");
        //    for (int i = 0; i < dt.Columns.Count; i++)
        //    {
        //        sb.AppendFormat("\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>{0}</td>", dt.Columns[i].Caption);
        //    }
        //    sb.Append("\r\n\t</tr>");

        //    // 表体，没有在columnInfos中的列不被导出。
        //    for (int i = 0; i < dt.Rows.Count; i++)
        //    {
        //        // 行
        //        sb.Append("\r\n\t<tr height=19 style='height:14.25pt'>");
        //        for (int j = 0; j < dt.Columns.Count; j++)
        //        {
        //            string colType = dt.Columns[j].DataType.ToString().ToLower();

        //            // 列
        //            string format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>{＄DataFormat＄}</td>";
        //            switch (colType)
        //            {
        //                case "system.datetime":
        //                    format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xShortDate align=right>{＄DataFormat＄}</td>";
        //                    break;
        //                case "system.decimal":
        //                case "system.double":
        //                case "system.int32":
        //                case "system.int64":
        //                    format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xNumeric align=right x:num='{0}'>{＄DataFormat＄}</td>";
        //                    break;
        //                case "system.string":
        //                    format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt' class=xText>{＄DataFormat＄}</td>";
        //                    break;
        //                default:
        //                    format = "\r\n\t\t<td height=19 width=72 style='height:14.25pt;width:54pt'>" + colType + ":{0}</td>";
        //                    break;
        //            }
        //            // 根据columnInfos中定义的数据显示格式重新格式化导出的数据
        //            format = format.Replace("{＄DataFormat＄}", "{0}");
        //            sb.AppendFormat(format, dt.Rows[i][j]);
        //        }
        //        // 行结束
        //        sb.Append("\r\n\t</tr>");
        //    }
        //    // 表结束
        //    sb.Append("\r\n</table>");

        //    // 最终的Excel数据
        //    xls.Replace("{＄Author＄}", this.ToString());
        //    xls.Replace("{＄LastAuthor＄}", this.ToString());
        //    xls.Replace("{＄Created＄}", DateTime.Now.ToLongDateString());
        //    xls.Replace("{＄LastSaved＄}", DateTime.Now.ToString("U"));
        //    xls.Replace("{＄Company＄}", "NSS");
        //    xls.Replace("{＄Body＄}", sb.ToString());

// return xls.ToString();
//}

        public void ExpertToExcel(DataTable dt, string filename)
        {
            Hashtable columnInfos = this.SetColumnInfo(dt);
            ExpertToExcel(dt, columnInfos, filename);
        }
    }

配置文件xlsformat.config
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

{＄Body＄}

例子

DataTable dt = this.dataset.Tables [0] ;
Grid.DataSource = dt;
Grid.DataBind ();
string filename = "filename.xls";
DatasetToExcel excel = new DatasetToExcel(this.Page);
Hashtable columnInfo;
columnInfo = excel.SetColumnInfo(dt, this.Grid);
excel.ExpertToExcel(dt, columnInfo, filename);

posted on 2006-12-01 12:59 十分之七阅读(2229) 评论(1) 收藏举报

刷新页面返回顶部