BTW, if you look at the quick guide https://poi.apache.org/spreadsheet/quick-guide.html#CellProperties you will find two methods of drawing borders without creating all those unused intermediate styles.
On Mon, Dec 19, 2016 at 4:04 PM, Eric Douglas <[email protected]> wrote: > 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] > > >> > > >> > > > > > >
