在java中使用apache poi更大的单元格颜色

时间:2021-07-07 22:52:40

I got a question for you.

我有个问题要问你。

I want to use apache poi to create a xls file and it's almost working Here is my code

我想使用apache poi创建一个xls文件,它几乎正常工作这是我的代码

public static void writeXLSFile(int L) throws IOException {

    String excelFileName = "Test.xls";//name of excel file
    String sheetName = "Sheet1";//name of sheet


    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);

    int i = 0;
    int l = 0;
    int c = 0;
    while (i < L+1) {
    Row row = sheet.createRow((short) l);
    Cell cell = row.createCell((short) c);
    cell.setCellValue(i+1);
    sheet.addMergedRegion(new CellRangeAddress(l, l + 9, c, c + 2));
    cell.setCellStyle(createBorderedStyle(wb));
        if (c>=5){
            c=0;
            l=l+10;
        }else {
            c=c+3;
        }
        i++;
    }

    FileOutputStream fileOut = new FileOutputStream(excelFileName);

    //write this workbook to an Outputstream.
    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

private static CellStyle createBorderedStyle(Workbook wb2) {
    CellStyle style = wb2.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment((short)2);
    style.setVerticalAlignment((short)0);

    return style;
}

The resultat I expect is number between 1 and L in black rectangle with 3 column and 10 row with 3 column long max. I want something like that :

我期望的结果是1到L之间的数字,黑色矩形有3列,10行有3列最长。我想要这样的东西:

https://image.noelshack.com/fichiers/2017/38/2/1505774253-uxbgme7s.png

https://image.noelshack.com/fichiers/2017/38/2/1505774253-uxbgme7s.png

I hope it's understandable, it's my first post Thank you guys

我希望这是可以理解的,这是我的第一篇帖子谢谢你们

1 个解决方案

#1


0  

You create the same row multiple times (so the values get lost except for the last one in a row). The borders should also be drawn around the complete range, not just the first cell. This would fix that problem:

您多次创建同一行(因此除了行中的最后一行之外,值会丢失)。边界也应该围绕整个范围绘制,而不仅仅是第一个单元格。这将解决这个问题:

    //create the first row
    Row row = sheet.createRow((short) l);
    while (i < L+1) {
        Cell cell = row.createCell((short) c);
        cell.setCellValue(i+1);
        sheet.addMergedRegion(new CellRangeAddress(l, l + 9, c, c + 2));

        //this adds the border
        CellRangeAddress cellRangeAddress = new CellRangeAddress(l, l + 9 , c, c + 2);
        HSSFRegionUtil.setBorderTop(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderRight(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);

        cell.setCellStyle(createBorderedStyle(wb));
        if (c>=5){
            c=0;
            l=l+10;
            //go to the next row here
            row = sheet.createRow((short) l);
        }else {
            c=c+3;
        }
        i++;
    }

About the alignment, this should help:

关于对齐,这应该有所帮助:

    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

#1


0  

You create the same row multiple times (so the values get lost except for the last one in a row). The borders should also be drawn around the complete range, not just the first cell. This would fix that problem:

您多次创建同一行(因此除了行中的最后一行之外,值会丢失)。边界也应该围绕整个范围绘制,而不仅仅是第一个单元格。这将解决这个问题:

    //create the first row
    Row row = sheet.createRow((short) l);
    while (i < L+1) {
        Cell cell = row.createCell((short) c);
        cell.setCellValue(i+1);
        sheet.addMergedRegion(new CellRangeAddress(l, l + 9, c, c + 2));

        //this adds the border
        CellRangeAddress cellRangeAddress = new CellRangeAddress(l, l + 9 , c, c + 2);
        HSSFRegionUtil.setBorderTop(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderRight(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);
        HSSFRegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cellRangeAddress, sheet, wb);

        cell.setCellStyle(createBorderedStyle(wb));
        if (c>=5){
            c=0;
            l=l+10;
            //go to the next row here
            row = sheet.createRow((short) l);
        }else {
            c=c+3;
        }
        i++;
    }

About the alignment, this should help:

关于对齐,这应该有所帮助:

    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);