Apache poi "pull down" formatting

Question

I have a requirement for apache poi to act like "pulling down" formatting in excel. So taking a sample row, getting the "formatting" in each cell and applying it to all the cells below. Formatting according to the requirement includes number formats and the cells' background colors changing depending on the value. So I wrote a class that gets the CellStyle from the example row's cells and applies it according.

public class FormatScheme implements ObjIntConsumer<Sheet> {

    private Map<Integer, CellStyle> cellFormats = new LinkedHashMap<>();

    public static FormatScheme of(Row row, int xOffset){
        FormatScheme scheme = new FormatScheme();

        for (int i = xOffset; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if(cell==null) continue;

            scheme.cellFormats.put(i, cell.getCellStyle());
        }

        return scheme;
    }


    @Override
    public void accept(Sheet sheet, int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if(row==null) row=sheet.createRow(rowIndex);
        Row finalRow = row;

        cellFormats.entrySet().forEach(entry -> {
            Cell cell = finalRow.getCell(entry.getKey());
            if(cell==null) cell= finalRow.createCell(entry.getKey());

            cell.setCellStyle(entry.getValue());
        });
    }


    private FormatScheme(){}
}

This does seem to work for the number formats but doesn't grab the changing background colors. ~I guess, I'm missing something.~

With help from Alex Richter I understand that I need to use the sheet's SheetConditionalFormatting. How can I get the ConditionalFormatting that are currently applied to a cell and expand the range the affect downward?

Answer 1

Your question is not really clear. But I suspect you want copying formatting from one target row to multiple adjacent following rows. And you want expanding the ranges of conditional formatting rules too, so that the cells in the adjacent following rows also follow that rules. So the same what Excel's format painter does if you select one row, then click format painter and then select multiple adjacent following rows.

How to copy cell styles, you have got already. But why doing this that complicated? Copying cell styles form one cell to another is a one-liner: targetCell.setCellStyle(sourceCell.getCellStyle());.

Second we should copy possible row style too. The following example has a method void copyRowStyle(Row sourceRow, Row targetRow) for this.

To expand the ranges for the conditional formatting rules we need getting the rules which are applied to the cell. The rules are stored on sheet level. So we need traversing the SheetConditionalFormatting to get the rules where the cell is in range. The following example has a method List<ConditionalFormatting> getConditionalFormattingsForCell(Cell cell) for this.

Having this we can expand the ranges of the conditional formatting rules. The following example has a method void expandConditionalFormatting(Cell sourceCell, Cell targetCell) for this. It expands the ranges of the conditional formatting rules from sourceCell to targetCell.

Complete example which shows the principle:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.util.*;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
 
import java.util.List;
import java.util.ArrayList;
 
class ExcelCopyFormatting {
 
 static List<ConditionalFormatting> getConditionalFormattingsForCell(Cell cell) {
  List<ConditionalFormatting> conditionalFormattingList = new ArrayList<ConditionalFormatting>();
  Sheet sheet = cell.getRow().getSheet();
  SheetConditionalFormatting sheetConditionalFormatting = sheet.getSheetConditionalFormatting();
  for (int i = 0; i < sheetConditionalFormatting.getNumConditionalFormattings(); i++) {
   ConditionalFormatting conditionalFormatting = sheetConditionalFormatting.getConditionalFormattingAt(i);
   CellRangeAddress[] cellRangeAddressArray = conditionalFormatting.getFormattingRanges();
   for (CellRangeAddress cellRangeAddress : cellRangeAddressArray) {
    if (cellRangeAddress.isInRange(cell)) {
     conditionalFormattingList.add(conditionalFormatting);
    }
   }
  }
  return conditionalFormattingList;
 }
 
 static void expandConditionalFormatting(Cell sourceCell, Cell targetCell) {
  List<ConditionalFormatting> conditionalFormattingList = getConditionalFormattingsForCell(sourceCell);
  for (ConditionalFormatting conditionalFormatting : conditionalFormattingList) {
   CellRangeAddress[] cellRangeAddressArray = conditionalFormatting.getFormattingRanges();
   for (int i = 0; i < cellRangeAddressArray.length; i++) {
    CellRangeAddress cellRangeAddress = cellRangeAddressArray[i];
    if (cellRangeAddress.isInRange(sourceCell)) {
     if (cellRangeAddress.getFirstRow() > targetCell.getRowIndex()) {
      cellRangeAddress.setFirstRow(targetCell.getRowIndex());
     }
     if (cellRangeAddress.getFirstColumn() > targetCell.getColumnIndex()) {
      cellRangeAddress.setFirstColumn(targetCell.getColumnIndex());
     }
     if (cellRangeAddress.getLastRow() < targetCell.getRowIndex()) {
      cellRangeAddress.setLastRow(targetCell.getRowIndex());
     }
     if (cellRangeAddress.getLastColumn() < targetCell.getColumnIndex()) {
      cellRangeAddress.setLastColumn(targetCell.getColumnIndex());
     }
     cellRangeAddressArray[i] = cellRangeAddress;
    }
   }
   conditionalFormatting.setFormattingRanges(cellRangeAddressArray);
  }
 }
 
 static void copyRowStyle(Row sourceRow, Row targetRow) {
  if (sourceRow.isFormatted()) {
   targetRow.setRowStyle(sourceRow.getRowStyle());
  }
 }

 static void copyCellStyle(Cell sourceCell, Cell targetCell) {
  targetCell.setCellStyle(sourceCell.getCellStyle());
 }

 static void copyFormatting(Sheet sheet, int fromRow, int upToRow) {
  Row sourceRow = sheet.getRow(fromRow);
  for (int r = fromRow + 1; r <= upToRow; r++) {
   Row targetRow = sheet.getRow(r);
   if (targetRow == null) targetRow = sheet.createRow(r); 
   copyRowStyle(sourceRow, targetRow);
   for (Cell sourceCell : sourceRow) {
    Cell targetCell = targetRow.getCell(sourceCell.getColumnIndex());
    if (targetCell == null) targetCell = targetRow.createCell(sourceCell.getColumnIndex());
    copyCellStyle(sourceCell, targetCell);
    if (r == upToRow) {
     if (getConditionalFormattingsForCell(sourceCell).size() > 0) {
      expandConditionalFormatting(sourceCell, targetCell);
     }
    }
   }
  }
 }
 
 public static void main(String[] args) throws Exception {
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./Workbook.xls")); String filePath = "./WorkbookNew.xls";
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Workbook.xlsx")); String filePath = "./WorkbookNew.xlsx";
 
  Sheet sheet = workbook.getSheetAt(0);
 
  copyFormatting(sheet, 1, 9); // copy formatting from row 2 up to row 10
 
  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();
 }
}