I found a PropertyTemplate class. It is in the poi-3.16-beta1.jar. It is not in the 3.15 current download.
On Tue, Dec 20, 2016 at 11:44 AM, Murphy, Mark <murphym...@metalexmfg.com> wrote: > PropertyTemplate is new for 3.15. It is in package org.apache.poi.ss.util > > Here are the javadocs: https://poi.apache.org/ > apidocs/index.html?org/apache/poi/ss/util/PropertyTemplate.html > > Both PropertyTemplate and setCellStyleProperties() will help in reducing > the number of styles generated. But neither will reduce the number of fills > or boarders that are defined. That issue is deeper in the API. However, I > have not seen nearly the number of boarders and fills defined as I have > styles from using setCellStyleProperty(). You are right, the HashMap does > not have all the CellStyle properties in it, just the ones you are trying > to change. It uses a putAll() behind the scenes which does a merge with the > existing cell's style. > > -----Original Message----- > From: Eric Douglas [mailto:edoug...@blockhouse.com] > Sent: Tuesday, December 20, 2016 10:23 AM > To: POI Users List <user@poi.apache.org> > Subject: Re: How do you code cell striping? > > Yeah that doesn't work. > I wrote a test program that generates a .xlsx file and the borders section > looks like this: > <borders > count="1"><border><left/><right/><top/><bottom/>< > diagonal/></border></borders> > > I change nothing except for adding this default border method: > BorderStyle DEFAULTBORDER = BorderStyle.HAIR; > Map<String, Object> properties = new HashMap<String, Object>(); > // border around a cell > properties.put(CellUtil.BORDER_TOP, DEFAULTBORDER); > properties.put(CellUtil.BORDER_BOTTOM, DEFAULTBORDER); > properties.put(CellUtil.BORDER_LEFT, DEFAULTBORDER); > properties.put(CellUtil.BORDER_RIGHT, DEFAULTBORDER); > // Give it a color (AUTOMATIC) > properties.put(CellUtil.TOP_BORDER_COLOR, > IndexedColors.AUTOMATIC.getIndex()); > properties.put(CellUtil.BOTTOM_BORDER_COLOR, > IndexedColors.AUTOMATIC.getIndex()); > properties.put(CellUtil.LEFT_BORDER_COLOR, > IndexedColors.AUTOMATIC.getIndex()); > properties.put(CellUtil.RIGHT_BORDER_COLOR, > IndexedColors.AUTOMATIC.getIndex()); > Then the call to that util under my cell: > r = curSheet.createRow(1); > c = r.createCell(0); > CellUtil.setCellStyleProperties(c, properties); > > Then the borders section looks like this: > <borders > count="9"><border><left/><right/><top/><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> > > That method example also creates a new HashMap, so it's not pulling sheet > defaults. The font on that cell is different from the other cells. > > The next method described on that page references a PropertyTemplate. I > Googled that and found org.apache.poi.ss.util.PropertyTemplate, searched > that and found POI 3.9. Whatever that was appears to be gone in POI 3.15. > > On Mon, Dec 19, 2016 at 10:57 PM, Mark Murphy <jmarkmur...@gmail.com> > wrote: > > > 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 > > <edoug...@blockhouse.com> > > 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 <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 > > > > >> > > > > >> > > > > > > > > > > > > > > >
