导出EXCEL

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;

namespace ZTE.IOA.Foundation.CommonUtils
{
    
    public class ColumnMap
    {
        private bool _usingColumnName = true;

        private int _columnIndex = -1;
        private string _columnName = null;
        private string _caption = null;

        private bool _IsNumber2Text = false;

        /// <summary>
        /// 创建源列列名与导出列名的影射
        /// </summary>
        /// <param name="columnName">源列列名</param>
        /// <param name="caption">导出列名</param>
        public ColumnMap(string columnName, string caption)
        {
            this._usingColumnName = true;
            this._columnName = columnName;
            this._caption = caption;
            this._IsNumber2Text = false;
        }

        /// <summary>
        /// 创建源列序号与导出列名的影射
        /// </summary>
        /// <param name="columnIndex">源列序号</param>
        /// <param name="caption">导出列名</param>
        public ColumnMap(int columnIndex, string caption)
        {
            this._usingColumnName = false;
            this._columnIndex = columnIndex;
            this._caption = caption;
            this._IsNumber2Text = false;
        }

        /// <summary>
        /// 创建源列序号与导出列名的影射
        /// </summary>
        /// <param name="columnIndex">源列序号</param>
        /// <param name="caption">导出列名</param>
        /// <param name="isNumber2Text">是否以将数字显示文本</param>
        public ColumnMap(int columnIndex, string caption, bool isNumber2Text)
        {
            this._usingColumnName = false;
            this._columnIndex = columnIndex;
            this._caption = caption;
            this._IsNumber2Text = isNumber2Text;
        }

        /// <summary>
        /// 创建源列序号与导出列名的影射
        /// </summary>
        /// <param name="columnName">绑定列名</param>
        /// <param name="caption">导出列名</param>
        /// <param name="isNumber2Text">是否以将数字显示文本</param>
        public ColumnMap(string columnName, string caption, bool isNumber2Text)
        {
            this._usingColumnName = false;
            this._columnName = columnName;
            this._caption = caption;
            this._IsNumber2Text = isNumber2Text;
        }

        /// <summary>
        /// 是否使用列名来指定源列
        /// </summary>
        public bool usingColumnName
        {
            get { return _usingColumnName; }
        }

        /// <summary>
        /// 源列序号
        /// </summary>
        public int columnIndex
        {
            get { return this._columnIndex; }
        }

        /// <summary>
        /// 导入到EXCEL中显示的列名
        /// </summary>
        public string caption
        {
            get { return this._caption; }
        }

        /// <summary>
        /// 源列名称
        /// </summary>
        public string columnName
        {
            get { return this._columnName; }
        }

        /// <summary>
        /// 是否将数字转化成文本格式
        /// </summary>
        public bool IsNumber2Text
        {
            get { return this._IsNumber2Text; }
        }
    }


    
    public class Export2Excel
    {
        /// <summary>
        /// EXCEL2000以后版本所能容纳最大的行数
        /// </summary>
        /// <remarks>EXCEL2000以后版本所能容纳最大的行数为65536，但除开列头，故此数值为65535</remarks>
        public const int EXCEL2000_MAXROW = 65535;

        /// <summary>
        /// 导出的最大记录数,0为无限制,缺省为0<br></br>
        /// </summary>
        /// <remarks>
        /// 本工具类主要思路是内存空间换取时间,因此需要注意内存占用过大的负面效应<br></br>
        /// 可配合EXCEL2000_MAXROW使用
        /// </remarks>
        public static int maxRowCount = 0;

        /// <summary>
        /// 在导入到CSV文件前，处理特殊字符
        /// </summary>
        /// <param name="s">待处理的字符串</param>
        /// <returns>处理完的字符串</returns>
        private static string SpecialChar(string s)
        {
            return s.Replace("/"", "/"/"");
        }

        /// <summary>
        /// 不可创建对象
        /// </summary>
        private Export2Excel()
        {
        }

       
        /// <summary>
        /// Dt导出Excel表格数据
        /// </summary>
        /// <param name="page">Web页面</param>
        /// <param name="dt">显示数据的源</param>        
        /// <param name="tileName">报表名</param>        
        static public void GeneralExcel(Page page, DataTable dt,string tileName )
        {
            ColumnMap[] maps = new ColumnMap[dt.Columns.Count];
            for (int i =0 ;i<dt.Columns.Count ;i++)
            {                
                maps[i] = new ColumnMap(dt.Columns[i].Caption,dt.Columns[i].ColumnName);              
            }
            GeneralExcel(page,dt,maps,tileName);
        }

        /// <summary>
        /// Dt导出Excel表格数据
        /// </summary>
        /// <param name="page">Web页面</param>
        /// <param name="dt">显示数据的源</param>
        /// <param name="maps">数据绑定类(指定哪个列对应该数据源的哪个列)</param>        
        /// <param name="tileName">报表名</param>        
        static public void GeneralExcel(Page page, DataTable dt,ColumnMap[] maps, string tileName)
        {
            
            HttpResponse httpResponse = page.Response;
            try
            {
                string FileName = tileName + ".xls";
                httpResponse.AppendHeader("Content-Disposition", "attachment;filename="
                    + HttpUtility.UrlEncode(FileName, Encoding.UTF8));

                httpResponse.ContentEncoding = Encoding.GetEncoding("GB2312");
                httpResponse.ContentType = "application/ms-excel";

                //修改日志2
                StringBuilder HtmlStr = new StringBuilder();
                string str =
                    @"<META http-equiv='Content-Type' content='text/html; charset=gb2312'>
                    <TABLE id='Table1' cellSpacing='1' cellPadding='1' width='100%' border='1' style='FONT-SIZE: 9pt; FONT-FAMILY: 宋体; TEXT-ALIGN: center'>
			            <TR>
				            <TD colSpan='{1}'>
					            <P align='center'><FONT size='{1}'><STRONG>{1}</STRONG></FONT></P>
				            </TD>
			            </TR>			            
						<TR>";
                str = string.Format(str, maps.Length.ToString(), tileName);
                //创建列头
                int count = maps.Length - 1;
                for (int i = 0; i <= count; i++)
                {
                    str += "<TD bgColor='silver'>" + maps[i].caption + "</TD>";
                }
                str += "</TR>";
                HtmlStr.Append(str);
                for (int i = 0; i <= count; i++)
                {
                    str += "<TD>{" + i.ToString() + "}</TD>";
                }

                //详细数据填写
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    str = "<TR>";
                    for (int col = 0; col <= count; col++)
                    {
                        string colValue =
                            HttpUtility.HtmlEncode(dt.Rows[i][maps[col].columnName].ToString());
                        if (maps[col].IsNumber2Text)
                        {
                            str += @"<TD style='mso-number-format:/@'>" + colValue + "</TD>";
                        }
                        else
                        {
                            str += @"<TD>" + colValue + "</TD>";
                        }
                    }
                    str += "</TR>";
                    HtmlStr.Append(str);
                }

                HtmlStr.Append("<TR>");

                str = "</TABLE>";
                HtmlStr.Append(str);
                
                byte[] bytes = Encoding.Default.GetBytes(HtmlStr.ToString());

                page.Response.Clear();  
            
                page.Response.BinaryWrite(bytes);
                page.Response.Flush();
                page.Response.End();
            }
            catch
            {
            }
            finally
            {
                page.Response.End();
                httpResponse.End();
            }
        }
    }
}