标准版初始化

lg

2025-11-20 b98df034707a61fd2857dbccd5a232605076ca59

 WebApi/Gs.Toolbox/ExcelHelper.cs

@@ -1,12 +1,14 @@
using System.Collections;
using System.Data;
using System.Text;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;
using System.Security.Cryptography.Xml;
using System.Text;
using System.Timers;


namespace Gs.Toolbox;
@@ -176,13 +178,13 @@
    {
        var roct = aryHeader.Count;
        IWorkbook workbook = new HSSFWorkbook();
        var sheet = workbook.CreateSheet();
        //取得列宽
        var arrColWidth = new int[dtSource.Columns.Count];
        foreach (DataColumn item in dtSource.Columns)
            arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
                .GetBytes(item.ColumnName).Length;
        if (fixW <= 0)
        {
            for (var i = 0; i < dtSource.Rows.Count; i++)
                for (var j = 0; j < dtSource.Columns.Count; j++)
                {
@@ -190,59 +192,80 @@
                        .GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
                }

        var cusRow = sheet.CreateRow(0);
        var _rr = 0;
        foreach (string _hh in aryHeader)
        {
            cusRow = sheet.CreateRow(_rr);
            cusRow.CreateCell(0).SetCellValue(_hh);
            //cellRangAddress参数说明：起始行，结束行，起始列，结束列
            sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
                dtSource.Columns.Count - 1));
            // sheet.AddMergedRegion(new Region(0, 0, _rr, dtSource.Columns.Count - 1));
            _rr++;
        }

        //填充表头
        var headerRow = sheet.CreateRow(roct);
        var headStyle = workbook.CreateCellStyle();
        headStyle.Alignment = HorizontalAlignment.Center;
        headStyle.FillPattern = FillPattern.SolidForeground;
        headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
        var font = workbook.CreateFont();
        font.Color = HSSFColor.White.Index;
        font.FontHeightInPoints = 12;
        font.Boldweight = 700;
        headStyle.SetFont(font);
        foreach (DataColumn column in dtSource.Columns)
        const int maxRowsPerXlsSheet = 65535; // HSSF 的单表行数上限（索引从0）
        int headerRowsCount = roct + 1; // aryHeader 行数 + 列头行
        int rowsPerSheet = maxRowsPerXlsSheet - headerRowsCount;
        if (rowsPerSheet <= 0) rowsPerSheet = 1;

        int totalRows = dtSource.Rows.Count;
        int sheetIndex = 0;
        ISheet sheet = null;
        ICellStyle headStyle = null;

        // 分片写入，每片写入 header + 数据
        for (int startRow = 0; startRow < totalRows; startRow += rowsPerSheet)
        {
            headerRow.CreateCell(column.Ordinal)
                .SetCellValue(column.ColumnName);
            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
            sheet.SetColumnWidth(column.Ordinal,
                (arrColWidth[column.Ordinal] + 6) * 256);
            sheet = workbook.CreateSheet("Sheet" + (sheetIndex + 1));

            // 创建并应用列宽（每个 sheet 都需要）
            for (int c = 0; c < dtSource.Columns.Count; c++)
            {
                sheet.SetColumnWidth(c, (arrColWidth[c] + 6) * 256);
            }

            // 写入 aryHeader（多行合并显示）
            var cusRow = sheet.CreateRow(0);
            var _rr = 0;
            foreach (string _hh in aryHeader)
            {
                cusRow = sheet.CreateRow(_rr);
                cusRow.CreateCell(0).SetCellValue(_hh);
                sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
                    dtSource.Columns.Count - 1));
                _rr++;
            }

            // 填充表头
            var headerRow = sheet.CreateRow(roct);
            headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.FillPattern = FillPattern.SolidForeground;
            headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
            var font = workbook.CreateFont();
            font.Color = HSSFColor.White.Index;
            font.FontHeightInPoints = 12;
            font.Boldweight = 700;
            headStyle.SetFont(font);
            for (int colIdx = 0; colIdx < dtSource.Columns.Count; colIdx++)
            {
                headerRow.CreateCell(colIdx).SetCellValue(dtSource.Columns[colIdx].ColumnName);
                headerRow.GetCell(colIdx).CellStyle = headStyle;
            }

            // 写入数据段
            int endRow = Math.Min(startRow + rowsPerSheet, totalRows);
            for (int i = startRow; i < endRow; i++)
            {
                var dataRow = sheet.CreateRow((i - startRow) + roct + 1);
                for (var j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j)
                        .SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }

            sheetIndex++;
        }

        //sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //首行筛选
        // if (fixW <= 0)
        sheet.CreateFreezePane(dtSource.Columns.Count,
            1 + aryHeader.Count); //首行冻结
        //填充内容
        var dataRow = sheet.CreateRow(roct + 1);
        for (var i = 0; i < dtSource.Rows.Count; i++)
        // 填充 footer（只写在最后一个 sheet 上）
        if (aryFotter != null && aryFotter.Count > 0 && sheet != null)
        {
            dataRow = sheet.CreateRow(i + roct + 1);
            for (var j = 0; j < dtSource.Columns.Count; j++)
                dataRow.CreateCell(j)
                    .SetCellValue(dtSource.Rows[i][j].ToString());
        }

        //填充footer
        if (aryFotter != null && aryFotter.Count > 0)
        {
            var _rot = roct + dtSource.Rows.Count + 2;
            var ftRow = sheet.CreateRow(_rot);
            var lastSheet = sheet;
            var _rot = roct + (totalRows - ((sheetIndex - 1) * rowsPerSheet)) + 1;
            if (_rot <= roct) _rot = roct + 1;
            var ftRow = lastSheet.CreateRow(_rot);
            var _ftidx = 0;
            var _ftw = dtSource.Columns.Count / 2;
            foreach (string _hh in aryFotter)
@@ -258,16 +281,14 @@
                    ftStyle.BorderBottom = BorderStyle.Thin;
                else
                    ftStyle.BorderBottom = BorderStyle.None;
                ftRow = sheet.CreateRow(_rot);

                ftRow = lastSheet.CreateRow(_rot);
                ftRow.CreateCell(0).SetCellValue(_hh);
                ftRow.GetCell(0).CellStyle = ftStyle;
                //cellRangAddress参数说明：起始行，结束行，起始列，结束列
                sheet.AddMergedRegion(
                    new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
                ftRow.CreateCell(_ftw)
                    .SetCellValue(aryFotter2[_ftidx].ToString());
                lastSheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
                ftRow.CreateCell(_ftw).SetCellValue(aryFotter2[_ftidx].ToString());
                ftRow.GetCell(_ftw).CellStyle = ftStyle;
                sheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
                lastSheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
                    dtSource.Columns.Count - 1));
                for (var c = 0; c < dtSource.Columns.Count; c++)
                {
@@ -280,7 +301,7 @@
            }
        }

        //保存
        // 保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,
