修改了对应代码

fei

5 天以前 07a2f9c762efb3be3a29e2e8cc7004d4164cc9d8

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

@@ -14,18 +14,18 @@
import com.ruoyi.common.utils.reflect.ReflectUtils;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
@@ -34,6 +34,7 @@
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;


/**
 * Excel相关处理
@@ -57,6 +58,14 @@
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;

    public Workbook getWb() {
        return wb;
    }

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

    /**
     * 工作薄对象
@@ -290,7 +299,7 @@
                    row = sheet.createRow(0);
                    sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 1));
                    row = sheet.createRow(2);
                    row.setHeightInPoints(120); // 设置行高为20磅
                    row.setHeightInPoints(100); // 设置行高为20磅

                    //拿到图片
                    // 创建单元格并添加图片
@@ -299,47 +308,45 @@
//sheet.setColumnWidth(1, 15 * 256); // 第二列宽度15字符
//sheet.setColumnWidth(2, 25 * 256); // 第三列宽度25字符 // 设置第一列宽度为20个字符

                    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) (cell.getColumnIndex()+1), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() +2),
                            cell.getRow().getRowNum() + 1);
                    // 计算居中位置
//                    int col1 = 0; // 中间列
//                    int col2 = col1 + 2;
//                    anchor.setCol1(col1);
//                    anchor.setCol2(col2);
//                    anchor.setDx1(100);
//                    anchor.setDy1(0);
//                    anchor.setDx2(255); // 宽度
//                    anchor.setDy2(255); // 高度
                    anchor.setRow1(2);
                    anchor.setRow2(3);
                    // 设置二维码
                    byte[] data = list.get(index).getSedcode();
// 设置图片大小和位置
                    anchor.setDx1(2400000);
                    anchor.setDy1(100);

                    anchor.setDy2(-600000);
                    // 获取图片原始尺寸
                    BufferedImage image = ImageIO.read(new ByteArrayInputStream(data));
                    double widthInEMU = image.getWidth() * 9525 * 0.2;
                    double heightInEMU = image.getHeight() * 9525;

                    // 设置图片原始尺寸
                    anchor.setDx2(-100000); // 原始宽度
                 //   anchor.setDy2((int)heightInEMU); // 原始高度
//anchor.setCol1(5); // 从第6列开始显示
//anchor.setCol2(10); // 到第11列结束

                    //Files.readAllBytes(Paths.get(RuoYiConfig.getProfile() + "/upload/2025/08/14/30_20250814212128A031.jpg"));
//
//
//                    System.out.println(data.length);
                    getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                            cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
                    if (data != null && data.length > 0) {
                        // 创建单元格
                        Cell qrCell = sheet.createRow(2).createCell(1);
                        // 设置行高足够高以显示二维码
                        sheet.getRow(2).setHeightInPoints(100);
                        // 二维码需要较大的列宽来显示，设置之前先保存当前列宽
                        int currentColumnWidth = sheet.getColumnWidth(1);
                        // 设置足够宽的列宽以显示二维码
                        sheet.setColumnWidth(1, 40 * 256);
                        
                        // 创建图片锚点，设置在第2行第2列
                        // 二维码显示在右上角且不填满整个格子
                        // 缩小图片范围：只占用部分单元格空间
                        ClientAnchor anchor = new HSSFClientAnchor(600, 20, 1000, 200, (short) 1, 2, (short) 1, 2);
                        // 设置图片位置和大小
                        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
                        
                        // 添加图片到工作表
                        getDrawingPatriarch(sheet).createPicture(anchor,
                                wb.addPicture(data, getImageType(data)));
                        // 二维码添加完成后，恢复原来的列宽设置
                        sheet.setColumnWidth(1, currentColumnWidth);
                    }



                    String [] tits = {"档        号:","档案馆(室)号:","缩  微  号: ","发  文  号:",
                            "案  卷  题  名:","编  制  日  期:","编  制  单  位:","保  管  期  限:","密     级:"};
                    // 以"档案馆 (室) 号:"为基准，统一所有标题的长度和格式
                    String [] tits = {
                              "档                    号:",
                              "档案馆  (室)  号:",
                               "缩        微        号:",
                              "发        文        号:",
                              "案     卷    题    名:",
                              "编     制    日    期:",
                              "编     制    单    位:",
                              "保     管    期    限:",
                              "密                     级:"};

                    List tmp = list.get(index).getDataset();
                    Object obj = tmp.get(0);
@@ -364,64 +371,113 @@
                         if(i > 3)
                             row = sheet.createRow(i+7);
                       // row.setHeight((short) 30);
                        row.setHeightInPoints(40); // 设置行高为20磅
                       // row.createCell(0)
                        // 不设置固定行高，后续使用autoSizeRow自动调整
                        cell = row.createCell(0);
                        //设置cell的宽度
                        sheet.setColumnWidth(0, 20 * 256);
                        sheet.setColumnWidth(1, 60 * 256); // 第二列宽度15字符
                        // 根据用户需求设置第二子表格的列宽
                        // 第一列宽度设置为27
                        // 第二列宽度设置为51
                        // 使用与addCell方法相同的计算公式设置列宽
                        sheet.setColumnWidth(0, (int) ((29 + 0.72) * 256)); // 第一列：宽度27
                        sheet.setColumnWidth(1, (int) ((52 + 0.72) * 256)); // 第二列：宽度51

                        CellStyle style = wb.createCellStyle();
                        style.setAlignment(HorizontalAlignment.RIGHT);
                        style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
                      //  style.setAlignment(VerticalAlignment.CENTER);
                        style.setVerticalAlignment(VerticalAlignment.BOTTOM); // 设置垂直下对齐
                        style.setWrapText(true); // 设置自动换行

                        // 设置字体为Times New Roman 18号
                        Font font = wb.createFont();
                        font.setBold(true);
                        font.setFontName("Times New Roman");
                        font.setFontHeightInPoints((short) 18);
                        style.setFont(font);
style.setWrapText(true); // 设置自动换行
                        style.setWrapText(true); // 设置自动换行
                        cell.setCellStyle(style);
                        cell.setCellValue(tits[i]);
                        Cell cel = row.createCell(1);
                        CellStyle style1 = wb.createCellStyle();
                        style1.setBorderBottom(BorderStyle.THIN); // 添加下划线
                        style1.setWrapText(true); // 设置自动换行
                        style1.setVerticalAlignment(VerticalAlignment.CENTER);
                        style1.setVerticalAlignment(VerticalAlignment.BOTTOM); // 设置垂直下对齐

                        // 设置内容字体为Times New Roman 16号
                        Font contentFont = wb.createFont();
                        contentFont.setFontName("Times New Roman");
                        contentFont.setFontHeightInPoints((short) 16);

                        // 当i==4时，为文字添加下划线
                        if(i==4) {
                            contentFont.setUnderline(Font.U_SINGLE); // 添加单下划线
                        }

                        style1.setFont(contentFont);
                        cel.setCellStyle(style1);
                        cel.setCellValue(fieldValue.toString());
                        if(i==4)
                        {
                            // 确保案     卷    题    名:这一行能根据内容长度自动换行
                            style1.setWrapText(true); // 明确设置自动换行
                            cel.setCellStyle(style1);
                        }
                        // 处理日期格式化，特别是"编  制  日  期:"这一行
                        if (i == 5 && fieldValue instanceof Date) {
                            // 设置日期格式为yyyy-MM-dd
                            String dateStr = DateUtils.parseDateToStr("yyyy-MM-dd", (Date) fieldValue);
                            cel.setCellValue(dateStr);
                        } else {
                            cel.setCellValue(fieldValue.toString());
                        }

                        // 对于i==4的情况，确保行高能根据内容自动调整
                        if(i==4)
                        {
                            // 设置行高为自动调整（-1表示自动高度）
                            row.setHeight((short) -1);
                            // 不限制最大行高，允许根据内容自动调整
                        } else {
                            // 使用setHeight方法设置行高为自动调整（-1表示自动高度）
                            row.setHeight((short) -1);
                            // 确保行高至少为40磅，但不限制最大行高
                            if (row.getHeightInPoints() < 40) {
                                row.setHeightInPoints(40);
                            }
                        }
                      //  sheet.setColumnWidth(0, 60 * 256);
                        i++;
                        if(i==3)
                        {
                            row = sheet.createRow(8);
                            // 设置行高为自动调整
                            row.setHeight((short) -1);
                            // row.setHeight((short) 30);
                            row.setHeightInPoints(40); // 设置行高为20磅
                            row.setHeightInPoints(20); // 设置行高为20磅
                            row = sheet.createRow(9);
                            //设置条形码
                            Cell cell1 = row.createCell(0);
//sheet.setColumnWidth(0, 20 * 256); // 第一列宽度20字符
//sheet.setColumnWidth(1, 15 * 256); // 第二列宽度15字符
//sheet.setColumnWidth(2, 25 * 256); // 第三列宽度25字符 // 设置第一列宽度为20个字符

                            ClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, (short) (cell1.getColumnIndex()), cell1.getRow().getRowNum(), (short) (cell1.getColumnIndex() +2),
                                    cell1.getRow().getRowNum() + 1);
                            // 计算居中位置
//// 设置图片大小和位置
anchor1.setDx1(600000);
anchor1.setDy1(0);
anchor1.setDx2(-600000); // 宽度
anchor1.setDy2(255); // 高度
//anchor1.setCol1(0.5); // 从第6列开始显示
//anchor1.setCol2(10); // 到第11列结束
                            anchor1.setRow1(9);
                            anchor1.setRow2(10);
                            // 设置条形码
                            byte[] dat = list.get(index).getImgr();
                            //Files.readAllBytes(Paths.get(RuoYiConfig.getProfile() + "/upload/2025/08/14/30_20250814212128A031.jpg"));
//
//
//                    System.out.println(data.length);
                            getDrawingPatriarch(cell1.getSheet()).createPicture(anchor1,
                                    cell.getSheet().getWorkbook().addPicture(dat, getImageType(dat)));
                            if (dat != null && dat.length > 0) {
                                // 创建单元格
                                Cell barcodeCell = sheet.createRow(9).createCell(0);
                                // 设置行高足够高以显示条形码
                                sheet.getRow(9).setHeightInPoints(40);
                                // 设置列宽足够宽以显示条形码，先保存当前列宽
                                int currentColumnWidth0 = sheet.getColumnWidth(0);
                                sheet.setColumnWidth(0, 40 * 256);

                                // 创建图片锚点，设置在第10行第1列
                                // 条形码居中且不超出第二个格子
                                // 调整结束列索引和位置参数
                                // 增加dx1值，使条形码整体向右移动
                                ClientAnchor anchor1 = new HSSFClientAnchor(660, 50, 900, 200, (short) 0, 9, (short) 1, 10);
                                // 设置图片位置和大小
                                anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);

                                // 添加图片到工作表
                                getDrawingPatriarch(sheet).createPicture(anchor1,
                                        wb.addPicture(dat, getImageType(dat)));

                                // 条形码添加完成后，恢复原来的列宽设置
                                sheet.setColumnWidth(0, currentColumnWidth0);
                            }



@@ -583,6 +639,8 @@
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格内容自动换行
        style.setWrapText(true);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
@@ -597,23 +655,41 @@
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);
        // 创建表头样式映射，根据Excel注解动态生成
        Map<String, CellStyle> headerStyles = new HashMap<String, CellStyle>();
        for (Object[] os : fields)
        {
            Excel excel = (Excel) os[1];
            String key = StringUtils.format("header_{}_{}_{}_{}_{}", excel.headerColor(), excel.headerBackgroundColor(), excel.headerFontName(), excel.headerFontBold(), excel.headerFontSize());
            if (!headerStyles.containsKey(key))
            {
                 style = wb.createCellStyle();
                style.cloneStyleFrom(styles.get("data"));
                style.setAlignment(HorizontalAlignment.CENTER);
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                // 设置单元格内容自动换行
                style.setWrapText(true);
                // 根据注解设置表头背景色 - 已注释，表头不显示背景色
                /*if (excel.headerBackgroundColor() != IndexedColors.WHITE) {
                    style.setFillForegroundColor(excel.headerBackgroundColor().index);
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                }*/
                Font headerFont = wb.createFont();
                headerFont.setFontName(excel.headerFontName());
                headerFont.setFontHeightInPoints((short) excel.headerFontSize()); // 使用Excel注解中的headerFontSize属性
                headerFont.setBold(excel.headerFontBold());
                headerFont.setColor(excel.headerColor().index);
                style.setFont(headerFont);
                headerStyles.put(key, style);
            }
        }
        styles.putAll(headerStyles);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置单元格内容自动换行
        style.setWrapText(true);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
