java的Excel导出，包括单元格的合并，样式设置
@Data
public class EmergencyReportingEntity {
private String mainTitle;
private List subTitle;
private List data;
}
数据处理
public Workbook exportExcel() {
// 根据数据生成 List
List list = new ArrayList<>();
addData1(list);
addData2(list);
return xlsOutputResourcePreparation(list);
}
public void addData1(List list) {
EmergencyReportingEntity emergencyReportingEntity = new EmergencyReportingEntity();
emergencyReportingEntity.setMainTitle("事件类型");
emergencyReportingEntity.setSubTitle(Arrays.asList("事件类型", "事件数量"));
List> dataList = new ArrayList<>();
for (int i = 0; i < 4; i++) {
List data = new ArrayList<>();
data.add("自然灾害" + i);
data.add("22" + i);
dataList.add(data);
}
emergencyReportingEntity.setData(dataList);
list.add(emergencyReportingEntity);
}
public void addData2(List list) {
EmergencyReportingEntity emergencyReportingEntity = new EmergencyReportingEntity();
emergencyReportingEntity.setMainTitle("重点突出类型");
emergencyReportingEntity.setSubTitle(Arrays.asList("类型名称", "类型数量"));
List> dataList = new ArrayList<>();
for (int i = 0; i < 7; i++) {
List data = new ArrayList<>();
data.add("安全生产" + i);
data.add("88" + i);
dataList.add(data);
}
emergencyReportingEntity.setData(dataList);
list.add(emergencyReportingEntity);
}
public Workbook xlsOutputResourcePreparation(List list) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("社会治理资源准备情况");
for (int i = 0; i < 4; i++) {
sheet.setColumnWidth(i, 10000);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
setStyle(workbook, sheet, list, "社会治理资源准备情况");
return workbook;
}
public void setStyle(HSSFWorkbook workbook, HSSFSheet sheet, List list, String sheetName) {
// 表头
HSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0); // 0列
// 设置值
cell1.setCellValue(sheetName);
// 合并表头
rowReportTitle.setHeight((short) 600); // 行高
//设置表头字体
Font headFont = workbook.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 20);// 字体大小
CellStyle headStyle = workbook.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 头部样式添加
cell1.setCellStyle(headStyle);
//设置主标题
Font mainFont = workbook.createFont();
mainFont.setFontName("宋体");
mainFont.setFontHeightInPoints((short) 16);// 字体大小
CellStyle mainStyle = workbook.createCellStyle();
mainStyle.setFont(mainFont);
mainStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
mainStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
//设置副标题
Font subFont = workbook.createFont();
subFont.setFontName("宋体");
subFont.setFontHeightInPoints((short) 10);// 字体大小
CellStyle subStyle = workbook.createCellStyle();
subStyle.setFont(subFont);
subStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
subStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
//表的样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
AtomicInteger rowIndex = new AtomicInteger(1);
AtomicInteger columnIndex = new AtomicInteger(0);
list.forEach(data -> {
AtomicInteger rowOffset = new AtomicInteger(0);
AtomicInteger columnOffset = new AtomicInteger(0);
HSSFRow mainRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.getAndIncrement()) : sheet.getRow(rowIndex.get() + rowOffset.getAndIncrement());
HSSFCell cell = mainRow.createCell(columnIndex.get());
cell.setCellValue(data.getMainTitle());
cell.setCellStyle(mainStyle);
mainRow.setHeight((short) 400);
List subTitle = data.getSubTitle();
subTitle.forEach(sub -> {
HSSFRow subRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
HSSFCell cell2 = subRow.createCell(columnIndex.get() + columnOffset.getAndIncrement());
cell2.setCellValue(sub);
cell2.setCellStyle(subStyle);
subRow.setHeight((short) 300);
});
rowOffset.getAndIncrement();
columnOffset.set(0);
List excelData = data.getData();
excelData.forEach(excel -> {
// excel.forEach(o -> {
HSSFRow dataRow = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
HSSFCell cell2 = dataRow.createCell(columnIndex.get() + columnOffset.getAndIncrement());
cell2.setCellValue(excel.getName());
cell2.setCellStyle(style);
// dataRow.setRowNum();
HSSFRow dataRow1 = sheet.getRow(rowIndex.get() + rowOffset.get()) == null ? sheet.createRow(rowIndex.get() + rowOffset.get()) : sheet.getRow(rowIndex.get() + rowOffset.get());
dataRow1.setRowStyle(style);
HSSFCell cell3 = dataRow1.createCell(columnIndex.get() + columnOffset.getAndIncrement());
if (excel.getCount() == null) {
excel.setCount(0);
}
cell3.setCellValue(excel.getCount());
cell3.setCellStyle(style);
// });
rowOffset.getAndIncrement();
columnOffset.set(0);
});
columnIndex.set(columnIndex.get() + subTitle.size());
// columnIndex.set(subTitle.size());
});
}
controller
@RequestMapping("/outputEventReporting")
public void xlsOutputConditionsEmergencyData(HttpServletResponse response) throws Exception {
response.setContentType("application/octet-stream;charset=UTF-8");
response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.addHeader("Content-Disposition", "attachment;fileName=" + new String(("测试情况导出.xls").getBytes(), "ISO8859-1" ) );
performanceStatisticsService.outputEventReporting().write(response.getOutputStream());
}
