我有个采购订单模版, 通过数据选渲染模版,然后导出. 模版有些cell是需要替换文字, 有些需要自动插入行, 印章(图片)的插入.

模版文件:

代码示例子:

@Test
    public void testWriteFromTemplate() throws IOException {
         String template = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/po.xlsx";
        String dist = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/dist.xlsx";

        // 这样就不会改变模版文件
        byte[] bytes = IOUtils.toByteArray(new FileInputStream(template));

        ExcelWriter excelWriter = new ExcelWriter(new XSSFWorkbook(new ByteArrayInputStream(bytes)));

        excelWriter.writeCell(new ExcelCell(7, 3, "PO NO: PY20230726-100"));

        excelWriter.writeCell(new ExcelCell(1, 5, "供方（Vendor）：苏州XX工程有限公司"));
        excelWriter.writeCell(new ExcelCell(2, 6, "李总 18898876623"));
        excelWriter.writeCell(new ExcelCell(2, 7, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(2, 8, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(1, 9, "ADD：  江苏省南京市武侯区"));


//        excelWriter.writeCell(new ExcelCell(7, 5, "需方：XX电机制造（苏州）有限公司"));
//        excelWriter.writeCell(new ExcelCell(7, 6, "慧Xx 18898876623"));
//        excelWriter.writeCell(new ExcelCell(7, 7, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 8, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 10, "交货地点：苏州市高新区"));


        ArrayList<Object[]> data = new ArrayList<>();
        data.add(new Object[]{1, "资材编号1", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{2, "资材编号2", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});
        data.add(new Object[]{3, "资材编号3", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{4, "资材编号4", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});

        int rowSize = data.size();

        // 获取 cell 样式
        XSSFRow row = excelWriter.getActiveSheet().getRow(11);
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        XSSFCellStyle[] cellStyles = new XSSFCellStyle[physicalNumberOfCells];
        for (int i = 0; i < physicalNumberOfCells; i++) {
            cellStyles[i] = row.getCell(i).getCellStyle();
        }

        XSSFColor redColor = new XSSFColor(Color.RED, new DefaultIndexedColorMap());

        // 文字色
        XSSFFont font = excelWriter.getBook().createFont();
        font.setColor(redColor);
        font.setBold(true);

        excelWriter.insertAndWriteRow(1, 12, data, row.getHeightInPoints(), cellStyles, (ecell, cell) -> {
            if (ecell.getX() == 9 && !String.valueOf(ecell.getValue()).matches("\\d{4}-\\d{2}-\\d{2}")) {
                XSSFCellStyle newStyle = cell.getCellStyle().copy();
                // 如果非日期类型, 表示是备注,则用红色加粗显示. CellStyle是copy的, 但是 XSSFFont 需要创建, 然后再赋值. 不然是引用, 会影响其他的cell
                font.setFamily(newStyle.getFont().getFamily());
                font.setFontName(newStyle.getFont().getFontName());
                font.setFontHeight(newStyle.getFont().getFontHeight());
                newStyle.setFont(font);
                cell.setCellStyle(newStyle);
            }
        });

        excelWriter.writeCell(new ExcelCell(3, 13 + rowSize, "¥46.00"));
        excelWriter.writeCell(new ExcelCell(3, 14 + rowSize, "RMB46"));
        excelWriter.writeCell(new ExcelCell(2, 15 + rowSize, "哈哈"));
        excelWriter.writeCell(new ExcelCell(4, 28 + rowSize, "本公司确认：王总2023/08/10"));

        excelWriter.getBook().setSheetName(0, "20230810");

        // 插入图片
        //创建一个excel文件，名称为：
        XSSFWorkbook workbook = excelWriter.getBook();
        //创建一个sheet，名称为工作簿1
        XSSFSheet sheet = excelWriter.getActiveSheet();

        BufferedImage bufferImg;
        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        String imageUrl = "/Users/rick/Space/Share/seal.png";

        //获取图片后缀
        bufferImg = ImageIO.read(new File(imageUrl));
        ImageIO.write(bufferImg, "png", byteArrayOut);

        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        //anchor主要用于设置图片的属性
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 7, 19 + rowSize, (short) 9, 30 + rowSize);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

        excelWriter.toFile(new FileOutputStream(dist));
    }