Java导出Excel（自定义格式，非纯导出表格）
最近业务有个需求需要用到导出Excel表，按模板格式导出报关单（比较复杂，需要一格一格画），这里记录一下Excel导出怎么用
如果只是导出纯表列数据，可以不用我这种方法
这里需要引入Apache POI依赖库，
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>
//准备报表用参数CVVoyage cvVoyage = this.cvVoyageDao.getByVoyageId(voyageId);List<Order> bookingList = this.csBookingDao.getByVoyageId(cvVoyage.getId());// Excel表开始String sheetname = "申报单";//创建一个HSSFWorkbook，对应一个Excel文件HSSFWorkbook wb = new HSSFWorkbook();//添加一个sheet,对应Excel文件中的sheetHSSFSheet sheet = wb.createSheet(sheetname);//设置宽度int[] width = {20, 10, 30, 30, 30, 20, 30};for (int columnIndex = 0; columnIndex < 7; columnIndex++) {sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);}//设置样式集合Map<String, HSSFCellStyle> styles = addStyle(wb);int rowIndex;int colIndex;//添加标题行HSSFRow row0 = sheet.createRow(0);row0.setHeight((short) (40 * 20));HSSFCell cell0 = row0.createCell(0);cell0.setCellStyle(styles.get("title"));String cellValues0 = "中华人民共和国海关运输工具起卸/添加物料申报单";cell0.setCellValue(cellValues0);CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 6);sheet.addMergedRegion(cra);HSSFRow row1 = sheet.createRow(1);row0.setHeight((short) (30 * 20));HSSFCell cell1 = row1.createCell(0);cell1.setCellStyle(styles.get("title"));String cellValues1 = "DECALRATION OF OFFLAND/SUPPLY PROVISIONS/STORES TO CUSTOMS\n";cell1.setCellValue(cellValues1);CellRangeAddress cra1 = new CellRangeAddress(1, 1, 0, 6);sheet.addMergedRegion(cra1);HSSFRow row2 = sheet.createRow(2);for (colIndex = 0; colIndex < 7; colIndex++) {row2.createCell(colIndex);row2.getCell(colIndex).setCellStyle(styles.get("header_center"));}row2.getCell(0).setCellValue("录入编号(SERIES NUMBER)");sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));row2.getCell(4).setCellValue("□起卸(OFFLAND)");sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));row2.getCell(6).setCellValue("□ 添加(SUPPLY)");// 后面的数据可以根据自己的需求写....................//生成文件String fileName = "申报单.xlsx";response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));OutputStream outputStream = response.getOutputStream();wb.write(outputStream);outputStream.flush();outputStream.close();
添加表格样式
private Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {@SuppressWarnings({"unchecked", "rawtypes"})Map<String, HSSFCellStyle> styles = new HashMap();//设置字体HSSFFont headFont = wb.createFont();headFont.setFontName("微软雅黑");headFont.setFontHeightInPoints((short) 16);headFont.setBold(true);HSSFFont bodyFont = wb.createFont();bodyFont.setFontName("微软雅黑");bodyFont.setFontHeightInPoints((short) 10);//标题行样式HSSFCellStyle style = wb.createCellStyle();style.setFont(headFont);style.setWrapText(true);style.setFillForegroundColor((short) 27);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);styles.put("title", style);//数据头居中样式style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);style.setFillForegroundColor((short) 27);styles.put("header_center", style);//数据行居中样式style = wb.createCellStyle();style.setFont(bodyFont);styles.put("data_center", style);//数据行居中底色样式style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);style.setFillForegroundColor((short) 27);styles.put("data_center_color", style);//数据行居中底色样式2style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);style.setFillForegroundColor((short) 27);styles.put("data_center_color1", style);//数据行居左样式style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);style.setFillForegroundColor((short) 27);style.setAlignment(HSSFCellStyle.ALIGN_LEFT);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);styles.put("data_left", style);//数据行居右样式style = wb.createCellStyle();style.setFont(bodyFont);style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);styles.put("data_right", style);//无边框样式style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);styles.put("data_noborder", style);//无底边框样式style = wb.createCellStyle();style.setFont(bodyFont);style.setWrapText(true);styles.put("data_bottom", style);return styles;}
最终效果图
具体用法可以参考POI官方文档
/fqfanqi/p/6172223.html
