Java导出Excel并且自定义单元格合并(easyPoi)
先贴效果图
依赖引入
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
controller层
@ResponseBody
@RequestMapping("/fadPoi")
public void fxpjDownloadPoi(HttpServletRequest request, HttpServletResponse response) throws IOException {
try {
List<ProjectTargetInfoExcelPoi> projectInfo = projectTargetInfoService.selFxSpecPoi();
String pjs = projectTargetInfoService.selFxSpecPjs();
PoiExcelUtil.exportExcelEasyPoi((ArrayList<ProjectTargetInfoExcelPoi>) projectInfo, ProjectTargetInfoExcelPoi.class, "指标情况分析", "指标情况分析", response, pjs);
} catch (Exception e) {
e.printStackTrace();
}
}
service层在这里就不贴了,里面都是查出的数据,进行处理业务逻辑,按照自己的逻辑处理好返回时是List集合就可以。
实体类
实体类在这里只贴了部分例子,按照这个格式编写即可,一定要跟数据库字段对上
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class ProjectTargetInfoExcelPoi implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 行次
*/
@Excel(name = "行次", width = 20, orderNum = "1")
private String rankNo;
/**
* 名称
*/
@Excel(name = "评价指标", width = 75, orderNum = "2")
private String name;
@Excel(name = "管理部门", width = 20, orderNum = "3")
private String leadDept;
/**
* 指标值
*/
@Excel(name = "本期", width = 20, orderNum = "4")
private String premium;
}
工具类
CellRangeAddress craOne = new CellRangeAddress(2, 2, 0, 9) 这是单元格合并,指对第几行和第几列进行合并距中。
ArrayList<ProjectTargetInfoExcelPoi> projectInfos = projectInfoPois(listData, pjs); 这是对数据进行处理,这里就不贴了。
public static void exportExcelEasyPoi(ArrayList<ProjectTargetInfoExcelPoi> listData, Class<?> pojoClass, String headTitle, String sheetName, HttpServletResponse response, String pjs) {
ExportParams params = new ExportParams(headTitle, sheetName, ExcelType.XSSF);
ArrayList<ProjectTargetInfoExcelPoi> projectInfos = projectInfoPois(listData, pjs);
Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, projectInfos);
CellRangeAddress craOne = new CellRangeAddress(2, 2, 0, 9);
// CellRangeAddress craOne1 = new CellRangeAddress(3, 3, 0, 9);
CellRangeAddress craOne2 = new CellRangeAddress(3, 3, 0, 3);
CellRangeAddress craOne3 = new CellRangeAddress(56, 56, 0, 3);
Sheet sheet = workbook.getSheetAt(0);
sheet.addMergedRegion(craOne);
// sheet.addMergedRegion(craOne1);
sheet.addMergedRegion(craOne2);
sheet.addMergedRegion(craOne3);
try {
String fileName = headTitle + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (Exception e) {
e.printStackTrace();
}
}