Apache POI get cell color when reading xlsx file

Question

Hello all i am reading one xlsx file using XSSF of Apche POI. Now i want to read color of the cell and apply same color on new xlsx file. how will i do it. my code is:

public void readXLSXFile(String filePath) throws FileNotFoundException, IOException
    {
        XSSFRow row;
        XSSFRow new_row;
        XSSFSheet sheet;
        XSSFCell cell;
        XSSFCell new_cell;
        XSSFCellStyle cellStyle;
        XSSFDataFormat dataFormat;
        XSSFColor color;

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet new_sheet = (XSSFSheet) workbook.createSheet();
        for(int i = 0; i < xssfWorkbook.getNumberOfSheets(); i++ )
        {
            sheet = xssfWorkbook.getSheetAt(i);
            for(int j =0; j<sheet.getLastRowNum(); j++)
            {
                row = (XSSFRow) sheet.getRow(j);
                new_row = new_sheet.createRow(j);
                for(int k = 0; k<row.getLastCellNum(); k++)
                {
                    cell = row.getCell(k);
                    new_cell = new_row.createCell(k);
                    cellStyle = workbook.createCellStyle();
                    dataFormat = workbook.createDataFormat();
                    cellStyle.setDataFormat(dataFormat.getFormat(cell.getCellStyle().getDataFormatString()));
                    color = cell.getCellStyle().getFillBackgroundColorColor();
                    cellStyle.setFillForegroundColor(color);
                    new_cell.setCellStyle(cellStyle);
                    System.out.println(cell.getCellStyle().getFillForegroundColor()+"#");
                    switch (cell.getCellType()) {
                    case 0:
                        new_cell.setCellValue(cell.getNumericCellValue());
                        break;
                    case 1:
                        new_cell.setCellValue(cell.getStringCellValue());
                        break;
                    case 2:
                        new_cell.setCellValue(cell.getNumericCellValue());
                        break;
                    case 3:
                        new_cell.setCellValue(cell.getStringCellValue());
                        break;
                    case 4:
                        new_cell.setCellValue(cell.getBooleanCellValue());
                        break;
                    case 5:
                        new_cell.setCellValue(cell.getErrorCellString());
                        break;
                    default:
                        new_cell.setCellValue(cell.getStringCellValue());
                        break;
                    }
                }
            }
        }
        workbook.write(new FileOutputStream("G:\\lalit.xlsx"));
    }

I an using Apche POI 3.8.

I've got a similar issue myself: http://stackoverflow.com/questions/18112155/access-to-the-color-palette-in-an-xssfworkbook I also get an indexed value of 64 for background which I'm pretty sure is not a valid value. I get it for both Orange and Black. — Paul Gregoire, Aug 07 '13 at 23:29

score 9 · Accepted Answer · answered May 16 '12 at 10:55

I posted a comment to vikiiii's answer. I thought I'd expand on it a bit more. His answer is specific to HSSF (.xls) but both the HSSF and XSSF classes descend from the same interface so the code is the same, you just use XSSF instead of HSSF. Seeing as you want to reuse the color I'd recommend using:

XSSFColor bgColor = xssfCell.getCellStyle().getFillBackgroundColorColor();

See here for the Javadoc. Now to set a new cell to that color you can use this.

secondCell.getCellStyle().setFillBackgroundColor(bgColor);

I'd recommend looking at the interfaces that the XSSF and HSSF classes descend from and have a look at making your code be able to handle both xls and xlsx files. As far as I'm aware the only difference is the way you set up the workbook, using WorkbookFactory.

vikiiii · Answer 2 · 2012-05-16T09:44:07.950

3

You can use this code to get the cell color.

cell.getCellStyle().getFillBackgroundColor();

Try

HSSFColor.getIndexHash().get(myCell.getCellStyle().getFillBackgroundColor())

edited May 16 '12 at 09:44

answered May 16 '12 at 09:30

vikiiii

9,246
9
49
68

thank you but i have all ready used it in my code that you can see. but it is not working. – Lalit Chattar May 16 '12 at 09:38
how can i apply that number on new cell? – Lalit Chattar May 16 '12 at 09:42
i am processing xlsx and using XSSF so how can i use HSSFColor – Lalit Chattar May 16 '12 at 09:49
You can use the same format. Try: xssfCell.getCellStyle().getFillBackgroundColor() or getFillBackgroundColorColor. The former returns a short and the second returns XSSFColor. http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html#getFillBackgroundColor%28%29 – TikkaBhuna May 16 '12 at 10:39

score 0 · Answer 3 · answered Apr 28 '23 at 11:27

I had .xlsx file, and this code helped me to retrieve correct HEX color values from cell background:

XSSFColor color = (XSSFColor) cell
    .getCellStyle()
    .getFillForegroundColorColor();
String hexColorValue = color
    .getCTColor()
    .xgetRgb()
    .getStringValue();

My org.apache.poi version is 3.17

Apache POI get cell color when reading xlsx file

3 Answers3

Linked