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
> > > >>
> > > >>
> > > >
> > >
> >
>
