EasyPOI手工导出excel带图片满格(在合并单元格)

原创 于 2021-05-19 16:23:21 发布 · 4.7k 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#java #easypoi 导出图片 #excel 导出图片 #easypoi 插入图片

本文介绍如何使用EasyPOI库实现从模板导出Excel文件，并在合并单元格中正确显示图片。通过覆写ExcelExportOfTemplateUtil类中的createImageCell方法，确保图片能完全填充合并单元格。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

EasyPOI手工导出excel带图片满格(在合并单元格中)

导出结果

Excel模版

导出excel的测试代码

package test;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.zscat.mallplus.export.vo.ExprotOrderInstruction;
import com.zscat.mallplus.util.JsonUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.util.*;


public class MainTest {


    public static void main(String[] args) throws Exception {

        System.out.println("411111");

//        TemplateExportParams params = new TemplateExportParams("exportTemplate/模板.xls");
          TemplateExportParams params = new TemplateExportParams("D:/git_project/mallplus/mallplus/mallplus-portal/src/main/resources/templates/InstructionSheet.xlsx");
//        TemplateExportParams params = new TemplateExportParams("http://localhost:8091/static/InstructionSheet.xlsx");
        //3.执行excel导出
        ExprotOrderInstruction exprotOmsOrderV2 = new ExprotOrderInstruction();
        exprotOmsOrderV2.setStoreName("11111111");
        exprotOmsOrderV2.setNickname("111");
        exprotOmsOrderV2.setReceiverName("AAAA");
        exprotOmsOrderV2.setPcount("11");
        exprotOmsOrderV2.setPweight("80");
        exprotOmsOrderV2.setMaterialsName("2008");
        exprotOmsOrderV2.setEndTime(Calendar.getInstance().getTime());
        exprotOmsOrderV2.setOrderSn("6666666666");
        exprotOmsOrderV2.setNote("note");
        exprotOmsOrderV2.setProgrammer("test2");
        exprotOmsOrderV2.setOperator("test3");
        exprotOmsOrderV2.setCreateTime(Calendar.getInstance().getTime()); //下单日期

//        制表人：	{{operator}}		编程员：	{{programmer}}


        String json  = JsonUtils.toJsonStr(exprotOmsOrderV2);
        //解决导出excel报null异常问题，String空null引起的，替换成""即可
        json = json.replace("null","\"\"");
        Map<String, Object> map =  JsonUtils.readJsonToMap(json);





        //图片，如果是多个图片，就新建多个map
//        Map<String,Object> picture1 = new HashMap<String, Object>();
//        picture1.put("width", 100);
//        picture1.put("height", 150);
//        picture1.put("type", "jpg");
//        picture1.put("content", inputStream2ByteArray(new FileInputStream("C:/Users/Administrator.LAPTOP-NG0R56A6/Pictures/微信图片_20210319095228.jpg"), true));


//        // 插入图片
//        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//        BufferedImage bufferImg = ImageIO.read(new FileInputStream("C:/Users/Administrator.LAPTOP-NG0R56A6/Pictures/微信图片_20210319095228.jpg"));
//        ImageIO.write(bufferImg, "jpg", byteArrayOut);
//        ImageEntity imageEntity = new ImageEntity(byteArrayOut.toByteArray(), 800, 600);
//        map.put("img0", imageEntity);

        byte[] datas = inputStream2ByteArray(new FileInputStream("C:/Users/Administrator.LAPTOP-NG0R56A6/Pictures/微信图片_20210319095228.jpg"), true);
        ImageEntity img = new ImageEntity(datas, 10, 10);
        map.put("img0",img);


        Workbook workbook = ExcelExportUtil.exportExcel(params, map);


        // 判断数据
        if(workbook == null) {
            return ;
        }

        // 写出数据输出流到页面
        try {
            FileOutputStream output = new FileOutputStream("D:/git_project/mallplus/mallplus-web-pc/static/InstructionSheet2.xlsx");
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    /**
     * 将输入流中的数据写入字节数组
     * @param in
     * @return
     */
    public static byte[] inputStream2ByteArray(InputStream in, boolean isClose){
        byte[] byteArray = null;
        try {
            int total = in.available();
            byteArray = new byte[total];
            in.read(byteArray);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            if(isClose){
                try {
                    in.close();
                } catch (Exception e2) {
                    System.out.println("关闭流失败");
                }
            }
        }
        return byteArray;
    }

}

 

覆写工具类：在自己项目代码创建一个包名：cn.afterturn.easypoi.excel.export.template，再创建下面的类即可；

主要是createImageCell方法覆写；

下面是easypoi 3.2.0的ExcelExportOfTemplateUtil类覆写；如果你们其他版本就，可以看源码拷贝源码的ExcelExportOfTemplateUtil出来，增加createImageCell方法进去即可。

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
/*重写cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil
        添加 createImageCell 方法 此方法保证在不设置宽高的情况下 图片会占满合并后的单元格*/
package cn.afterturn.easypoi.excel.export.template;

import cn.afterturn.easypoi.cache.ExcelCache;
import cn.afterturn.easypoi.cache.ImageCache;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.base.BaseExportService;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import cn.afterturn.easypoi.excel.export.template.TemplateSumHandler.TemplateSumEntity;
import cn.afterturn.easypoi.excel.html.helper.MergedRegionHelper;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import cn.afterturn.easypoi.util.PoiCellUtil;
import cn.afterturn.easypoi.util.PoiElUtil;
import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.util.PoiSheetUtil;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public final class ExcelExportOfTemplateUtil extends BaseExportService {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportOfTemplateUtil.class);
    private Set<String> tempCreateCellSet = new HashSet();
    private TemplateExportParams teplateParams;
    private MergedRegionHelper mergedRegionHelper;
    private TemplateSumHandler templateSumHandler;

    public ExcelExportOfTemplateUtil() {
    }

    private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook) throws Exception {
        Map<String, Integer> titlemap = this.getTitleMap(sheet);
        Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
        Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = (ExcelTarget)pojoClass.getAnnotation(ExcelTarget.class);
        String targetId = null;
        if (etarget != null) {
            targetId = etarget.value();
        }

        List<ExcelExportEntity> excelParams = new ArrayList();
        this.getAllExcelField((String[])null, targetId, fileds, excelParams, pojoClass, (List)null, (ExcelEntity)null);
        this.sortAndFilterExportField(excelParams, titlemap);
        short rowHeight = this.getRowHeight(excelParams);
        int index = this.teplateParams.getHeadingRows() + this.teplateParams.getHeadingStartRow();
        sheet.shiftRows(this.teplateParams.getHeadingRows() + this.teplateParams.getHeadingStartRow(), sheet.getLastRowNum(), this.getShiftRows(dataSet, excelParams), true, true);
        if (excelParams.size() != 0) {
            Object t;
            for(Iterator its = dataSet.iterator(); its.hasNext(); index += this.createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight)) {
                t = its.next();
            }

            this.mergeCells(sheet, excelParams, index);
        }
    }

    private int getShiftRows(Collection<?> dataSet, List<ExcelExportEntity> excelParams) throws Exception {
        int size = 0;

        Object t;
        for(Iterator its = dataSet.iterator(); its.hasNext(); size += this.getOneObjectSize(t, excelParams)) {
            t = its.next();
        }

        return size;
    }

    public int getOneObjectSize(Object t, List<ExcelExportEntity> excelParams) throws Exception {
        int maxHeight = 1;
        int k = 0;

        for(int paramSize = excelParams.size(); k < paramSize; ++k) {
            ExcelExportEntity entity = (ExcelExportEntity)excelParams.get(k);
            if (entity.getList() != null) {
                Collection<?> list = (Collection)entity.getMethod().invoke(t);
                if (list != null && list.size() > maxHeight) {
                    maxHeight = list.size();
                }
            }
        }

        return maxHeight;
    }

    public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {
        if (params != null && map != null && !StringUtils.isEmpty(params.getTemplateUrl())) {
            Workbook wb = null;

            try {
                this.teplateParams = params;
                wb = this.getCloneWorkBook();
                if (wb instanceof XSSFWorkbook) {
                    super.type = ExcelType.XSSF;
                }

                this.setExcelExportStyler((IExcelExportStyler)this.teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
                int i = 0;

                for(int le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; ++i) {
                    if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                        wb.setSheetName(i, params.getSheetName()[i]);
                    }

                    this.tempCreateCellSet.clear();
                    this.parseTemplate(wb.getSheetAt(i), map, params.isColForEach());
                }

                if (dataSet != null) {
                    this.dataHandler = params.getDataHandler();
                    if (this.dataHandler != null) {
                        this.needHandlerList = Arrays.asList(this.dataHandler.getNeedHandlerFields());
                    }

                    this.addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
                }

                return wb;
            } catch (Exception var8) {
                LOGGER.error(var8.getMessage(), var8);
                return null;
            }
        } else {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
    }

    private Workbook getCloneWorkBook() throws Exception {
        return ExcelCache.getWorkbook(this.teplateParams.getTemplateUrl(), this.teplateParams.getSheetNum(), this.teplateParams.isScanAllsheet());
    }

    private Map<String, Integer> getTitleMap(Sheet sheet) {
        Row row = null;
        Map<String, Integer> titlemap = new HashMap();

        for(int j = 0; j < this.teplateParams.getHeadingRows(); ++j) {
            row = sheet.getRow(j + this.teplateParams.getHeadingStartRow());
            Iterator<Cell> cellTitle = row.cellIterator();

            for(int i = row.getFirstCellNum(); cellTitle.hasNext(); ++i) {
                Cell cell = (Cell)cellTitle.next();
                String value = cell.getStringCellValue();
                if (!StringUtils.isEmpty(value)) {
                    titlemap.put(value, i);
                }
            }
        }

        return titlemap;
    }

    private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
        this.deleteCell(sheet, map);
        this.mergedRegionHelper = new MergedRegionHelper(sheet);
        this.templateSumHandler = new TemplateSumHandler(sheet);
        if (colForeach) {
            this.colForeach(sheet, map);
        }

        Row row = null;
        int index = 0;

        while(true) {
            do {
                if (index > sheet.getLastRowNum()) {
                    this.hanlderSumCell(sheet);
                    return;
                }

                row = sheet.getRow(index++);
            } while(row == null);

            for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); ++i) {
                if (row.getCell(i) != null && !this.tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                    this.setValueForCellByMap(row.getCell(i), map);
                }
            }
        }
    }

    private void hanlderSumCell(Sheet sheet) {
        Iterator var2 = this.templateSumHandler.getDataList().iterator();

        while(var2.hasNext()) {
            TemplateSumEntity sumEntity = (TemplateSumEntity)var2.next();
            Cell cell = sheet.getRow(sumEntity.getRow()).getCell(sumEntity.getCol());
            cell.setCellValue(cell.getStringCellValue().replace("sum:(" + sumEntity.getSumKey() + ")", sumEntity.getValue() + ""));
        }

    }

    private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {
        Row row = null;
        Cell cell = null;
        int index = 0;

        while(true) {
            do {
                if (index > sheet.getLastRowNum()) {
                    return;
                }

                row = sheet.getRow(index++);
            } while(row == null);

            for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); ++i) {
                cell = row.getCell(i);
                if (row.getCell(i) != null && (cell.getCellType() == 1 || cell.getCellType() == 0)) {
                    String text = PoiCellUtil.getCellValue(cell);
                    if (text.contains("#fe:") || text.contains("v_fe:")) {
                        this.foreachCol(cell, map, text);
                    }
                }
            }
        }
    }

    private void foreachCol(Cell cell, Map<String, Object> map, String name) throws Exception {
        boolean isCreate = name.contains("v_fe:");
        name = name.replace("v_fe:", "").replace("#fe:", "").replace("{{", "");
        String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
        Collection<?> datas = (Collection)PoiPublicUtil.getParamsValue(keys[0], map);
        Object[] columnsInfo = this.getAllDataColumns(cell, name.replace(keys[0], ""), this.mergedRegionHelper);
        if (datas != null) {
            Iterator<?> its = datas.iterator();
            int rowspan = (Integer)columnsInfo[0];
            int colspan = (Integer)columnsInfo[1];

            for(List columns = (List)columnsInfo[2]; its.hasNext(); cell = cell.getRow().getCell(cell.getColumnIndex() + colspan)) {
                Object t = its.next();
                this.setForEeachRowCellValue(true, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, this.mergedRegionHelper);
                if (cell.getRow().getCell(cell.getColumnIndex() + colspan) == null) {
                    cell.getRow().createCell(cell.getColumnIndex() + colspan);
                }
            }

            if (isCreate) {
                cell = cell.getRow().getCell(cell.getColumnIndex() - 1);
                cell.setCellValue(cell.getStringCellValue() + "}}");
            }

        }
    }

    private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
        Row row = null;
        Cell cell = null;
        int index = 0;

        while(true) {
            do {
                if (index > sheet.getLastRowNum()) {
                    return;
                }

                row = sheet.getRow(index++);
            } while(row == null);

            for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); ++i) {
                cell = row.getCell(i);
                if (row.getCell(i) != null && (cell.getCellType() == 1 || cell.getCellType() == 0)) {
                    cell.setCellType(1);
                    String text = cell.getStringCellValue();
                    if (text.contains("!if:")) {
                        if (Boolean.valueOf(PoiElUtil.eval(text.substring(text.indexOf("{{") + 2, text.indexOf("}}")).trim(), map).toString())) {
                            PoiSheetUtil.deleteColumn(sheet, i);
                            --i;
                        }

                        cell.setCellValue("");
                    }
                }
            }
        }
    }

    private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
        int cellType = cell.getCellType();
        if (cellType == 1 || cellType == 0) {
            cell.setCellType(1);
            String oldString = cell.getStringCellValue();
            if (oldString != null && oldString.indexOf("{{") != -1 && !oldString.contains("fe:")) {
                String params = null;
                boolean isNumber = false;
                if (this.isNumber(oldString)) {
                    isNumber = true;
                    oldString = oldString.replaceFirst("n:", "");
                }

                Object obj = PoiPublicUtil.getRealValue(oldString, map);
                if (obj instanceof ImageEntity) {
                    ImageEntity img = (ImageEntity)obj;
                    cell.setCellValue("");
                    if (img.getRowspan() > 1 || img.getColspan() > 1) {
                        img.setHeight(0);
                        cell.getSheet().addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + img.getRowspan() - 1, cell.getColumnIndex(), cell.getColumnIndex() + img.getColspan() - 1));
                    }


                    this.createImageCell(cell, (double)img.getHeight(), img.getUrl(), img.getData());
                } else if (isNumber && StringUtils.isNotBlank(obj.toString())) {
                    cell.setCellValue(Double.parseDouble(obj.toString()));
                    cell.setCellType(0);
                } else {
                    cell.setCellValue(obj.toString());
                }
            }

            if (oldString != null && oldString.contains("fe:")) {
                this.addListDataToExcel(cell, map, oldString.trim());
            }

        }
    }

