增加了签名和注解

fei

昨天 ca4b62abd2560b6dcffbf3ff43da1b04427b494b

 ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtilManySheet.java

@@ -14,12 +14,10 @@
import com.ruoyi.common.utils.file.ImageUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
@@ -61,6 +59,14 @@
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;

    public Workbook getWb() {
        return wb;
    }

    public void setWb(Workbook wb) {
        this.wb = wb;
    }

    /**
     * 工作薄对象
@@ -292,10 +298,36 @@
                if(index == 1) {
                    // 合并第一行的前5个单元格
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
                    // 获取页面设置
                    // 必须先设置为true
                    // 页面布局设置
                    sheet.setAutobreaks(true);
                    sheet.setFitToPage(true);

                    // 创建行并设置高度
                    // 获取打印设置
                    PrintSetup printSetup = sheet.getPrintSetup();

                    // 设置为1页宽度
                    printSetup.setFitWidth((short) 1);   // 宽度调整为1页
                    printSetup.setFitHeight((short) 0);  // 高度不限制

                    // 其他打印设置
                    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);  // A4纸
                    printSetup.setLandscape(false);  // 纵向打印

                    // 设置页边距
                    sheet.setMargin(Sheet.LeftMargin, 0.65);
                    sheet.setMargin(Sheet.RightMargin, 0.65);
                    sheet.setMargin(Sheet.TopMargin,0.31);
                    sheet.setMargin(Sheet.BottomMargin, 0.31);
                    // 设置打印区域，从第0行到第1000行，第0列到第20列（根据实际情况调整）
                 //   sheet.setPrintArea(0, 0, 20, 0, 1000);
                    // 如果是index为1的sheet，设置顶端标题行

                        // 将第1行（索引为0）设置为每页重复的标题行
                        sheet.setRepeatingRows(CellRangeAddress.valueOf("$1:$5"));
                     row = sheet.createRow(0);
                    row.setHeight((short)(30 *30));
                    row.setHeight((short) (37*20));

                    // 创建单元格并添加图片
                    Cell cell = row.createCell(0);
@@ -304,16 +336,22 @@
                      //  if (Files.exists(Paths.get(imagePath))) {
                          //  byte[] imageBytes = Files.readAllBytes(Paths.get(imagePath));
                            // 将byte数组转换成inputstream，以便插入到Excel中
                            ClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 5),
                                    cell.getRow().getRowNum() + 1);
                            ClientAnchor anchor = new HSSFClientAnchor(813, 0, 787, 71, (short) 2, 0, (short) 4,
                                    1);



//                    ClientAnchor anchor = new HSSFClientAnchor(813, 0, 787, 71, (short) 2, 0, (short) 4,
//                            2);
                            // 设置锚点类型为MOVE_AND_RESIZE，使图片可以随单元格调整大小
                            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                                    // 计算居中位置
                    int col1 = 3; // 中间列
                    int col2 = col1 + 2;
                    anchor.setCol1(col1);
                    anchor.setCol2(col2);
                    anchor.setRow1(0);
                    anchor.setRow2(1);
                  //          anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
//                    int col1 = 3; // 中间列
//                    int col2 = col1 + 2;
//                    anchor.setCol1(col1);
//                    anchor.setCol2(col2);
//                    anchor.setRow1(0);
//                    anchor.setRow2(1);
//                            anchor.set
//                            anchor.setHorizontallyCenter(true);

@@ -333,9 +371,10 @@
                   //     log.error("添加图片失败: {}", e.getMessage());
                   // }
                    row = sheet.createRow(1);
                    row.setHeight((short) (32.5*20));
                    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
                    Cell titleCell = row.createCell(0);
                    titleCell.setCellValue("卷  内  目  录");
                    titleCell.setCellValue("卷      内      目      录");
                    // 设置加粗、宋体、18号字体和居中样式
                    CellStyle style = wb.createCellStyle();
                    Font font = wb.createFont();
@@ -346,15 +385,40 @@
                    style.setAlignment(HorizontalAlignment.CENTER);
                    titleCell.setCellStyle(style);
                    row = sheet.createRow(2);
                    row.setHeight((short) (20*20));
               //
                    row.createCell(4).setCellValue("档号:");
                    Cell dnumCell = row.createCell(4);
                    dnumCell.setCellValue("档号:");
                    // 设置加粗、宋体、18号字体和居中样式
                    CellStyle style1 = wb.createCellStyle();
                    Font font1 = wb.createFont();
                    font1.setBold(true);
                    font1.setFontName("宋体");
                    font1.setFontHeightInPoints((short) 12);
                    style1.setFont(font1);
                    style1.setAlignment(HorizontalAlignment.RIGHT);
                    dnumCell.setCellStyle(style1);
                 //   row.createCell(4).setCellValue("档号:");
                    //拿到档号
                    String recordId = list.get(index).getRecordId();
                    row.createCell(5).setCellValue(recordId);
                    // 创建带有下划线样式的单元格
                    Cell recordIdCell = row.createCell(5);
                    recordIdCell.setCellValue(recordId);
                    // 创建下划线样式
                    CellStyle underlineStyle = wb.createCellStyle();
                    Font underlineFont = wb.createFont();
                    underlineFont.setFontName("宋体");
                    underlineFont.setFontHeightInPoints((short) 12);
                    underlineFont.setUnderline(Font.U_SINGLE); // 设置下划线
                    underlineStyle.setFont(underlineFont);
                    recordIdCell.setCellStyle(underlineStyle);
                    row = sheet.createRow(3);
                    row.setHeight((short) (17.5*20));
                       row = sheet.createRow(4);
//                     // 添加总页数和当前页码信息
//                     row.createCell(5).setCellValue("总页数: " + list.size() + ", 当前页: " + (index + 1));
//   row = sheet.createRow(4);
                    column = 0;

                } else {
                    // 普通sheet处理
                 //   recordId = (DocumentMaterialsVo)list.get(0)
@@ -463,11 +527,19 @@
    public void fillExcelData(int index, Row row) {
//        int startNo = index * sheetSize;
//        int endNo = Math.min(startNo + sheetSize, list.size());

        int startRow = (index == 1) ? 5 : 1;
        for (int i = 0; i < list.size(); i++) {
            if(index==1)
                row = sheet.createRow(i + 4 );
                row = sheet.createRow(i + 5 );
            else
                row = sheet.createRow(i + 1 );
            // 第一步：让POI自动计算适配内容的行高
         //   sheet.autoSizeRow(0);
            // 第二步：获取自动计算后的行高

        //    row.setHeight((short) -1); // 先设为自动，由addCell计算后覆盖

            // 得到导出对象.
            T vo = (T) list.get(i);
            int column = 0;
@@ -477,10 +549,182 @@
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                this.addCell(excel, row, vo, field, column++);

            }
            // 批量调整行高（数据填充完成后）
            if (list.size() > 0) {
                int lastRow = startRow + list.size() - 1;
                batchAdjustRowHeights(sheet, startRow, lastRow);
            }
        }
    }
    /**
     * 批量处理行高（在数据填充完成后统一计算）
     */
    /**
     * 批量处理行高（在数据填充完成后统一计算）
     */
    private String getCellStringValue(Cell cell) {
        if (cell == null) return null;

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return null;
        }
    }
    private void batchAdjustRowHeights(Sheet sheet, int startRow, int endRow) {
        Workbook workbook = sheet.getWorkbook();


        for (int rowNum = startRow; rowNum <= endRow; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            int maxLinesInRow = 1; // 记录该行所有单元格中的最大行数

            // 第一步：遍历该行的所有单元格，找到最大行数需求
            for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell == null) continue;

                // 获取单元格内容
                String content = getCellStringValue(cell);
                if (content == null || content.isEmpty()) continue;

                // 获取列宽
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                if (columnWidth <= 0) columnWidth = 10; // 默认列宽

                // 计算该单元格内容需要的行数
                int contentLines = calculateContentLines(content, columnWidth);

                // 更新最大行数
                maxLinesInRow = Math.max(maxLinesInRow, contentLines);
            }

            // 第二步：根据最大行数设置行高（关键修复）
            if (maxLinesInRow > 1) {
                // 基础行高：一行文本的高度
                int baseHeightPerLine = 400; // 20点 = 400单位（建议值）

                // 计算总高度 = 行数 × 每行高度
                int newHeight = maxLinesInRow * baseHeightPerLine + 600;

                // 限制最小和最大高度
                int minHeight = 300;   // 15点
                int maxHeight = 10000; // 500点
                newHeight = Math.max(minHeight, Math.min(newHeight, maxHeight));

                // 设置行高
                row.setHeight((short) newHeight);

                // 可选：记录调整信息用于调试
                log.debug("Row {} adjusted: maxLines={}, height={}",
                        rowNum, maxLinesInRow, newHeight);
            }
        }
    }
    /**
     * 计算文本的有效字符宽度
     */
    private double calculateEffectiveWidth(String text) {
        if (text == null || text.isEmpty()) {
            return 0;
        }

        double totalWidth = 0;

        for (char c : text.toCharArray()) {
            if (isChineseChar(c)) {
                // 中文字符：1.0宽度
                totalWidth += 2.0;
            } else if (Character.isDigit(c)) {
                // 数字：0.6宽度
                totalWidth += 0.6;
            } else if (Character.isUpperCase(c)) {
                // 大写字母：0.7宽度
                totalWidth += 0.7;
            } else if (Character.isLowerCase(c)) {
                // 小写字母：0.5宽度
                totalWidth += 0.5;
            } else if (c == '.' || c == ',') {
                // 点号、逗号：0.3宽度
                totalWidth += 0.3;
            } else if (c == '-' || c == '_') {
                // 连字符、下划线：0.35宽度
                totalWidth += 0.35;
            } else if (c == ' ') {
                // 空格：0.3宽度
                totalWidth += 0.3;
            } else if (c == '\t') {
                // 制表符：4.0宽度
                totalWidth += 4.0;
            } else {
                // 其他字符：默认0.6宽度
                totalWidth += 0.6;
            }
        }

        return totalWidth;
    }
    /**
     * 计算单元格内容所需行数（更精确的版本）
     */
    private int calculateContentLines(String content, int columnWidthChars) {
        if (content == null || content.isEmpty() || columnWidthChars <= 0) {
            return 1;
        }

        int totalLines = 0;
        String[] lines = content.split("\n");

        for (String line : lines) {
            if (line.trim().isEmpty()) {
                totalLines++; // 空行也算一行
                continue;
            }

            // 计算该行需要的字符宽度
            // 考虑中英文字符宽度差异
            double effectiveLength = 0;


            effectiveLength += calculateEffectiveWidth(line); // 中文字符占1个宽度



            // 计算需要的行数
            int linesForText = (int) Math.ceil(effectiveLength / columnWidthChars);
            totalLines += Math.max(1, linesForText);
        }

        return Math.max(totalLines, 1);
    }

    /**
     * 判断是否为中文字符
     */
    private boolean isChineseChar(char c) {
        Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
        return ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS
                || ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A
                || ub == Character.UnicodeBlock.GENERAL_PUNCTUATION
                || ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION
                || ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS;
    }
    /**
     * 创建表格样式
     *
@@ -494,18 +738,18 @@
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 设置自动换行
        style.setWrapText(true);
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short) 12);
        style.setFont(dataFont);
        styles.put("data", style);

@@ -530,7 +774,7 @@
                style.setWrapText(true);
                Font headerFont = wb.createFont();
                headerFont.setFontName(excel.headerFontName());
                headerFont.setFontHeightInPoints((short) 10);
                headerFont.setFontHeightInPoints((short) 12);
                headerFont.setBold(excel.headerFontBold());
                headerFont.setColor(excel.headerColor().index);
                style.setFont(headerFont);
@@ -543,8 +787,8 @@
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        totalFont.setFontName("宋体");
        totalFont.setFontHeightInPoints((short) 12);
        style.setFont(totalFont);
        // 总计行也设置自动换行
        style.setWrapText(true);
@@ -680,7 +924,14 @@
                } else if (align == HorizontalAlignment.RIGHT) {
                    styleKey = "data3";
                }
                cell.setCellStyle(styles.get(styleKey));
                System.out.println(styleKey);
                // 获取并修改样式
                CellStyle style = styles.get(styleKey);
                Workbook workbook = row.getSheet().getWorkbook();
                CellStyle newStyle = workbook.createCellStyle();
                newStyle.cloneStyleFrom(style);
                newStyle.setWrapText(true); // 关键：启用自动换行
                cell.setCellStyle(newStyle);

                // 用于读取对象中的属性
                Object value = getTargetValue(vo, field, attr);
@@ -697,6 +948,17 @@
                    // 设置列类型
                    setCellVo(value, attr, cell);
                }


                System.out.println(row.getHeight());
//             //   adjustRowHeightAfterSetValue(row, cell, value);
//                int defaultRowHeight = row.getHeight() / 20; // 原始行高（转成点数）
//                if(value==null)
//                    value="";
//                System.out.println(sheet.getColumnWidth(column));
//                int contentLines = getContentLines(value.toString(), sheet.getColumnWidth(column), style); // 计算换行后的行数
//                int newHeight = defaultRowHeight * contentLines; // 按行数调整行高（可加少量冗余）
//                row.setHeightInPoints((short) (newHeight)); // 提高行高（+2 是冗余，避免内容被截断）
                addStatisticsData(column, Convert.toStr(value), attr);
            }
        } catch (Exception e) {
@@ -964,6 +1226,42 @@
        } else {
            wb.setSheetName(index, sheetName + index);
        }
        // 设置页面属性
        sheet.setFitToPage(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); // 设置为A4纸张
        printSetup.setLandscape(false); // 纵向打印
        printSetup.setFitWidth((short) 1);  // 宽度调整为1页
        printSetup.setFitHeight((short) 0); // 0表示高度不限制页数（自动）
        // 设置页边距
        sheet.setMargin(Sheet.TopMargin, 1.0); // 增加顶部边距以容纳页眉
        sheet.setMargin(Sheet.BottomMargin, 0.5);
        sheet.setMargin(Sheet.LeftMargin, 0.5);
        sheet.setMargin(Sheet.RightMargin, 0.5);
        sheet.setMargin(Sheet.HeaderMargin, 0.5); // 设置页眉边距
        sheet.setMargin(Sheet.FooterMargin, 0.5); // 设置页脚边距
        // 设置打印区域，从第0行到第1000行，第0列到第20列（根据实际情况调整）
       //sheet.setPrintArea(0, 0, 20, 0, 1000);
        // 设置页眉，显示总页数和当前页码
        if (wb instanceof HSSFWorkbook) {
            HSSFWorkbook hssfWb = (HSSFWorkbook) wb;
            HSSFSheet hssfSheet = hssfWb.getSheetAt(index);
            HSSFHeader header = hssfSheet.getHeader();
            // 设置页眉字体
         //   header.setFontName("宋体");
         //   header.setFontSize((short) 10);
            // 设置页眉内容
            hssfSheet.setMargin(Sheet.HeaderMargin, 0.5); 
            header.setRight("总页数: &P, 当前页: &N");
            // 设置页眉左对齐和右对齐内容（可选）
            header.setLeft("文档标题");
            header.setRight("&D &T"); // 日期和时间
        }
        // 如果是index为1的sheet，设置顶端标题行
        if (index == 1) {
            // 将第1行（索引为0）设置为每页重复的标题行
            sheet.setRepeatingRows(CellRangeAddress.valueOf("$1:$4"));
        }
    }
    /**
     * 创建工作表
@@ -974,6 +1272,40 @@
        this.sheet = wb.createSheet();
        this.styles = createStyles(wb);
        wb.setSheetName(index, sheetName);
        // 获取页面设置
        // 必须先设置为true
        sheet.setFitToPage(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE); // 设置为A4纸张
        printSetup.setLandscape(false); // 纵向打印
        printSetup.setFitWidth((short) 1);  // 宽度调整为1页
        printSetup.setFitHeight((short) 0); // 0表示高度不限制页数（自动）
        // 设置页边距
        sheet.setMargin(Sheet.TopMargin, 1.0); // 增加顶部边距以容纳页眉
        sheet.setMargin(Sheet.BottomMargin, 0.5);
        sheet.setMargin(Sheet.LeftMargin, 0.5);
        sheet.setMargin(Sheet.RightMargin, 0.5);
        sheet.setMargin(Sheet.HeaderMargin, 0.5); // 设置页眉边距
        sheet.setMargin(Sheet.FooterMargin, 0.5); // 设置页脚边距
        // 设置打印区域，从第0行到第1000行，第0列到第20列（根据实际情况调整）
      //  sheet.setPrintArea(0, 0, 20, 0, 1000);
        // 设置页眉，显示总页数和当前页码
        if (wb instanceof HSSFWorkbook) {
            HSSFWorkbook hssfWb = (HSSFWorkbook) wb;
            HSSFSheet hssfSheet = hssfWb.getSheetAt(index);
            HSSFHeader header = hssfSheet.getHeader();
            // 设置页眉字体
           // header.setFontName("宋体");4
           // header.setFontSize((short) 10);
            // 设置页眉内容
            hssfSheet.setMargin(Sheet.HeaderMargin, 1.43);
            header.setRight("&\"宋体,Bold\"共 &N 页   第 &P 页");
        }
        // 如果是index为1的sheet，设置顶端标题行
        if (index == 1) {
            // 将第1行（索引为0）设置为每页重复的标题行
            sheet.setRepeatingRows(CellRangeAddress.valueOf("$1:$3"));
        }
    }
    /**
     * 获取单元格值
@@ -1015,4 +1347,4 @@
        }
        return val;
    }
}
}