使用SpringEL功能动态解析模板Excel来生成Excel

冷不防 2021-11-02 11:14 469阅读 0赞

SpringEL可以参考：[https://www.cnblogs.com/yangzhilong/p/11282560.html][https_www.cnblogs.com_yangzhilong_p_11282560.html]

本文基于POI 3.1.7，spring 5.1.7编写。

由于excel里的\#是数字的关键字，所有我使用$\{\}来替换了\#字符。

核心帮助类：

package com.longge.util;
    
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.springframework.core.io.ClassPathResource;
    import org.springframework.expression.EvaluationContext;
    import org.springframework.expression.ExpressionParser;
    import org.springframework.expression.spel.standard.SpelExpressionParser;
    import org.springframework.expression.spel.support.StandardEvaluationContext;
    import org.springframework.util.CollectionUtils;
    
    /**
     * @author yangzhilong
     * @date 8/01/2019
     */
    public class ExcelTemplateWriteUtils {
        
        /**
         * get excel template Workbook
         * @param templateFilePath  eg: static/excelTemplate/xx.xlsx
         * @return
         */
        public static Workbook getWorkbook(String templateFilePath) {
            try {
                InputStream is;
                if(templateFilePath.startsWith("/")) {
                    is = ExcelTemplateWriteUtils.class.getResourceAsStream(templateFilePath);
                } else {
                    is = new ClassPathResource(templateFilePath).getInputStream();
                }
                return WorkbookFactory.create(is);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        
        /**
         * @param sheet
         * @param templateRowIndex
         * @param list
         */
        public static void fillDataWithTemplate(Sheet sheet, int templateRowIndex, List<? extends Object> list) {
            int tIndex = templateRowIndex;
            Row templateRow = sheet.getRow(templateRowIndex);
            if(CollectionUtils.isEmpty(list)) {
                sheet.removeRow(templateRow);
            } else {
                List<Object> template = new ArrayList<>(templateRow.getPhysicalNumberOfCells());
                List<CellStyle> sytles = new ArrayList<>(templateRow.getPhysicalNumberOfCells());
                templateRow.forEach(cell -> {
                    sytles.add(cell.getCellStyle());
                    cell.getCellStyle();
                    CellType type = cell.getCellTypeEnum();
                    if(CellType.NUMERIC.equals(type)) {
                        template.add(cell.getNumericCellValue());
                    } else if(CellType.STRING.equals(type)) {
                        String value = cell.getStringCellValue();
                        if(value.startsWith("${")) {
                            value = formatEl(value);
                        }
                        template.add(value);
                    } else if(CellType.BLANK.equals(type)) {
                        template.add(null);
                    } else {
                        throw new RuntimeException("can't support cell type:" + type.toString());
                    }
                });
                ExpressionParser parser = new SpelExpressionParser();
                for(int i=0,len=list.size(); i<len; i++) {
                    Object item = list.get(i);
                    
                    Row newRow = sheet.createRow(tIndex++);
                    EvaluationContext context = new StandardEvaluationContext();
                    context.setVariable("item", item);
                    
                    ForEachUtils.forEach(0, template, (index, value) -> {
                        Cell newCell = newRow.createCell(index);
                        newCell.setCellStyle(sytles.get(index));
                        if(null != value) {
                            if (value instanceof String) {
                                String v = (String)value;
                                if(v.startsWith("#")) {
                                    v = parser.parseExpression(v).getValue(context, String.class);
                                }
                                newCell.setCellValue(v);
                            } else if(value instanceof Number) {
                                newCell.setCellValue(((Number)value).doubleValue());
                            }
                        }
                    });
                }
            }
        }
        
        private static String formatEl(String el) {
            return el.replace("${", "#").replace("}", "");
        }
    }

单元测试类：

package com.longge.util;
    
    import static org.junit.Assert.assertEquals;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.UUID;
    
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.junit.Test;
    
    import com.longge.dto.CustomerDto;
    
    /**
     * @author yangzhilong
     * @date 8/01/2019
     */
    public class ExcelTemplateWriteUtilsTest {
        @Test
        public void test() throws Exception {
            String path = "util/template.xlsx";
            //String path = "/util/template.xlsx";
            Workbook workbook = ExcelTemplateWriteUtils.getWorkbook(path);
            Sheet sheet = workbook.getSheetAt(0);
            
            List<CustomerDto> list = new ArrayList<>(10);
            for(int i=0; i<10; i++) {
                CustomerDto dto = new CustomerDto();
                dto.setChineseName("name:" + i);
                dto.setCustomerCode("code:" + i);
                dto.setOwnerGroup("group:" + i);
                list.add(dto);
            }
            
            ExcelTemplateWriteUtils.fillDataWithTemplate(sheet, 1, list);
            
            assertEquals(sheet.getPhysicalNumberOfRows(), 11);
            
            workbook.write(new FileOutputStream(new File("C:\\Users\\RYan27\\Desktop\\test\\" + UUID.randomUUID().toString() + ".xlsx")));
            workbook.close();
        }
    }

模板文件：

![493447-20190801155519565-1589173682.png][]

![493447-20190801155559694-662545633.png][]

最终生成的文件实例：

![493447-20190801155652366-689641375.png][]

转载于:https://www.cnblogs.com/yangzhilong/p/11283155.html

[https_www.cnblogs.com_yangzhilong_p_11282560.html]: https://www.cnblogs.com/yangzhilong/p/11282560.html
[493447-20190801155519565-1589173682.png]: /images/20211101/017a63192ef9450d9860bca51c3f4ddc.png
[493447-20190801155559694-662545633.png]: /images/20211101/6f76622ace204e8387530bb771783d51.png
[493447-20190801155652366-689641375.png]: /images/20211101/218c6c97b7634843997a774bd08db9fe.png