0

i'm currently adding merged regions to my xlsx files using Apache POI. I'm using the RegionUtil to create borders for merged cells spanning multiple columns and rows.

The part about selecting the correct borderstyle works fine, but the color itself is a black (instead of the expected grey from the input awt.Color). The API only mentions an int color, i wrongly assumend they mean the index : https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/RegionUtil.html

How do i provide the correct int value (based on my input awt.Color)so the bordercolor is the same one as in my normal cellstyle?

Code for setting up the merged borders

private void setBordersToMergedCells(XSSFSheet in_sheet, java.awt.Color in_bordercolor) {
    int numMerged = in_sheet.getNumMergedRegions();

    for (int i = 0; i < numMerged; i++) {
        //this works
        CellRangeAddress mergedRegions = in_sheet.getMergedRegion(i);
        RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegions, in_sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegions, in_sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegions, in_sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegions, in_sheet);
        //this does not work
        RegionUtil.setLeftBorderColor(new XSSFColor(in_bordercolor).getIndex(), mergedRegions, in_sheet);
        RegionUtil.setRightBorderColor(new XSSFColor(in_bordercolor).getIndex(), mergedRegions, in_sheet);
        RegionUtil.setTopBorderColor(new XSSFColor(in_bordercolor).getIndex(), mergedRegions, in_sheet);
        RegionUtil.setBottomBorderColor(new XSSFColor(in_bordercolor).getIndex(), mergedRegions, in_sheet);           
    }

For comparison, here is how i set up my cellstyle using (given) awt colors (works perfectly):

protected XSSFCellStyle createBorderedStyleXLSX(Workbook in_wb, java.awt.Color in_bordercolor) {
    XSSFCellStyle style = (XSSFCellStyle) in_wb.createCellStyle();
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderColor(XSSFCellBorder.BorderSide.TOP, new XSSFColor(in_bordercolor));
    style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, new XSSFColor(in_bordercolor));
    style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, new XSSFColor(in_bordercolor));
    style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, new XSSFColor(in_bordercolor));
    return style;
}
ptstone
  • 478
  • 7
  • 17
  • Check `isIndexed()` of your newly created `XSSFColor`. I would assume that it has no index or defaults to 0. You are probably looking for `IndexedColors.GREY_80_PERCENT` – XtremeBaumer Jan 18 '19 at 15:24
  • 1
    Unfortunately I'm not looking for a specific color, the color is a given awt color depending on my input. However, i tried the line `RegionUtil.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex(), mergedRegions, in_sheet);` and surprisingly, it didn't fix it but instead changed my whole cells to black (which is quite strange, since it should affect only borders?). – ptstone Jan 18 '19 at 15:37
  • This is not possible until now. See https://stackoverflow.com/questions/45126788/not-able-to-set-custom-color-in-xssfcell-apache-poi/45127209#45127209. Your new created `XSSFColor` are not indexed. So this cannot work. See https://stackoverflow.com/questions/50712340/apply-fill-colors-and-borders-to-excel-range-using-apache-poi/50762030#50762030 for a working example using `PropertyTemplate` – Axel Richter Jan 18 '19 at 16:49
  • Thank you for your examples, i will dive into them deeper after some sleep. I think something might be off with my region merger, since they turn black when i add the index of an indexedColor. For now i "solved" it with an ugly hack by adding another row of empty cells after the header whose cellstyle only contains the left border style and color... – ptstone Jan 18 '19 at 17:28

0 Answers0