Java get the excel cell background color from Apache poi

user_2953798 0
Viewed 2656

I am trying to get cell color info in .xlsx file using Apache POI.

Method cellStyle.getFillBackgroundColor() is returning short. How can I convert short to java.awt.Color or any other format(XSSFColor).

Ultimately I want to store the value of cell based on its background color.

 Workbook workbook = WorkbookFactory.create(new FileInputStream (new File(SAMPLE_XLSX_FILE_PATH)));
    Sheet sheet = workbook.getSheetAt(0);
    DataFormatter dataFormatter = new DataFormatter();
    sheet.forEach(row -> {
        row.forEach(cell -> {
            String cellValue = dataFormatter.formatCellValue(cell);
            CellStyle cellStyle = cell.getCellStyle();
            System.out.println(cellStyle.getFillBackgroundColor());
            //Color userColor = cellStyle.getFillBackgroundColor(); //ERROR 
        });
        System.out.println();
    });

I am using version 3.6 which I think do not support getFillBackgroundColorColor()

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.6</version>
</dependency>
xlsx apache-poi excel apache java
1 Answers

With .xlsx spreadsheets, you can call the getFillBackgroundColorColor (2 "Color" words) method. It returns an org.apache.poi.ss.usermodel.Color (not a very useful interface), that XSSFColor implements. Then you can cast it as XSSFColor.

XSSFColor = (XSSFColor) cellStyle.getFillBackgroundColorColor();

Alternatively, again with .xlxs spreadsheets, you can cast the CellStyle as an XSSFCellStyle, and XSSFCellStyle's getFillBackgroundColorColor method returns an XSSFColor directly. It also has getFillBackgroundXSSFColor which does the same thing.

Get the background fill color.

Note - many cells are actually filled with a foreground fill, not a background fill - see getFillForegroundColor()

Beware that solid fills are implemented as foreground colors, so the foreground color may be what you're really after. There are complementary methods for the foreground color, e.g. getFillForegroundColorColor.

user_17070910
Related