1.示例代码

/*** 导出excel表格** @return excel 文件流*/@ResponseExcel@ApiOperation(value = "导出excel表格", notes = "导出详情excel表格")@GetMapping("/export")public void export(HttpServletResponse response) {try {Map<String, Object> map = new HashMap<>();List<PlanningDetailVo> list=new ArrayList<>();list.addAll(PlanningDetailVo.testList1());list.addAll(PlanningDetailVo.testList2());map.put("list",list);// 获取模板文件String path = "excelFile/重点工作计划考核指标.xlsx";TemplateExportParams params = new TemplateExportParams(path);Workbook workbook = ExcelExportUtil.exportExcel(params, map);Sheet sheet = workbook.getSheet("sheet1");for (int i = 0; i < list.size(); i++) {PlanningDetailVo vo = list.get(i);if (vo.getIndex()!=null) {if (vo.getIndex().length()>=5){//合并标题列CellRangeAddress cell=new CellRangeAddress(i,i,0,2);sheet.addMergedRegion(cell);setFontAlign(workbook,i,0);}if ("序号".equals(vo.getIndex())||vo.getIndex().length()==1){//设置边框for (int j = 0; j <3; j++) {setBorder(workbook,i,j);}}}}// 设置文件名response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("重点工作计划考核指标.xlsx", "UTF-8"));// 写出文件OutputStream os = new BufferedOutputStream(response.getOutputStream());// 输出workbook.write(os);os.flush();os.close();os.close();} catch (Exception e) {e.printStackTrace();}}/*** 给指定行指定列设置字体及居中* @param workbook* @param rowIndex 第几行* @param columnIndex 第几列*/
private void setFontAlign(Workbook workbook,int rowIndex,int columnIndex){Sheet sheet = workbook.getSheet("sheet1");CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setBold(true);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐在单元格的高度上居中Row row = sheet.getRow(rowIndex);Cell cell = row.getCell(columnIndex);cell.setCellStyle(cellStyle);
}/*** 给指定行指定列设置边框* @param workbook* @param rowIndex 第几行* @param columnIndex 第几列*/
private void setBorder(Workbook workbook,int rowIndex,int columnIndex){Sheet sheet = workbook.getSheet("sheet1");CellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐在单元格的高度上居中cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);Row row = sheet.getRow(rowIndex);Cell cell = row.getCell(columnIndex);cell.setCellStyle(cellStyle);
}

2.模拟数据

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PlanningDetailVo {//序号(部门名称)private String index;//类型private String typeName;//备注private String remark;public static List<PlanningDetailVo> testList1(){List<PlanningDetailVo> list=new ArrayList<>();//第一行标题list.add(new PlanningDetailVo("信息管理部","",""));//第二行表头list.add(new PlanningDetailVo("序号","类型","备注"));//第三行开始为数据list.add(new PlanningDetailVo("1","指标1","备注1"));list.add(new PlanningDetailVo("2","指标2","备注2"));list.add(new PlanningDetailVo("3","指标3","备注3"));list.add(new PlanningDetailVo("4","指标4","备注4"));list.add(new PlanningDetailVo("5","指标5","备注5"));return list;}public static List<PlanningDetailVo> testList2(){List<PlanningDetailVo> list=new ArrayList<>();//第一行标题list.add(new PlanningDetailVo("科技管理部","",""));//第二行表头list.add(new PlanningDetailVo("序号","类型","备注"));//第三行开始为数据list.add(new PlanningDetailVo("1","考核1","备注0001"));list.add(new PlanningDetailVo("2","考核2","备注0002"));list.add(new PlanningDetailVo("3","考核3","备注0003"));list.add(new PlanningDetailVo("4","考核4","备注0004"));list.add(new PlanningDetailVo("5","考核5","备注0005"));return list;}
}