@@ -360,13 +381,15 @@
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);

        style.WrapText = true; // 设置自动换行
        styleHeader.WrapText = true; // 设置自动换行
        style12.WrapText = true; // 设置自动换行
        // 设置列宽（6列）
        sheet.SetColumnWidth(0, 4000);  // 第一列稍宽
        sheet.SetColumnWidth(1, 3000);
        sheet.SetColumnWidth(1, 4000);
        sheet.SetColumnWidth(2, 3000);
        sheet.SetColumnWidth(3, 3000);
        sheet.SetColumnWidth(4, 3000);
        sheet.SetColumnWidth(4, 4000);
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        // 创建20行
@@ -429,16 +452,134 @@
        //从第7行开始，就是循环数据库
        int _idx = 7;
        DataTable tb1 = dtSource.Tables[1];
        string dd = "";
        bool _blPscs = false;
        bool _blGjyzx = false;
        foreach (DataRow rrr in tb1.Rows)
        {
            //增加品水测试标题
            if (_blPscs == false && rrr["项目"].ToString() == "品水测试")
            {
                IRow row700 = sheet.CreateRow(_idx);
                for (int i = 0; i < ary.Length; i++)
                {
                    ICell cell700 = row700.CreateCell(i);
                    cell700.CellStyle = style;
                    if (i == 0)
                        cell700.SetCellValue(rrr["项目"].ToString());
                    if (i == 1)
                        cell700.SetCellValue("温度");
                    if (i == 3)
                        cell700.SetCellValue("流量");
                    if (i == 5)
                        cell700.SetCellValue("是否有异味");
                    if (i == 6)
                        cell700.SetCellValue("检验结果");
                }
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
                _idx++;
                _blPscs = true;
            }
            //增加标题
            if (_blGjyzx == false && rrr["项目"].ToString() == "关键部件一致性")
            {
                IRow row700 = sheet.CreateRow(_idx);
                for (int i = 0; i < ary.Length; i++)
                {
                    ICell cell700 = row700.CreateCell(i);
                    cell700.CellStyle = style;
                    if (i == 0)
                        cell700.SetCellValue(rrr["项目"].ToString());
                    if (i == 1)
                        cell700.SetCellValue("关键名称");
                    if (i == 2)
                        cell700.SetCellValue("生产日期");
                    if (i == 3)
                        cell700.SetCellValue("使用数量");
                    if (i == 4)
                        cell700.SetCellValue("认证信息");
                    if (i == 5)
                        cell700.SetCellValue("是否符合CCC一致性");
                    if (i == 6)
                        cell700.SetCellValue("操作");
                }
                _idx++;
                _blGjyzx = true;
            }

            IRow row7 = sheet.CreateRow(_idx);
            row7.HeightInPoints = rowHeight;
            string _tmphb = rrr["项目合并行数"].ToString();
            string _tmp = "";
            if (dd == rrr["项目"].ToString())
                _tmp = "";
            else
                _tmp = rrr["项目"].ToString();
            dd = rrr["项目"].ToString();
            for (int i = 0; i < ary.Length; i++)
            {
                ICell cell7 = row7.CreateCell(i);
                cell7.CellStyle = style;
                cell7.SetCellValue(rrr[ary[i]].ToString());

                if (i == 0)
                {
                    cell7.SetCellValue(_tmp);
                    if (!string.IsNullOrEmpty(_tmp) && int.Parse(_tmphb) > 1)
                    {
                        if (rrr["项目"].ToString() == "品水测试" || rrr["项目"].ToString() == "关键部件一致性")
                            sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx + int.Parse(_tmphb) - 1, 0, 0));
                        else
                            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + int.Parse(_tmphb) - 1, 0, 0));
                    }
                }
                else
                {
                    switch (rrr["项目"].ToString())
                    {
                        case "制热性能":
                            // cell7.SetCellValue(rrr["制热性能table"].ToString());
                            string ddd = rrr["制热性能table"].ToString();
                            string ccc = getTableToString(ddd);
                            cell7.SetCellValue(ccc);
                            row7.HeightInPoints = rowHeight;
                            if (i == ary.Length - 1)
                                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 6));
                            break;
                        case "品水测试":
                            //度数
                            if (i == 1)
                                cell7.SetCellValue(rrr["技术/品质要求"].ToString());
                            if (i == 3)
                                cell7.SetCellValue(rrr["品水测试流量"].ToString());
                            if (i == 5)
                                cell7.SetCellValue(rrr["是否有异味"].ToString());
                            if (i == 6)
                                cell7.SetCellValue(rrr["检验结果"].ToString());
                            if (i == ary.Length - 1)
                            {
                                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
                                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
                            }
                            break;
                        case "关键部件一致性":
                            if (i == 1)
                                cell7.SetCellValue(rrr["技术/品质要求"].ToString());
                            if (i == 2)
                                cell7.SetCellValue(rrr["生产日期"].ToString());
                            if (i == 3)
                                cell7.SetCellValue(rrr["使用数量"].ToString());
                            if (i == 4)
                                cell7.SetCellValue(rrr["认证信息"].ToString());
                            if (i == 5)
                                cell7.SetCellValue(rrr["是否符合CCC一致性"].ToString());
                            //if (i == 6)
                            //    cell7.SetCellValue(rrr["检验结果"].ToString());
                            break;
                        default:
                            cell7.SetCellValue(rrr[ary[i]].ToString());
                            break;
                    }
                }
            }
            _idx++;
        }
