I am setting the excel cell foreground color, this is the Java 8 code look like:

 /**
 * @param cell
 */
private void setCellColor(Cell cell) {
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle cellStyle = createStyle(workbook);
    cell.getRow().getCell(cell.getColumnIndex()).setCellStyle(cellStyle);
}

/**
 * @param cell
 */
private void setCellColor0(Cell cell) {
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle cellStyle = createStyle(workbook);
    cell.getRow().getCell(1).setCellStyle(cellStyle);
}

In the code context, I invoke the two function to turn the cell color. I found out only one cell turned to red color. seems the new set cell style override the old cell. I did not figure out why did this happen, what should I do to avoid this problem? what I want is make all the cell turn to red color which invoke the setCellColor function. I am using the POI: api group: 'org.apache.poi', name: 'poi', version: '4.0.0'. this is the createStyle function:

private CellStyle createStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(255, 0, 0);
        // get the palette index of that color
        short palIndex = myColor.getIndex();
        cellStyle.setFillForegroundColor(palIndex);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }

this did not work:

int idx = cell.getColumnIndex();
cell.getRow().getCell(idx).setCellStyle(cellStyle);

this did not work:

cell.setCellStyle(cellStyle);

this work:

cell.getRow().getCell(0).setCellStyle(cellStyle);

after do some test, I found that if I set the column of current cell, it would not work. If I set the style less than the current column. It works. For example, current column index is 5, I set the 0,1,2,3,4 column style works, but the column 5 did not work. Why current cell style could not set? I think the cell was created in the context, this is the class which set the style:

public class CellStyleWriteHandler extends AbstractCellStyleStrategy {

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        setCellColorEnhance(cell, IndexedColors.RED);
        // impl(cell,head,relativeRowIndex);
    }

    static void setCellColorEnhance(Cell cell, IndexedColors color) {
        Map<String, Object> properties = new HashMap<String, Object>();
        properties.put(CellUtil.FILL_FOREGROUND_COLOR, color.getIndex());
        properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
        CellUtil.setCellStyleProperties(cell, properties);
    }
}

CodePudding user response：

In Excel cell styles are stored on workbook level. Multiple cells in multiple sheets might share the same style. And cell styles contain multiple styling options like font, color, number format, alignment, ...

That's why it is a bad idea to get the cell style of a cell and change it when there could be other cells which share the same style and should not be changed. Also it is a bad idea to create an own cell style for each cell as there is a limit for the count of unique cell formats/cell styles per workbook. See Excel specifications and limits.

To solve this problem Apache POI provides CellUtil. It's method CellUtil.setCellStyleProperties is able setting specific cell style properties to a cell without influencing the other styling options of that cell. Also it is to prevent creating too many styles and reaching the limit.

The following complete example shows how to use CellUtil to set cell colors. It changes the color of all cells in all sheets of the given workbook if the cell value contains the word "red" or contains a minus sign, for example because of a negative numeric value.

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;

import java.util.Map;
import java.util.HashMap;

public class UsingCellUtilToColorCells {
 
 static void setCellColor(Cell cell, IndexedColors color) {
  Map<String, Object> properties = new HashMap<String, Object>();
  properties.put(CellUtil.FILL_FOREGROUND_COLOR, color.getIndex());
  properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
  CellUtil.setCellStyleProperties(cell, properties);
 }
 
 public static void main(String[] args) throws Exception {
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./Template.xlsx")); String filePath = "./ExcelOut.xlsx";
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Template.xls")); String filePath = "./ExcelOut.xls";

  DataFormatter dataFormatter = new DataFormatter(java.util.Locale.US);
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  for (Sheet sheet : workbook) {
   for (Row row : sheet) {
    for (Cell cell : row) {
     String cellValue =  dataFormatter.formatCellValue(cell, formulaEvaluator);
     if (cellValue.contains("red") || cellValue.contains("-")) {
      setCellColor(cell, IndexedColors.RED);
      System.out.println(sheet.getSheetName()   ", "   cell.getAddress()  ":"   cellValue   ", "   "cell color set to red");
     }
    }
   }   
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

CodePudding user response：

your code is assign one cell , and that is all right. if you want to fill CellStyle by row , you need use
int cellNum = cell.getRow().getLastCellNum() , and use for i . like for (int j = 0; j < cellNum; j ) { row.getCell(j).setCellStyle(cellStyle); }