I can take a stab at one of these. #1) Why does Conditional formatting use setBackgroundColor to set the fill color for Solid Fill while styles use setForgroundColor?
I noticed this discrepancy myself. To answer the question I generated a simple spreadsheet in MS Excel with one cell colored via style, and a second colored with a conditional format. It turned out that MS Excel uses Foreground Color for styles, and Background Color for conditional formats. I don't know why, and it only seems to apply to the Solid Fill pattern. For other fill patterns, Excel uses Foreground Color and Background Color consistently. You can validate this yourself. Create a simple spreadsheet with various fills using conditional formatting, and styles. Save it as an XSLX. Rename the resulting file to .ZIP, and look at the resulting XML. You will see the discrepancy. Why? I don't know, maybe ask on MSDN? MS is unlikely to fix this as it is pervasive at this point. -----Original Message----- From: Eric Douglas [mailto:edoug...@blockhouse.com] Sent: Tuesday, December 13, 2016 10:00 AM To: POI Users List <user@poi.apache.org> Subject: Re: How do you code cell striping? So I took another stab at it. This seems to work, but I have questions. import java.awt.Color; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.PatternFormatting; import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class TestExcel { public static void main(String[] args) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet curSheet = wb.createSheet("Sheet " + (wb.getNumberOfSheets() + 1)); XSSFCell c1 = curSheet.createRow(0).createCell(0); c1.setCellValue(wb.getCreationHelper().createRichTextString("No color")); XSSFCell c2 = curSheet.createRow(1).createCell(0); c2.setCellValue(wb.getCreationHelper().createRichTextString("Color this cell")); XSSFCell c3 = curSheet.createRow(2).createCell(0); c3.setCellValue(wb.getCreationHelper().createRichTextString("No color")); XSSFCell c4 = curSheet.createRow(3).createCell(0); c4.setCellValue(wb.getCreationHelper().createRichTextString("Color this cell")); // set one cell's color final XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new Color(123,124,125))); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); c4.setCellStyle(style1); // set all cells' color, every other row CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:AMJ1048576")}; SheetConditionalFormatting sheetCF = curSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISEVEN(ROW())"); PatternFormatting fill1 = rule1.createPatternFormatting(); final XSSFColor customColor = new XSSFColor(new Color(228, 247, 247)); fill1.setFillBackgroundColor(customColor); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); sheetCF.addConditionalFormatting(regions, rule1); File fi = new File("output.xlsx"); if (fi.exists()) { fi.delete(); } FileOutputStream output = new FileOutputStream(fi); wb.write(output); wb.close(); output.flush(); output.close(); } } 1) Why does it change the background of one cell using setFillForegroundColor, but to change the background of many cells with a condition I have to call setFillBackgroundColor?? 2) Why does this create a generic format? When I call createPatternFormatting() it shows up in the LibreOffice Calc Styles and Formatting section as ConditionalStyle_1. Can I name this style? 3) Why does this look like a regular style in the Calc program with many attributes I can set, but the API only allows me to set the fill color? On Mon, Dec 12, 2016 at 4:33 PM, Nick Burch <apa...@gagravarr.org> wrote: > On Mon, 12 Dec 2016, Eric Douglas wrote: > >> I found one sample that shows how to code the condition using >> org.apache.poi.ss.usermodel.SheetConditionalFormatting.addConditional >> Formatting() to put in the formula that would color each cell if it's >> in an even numbered row, but I'm having trouble figuring out the API >> to apply the formula to every cell on the worksheet. >> > > For every cell on a sheet, just give a cellrangeaddress that covers > the whole extent > > For every formula cell, you'd need to loop over all cells checking the > cell type, then add just those > > Nick > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional > commands, e-mail: user-h...@poi.apache.org > >