//重写BaseExportService的createImageCell方法
    public void createImageCell(Cell cell, double height, String imagePath, byte[] data) throws Exception {
        if(height > (double)cell.getRow().getHeight()) {
            cell.getRow().setHeight((short)((int)height));
        }
        //获取当前单元格所在的sheet
        Sheet sheet = cell.getRow().getSheet();
        //获取当前sheet页中的所有合并单元格信息
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        //获取当前单元格的开始列号
        int firstColumn = (short)cell.getColumnIndex();
        //获取当前单元格的开始行号
        int firstRow = cell.getRow().getRowNum();
        //获取当前单元格的结束列号
        int lastColumn = (short)(cell.getColumnIndex());
        //获取当前单元格的结束行号
        int lastRow = cell.getRow().getRowNum();
        for(CellRangeAddress mergedRegion : mergedRegions){
            //判断当前单元格是否包含合并行或和并列 当前单元格的所有行号和列号都包含在合并域内 则认为当前单元格存在合并行或和并列
            if(cell.getColumnIndex()>=mergedRegion.getFirstColumn()
                    && cell.getColumnIndex()<=mergedRegion.getLastColumn()
                    && cell.getRow().getRowNum()>=mergedRegion.getFirstRow()
                    && cell.getRow().getRowNum()<=mergedRegion.getLastRow()){
                //获取合并域的开始行号
                firstRow = mergedRegion.getFirstRow();
                //获取合并域的结束行号
                lastRow = mergedRegion.getLastRow();
                //获取合并域的开始列号
                firstColumn = mergedRegion.getFirstColumn();
                //获取合并域的结束列号
                lastColumn = mergedRegion.getLastColumn();
                break;
            }
        }

        Object anchor;
        if(this.type.equals(ExcelType.HSSF)) {
            anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)firstColumn, firstRow, (short)(lastColumn+1), lastRow+1);
        } else {
            anchor = new XSSFClientAnchor(0, 0, 0, 0, (short)firstColumn, firstRow, (short)(lastColumn+1), lastRow+1);
        }

        if(StringUtils.isNotEmpty(imagePath)) {
            data = ImageCache.getImage(imagePath);
        }

        if(data != null) {
            PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture((ClientAnchor)anchor, cell.getSheet().getWorkbook().addPicture(data, this.getImageType(data)));
        }

    }

    private boolean isNumber(String text) {
        return text.startsWith("n:") || text.contains("{n:") || text.contains(" n:");
    }

    private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
        boolean isCreate = !name.contains("!fe:");
        boolean isShift = name.contains("$fe:");
        name = name.replace("!fe:", "").replace("$fe:", "").replace("fe:", "").replace("{{", "");
        String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
        Collection<?> datas = (Collection)PoiPublicUtil.getParamsValue(keys[0], map);
        Object[] columnsInfo = this.getAllDataColumns(cell, name.replace(keys[0], ""), this.mergedRegionHelper);
        if (datas != null) {
            Iterator<?> its = datas.iterator();
            int rowspan = (Integer)columnsInfo[0];
            int colspan = (Integer)columnsInfo[1];
            List<ExcelForEachParams> columns = (List)columnsInfo[2];
            Row row = null;
            int rowIndex = cell.getRow().getRowNum() + 1;
            Object t;
            if (its.hasNext()) {
                t = its.next();
                this.setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan, colspan, this.mergedRegionHelper);
                rowIndex += rowspan - 1;
            }

            if (isShift && datas.size() * rowspan > 1) {
                this.createRowNoRow(cell.getRowIndex() + rowspan, cell.getRow().getSheet().getLastRowNum(), (datas.size() - 1) * rowspan);
                cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, cell.getRow().getSheet().getLastRowNum(), (datas.size() - 1) * rowspan, true, true);
                this.templateSumHandler.shiftRows(cell.getRowIndex(), (datas.size() - 1) * rowspan);
            }

            while(its.hasNext()) {
                t = its.next();
                row = this.createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
                this.setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan, colspan, this.mergedRegionHelper);
                rowIndex += rowspan;
            }

        }
    }

    private void createRowNoRow(int startRow, int lastRowNum, int i1) {
    }

    private Row createRow(int rowIndex, Sheet sheet, boolean isCreate, int rows) {
        for(int i = 0; i < rows; ++i) {
            if (isCreate) {
                sheet.createRow(rowIndex++);
            } else if (sheet.getRow(rowIndex++) == null) {
                sheet.createRow(rowIndex - 1);
            }
        }

        return sheet.getRow(rowIndex - rows);
    }

    private void setForEeachRowCellValue(boolean isCreate, Row row, int columnIndex, Object t, List<ExcelForEachParams> columns, Map<String, Object> map, int rowspan, int colspan, MergedRegionHelper mergedRegionHelper) throws Exception {
        int k;
        int ci;
//        int i;
        for(int i = 0; i < rowspan; ++i) {
            k = columns.size();
            ci = columnIndex;

            for(i = columnIndex + colspan; ci < i; ++ci) {
                if (row.getCell(ci) == null) {
                    row.createCell(ci);
                    CellStyle style = row.getRowNum() % 2 == 0 ? this.getStyles(false, k >= ci - columnIndex ? null : (ExcelForEachParams)columns.get(ci - columnIndex)) : this.getStyles(true, k >= ci - columnIndex ? null : (ExcelForEachParams)columns.get(ci - columnIndex));
                    if (style != null) {
                        row.getCell(ci).setCellStyle(style);
                    }
                }
            }

            if (i < rowspan - 1) {
                row = row.getSheet().getRow(row.getRowNum() + 1);
            }
        }

        row = row.getSheet().getRow(row.getRowNum() - rowspan + 1);

        for(k = 0; k < rowspan; ++k) {
            ci = columnIndex;
            row.setHeight(((ExcelForEachParams)columns.get(0 * colspan)).getHeight());

            for(int i = 0; i < colspan && i < columns.size(); ++i) {
                boolean isNumber = false;
                ExcelForEachParams params = (ExcelForEachParams)columns.get(colspan * k + i);
                this.tempCreateCellSet.add(row.getRowNum() + "_" + ci);
                if (params != null) {
                    if (StringUtils.isEmpty(params.getName()) && StringUtils.isEmpty(params.getConstValue())) {
                        row.getCell(ci).setCellStyle(((ExcelForEachParams)columns.get(i)).getCellStyle());
                        ci += ((ExcelForEachParams)columns.get(i)).getColspan();
                    } else {
                        String val = null;
                        Object obj = null;
                        if (StringUtils.isEmpty(params.getName())) {
                            val = params.getConstValue();
                        } else {
                            String tempStr = new String(params.getName());
                            if (this.isNumber(tempStr)) {
                                isNumber = true;
                                tempStr = tempStr.replaceFirst("n:", "");
                            }

                            map.put(this.teplateParams.getTempParams(), t);
                            obj = PoiElUtil.eval(tempStr, map);
                            val = obj.toString();
                        }

                        if (obj != null && obj instanceof ImageEntity) {
                            ImageEntity img = (ImageEntity)obj;
                            row.getCell(ci).setCellValue("");
                            this.createImageCell(row.getCell(ci), (double)img.getHeight(), img.getUrl(), img.getData());
                        } else if (isNumber && StringUtils.isNotEmpty(val)) {
                            row.getCell(ci).setCellValue(Double.parseDouble(val));
                            row.getCell(ci).setCellType(0);
                        } else {
                            try {
                                row.getCell(ci).setCellValue(val);
                            } catch (Exception var18) {
                                LOGGER.error(var18.getMessage(), var18);
                            }
                        }

                        row.getCell(ci).setCellStyle(params.getCellStyle());
                        if (params.isNeedSum()) {
                            this.templateSumHandler.addValueOfKey(params.getName(), val);
                        }

                        this.setMergedRegionStyle(row, ci, params);
                        if ((params.getRowspan() != 1 || params.getColspan() != 1) && !mergedRegionHelper.isMergedRegion(row.getRowNum() + 1, ci)) {
                            row.getSheet().addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum() + params.getRowspan() - 1, ci, ci + params.getColspan() - 1));
                        }

                        ci += params.getColspan();
                    }
                }
            }

            row = row.getSheet().getRow(row.getRowNum() + 1);
        }

    }

    private CellStyle getStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return this.excelExportStyler.getTemplateStyles(isSingle, excelForEachParams);
    }

    private void setMergedRegionStyle(Row row, int ci, ExcelForEachParams params) {
        int i;
        for(i = 1; i < params.getColspan(); ++i) {
            row.getCell(ci + i).setCellStyle(params.getCellStyle());
        }

        for(i = 1; i < params.getRowspan(); ++i) {
            for(int j = 0; j < params.getColspan(); ++j) {
                row.getCell(ci + j).setCellStyle(params.getCellStyle());
            }
        }

    }

    private Object[] getAllDataColumns(Cell cell, String name, MergedRegionHelper mergedRegionHelper) {
        List<ExcelForEachParams> columns = new ArrayList();
        cell.setCellValue("");
        columns.add(this.getExcelTemplateParams(name.replace("}}", ""), cell, mergedRegionHelper));
        int rowspan = 1;
        int colspan = 1;
        int index;
        if (!name.contains("}}")) {
            index = cell.getColumnIndex();
            int startIndex = cell.getColumnIndex();
            Row row = cell.getRow();

            label73:
            while(true) {
                while(true) {
                    if (index >= row.getLastCellNum()) {
                        break label73;
                    }

                    int colSpan = columns.get(columns.size() - 1) != null ? ((ExcelForEachParams)columns.get(columns.size() - 1)).getColspan() : 1;
                    index += colSpan;

                    for(int i = 1; i < colSpan; ++i) {
                        columns.add(null);
                    }

                    cell = row.getCell(index);
                    if (cell == null) {
                        columns.add(null);
                    } else {
                        String cellStringString;
                        try {
                            cellStringString = cell.getStringCellValue();
                            if (StringUtils.isBlank(cellStringString) && colspan + startIndex <= index) {
                                throw new ExcelExportException("for each 当中存在空字符串,请检查模板");
                            }

                            if (StringUtils.isBlank(cellStringString) && colspan + startIndex > index) {
                                columns.add(new ExcelForEachParams((String)null, cell.getCellStyle(), (short)0));
                                continue;
                            }
                        } catch (Exception var13) {
                            throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR, var13);
                        }

                        cell.setCellValue("");
                        if (cellStringString.contains("}}")) {
                            columns.add(this.getExcelTemplateParams(cellStringString.replace("}}", ""), cell, mergedRegionHelper));
                            break label73;
                        }

                        if (cellStringString.contains("]]")) {
                            columns.add(this.getExcelTemplateParams(cellStringString.replace("]]", ""), cell, mergedRegionHelper));
                            colspan = index - startIndex + 1;
                            index = startIndex - 1;
                            row = row.getSheet().getRow(row.getRowNum() + 1);
                            ++rowspan;
                        } else {
                            columns.add(this.getExcelTemplateParams(cellStringString.replace("]]", ""), cell, mergedRegionHelper));
                        }
                    }
                }
            }
        }

        colspan = 0;

        for(index = 0; index < columns.size(); ++index) {
            colspan += columns.get(index) != null ? ((ExcelForEachParams)columns.get(index)).getColspan() : 0;
        }

        colspan /= rowspan;
        return new Object[]{rowspan, colspan, columns};
    }

    private ExcelForEachParams getExcelTemplateParams(String name, Cell cell, MergedRegionHelper mergedRegionHelper) {
        name = name.trim();
        ExcelForEachParams params = new ExcelForEachParams(name, cell.getCellStyle(), cell.getRow().getHeight());
        if (name.startsWith("'") && name.endsWith("'")) {
            params.setName((String)null);
            params.setConstValue(name.substring(1, name.length() - 1));
        }

        if ("&NULL&".equals(name)) {
            params.setName((String)null);
            params.setConstValue("");
        }

        if (mergedRegionHelper.isMergedRegion(cell.getRowIndex() + 1, cell.getColumnIndex())) {
            Integer[] colAndrow = mergedRegionHelper.getRowAndColSpan(cell.getRowIndex() + 1, cell.getColumnIndex());
            params.setRowspan(colAndrow[0]);
            params.setColspan(colAndrow[1]);
        }

        params.setNeedSum(this.templateSumHandler.isSumKey(params.getName()));
        return params;
    }

    private void sortAndFilterExportField(List<ExcelExportEntity> excelParams, Map<String, Integer> titlemap) {
        for(int i = excelParams.size() - 1; i >= 0; --i) {
            if (((ExcelExportEntity)excelParams.get(i)).getList() != null && ((ExcelExportEntity)excelParams.get(i)).getList().size() > 0) {
                this.sortAndFilterExportField(((ExcelExportEntity)excelParams.get(i)).getList(), titlemap);
                if (((ExcelExportEntity)excelParams.get(i)).getList().size() == 0) {
                    excelParams.remove(i);
                } else {
                    ((ExcelExportEntity)excelParams.get(i)).setOrderNum(i);
                }
            } else if (titlemap.containsKey(((ExcelExportEntity)excelParams.get(i)).getName())) {
                ((ExcelExportEntity)excelParams.get(i)).setOrderNum(i);
            } else {
                excelParams.remove(i);
            }
        }

        this.sortAllParams(excelParams);
    }

    public Workbook createExcleByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) {
        if (params != null && map != null && !StringUtils.isEmpty(params.getTemplateUrl())) {
            Workbook wb = null;

            try {
                this.teplateParams = params;
                wb = this.getCloneWorkBook();
                int i = 0;

                for(int le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params.getSheetNum().length; i < le; ++i) {
                    if (params.getSheetName() != null && params.getSheetName().length > i && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                        wb.setSheetName(i, params.getSheetName()[i]);
                    }

                    this.tempCreateCellSet.clear();
                    this.parseTemplate(wb.getSheetAt(i), (Map)map.get(i), params.isColForEach());
                }

                return wb;
            } catch (Exception var6) {
                LOGGER.error(var6.getMessage(), var6);
                return null;
            }
        } else {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
    }
}

pom依赖包：

        <!--export Excel-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

 

参考文章：

https://blog.csdn.net/weixin_36380516/article/details/108426874
使用 EasyPOI 优雅导出Excel模板数据（含图片）
https://blog.csdn.net/j3T9Z7H/article/details/108744030
使用 EasyPOI 优雅导出Excel模板数据（含图片）
https://blog.csdn.net/li246464/article/details/88056778
EasyPOI模板导出图片存在合并单元格只填充一个单元格问题

keyword: TemplateExportParams
https://blog.csdn.net/FunnyWhiteCat/article/details/105641942
easypoi 读取resource下的模板时TemplateExportParams的设置

 

评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值