0
XSSFWorkbook existingWorkbook = (XSSFWorkbook) WorkbookFactory.create(new File("c:/test1.xlsx"));
XSSFWorkbook brandNewWorkbook = (XSSFWorkbook) WorkbookFactory.create(true);
XSSFSheet existingSheet = existingWorkbook.getSheet("Sheet1");
CTWorksheet ctWorksheet = existingSheet.getCTWorksheet();
List<CTConditionalFormatting> formattingList = ctWorksheet.getConditionalFormattingList();
XSSFSheet sheet = brandNewWorkbook.createSheet("Sheet1");
for (CTConditionalFormatting cf : formattingList) {
    CTConditionalFormatting newCf = sheet.getCTWorksheet().addNewConditionalFormatting();
    newCf.xsetSqref(cf.xgetSqref());
    newCf.setCfRuleArray(cf.getCfRuleList().toArray(new CTCfRule[]{}));
}
CTDxfs dxfs = existingWorkbook.getStylesSource().getCTStylesheet().getDxfs();
brandNewWorkbook.getStylesSource().getCTStylesheet().setDxfs(dxfs);
try (FileOutputStream out = new FileOutputStream(new File("c:/test2.xlsx"))) {
    brandNewWorkbook.write(out);
}

This was the answer of Andrey Grigoriev on how to copy XSSF Sheet conditional formatting to XSSF Sheet conditional formatting.

Now I would need pretty much the same. But from XSSF to SXSSF (Streaming API). We cannot get rid of the streaming api because of memory issues, so we can`t use the solution above.

I can`t even find a method on the sxssf Sheet to .add() or .set() a conditional style to it. Is this supported? There is only a getSheetConditionalFormatting() method.

Silmarillion1973
  • 1
  • 1
    SXSSFWorkbook has `public XSSFWorkbook getXSSFWorkbook()`. I wouldn't recommend modifying the underlying XSSFWorkbook (or related XSSFSheets) for a SXSSFWorkbook in most cases but for this conditional formatting case, it might work ok. You can get the sheetIndex from an SXSSFSheet instance and find the related XSSFSheet from the XSSFWorkbook based on this index. – PJ Fanning Feb 03 '23 at 10:47

0 Answers0