EasyExcel数据写入

EasyExcel数据写入

写入表头对象信息：

public class MD66EventExcelHeader {

    @ColumnWidth(20) //列表宽度
    @ExcelProperty(value = "样本编号", index = 0)
    public String sampleNumber;

    @ColumnWidth(10)
    @ExcelProperty(value = "样本ID", index = 1)
    public String sampleID;

    @ColumnWidth(10)
    @ExcelProperty(value = "病人ID", index = 2)
    public String patientID;

    @ColumnWidth(10)
    @ExcelProperty(value = "姓名", index = 3)
    public String name;

    @ColumnWidth(30)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")  
    @ExcelProperty(value = "测试时间", index = 4)
    public Date testEndTime;

    @ExcelProperty(value = "项目", index = 5)
    public String projectName;

    @ExcelProperty(value = "子项目", index = 6)
    public String sonProjectName;

    @ColumnWidth(50)
    @ExcelProperty(value = "数据查看", index = 7)
    public String parameters;
    
    @ExcelIgnore  //忽略这个字段
    private String ignore;
}

写入数据信息列表：

public List getExcelData(ParamEntity paramEntity) {
	if (paramEntity.getIdentifier().equals("FinishProjectTest")) {
        List<MD66EventExcelHeader> eventExcelList = new ArrayList<>();
        List<EventLog> commonEventLog = eventLogRepository.findCommonEventLog(paramEntity);
        if (null == commonEventLog || commonEventLog.size() == 0) {
        	return null;
        }
        for (EventLog eventLog : commonEventLog) {
            MD66EventExcelHeader md66EventExcelHeader = new MD66EventExcelHeader();
            com.alibaba.fastjson.JSONObject jsonObject = (com.alibaba.fastjson.JSONObject) JSON.toJSON(eventLog.getLogContent().getJSONObject("params").getJSONObject("outputData"));
            md66EventExcelHeader.setSampleNumber(jsonObject.getString("SampleNumber"));
            md66EventExcelHeader.setSampleID(jsonObject.getString("SampleID"));
            md66EventExcelHeader.setPatientID(jsonObject.getString("PatientID"));
            md66EventExcelHeader.setTestEndTime(jsonObject.getDate("TestEndTime").getTime() != 0 ? jsonObject.getDate("TestEndTime") : null);
            md66EventExcelHeader.setProjectName(jsonObject.getString("ProjectName"));
            md66EventExcelHeader.setSonProjectName(jsonObject.getString("SonProjectName"));
            com.alibaba.fastjson.JSONArray jsonArray = jsonObject.getJSONArray("Parameters");
            StringBuilder parameters = new StringBuilder();
            for (int i = 0; i < jsonArray.size(); i++) {
                String parameter = jsonArray.getJSONObject(i).getString("Parameter");
                String value = jsonArray.getJSONObject(i).getString("Value");
                parameters.append(parameter).append(":").append(value).append("; ");
            }
            md66EventExcelHeader.setParameters(parameters.toString());
            eventExcelList.add(md66EventExcelHeader);
        }
        return eventExcelList;
    }
}

web中的写：

@GetMapping("download")
public void download(HttpServletResponse response,
                     @RequestBody(required = false) ParamEntity paramEntity) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    EasyExcel.write(response.getOutputStream(), MD66EventExcelHeader.class)
        .registerWriteHandler(horizontalCellStyleStrategy).sheet("模板").doWrite(getExcelData(paramEntity));
}

web中的写并且失败的时候返回json：

@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 这里需要设置不关闭流
        EasyExcel.write(response.getOutputStream(), DownloadData.class).autoCloseStream(Boolean.FALSE).sheet("模板")
        .doWrite(data());
    } catch (Exception e) {
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = new HashMap<String, String>();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + e.getMessage());
        response.getWriter().println(JSON.toJSONString(map));
    }
}

JSONObject.getString(key) 当value为null时 sf.json会解析成'null'字符串。fastjson会解析成null对象