java通过poi生成excel表格(自适应列宽、合并单元格后的边框添加)

具体java通过POI读写Excel的基本使用方法可参考：
POI读写Excel的基本使用

1.项目导入依赖：

<!--xls-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<!--xlsx-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

2.自己根据需求构建的工具类

/**
 * 设置单元格样式的工具类可根据自身需求自行构建
 */
public class PoiUtil {
    /**
     *
     * @param workbook
     * @param fontSize
     * @return 表头单元格样式
     */
    private static CellStyle createHeadCellStyle(Workbook workbook, short fontSize) {
        //创建cellStyle对象
        CellStyle style = workbook.createCellStyle();
        //设置边框
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        //设置文字居中
        style.setAlignment(CellStyle.ALIGN_CENTER);
        //设置字体大小、加粗
        Font font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //设置文字样式生效
        style.setFont(font);
        return style;
    }

    /**
     *
     * @param workbook
     * @param fontSize
     * @return 表信息单元格样式
     */
    private static CellStyle createMesCellStyle(Workbook workbook, short fontSize) {
        //创建cellStyle对象
        CellStyle style = workbook.createCellStyle();
        //设置边框
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        //设置文字居中
        style.setAlignment(CellStyle.ALIGN_CENTER);
        //设置字体大小以及字体水平&垂直居中
        Font font = workbook.createFont();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        font.setFontHeightInPoints(fontSize);
        //设置自动换行
        style.setWrapText(true);
        //设置文字样式生效
        style.setFont(font);
        return style;
    }

	/**
     * 设置合并单元格后的边框
     * @param border
     * @param cellRangeAddress
     * @param sheet
     * @param workbook
     */
    private static void setCellRangeAddress(int border, CellRangeAddress cellRangeAddress, Sheet sheet,Workbook workbook){
        //设置合并单元格的边框
        RegionUtil.setBorderBottom(border, cellRangeAddress, sheet,workbook);
        RegionUtil.setBorderTop(border, cellRangeAddress, sheet,workbook);
        RegionUtil.setBorderLeft(border, cellRangeAddress, sheet,workbook);
        RegionUtil.setBorderRight(border, cellRangeAddress, sheet,workbook);
    }


    /**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size
     */
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

}

3.生成表结构的代码

