hutool poi 分页导出，格式设置

 1     @RequestMapping(value = "/exportXls")
 2     public void exportXls(HttpServletRequest request, DnbUser dnbUser, HttpServletResponse response) throws IOException {
 3 
 4         ExcelWriter writer = ExcelUtil.getWriter();
 5 
 6         List<DnbUser> dnbUsers = dnbUserService.list();
 7 
 8         //每个sheet页的数据量
 9         int num = 4;
10         //总数据条数
11         int size = dnbUsers.size();
12         //分sheet页
13         int sheetNum = size / num + 1;
14         // 需要设置一下
15         writer.renameSheet(0, "Sheet0");
16         int startIndex = 0;
17         int endIndex = 0;
18         int styleEndIndex = num;
19         for (int i = 0; i < sheetNum; i++) {
20             //指定sheet页
21             writer.setSheet(i);
22             //自定义Bean中key的别名,为表头设置列名
23             writer.addHeaderAlias("no", "编号");
24             if (sheetNum == 1) {
25                 startIndex = 0;
26                 endIndex = size;
27                 styleEndIndex = size;
28             } else if (i == sheetNum - 1) {
29                 startIndex = i * num;
30                 endIndex = size;
31                 styleEndIndex = size-i * num;
32             } else {
33                 startIndex = i * num;
34                 endIndex = (i + 1) * num;
35                 styleEndIndex = 4;
36             }
37             writer.write(dnbUsers.subList(startIndex, endIndex), true);
38             //每个新的sheet页都是行数都是从0开始的
39             generateStyle(writer, i, styleEndIndex,dnbUsers,startIndex);
40         }
41         response.setContentType("application/vnd.ms-excel;charset=utf-8");
42         response.setHeader("Content-Disposition", "attachment;filename=test.xls");
43         ServletOutputStream out = response.getOutputStream();
44 
45         writer.flush(out, true);
46         writer.close();
47         IoUtil.close(out);
48     }
49 
50     private void generateStyle(ExcelWriter writer, int sheetNo, int styleEndIndex,List<DnbUser> dnbUsers ,int dataStartIndex) {
51 //        Workbook wb = writer.getWorkbook();
52 //        Font font = writer.createFont();
53 //        font.setBold(true);
54 //        font.setColor(Font.COLOR_RED);
55 //        font.setItalic(true);
56 //
57 //        StyleSet style = writer.getStyleSet();
58 //        CellStyle cellStyle = style.getCellStyle();
59 //        cellStyle.setLeftBorderColor((short) 999);
60 //        cellStyle.setFont(font);
61 
62         Workbook wb = writer.getWorkbook();
63         CellStyle cellStyle = wb.createCellStyle();
64         cellStyle.setAlignment(HorizontalAlignment.CENTER);
65         cellStyle.setBorderBottom((short)1);
66         cellStyle.setBorderLeft((short)1);
67         cellStyle.setBorderRight((short)1);
68         cellStyle.setBorderTop((short)1);
69         cellStyle.setFillPattern((short)1);
70         cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
71 
72         Sheet sheet = wb.getSheetAt(sheetNo);
73         for(int i = 1; i <= styleEndIndex; ++i,dataStartIndex++) {
74             if(dnbUsers.get(dataStartIndex).getNo().equalsIgnoreCase("1")){
75                sheet.getRow(i).getCell(13).setCellStyle(cellStyle);
76             }
77         }
78     }