@@ -487,28 +628,9 @@
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 6));
        setCellVal(sheet, _idx, 0, "首件填写,来料检验");
        setCellVal(sheet, _idx, 0, "首件填写,成品检验");
        //****合并好后开始赋值end**************

        // 5. 第8到10行中的第一列应被合并
        //sheet.AddMergedRegion(new CellRangeAddress(7, 9, 0, 0));

        //// 6. 第17，18行中的第一列合并
        //sheet.AddMergedRegion(new CellRangeAddress(16, 17, 0, 0));

        //// 7. 第17行中的2，3，4，5列合并
        //sheet.AddMergedRegion(new CellRangeAddress(16, 16, 1, 4));

        //// 8. 第18行中的，2，3列合并，4，5，6列合并
        //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 1, 2));
        //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 3, 5));

        //// 9. 第19行中的，列2，3，4合并
        //sheet.AddMergedRegion(new CellRangeAddress(18, 18, 1, 3));

        //// 10. 第20行中的列全部合并
        //sheet.AddMergedRegion(new CellRangeAddress(19, 19, 0, 5));

        getRowHeight(_idx, sheet, maxCols, "XlsFontXj");
        //保存
        using (var ms = new MemoryStream())
        {
@@ -531,6 +653,8 @@
    /// <param name="dtSource"></param>
    public static void ExportIqc(DataSet dtSource, string strFileName)
    {
        string isHg = "";

        //行高
        int rowHeight = 25;
        //总列
@@ -543,6 +667,12 @@
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);
        style.WrapText = true; // 设置自动换行
        styleHeader.WrapText = true; // 设置自动换行
        style12.WrapText = true; // 设置自动换行
        //style.ShrinkToFit = true;
        //styleHeader.ShrinkToFit = true;
        //style12.ShrinkToFit = true;

        // 设置列宽（6列）
        sheet.SetColumnWidth(0, 3000);  // 第一列稍宽
@@ -552,7 +682,7 @@
        sheet.SetColumnWidth(4, 3000);
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        sheet.SetColumnWidth(7, 3000);
        sheet.SetColumnWidth(7, 3500);
        // 创建9行
        for (int rowIndex = 0; rowIndex <= 8; rowIndex++)
        {
@@ -574,6 +704,7 @@
        //这是表头
        DataTable tb0 = dtSource.Tables[0];
        DataRow row0 = tb0.Rows[0];
        isHg = row0["zhpd"].ToString();
        //第一行列合并
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 7));
@@ -625,9 +756,9 @@
        setCellVal(sheet, 6, 6, "不合格数");
        setCellVal(sheet, 6, 7, row0["bhgs"].ToString());
        setCellVal(sheet, 7, 0, "判定Ac/Re");
        setCellVal(sheet, 7, 1, "0/1");
        setCellVal(sheet, 7, 2, "0/1");
        setCellVal(sheet, 7, 3, "0/1");
        setCellVal(sheet, 7, 1, row0["cr"].ToString());
        setCellVal(sheet, 7, 2, row0["ma"].ToString());
        setCellVal(sheet, 7, 3, row0["mi"].ToString());
        setCellVal(sheet, 8, 0, "项目");
        setCellVal(sheet, 8, 1, "质量要求");
        setCellVal(sheet, 8, 6, "检验记录");
