blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
fei
2026-01-05 6f0797731657022e9812b92fb4d78bcf56a65c1d 
 ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtilManySheetFour.java


@@ -12,17 +12,16 @@


import com.ruoyi.common.utils.file.FileTypeUtils;


import com.ruoyi.common.utils.file.ImageUtils;


import com.ruoyi.common.utils.reflect.ReflectUtils;


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.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.HSSFRichTextString;


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;


import org.apache.poi.hssf.usermodel.HSSFRichTextString;


import org.apache.poi.xssf.usermodel.XSSFClientAnchor;


import org.apache.poi.xssf.usermodel.XSSFDataValidation;


import org.apache.poi.xssf.usermodel.XSSFRichTextString;


import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import org.slf4j.Logger;


import org.slf4j.LoggerFactory;




@@ -260,8 +259,11 @@


     * @throws IOException


     */


    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException {


        response.setContentType("application/vnd.ms-excel");


        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


        response.setCharacterEncoding("utf-8");


        // 设置文件名


        String fileName = UUID.randomUUID() + "_"  + ".xlsx";


        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);


        this.init(list, sheetName, Excel.Type.EXPORT);


        exportExcel(response.getOutputStream());


    }


@@ -274,10 +276,21 @@


     * @return 结果


     * @throws IOException


     */


    /**


     * 对list数据源将其里面的数据导入到excel表单


     *


     * @param response  返回数据


     * @param list      导出数据集合


     * @return 结果


     * @throws IOException


     */


    public void exportExcelManySheet(HttpServletResponse response, List<ExcelExp> list, boolean includeQrCode, byte[] bt,


                                     List<String> sigArr, List<String> arrAn, String inquiryNumber, String caseTitle) throws IOException {


        //  response.setContentType("application/vnd.ms-excel");


        //  response.setCharacterEncoding("utf-8");


        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


        response.setCharacterEncoding("utf-8");


        // 设置文件名


        String fileName = UUID.randomUUID() + "_" + ".xlsx";


        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);




        try {


            createWorkbook();


@@ -296,18 +309,33 @@


                    // 合并第一行的前5个单元格


                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));




                    // 必须先设置为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);  // 纵向打印






                    // 创建行并设置高度


                    row = sheet.createRow(firow);


                    row.setHeight((short)(40 * 40));


                     row = sheet.createRow(firow);


                    //生成二维码


                    if(includeQrCode)


                    {


                        Cell cell = row.createCell(6);


                        row.setHeight((short)(40 * 40));




                        Cell cell = row.createCell(6);


                        // 设置图片大小和位置


                        ClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) (cell.getColumnIndex()+1), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() +2),


                        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; // 中间列


@@ -318,20 +346,23 @@


//                    anchor.setDy1(0);


//                    anchor.setDx2(255); // 宽度


//                    anchor.setDy2(255); // 高度


                        anchor.setRow1(1);


                        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);


                        // 计算居中位置


                        int col1 = 3; // 中间列


                        int col2 = col1 + 3;


                        anchor.setCol1(col1);


                        anchor.setCol2(col2);


                        anchor.setRow1(0);


                        anchor.setRow2(1);


                        byte[] data = bt;


                        anchor.setDx1(2400000);


                        anchor.setDy1(-10);




                        anchor.setDy2(-800000);


                 


                        // 获取图片原始尺寸


                        BufferedImage image = ImageIO.read(new ByteArrayInputStream(data));


                        double widthInEMU = image.getWidth() * 9525 * 0.2;


                        double heightInEMU = image.getHeight() * 9525;




                        // 设置图片原始尺寸


                        anchor.setDx2(-100000); // 原始宽度


                    //    anchor.setDx2(-100000); // 原始宽度


                        //   anchor.setDy2((int)heightInEMU); // 原始高度


//anchor.setCol1(5); // 从第6列开始显示


//anchor.setCol2(10); // 到第11列结束


