easypoi导出Excel报表(报表存入Aliyun OSS，返回前端路径)

一、导入依赖

<!-- easypoi -->
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>4.4.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
	<artifactId>easypoi-annotation</artifactId>
	<version>4.4.0</version>
</dependency>

二、单表头Excel

1.controller层

@GetMapping("/export/studentExcel")
public ResponseEntity exportStudentExcel(){
    return ResponseEntity.ok(studentService.exportStudentExcel());
}

2.service层

public String exportDataDownload() {
        // Excel表的参数——title、sheetName、ExcelType
        ExportParams exportParams = new ExportParams("学生表", "学生表", ExcelType.XSSF);

        // Excel表的样式，可实现IExcelExportStyler接口自定义
        exportParams.setStyle(ExcelStyleUtil.class);

        // 表头ExcelExportEntity(name,key,width)的集合
        List<ExcelExportEntity> entityList = new ArrayList<>();
        ExcelExportEntity stuNumEntity = new ExcelExportEntity("学号", "stuNum", 10);
        entityList.add(stuNumEntity);
        ExcelExportEntity stuNameEntity = new ExcelExportEntity("姓名", "stuName", 30);
        entityList.add(stuNameEntity);
        ExcelExportEntity gradeEntity = new ExcelExportEntity("年级", "grade", 20);
        // 列的合并
        // gradeEntity.setMergeVertical(true);
        entityList.add(gradeEntity);

        // 表体-Map集合，Map的key要和表头中的key对应
        List<Map<String, Object>> dataList = new ArrayList<>();
        Map<String, Object> stu1 = new HashMap<>();
        stu1.put("stuNum", "4184002223");
        stu1.put("stuName", "酉曲豆");
        stu1.put("grade", 3);
        dataList.add(stu1);
        Map<String, Object> stu2 = new HashMap<>();
        stu2.put("stuNum", "4184002222");
        stu2.put("stuName", "夏禾");
        stu2.put("grade", 6);
        dataList.add(stu2);

        // 生成Workbook对象
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, dataList);
        return fileService.getFilePath("学生表.xlsx", workbook);
    }

3.ExcelStyleUtil(样式示例)和fileService.getFilePath()方法

 ExcelStyleUtil：

public class ExcelStyleUtil implements IExcelExportStyler {
	private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
	private static final short FONT_SIZE_TEN = 9;
	private static final short FONT_SIZE_ELEVEN = 10;
	private static final short FONT_SIZE_TWELVE = 10;
	/**
	 * 大标题样式
	 */
	private CellStyle headerStyle;
	/**
	 * 每列标题样式
	 */
	private CellStyle titleStyle;
	/**
	 * 数据行样式
	 */
	private CellStyle styles;

	public ExcelStyleUtil(Workbook workbook) {
		this.init(workbook);
	}

	/**
	 * 初始化样式
	 *
	 * @param workbook
	 */
	private void init(Workbook workbook) {
		this.headerStyle = initHeaderStyle(workbook);
		this.titleStyle = initTitleStyle(workbook);
		this.styles = initStyles(workbook);
	}

	/**
	 * 大标题样式
	 *
	 * @param color
	 * @return
	 */
	@Override
	public CellStyle getHeaderStyle(short color) {
		return headerStyle;
	}

	/**
	 * 每列标题样式
	 *
	 * @param color
	 * @return
	 */
	@Override
	public CellStyle getTitleStyle(short color) {
		return titleStyle;
	}

	/**
	 * 数据行样式
	 *
	 * @param parity 可以用来表示奇偶行
	 * @param entity 数据内容
	 * @return 样式
	 */
	@Override
	public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
		return styles;
	}

	/**
	 * 获取样式方法
	 *
	 * @param dataRow 数据行
	 * @param obj     对象
	 * @param data    数据
	 */
	@Override
	public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
		return getStyles(true, entity);
	}

	/**
	 * 模板使用的样式设置
	 */
	@Override
	public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
		return null;
	}

	/**
	 * 初始化--大标题样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initHeaderStyle(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
		return style;
	}

	/**
	 * 初始化--每列标题样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initTitleStyle(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
		//背景色
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		return style;
	}

	/**
	 * 初始化--数据行样式
	 *
	 * @param workbook
	 * @return
	 */
	private CellStyle initStyles(Workbook workbook) {
		CellStyle style = getBaseCellStyle(workbook);
		style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
		style.setDataFormat(STRING_FORMAT);
		return style;
	}

	/**
	 * 基础样式
	 *
	 * @return
	 */
	private CellStyle getBaseCellStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		//下边框
		style.setBorderBottom(BorderStyle.THIN);
		//左边框
		style.setBorderLeft(BorderStyle.THIN);
		//上边框
		style.setBorderTop(BorderStyle.THIN);
		//右边框
		style.setBorderRight(BorderStyle.THIN);
		//水平居中
		style.setAlignment(HorizontalAlignment.CENTER);
		//上下居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		//设置自动换行
		style.setWrapText(true);
		return style;
	}

	/**
	 * 字体样式
	 *
	 * @param size   字体大小
	 * @param isBold 是否加粗
	 * @return
	 */
	private Font getFont(Workbook workbook, short size, boolean isBold) {
		Font font = workbook.createFont();
		//字体样式
		font.setFontName("宋体");
		//是否加粗
		font.setBold(isBold);
		//字体大小
		font.setFontHeightInPoints(size);
		return font;
	}
}

