You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Eric Douglas <ed...@blockhouse.com> on 2016/12/12 13:24:22 UTC
How do you code cell striping?
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.
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
I can take a stab at one of these.
#1) Why does Conditional formatting use setBackgroundColor to set the fill color for Solid Fill while styles use setForgroundColor?
I noticed this discrepancy myself. To answer the question I generated a simple spreadsheet in MS Excel with one cell colored via style, and a second colored with a conditional format. It turned out that MS Excel uses Foreground Color for styles, and Background Color for conditional formats. I don't know why, and it only seems to apply to the Solid Fill pattern. For other fill patterns, Excel uses Foreground Color and Background Color consistently. You can validate this yourself. Create a simple spreadsheet with various fills using conditional formatting, and styles. Save it as an XSLX. Rename the resulting file to .ZIP, and look at the resulting XML. You will see the discrepancy. Why? I don't know, maybe ask on MSDN? MS is unlikely to fix this as it is pervasive at this point.
-----Original Message-----
From: Eric Douglas [mailto:edouglas@blockhouse.com]
Sent: Tuesday, December 13, 2016 10:00 AM
To: POI Users List <us...@poi.apache.org>
Subject: Re: How do you code cell striping?
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 <ap...@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.addConditional
>> Formatting() 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-unsubscribe@poi.apache.org For additional
> commands, e-mail: user-help@poi.apache.org
>
>
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
I am pretty sure this is the way it works in Excel. Conditional formatting overrides the stated cell style. In order to get any other colors you need to add additional rules to the conditional formatting.
-----Original Message-----
From: Eric Douglas [mailto:edouglas@blockhouse.com]
Sent: Monday, December 19, 2016 4:04 PM
To: POI Users List <us...@poi.apache.org>
Subject: Re: How do you code cell striping?
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 <on...@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" <ed...@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
> <ed...@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 <ap...@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-unsubscribe@poi.apache.org For
> >> additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
>
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
Ok, I thought it was earlier than that. But as I said earlier, it will still do the same thing with the boarder and fill blocks in styles.xml. It is based on setCellStyleProperties() It will reduce the number of styles though vs. setCellStyleProperty().
-----Original Message-----
From: Eric Douglas [mailto:edouglas@blockhouse.com]
Sent: Tuesday, December 20, 2016 2:15 PM
To: POI Users List <us...@poi.apache.org>
Subject: Re: How do you code cell striping?
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 <mu...@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:edouglas@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:23 AM
> To: POI Users List <us...@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 <jm...@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
> > <ed...@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 <on...@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" <ed...@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
> > > > <edouglas@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
> > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <mu...@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:edouglas@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:23 AM
> To: POI Users List <us...@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 <jm...@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
> > <ed...@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 <on...@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" <ed...@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
> > > > <edouglas@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
> > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <mu...@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:edouglas@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:23 AM
> To: POI Users List <us...@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 <jm...@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
> > <ed...@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 <on...@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" <ed...@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
> > > > <edouglas@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
> > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
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:edouglas@blockhouse.com]
Sent: Tuesday, December 20, 2016 10:23 AM
To: POI Users List <us...@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 <jm...@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
> <ed...@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 <on...@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" <ed...@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
> > > <edouglas@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
> > > > <ap...@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-unsubscribe@poi.apache.org For
> > > >> additional commands, e-mail: user-help@poi.apache.org
> > > >>
> > > >>
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <jm...@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 <ed...@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 <on...@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" <ed...@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 <edouglas@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 <ap...@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-unsubscribe@poi.apache.org
> > > >> For additional commands, e-mail: user-help@poi.apache.org
> > > >>
> > > >>
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
That is the obvious. If the xlsx spec says it colors all cells using
individual cell styling, then it replaces those colors using conditional
formatting, if we can't change the order of precedence I'd need to code the
conditional formatting to check if the cell already has color applied. So
far the only way I can tell we could do that is by embedding a VBA method,
but the syntax for that is not so obvious.
On Tue, Dec 20, 2016 at 11:53 AM, Murphy, Mark <mu...@metalexmfg.com>
wrote:
> I just tried what you want to do in Excel. Conditional formats always
> override the cell's formatting. You can't set a cell's style to override
> the conditional formatting. The only thing you can do is omit the cells
> from conditional formatting that you want to leave alone.
>
> -----Original Message-----
> From: Murphy, Mark [mailto:murphymdev@metalexmfg.com]
> Sent: Tuesday, December 20, 2016 11:45 AM
> To: 'POI Users List' <us...@poi.apache.org>
> Subject: RE: How do you code cell striping?
>
> Can you do the color thing in Excel? If not, you probably can't do it in
> POI either.
>
> -----Original Message-----
> From: Eric Douglas [mailto:edouglas@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:34 AM
> To: POI Users List <us...@poi.apache.org>
> Subject: Re: How do you code cell striping?
>
> I did get the conditional formatting to work for row striping, other than
> when individual cells need color that should override the color in the
> conditional format.
> The data table idea sounds interesting but the first example Google found
> doesn't seem to work.
> https://svn.apache.org/repos/asf/poi/trunk/src/examples/
> src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
>
> On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal <ja...@gmail.com>
> wrote:
>
> > Looks like Microsoft suggests [1] conditional formatting or a data
> > table (XSSFTable), so you were on the right track to solve this with
> > POI's conditional formatting.
> >
> > Either way, it sounds like we should cover this somewhat common
> > scenario either in poi-examples or the spreadsheet quick guide. Pull
> > requests greatly appreciated.
> >
> > [1]
> > https://support.office.com/en-us/article/Apply-shading-to-
> > alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
> >
> >
> > On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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
> > > <ed...@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 <on...@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" <ed...@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 <
> > edouglas@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
> > > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > > >>
> > > > > >>
> > > > > >
> > > > >
> > > >
> > >
> >
>
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
I just tried what you want to do in Excel. Conditional formats always override the cell's formatting. You can't set a cell's style to override the conditional formatting. The only thing you can do is omit the cells from conditional formatting that you want to leave alone.
-----Original Message-----
From: Murphy, Mark [mailto:murphymdev@metalexmfg.com]
Sent: Tuesday, December 20, 2016 11:45 AM
To: 'POI Users List' <us...@poi.apache.org>
Subject: RE: How do you code cell striping?
Can you do the color thing in Excel? If not, you probably can't do it in POI either.
-----Original Message-----
From: Eric Douglas [mailto:edouglas@blockhouse.com]
Sent: Tuesday, December 20, 2016 10:34 AM
To: POI Users List <us...@poi.apache.org>
Subject: Re: How do you code cell striping?
I did get the conditional formatting to work for row striping, other than when individual cells need color that should override the color in the conditional format.
The data table idea sounds interesting but the first example Google found doesn't seem to work.
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal <ja...@gmail.com> wrote:
> Looks like Microsoft suggests [1] conditional formatting or a data
> table (XSSFTable), so you were on the right track to solve this with
> POI's conditional formatting.
>
> Either way, it sounds like we should cover this somewhat common
> scenario either in poi-examples or the spreadsheet quick guide. Pull
> requests greatly appreciated.
>
> [1]
> https://support.office.com/en-us/article/Apply-shading-to-
> alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
>
>
> On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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
> > <ed...@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 <on...@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" <ed...@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 <
> edouglas@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
> > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
That data table example? I ran the sample, it didn't crash, it just didn't
look like anything useful in LibreOffice. I don't know if Excel sees it as
anything, but our users are using LibreOffice so it has to work there. Our
spreadsheets will probably only be used in house but I only want to use
universal syntax in case we do send to someone who opens with a different
application so they can see it properly. I know some of our customers have
sent us spreadsheets made in Excel that don't open properly in anything but
Excel but I'd like to avoid that.
On Tue, Dec 20, 2016 at 11:45 AM, Murphy, Mark <mu...@metalexmfg.com>
wrote:
> Can you do the color thing in Excel? If not, you probably can't do it in
> POI either.
>
> -----Original Message-----
> From: Eric Douglas [mailto:edouglas@blockhouse.com]
> Sent: Tuesday, December 20, 2016 10:34 AM
> To: POI Users List <us...@poi.apache.org>
> Subject: Re: How do you code cell striping?
>
> I did get the conditional formatting to work for row striping, other than
> when individual cells need color that should override the color in the
> conditional format.
> The data table idea sounds interesting but the first example Google found
> doesn't seem to work.
> https://svn.apache.org/repos/asf/poi/trunk/src/examples/
> src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
>
> On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal <ja...@gmail.com>
> wrote:
>
> > Looks like Microsoft suggests [1] conditional formatting or a data
> > table (XSSFTable), so you were on the right track to solve this with
> > POI's conditional formatting.
> >
> > Either way, it sounds like we should cover this somewhat common
> > scenario either in poi-examples or the spreadsheet quick guide. Pull
> > requests greatly appreciated.
> >
> > [1]
> > https://support.office.com/en-us/article/Apply-shading-to-
> > alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
> >
> >
> > On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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
> > > <ed...@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 <on...@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" <ed...@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 <
> > edouglas@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
> > > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > > >>
> > > > > >>
> > > > > >
> > > > >
> > > >
> > >
> >
>
RE: How do you code cell striping?
Posted by "Murphy, Mark" <mu...@metalexmfg.com>.
Can you do the color thing in Excel? If not, you probably can't do it in POI either.
-----Original Message-----
From: Eric Douglas [mailto:edouglas@blockhouse.com]
Sent: Tuesday, December 20, 2016 10:34 AM
To: POI Users List <us...@poi.apache.org>
Subject: Re: How do you code cell striping?
I did get the conditional formatting to work for row striping, other than when individual cells need color that should override the color in the conditional format.
The data table idea sounds interesting but the first example Google found doesn't seem to work.
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal <ja...@gmail.com> wrote:
> Looks like Microsoft suggests [1] conditional formatting or a data
> table (XSSFTable), so you were on the right track to solve this with
> POI's conditional formatting.
>
> Either way, it sounds like we should cover this somewhat common
> scenario either in poi-examples or the spreadsheet quick guide. Pull
> requests greatly appreciated.
>
> [1]
> https://support.office.com/en-us/article/Apply-shading-to-
> alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
>
>
> On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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
> > <ed...@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 <on...@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" <ed...@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 <
> edouglas@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
> > > > > <ap...@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-unsubscribe@poi.apache.org For
> > > > >> additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
I did get the conditional formatting to work for row striping, other than
when individual cells need color that should override the color in the
conditional format.
The data table idea sounds interesting but the first example Google found
doesn't seem to work.
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
On Tue, Dec 20, 2016 at 12:01 AM, Javen O'Neal <ja...@gmail.com> wrote:
> Looks like Microsoft suggests [1] conditional formatting or a data table
> (XSSFTable), so you were on the right track to solve this with POI's
> conditional formatting.
>
> Either way, it sounds like we should cover this somewhat common scenario
> either in poi-examples or the spreadsheet quick guide. Pull requests
> greatly appreciated.
>
> [1]
> https://support.office.com/en-us/article/Apply-shading-to-
> alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
>
>
> On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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 <ed...@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 <on...@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" <ed...@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 <
> edouglas@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 <ap...@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-unsubscribe@poi.apache.org
> > > > >> For additional commands, e-mail: user-help@poi.apache.org
> > > > >>
> > > > >>
> > > > >
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Javen O'Neal <ja...@gmail.com>.
Looks like Microsoft suggests [1] conditional formatting or a data table
(XSSFTable), so you were on the right track to solve this with POI's
conditional formatting.
Either way, it sounds like we should cover this somewhat common scenario
either in poi-examples or the spreadsheet quick guide. Pull requests
greatly appreciated.
[1]
https://support.office.com/en-us/article/Apply-shading-to-alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880
On Dec 19, 2016 19:57, "Mark Murphy" <jm...@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 <ed...@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 <on...@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" <ed...@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 <edouglas@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 <ap...@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-unsubscribe@poi.apache.org
> > > >> For additional commands, e-mail: user-help@poi.apache.org
> > > >>
> > > >>
> > > >
> > >
> >
>
Re: How do you code cell striping?
Posted by Mark Murphy <jm...@gmail.com>.
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 <ed...@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 <on...@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" <ed...@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 <ed...@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 <ap...@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-unsubscribe@poi.apache.org
> > >> For additional commands, e-mail: user-help@poi.apache.org
> > >>
> > >>
> > >
> >
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <on...@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" <ed...@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 <ed...@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 <ap...@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-unsubscribe@poi.apache.org
> >> For additional commands, e-mail: user-help@poi.apache.org
> >>
> >>
> >
>
Re: How do you code cell striping?
Posted by Javen O'Neal <on...@apache.org>.
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" <ed...@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 <ed...@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 <ap...@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <ed...@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 <ap...@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
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 <ap...@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: How do you code cell striping?
Posted by Eric Douglas <ed...@blockhouse.com>.
I don't want a loop. I don't want formula values on every cell.
I want a conditional format applied to the entire sheet to color every
other row. If you sort columns and change the order of the rows it should
still color every other row.
Can we attach files on this list? Attachments would show exactly what the
output looks like.
To reproduce what I'm explaining, here's the code of the first part.
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.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.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("Color this
cell"));
XSSFCell c2 = curSheet.createRow(1).createCell(0);
c2.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
XSSFCell c3 = curSheet.createRow(2).createCell(0);
c3.setCellValue(wb.getCreationHelper().createRichTextString("Color this
cell"));
XSSFCell c4 = curSheet.createRow(3).createCell(0);
c4.setCellValue(wb.getCreationHelper().createRichTextString("No
color"));
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.setFillForegroundColor(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();
}
}
Now to the second part I can't figure out how to code:
1) I open the output file in LibreOffice Calc, click the blank space on the
row and column heading bar to select all cells.
2) I select Styles and Formatting from the tool bar on the right.
3) I select the option New Style, give it a name, select OK.
4) I right click the style I just created, select Modify, select a
background color, select OK to accept.
5) With the style I just created still selected, I select the New Style
option, assign a different name. This second style shows under the first.
6) I right click this second style, Modify, and select No Fill background.
7) From the menu I select Format, Conditional Formatting, Manage. This
shows the format I created from the above program.
8) I Edit this condition, select Apply Style = the first style I just
created.
Rows are now striped. I select column A, select Data - Sort from the menu,
you see the rows change order and color is still applied to only even
numbered rows.
On Mon, Dec 12, 2016 at 4:33 PM, Nick Burch <ap...@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: How do you code cell striping?
Posted by Nick Burch <ap...@gagravarr.org>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org