本文参考自https://www.jb51.net/article/161337.htm 仅仅作为记录
<!-- 导出excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
代码: @RequestMapping("/export") public ResponseEntity<byte[]> export(HttpServletResponse response) throws Exception { // 造几条数据 List<BcBlock> list = new ArrayList<>(); list.add(new BcBlock(1,"2","3")); list.add(new BcBlock(2,"3","4")); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=export.xls"); return buildResponseEntity(export((List<BcBlock>) list), "用户表.xls"); } private InputStream export(List<BcBlock> list) { ByteArrayOutputStream output = null; InputStream inputStream1 = null; SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中 SXSSFSheet sheet = wb.createSheet(); // 设置报表头样式 CellStyle header = headSytle(wb);// cell样式 CellStyle content = contentStyle(wb);// 报表体样式 // 每一列字段名 String[] strs = new String[] { "姓名", "性别", "年龄"}; // 字段名所在表格的宽度 int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 }; // 设置表头样式 initTitleEX(sheet, header, strs, ints); if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { BcBlock user = list.get(i); SXSSFRow row = sheet.createRow(i + 1); int j = 0; SXSSFCell cell = row.createCell(j++); cell.setCellValue(user.getBlockId()); // 姓名 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getBlockHash()); // 性别 cell.setCellStyle(content); cell = row.createCell(j++); cell.setCellValue(user.getPreviousBlockHash()); // 年龄 cell.setCellStyle(content); } } try { output = new ByteArrayOutputStream(); wb.write(output); inputStream1 = new ByteArrayInputStream(output.toByteArray()); output.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (output != null) { output.close(); if (inputStream1 != null) inputStream1.close(); } } catch (IOException e) { e.printStackTrace(); } } return inputStream1; } public static void initTitleEX(SXSSFSheet sheet, CellStyle header,String title[],int titleLength[]) { SXSSFRow row0 = sheet.createRow(0); row0.setHeight((short) 800); for(int j = 0;j<title.length; j++) { SXSSFCell cell = row0.createCell(j); //设置每一列的字段名 cell.setCellValue(title[j]); cell.setCellStyle(header); sheet.setColumnWidth(j, titleLength[j]); } } public static CellStyle headSytle(SXSSFWorkbook workbook){ // 设置style1的样式,此样式运用在第二行 CellStyle style1 = workbook.createCellStyle();// cell样式 // 设置单元格背景色,设置单元格背景色以下两句必须同时设置 style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式 style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色 // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); Font font1 = workbook.createFont();// 创建一个字体对象 font1.setBoldweight((short) 10);// 设置字体的宽度 font1.setFontHeightInPoints((short) 10);// 设置字体的高度 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 style1.setFont(font1);// 设置style1的字体 style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } public static CellStyle contentStyle(SXSSFWorkbook wb){ // 设置style1的样式,此样式运用在第二行 CellStyle style1 = wb.createCellStyle();// cell样式 // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } public static HSSFCellStyle titleSytle(HSSFWorkbook workbook, short color, short fontSize){ // 设置style1的样式,此样式运用在第二行 HSSFCellStyle style1 = workbook.createCellStyle();// cell样式 // 设置单元格背景色,设置单元格背景色以下两句必须同时设置 //style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式 //short fcolor = color; if(color != HSSFColor.WHITE.index){ style1.setFillForegroundColor(color);// 设置填充色 } // 设置单元格上、下、左、右的边框线 style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = workbook.createFont();// 创建一个字体对象 font1.setBoldweight(fontSize);// 设置字体的宽度 font1.setFontHeightInPoints(fontSize);// 设置字体的高度 font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 style1.setFont(font1);// 设置style1的字体 style1.setWrapText(true);// 设置自动换行 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向) style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向) return style1; } public ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws Exception { HttpHeaders header = new HttpHeaders(); String fileSuffix = name.substring(name.lastIndexOf('.') + 1); fileSuffix = fileSuffix.toLowerCase(); Map<String, String> arguments = new HashMap<String, String>(); arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); arguments.put("xls", "application/vnd.ms-excel"); String contentType = arguments.get(fileSuffix); header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download")); if (is != null && is.available() != 0) { header.add("Content-Length", String.valueOf(is.available())); header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); byte[] bs = IOUtils.toByteArray(is); return new ResponseEntity<>(bs, header, HttpStatus.OK); } else { String string = "数据为空"; header.add("Content-Length", "0"); header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8")); return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK); } }