using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Microsoft.Office.Interop.Word;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using DataTable = System.Data.DataTable;
using IFont = NPOI.SS.UserModel.IFont;

namespace CommonHelper
{
    /// <summary>
    /// NPOI操作帮助类
    /// </summary>
    public class NPOIHelper
    {
        public static readonly string SheetLockPassword = Configs.GetValue("ExcelTemplatePsw");

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void ExportExcel(DataTable dt, string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

            //表头  
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }
        /// <summary>
        /// 公司导出到Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void ExportExcelCompany(DataSet dt, string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            for (int z = 0; z < dt.Tables.Count; z++)
            {
                ISheet sheet = string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);

                //表头  
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Tables[z].Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Tables[z].Columns[i].ColumnName);
                }

                //数据  
                for (int i = 0; i < dt.Tables[z].Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Tables[z].Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Tables[z].Rows[i][j].ToString());
                    }
                }

            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }
        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void FRMExportExcel(DataTable dt, string file, string title, bool IsShow)
        {
            if (IsShow)
            {
                IWorkbook workbook;
                string fileExt = Path.GetExtension(file).ToLower();
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

                //表头  
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;    //行高为30
                                         // sheet.SetColumnWidth(0, 13 * 256);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
                sheet.SetColumnWidth(0, 30 * 256);
                sheet.AddMergedRegion(region);
                #region 标题样式
                ICellStyle style = workbook.CreateCellStyle();//创建样式对象
                IFont font = workbook.CreateFont(); //创建一个字体样式对象
                font.FontName = "微软雅黑"; //和excel里面的字体对应
                                        // font.Color = new HSSFColor.Pink().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
                font.IsItalic = false; //斜体
                font.FontHeightInPoints = 14;//字体大小
                font.Boldweight = short.MaxValue;//字体加粗
                style.SetFont(font); //将字体样式赋给样式对象
                style.BorderTop = BorderStyle.Thin;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.WrapText = true;//设置自动换行
                style.Alignment = HorizontalAlignment.Center;
                #endregion
                #region 内容样式 1 右对齐 千位符保留两位小数
                ICellStyle style1 = workbook.CreateCellStyle();
                IFont font1 = workbook.CreateFont();
                var format1 = workbook.CreateDataFormat();
                font1.FontName = "微软雅黑";
                font1.IsItalic = false;
                font1.FontHeightInPoints = 12;
                font1.Boldweight = short.MaxValue;
                style1.SetFont(font1);
                style1.DataFormat = format1.GetFormat("#,##0.00");
                style1.BorderTop = BorderStyle.Thin;
                style1.BorderBottom = BorderStyle.Thin;
                style1.BorderLeft = BorderStyle.Thin;
                style1.BorderRight = BorderStyle.Thin;
                style1.WrapText = true;//设置自动换行
                style1.Alignment = HorizontalAlignment.Right;
                #endregion

                #region 内容样式2 左对齐
                ICellStyle style2 = workbook.CreateCellStyle();
                IFont font2 = workbook.CreateFont();
                font2.FontName = "微软雅黑";
                font2.IsItalic = false;
                font2.FontHeightInPoints = 12;
                font2.Boldweight = short.MaxValue;
                style2.SetFont(font2);
                style2.BorderTop = BorderStyle.Thin;
                style2.BorderBottom = BorderStyle.Thin;
                style2.BorderLeft = BorderStyle.Thin;
                style2.BorderRight = BorderStyle.Thin;
                style2.WrapText = true;
                style2.Alignment = HorizontalAlignment.Left;
                #endregion
                #region 内容样式3  左对齐 加粗
                ICellStyle style3 = workbook.CreateCellStyle();
                IFont font3 = workbook.CreateFont();
                font3.FontName = "微软雅黑";
                font3.IsItalic = false;
                font3.FontHeightInPoints = 12;
                font3.Boldweight = (short)FontBoldWeight.Bold;
                style3.SetFont(font3);
                style3.BorderTop = BorderStyle.Thin;
                style3.BorderBottom = BorderStyle.Thin;
                style3.BorderLeft = BorderStyle.Thin;
                style3.BorderRight = BorderStyle.Thin;
                style3.WrapText = true;
                style3.Alignment = HorizontalAlignment.Left;
                #endregion
                #region 内容样式4 右对齐 加粗 千位符 保留两位小数
                ICellStyle style4 = workbook.CreateCellStyle();
                IFont font4 = workbook.CreateFont();
                var format4 = workbook.CreateDataFormat();
                font4.FontName = "微软雅黑";
                font4.IsItalic = false;
                font4.FontHeightInPoints = 12;
                font4.Boldweight = (short)FontBoldWeight.Bold;
                style4.SetFont(font4);
                style4.DataFormat = format4.GetFormat("#,##0.00");
                style4.BorderTop = BorderStyle.Thin;
                style4.BorderBottom = BorderStyle.Thin;
                style4.BorderLeft = BorderStyle.Thin;
                style4.BorderRight = BorderStyle.Thin;
                style4.WrapText = true;
                style4.Alignment = HorizontalAlignment.Right;
                #endregion
                #region 内容样式 5 右对齐 
                ICellStyle style5 = workbook.CreateCellStyle();
                IFont font5 = workbook.CreateFont();
                font5.FontName = "微软雅黑";
                font5.IsItalic = false;
                font5.FontHeightInPoints = 12;
                font5.Boldweight = short.MaxValue;
                style5.SetFont(font5);
                style5.BorderTop = BorderStyle.Thin;
                style5.BorderBottom = BorderStyle.Thin;
                style5.BorderLeft = BorderStyle.Thin;
                style5.BorderRight = BorderStyle.Thin;
                style5.WrapText = true;//设置自动换行
                style5.Alignment = HorizontalAlignment.Right;
                #endregion
                ICell cellt = row.CreateCell(0);
                cellt.CellStyle = style;   //把样式赋给单元格
                cellt.SetCellValue(title);

                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    if (row1 == null)
                    {
                        continue;
                    }

                    int OldHg = row1.Height;
                    for (int z = 0; z < dt.Columns.Count; z++)
                    {
                        if (row1.GetCell(z) == null)
                        {
                            continue;
                        }
                        ICell CurCell = row1.GetCell(z);
                        if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
                        {
                            double CurHeight = row1.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
                            double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
                            short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
                            if (height * 256 > OldHg)
                            {
                                OldHg = height * 256;
                            }
                        }
                    }
                    if (row1.Height < OldHg)
                    {
                        row1.Height = Convert.ToInt16(OldHg);
                    }
                    sheet.SetColumnWidth(i + 1, 25 * 256);
                    bool FontFlag = false;
                    //row1.Height = 36 * 20;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {

                        dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("wqeewqsdad", "").Replace("--------", "*").Replace("木部", "本部").Replace("贷币", "货币");
                        if (i == 0)
                        {
                            if (dt.Rows[i][j].ToString() == "" || dt.Rows[i][j].ToString().Length > 5)
                            {
                                if (j == 0)
                                {
                                    dt.Rows[i][j] = "项目";
                                }
                            }
                        }
                        if (dt.Columns.Count == 4 && i == 0)
                        {
                            if (dt.Rows[i][j].ToString() == "")
                            {
                                if (j == 0)
                                {
                                    dt.Rows[i][j] = "项目";
                                }
                                if (j == 2)
                                {
                                    dt.Rows[i][j] = "本年金额";
                                }
                                if (j == 3)
                                {
                                    dt.Rows[i][j] = "上年金额";
                                }
                            }
                        }

                        ICell cell = row1.CreateCell(j);
                        if (j == 0)
                        {
                            if ((dt.Rows[i][j].ToString().Contains("流动资产") && dt.Rows[i][j].ToString().Length < 6) || (dt.Rows[i][j].ToString().Contains("非流动资产")) && dt.Rows[i][j].ToString().Length < 7 || dt.Rows[i][j].ToString().Contains("合计") || dt.Rows[i][j].ToString().Contains("总计"))
                            {
                                FontFlag = true;
                                cell.CellStyle = style3;
                            }
                            else
                            {
                                cell.CellStyle = style2;
                            }

                        }
                        else
                        {
                            if (FontFlag)
                            {
                                cell.CellStyle = style4;
                            }
                            else
                            {
                                cell.CellStyle = style1;
                            }

                        }
                        double Money = -1;
                        if (double.TryParse(dt.Rows[i][j].ToString(), out Money))
                        {
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                    }
                }

                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件 
                //if (System.IO.Directory.Exists(file) == false)
                //{
                //    System.IO.Directory.CreateDirectory(file);
                //}

                using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            else
            {
                IWorkbook workbook;
                string fileExt = Path.GetExtension(file).ToLower();
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
                if (workbook == null) { return; }
                ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

                //表头  
                IRow row = sheet.CreateRow(0);
                row.Height = 25 * 20;    //行高为30
                                         // sheet.SetColumnWidth(0, 13 * 256);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
                sheet.SetColumnWidth(0, 30 * 256);
                sheet.AddMergedRegion(region);
                #region 标题样式
                ICellStyle style = workbook.CreateCellStyle();//创建样式对象
                IFont font = workbook.CreateFont(); //创建一个字体样式对象
                font.FontName = "微软雅黑"; //和excel里面的字体对应
                                        // font.Color = new HSSFColor.Pink().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
                font.IsItalic = false; //斜体
                font.FontHeightInPoints = 14;//字体大小
                font.Boldweight = short.MaxValue;//字体加粗
                style.SetFont(font); //将字体样式赋给样式对象
                style.BorderTop = BorderStyle.Thin;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.WrapText = true;//设置自动换行
                style.Alignment = HorizontalAlignment.Center;
                #endregion
                #region 内容样式 1 右对齐 千位符保留两位小数
                ICellStyle style1 = workbook.CreateCellStyle();
                IFont font1 = workbook.CreateFont();
                var format1 = workbook.CreateDataFormat();
                font1.FontName = "微软雅黑";
                font1.IsItalic = false;
                font1.FontHeightInPoints = 12;
                font1.Boldweight = short.MaxValue;
                style1.SetFont(font1);
                //style1.DataFormat = format1.GetFormat("#,##0.00");
                style1.BorderTop = BorderStyle.Thin;
                style1.BorderBottom = BorderStyle.Thin;
                style1.BorderLeft = BorderStyle.Thin;
                style1.BorderRight = BorderStyle.Thin;
                style1.WrapText = true;//设置自动换行
                style1.Alignment = HorizontalAlignment.Right;
                #endregion

                #region 内容样式2 左对齐
                ICellStyle style2 = workbook.CreateCellStyle();
                IFont font2 = workbook.CreateFont();
                font2.FontName = "微软雅黑";
                font2.IsItalic = false;
                font2.FontHeightInPoints = 12;
                font2.Boldweight = short.MaxValue;
                style2.SetFont(font2);
                style2.BorderTop = BorderStyle.Thin;
                style2.BorderBottom = BorderStyle.Thin;
                style2.BorderLeft = BorderStyle.Thin;
                style2.BorderRight = BorderStyle.Thin;
                style2.WrapText = true;
                style2.Alignment = HorizontalAlignment.Left;
                #endregion
                #region 内容样式3  左对齐 加粗
                ICellStyle style3 = workbook.CreateCellStyle();
                IFont font3 = workbook.CreateFont();
                font3.FontName = "微软雅黑";
                font3.IsItalic = false;
                font3.FontHeightInPoints = 12;
                font3.Boldweight = (short)FontBoldWeight.Bold;
                style3.SetFont(font3);
                style3.BorderTop = BorderStyle.Thin;
                style3.BorderBottom = BorderStyle.Thin;
                style3.BorderLeft = BorderStyle.Thin;
                style3.BorderRight = BorderStyle.Thin;
                style3.WrapText = true;
                style3.Alignment = HorizontalAlignment.Left;
                #endregion
                #region 内容样式4 右对齐 加粗 千位符 保留两位小数
                ICellStyle style4 = workbook.CreateCellStyle();
                IFont font4 = workbook.CreateFont();
                var format4 = workbook.CreateDataFormat();
                font4.FontName = "微软雅黑";
                font4.IsItalic = false;
                font4.FontHeightInPoints = 12;
                font4.Boldweight = (short)FontBoldWeight.Bold;
                style4.SetFont(font4);
                // style4.DataFormat = format4.GetFormat("#,##0.00");
                style4.BorderTop = BorderStyle.Thin;
                style4.BorderBottom = BorderStyle.Thin;
                style4.BorderLeft = BorderStyle.Thin;
                style4.BorderRight = BorderStyle.Thin;
                style4.WrapText = true;
                style4.Alignment = HorizontalAlignment.Right;
                #endregion
                #region 内容样式 5 右对齐 
                ICellStyle style5 = workbook.CreateCellStyle();
                IFont font5 = workbook.CreateFont();
                font5.FontName = "微软雅黑";
                font5.IsItalic = false;
                font5.FontHeightInPoints = 12;
                font5.Boldweight = short.MaxValue;
                style5.SetFont(font5);
                style5.BorderTop = BorderStyle.Thin;
                style5.BorderBottom = BorderStyle.Thin;
                style5.BorderLeft = BorderStyle.Thin;
                style5.BorderRight = BorderStyle.Thin;
                style5.WrapText = true;//设置自动换行
                style5.Alignment = HorizontalAlignment.Right;
                #endregion
                ICell cellt = row.CreateCell(0);
                cellt.CellStyle = style;   //把样式赋给单元格
                cellt.SetCellValue(title);

                //数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    if (row1 == null)
                    {
                        continue;
                    }

                    int OldHg = row1.Height;
                    for (int z = 0; z < dt.Columns.Count; z++)
                    {
                        if (row1.GetCell(z) == null)
                        {
                            continue;
                        }
                        ICell CurCell = row1.GetCell(z);
                        if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
                        {
                            double CurHeight = row1.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
                            double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
                            short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
                            if (height * 256 > OldHg)
                            {
                                OldHg = height * 256;
                            }
                        }
                    }
                    if (row1.Height < OldHg)
                    {
                        row1.Height = Convert.ToInt16(OldHg);
                    }
                    sheet.SetColumnWidth(i + 1, 25 * 256);
                    bool FontFlag = false;
                    //row1.Height = 36 * 20;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {

                        dt.Rows[i][j] = dt.Rows[i][j].ToString().Replace("wqeewqsdad", "").Replace("--------", "*").Replace("木部", "本部").Replace("贷币", "货币");
                        if (i == 0)
                        {
                            if (dt.Rows[i][j].ToString() == "" || dt.Rows[i][j].ToString().Length > 5)
                            {
                                if (j == 0)
                                {
                                    dt.Rows[i][j] = "项目";
                                }
                            }
                        }
                        if (dt.Columns.Count == 4 && i == 0)
                        {
                            if (dt.Rows[i][j].ToString() == "")
                            {
                                if (j == 0)
                                {
                                    dt.Rows[i][j] = "项目";
                                }
                                if (j == 2)
                                {
                                    dt.Rows[i][j] = "本年金额";
                                }
                                if (j == 3)
                                {
                                    dt.Rows[i][j] = "上年金额";
                                }
                            }
                        }

                        ICell cell = row1.CreateCell(j);
                        if (j == 0)
                        {
                            if ((dt.Rows[i][j].ToString().Contains("流动资产") && dt.Rows[i][j].ToString().Length < 6) || (dt.Rows[i][j].ToString().Contains("非流动资产")) && dt.Rows[i][j].ToString().Length < 7 || dt.Rows[i][j].ToString().Contains("合计") || dt.Rows[i][j].ToString().Contains("总计"))
                            {
                                FontFlag = true;
                                cell.CellStyle = style3;
                            }
                            else
                            {
                                cell.CellStyle = style2;
                            }

                        }
                        else
                        {
                            if (FontFlag)
                            {
                                cell.CellStyle = style4;
                            }
                            else
                            {
                                cell.CellStyle = style1;
                            }

                        }
                        double Money = -1;
                        if (double.TryParse(dt.Rows[i][j].ToString(), out Money))
                        {
                            cell.SetCellValue(Convert.ToDouble(dt.Rows[i][j].ToString()).ToString("0.00"));
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                    }
                }

                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件 
                //if (System.IO.Directory.Exists(file) == false)
                //{
                //    System.IO.Directory.CreateDirectory(file);
                //}

                using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>   
        /// DataTable导出到Excel文件   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName,
            string[] oldColumnNames, string[] newColumnNames)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>   
        /// DataTable导出到Excel的MemoryStream   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strSheetName">工作表名称</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName,
            string[] oldColumnNames, string[] newColumnNames)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            FillWorkbook(ref workbook, dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames);

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        /// <summary>   
        /// DataSet导出到Excel的MemoryStream   
        /// </summary>   
        /// <param name="dtSource">源DataSet</param>   
        public static MemoryStream ExportDataSet(DataSet dtSource)
        {

            HSSFWorkbook workbook = new HSSFWorkbook();
            var index = 0;
            foreach (DataTable dt in dtSource.Tables)
            {
                index++;
                //生成列
                string columns = "";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (i > 0)
                    {
                        columns += ",";
                    }

                    columns += dt.Columns[i].ColumnName;
                }

                var sheetname = string.IsNullOrWhiteSpace(dt.TableName) ? ("Sheet" + index) : dt.TableName;
                FillWorkbook(ref workbook, dt, dt.TableName, sheetname, columns.Split(','), columns.Split(','));
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        private static void FillWorkbook(ref HSSFWorkbook workbook, DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames,
            string[] newColumnNames)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return;
            }
            ISheet sheet = workbook.CreateSheet(strSheetName);

            #region 右击文件 属性信息

            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    workbook.DocumentSummaryInformation = dsi;

            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    //if (HttpContext.Current.Session["realname"] != null)
            //    //{
            //    //    si.Author = HttpContext.Current.Session["realname"].ToString();
            //    //}
            //    //else
            //    //{
            //    //    if (HttpContext.Current.Session["username"] != null)
            //    //    {
            //    //        si.Author = HttpContext.Current.Session["username"].ToString();
            //    //    }
            //    //} //填加xls文件作者信息   

            //    //si.ApplicationName = "东方融资网"; //填加xls文件创建程序信息   
            //    //si.LastAuthor = "融管系统"; //填加xls文件最后保存者信息   
            //    //si.Comments = "融管系统自建文件"; //填加xls文件作者信息   
            //    si.Title = strHeaderText; //填加xls文件标题信息   
            //    si.Subject = strHeaderText; //填加文件主题信息   
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}

            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽

            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString())
                        .Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }

                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }

            #endregion

            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式

                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式

                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }

                    #endregion


                    #region 列头及样式

                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }

                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }

                    #endregion

                    rowIndex = 2;
                }

                #endregion


                #region 填充内容

                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String": //字符串类型   
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime": //日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle; //格式化显示   
                            break;
                        case "System.Boolean": //布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16": //整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal": //浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull": //空值处理   
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }
        }

        /// <summary>   
        /// WEB导出DataTable到Excel   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">文件名</param>   
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");
        }

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名，包含扩展名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
            string[] oldColumnNames, string[] newColumnNames)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);
        }

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名</param>
        /// <param name="strSheetName">工作表名称</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
            string strSheetName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式   
            curContext.Response.ContentType = "application/octet-stream";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            //生成列
            string columns = "";
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                if (i > 0)
                {
                    columns += ",";
                }

                columns += dtSource.Columns[i].ColumnName;
            }

            curContext.Response.BinaryWrite(
                Export(dtSource, strHeaderText, strSheetName, columns.Split(','), columns.Split(',')).GetBuffer());
            curContext.Response.End();

        }

        public static void ExportByWeb(DataSet dtSource, string strFileName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式   
            curContext.Response.ContentType = "application/octet-stream";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            curContext.Response.BinaryWrite(
                ExportDataSet(dtSource).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>
        /// 导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">要导出的DataTable</param>
        /// <param name="strHeaderText">标题文字</param>
        /// <param name="strFileName">文件名，包含扩展名</param>
        /// <param name="strSheetName">工作表名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName,
            string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式   
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            curContext.Response.BinaryWrite(
                Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>读取excel   
        /// 默认第一行为表头，导入第一个工作表
        /// </summary>   
        /// <param name="strFileName">excel文档路径</param>   
        /// <returns></returns>   
        public static DataTable Import(string strFileName)
        {
            //int m = 0;
            try
            {
                DataTable dt = new DataTable();

                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }

                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                for (int j = 0; j < cellCount; j++)
                {
                    //m = j;
                    ICell cell = headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    //if (i == 3)
                    //{
                    //    string mm = "";
                    //}
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }

                        dt.Rows.Add(dataRow);
                    }
                }

                return dt;
            }
            catch (Exception e)
            {
                //Console.WriteLine(m.ToString());
                return null;
            }

        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }
        /// <summary>
        /// 从Excel中获取数据到DataTable(针对公司)
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcelCompany(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex = 0)
        {
            IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcelReport(workbook, SheetName, HeaderRowIndex, true);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    //headerRow.GetCell(i).SetCellType(CellType.String);
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable

                for (int i = (sheet.FirstRowNum + 2); i < sheet.LastRowNum + 1; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    dataRow[j] = null;
                                    break;
                                case CellType.Boolean:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dataRow[j] = cell.DateCellValue.ToString();
                                    }
                                    else
                                    {
                                        dataRow[j] = cell.ToString();
                                    }
                                    break;
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula:
                                    cell.SetCellType(CellType.String);
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }

                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }

                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }

            #region 清除最后的空行

            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }

                if (isnull)
                {
                    table.Rows[i].Delete();
                }
                else
                {
                    break;
                }
            }






            #endregion

            return table;
        }
        #region 报告
        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcelReport(IWorkbook workbook, string SheetName, int HeaderRowIndex, bool CompanyFlag = false)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            int Cou = 0;
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    //headerRow.GetCell(i).SetCellType(CellType.String);
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                if (CompanyFlag)
                {

                    try
                    {
                        for (int i = (sheet.FirstRowNum); i < sheet.LastRowNum + 1; i++)
                        {

                            IRow row = sheet.GetRow(i);
                            if (row != null)
                            {
                                DataRow dataRow = table.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; j++)
                                {
                                    ICell cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        dataRow[j] = null;
                                    }
                                    else
                                    {
                                        //dataRow[j] = cell.ToString();
                                        switch (cell.CellType)
                                        {
                                            case CellType.Blank:
                                                dataRow[j] = null;
                                                break;
                                            case CellType.Boolean:
                                                dataRow[j] = cell.BooleanCellValue;
                                                break;
                                            case CellType.Numeric:

                                                if (DateUtil.IsCellDateFormatted(cell))
                                                {
                                                    if (SheetName == "资产负债表" || SheetName == "利润表" || SheetName == "现金流量表")
                                                    {
                                                        dataRow[j] = "";
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = cell.DateCellValue.ToString();
                                                    }
                                                }
                                                else
                                                {
                                                    dataRow[j] = cell.ToString();
                                                }
                                                break;
                                            case CellType.String:
                                                dataRow[j] = cell.StringCellValue;
                                                break;
                                            case CellType.Error:
                                                dataRow[j] = cell.ErrorCellValue;
                                                break;
                                            case CellType.Formula:
                                                cell.SetCellType(CellType.String);
                                                dataRow[j] = cell.StringCellValue;
                                                break;
                                            default:
                                                dataRow[j] = "=" + cell.CellFormula;
                                                break;
                                        }
                                    }
                                }

                                table.Rows.Add(dataRow);
                            }

                            //dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    catch (Exception)
                    {
                        var s = Cou;
                        throw;
                    }

                }
                else
                {
                    for (int i = (sheet.FirstRowNum + 2); i < sheet.LastRowNum + 1; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                dataRow[j] = null;
                            }
                            else
                            {
                                //dataRow[j] = cell.ToString();
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        dataRow[j] = null;
                                        break;
                                    case CellType.Boolean:
                                        dataRow[j] = cell.BooleanCellValue;
                                        break;
                                    case CellType.Numeric:

                                        if (DateUtil.IsCellDateFormatted(cell))
                                        {
                                            if (SheetName == "资产负债表" || SheetName == "利润表" || SheetName == "现金流量表")
                                            {
                                                dataRow[j] = "";
                                            }
                                            else
                                            {
                                                dataRow[j] = cell.DateCellValue.ToString();
                                            }
                                        }
                                        else
                                        {
                                            dataRow[j] = cell.ToString();
                                        }
                                        break;
                                    case CellType.String:
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                    case CellType.Error:
                                        dataRow[j] = cell.ErrorCellValue;
                                        break;
                                    case CellType.Formula:
                                        cell.SetCellType(CellType.String);
                                        dataRow[j] = cell.StringCellValue;
                                        break;
                                    default:
                                        dataRow[j] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }

                        table.Rows.Add(dataRow);
                        //dataRow[j] = row.GetCell(j).ToString();
                    }
                }


                #endregion
            }
            catch (System.Exception ex)
            {
                var s = table.Rows.Count;
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }

            #region 清除最后的空行

            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }

                if (isnull)
                {
                    table.Rows[i].Delete();
                }
                else
                {
                    break;
                }
            }






            #endregion

            return table;
        }
        public static DataSet RenderDataTableFromExcelReport(Stream ExcelFileStream, string[] SheetName, int HeaderRowIndex, ref bool NameExistFlag)
        {
            DataSet dataSet = new DataSet();
            IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
            ExcelFileStream.Close();
            int SheetCount = workbook.NumberOfSheets; //获取表的数量
            string[] SheetNames = new string[SheetCount]; //保存表的名称
            for (int i = 0; i < SheetCount; i++)
            {
                SheetNames[i] = workbook.GetSheetName(i);
            }
            foreach (var item in SheetName)
            {
                if (SheetNames.Contains(item))
                {
                    NameExistFlag = true;
                    DataTable dt = RenderDataTableFromExcelReport(workbook, item, HeaderRowIndex);
                    dt.TableName = item;
                    dataSet.Tables.Add(dt.Copy());
                }
            }
            return dataSet;
            //if (SheetNames.Contains(SheetName))
            //{
            //    NameExistFlag = true;
            //    return RenderDataTableFromExcelReport(workbook, SheetName, HeaderRowIndex);
            //}
            //else
            //{
            //    NameExistFlag = false;
            //    return null;
            //}
        }
        #endregion
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, ref bool NameExistFlag)
        {
            IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
            ExcelFileStream.Close();
            int SheetCount = workbook.NumberOfSheets; //获取表的数量
            string[] SheetNames = new string[SheetCount]; //保存表的名称
            for (int i = 0; i < SheetCount; i++)
            {
                SheetNames[i] = workbook.GetSheetName(i);
            }
            if (SheetNames.Contains(SheetName))
            {
                NameExistFlag = true;
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
            else
            {
                NameExistFlag = false;
                return null;
            }
        }

        public static void GenerateFiletoExcel(string TempletFilePath, string ReportFilePath, string SheetName, DataTable dt, int MaxColumn, int MaxDateCol = 1, int MaxNoteCol = 0, int MaxTypeCol = 0)
        {
            using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);

                ISheet sheet1 = workbook.GetSheet(SheetName);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < MaxColumn; j++)
                    {
                        if (j < MaxDateCol)//前面0-MaxDateCol列日期格式
                        {
                            //ICellStyle cellStyle = workbook.CreateCellStyle();
                            //cellStyle.IsLocked = false;
                            //sheet1.GetRow(i + 2).GetCell(j).CellStyle = cellStyle;
                            //string test = dt.Rows[i][j].ToString().Replace("/","-");
                            if ((SheetName == "回收计划" || SheetName == "支付计划") && MaxTypeCol == 1)
                            {
                                sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("-", "/"));
                            }
                            else
                            {
                                sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("/", "-"));
                            }

                        }
                        else if (MaxNoteCol > 0 && j >= MaxColumn - 1) //后面最后一列是备注内容
                        {
                            IRow row = sheet1.GetRow(i + 2);
                            ICell cell = row.CreateCell(j, CellType.String);
                            ICellStyle cellStyle = workbook.CreateCellStyle();
                            cellStyle.BorderBottom = BorderStyle.Thin;
                            cellStyle.BorderRight = BorderStyle.Thin;
                            cellStyle.IsLocked = false;
                            cell.CellStyle = cellStyle;
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                        else if (MaxTypeCol > 0 && j >= MaxColumn - 2)//列中有布尔类型
                        {
                            IRow row = sheet1.GetRow(i + 2);
                            ICell cell = row.CreateCell(j, CellType.String);
                            ICellStyle cellStyle = workbook.CreateCellStyle();
                            cellStyle.BorderBottom = BorderStyle.Thin;
                            cellStyle.BorderRight = BorderStyle.Thin;
                            cellStyle.IsLocked = false;
                            cell.CellStyle = cellStyle;
                            cell.SetCellValue(Convert.ToBoolean(dt.Rows[i][j].ToString()) == true ? "是" : "否");
                        }
                        else if (SheetName == "流动性分析")
                        {
                            if (j == 0)
                            {
                                IRow row = sheet1.GetRow(i + 2);
                                ICell cell = row.CreateCell(j, CellType.String);
                                ICellStyle cellStyle = workbook.CreateCellStyle();
                                cellStyle.BorderBottom = BorderStyle.Thin;
                                cellStyle.BorderRight = BorderStyle.Thin;
                                cellStyle.IsLocked = false;
                                cell.CellStyle = cellStyle;
                                cell.SetCellValue(Convert.ToInt32(dt.Rows[i][j].ToString()));
                            }
                            else if (j == 3 || j == 5)
                            {
                                IRow row = sheet1.GetRow(i + 2);
                                ICell cell = row.CreateCell(j, CellType.String);
                                ICellStyle cellStyle = workbook.CreateCellStyle();
                                cellStyle.BorderBottom = BorderStyle.Thin;
                                cellStyle.BorderRight = BorderStyle.Thin;
                                cellStyle.IsLocked = false;
                                cell.CellStyle = cellStyle;
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                            else
                            {
                                sheet1.GetRow(i + 2).GetCell(j).SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                        }
                        else if (SheetName == "收款分析" || SheetName == "付款分析")
                        {
                            if (j == 0)
                            {
                                IRow row = sheet1.GetRow(i + 2);
                                ICell cell = row.CreateCell(j, CellType.String);
                                ICellStyle cellStyle = workbook.CreateCellStyle();
                                cellStyle.BorderBottom = BorderStyle.Thin;
                                cellStyle.BorderRight = BorderStyle.Thin;
                                cellStyle.IsLocked = false;
                                cell.CellStyle = cellStyle;
                                cell.SetCellValue(Convert.ToInt32(dt.Rows[i][j].ToString()));
                            }
                            else if (j == 1)
                            {
                                sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", "").Replace("-", "/"));
                            }
                            else if (j == 2)
                            {
                                IRow row = sheet1.GetRow(i + 2);
                                ICell cell = row.CreateCell(j, CellType.String);
                                ICellStyle cellStyle = workbook.CreateCellStyle();
                                cellStyle.BorderBottom = BorderStyle.Thin;
                                cellStyle.BorderRight = BorderStyle.Thin;
                                cellStyle.IsLocked = false;
                                cell.CellStyle = cellStyle;
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                            else
                            {
                                sheet1.GetRow(i + 2).GetCell(j).SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                        }

                    }
                }
                sheet1.ProtectSheet(SheetLockPassword);
                ISheet sheetNote = workbook.GetSheet("填写说明");
                if (sheetNote != null)
                {
                    sheetNote.ProtectSheet(SheetLockPassword);
                }

                using (FileStream filess = File.OpenWrite(ReportFilePath))
                {
                    workbook.Write(filess);
                }
            }
        }
        #region 导出报表
        public static void GenerateReportExcel(string[] SheetArr, string TempletFilePath, string ReportFilePath, DataSet dt, int MaxColumn)
        {
            //IWorkbook workbook;
            //string fileExt = Path.GetExtension(ReportFilePath).ToLower();
            //if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            //if (workbook == null) { return; }

            using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
            {
                string[] SheetAll = new[] { "填写说明", "一般企业-资产负债表", "一般企业-利润表", "一般企业-现金流量表", "一般企业-现金流量表补充资料", "一般企业-所有者权益变动表", "商业银行-资产负债表", "商业银行-利润表", "商业银行-现金流量表", "商业银行-现金流量表补充资料", "商业银行-所有者权益变动表", "证券公司-资产负债表", "证券公司-利润表", "证券公司-现金流量表", "证券公司-现金流量表补充资料", "证券公司-所有者权益变动表", "保险公司-资产负债表", "保险公司-利润表", "保险公司-现金流量表", "保险公司-现金流量表补充资料", "保险公司-所有者权益变动表", "综合企业-资产负债表", "综合企业-利润表", "综合企业-现金流量表", "综合企业-现金流量表补充资料", "综合企业-所有者权益变动表" };
                #region 预设行数

                //var SheetRow = 0;
                //if (SheetName == "资产负债表")
                //{
                //    if (ReportType == "合并报表")
                //    {
                //        SheetName = "资产负债表-合并";
                //        SheetRow = 136;
                //    }
                //    else
                //    {
                //        SheetName = "资产负债表-母公司";
                //        SheetRow = 134;
                //    }

                //}
                //else if (SheetName == "利润表")
                //{
                //    if (ReportType == "合并报表")
                //    {
                //        SheetName = "利润表-合并";
                //        SheetRow = 87;
                //    }
                //    else
                //    {
                //        SheetName = "利润表-母公司";
                //        SheetRow = 80;
                //    }

                //}
                //else if(SheetName== "现金流量表")
                //{
                //    if (ReportType == "合并报表")
                //    {
                //        SheetName = "现金流量表-合并";
                //        SheetRow = 66;
                //    }
                //    else
                //    {
                //        SheetName = "现金流量表-母公司";
                //        SheetRow = 64;
                //    }

                //}
                //else if (SheetName=="现金流量表补充资料")
                //{
                //    if (ReportType== "合并报表")
                //    {
                //        SheetName = "现金流量表补充资料-合并";
                //    }
                //    else
                //    {
                //        SheetName = "现金流量表补充资料-母公司";
                //    }
                //    SheetRow = 32;
                //}
                //else if (SheetName== "所有者权益变动表")
                //{
                //    if (ReportType == "合并报表")
                //    {
                //        SheetName = "所有者权益变动表-合并";
                //    }
                //    else
                //    {
                //        SheetName = "所有者权益变动表-母公司";
                //    }
                //    SheetRow = 28;
                //}
                #endregion
                IWorkbook workbook1 = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
                var DelSheet = SheetAll.Except(SheetArr).ToList();
                List<int> Index = new List<int>();
                //foreach (var item in SheetArr)
                //{
                //    Index.Add(workbook1.GetSheetIndex(item));
                //}
                //var SheetNum = workbook1.NumberOfSheets;
                //for (int i = 0; i < SheetNum; i++)
                //{
                //    if (!Index.Equals(i))
                //    {
                //        workbook1.RemoveSheetAt(i);
                //    }

                //}
                for (int i = 0; i < DelSheet.Count; i++)
                {
                    workbook1.RemoveSheetAt(workbook1.GetSheetIndex(DelSheet[i]));

                }


                //ISheet sheet2 = workbook1.GetSheet(SheetArr[1]);
                //ISheet sheet3 = workbook1.GetSheet(SheetArr[2]);
                //ISheet sheet4 = workbook1.GetSheet(SheetArr[3]);
                //ISheet sheet5 = workbook1.GetSheet(SheetArr[4]);

                // ISheet sheet = workbook.CreateSheet(dt.Tables[z].TableName);
                //CopySheet(workbook, sheet1, sheet, true);
                // XSSFWorkbook myBook = new XSSFWorkbook(fs2);
                //  workbook.CreateSheet(dt.Tables[z].TableName);
                //ISheet sheet=string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);
                //string.IsNullOrEmpty(dt.Tables[z].TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.Tables[z].TableName);
                //sheet1.ForceFormulaRecalculation = false;
                //for (int i = 0; i < dt.Tables[z].Rows.Count; i++)
                //{

                //ISheet sheet2 = workbook1.GetSheet(SheetArr[1]);
                //ISheet sheet3 = workbook1.GetSheet(SheetArr[2]);
                //ISheet sheet4 = workbook1.GetSheet(SheetArr[3]);
                //ISheet sheet5 = workbook1.GetSheet(SheetArr[4]);
                #region 按模板导出
                for (int z = 0; z < dt.Tables.Count; z++)
                {
                    ISheet sheet1 = workbook1.GetSheet(SheetArr[z]);
                    int i = 0;
                    if (dt.Tables[z].TableName == "资产负债表")
                    {
                        i += 4;
                    }
                    else if (dt.Tables[z].TableName == "利润表")
                    {
                        i += 4;
                    }
                    else if (dt.Tables[z].TableName == "现金流量表")
                    {
                        i += 4;
                    }
                    else if (dt.Tables[z].TableName == "现金流量表补充资料")
                    {
                        i += 4;
                    }
                    else if (dt.Tables[z].TableName == "所有者权益变动表")
                    {
                        i += 6;
                    }
                    if (!SheetArr[z].Contains("综合企业"))
                    {
                        for (int j = 0; j < dt.Tables[z].Rows.Count; j++)
                        {
                            IRow row = sheet1.GetRow(j + i);
                            if (dt.Tables[z].TableName == "所有者权益变动表")
                            {
                                var SName = row.GetCell(0);

                                if (SName != null && SName.ToString().Trim() != "")
                                {
                                    var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
                                    if (Sel != null && Sel.Count == 1)
                                    {
                                        int index = 1;
                                        foreach (var item in Sel[0].Table.Rows)
                                        {
                                            ICell cell = row.GetCell(index);
                                            if (index == 15)
                                            {
                                                cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
                                            }
                                            else if (index < 15)
                                            {
                                                cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
                                            }

                                            index++;
                                        }
                                    }
                                    else if (Sel.Count > 1)
                                    {

                                        var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();


                                        if (OneArr.Count > 0)
                                        {
                                            int index = 1;
                                            foreach (var item in Sel[0].Table.Rows)
                                            {
                                                ICell cell = row.GetCell(index);

                                                if (index == 15)
                                                {
                                                    cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
                                                }
                                                else if (index < 15)
                                                {
                                                    cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
                                                }
                                                index++;
                                            }
                                        }



                                    }

                                }
                            }
                            else
                            {
                                if (row != null)
                                {
                                    var SName = row.GetCell(1);
                                    if (SName != null && SName.ToString().Trim() != "")
                                    {
                                        if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益 （或股东权益）合计" && SName.ToString().Trim() != "所有者权益（或股东权益）合计" && SName.ToString().Trim() != "负债和所有者权益总计")
                                        {
                                            var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
                                            if (Sel != null && Sel.Count == 1)
                                            {
                                                ICell cell = row.GetCell(2);
                                                cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
                                                if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
                                                {
                                                    if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                                                    {
                                                        ICell cell2 = row.GetCell(3);
                                                        cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
                                                    }

                                                    ICell cell3 = row.GetCell(4);
                                                    if (z == 1)
                                                    {
                                                        cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
                                                    }
                                                    else
                                                    {
                                                        cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                                                    }

                                                }
                                                else
                                                {
                                                    ICell cell2 = row.GetCell(3);
                                                    cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
                                                }

                                            }
                                            else if (Sel.Count > 1)
                                            {

                                                var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
                                                ICell cell = row.GetCell(2);
                                                if (OneArr.Count > 0)
                                                {
                                                    cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
                                                }


                                                if (!dt.Tables[z].TableName.Contains("现金流量表"))
                                                {

                                                    if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                                                    {
                                                        ICell cell2 = row.GetCell(3);
                                                        if (OneArr.Count > 0)
                                                        {
                                                            cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
                                                        }

                                                    }
                                                    ICell cell3 = row.GetCell(4);
                                                    if (OneArr.Count > 0)
                                                    {
                                                        if (z == 1)
                                                        {
                                                            cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
                                                        }
                                                        else
                                                        {
                                                            cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                                                        }
                                                    }

                                                }
                                                else
                                                {
                                                    ICell cell2 = row.GetCell(3);
                                                    if (OneArr.Count > 0)
                                                    {
                                                        cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
                                                    }
                                                    cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());

                                                }
                                            }
                                        }
                                    }
                                }

                            }
                        }
                    }
                    else
                    {
                        for (int j = 0; j < dt.Tables[z].Rows.Count; j++)
                        {
                            IRow row = sheet1.GetRow(j + i);
                            if (dt.Tables[z].TableName == "所有者权益变动表")
                            {
                                var SName = row.GetCell(0);

                                if (SName != null && SName.ToString().Trim() != "")
                                {
                                    var Sel = dt.Tables[z].Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();
                                    if (Sel != null && Sel.Count == 1)
                                    {
                                        int index = 1;
                                        foreach (var item in Sel[0].Table.Rows)
                                        {
                                            ICell cell = row.GetCell(index);
                                            if (index == 15)
                                            {
                                                cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
                                            }
                                            else if (index < 15)
                                            {
                                                cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
                                            }

                                            index++;
                                        }
                                    }
                                    else if (Sel.Count > 1)
                                    {

                                        var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();


                                        if (OneArr.Count > 0)
                                        {
                                            int index = 1;
                                            foreach (var item in Sel[0].Table.Rows)
                                            {
                                                ICell cell = row.GetCell(index);

                                                if (index == 15)
                                                {
                                                    cell.SetCellValue(Sel[0][index].ToString() == null ? "--" : Sel[0][index].ToString());
                                                }
                                                else if (index < 15)
                                                {
                                                    cell.SetCellValue(double.Parse(Sel[0][index].ToString()));
                                                }
                                                index++;
                                            }
                                        }



                                    }

                                }
                            }
                            else
                            {
                                if (row != null)
                                {
                                    var SName = row.GetCell(0);
                                    if (SName != null && SName.ToString().Trim() != "")
                                    {
                                        if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益合计" && SName.ToString().Trim() != "所有者权益合计" && SName.ToString().Trim() != "负债和所有者权益总计")
                                        {
                                            var Sel = dt.Tables[z].Select("Name like '" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "'").ToList();
                                            if (Sel != null && Sel.Count == 1)
                                            {
                                                ICell cell = row.GetCell(1);
                                                cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
                                                if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
                                                {
                                                    if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                                                    {
                                                        ICell cell2 = row.GetCell(2);
                                                        cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
                                                    }

                                                    ICell cell3 = row.GetCell(3);
                                                    if (z == 1)
                                                    {
                                                        cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
                                                    }
                                                    else
                                                    {
                                                        cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                                                    }

                                                }
                                                else
                                                {
                                                    ICell cell2 = row.GetCell(2);
                                                    cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
                                                }

                                            }
                                            else if (Sel.Count > 1)
                                            {
                                                int SelIndex = 0;
                                                if (SName.ToString().IndexOf(" ") >= 0)
                                                {
                                                    SelIndex= Sel[0][0].ToString().Contains("2")?0:1;
                                                }
                                                ICell cell = row.GetCell(1);
                                                cell.SetCellValue(double.Parse(Sel[SelIndex][2].ToString()));
                                                if (!dt.Tables[z].TableName.Contains("现金流量表"))
                                                {

                                                    if (Sel[SelIndex][0].ToString() != "所有者权益" && Sel[SelIndex][1].ToString() != "其中：优先股" && Sel[SelIndex][1].ToString() != "永续债")
                                                    {
                                                        ICell cell2 = row.GetCell(2);
                                                        if (Sel.Count > 0)
                                                        {
                                                            cell2.SetCellValue(Sel[SelIndex][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[SelIndex][3].ToString()));
                                                        }

                                                    }
                                                    ICell cell3 = row.GetCell(3);
                                                    if (Sel.Count > 0)
                                                    {
                                                        if (z == 1)
                                                        {
                                                            cell3.SetCellValue(Sel[SelIndex][5].ToString() == null ? "--" : Sel[SelIndex][5].ToString());
                                                        }
                                                        else
                                                        {
                                                            cell3.SetCellValue(Sel[SelIndex][4].ToString() == null ? "--" : Sel[SelIndex][4].ToString());
                                                        }
                                                    }

                                                }
                                                else
                                                {
                                                    ICell cell2 = row.GetCell(2);
                                                    if (Sel.Count > 0)
                                                    {
                                                        cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
                                                    }
                                                    cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());

                                                }
                                            }
                                            #region
                                            //if (Sel != null)
                                            //{

                                            //    ICell cell = row.GetCell(2);
                                            //    cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
                                            //    if (!dt.Tables[z].TableName.Equals("现金流量表") && !dt.Tables[z].TableName.Equals("现金流量表补充资料"))
                                            //    {
                                            //        if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                                            //        {
                                            //            ICell cell2 = row.GetCell(3);
                                            //            cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
                                            //        }

                                            //        ICell cell3 = row.GetCell(4);
                                            //        if (z == 1)
                                            //        {
                                            //            cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
                                            //        }
                                            //        else
                                            //        {
                                            //            cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                                            //        }

                                            //    }
                                            //    else
                                            //    {
                                            //        ICell cell2 = row.GetCell(3);
                                            //        cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
                                            //    }

                                            //}
                                            //else if (Sel.Count > 1)
                                            //{

                                            //    var OneArr = dt.Tables[z].Select("Name like '" + SName.ToString().Trim() + "'").ToList();
                                            //    ICell cell = row.GetCell(2);
                                            //    if (OneArr.Count > 0)
                                            //    {
                                            //        cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
                                            //    }


                                            //    if (!dt.Tables[z].TableName.Contains("现金流量表"))
                                            //    {

                                            //        if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                                            //        {
                                            //            ICell cell2 = row.GetCell(3);
                                            //            if (OneArr.Count > 0)
                                            //            {
                                            //                cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
                                            //            }

                                            //        }
                                            //        ICell cell3 = row.GetCell(4);
                                            //        if (OneArr.Count > 0)
                                            //        {
                                            //            if (z == 1)
                                            //            {
                                            //                cell3.SetCellValue(Sel[0][5].ToString() == null ? "--" : Sel[0][5].ToString());
                                            //            }
                                            //            else
                                            //            {
                                            //                cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                                            //            }
                                            //        }

                                            //    }
                                            //    else
                                            //    {
                                            //        ICell cell2 = row.GetCell(3);
                                            //        if (OneArr.Count > 0)
                                            //        {
                                            //            cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
                                            //        }
                                            //        cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());

                                            //    }
                                            //}
                                            #endregion
                                        }
                                    }
                                }

                            }

                        }
                    }
                }


                #endregion
                #region 直接导出
                //if (SheetName.Contains("所有者权益变动表"))
                //{
                //    for (int j = 0; j < MaxColumn; j++)
                //    {
                //        if (ReportType == "合并报表")
                //        {
                //            if (j == 0 || j == 15)
                //            {
                //                IRow row = sheet1.GetRow(i + 11);
                //                if (row != null)
                //                {
                //                    ICell cell = row.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 11);
                //                    ICell cell = row1.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }

                //            }
                //            else
                //            {
                //                IRow row = sheet1.GetRow(i + 11);
                //                if (row != null)
                //                {
                //                    ICell cell = row.GetCell(j);
                //                    if (cell != null)
                //                    {
                //                        row.RemoveCell(cell);
                //                    }
                //                    ICell cell1 = row.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell1.CellStyle = cellStyle;
                //                    cell1.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 11);

                //                    ICell cell = row1.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //            }
                //        }
                //        else
                //        {
                //            if (j == 0 || j == 13)
                //            {
                //                IRow row = sheet1.GetRow(i + 11);
                //                if (row != null)
                //                {
                //                    ICell cell = row.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 11);
                //                    ICell cell = row1.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }

                //            }
                //            else
                //            {
                //                IRow row = sheet1.GetRow(i + 11);
                //                if (row != null)
                //                {
                //                    ICell cell = row.GetCell(j);
                //                    if (cell != null)
                //                    {
                //                        row.RemoveCell(cell);
                //                    }
                //                    ICell cell1 = row.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell1.CellStyle = cellStyle;
                //                    cell1.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 11);

                //                    ICell cell = row1.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //            }
                //        }

                //    }
                //}
                //else
                //{
                //    for (int j = 0; j < MaxColumn; j++)
                //    {
                //        if (MaxColumn == 5)
                //        {
                //            if (j == 0 || j == 1 || j == 4)
                //            {
                //                IRow row = sheet1.GetRow(i + 7);
                //                if (row != null)
                //                {
                //                    ICell cell = row.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 7);
                //                    ICell cell = row1.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }

                //            }
                //            else
                //            {
                //                IRow row = sheet1.GetRow(i + 7);
                //                if (row != null)
                //                {
                //                    ICell cell = row.GetCell(j);
                //                    if (cell != null)
                //                    {
                //                        row.RemoveCell(cell);
                //                    }
                //                    ICell cell1 = row.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell1.CellStyle = cellStyle;
                //                    cell1.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 7);

                //                    ICell cell = row1.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //            }
                //        }
                //        else
                //        {
                //            if (j == 0 || j == 1 || j == 3)
                //            {
                //                IRow row = sheet1.GetRow(i + 7);
                //                if (row != null)
                //                {
                //                    ICell cell = row.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 7);
                //                    ICell cell = row1.CreateCell(j, CellType.String);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }

                //            }
                //            else
                //            {
                //                IRow row = sheet1.GetRow(i + 7);
                //                if (row != null)
                //                {
                //                    ICell cell = row.GetCell(j);
                //                    if (cell != null)
                //                    {
                //                        row.RemoveCell(cell);
                //                    }
                //                    ICell cell1 = row.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell1.CellStyle = cellStyle;
                //                    cell1.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //                else
                //                {
                //                    IRow row1 = sheet1.CreateRow(i + 7);

                //                    ICell cell = row1.CreateCell(j, CellType.Numeric);
                //                    ICellStyle cellStyle = workbook.CreateCellStyle();
                //                    cellStyle.BorderBottom = BorderStyle.Thin;
                //                    cellStyle.BorderRight = BorderStyle.Thin;
                //                    cellStyle.IsLocked = false;
                //                    cell.CellStyle = cellStyle;
                //                    cell.SetCellValue(dt.Rows[i][j].ToString());
                //                }
                //            }
                //        }



                //    }
                //}
                #endregion
                //}
                //var Num = SheetRow - dt.Rows.Count;
                //if (Num>0)
                //{
                //    for (int i = dt.Rows.Count; i <= SheetRow; i++)
                //    {
                //        IRow row = sheet1.GetRow(i + 7);
                //        for (int j = 0; j < MaxColumn; j++)
                //        {
                //            ICell cell = row.GetCell(j);
                //            if (cell!=null)
                //            {
                //                row.RemoveCell(cell);
                //            }

                //        }

                //    }
                //}
                //sheet1.ProtectSheet(SheetLockPassword);
                //ISheet sheetNote = workbook.GetSheet("填写说明");
                //if (sheetNote != null)
                //{
                //    sheetNote.ProtectSheet(SheetLockPassword);
                //}
                // work
                using (FileStream filess = File.OpenWrite(ReportFilePath))
                {
                    workbook1.Write(filess);
                }
            }


        }

        public static void GenerateReportExcel(string TempletFilePath, string ReportFilePath, string SheetName, DataTable dt, string ReportType, int MaxColumn)
        {
            using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
                #region 预设行数

                var SheetRow = 0;
                if (SheetName == "资产负债表")
                {
                    if (ReportType == "合并报表")
                    {
                        SheetName = "资产负债表-合并";
                        SheetRow = 136;
                    }
                    else
                    {
                        SheetName = "资产负债表-母公司";
                        SheetRow = 134;
                    }

                }
                else if (SheetName == "利润表")
                {
                    if (ReportType == "合并报表")
                    {
                        SheetName = "利润表-合并";
                        SheetRow = 87;
                    }
                    else
                    {
                        SheetName = "利润表-母公司";
                        SheetRow = 80;
                    }

                }
                else if (SheetName == "现金流量表")
                {
                    if (ReportType == "合并报表")
                    {
                        SheetName = "现金流量表-合并";
                        SheetRow = 66;
                    }
                    else
                    {
                        SheetName = "现金流量表-母公司";
                        SheetRow = 64;
                    }

                }
                else if (SheetName == "现金流量表补充资料")
                {
                    if (ReportType == "合并报表")
                    {
                        SheetName = "现金流量表补充资料-合并";
                    }
                    else
                    {
                        SheetName = "现金流量表补充资料-母公司";
                    }
                    SheetRow = 32;
                }
                else if (SheetName == "所有者权益变动表")
                {
                    if (ReportType == "合并报表")
                    {
                        SheetName = "所有者权益变动表-合并";
                    }
                    else
                    {
                        SheetName = "所有者权益变动表-母公司";
                    }
                    SheetRow = 28;
                }
                #endregion
                ISheet sheet1 = workbook.GetSheet(SheetName);
                sheet1.ForceFormulaRecalculation = false;
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    #region 按模板导出
                    //IRow row = sheet1.GetRow(i + 7);
                    //var SName = row.GetCell(1);
                    //if (SName.ToString().Trim() != null && SName.ToString().Trim() != "")
                    //{
                    //    if (SName.ToString().Trim() != "流动资产合计" && SName.ToString().Trim() != "非流动资产合计" && SName.ToString().Trim() != "资产总计" && SName.ToString().Trim() != "流动负债合计" && SName.ToString().Trim() != "非流动负债合计" && SName.ToString().Trim() != "负债总计" && SName.ToString().Trim() != "归属于母公司所有者权益 （或股东权益）合计" && SName.ToString().Trim() != "所有者权益（或股东权益）合计" && SName.ToString().Trim() != "负债和所有者权益（或股东权益）总计")
                    //    {
                    //        var Sel = dt.Select("Name like '%" + SName.ToString().Trim().Replace("%", "[%]").Replace("*", "[*]") + "%'").ToList();

                    //        if (Sel != null && Sel.Count == 1)
                    //        {

                    //            ICell cell = row.GetCell(2);
                    //            cell.SetCellValue(double.Parse(Sel[0][2].ToString()));
                    //            if (!SheetName.Contains("现金流量表"))
                    //            {
                    //                if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                    //                {
                    //                    ICell cell2 = row.GetCell(3);
                    //                    cell2.SetCellValue(Sel[0][3].ToString() == null ? double.Parse("0") : double.Parse(Sel[0][3].ToString()));
                    //                }

                    //                ICell cell3 = row.GetCell(4);
                    //                cell3.SetCellValue(Sel[0][4].ToString() == null ? "--" : Sel[0][4].ToString());
                    //            }
                    //            else
                    //            {
                    //                ICell cell2 = row.GetCell(3);
                    //                cell2.SetCellValue(Sel[0][3].ToString() == null ? "--" : Sel[0][3].ToString());
                    //            }

                    //        }
                    //        else if (Sel.Count > 1)
                    //        {

                    //            var OneArr = dt.Select("Name like '" + SName.ToString().Trim() + "'").ToList();
                    //            ICell cell = row.GetCell(2);
                    //            if (OneArr.Count > 0)
                    //            {
                    //                cell.SetCellValue(double.Parse(OneArr[0][2].ToString()));
                    //            }


                    //            if (!SheetName.Contains("现金流量表"))
                    //            {

                    //                if (Sel[0][0].ToString() != "所有者权益" && Sel[0][1].ToString() != "其中：优先股" && Sel[0][1].ToString() != "永续债")
                    //                {
                    //                    ICell cell2 = row.GetCell(3);
                    //                    if (OneArr.Count > 0)
                    //                    {
                    //                        cell2.SetCellValue(OneArr[0][3].ToString() == null ? double.Parse("0") : double.Parse(OneArr[0][3].ToString()));
                    //                    }

                    //                }
                    //                ICell cell3 = row.GetCell(4);
                    //                if (OneArr.Count > 0)
                    //                {
                    //                    cell3.SetCellValue(OneArr[0][4].ToString() == null ? "--" : OneArr[0][4].ToString());
                    //                }

                    //            }
                    //            else
                    //            {
                    //                ICell cell2 = row.GetCell(3);
                    //                if (OneArr.Count > 0)
                    //                {
                    //                    cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());
                    //                }
                    //                cell2.SetCellValue(OneArr[0][3].ToString() == null ? "--" : OneArr[0][3].ToString());

                    //            }
                    //        }

                    //    }
                    //}

                    #endregion
                    #region 直接导出
                    if (SheetName.Contains("所有者权益变动表"))
                    {
                        for (int j = 0; j < MaxColumn; j++)
                        {
                            if (ReportType == "合并报表")
                            {
                                if (j == 0 || j == 15)
                                {
                                    IRow row = sheet1.GetRow(i + 11);
                                    if (row != null)
                                    {
                                        ICell cell = row.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 11);
                                        ICell cell = row1.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }

                                }
                                else
                                {
                                    IRow row = sheet1.GetRow(i + 11);
                                    if (row != null)
                                    {
                                        ICell cell = row.GetCell(j);
                                        if (cell != null)
                                        {
                                            row.RemoveCell(cell);
                                        }
                                        ICell cell1 = row.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell1.CellStyle = cellStyle;
                                        cell1.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 11);

                                        ICell cell = row1.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                }
                            }
                            else
                            {
                                if (j == 0 || j == 13)
                                {
                                    IRow row = sheet1.GetRow(i + 11);
                                    if (row != null)
                                    {
                                        ICell cell = row.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 11);
                                        ICell cell = row1.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }

                                }
                                else
                                {
                                    IRow row = sheet1.GetRow(i + 11);
                                    if (row != null)
                                    {
                                        ICell cell = row.GetCell(j);
                                        if (cell != null)
                                        {
                                            row.RemoveCell(cell);
                                        }
                                        ICell cell1 = row.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell1.CellStyle = cellStyle;
                                        cell1.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 11);

                                        ICell cell = row1.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                }
                            }

                        }
                    }
                    else
                    {
                        for (int j = 0; j < MaxColumn; j++)
                        {
                            if (MaxColumn == 5)
                            {
                                if (j == 0 || j == 1 || j == 4)
                                {
                                    IRow row = sheet1.GetRow(i + 7);
                                    if (row != null)
                                    {
                                        ICell cell = row.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 7);
                                        ICell cell = row1.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }

                                }
                                else
                                {
                                    IRow row = sheet1.GetRow(i + 7);
                                    if (row != null)
                                    {
                                        ICell cell = row.GetCell(j);
                                        if (cell != null)
                                        {
                                            row.RemoveCell(cell);
                                        }
                                        ICell cell1 = row.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell1.CellStyle = cellStyle;
                                        cell1.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 7);

                                        ICell cell = row1.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                }
                            }
                            else
                            {
                                if (j == 0 || j == 1 || j == 3)
                                {
                                    IRow row = sheet1.GetRow(i + 7);
                                    if (row != null)
                                    {
                                        ICell cell = row.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 7);
                                        ICell cell = row1.CreateCell(j, CellType.String);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }

                                }
                                else
                                {
                                    IRow row = sheet1.GetRow(i + 7);
                                    if (row != null)
                                    {
                                        ICell cell = row.GetCell(j);
                                        if (cell != null)
                                        {
                                            row.RemoveCell(cell);
                                        }
                                        ICell cell1 = row.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell1.CellStyle = cellStyle;
                                        cell1.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                    else
                                    {
                                        IRow row1 = sheet1.CreateRow(i + 7);

                                        ICell cell = row1.CreateCell(j, CellType.Numeric);
                                        ICellStyle cellStyle = workbook.CreateCellStyle();
                                        cellStyle.BorderBottom = BorderStyle.Thin;
                                        cellStyle.BorderRight = BorderStyle.Thin;
                                        cellStyle.IsLocked = false;
                                        cell.CellStyle = cellStyle;
                                        cell.SetCellValue(dt.Rows[i][j].ToString());
                                    }
                                }
                            }



                        }
                    }
                    #endregion
                }
                var Num = SheetRow - dt.Rows.Count;
                if (Num > 0)
                {
                    for (int i = dt.Rows.Count; i <= SheetRow; i++)
                    {
                        IRow row = sheet1.GetRow(i + 7);
                        for (int j = 0; j < MaxColumn; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                row.RemoveCell(cell);
                            }

                        }

                    }
                }
                //sheet1.ProtectSheet(SheetLockPassword);
                ISheet sheetNote = workbook.GetSheet("填写说明");
                if (sheetNote != null)
                {
                    sheetNote.ProtectSheet(SheetLockPassword);
                }

                using (FileStream filess = File.OpenWrite(ReportFilePath))
                {
                    workbook.Write(filess);
                }
            }
        }
        #region 
        //public static void CopyCellStyle(IWorkbook wb, ICellStyle fromStyle, ICellStyle toStyle)
        //{
        //    toStyle.Alignment = fromStyle.Alignment;
        //    //边框和边框颜色
        //    toStyle.BorderBottom = fromStyle.BorderBottom;
        //    toStyle.BorderLeft = fromStyle.BorderLeft;
        //    toStyle.BorderRight = fromStyle.BorderRight;
        //    toStyle.BorderTop = fromStyle.BorderTop;
        //    toStyle.TopBorderColor = fromStyle.TopBorderColor;
        //    toStyle.BottomBorderColor = fromStyle.BottomBorderColor;
        //    toStyle.RightBorderColor = fromStyle.RightBorderColor;
        //    toStyle.LeftBorderColor = fromStyle.LeftBorderColor;
        //    //背景和前景
        //    toStyle.FillBackgroundColor = fromStyle.FillBackgroundColor;
        //    toStyle.FillForegroundColor = fromStyle.FillForegroundColor;
        //    toStyle.DataFormat = fromStyle.DataFormat;
        //    toStyle.FillPattern = fromStyle.FillPattern;
        //    //toStyle.Hidden=fromStyle.Hidden;
        //    toStyle.IsHidden = fromStyle.IsHidden;
        //    toStyle.Indention = fromStyle.Indention;//首行缩进
        //    toStyle.IsLocked = fromStyle.IsLocked;
        //    toStyle.Rotation = fromStyle.Rotation;//旋转
        //    toStyle.VerticalAlignment = fromStyle.VerticalAlignment;
        //    toStyle.WrapText = fromStyle.WrapText;
        //    toStyle.SetFont(fromStyle.GetFont(wb));
        //}
        ///// <summary>
        ///// 复制表
        ///// </summary>
        ///// <param name="wb"></param>
        ///// <param name="fromSheet"></param>
        ///// <param name="toSheet"></param>
        ///// <param name="copyValueFlag"></param>
        //public static void CopySheet(IWorkbook wb, ISheet fromSheet, ISheet toSheet, bool copyValueFlag)
        //{
        //    //合并区域处理
        //    MergerRegion(fromSheet, toSheet);
        //    System.Collections.IEnumerator rows = fromSheet.GetRowEnumerator();
        //    while (rows.MoveNext())
        //    {
        //        IRow row = null;
        //        if (wb is HSSFWorkbook)
        //            row = rows.Current as HSSFRow;
        //        else
        //            row = rows.Current as NPOI.XSSF.UserModel.XSSFRow;
        //        IRow newRow = toSheet.CreateRow(row.RowNum);
        //        CopyRow(wb, row, newRow, copyValueFlag);
        //    }
        //}
        ///// <summary>
        ///// 复制行
        ///// </summary>
        ///// <param name="wb"></param>
        ///// <param name="fromRow"></param>
        ///// <param name="toRow"></param>
        ///// <param name="copyValueFlag"></param>
        //public static void CopyRow(IWorkbook wb, IRow fromRow, IRow toRow, bool copyValueFlag)
        //{
        //    System.Collections.IEnumerator cells = fromRow.GetEnumerator();//.GetRowEnumerator();
        //    toRow.Height = fromRow.Height;
        //    while (cells.MoveNext())
        //    {
        //        ICell cell = null;
        //        //ICell cell = (wb is HSSFWorkbook) ? cells.Current as HSSFCell : cells.Current as NPOI.XSSF.UserModel.XSSFCell;
        //        if (wb is HSSFWorkbook)
        //            cell = cells.Current as HSSFCell;
        //        else
        //            cell = cells.Current as NPOI.XSSF.UserModel.XSSFCell;
        //        ICell newCell = toRow.CreateCell(cell.ColumnIndex);
        //        CopyCell(wb, cell, newCell, copyValueFlag);
        //    }
        //}
        ///// <summary>
        ///// 复制原有sheet的合并单元格到新创建的sheet
        ///// </summary>
        ///// <param name="fromSheet"></param>
        ///// <param name="toSheet"></param>
        //public static void MergerRegion(ISheet fromSheet, ISheet toSheet)
        //{
        //    int sheetMergerCount = fromSheet.NumMergedRegions;
        //    for (int i = 0; i < sheetMergerCount; i++)
        //    {
        //        //Region mergedRegionAt = fromSheet.GetMergedRegion(i); //.MergedRegionAt(i);
        //        //CellRangeAddress[] cra = new CellRangeAddress[1];
        //        //cra[0] = fromSheet.GetMergedRegion(i);
        //        //Region[] rg = Region.ConvertCellRangesToRegions(cra);
        //        toSheet.AddMergedRegion(fromSheet.GetMergedRegion(i));
        //    }
        //}
        ///// <summary>
        ///// 复制单元格
        ///// </summary>
        ///// <param name="wb"></param>
        ///// <param name="srcCell"></param>
        ///// <param name="distCell"></param>
        ///// <param name="copyValueFlag"></param>
        //public static void CopyCell(IWorkbook wb, ICell srcCell, ICell distCell, bool copyValueFlag)
        //{
        //    ICellStyle newstyle = wb.CreateCellStyle();
        //    CopyCellStyle(wb, srcCell.CellStyle, newstyle);
        //    //样式
        //    distCell.CellStyle = newstyle;
        //    //评论
        //    if (srcCell.CellComment != null)
        //    {
        //        distCell.CellComment = srcCell.CellComment;
        //    }
        //    // 不同数据类型处理
        //    CellType srcCellType = srcCell.CellType;
        //    distCell.SetCellType(srcCellType);
        //    if (copyValueFlag)
        //    {
        //        if (srcCellType == CellType.Numeric)
        //        {
        //            if (HSSFDateUtil.IsCellDateFormatted(srcCell))
        //            {
        //                distCell.SetCellValue(srcCell.DateCellValue);
        //            }
        //            else
        //            {
        //                distCell.SetCellValue(srcCell.NumericCellValue);
        //            }
        //        }
        //        else if (srcCellType == CellType.String)
        //        {
        //            distCell.SetCellValue(srcCell.RichStringCellValue);
        //        }
        //        else if (srcCellType == CellType.Blank)
        //        {
        //            // nothing21
        //        }
        //        else if (srcCellType == CellType.Boolean)
        //        {
        //            distCell.SetCellValue(srcCell.BooleanCellValue);
        //        }
        //        else if (srcCellType == CellType.Error)
        //        {
        //            distCell.SetCellErrorValue(srcCell.ErrorCellValue);
        //        }
        //        else if (srcCellType == CellType.Formula)
        //        {
        //            distCell.SetCellFormula(srcCell.CellFormula);
        //        }
        //        else
        //        { // nothing29
        //        }
        //    }
        //}
        #endregion

        public static void GenerateReportTemplate(string TempletFilePath, string ReportFilePath, string SheetName)
        {
            using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);

                ISheet sheet1 = workbook.GetSheet(SheetName);
                //sheet1.ProtectSheet(SheetLockPassword);
                ISheet sheetNote = workbook.GetSheet("填写说明");
                if (sheetNote != null)
                {
                    sheetNote.ProtectSheet(SheetLockPassword);
                }
                using (FileStream filess = File.OpenWrite(ReportFilePath))
                {
                    workbook.Write(filess);
                }
            }
        }
        #endregion


        public static void GenerateFiletoExcel(string TempletFilePath, string ReportFilePath, string SheetName, string SheetNameCashFlow, DataTable dt, DataTable dtCashFlow, int MaxColumn)
        {
            using (FileStream ExcelFileStream = new FileStream(TempletFilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(ExcelFileStream);
                ISheet sheet1 = workbook.GetSheet(SheetName);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < MaxColumn; j++)
                    {
                        sheet1.GetRow(i + 2).GetCell(j).SetCellValue(dt.Rows[i][j].ToString().Replace("0:00:00", ""));
                    }
                }

                ISheet sheet2 = workbook.GetSheet(SheetNameCashFlow);
                for (int i = 0; i < dtCashFlow.Rows.Count; i++)
                {
                    for (int j = 0; j < dtCashFlow.Columns.Count; j++)
                    {
                        if ((i < 2 || i == dtCashFlow.Rows.Count - 1) || j <= 1)
                        {
                            sheet2.GetRow(i + 1).GetCell(j)
                                .SetCellValue(dtCashFlow.Rows[i][j].ToString().Replace("0:00:00", ""));
                        }
                        else
                        {
                            double Result = 0;
                            double.TryParse(dtCashFlow.Rows[i][j].ToString(), out Result);
                            ICellStyle cellStyle = workbook.CreateCellStyle();
                            IDataFormat format = workbook.CreateDataFormat();
                            cellStyle.BorderBottom = BorderStyle.Thin;
                            cellStyle.BorderRight = BorderStyle.Thin;
                            cellStyle.BorderLeft = BorderStyle.Thin;
                            cellStyle.IsLocked = false;
                            if (dtCashFlow.Rows[i][0].ToString().IndexOf("数量") < 0)
                            {
                                cellStyle.DataFormat = format.GetFormat("#,##0.00");
                            }
                            else
                            {
                                cellStyle.DataFormat = format.GetFormat("0.00");
                            }
                            sheet2.GetRow(i + 1).GetCell(j).CellStyle = cellStyle;
                            sheet2.GetRow(i + 1).GetCell(j).SetCellValue(Result);
                        }
                    }
                }
                sheet1.ProtectSheet(SheetLockPassword);
                sheet2.ProtectSheet(SheetLockPassword);
                ISheet sheetNote = workbook.GetSheet("填写说明");
                if (sheetNote != null)
                {
                    sheetNote.ProtectSheet(SheetLockPassword);
                }

                using (FileStream filess = File.OpenWrite(ReportFilePath))
                {
                    workbook.Write(filess);
                }
            }
        }

        public static double PMTFunction(double Rate, double Year, double Cash)
        {
            double Result = 0;

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            IRow row1 = sheet1.CreateRow(0);
            ICell cell1 = row1.CreateCell(0);
            ICell cell2 = row1.CreateCell(1);
            ICell cell3 = row1.CreateCell(2);
            ICell cellSum = row1.CreateCell(3);

            cell1.SetCellValue(Rate);
            cell2.SetCellValue(Year);
            cell3.SetCellValue(Cash);
            cellSum.SetCellFormula("PMT(A1,B1,C1)");

            HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
            cellSum = e.EvaluateInCell(cellSum);
            Result = cellSum.NumericCellValue * (-1);
            return Result;
        }

        //public static double LOGNORMDISTFunction(double BaseAverage, double BaseSqrt)
        //{
        //    double Result = 0;

        //    //IWorkbook workbook = new HSSFWorkbook();
        //    //ISheet sheet1 = workbook.CreateSheet("Sheet1");
        //    //IRow row1 = sheet1.CreateRow(0);
        //    //ICell cell1 = row1.CreateCell(0);
        //    //ICell cell2 = row1.CreateCell(1);
        //    //ICell cell3 = row1.CreateCell(2);
        //    //ICell cellSum = row1.CreateCell(3);

        //    //cell1.SetCellValue(1);
        //    //cell2.SetCellValue(BaseAverage);
        //    //cell3.SetCellValue(BaseSqrt);
        //    //cellSum.SetCellFormula("LOGNORMDIST(A1,B1,C1)");

        //    //HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
        //    //cellSum = e.EvaluateInCell(cellSum);
        //    //Result = cellSum.NumericCellValue ;
        //    Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //    Result = excel.WorksheetFunction.LogNormDist(1.0, BaseAverage, BaseSqrt);
        //    return Result;
        //}

    }
}