RegionUtil set border invalid issue resolved after Apache Poi 4.1.0 merged cells

background

Excel complex table header export using Apache Poi, involving table merge, RegionUtil call to set border effect invalid. As shown below.

To solve

RegionUtil was used after a series of data checks. Instead, style each cell, including the merged cells.

// The second line of table header: involves table header merge
row = sheet.createRow(1);
// Add this part of the code to set the border style for the merged and unfilled values
int preColNum = 16;
for (int j = 0; j < preColNum; j++) {
	HSSFCell cell = row.createCell(j);
	cell.setCellStyle(style);
}
// Set a worthwhile cell style
for (int i = 0; i < excelHeader1.length; i++) {
	HSSFCell cell = row.createCell(i + preColNum);
	cell.setCellValue(excelHeader1[i]);
	cell.setCellStyle(style);
	sheet.autoSizeColumn(i + preColNum, true);// Automatically adjust the width
}
// Dynamically merge cells
for (int i = 0; i < headnum1.length; i++) {
	sheet.autoSizeColumn(i, true);
	String[] temp = headnum1[i].split(",");
	Integer startrow = Integer.parseInt(temp[0]);
	Integer overrow = Integer.parseInt(temp[1]);
	Integer startcol = Integer.parseInt(temp[2]);
	Integer overcol = Integer.parseInt(temp[3]);
	CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
	sheet.addMergedRegion(cra);
	// Call RegionUtil setting invalid
	// this.setRegionBorder(BorderStyle.THIN, cra, sheet);
}
Copy the code

The complete code

After finishing, the complete code is as follows:

public class RoomReserveExportUtil {
    private void setRegionBorder(BorderStyle border, CellRangeAddress region, Sheet sheet) {
        RegionUtil.setBorderBottom(border, region, sheet);
        RegionUtil.setBorderLeft(border, region, sheet);
        RegionUtil.setBorderRight(border, region, sheet);
        RegionUtil.setBorderTop(border, region, sheet);
    }