@@ -345,6 +376,9 @@


                        firow = firow + 1;


                        row = sheet.createRow(firow);


                    }


                    else


                        row.setHeight((short)(20 * 20));




                    //  sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));


                    if(firow==1)


                    {


@@ -364,16 +398,67 @@


                    titleCell.setCellStyle(style);


                    firow = firow + 1;


                    row = sheet.createRow(firow);


                    // 将固定行高改为自动行高，以便内容能完整显示在合并区域


                    row.setHeight((short)(50*20));


                    //


                    row.createCell(0).setCellValue("发文号:");


                    sheet.addMergedRegion(new CellRangeAddress(firow, firow, 1, 3));


                    row.createCell(1).setCellValue(inquiryNumber);


                    row.createCell(4).setCellValue("案卷题名:");


                    sheet.addMergedRegion(new CellRangeAddress(firow, firow, 5, 9));


                    CellStyle style1 = wb.createCellStyle();


                    style1.setAlignment(HorizontalAlignment.LEFT);


                    Cell cell2 = row.createCell(0);


                    cell2.setCellValue("发文号:");


                    cell2.setCellStyle(style1);


                    


                    // 合并第1-3列


                    int startCol1 = 1;


                    int endCol1 = 3;


                    sheet.addMergedRegion(new CellRangeAddress(firow, firow, startCol1, endCol1));


                    


                    // 设置所有合并单元格的样式


                    for (int col = startCol1; col <= endCol1; col++) {


                        Cell mergedCell = row.createCell(col);


                        mergedCell.setCellStyle(style1);


                    }


                    


                    // 只在起始单元格设置数据


                    Cell inquiryCell = row.getCell(startCol1);


                    inquiryCell.setCellValue(inquiryNumber);


                    Cell cell1 = row.createCell(4);


                    cell1.setCellValue("案卷题名:");


                    cell1.setCellStyle(style1);


                    


                    // 创建一个新的样式用于案卷题名合并单元格


                    CellStyle mergedCellStyle = wb.createCellStyle();


                    mergedCellStyle.cloneStyleFrom(style1); // 继承原有样式


                    mergedCellStyle.setAlignment(HorizontalAlignment.LEFT); // 改为左对齐


                    mergedCellStyle.setWrapText(true); // 启用自动换行


                    //mergedCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中


                    


                    // 合并第5-8列


                    int startCol = 5;


                    int endCol = 8;


                    


                    // 先创建所有需要的单元格并设置样式


                    for (int col = startCol; col <= endCol; col++) {


                        Cell mergedCell = row.createCell(col);


                        mergedCell.setCellStyle(mergedCellStyle);


                    }


                    


                    // 然后执行合并操作


                    sheet.addMergedRegion(new CellRangeAddress(firow, firow, startCol, endCol));




                    //   row.createCell(4).setCellValue("档号:");


                    //拿到档号


                    row.createCell(5).setCellValue(caseTitle);


                    // 只在起始单元格设置数据


                    Cell getCas = row.getCell(startCol);


                    getCas.setCellStyle(mergedCellStyle);


                    getCas.setCellValue(caseTitle);


                    // 只在起始单元格设置数据


                    //ces.setCellValue(caseTitle);




                    // 设置自动换行


//                    row = sheet.createRow(2);


//                    row.createCell(0).setCellValue("案卷题名:");








                    // 设置自动换行


//                    row = sheet.createRow(2);


//                    row.createCell(0).setCellValue("案卷题名:");


                    firow = firow + 1;


@@ -383,7 +468,7 @@


                    column = 0;


                } else {


                    // 普通sheet处理


                    //   recordId = (DocumentMaterialsVo)list.get(0)


                 //   recordId = (DocumentMaterialsVo)list.get(0)


                    row = sheet.createRow(0);


                    column = 0;


                }


@@ -447,8 +532,11 @@


     * @return 结果


     */


    public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException {


        response.setContentType("application/vnd.ms-excel");


        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


        response.setCharacterEncoding("utf-8");


        // 设置文件名


        String fileName = UUID.randomUUID() + "_"  + ".xlsx";


        response.setHeader("Content-Disposition", "attachment; filename=" );


        this.init(null, sheetName, Excel.Type.IMPORT);


        exportExcel(response.getOutputStream());


    }


@@ -562,7 +650,7 @@


            String key = StringUtils.format("header_{}_{}", excel.headerColor(), excel.headerBackgroundColor());


            if (!headerStyles.containsKey(key))


            {


                style = wb.createCellStyle();


                 style = wb.createCellStyle();


                style.cloneStyleFrom(styles.get("data"));


                style.setAlignment(HorizontalAlignment.CENTER);


                style.setVerticalAlignment(VerticalAlignment.CENTER);


@@ -647,7 +735,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 HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),


            ClientAnchor anchor = new XSSFClientAnchor(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)) {


@@ -694,12 +782,12 @@


        // 如果设置了提示信息则鼠标放上去提示.


        if (StringUtils.isNotEmpty(attr.prompt())) {


            // 这里默认设了2-101列提示.


            setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);


            setCellPrompt(sheet, "", attr.prompt(), 1, 100, column, column);


        }


        // 如果设置了combo属性则本列只能选择不能输入


        if (attr.combo().length > 0) {


            // 这里默认设了2-101列只能选择不能输入.


            setHSSFValidation(sheet, attr.combo(), 1, 100, column, column);


            setCellValidation(sheet, attr.combo(), 1, 100, column, column);


        }


    }




@@ -751,7 +839,7 @@


    }




    /**


     * 设置 POI HSSFSheet 单元格提示


     * 设置单元格提示


     *


     * @param sheet         表单


     * @param promptTitle   提示标题


@@ -761,7 +849,7 @@


     * @param firstCol      开始列


     * @param endCol        结束列


     */


    public void setHSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,


    public void setCellPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,


                              int firstCol, int endCol) {


        DataValidationHelper helper = sheet.getDataValidationHelper();


        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");


@@ -783,7 +871,7 @@


     * @param endCol   结束列


     * @return 设置好的sheet.


     */


    public void setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {


    public void setCellValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {


        DataValidationHelper helper = sheet.getDataValidationHelper();


        // 加载下拉列表内容


        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);


@@ -792,7 +880,7 @@


        // 数据有效性对象


        DataValidation dataValidation = helper.createValidation(constraint, regions);


        // 处理Excel兼容性问题


        if (dataValidation instanceof HSSFDataValidation) {


        if (dataValidation instanceof XSSFDataValidation) {


            dataValidation.setSuppressDropDownArrow(false);


            dataValidation.setShowErrorBox(true);


        }


@@ -991,7 +1079,7 @@


     * 创建一个工作簿


     */


    public void createWorkbook() {


        this.wb = new HSSFWorkbook();


        this.wb = new XSSFWorkbook();


    }




    /**


@@ -1060,4 +1148,18 @@


        }


        return val;


    }


}


}