Get text and background color of a cell in .xls sheet as hex in java

Question

I need to validate that the hex color of the text of the cell Discount is #FF1493 and the hex color of the background of the same cell is #FFC0CB.

I'm getting the rgb color of the background but I could not convert it into hex and I'm not able to get the hex color of the text.

Please find below the code and a screenshot of the table in order to give you an idea about the cell into question.

        String fileFormat = FileManagement.getFileFormat(new java.io.File(fileName));
    List<String> content = new ArrayList<>();
    HSSFWorkbook workbookXLS = null;
    try {
        InputStream ExcelFileToRead = new FileInputStream(fileName);
        //Getting the workbook instance for .XLS('old' excel format) file
        workbookXLS = new HSSFWorkbook(ExcelFileToRead);
    } catch (FileNotFoundException | NotOLE2FileException ex) {
        fail(ex.getMessage() + "! File: " + fileName);
    }
    //getting the first or specific sheet from the workbook
    HSSFSheet sheetXLS = workbookXLS.getSheetAt(0);
    HSSFRow rowXLS;
    HSSFCell cellXLS;
    //Iterating all the rows in the sheet
    Iterator rows = sheetXLS.rowIterator();
    while (rows.hasNext()) {
        rowXLS = (HSSFRow) rows.next();
        if (rowXLS.getRowNum() = 7) {
            cellXLS = rowXLS.getCell(0);
            if (cellXLS.getCellTypeEnum() == CellType.STRING) {
                if (cellXLS.getStringCellValue().equals("loads : Id")) {
                    Color colorFG = cellXLS.getCellStyle().getFillForegroundColorColor();
                    short[] rgb = HSSFColor.toHSSFColor(colorFG).getTriplet();   //this returns the rgb color of the background -> 255 192 203
                    System.out.println("THE FIRST COLOR RGB IS " + rgb[0] + "SECOND " + rgb[1] + "THIRD " + rgb[2]);
                    Color colorBG = cellXLS.getCellStyle().getFillBackgroundColorColor();
                    short[] hshs = HSSFColor.toHSSFColor(colorBG).getTriplet();  //this returns ->  0 0 0
                    System.out.println("THE SECOND COLOR RGB IS" + hshs[0] + "SECOND " + hshs[1] + "THIRD " + hshs[2]);
                }
            } else if (cellXLS.getCellTypeEnum() == CellType.NUMERIC) {
                content.add(String.valueOf(cellXLS.getNumericCellValue()));
            }
        }
    }[![enter image description here][1]][1]

Any help will be highly appreciated.

GolamMazid Sajib · Accepted Answer · 2018-07-26T08:32:32.710

3

You can try with this:

String hex = String.format("#%02x%02x%02x", rgb[0], rgb[1], rgb[2]);

or for capital X:

String hex = String.format("#%02X%02X%02X", rgb[0], rgb[1], rgb[2]);

edited Jul 26 '18 at 08:32

answered Jul 26 '18 at 06:40

GolamMazid Sajib

8,698
6
21
39

Thank you for the idea how to convert it into hex, Sajib! I managed to get the rgb color of the text using the answer provided in https://stackoverflow.com/questions/11501434/apache-poi-getting-exact-font-color-from-excel-in-java/14347451. – Mr Cas Jul 26 '18 at 07:26

Get text and background color of a cell in .xls sheet as hex in java

1 Answers1