fileService.getFilePath()：

@Service
public class FileService {
    // aliyunoss的配置
    @Value("${oss.access_id}")
    private String accessID;
    @Value("${oss.access_key}")
    private String accessKey;
    @Value("${oss.endpoint}")
    private String endPoint;
    @Value("${oss.bucket}")
    protected String bucket;
    @Value("${oss.path}")
    protected String path;

    private OSSClient ossClient;

    public OSSClient getOssClient() {
        if (ossClient == null) {
            ossClient = new OSSClient(endPoint, accessID, accessKey);
        }
        return ossClient;
    }


    public String getFilePath(String fileName, Workbook workbook) {
        InputStream excelStream = null;
        String key = LocalDate.now().toString().replace("-", "") + "/" + fileName;
        String localPath = path + key;
        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            workbook.close();
            excelStream = new ByteArrayInputStream(out.toByteArray());
            this.getOssClient().putObject(bucket, key, excelStream);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return localPath;
    }
}

三、根据Excel模板生成Excel(可满很多特定需求)

EasyPoi支持的指令，最主要的就是各种fe的用法

• fe: 遍历数据,创建row
• !fe: 遍历数据不创建row
• $fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
• #fe: 横向遍历
• v_fe: 横向遍历值

整体风格和el表达式类似，大家应该也比较熟悉 采用的写法是{{}}代表表达式，然后根据表达式里面的数据取值

关于样式问题 easypoi不会改变excel原有的样式，如果是遍历，easypoi会根据模板的那一行样式进行复制

更详细的可看官方文档：https://easypoi.mydoc.io/

使用如下图：

@Test
public void fe_map() throws Exception {
    TemplateExportParams params = new TemplateExportParams(
                "D:\\xxx学校学生模板.xlsx");
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("schoolName", "天神书院");
    map.put("schoolAddress", "九重天");
    map.put("schoolTel", "88888");
    map.put("startDate", "2021-01-01");
    map.put("endDate", "至今");
    List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
    for (int i = 0; i < 4; i++) {
        Map<String, String> lm = new HashMap<String, String>();
        lm.put("stuNum", "10000" + i);
        lm.put("stuName", "有趣的" + i);
        lm.put("sex", i%2==0 ? "女" : "男");
        lm.put("stuGrade", i);
    }
    map.put("maplist", listMap);

    Workbook workbook = ExcelExportUtil.exportExcel(params, map);
    File savefile = new File("D:/excel/");
    if (!savefile.exists()) {
        savefile.mkdirs();
    }
    FileOutputStream fos = new FileOutputStream("D:/excel/xxx学校学生表.xlsx");
    workbook.write(fos);
    fos.close();
}

四、根据单个Excel模板生成一个多sheet的Excel

        // 引入模板
        // TemplateExportParams templateExportParams = new TemplateExportParams("D:\\门店缺货率测试.xlsx");
        
        // 将所有sheet使用得map进行包装
        Map<Integer, List<Map<String, Object>>> sheetMap = new HashMap<>();

        for (int i = 0;i < 10;i++) {
            List<Map<String, Object>> allMapList = new ArrayList<>();
            Map<String, Object> map = new HashMap<>();
            // 设置sheetName，若不设置该参数，则使用得原本的sheet名称
            map.put("sheetName", "大药店" + i + "号");// 此处填sheet页名称
            // 需与模板文件中字段对应
            map.put("storeName", i + "号店" );
            map.put("saNum", i * 2);
            map.put("sbNum", i * 3);
            map.put("saLackNum", i * 4);
            map.put("sbLackNum", i * 5);
            map.put("saLackRate", i * 0.2);
            map.put("sbLackRate", i * 0.3);
            List<Map<String, String>> mapList = new ArrayList<>();
            for(int j = 1; j <= 5; j++){
                // 创建商品数据
                Map<String,String> skuMap = new HashMap<>();
                skuMap.put("skuNumber", j + "11");
                skuMap.put("sku", j + "22");
                skuMap.put("unit", j + "33");
                skuMap.put("returnFlag", j + "44");
                skuMap.put("level", j + "55");
                skuMap.put("averageSale", j + "66");
                skuMap.put("max", j + "77");
                skuMap.put("mini", j + "88");
                skuMap.put("mouthSale", j + "99");
                skuMap.put("stock", j + "00");
                skuMap.put("turnDays", j + "12");
                skuMap.put("lackStr", j + "13");
                skuMap.put("category", j + "14");
                skuMap.put("replenishmentNum", j + "15");
                mapList.add(skuMap);
            }
            map.put("mapList", mapList);

            allMapList.add(map);
            // 一个sheet页
            sheetMap.put(i, allMapList);
        }
        // 导出excel
        Workbook workbook = ExcelExportUtil.exportExcelClone(sheetMap, templateExportParams);
        return fileService.getFilePath("批量导出门店缺货测试.xlsx",workbook);