DataTable 类型 导出Excel 方法

 测试代码

    private List<string> CurrentCheckedItems
    {
        get
        {
            return (List<string>)ViewState["CurrentCheckedItems"] ?? new List<string>();
        }
        set
        {
            ViewState["CurrentCheckedItems"] = value;
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

       

        EpochSoft.CostController.Inteface.VoucherCommonAdapter.YingChunExcelAdapter yinc = new EpochSoft.CostController.Inteface.VoucherCommonAdapter.YingChunExcelAdapter();
        EpochSoft.CostController.Inteface.Fundation.AdapterRequestInfo info = new EpochSoft.CostController.Inteface.Fundation.AdapterRequestInfo();
        this.CurrentCheckedItems = this.GetCheckedList();
      
        info.RequestInfo = this.CurrentCheckedItems.ToArray();
        EpochSoft.CostController.Inteface.Fundation.AdapterResponseInfo resInfo = yinc.Run(info);
    }
    private List<string> GetCheckedList()
    {
        List<string> result = new List<string>();
        //string[] languages = { "WLZDH20110316009", "WLZDH20110317002" };
        string[] languages = { "8b9687a2-3125-4211-a743-9b0334e6fdb5", "53a443c2-ed6a-43b0-b427-0cff708433a3", "6f4e990a-0f27-404b-83f8-60d667f1b22f" };
        for(int i=0;i<languages.Length;i++){
            result.Add(languages[i].ToString());
        }
        return result;
    }

 

using System;
using System.Collections.Generic;
using System.Text;
using EpochSoft.CostController.Inteface.Fundation;
using System.Data;
using EpochSoft.CostController.Inteface.VoucherCommonAdapter.ESVoucherService;
using System.Data.OleDb;

namespace EpochSoft.CostController.Inteface.VoucherCommonAdapter
{
    public class YingChunExcelAdapter : ICommonAdapter
    {

        #region ICommonAdapter Members
        /// <summary>
        /// 中烟凭证审核时的处理逻辑是：
        /// a．在控制系统的“凭证审核”页面审核完凭证后点击“凭证审核”按钮，凭证自动通过接口将凭证传给总账系统（NC）。
        /// b．NC系统接受到控制系统传递凭证后验证凭证的正确性，如果正确无误，反馈凭证号给控制系统，控制系统记录该凭证号。如果错误，则反馈错误信息，控制系统接收错误信息并显示给操作人员。
        /// </summary>
        /// <param name="input">input.RequestInfo数据包含一个voucherDataID</param>
        /// <returns></returns>
        public AdapterResponseInfo Run(AdapterRequestInfo input)
        {
            VoucherService vs = new VoucherService();
            int state = vs.Logon("Epochsoft", "Epochsoft");
            //vs.ExportVoucherModel(new string[] { "" });

            AdapterResponseInfo responseInfo = new AdapterResponseInfo(true);

            try
            {
                DataTable dt = new DataTable("BillMainDataTable");
                VoucherItem[] items = vs.ExportVoucherModelByVoucherDataIDs(input.RequestInfo);
                if (input.RequestInfo[0].ToString() != null)
                {
                    string auxiliaryAccounStr = string.Empty;
                    for (int i = 0; i < items[0].Body[0].AuxiliaryAccountingItem.Length; i++)
                    {
                        auxiliaryAccounStr += items[0].Body[0].AuxiliaryAccountingItem[i].DimName + ",";
                    }
                    for (int j = 0; j < items[0].Body[0].Field.Length; j++)
                    {
                        if (auxiliaryAccounStr.IndexOf(items[0].Body[0].Field[j].FieldName) == -1)
                        {
                            dt.Columns.Add(new DataColumn(items[0].Body[0].Field[j].FieldName, typeof(string)));
                        }
                    }
                    dt.Columns.Add(new DataColumn("辅助核算", typeof(string)));
            
                    for(int ii=0;ii<items.Length;ii++)
                    {
                        string auxiliaryAccountStr = string.Empty;
                        string auxiliaryStr = string.Empty;
                        for (int i = 0; i < items[0].Body[0].AuxiliaryAccountingItem.Length; i++)
                        {
                            auxiliaryStr += items[0].Body[0].AuxiliaryAccountingItem[i].DimName + ",";
                            auxiliaryAccountStr += items[ii].Body[0].AuxiliaryAccountingItem[i].DimName + "---" + items[ii].Body[0].AuxiliaryAccountingItem[i].MemberCode + "---" + items[ii].Body[0].AuxiliaryAccountingItem[i].MemberName + "||";
                        }
                        auxiliaryAccountStr = auxiliaryAccountStr.Substring(0, auxiliaryAccountStr.Length - 2);
                        DataRow row = dt.NewRow();
                        for (int j = 0; j < items[0].Body[0].Field.Length; j++)
                        {
                            if (auxiliaryAccountStr.IndexOf(items[0].Body[0].Field[j].FieldName) == -1)
                            {
                                row[items[0].Body[0].Field[j].FieldName] = items[ii].Body[0].Field[j].Value.ToString();
                            }
                        }
                        row["辅助核算"] = auxiliaryAccountStr;

                        dt.Rows.Add(row);
                    }
             }
               
                BizExcel ImportExcel = new BizExcel();
                ImportExcel.Write(dt);
            }
            catch (Exception ex)
            {
                responseInfo.IsSuccess = false;
            }

            return new AdapterResponseInfo(true);
        }
        #endregion
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Web;
namespace EpochSoft.CostController.Inteface.VoucherCommonAdapter
{
    ///描述：此类用于通过OleDB对Excel进行操作
    ///创建者：Lixzh
    ///最后更改日期：2010.3.26
    ///
    /// <summary>
    /// 此枚举用来声明，Excel第一行是否作为表头
    /// </summary>
    public enum HDR
    {
        No = 0,//第一行不作为表头
        Yes = 1//第一行作为表头
    }

    /// <summary>
    /// 此枚举用来告诉驱动程序使用Excel文件的模式
    /// </summary>
    public enum IMEX
    {
        Export = 0,//导出
        Import = 1,//导入
        Mix = 2//混合
    }

    /// <summary>
    /// 此类用于通过OleDB对Excel进行操作
    /// </summary>
    public class BizExcel
    {
        #region 字段
        /// <summary>
        /// 连接字符串
        /// </summary>
        private readonly string OleDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR={1};IMEX={2}\"";
        private string filePath = string.Empty;
        private string oleDbConnection = string.Empty;
        private string hdr_yes = "Yes";
        private string hdr_no = "No";
        #endregion

        #region 构造函数
        public BizExcel() : this(DefaultFilePath) { }

        public BizExcel(string filePath) : this(filePath, HDR.No) { }

        public BizExcel(string filePath, HDR hdr) : this(filePath, hdr, IMEX.Export) { }

        public BizExcel(HDR hdr, IMEX imex) : this(DefaultFilePath, hdr, imex) { }

        public BizExcel(string filePath, HDR hdr, IMEX imex)
        {
            this.filePath = filePath;
            this.oleDbConnection = string.Format(this.OleDBConnection, filePath, this.GetHDRString(hdr), this.GetIMEXString(imex));
        }

        #endregion

        #region 公有方法
        public DataSet Read()
        {
            return this.Read(false);
        }

        public DataSet Read(bool deleteTmpFile)
        {
            this.EnsurePath();
            DataSet ds = new DataSet();
            using (OleDbConnection conn = this.CurrentConnection)
            {
                conn.Open();
                this.CreateDataSet(ds, conn);
            }
            if (deleteTmpFile)
            {
                this.Dispose();
            }
            return ds;
        }

        public void Write(DataSet ds)
        {
            foreach (DataTable dt in ds.Tables)
            {
                this.Write(dt);
            }
        }

        public void Write(DataTable dt)
        {
            using (OleDbConnection conn = this.CurrentConnection)
            {
                conn.Open();
                string createSql = this.GetCreateSql(dt);
                this.ExecuteNonQuery(conn, createSql);

                List<string> columns = this.GetColumns(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    string insertSql = this.GetInsertSql(dt, columns, dr);
                    this.ExecuteNonQuery(conn, insertSql);
                }
            }
        }

        public void Dispose()
        {
            File.Delete(this.FilePath);
        }
        #endregion

        #region 私有方法
        private string GetInsertSql(DataTable dt, List<string> columns, DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("INSERT INTO [{0}] VALUES(", dt.TableName));
            foreach (string columnName in columns)
            {
                object o = dr[columnName];
                sb.Append(string.Format("\t'{0}',", o == null ? string.Empty : o.ToString()));
            }
            string insertSql = sb.ToString();
            insertSql = (insertSql.EndsWith(",") ? insertSql.Substring(0, insertSql.LastIndexOf(",")) : insertSql) + ")";
            return insertSql;
        }

        private void ExecuteNonQuery(OleDbConnection conn, string createSql)
        {
            OleDbCommand command = this.GetCommand(conn, createSql);
            command.ExecuteNonQuery();
        }

        private string GetCreateSql(DataTable dt)
        {
            string dtName = dt.TableName;
            List<string> columns = this.GetColumns(dt);
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("CREATE TABLE [{0}] (", dtName));
            foreach (string columnName in columns)
            {
                sb.Append(string.Format(" [{0}] string,", columnName));
            }
            string createSql = sb.ToString();
            createSql = (createSql.EndsWith(",") ? createSql.Substring(0, createSql.LastIndexOf(",")) : createSql) + ")";
            return createSql;
        }

        /// <summary>
        /// 创建数据集，把Excel文件读取到数据集中，并且每个Sheet都是一张表
        /// </summary>
        /// <param name="ds">数据集对象</param>
        /// <param name="conn">连接对象</param>
        private void CreateDataSet(DataSet ds, OleDbConnection conn)
        {
            List<string> sheets = this.GetExcelTables(conn);
            foreach (string sheetName in sheets)
            {
                this.ReadSheetToTable(conn, sheetName, ds);
            }
        }

        /// <summary>
        /// 把Excel的Sheet读到DataTable中
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="sheetName">SheetName</param>
        /// <returns>DataTable</returns>
        private void ReadSheetToTable(OleDbConnection conn, string sheetName, DataSet ds)
        {
            OleDbCommand command = this.GetCommand(conn, string.Format(" SELECT * FROM [{0}$] ", sheetName));
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            ds.Tables.Add(new DataTable(sheetName));
            adapter.Fill(ds, sheetName);
        }

        private OleDbCommand GetCommand(OleDbConnection conn, string sqlString)
        {
            OleDbCommand command = new OleDbCommand(sqlString, conn);
            return command;
        }

        /// <summary>
        /// 验证路径是否合法
        /// </summary>
        private void ValidatePath()
        {
            if (!File.Exists(this.filePath))
            {
                throw new FileNotFoundException();
            }
        }

        /// <summary>
        /// 验证路径是否合法
        /// </summary>
        private void EnsurePath()
        {
            this.ValidatePath();
        }

        private string GetHDRString()
        {
            return this.GetHDRString(HDR.No);
        }

        private string GetHDRString(HDR hdr)
        {
            return hdr == HDR.Yes ? hdr_yes : hdr_no;
        }

        private string GetIMEXString(IMEX imex)
        {
            return ((int)imex).ToString();
        }

        /// <summary>
        /// 获取指定表名的所有列
        /// </summary>
        /// <param name="oConn">连接对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>列名列表</returns>
        private List<string> GetColumns(OleDbConnection oConn, string tableName)
        {
            DataTable columnTable = this.GetColumnTable(oConn, tableName);
            List<string> colList = new List<string>();
            foreach (DataRow dr in columnTable.Rows)
            {
                colList.Add(dr["Column_Name"].ToString());
            }
            return colList;
        }

        private List<string> GetColumns(DataTable dt)
        {
            List<string> result = new List<string>();
            foreach (DataColumn dc in dt.Columns)
            {
                result.Add(dc.ColumnName);
            }
            return result;
        }

        private DataTable GetColumnTable(OleDbConnection oConn, string tableName)
        {
            DataTable columnTable = oConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
            return columnTable;
        }

        /// <summary>
        /// 获取指定Excel的所有列名
        /// </summary>
        /// <param name="oConn">连接对象</param>
        /// <returns>表名列表</returns>
        private List<string> GetExcelTables(OleDbConnection oConn)
        {
            DataTable t = this.GetTables(oConn);
            List<string> tableNames = new List<string>();
            foreach (DataRow dr in t.Rows)
            {
                string tableName = dr["Table_Name"].ToString();
                string _name = string.Empty;
                if (tableName.EndsWith("$"))
                {
                    _name = dr["Table_Name"].ToString();
                    _name = _name.Substring(0, _name.Length - 1);
                    if (!tableNames.Contains(_name))
                    {
                        tableNames.Add(_name);
                    }
                }
            }
            return tableNames;
        }

        private DataTable GetTables(OleDbConnection oConn)
        {
            DataTable t = oConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            return t;
        }
        #endregion

        #region 属性
        private OleDbConnection CurrentConnection
        {
            get
            {
                return new OleDbConnection(this.oleDbConnection);
            }
        }

        public string FilePath
        {
            get
            {
                return this.filePath;
            }
        }

        private static string DefaultFilePath
        {
            get
            {
                 Guid newid = new Guid();
               // return  newid+".xls";// HttpContext.Current.Server.MapPath(BizCommon.TemplateFolder) + SysShared.GetNewGuid() + ".xls";
                 return   HttpContext.Current.Server.MapPath("TemplateFolder") + System.Guid.NewGuid() + ".xls";
            }
        }
        #endregion
    }
}