Java导出Excel（自定义格式）

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文件中的sheet
        HSSFSheet 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();

2. 添加表格样式

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);

        //数据行居中底色样式2
        style = 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官方文档
https://www.cnblogs.com/fqfanqi/p/6172223.html