public String generatePlanExcel(@RequestParam(value = "planId") int planId) throws Exception{
        // 1.创建新的Excel工作簿(workbook)
        // 1.1 07版本的Excel需要XSSFWorkbook对象
        Workbook workbook = new XSSFWorkbook();
        // 2.使用workbook创建sheet
        // 2.1在Excel工作簿中建一工作表(sheet)，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 2.2如要新建一名为"预案详细信息"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("预案详细信息");
        //合并预案名称单元格,起始行,结束行,起始列,结束列
        CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);
        sheet.addMergedRegion(callRangeAddress);
        //合并资源明细单元格,起始行,结束行,起始列,结束列
        CellRangeAddress cellResourceAddress = new CellRangeAddress(4,4,0,7);
        sheet.addMergedRegion(cellResourceAddress);


        //查询相关预案信息
        PlanRecordAndResources planRecordAndResources = taskAndPlanService.checkPlanById(planId);

        //封装预案的值信息
        List<String> planDataCellList = new ArrayList<String>();
        planDataCellList.add(planRecordAndResources.getName());
        planDataCellList.add(planRecordAndResources.getTargetName());
        planDataCellList.add(planRecordAndResources.getTargetCoordinates());
        planDataCellList.add(planRecordAndResources.getSupportStartTime());
        planDataCellList.add(planRecordAndResources.getSupportAutomatedTime());
        planDataCellList.add(planRecordAndResources.getSupportResourceDetail());
        planDataCellList.add(planRecordAndResources.getAddDate());
        planDataCellList.add(planRecordAndResources.getModifyDate());

        //封装资源的值信息
        List<Map<Integer,String>> resourceDataCellList = new ArrayList<Map<Integer,String>>();
        List<PlanResources> resourcesList = planRecordAndResources.getResourcesList();
        for (PlanResources planResources : resourcesList) {
            Map<Integer,String> resourceDataMap = new HashMap<Integer,String>();
            resourceDataMap.put(0,planResources.getHospitalName());
            resourceDataMap.put(1,planResources.getDepartmentName());
            resourceDataMap.put(2,planResources.getResourceName());
            resourceDataMap.put(3,planResources.getResourceNum()+"");
            resourceDataMap.put(4,planResources.getResourceNote());
            resourceDataMap.put(5,planResources.getAddDate());
            resourceDataMap.put(6,planResources.getModifyDate());
            resourceDataCellList.add(resourceDataMap);
        }


        String[] planRowName = {"序号","保障目标名称","保障目标坐标","预案开始时间","保障自动执行时间","预案保障资源明细","预案添加时间","预案修改时间"};
        String[] resourceRowName = {"序号","医院名称","科室名称","资源类别名称","资源所需数量","备注","资源信息添加时间","资源信息修改时间"};

        //预案名称的样式
        CellStyle planHeadStyle = PoiUtil.createHeadCellStyle(workbook,(short)16);
        //预案名称的row
        Row planNameRow = sheet.createRow(0);
        Cell planNameCell = planNameRow.createCell(0);
        //加载单元格样式
        planNameCell.setCellStyle(planHeadStyle);
        //获取并设置预案信息名
        planNameCell.setCellValue(planDataCellList.get(0));
        //设置合并后的单元格边框
        PoiUtil.setCellRangeAddress(1,callRangeAddress,sheet,workbook);


        //保障地点信息含义的row
        Row planRow = sheet.createRow(1);
        CellStyle planMeanStyle = PoiUtil.createHeadCellStyle(workbook,(short)12);

        //保障地点信息的row
        Row planDataRow = sheet.createRow(2);
        CellStyle planDataStyle = PoiUtil.createMesCellStyle(workbook,(short)11);

        for (int i = 0; i < 8; i++) {
            //向保障地点信息含义的cell中设置内容
            Cell planMeanCell = planRow.createCell(i);
            planMeanCell.setCellValue(planRowName[i]);
            planMeanCell.setCellStyle(planMeanStyle);
            //向保障地点信息的cell中设置内容
            Cell planDataCell = planDataRow.createCell(i);
            if(i==0){
                planDataCell.setCellValue(1);
                planDataCell.setCellStyle(planDataStyle);
                continue;
            }
            planDataCell.setCellValue(planDataCellList.get(i));
            planDataCell.setCellStyle(planDataStyle);
        }



        //资源明细的cellStyle
        CellStyle resourceHeadStyle = PoiUtil.createHeadCellStyle(workbook,(short)16);
        Row resourceNameRow = sheet.createRow(4);
        Cell resourceNameCell = resourceNameRow.createCell(0);
        //加载单元格样式
        resourceNameCell.setCellStyle(resourceHeadStyle);
        //获取并设置预案信息名
        resourceNameCell.setCellValue("资源调配明细");
        //设置合并后的单元格边框
        PoiUtil.setCellRangeAddress(1,cellResourceAddress,sheet,workbook);



        //资源信息含义的row
        Row resourceRow = sheet.createRow(5);
        CellStyle resourceMeanStyle = PoiUtil.createHeadCellStyle(workbook,(short)12);
        //资源信息的row
        CellStyle resourceDataStyle = PoiUtil.createMesCellStyle(workbook,(short)11);
        //资源row集合
        List<Row> resourceDataRowList = new ArrayList<Row>();
        for (int i = 0; i < resourceDataCellList.size(); i++) {
            //资源信息的row
            Row resourceDataRow = sheet.createRow(6+i);
            resourceDataRowList.add(resourceDataRow);
        }


        int num = 0;
        for (int i = 0; i < 8; i++) {
            //向资源信息含义的cell中设置内容
            Cell cell = resourceRow.createCell(i);
            cell.setCellValue(resourceRowName[i]);
            cell.setCellStyle(resourceMeanStyle);
            for (int j = 0; j < resourceDataCellList.size(); j++) {
                Cell cell1 = resourceDataRowList.get(j).createCell(i);
                //向资源信息的cell中设置内容
                if(i==0){
                    cell1.setCellValue(++num);
                    cell1.setCellStyle(resourceDataStyle);
                    continue;
                }
                cell1.setCellValue(resourceDataCellList.get(j).get(i-1));
                cell1.setCellStyle(resourceDataStyle);
            }
        }
        PoiUtil.setSizeColumn(sheet,8);

        // 6.新建一输出文件流（注意：要先创建文件夹）
        FileOutputStream out = new FileOutputStream("F:/PoiTest/国家体育馆预案.xlsx");
        // 7.把相应的Excel 工作簿存盘
        workbook.write(out);
        // 8.操作结束，关闭文件
        out.close();

        return ResponseTemplate.response(ResponseModel.SUCCESS);

    }

4.结果

生成表格的代码逻辑是我自己的，工具类里面有对应解决自适应列宽（中文支持）以及合并单元格后边框的添加还有样式如何设置如何使其生效的方法，自行参考。