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.

回答1:

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.

回答2:

You can use this code to get the cell color.

cell.getCellStyle().getFillBackgroundColor();

Try

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