Java 导出Excel 自定义模板

JAVA POI导出EXCEL设置自定义样式（线框加粗，合并指定行，合计求和，冻结行）：https://blog.csdn.net/Mou_O/article/details/123133202

@Override
    public void exportCreatedN1ShippingPlan(List<N1CreateSHippingPlanInputDto> list) {
        // 查询要导出的数据
        List<N1CreateShippingPlanOutputDto> planList = repository.createN1ShippingPlan(list);
        if (!CollectionUtils.isEmpty(planList)) {
            HSSFWorkbook workbook = new HSSFWorkbook();  // 创建一个excel
            // excel生成过程: excel-->sheet-->row-->cell
            HSSFSheet sheet = workbook.createSheet("sheet1");
            // 列宽  (本次导出共11列)
            for (int i = 0; i < 11; i++) {
                sheet.setColumnWidth(i, 20 * 255);
            }

            // 字体样式
            Font fontStyle = workbook.createFont();
            fontStyle.setBold(true); // 加粗
            fontStyle.setFontName("黑体"); // 字体
            fontStyle.setFontHeightInPoints((short) 11); // 大小

            // 单元格样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            // 边框，居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            // 创建第一行并填充值
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cellB1 = row1.createCell(0);
            cellB1.setCellValue("N-1（W-3）船运计划明细");
            cellB1.setCellStyle(cellStyle);
            // 合并首行单元格  // 起始行, 终止行, 起始列, 终止列
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 10);
            sheet.addMergedRegion(cra);
            sheet.setDefaultRowHeight((short) 300);
            // 设置第一行高度
            sheet.getRow(0).setHeightInPoints(30);
            // 合并单元格后设置该行样式
            sheet.getRow(0).getCell(0).setCellStyle(cellStyle);
            // 创建第二行添加表头内容
            HSSFRow row2 = sheet.createRow(1);
            HSSFCell cellB2_1 = row2.createCell(0);
            cellB2_1.setCellValue("车型");
            cellB2_1.setCellStyle(cellStyle);

            HSSFCell cellB2_2 = row2.createCell(1);
            cellB2_2.setCellValue("装船PC NO.");
            cellB2_2.setCellStyle(cellStyle);

            HSSFCell cellB2_3 = row2.createCell(2);
            cellB2_3.setCellValue("装船D/I NO.");
            cellB2_3.setCellStyle(cellStyle);

            HSSFCell cellB2_4 = row2.createCell(3);
            cellB2_4.setCellValue("装船MTOC");
            cellB2_4.setCellStyle(cellStyle);
            HSSFCell cellB2_5 = row2.createCell(4);
            cellB2_5.setCellValue("装船MTOC台数");
            cellB2_5.setCellStyle(cellStyle);
            HSSFCell cellB2_6 = row2.createCell(5);
            cellB2_6.setCellValue("生产月");
            cellB2_6.setCellStyle(cellStyle);
            HSSFCell cellB2_7 = row2.createCell(6);
            cellB2_7.setCellValue("装货港");
            cellB2_7.setCellStyle(cellStyle);
            HSSFCell cellB2_8 = row2.createCell(7);
            cellB2_8.setCellValue("海船公司");
            cellB2_8.setCellStyle(cellStyle);
            HSSFCell cellB2_9 = row2.createCell(8);
            cellB2_9.setCellValue("海船名");
            cellB2_9.setCellStyle(cellStyle);
            HSSFCell cellB2_10 = row2.createCell(9);
            cellB2_10.setCellValue("海船ETD");
            cellB2_10.setCellStyle(cellStyle);
            HSSFCell cellB2_11 = row2.createCell(10);
            cellB2_11.setCellValue("卸货港");
            cellB2_11.setCellStyle(cellStyle);

            // 将正文数据填充到excel具体行
            for (int i = 0; i < planList.size(); i++) {
                N1CreateShippingPlanOutputDto dto = planList.get(i);
                // 创建新的一行
                HSSFRow row = sheet.createRow(i + 2);
                // 创建该行单元格  // 第1列
                HSSFCell cell0 = row.createCell(0);
                cell0.setCellValue(dto.getCarTypeCode());
                cell0.setCellStyle(cellStyle);
                // 第2列
                HSSFCell cell1 = row.createCell(1);
                cell1.setCellValue(dto.getPcNo());
                cell1.setCellStyle(cellStyle);
                // 第3列
                HSSFCell cellB2 = row.createCell(2);
                cellB2.setCellValue(dto.getDiNo());
                cellB2.setCellStyle(cellStyle);
                HSSFCell cell3 = row.createCell(3);
                cell3.setCellValue(dto.getMtoc());
                cell3.setCellStyle(cellStyle);
                HSSFCell cell4 = row.createCell(4);
                cell4.setCellValue(dto.getMtocNumber());
                cell4.setCellStyle(cellStyle);
                HSSFCell cell5 = row.createCell(5);
                cell5.setCellValue(dto.getCreateMonth());
                cell5.setCellStyle(cellStyle);
                HSSFCell cell6 = row.createCell(6);
                cell6.setCellValue(dto.getLoadingPort());
                cell6.setCellStyle(cellStyle);
                HSSFCell cellB7 = row.createCell(7);
                cellB7.setCellValue(dto.getVesselCompanyName());
                cellB7.setCellStyle(cellStyle);
                HSSFCell cell8 = row.createCell(8);
                cell8.setCellValue(dto.getVesselName());
                cell8.setCellStyle(cellStyle);
                HSSFCell cell9 = row.createCell(9);
                cell9.setCellValue(dto.getEtd());
                cell9.setCellStyle(cellStyle);
                HSSFCell cell10 = row.createCell(10);
                cell10.setCellValue(dto.getDischargePort());
                cell10.setCellStyle(cellStyle);
            }
            // 输出到本地
            String excelName = "/N-1（W-3）船运计划明细.xls";
            FileOutputStream out = null;
            try {
                out = new FileOutputStream(excelName);
                workbook.write(out);
                out.flush();
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (out != null)
                    try {
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                out = null;
            }
        }
    }

导出结果如下：