@@ -658,10 +789,11 @@
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell = row8.CreateCell(colIndex);
            cell.CellStyle = style12;
            cell.CellStyle = style;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 7));
        setCellVal(sheet, _idx, 0, "备注：");
        setCellVal(sheet, _idx, 1, row0["iqcRemark"].ToString());
        _idx++;
        //综合判定
        for (int i = 0; i < 2; i++)
@@ -699,28 +831,36 @@
        setCellVal(sheet, _idx - 2, 0, "审核意见：");
        setCellVal(sheet, _idx - 2, 1, row0["shyj"].ToString());
        setCellVal(sheet, _idx - 1, 1, row0["shyjqz"].ToString());
        string[] ary = { "采购部意见", "销售部意见", "技术部意见", "生产部意见", "品质部意见" };
        for (int i = 0; i < ary.Length; i++)

        //如果是合格件，那下面不要了begin
        if (isHg != "Y:合格")
        {
            IRow row9 = sheet.CreateRow(_idx);
            row9.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            string[] ary = { "采购部意见", "销售部意见", "技术部意见", "生产部意见", "品质部意见" };
            for (int i = 0; i < ary.Length; i++)
            {
                ICell cell = row9.CreateCell(colIndex);
                cell.CellStyle = style;
                IRow row9 = sheet.CreateRow(_idx);
                row9.HeightInPoints = rowHeight;
                for (int colIndex = 0; colIndex < maxCols; colIndex++)
                {
                    ICell cell = row9.CreateCell(colIndex);
                    cell.CellStyle = style;
                }
                // sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 1));
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 7));
                _idx++;
            }
            // sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 7));
            _idx++;
            setCellVal(sheet, _idx - ary.Length, 0, "不合格评审：");
            sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1, 0, 0));
            for (int i = 0; i < ary.Length; i++)
            {
                setCellVal(sheet, _idx - i - 1, 1, ary[i]);
                setCellVal(sheet, _idx - i - 1, 2, "□ 同意           □ 不同意        签名/日期：");
            }
        }
        setCellVal(sheet, _idx - ary.Length, 0, "不合格评审：");
        sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1, 0, 0));
        for (int i = 0; i < ary.Length; i++)
        {
            setCellVal(sheet, _idx - i - 1, 1, ary[i]);
            setCellVal(sheet, _idx - i - 1, 2, "□ 同意           □ 不同意        签名/日期：");
        }
        //如果是合格件，那下面不要了end

        //****合并好后开始赋值end**************
        getRowHeight(_idx, sheet, maxCols, "XlsFont");
        //保存
        using (var ms = new MemoryStream())
        {
@@ -736,7 +876,82 @@
            }
        }
    }
    /// <summary>
    /// 计算高度
    /// </summary>
    /// <param name="_idx"></param>
    /// <param name="sheet"></param>
    /// <param name="maxCols"></param>
    private static void getRowHeight(int _idx, ISheet sheet, int maxCols, string XlsFont)
    {
        int _XlsFont = int.Parse(AppSettingsHelper.getValueByKey(XlsFont).ToString());
        for (int i = 0; i < _idx; i++)
        {
            if (sheet.GetRow(i) == null)
            {
                continue;
            }
            IRow ICurRow = sheet.GetRow(i);
            int OldHg = ICurRow.Height;
            for (int j = 0; j < maxCols; j++)
            {
                if (ICurRow.GetCell(j) == null)
                {
                    continue;
                }
                ICell CurCell = ICurRow.GetCell(j);
                if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
                {
                    double CurHeight = ICurRow.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
                    double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
                    short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
                    if (height * _XlsFont > OldHg)
                    {
                        OldHg = height * _XlsFont;
                    }
                }
            }
            if (ICurRow.Height < OldHg)
            {
                ICurRow.Height = Convert.ToInt16(OldHg);
            }
            //因为这里全部 数字，感觉取的高度不对
            if (ICurRow.GetCell(0).ToString() == "制热性能")
            {
                ICurRow.Height = Convert.ToInt16(OldHg / 3);
            }
        }
    }

    /// <summary>
    /// 读取制热性能，把table转为strin
    /// </summary>
    /// <param name="json"></param>
    /// <returns></returns>
    private static string getTableToString(string json)
    {
        if (string.IsNullOrEmpty(json))
            return "";
        System.Text.StringBuilder sbLine = new StringBuilder();
        var data = JsonConvert.DeserializeObject<Dictionary<string, Dictionary<string, string>>>(json);
        List<string> resultLines = new List<string>();
        foreach (var entry in data)
        {
            string key = entry.Key;
            var values = entry.Value;
            string temp = values["temp"];
            sbLine.Append(key + "/" + temp + "：\t");
            System.Text.StringBuilder dbdb = new StringBuilder();
            foreach (var dddddd in values)
            {
                if (dddddd.Key == "temp")
                    continue;
                dbdb.Append(dddddd.Key + "-" + dddddd.Value + "、");
            }
            sbLine.Append(dbdb.ToString() + "\n");
        }
        return sbLine.ToString();
    }

    /// <summary>
    /// 生成巡检