    /** * Dynamically merge cells *@paramHeadNum specifies the table head number, 0,2,0,0 ===> start row, end row, start column, end column *@param sheet
     */
    private void mergeCell(String[] headNum, Sheet sheet) {
        // Dynamically merge cells
        for (int i = 0; i < headNum.length; i++) {
            sheet.autoSizeColumn(i, true);
            String[] temp = headNum[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);

            CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
            sheet.addMergedRegion(cra);
// this.setRegionBorder(BorderStyle.THIN, cra, sheet);}}/** * set the merge table, blank cell style *@param row
     * @param startNum
     * @param endNum
     * @param style
     */
    private void setEmptyCellStyle(HSSFRow row, int startNum, int endNum, HSSFCellStyle style) {
        for (intj = startNum; j < endNum; j++) { HSSFCell cell = row.createCell(j); cell.setCellStyle(style); }}public HSSFWorkbook exportGoods(List<RoomReserveExtend> list) {
        //int titleRow = 6; // The table header and subheader take up 6 lines
        //int tableBody = titleRow+1; // start the table
        // Declare an array of strings and initialize the elements (header names)
        // The header field in the first row is repeated several times when the fields span several columns when merging cells
        String[] excelHeader0 = {
                "Project Name"."Tender No."."Bid Inviting Unit"."Item Category"."Project Area".Time of bid Opening."Bid Evaluation Time"."Audit Status"."Bidding Method"."Form of Bidding Organization"."Tendering Agency".Name of Person present at Bid Opening (Agency)."Paying unit (winning bidder)"."Bid-winning Amount (YUAN)".Description of bid-winning Amount."Demand Note Time"."Amount paid ($)"."Amount paid ($)"."Amount paid ($)"."Amount paid ($)"."Amount paid ($)"."Change of ticket"."Change of ticket"

        };
        // "0,2,0,0" ===> "start row, end row, start column, end column"
        String[] headnum0 = {
                "0,2,0,0"."0,2,1,1"."0,2,2,2"."0,2,3,3"."0,2,4,4"."0,2,5,5"."0,2,6,6"."0,2,7,7"."0,2,8,8"."0,2,9,9"."0,2,10,10"."0,2,11,11"."0,2,12,12"."0,2,13,13"."0,2,14,14"."0,2,15,15"."0,0,16,20"."0,0,21,22"
        };
        // The second row header field, where the empty double quotes are used to complete the table border
        String[] excelHeader1 = {
                "Total 5 = (1 + 2)"."Venue Rental fee (1)"."Service charge"."Service charge"."Service charge"."Time"."Invoice Number"
        };
        // Merge cells
        String[] headnum1 = {
                "1,2,16,16"."1,2,17,17"."1,1,18,20"."1,2,21,21"."1,2,22,22"
        };

        // The third row header field
        String[] excelHeader2 = {
                "New Standard (2)"."Old Standard (2)"."Minus case 4=(3-2)"
        };

// String[] headnum2 = {
2,2,18,18 "/ /",
2,2,19,19 "/ /",
2,2,20,20 "/ /"
/ /};

        // Declare a workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // Create a table
        HSSFSheet sheet = wb.createSheet("Project export Results");

        // Generate a style style
        HSSFCellStyle style = wb.createCellStyle();
        // Set the style
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // Generate a font
        HSSFFont font = wb.createFont();
        // Set the font
        font.setFontName(Microsoft Yahei);
        // Set the font size
        font.setFontHeightInPoints((short) 12);
        // Reference the font in the style
        style.setFont(font);

// // Generates the title style style1
// HSSFCellStyle style1 = wb.createCellStyle();
// // Set styles
// style1.setAlignment(HorizontalAlignment.CENTER);
// style1.setVerticalAlignment(VerticalAlignment.CENTER);
//
// // Generates the title font 1
// HSSFFont font1 = wb.createFont();
// // Set font
// font1.setFontName(" Microsoft yahei ");
// // Set the font size
// font1.setFontHeightInPoints((short) 25);
// // bold
// font1.setBold(true);
// // references this font in styles
// style1.setFont(font1);

        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < excelHeader0.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader0[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i, true);// Adjust the column width automatically according to the field length
        }
        // Dynamically merge cells
        this.mergeCell(headnum0, sheet);

        // The second line header
        row = sheet.createRow(1);
        int preColNum = 16;
        // Set the merged cell, blank cell style
        this.setEmptyCellStyle(row, 0, preColNum, style);

        for (int i = 0; i < excelHeader1.length; i++) {
            HSSFCell cell = row.createCell(i + preColNum);
            cell.setCellValue(excelHeader1[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i + preColNum, true);// Automatically adjust the width
        }
        // Dynamically merge cells
        this.mergeCell(headnum1, sheet);

        // The third row header
        row = sheet.createRow(2);
        preColNum = 18;
        // Set the merged cell, blank cell style
        this.setEmptyCellStyle(row, 0, preColNum, style);

        for (int i = 0; i < excelHeader2.length; i++) {
            HSSFCell cell = row.createCell(i + preColNum);
            cell.setCellValue(excelHeader2[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i + preColNum, true);// Automatically adjust the width
        }
        // Dynamically merge cells
// this.mergeCell(headnum2, sheet);

        // Set the merged cell, blank cell style
        this.setEmptyCellStyle(row, 21.23, style);

        for (int i = 0; i < list.size(); i++) {
            RoomReserveExtend vo = list.get(i);
            row = sheet.createRow(i + 3);

            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(vo.getProjectName());

            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(vo.getTenderCode());

            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(vo.getTenderUnit());

            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(vo.getProjectType());

            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(vo.getArea());

            HSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(vo.getApplyOpenTime());

            HSSFCell cell6 = row.createCell(6);
            cell6.setCellValue(vo.getEvalTime());

            HSSFCell cell7 = row.createCell(7);
            cell7.setCellValue(vo.getManageStatus());

            HSSFCell cell8 = row.createCell(8);
            cell8.setCellValue(vo.getTenderType());

            HSSFCell cell9 = row.createCell(9);
            cell9.setCellValue(vo.getTenderOrgType());

            HSSFCell cell10 = row.createCell(10);
            cell10.setCellValue(vo.getAgencyName());

            HSSFCell cell11 = row.createCell(11);
            cell11.setCellValue(vo.getOpenUsersShow());

            HSSFCell cell12 = row.createCell(12);
            cell12.setCellValue(vo.getAgencyName());

            HSSFCell cell13 = row.createCell(13);
            cell13.setCellValue(String.valueOf(vo.getTradePrice()));

            HSSFCell cell14 = row.createCell(14);
            cell14.setCellValue(vo.getTradePriceDesc());

            HSSFCell cell15 = row.createCell(15);
            cell15.setCellValue("");

            HSSFCell cell16 = row.createCell(16);
            cell16.setCellValue(vo.getAgencyName());

            HSSFCell cell17 = row.createCell(17);
            cell17.setCellValue(vo.getAgencyName());

            HSSFCell cell18 = row.createCell(18);
            cell18.setCellValue(vo.getAgencyName());

            HSSFCell cell19 = row.createCell(19);
            cell19.setCellValue(vo.getAgencyName());

            HSSFCell cell20 = row.createCell(20);
            cell20.setCellValue(vo.getAgencyName());

            HSSFCell cell21 = row.createCell(21);
            cell21.setCellValue(vo.getAgencyName());

            HSSFCell cell22 = row.createCell(22);
            cell22.setCellValue(vo.getAgencyName());
        }
        returnwb; }}Copy the code

The final result

RegionUtil set border invalid after Apache Poi 4.1.0 merging cells