Poi 导出带复杂表头多个sheet的内容
目录
知道了如何导出单个sheet复杂表头的内容? 如何导出多个的呢? 结和导出简单表头多个sheet的内容,整理下内容。
目标:
直接上代码:
代码:
Contrller
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
@Resource
private ExcelService excelService;
@GetMapping("/exportMulSheetMulHeader")
@ApiOperation(value="导出多个sheet多表头数据接口")
public void exportMulSheetMulHeader(HttpServletResponse response) {
excelService.exportMulSheetMulHeader(response);
}
}
Service:
@Service
public class ExcelService {
public void exportMulSheetMulHeader(HttpServletResponse response) {
String tableName = "exportMulSheetMulHeader " + LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue() + "-"
+ LocalDate.now().getDayOfMonth();
// 获取需要导出的数据
List<Map<String, Object>> dataList = getResultData();
List<List<List<String>>> titleList = new ArrayList<>();
List<List<List<String>>> contentList = new ArrayList<>();
List<List<CellRangeAddress>> cellRangeAddressList = new ArrayList<>();
// 需要展示的列
List<String> fieldList1 = Lists.newArrayList("orderTime","total","except","overTime","successRate");
List<String> fieldList2 = Lists.newArrayList("orderTime","total","except","timelyRate","successRate");
// 匹配数据
List<List<String>> results1 = matchFieldData(dataList, fieldList1);
List<List<String>> results2 = matchFieldData(dataList, fieldList2);
contentList.add(results1);
contentList.add(results2);
// 设置表题
titleList.add(getTitleOne());
titleList.add(getTitleTwo());
// 设置表头合并
cellRangeAddressList.add(addMergeThree());
cellRangeAddressList.add(addMergeFour());
try {
ExcelExportUtil.exportExcelMulHeader(tableName, titleList, contentList,cellRangeAddressList,response);
} catch (Exception e) {
e.printStackTrace();
}
}
private List<List<String>> getTitleOne(){
List<List<String>> titleList = new ArrayList<>();
List<String> titleOne = Lists.newArrayList("统计","","","","");
List<String> titleTwo = Lists.newArrayList("日期","综合","","","");
List<String> titleThree = Lists.newArrayList("","订单总量","异常量","超时量","及时率");
titleList.add(titleOne);
titleList.add(titleTwo);
titleList.add(titleThree);
return titleList;
}
private List<List<String>> getTitleTwo(){
List<List<String>> titleList = new ArrayList<>();
List<String> titleOne = Lists.newArrayList("统计","","","","");
List<String> titleTwo = Lists.newArrayList("日期","综合","","","");
List<String> titleThree = Lists.newArrayList("","数值","","比率","");
List<String> titleFour = Lists.newArrayList("","订单总量","异常量","及时率","成功率");
titleList.add(titleOne);
titleList.add(titleTwo);
titleList.add(titleThree);
titleList.add(titleFour);
return titleList;
}
private List<CellRangeAddress> addMergeThree() {
List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
cellRangeAddressList.add(new CellRangeAddress(0, 0, 0, 4));
cellRangeAddressList.add(new CellRangeAddress(1, 1, 1, 4));
cellRangeAddressList.add(new CellRangeAddress(1, 2, 0, 0));
return cellRangeAddressList;
}
private List<CellRangeAddress> addMergeFour() {
List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
cellRangeAddressList.add(new CellRangeAddress(0, 0, 0, 4));
cellRangeAddressList.add(new CellRangeAddress(1, 1, 1, 4));
cellRangeAddressList.add(new CellRangeAddress(2, 2, 1, 2));
cellRangeAddressList.add(new CellRangeAddress(2, 2, 3, 4));
cellRangeAddressList.add(new CellRangeAddress(1, 3, 0, 0));
return cellRangeAddressList;
}
private List<Map<String, Object>> getResultData(){
String data = "[{\"orderTime\":\"2021-03-03 10:10:10\",\"total\":55,\"except\":12,\"overTime\":8,\"timelyRate\":\"88%\",\"successRate\":\"77\"},{\"orderTime\":\"2021-03-03 10:15:10\",\"total\":155,\"except\":44,\"overTime\":20,\"timelyRate\":\"78%\",\"successRate\":\"65\"},{\"orderTime\":\"2021-03-03 10:20:10\",\"total\":85,\"except\":6,\"overTime\":5,\"timelyRate\":\"98%\",\"successRate\":\"97\"}]";
return GsonUtils.changeJsonToList(data);
}
private List<List<String>> matchFieldData(List<Map<String, Object>> dataList, List<String> fieldList) {
return ListUtils
.emptyIfNull(dataList).stream().filter(Objects::nonNull).map(e -> ListUtils.emptyIfNull(fieldList)
.stream().map(f -> MapUtils.getString(e, f)).collect(Collectors.toList()))
.collect(Collectors.toList());
}
}
导出工具类:ExcelExportUtil
@Component
public class ExcelExportUtil {
private static Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtil.class);
public static void exportSingleSheetMulHeader(String fileName, List<List<String>> titles, List<List<String>> result,List<CellRangeAddress> cellRangeAddressList,
HttpServletResponse response) {
List<List<List<String>>> titleList = Lists.newArrayList();
List<List<List<String>>> contentList = Lists.newArrayList();
List<List<CellRangeAddress>> rangeList = Lists.newArrayList();
titleList.add(titles);
contentList.add(result);
rangeList.add(cellRangeAddressList);
exportExcelMulHeader(fileName, titleList, contentList,rangeList, response);
}
public static void exportExcelMulHeader(String fileName, List<List<List<String>>> titleList, List<List<List<String>>> result,List<List<CellRangeAddress>> cellRangeAddressList,
HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
new PoiExcelExporter().exportMulHeader(response, fileName, titleList, result,cellRangeAddressList);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
}
public static void exportExcel(String fileName, String[] titles, List<List<String>> result,
HttpServletResponse response) {
List<String[]> titleList = Lists.newArrayList();
List<List<List<String>>> contentList = Lists.newArrayList();
titleList.add(titles);
contentList.add(result);
exportExcel(fileName, titleList, contentList, response);
}
public static void exportExcel(String fileName, List<String[]> titleList, List<List<List<String>>> result,
HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
new PoiExcelExporter().export(response, fileName, changeStrArrToList(titleList), result);
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
}
private static List<List<String>> changeStrArrToList(List<String[]> titleList){
return ListUtils.emptyIfNull(titleList).stream().map(Arrays::asList).collect(Collectors.toList());
}
}
这边处理单个的时候,就没办法用同一个方法名了,内容都是List<T>类型,被视为同一类型,单个和多个方法命名就得不一样了。不能像 exportExcel一样
导出类:PoiExcelExporter
public class PoiExcelExporter {
PoiExcelExporter() {}
/*多个sheet页 单表头*/
public void export(HttpServletResponse response, String fileName, List<List<String>> titleList,
List<List<List<String>>> contentList) {
HSSFSheet sheet;
try (HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {
setResponse(response, fileName);
int sheetNum = titleList.size();
for (int i = 0; i < sheetNum; i++) {
sheet = workbook.createSheet("Sheet" + (i + 1));
int rowNum = 0;
createHeader(workbook, sheet, titleList.get(i), rowNum);
rowNum++;
createContent(sheet, contentList.get(i), rowNum);
}
workbook.write(output);
output.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
/*多个sheet页 复杂表头*/
public void exportMulHeader(HttpServletResponse response, String fileName, List<List<List<String>>> titleList,
List<List<List<String>>> contentList,List<List<CellRangeAddress>> cellRangeAddressList) {
HSSFSheet sheet;
try (HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {
setResponse(response, fileName);
int sheetNum = titleList.size();
for (int i = 0; i < sheetNum; i++) {
sheet = workbook.createSheet("Sheet" + (i + 1));
int rowNum = 0;
// 添加完表头后,返回对应的行数
rowNum = createMulHeader(workbook, sheet, titleList.get(i), rowNum);
mergeCellRange(sheet,cellRangeAddressList.get(i));
createContent(sheet, contentList.get(i), rowNum);
}
workbook.write(output);
output.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
private int createMulHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<List<String>> titles, int rowNum) {
setHeaderStyle(workbook);
for (List<String> title : ListUtils.emptyIfNull(titles)) {
setSheetContent(sheet, title, rowNum);
rowNum++;
System.out.println("title rowNum " + rowNum);
}
return rowNum;
}
private void mergeCellRange(HSSFSheet sheet, List<CellRangeAddress> cellRangeAddressList){
//处理 cellRangeAddressList 合并内容
if(CollectionUtils.isNotEmpty(cellRangeAddressList)){
cellRangeAddressList.forEach(sheet::addMergedRegion);
}
}
private void createHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<String> titles, int rowNum) {
setHeaderStyle(workbook);
setSheetContent(sheet, titles, rowNum);
}
private static void setSheetContent(HSSFSheet sheet, List<String> contentList, int rownum) {
HSSFRow row = sheet.createRow(rownum);
AtomicInteger i = new AtomicInteger();
for (String title : ListUtils.emptyIfNull(contentList)) {
HSSFCell cell = row.createCell(i.getAndIncrement());
cell.setCellValue(title);
}
}
private void createContent(HSSFSheet sheet, List<List<String>> content, int rownum) throws Exception {
for (List<String> lineData : content) {
setSheetContent(sheet,lineData,rownum);
rownum++;
}
}
private void setResponse(HttpServletResponse response, String fileName) {
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", getResponseHeader(fileName));
}
private String getResponseHeader(String fileName) {
return "attachment; filename=" + gbToUtf8(fileName) + ".xls";
}
private static String gbToUtf8(String src) {
byte[] b = src.getBytes();
char[] c = new char[b.length];
for (int x = 0; x < b.length; x++) {
c[x] = (char) (b[x] & 0x00FF);
}
return new String(c);
}
private static void setHeaderStyle(HSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();//设置样式
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 13);//设置字体大小
font.setBold(true);//字体加粗
style.setFont(font);//设置的字体
style.setBorderTop(BorderStyle.DASHED);//上边框
style.setBorderBottom(BorderStyle.DASHED); //下边框
style.setBorderBottom(BorderStyle.DASHED);//左边框
style.setRightBorderColor(IndexedColors.BLACK.getIndex());//右边框颜色
style.setTopBorderColor(IndexedColors.BLACK.getIndex());//上边框颜色
style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //下边框颜色
style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); //左边框颜色
style.setBorderBottom(BorderStyle.DASHED);//右边框
style.setAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐
style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐
}
}
结合了单表头,多表头,单个sheet,多个sheet的内容,内容比较完整了。
测试用postman:
结果:
结果和目标保持一致,任务完成。
总结:
导出带复杂表头,多个sheet的内容时候,除了如何拼接复杂的表头。先导出表题的时候,要注意行号,处理完表头后返回对应的行数,当然也可以直接获取表头的行数,直接赋值。简单表头是因为表头就一行,而复杂表头有多行。