@@ -750,16 +965,14 @@
        //行高
        int rowHeight = 25;
        //总列
        int maxCols = tb1.Columns.Count;
        int maxCols = 10;
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        // 创建单元格样式 - 带边框和指定字体
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 20, true, true);
        ICellStyle style12 = getStyle(workbook, 11, false, true);

        // 设置列宽（6列）
        // 设置列宽（10列）
        sheet.SetColumnWidth(0, 3000);  // 第一列稍宽
        sheet.SetColumnWidth(1, 3000);
        sheet.SetColumnWidth(2, 3000);
@@ -768,8 +981,10 @@
        sheet.SetColumnWidth(5, 3000);
        sheet.SetColumnWidth(6, 3000);
        sheet.SetColumnWidth(7, 3000);
        // 创建行
        for (int rowIndex = 0; rowIndex <= 1; rowIndex++)
        sheet.SetColumnWidth(8, 3000);
        sheet.SetColumnWidth(9, 3000);
        // 创建头行
        for (int rowIndex = 0; rowIndex <= 2; rowIndex++)
        {
            IRow row = sheet.CreateRow(rowIndex);
            // 设置行高（所有行相同高度）
@@ -785,31 +1000,70 @@
            }
        }
        //第一行列合并
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        setCellVal(sheet, 0, 0, row0["title"].ToString());
        //第二行列合并
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 6, 9));
        setCellVal(sheet, 1, 0, "工单号");
        setCellVal(sheet, 1, 2, row0["daaNo"].ToString());
        setCellVal(sheet, 1, 3, "产品名称");
        setCellVal(sheet, 1, 4, row0["itemName"].ToString());
        setCellVal(sheet, 1, 5, "规格型号");
        setCellVal(sheet, 1, 6, row0["itemModel"].ToString());
        //第三行列合并
        sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 4));
        setCellVal(sheet, 2, 0, "序号");
        setCellVal(sheet, 2, 1, "检验项目");
        setCellVal(sheet, 2, 2, "检验标准");
        setCellVal(sheet, 2, 5, row0["tm1"].ToString());
        setCellVal(sheet, 2, 6, row0["tm2"].ToString());
        setCellVal(sheet, 2, 7, row0["tm3"].ToString());
        setCellVal(sheet, 2, 8, row0["tm4"].ToString());
        setCellVal(sheet, 2, 9, row0["tm5"].ToString());
        //循环数据库
        int _idx = 1;
        IRow row6 = sheet.CreateRow(_idx);
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
        {
            ICell cell6 = row6.CreateCell(colIndex);
            cell6.CellStyle = style;
            cell6.SetCellValue(tb1.Columns[colIndex].Caption.ToString().Trim());
        }
        _idx++;
        int _idx = 3;
        foreach (DataRow rrr in tb1.Rows)
        {
            IRow row7 = sheet.CreateRow(_idx);
            row7.HeightInPoints = rowHeight;
            for (int colIndex = 0; colIndex < tb1.Columns.Count; colIndex++)
            for (int colIndex = 0; colIndex < maxCols; colIndex++)
            {
                ICell cell7 = row7.CreateCell(colIndex);
                cell7.CellStyle = style;
                cell7.SetCellValue(rrr[tb1.Columns[colIndex]].ToString());
                // cell7.SetCellValue("1");
                switch (colIndex)
                {
                    case 0:
                        cell7.SetCellValue(rrr["序号"].ToString());
                        break;
                    case 1:
                        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 4));
                        cell7.SetCellValue(rrr["检验项目"].ToString());
                        break;
                    case 2:
                        cell7.SetCellValue(rrr["检验标准"].ToString());
                        break;
                    case 5:
                        cell7.SetCellValue(rrr["tm1"].ToString());
                        break;
                    case 6:
                        cell7.SetCellValue(rrr["tm2"].ToString());
                        break;
                    case 7:
                        cell7.SetCellValue(rrr["tm3"].ToString());
                        break;
                    case 8:
                        cell7.SetCellValue(rrr["tm4"].ToString());
                        break;
                    case 9:
                        cell7.SetCellValue(rrr["tm5"].ToString());
                        break;
                }
            }
            _idx++;
        }
        //备注
        ////备注
        IRow row8 = sheet.CreateRow(_idx);
        row8.HeightInPoints = rowHeight;
        for (int colIndex = 0; colIndex < maxCols; colIndex++)
