The PropertyTemplate is brand new? Somehow my search for the class turned up a POI 3.9 reference. I just know it's not finding in my jars (poi-3.15.jar, poi-ooxml-3.15.jar, poi-scratchpad-3.15.jar) date stamped 9/17/2016.
I was hoping to generate a clean file with no tags in it not being used by the spreadsheet in any way. At least 9 border tags when we're only referencing 1 or 2 isn't terrible. I know the styles get insane by default. My first attempt at using the API as is with the samples I could find on the internet generated some 8000+ style tags. I did my own style management, so I've generated a file with 3 columns of data and 13,725 rows having <cellStyleXfs count="5"> and <cellXfs count="5">. I just generate one dummy XSSFCellStyle object not referenced by any cell. Then... the cell style objects are shared, so I can't get a cell's style and change any property of it unless I want to affect all cells that share it's current style. So, any time I set the property of a cell I call every get and set method to copy each property we could want to set in our API from the cell's style to that dummy style. Then I run through a loop of cell styles in an array and compare each of those properties to saved styles. If I find a match I pass that style into the setCellStyle for that cell. Otherwise I pass it that dummy style with the properties copied plus the property I'm changing, add that style to the array, and generate a new dummy style. This seems as efficient as I can get. I don't know why the API must create all those border tags when I try to setup one style having a top/left/right/bottom border, but the only obvious workaround is to set that up once and remove the extra ones directly from the style sheet. Why must we call setBorderStyle and setBorderColor for each border? I would guess the obvious fix is to setup multiple borders with an array/map in a single method to have it only create one new border tag. 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 > > > > >> > > > > >> > > > > > > > > > > > > > > >
