I am pretty sure this is the way it works in Excel. Conditional formatting overrides the stated cell style. In order to get any other colors you need to add additional rules to the conditional formatting.
-----Original Message----- From: Eric Douglas [mailto:edoug...@blockhouse.com] Sent: Monday, December 19, 2016 4:04 PM To: POI Users List <user@poi.apache.org> Subject: Re: How do you code cell striping? Yeah, I wouldn't expect really fast response from a list like this. My previous message was 6 days ago. I didn't see an easy way to get alternate row styling with regular styles (to color only even numbered rows, and keep only even numbered rows colored if the user sorts by a different column) but I did get the conditional formatting working. The bad part is the order of precedence. The application applies the individual cell colors then the conditional format. I wanted cell colors in the individual cell styles to override the conditional. The only thing I've found that I think would work is coding a VBA method into the conditional formatting to tell the "ISEVEN(ROW())" to apply only if the cell does not already have individual coloring, but the syntax for applying VBA sounds ugly. I did unzip some xlsx files and read through the schema and we can easily see why some people send us really large files. Saving spreadsheets from LibreOffice wants to write a lot of styles it doesn't need. Some just code duplicate styles and end up with hundreds or thousands, where I got mine only writing unique so there's about 11. Some of the POI API doesn't make sense, as I tried to apply a thin border around every populated cell and ended up with 9 border styles, with cell styles only referencing 3 of them. ie: <borders count="9"><border><left style="hair"><color auto="true"/></left><right style="hair"><color auto="true"/></right><top style="hair"><color auto="true"/></top><bottom style="hair"><color auto="true"/></bottom><diagonal/></border><border><bottom style="hair"/></border><border><left style="hair"/><bottom style="hair"/></border><border><left style="hair"/><right style="hair"/><bottom style="hair"/></border><border><left style="hair"/><right style="hair"/><top style="hair"/><bottom style="hair"/></border><border><left style="hair"/><right style="hair"/><top style="hair"/><bottom style="hair"><color indexed="64"/></bottom></border><border><left style="hair"><color indexed="64"/></left><right style="hair"/><top style="hair"/><bottom style="hair"><color indexed="64"/></bottom></border><border><left style="hair"><color indexed="64"/></left><right style="hair"><color indexed="64"/></right><top style="hair"/><bottom style="hair"><color indexed="64"/></bottom></border><border><left style="hair"><color indexed="64"/></left><right style="hair"><color indexed="64"/></right><top style="hair"><color indexed="64"/></top><bottom style="hair"><color indexed="64"/></bottom></border></borders> <cellStyleXfs count="8"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="1"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="2"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="3"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="4"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="5"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="6"/><xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="7"/></cellStyleXfs> <cellXfs count="8"><xf numFmtId="0" fontId="0" fillId="0" borderId="4" xfId="0" applyFont="true" applyBorder="true"><alignment horizontal="left"/></xf><xf xfId="1" borderId="8" fillId="5" fontId="0" numFmtId="0" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="left" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="2" borderId="8" fillId="5" fontId="0" numFmtId="0" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="center" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="3" borderId="8" fillId="3" fontId="0" numFmtId="0" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="left" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="4" borderId="8" fillId="2" fontId="0" numFmtId="164" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="left" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="5" borderId="8" fillId="3" fontId="0" numFmtId="165" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="left" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="6" borderId="8" fillId="3" fontId="0" numFmtId="165" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="right" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf><xf xfId="7" borderId="8" fillId="2" fontId="0" numFmtId="165" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true"><alignment horizontal="right" textRotation="0" vertical="bottom" wrapText="false"/><protection locked="true"/></xf></cellXfs> The conditional formatting ends up in sheet1.xml after the sheetData, and I coded an option to skip heading rows: <conditionalFormatting sqref="A1:AMJ1048576"><cfRule type="expression" dxfId="0" priority="1"><formula>AND(ISEVEN(ROW()),ROW()>1)</formula></cfRule></conditionalFormatting> which of course links to dxf in the styles.xml <dxfs count="1"><dxf><fill><patternFill patternType="solid"><bgColor rgb="E4F7F7"/></patternFill></fill></dxf></dxfs> On Mon, Dec 19, 2016 at 3:36 PM, Javen O'Neal <one...@apache.org> wrote: > I think there's a way to apply alternate row styling within regular > styles (not conditional formatting), but have never used POI to do > this. Rather than tell you the wrong answer, I'd rather stay quiet to > avoid unnecessary confusion. Read through the OOXML schemas or create > a file in Excel with alternate row styling, unzip the xlsx file, and > read the XML to figure out how POI needs to create the same file. > > > How many POI developers actively monitor this list? > At least half a dozen. Nick is one of them. > Some of us don't live in your timezone, so same-day responses are unlikely. > We use a minimum window of 72 hours whenever we vote on a release. > > Not all of us are experts at the feature you're needing help with, > conditional formatting, which may be another reason for low response. > > We volunteer our time, working on POI between our day jobs and > personal lives. Given the holiday season is close, I would expect > developers to be particularly busy, finishing projects at work before > the holiday closure and preparing for travel to relatives. > > Personally, I spend less time on POI when work at my day job gets busy. > This is to avoid coming down with a cold due to lack of sleep or > prolonged stress, or burning out. > > We appreciate your patience. > > On Dec 19, 2016 11:24, "Eric Douglas" <edoug...@blockhouse.com> wrote: > > How many POI developers actively monitor this list? I haven't gotten > any answers to those questions. > Meanwhile I have a new question. If I create a conditional formatting > which is linked to the entire document and colors every other row, how > can I get individual colors in cell styles to override that, or how > can I get the conditional formatting to only color cells that have no > color in their individual cell styles? > > On Tue, Dec 13, 2016 at 9:59 AM, Eric Douglas > <edoug...@blockhouse.com> > wrote: > > > 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. > addConditionalFormatting() > >>> 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 > >> > >> > > >