@@ -623,16 +699,22 @@
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        // 设置单元格内容自动换行
        style.setWrapText(true);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容自动换行
        style.setWrapText(true);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        // 设置单元格内容自动换行
        style.setWrapText(true);
        styles.put("data3", style);

        return styles;
@@ -647,7 +729,9 @@
        // 写入列信息
        cell.setCellValue(attr.name());
        setDataValidation(attr, row, column);
        cell.setCellStyle(styles.get("header"));
        // 根据Excel注解动态选择表头样式
        String key = StringUtils.format("header_{}_{}_{}_{}_{}", attr.headerColor(), attr.headerBackgroundColor(), attr.headerFontName(), attr.headerFontBold(), attr.headerFontSize());
        cell.setCellStyle(styles.get(key));
        return cell;
    }

@@ -664,7 +748,7 @@
        } else if (Excel.ColumnType.NUMERIC == attr.cellType()) {
            cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
        } else if (Excel.ColumnType.IMAGE == attr.cellType()) {
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
            ClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
                    cell.getRow().getRowNum() + 1);
            String imagePath = Convert.toStr(value);
            if (StringUtils.isNotEmpty(imagePath)) {
@@ -711,12 +795,12 @@
        // 如果设置了提示信息则鼠标放上去提示.
        if (StringUtils.isNotEmpty(attr.prompt())) {
            // 这里默认设了2-101列提示.
            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
            setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        // 如果设置了combo属性则本列只能选择不能输入
        if (attr.combo().length > 0) {
            // 这里默认设了2-101列只能选择不能输入.
            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
            setHSSFValidation(sheet, attr.combo(), 1, 100, column, column);
        }
    }

@@ -726,15 +810,20 @@
    public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
        Cell cell = null;
        try {
            // 设置行高
            row.setHeight((short) 2000);
            // 设置行高为自动调整
            row.setHeight((short) -1);
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport()) {
                // 创建cell
                cell = row.createCell(column);
                //设置cell的宽度
                sheet.setColumnWidth(0, 20 * 256);
                sheet.setColumnWidth(1, 60 * 256); // 第二列宽度15字符
                // 设置列宽，使用@Excel注解中配置的宽度
                if (column == 0) {
                    // 第一列使用固定宽度
                    sheet.setColumnWidth(0, 20 * 256);
                } else if (column == 1) {
                    // 第二列使用@Excel注解中配置的宽度
                    sheet.setColumnWidth(1, (int) ((attr.width() + 0.72) * 256));
                }

                CellStyle style = wb.createCellStyle();
//                style.setAlignment(HorizontalAlignment.RIGHT);
@@ -742,7 +831,7 @@
                style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
                //  style.setAlignment(VerticalAlignment.CENTER);
                Font font = wb.createFont();
                font.setBold(true);
              //  font.setBold(true);
                style.setFont(font);
                style.setWrapText(true); // 设置自动换行

@@ -775,7 +864,7 @@
    }

    /**
     * 设置 POI XSSFSheet 单元格提示
     * 设置 POI HSSFSheet 单元格提示
     *
     * @param sheet         表单
     * @param promptTitle   提示标题
@@ -785,7 +874,7 @@
     * @param firstCol      开始列
     * @param endCol        结束列
     */
    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
    public void setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                              int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
@@ -807,7 +896,7 @@
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
    public void setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
@@ -816,11 +905,9 @@
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
        if (dataValidation instanceof HSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(false);
            dataValidation.setShowErrorBox(true);
        }

        sheet.addValidationData(dataValidation);
@@ -1017,7 +1104,7 @@
     * 创建一个工作簿
     */
    public void createWorkbook() {
        this.wb = new SXSSFWorkbook(500);
        this.wb = new HSSFWorkbook();
    }

    /**
@@ -1086,4 +1173,27 @@
        }
        return val;
    }

    /**
     * 将Apache POI的Workbook转换为Aspose.Cells的Workbook
     *
     * @param poiWorkbook Apache POI的Workbook对象
     * @return Aspose.Cells的Workbook对象
     * @throws IOException IO异常
     */



}