.

leo

2022-11-10 ea4a4d70dd12d33414be4739cefb03fe3a0f9cf0

 CommonHelper/Excel/NPOIHelper.cs

@@ -19,6 +19,7 @@
using DataTable = System.Data.DataTable;
using IFont = NPOI.SS.UserModel.IFont;


namespace CommonHelper
{
    /// <summary>
@@ -77,7 +78,7 @@
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void FRMExportExcel(DataTable dt, string file,string title)
        public static void FRMExportExcel(DataTable dt, string file, string title)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
@@ -87,16 +88,16 @@

            //表头  
            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);
            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.Color = new HSSFColor.Pink().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
            font.IsItalic = false; //斜体
            font.FontHeightInPoints = 14;//字体大小
            font.Boldweight = short.MaxValue;//字体加粗
@@ -110,10 +111,10 @@
            #endregion
            #region 内容样式 1 右对齐 千位符保留两位小数
            ICellStyle style1 = workbook.CreateCellStyle();
            IFont font1 = workbook.CreateFont(); 
            IFont font1 = workbook.CreateFont();
            var format1 = workbook.CreateDataFormat();
            font1.FontName = "微软雅黑"; 
            font1.IsItalic = false; 
            font1.FontName = "微软雅黑";
            font1.IsItalic = false;
            font1.FontHeightInPoints = 12;
            font1.Boldweight = short.MaxValue;
            style1.SetFont(font1);
@@ -125,15 +126,15 @@
            style1.WrapText = true;//设置自动换行
            style1.Alignment = HorizontalAlignment.Right;
            #endregion
            

            #region 内容样式2 左对齐
            ICellStyle style2 = workbook.CreateCellStyle();
            IFont font2 = workbook.CreateFont(); 
            font2.FontName = "微软雅黑"; 
            font2.IsItalic = false; 
            IFont font2 = workbook.CreateFont();
            font2.FontName = "微软雅黑";
            font2.IsItalic = false;
            font2.FontHeightInPoints = 12;
            font2.Boldweight = short.MaxValue;
            style2.SetFont(font2); 
            style2.SetFont(font2);
            style2.BorderTop = BorderStyle.Thin;
            style2.BorderBottom = BorderStyle.Thin;
            style2.BorderLeft = BorderStyle.Thin;
@@ -200,9 +201,9 @@
                {
                    continue;
                }
               

                int OldHg = row1.Height;
                for (int z= 0; z < dt.Columns.Count; z++)
                for (int z = 0; z < dt.Columns.Count; z++)
                {
                    if (row1.GetCell(z) == null)
                    {
@@ -229,8 +230,8 @@
                //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("贷币", "货币");

                    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)
@@ -241,11 +242,11 @@
                            }
                        }
                    }
                    if (dt.Columns.Count==4&&i == 0)
                    if (dt.Columns.Count == 4 && i == 0)
                    {
                        if (dt.Rows[i][j].ToString() == "")
                        {
                            if (j==0)
                            if (j == 0)
                            {
                                dt.Rows[i][j] = "项目";
                            }
@@ -259,11 +260,11 @@
                            }
                        }
                    }
                    

                    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("总计"))
                        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;
@@ -272,7 +273,7 @@
                        {
                            cell.CellStyle = style2;
                        }
                            

                    }
                    else
                    {
@@ -284,10 +285,10 @@
                        {
                            cell.CellStyle = style1;
                        }
                       

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

                }
            }

@@ -1300,7 +1301,7 @@
            }
        }
        #region 导出报表
        public static void GenerateReportExcel(string TempletFilePath, string ReportFilePath, string SheetName, DataTable dt,string ReportType, int MaxColumn)
        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))
            {
@@ -1336,7 +1337,7 @@
                    }

                }
                else if(SheetName== "现金流量表")
                else if (SheetName == "现金流量表")
                {
                    if (ReportType == "合并报表")
                    {
@@ -1350,9 +1351,9 @@
                    }

                }
                else if (SheetName=="现金流量表补充资料")
                else if (SheetName == "现金流量表补充资料")
                {
                    if (ReportType== "合并报表")
                    if (ReportType == "合并报表")
                    {
                        SheetName = "现金流量表补充资料-合并";
                    }
@@ -1362,7 +1363,7 @@
                    }
                    SheetRow = 32;
                }
                else if (SheetName== "所有者权益变动表")
                else if (SheetName == "所有者权益变动表")
                {
                    if (ReportType == "合并报表")
                    {
@@ -1463,7 +1464,7 @@
                    {
                        for (int j = 0; j < MaxColumn; j++)
                        {
                            if (ReportType=="合并报表")
                            if (ReportType == "合并报表")
                            {
                                if (j == 0 || j == 15)
                                {
@@ -1583,8 +1584,8 @@
                                    }
                                }
                            }
                                
                         }

                        }
                    }
                    else
                    {
@@ -1718,7 +1719,7 @@
                    #endregion
                }
                var Num = SheetRow - dt.Rows.Count;
                if (Num>0)
                if (Num > 0)
                {
                    for (int i = dt.Rows.Count; i <= SheetRow; i++)
                    {
@@ -1726,11 +1727,11 @@
                        for (int j = 0; j < MaxColumn; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell!=null)
                            if (cell != null)
                            {
                                row.RemoveCell(cell);
                            }
                            

                        }

                    }
@@ -1753,7 +1754,7 @@
            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("填写说明");
@@ -1879,5 +1880,6 @@
        //    return Result;
        //}


    }
}