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 <[email protected]> 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" <[email protected]> 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 <[email protected]> > 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 <[email protected]> > 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: [email protected] > >> For additional commands, e-mail: [email protected] > >> > >> > > >
