CODEWITHSUNDEEP
javaapache-poi
Lalit Chattar
Lalit Chattar

Reputation: 1984

Apache POI get cell color when reading xlsx file

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.

Upvotes: 5

Views: 13203

Answers (3)

Volodymyr Taras
Volodymyr Taras

Reputation: 91

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

Upvotes: 0

TikkaBhuna
TikkaBhuna

Reputation: 515

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.

Upvotes: 9

vikiiii
vikiiii

Reputation: 9456

You can use this code to get the cell color.

cell.getCellStyle().getFillBackgroundColor();

Try 

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

Upvotes: 3

Related Questions