EasyExcel資料匯出實現、動態表頭生成、SpringBoot3框架

1、引入EasyExcel依賴

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.3.2</version>
    </dependency>

2、定義ExcelModel表單模型

public class ExcelModel implements Serializable {
	private String fileName;//定義匯出檔名
	private String[] headMap;//定義匯出匯出表頭
	private String[] fieldMap;//定義指導匯出欄位
	private List<Map<String,Object>> datalist;//表單資料
}

3、定義ExcelUtils工具類

public static void dataWrite(@RequestBody ExcelModel excelModel,HttpServletResponse response){
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    try {
        String fileName = URLEncoder.encode(excelModel.getFileName(),"UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream()).head(setHead(excelModel.getHeadMap())).sheet(excelModel.getFileName()).doWrite(setData(excelModel.getDatalist(),excelModel.getFieldMap()));
        // 這裡需要設定不關閉流
    } catch (Exception e) {
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        throw new SunException("下載失敗",e);
    }
}

/**
 * 設定表格表頭
 * @param headMap Stirng[] 指定需要匯出的表頭
 * @return
 */
public static List<List<String>> setHead(String[] headMap){
    List<List<String>> list = new ArrayList<List<String>>();
    for (String head : headMap ){
        List<String> h = new ArrayList<String>();
        h.add(head);
        list.add(h);
    }
    return list;
}

/**
 * 設定表單資料
 * @param datalist List<Map<String,Object>>格式、方便使用Map集合的資料處理
 * @param fieldMap String[] 指定需要的匯出欄位的陣列
 * @return
 */
public static List<List<Object>> setData(List<Map<String,Object>> datalist,String[] fieldMap){
    List<List<Object>> lists = new ArrayList<List<Object>>();
    for(Map<String,Object> map : datalist){
        List<Object> list = new ArrayList<Object>();
        for (int i=0;i<fieldMap.length;i++){
            list.add(map.get(fieldMap[i]));
        }
        lists.add(list);
    }
    return lists;
}

4、介面具體實現類

public class SysLogServiceImpl extends ServiceImpl<SysLogMapper, SysLog>
implements SysLogService{

	@Override
	public List<Map<String,Object>> queryAllLog(Map<String, Object> params) {
		return baseMapper.selectMaps(new QueryWrapper<>());
	}
}

5、控制類進行具體實現

    @GetMapping("/export")
public void export(@RequestParam Map<String,Object> params){

    HttpServletResponse res = HttpContextUtils.getHttpServletResponse();
    List<Map<String,Object>> list = sysLogService.queryAllLog(params);
    ExcelModel model = new ExcelModel();
    model.setFileName("匯出測試");
    String[] headMap ={"使用者名稱","操作方式"};//自定義表頭
    String[] dataMap ={"username","operation"};//自定義匯出字串
    model.setHeadMap(headMap);
    model.setFieldMap(dataMap);
    model.setDatalist(list);
    ExcelUtils.dataWrite(model,res);
}