DataTable生成Excel文件，返回下载地址

DataTable生成Excel文件，返回下载地址

特点：

1.依据传入参数SheetNum，根据每个Sheet存储的数据行拆分成多个Sheet表

2.根据传入SheetDTAdd，向最后追加一个Sheet表

3.通过ColFunc委托，对列名做修改

4.通过RowFunc委托，对行数据做修改

5.支持.xlsx和.xls

注意：NPOI插件，自行在NuGet上下载

 

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

public static class ExcelHelper
    {
        #region DataTable生成Excel
        /// <summary>
        /// DataTable生成Excel
        /// </summary>
        /// <param name="SheetDT">需要转换的DataTable</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="ExlName">存储的名字</param>
        /// <param name="ExlPath">存储的地址 相对路径</param>
        /// <param name="SheetNum">每个Sheet中存在的数据量</param>
        /// <param name="sType">Excel版本（0 .xlsx 1 .xls）</param>
        /// <returns>返回生成的路径地址</returns>
        public static string DataTableByExcel(DataTable SheetDT, string SheetName, string ExlName, string ExlPath, int SheetNum = 40000, int sType = 0)
        {
            return DataTableByExcel(SheetDT, SheetName, ExlName, ExlPath, null, "", null, null, SheetNum, sType);
        }
        /// <summary>
        /// DataTable生成Excel
        /// </summary>
        /// <param name="SheetDT">需要转换的DataTable</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="ExlName">存储的名字</param>
        /// <param name="ExlPath">存储的地址 相对路径</param>
        /// <param name="ColFunc">产生Col列的委托，用以重设列名</param>
        /// <param name="RowFunc">产生Row行的委托，用以重设DataRow</param>
        /// <param name="SheetNum">每个Sheet中存在的数据量</param>
        /// <param name="sType">Excel版本（0 .xlsx 1 .xls）</param>
        /// <returns>返回生成的路径地址</returns>
        public static string DataTableByExcel(DataTable SheetDT, string SheetName, string ExlName, string ExlPath, Func<string, string> ColFunc = null, Func<DataRow, DataRow> RowFunc = null, int SheetNum = 40000, int sType = 0)
        {
            return DataTableByExcel(SheetDT, SheetName, ExlName, ExlPath, null, "", ColFunc, RowFunc, SheetNum, sType);
        }

        /// <summary>
        /// DataTable生成Excel
        /// </summary>
        /// <param name="SheetDT">需要转换的DataTable</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="ExlName">存储的名字</param>
        /// <param name="ExlPath">存储的地址 相对路径</param>
        /// <param name="SheetDTAdd">追加的DataTable</param>
        /// <param name="SheetNameAdd">追加的DataTable的Sheet名称</param>
        /// <param name="ColFunc">产生Col列的委托，用以重设列名</param>
        /// <param name="RowFunc">产生Row行的委托，用以重设DataRow</param>
        /// <param name="SheetNum">每个Sheet中存在的数据量</param>
        /// <param name="sType">Excel版本（0 .xlsx 1 .xls）</param>
        /// <returns>返回生成的路径地址</returns>
        public static string DataTableByExcel(
            DataTable SheetDT, string SheetName, string ExlName, string ExlPath,
            DataTable SheetDTAdd, string SheetNameAdd,
            Func<string, string> ColFunc = null, Func<DataRow, DataRow> RowFunc = null,
            int SheetNum = 40000, int sType = 0)
        {
            if (SheetDT == null || SheetDT.Rows.Count <= 0) return "";
            List<string> CloumsList = new List<string>();
            获取列名集合
            foreach (DataColumn column in SheetDT.Columns) CloumsList.Add(column.ColumnName);
            XSSFWorkbook Xbook = new XSSFWorkbook();
            HSSFWorkbook Hbook = new HSSFWorkbook();
            ISheet sheet = null;
            int i = 0;//记录总数据
            int SheetIndex = 1;//Sheet起始索引
            int RowIndex = 0;//每个sheet起始行索引
            int CloumsIndex = 0;//每个sheet起始列索引
            SheetName = string.IsNullOrEmpty(SheetName) ? "sheet" : SheetName;//Sheet名称
            foreach (DataRow dr in SheetDT.Rows)
            {
                i++;
                if ((i - 1) % SheetNum == 0)//每个sheet存储SheetNum条，多出拆分成多个Sheet
                {
                    if (sType == 0) sheet = Xbook.CreateSheet(SheetName + SheetIndex);//添加sheet
                    else Hbook.CreateSheet(SheetName + SheetIndex);//添加sheet
                    CloumsIndex = 0;//重置列索引
                    RowIndex = 0;//重置行索引
                    //遍历列，绑定每个sheet的首行为列名
                    foreach (string Cloums in CloumsList)
                    {
                        string FuncCloums = Cloums;
                        if (ColFunc != null) FuncCloums = ColFunc.Invoke(Cloums);//存在委托
                        if (CloumsIndex == 0) sheet.CreateRow(RowIndex).CreateCell(CloumsIndex).SetCellValue(FuncCloums);
                        else sheet.GetRow(RowIndex).CreateCell(CloumsIndex).SetCellValue(FuncCloums);
                        CloumsIndex++;
                    }
                    SheetIndex++;
                }
                RowIndex++;
                DataRow FuncDR = dr;
                if (RowFunc != null) FuncDR = RowFunc.Invoke(dr);//存在委托
                //根据列索引,绑定数据
                for (int S = 0; S < CloumsList.Count; S++)
                {
                    if (S == 0) sheet.CreateRow(RowIndex).CreateCell(S).SetCellValue(FuncDR[S].ToString());
                    else sheet.GetRow(RowIndex).CreateCell(S).SetCellValue(FuncDR[S].ToString());
                }
            }
            SheetNameAdd = string.IsNullOrEmpty(SheetNameAdd) ? "SheetAdd" : SheetNameAdd;//追加的Sheet名称
            //追加的datatable
            if (SheetDTAdd != null || SheetDTAdd.Rows.Count > 0)
            {
                int j = 0;//记录附加表总数据
                if (sType == 0) sheet = Xbook.CreateSheet(SheetNameAdd);
                else sheet = Hbook.CreateSheet(SheetNameAdd);
                CloumsList.Clear();
                获取列名集合
                foreach (DataColumn column in SheetDTAdd.Columns) CloumsList.Add(column.ColumnName);
                CloumsIndex = 0;//重置列索引
                //遍历列，绑定sheet的首行为列名
                foreach (string Cloums in CloumsList)
                {
                    if (CloumsIndex == 0) sheet.CreateRow(j).CreateCell(CloumsIndex).SetCellValue(Cloums);
                    else sheet.GetRow(j).CreateCell(CloumsIndex).SetCellValue(Cloums);
                    CloumsIndex++;
                }
                foreach (DataRow dr in SheetDTAdd.Rows)
                {
                    j++;
                    //根据列索引,绑定数据
                    for (int S = 0; S < CloumsList.Count; S++)
                    {
                        if (S == 0) sheet.CreateRow(j).CreateCell(S).SetCellValue(dr[S].ToString());
                        else sheet.GetRow(j).CreateCell(S).SetCellValue(dr[S].ToString());
                    }

                }
            }
            //sheet.DisplayGridlines = true;//不显示网格
            if (!Directory.Exists(ExlPath)) Directory.CreateDirectory(ExlPath);
            string strErrorFile = ExlPath + ExlName + ((sType == 0) ? ".xlsx" : ".xls");//相对路径
            string strFilePath = HttpContext.Current.Server.MapPath(strErrorFile);//绝对路径
            //string path = AppDomain.CurrentDomain.BaseDirectory + @"Logs\";
            FileStream file = new System.IO.FileStream(strFilePath, FileMode.Create);
            if (sType == 0)
            {
                Xbook.Write(file);
                Xbook.Close();
                Xbook = null;
            }
            else
            {
                Hbook.Write(file);
                Hbook.Close();
                Hbook = null;
            }
            file.Dispose();
            return strErrorFile;
        }
        #endregion

    }