@@ -817,12 +1071,13 @@
            ICell cell = row8.CreateCell(colIndex);
            cell.CellStyle = style12;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 2));
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 4));
        setCellVal(sheet, _idx, 0, "巡检人：");
        for (int colIndex = 2; colIndex < tb1.Columns.Count; colIndex++)
        {
            setCellVal(sheet, _idx, colIndex, "余吉林");
        }
        setCellVal(sheet, _idx, 5, row0["xjr1"].ToString());
        setCellVal(sheet, _idx, 6, row0["xjr2"].ToString());
        setCellVal(sheet, _idx, 7, row0["xjr3"].ToString());
        setCellVal(sheet, _idx, 8, row0["xjr4"].ToString());
        setCellVal(sheet, _idx, 9, row0["xjr5"].ToString());
        //保存
        using (var ms = new MemoryStream())
        {
@@ -839,13 +1094,374 @@
        }
    }

    /// <summary>
    /// 生成成品检验报告（按实际模板结构）
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="strFileName"></param>
    public static void ExportChengPin(DataSet dtSource, string strFileName)
    {
        //行高
        int rowHeight = 25;
        //总列数 - 根据模板分析，至少需要18列
        int maxCols = 18;
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");
        // 创建单元格样式
        ICellStyle style = getStyle(workbook);
        ICellStyle styleHeader = getStyle(workbook, 16, true, true);
        ICellStyle styleTitle = getStyle(workbook, 14, true, true);
        ICellStyle styleSmall = getStyle(workbook, 10, false, false);
        style.WrapText = true;
        styleHeader.WrapText = true;
        styleTitle.WrapText = true;
        styleSmall.WrapText = true;

        // 设置列宽
        sheet.SetColumnWidth(0, 3500);   // A列：项目名称
        sheet.SetColumnWidth(1, 5000);   // B列：标准/规范
        for (int i = 2; i < 18; i++)     // C到R列：16个检测值列
        {
            sheet.SetColumnWidth(i, 3500);
        }

        //获取表头数据
        DataTable tb0 = dtSource.Tables[0];
        DataRow row0 = tb0.Rows[0];
        int _idx = 0;

        // 第1行：报告标题
        IRow titleRow = sheet.CreateRow(_idx);
        titleRow.HeightInPoints = 30;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = titleRow.CreateCell(i);
            cell.CellStyle = styleHeader;
        }
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, maxCols - 1));
        setCellVal(sheet, _idx, 0, "成品检验报告");
        _idx++;

        // 第2行：基本信息第一行
        IRow info1Row = sheet.CreateRow(_idx);
        info1Row.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = info1Row.CreateCell(i);
            cell.CellStyle = style;
        }
        setCellVal(sheet, _idx, 0, "生产车间：" + (row0["workShop"]?.ToString() ?? ""));
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 5));
        _idx++;

        // 第3行：产品信息
        IRow info2Row = sheet.CreateRow(_idx);
        info2Row.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = info2Row.CreateCell(i);
            cell.CellStyle = style;
        }
        setCellVal(sheet, _idx, 0, "产品名称");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
        setCellVal(sheet, _idx, 1, row0["itemName"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 5, "型号规格");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
        setCellVal(sheet, _idx, 6, row0["itemModel"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 12, "线号");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 15));
        setCellVal(sheet, _idx, 13, row0["lineNo"]?.ToString() ?? "");
        _idx++;

        // 第4行：商标等信息
        IRow info3Row = sheet.CreateRow(_idx);
        info3Row.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = info3Row.CreateCell(i);
            cell.CellStyle = style;
        }
        setCellVal(sheet, _idx, 0, "商标");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
        setCellVal(sheet, _idx, 1, row0["brand"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 5, "生产日期");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
        setCellVal(sheet, _idx, 6, row0["productionDate"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 12, "班次");
        setCellVal(sheet, _idx, 13, row0["classes"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 14, "批量");
        setCellVal(sheet, _idx, 15, row0["batch"]?.ToString() ?? "");
        _idx++;

        // 第5行：AC/Re标准
        IRow acreRow = sheet.CreateRow(_idx);
        acreRow.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = acreRow.CreateCell(i);
            cell.CellStyle = style;
        }
        setCellVal(sheet, _idx, 0, "Ac/Re(A类）");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
        setCellVal(sheet, _idx, 1, row0["acRe_A"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 6, "Ac/Re(B类）");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
        setCellVal(sheet, _idx, 7, row0["acRe_B"]?.ToString() ?? "");

        setCellVal(sheet, _idx, 12, "Ac/Re(C类）");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
        setCellVal(sheet, _idx, 13, row0["acRe_C"]?.ToString() ?? "");
        _idx++;

        // 第6行：抽样信息
        IRow sampleRow = sheet.CreateRow(_idx);
        sampleRow.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = sampleRow.CreateCell(i);
            cell.CellStyle = style;
        }
        setCellVal(sheet, _idx, 0, "抽样");
        setCellVal(sheet, _idx, 1, "匀速");
        setCellVal(sheet, _idx, 2, row0["SampleMethod"]?.ToString() == "匀速" ? "√" : "");
        setCellVal(sheet, _idx, 3, "随机");
        setCellVal(sheet, _idx, 4, row0["SampleMethod"]?.ToString() == "随机" ? "√" : "");

        setCellVal(sheet, _idx, 5, "样本数");
        setCellVal(sheet, _idx, 6, "匀速抽样数");
        setCellVal(sheet, _idx, 7, row0["sampleSize1"]?.ToString() ?? "");
        setCellVal(sheet, _idx, 8, "随机抽样数");
        setCellVal(sheet, _idx, 9, row0["sampleSize2"]?.ToString() ?? "");
        setCellVal(sheet, _idx, 10, "样本");
        setCellVal(sheet, _idx, 11, row0["sampleSize3"]?.ToString() ?? "");
        _idx++;

        int num;
        if (row0["SampleMethod"]?.ToString() == "匀速")
        {
            if (int.TryParse(row0["sampleSize1"]?.ToString(), out int size))
            {
                num = size;
            }
            else
            {
                num = 0;
            }
        }
        else
        {
            if (int.TryParse(row0["sampleSize2"]?.ToString(), out int size))
            {
                num = size;
            }
            else
            {
                num = 0;
            }
        }
        if (num > 16)
            num = 16;

        // 第7行：检验项目表头
        IRow headerRow = sheet.CreateRow(_idx);
        headerRow.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = headerRow.CreateCell(i);
            cell.CellStyle = styleTitle;
        }
        setCellVal(sheet, _idx, 0, "项目");
        setCellVal(sheet, _idx, 1, "标准");
        // 根据需要继续添加更多列标题，这里简化为主要列
        for (int i = 2; i < num + 2; i++)
        {
            setCellVal(sheet, _idx, i, (i - 1).ToString());
        }
        _idx++;

        // 检验项目数据
        if (dtSource.Tables.Count > 1)
        {
            DataTable tb1 = dtSource.Tables[1];
            foreach (DataRow rrr in tb1.Rows)
            {
                IRow dataRow = sheet.CreateRow(_idx);
                dataRow.HeightInPoints = rowHeight;

                for (int i = 0; i < maxCols; i++)
                {
                    ICell cell = dataRow.CreateCell(i);
                    cell.CellStyle = style;
                }

                setCellVal(sheet, _idx, 0, rrr["RPB003"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 1, rrr["RPB004"]?.ToString() ?? "");

                // 填充16个测试值
                for (int i = 0; i < num; i++)
                {
                    string colName = $"RPB{i + 5:D3}"; // RPB005, RPB006, ..., RPB020
                    if (rrr.Table.Columns.Contains(colName))
                    {
                        setCellVal(sheet, _idx, i + 2, rrr[colName]?.ToString() ?? "");
                    }
                    else
                    {
                        setCellVal(sheet, _idx, i + 2, "");
                    }
                }

                _idx++;
            }
        }

        // 不合格内容记录
        IRow defectHeaderRow = sheet.CreateRow(_idx);
        defectHeaderRow.HeightInPoints = rowHeight;
        for (int i = 0; i < maxCols; i++)
        {
            ICell cell = defectHeaderRow.CreateCell(i);
            cell.CellStyle = styleTitle;
        }
        setCellVal(sheet, _idx, 0, "序号");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 不合格内容占多列
        setCellVal(sheet, _idx, 1, "不合格内容");
        setCellVal(sheet, _idx, 11, "A类");
        setCellVal(sheet, _idx, 12, "B类");
        setCellVal(sheet, _idx, 13, "C类");
        sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
        setCellVal(sheet, _idx, 14, "注意点");
        _idx++;

        if (dtSource.Tables.Count > 2)
        {
            DataTable tb2 = dtSource.Tables[2];
            foreach (DataRow rrr in tb2.Rows)
            {
                if (rrr["Description"]?.ToString() == "不良数量")
                    break;
                IRow dataRow = sheet.CreateRow(_idx);
                dataRow.HeightInPoints = rowHeight;

                for (int i = 0; i < maxCols; i++)
                {
                    ICell cell = dataRow.CreateCell(i);
                    cell.CellStyle = style;
                }

                setCellVal(sheet, _idx, 0, rrr["Seq"]?.ToString() ?? "");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 不合格内容占多列
                setCellVal(sheet, _idx, 1, rrr["Description"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 11, rrr["AClass"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 12, rrr["BClass"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 13, rrr["CClass"]?.ToString() ?? "");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
                setCellVal(sheet, _idx, 14, rrr["Point"]?.ToString() ?? "");
                _idx++;
            }

            DataRow dr = tb2.Rows[tb2.Rows.Count - 1];
            // 备注行
            IRow remarkRow = sheet.CreateRow(_idx);
            remarkRow.HeightInPoints = rowHeight;
            for (int i = 0; i < maxCols; i++)
            {
                ICell cell = remarkRow.CreateCell(i);
                cell.CellStyle = style;
            }
            setCellVal(sheet, _idx, 0, "备注：");
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 8));
            setCellVal(sheet, _idx, 9, "不良数量：");
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 9, 10));
            setCellVal(sheet, _idx, 11, dr["AClass"]?.ToString() ?? "");
            setCellVal(sheet, _idx, 12, dr["BClass"]?.ToString() ?? "");
            setCellVal(sheet, _idx, 13, dr["CClass"]?.ToString() ?? "");
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
            setCellVal(sheet, _idx, 14, dr["Point"]?.ToString() ?? "");
            _idx++;

            // 大的备注内容区域
            IRow remarkContentRow = sheet.CreateRow(_idx);
            remarkContentRow.HeightInPoints = 60;
            for (int i = 0; i < maxCols; i++)
            {
                ICell cell = remarkContentRow.CreateCell(i);
                cell.CellStyle = style;
            }
            sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 17));
            setCellVal(sheet, _idx, 0, dr["Remark"]?.ToString() ?? "");
            _idx++;
        }

        // 检验判定区域（3行布局）
        for (int judgeRowIndex = 0; judgeRowIndex < 2; judgeRowIndex++)
        {
            IRow judgmentRow = sheet.CreateRow(_idx);
            judgmentRow.HeightInPoints = rowHeight;
            for (int i = 0; i < maxCols; i++)
            {
                ICell cell = judgmentRow.CreateCell(i);
                cell.CellStyle = styleTitle;
            }

            if (judgeRowIndex == 0)
            {
                // 第一行：检验判定
                setCellVal(sheet, _idx, 0, "检验判定");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 1, 0, 0)); // 修改为跨2行而不是3行
                setCellVal(sheet, _idx, 1, row0["CheckResult"]?.ToString() ?? "");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5)); // 只合并当前行

                setCellVal(sheet, _idx, 6, "检验签名");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 10)); // 只合并当前行
                setCellVal(sheet, _idx, 7, row0["JY_NAME"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 11, "审核签名");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 16)); // 只合并当前行
                setCellVal(sheet, _idx, 12, row0["CHECK_USER"]?.ToString() ?? "");
            }
            else if (judgeRowIndex == 1)
            {
                setCellVal(sheet, _idx, 6, "检验日期");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 10)); // 只合并当前行
                setCellVal(sheet, _idx, 7, row0["JY_DATE"]?.ToString() ?? "");
                setCellVal(sheet, _idx, 11, "审核日期");
                sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 16)); // 只合并当前行
                setCellVal(sheet, _idx, 12, row0["CHECK_DATE"]?.ToString() ?? "");
            }
            _idx++;
        }

        getRowHeight(_idx, sheet, maxCols, "XlsFontXj");

        //保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                var data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary>
    ///     Excel导入成Datable
    ///   Excel导入成Datable
    /// </summary>
    /// <param name="file">导入路径(包含文件名与扩展名)</param>
    /// <param name="strType">工单编号，过滤多表格</param>
    /// <returns></returns>
    public static DataTable ExcelToTable(string file)
    public static DataTable ExcelToTable(string file, string strType = "")
    {
        var dt = new DataTable();
        IWorkbook workbook;
@@ -865,7 +1481,16 @@

            var sheet = workbook.GetSheetAt(0);
            //表头  
            var header = sheet.GetRow(sheet.FirstRowNum + 1);
            int intBt = 0;
            //如果是后盖码，取第1行，否则取第二行
            if (string.IsNullOrEmpty(strType))
            {
                intBt = sheet.FirstRowNum + 1;
            }
            else
                intBt = sheet.FirstRowNum;

            var header = sheet.GetRow(intBt);
            var columns = new List<int>();
            for (var i = 0; i < header.LastCellNum; i++)
            {
@@ -880,8 +1505,19 @@
            for (var act = 0; act < numberOfSheets; act++)
            {
                var _sheet = workbook.GetSheetAt(act);
                #region 后盖码特殊
                if (!string.IsNullOrEmpty(strType))
                {
                    string _sheetName = _sheet.SheetName.Trim();
                    if (strType != _sheetName)
                    {
                        continue;
                    }
                }
                #endregion

                //数据  
                for (var i = _sheet.FirstRowNum + 1 + 1;
                for (var i = intBt + 1;
                     i <= _sheet.LastRowNum;
                     i++)
                {
@@ -903,7 +1539,7 @@
    }

    /// <summary>
    ///     获取单元格类型
    /// 获取单元格类型
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>