1、收料模块 2、异常单

kyy

8 天以前 a6f9b5610c5c9dcc25e9c4dfc288a959fb11f458

 WebApi/Gs.Toolbox/ExcelHelper.cs

@@ -178,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++)
                {
@@ -192,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)
@@ -260,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++)
                {
@@ -282,7 +301,7 @@
            }
        }

        //保存
        // 保存
        using (var ms = new MemoryStream())
        {
            using (var fs = new FileStream(strFileName, FileMode.Create,