Apache poi“下拉”格式

Question

我对apache poi的要求类似于Excel中的“下拉”格式设置。因此，获取一个示例行，在每个单元格中获取“格式”，并将其应用于下面的所有单元格。根据要求设置格式包括数字格式和单元格的背景颜色随值的变化。因此，我编写了一个类，它从示例行的单元格中获取CellStyle并根据其应用。

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(){}
}

在Alex Richter的帮助下，我了解到需要使用工作表的SheetConditionalFormatting。如何获取当前应用于单元格的conditionalformatting并向下扩展影响范围？

Answer 1

你的问题不是很清楚。但我怀疑您希望将格式从一个目标行复制到多个相邻的以下行。您还希望扩展条件格式规则的范围，以便以下相邻行中的单元格也遵循该规则。因此，与Excel的格式绘制器所做的相同，如果选择一行，则单击格式绘制器，然后选择相邻的多行。

如何复制单元格样式，你已经知道了。但为什么这么复杂呢？将单元格样式从一个单元格复制到另一个单元格只需一行程序:targetCell.setCellStyle(sourceCell.getCellStyle())；。

其次，我们也应该复制可能的行样式。下面的示例有一个方法void copyRowStyle(Row sourceRow,Row targetRow)。

有了这个，我们可以扩展条件格式规则的范围。下面的示例有一个方法void expandConditionalFormatting(Cell sourceCell,Cell targetCell)。它将条件格式规则的范围从SourceCell扩展到TargetCell。

显示原理的完整示例：

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();
 }
}