How many POI developers actively monitor this list? I haven't gotten any
answers to those questions.
Meanwhile I have a new question. If I create a conditional formatting
which is linked to the entire document and colors every other row, how can
I get individual colors in cell styles to override that, or how can I get
the conditional formatting to only color cells that have no color in their
individual cell styles?
On Tue, Dec 13, 2016 at 9:59 AM, Eric Douglas <edoug...@blockhouse.com>
wrote:
> So I took another stab at it. This seems to work, but I have questions.
>
> import java.awt.Color;
> import java.io.File;
> import java.io.FileOutputStream;
> import java.io.IOException;
>
> import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
> import org.apache.poi.ss.usermodel.FillPatternType;
> import org.apache.poi.ss.usermodel.PatternFormatting;
> import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
> import org.apache.poi.ss.util.CellRangeAddress;
> import org.apache.poi.xssf.usermodel.XSSFCell;
> import org.apache.poi.xssf.usermodel.XSSFCellStyle;
> import org.apache.poi.xssf.usermodel.XSSFColor;
> import org.apache.poi.xssf.usermodel.XSSFSheet;
> import org.apache.poi.xssf.usermodel.XSSFWorkbook;
>
> public class TestExcel {
>
> public static void main(String[] args) throws IOException {
> XSSFWorkbook wb = new XSSFWorkbook();
> XSSFSheet curSheet = wb.createSheet("Sheet " +
> (wb.getNumberOfSheets() + 1));
> XSSFCell c1 = curSheet.createRow(0).createCell(0);
> c1.setCellValue(wb.getCreationHelper().createRichTextString("No
> color"));
> XSSFCell c2 = curSheet.createRow(1).createCell(0);
> c2.setCellValue(wb.getCreationHelper().createRichTextString("Color
> this cell"));
> XSSFCell c3 = curSheet.createRow(2).createCell(0);
> c3.setCellValue(wb.getCreationHelper().createRichTextString("No
> color"));
> XSSFCell c4 = curSheet.createRow(3).createCell(0);
> c4.setCellValue(wb.getCreationHelper().createRichTextString("Color
> this cell"));
> // set one cell's color
> final XSSFCellStyle style1 = wb.createCellStyle();
> style1.setFillForegroundColor(new XSSFColor(new
> Color(123,124,125)));
> style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
> c4.setCellStyle(style1);
> // set all cells' color, every other row
> CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:
> AMJ1048576")};
> SheetConditionalFormatting sheetCF = curSheet.
> getSheetConditionalFormatting();
> ConditionalFormattingRule rule1 = sheetCF.
> createConditionalFormattingRule("ISEVEN(ROW())");
> PatternFormatting fill1 = rule1.createPatternFormatting();
> final XSSFColor customColor = new XSSFColor(new Color(228, 247,
> 247));
> fill1.setFillBackgroundColor(customColor);
> fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
> sheetCF.addConditionalFormatting(regions, rule1);
> File fi = new File("output.xlsx");
> if (fi.exists()) {
> fi.delete();
> }
> FileOutputStream output = new FileOutputStream(fi);
> wb.write(output);
> wb.close();
> output.flush();
> output.close();
> }
> }
>
> 1) Why does it change the background of one cell using
> setFillForegroundColor, but to change the background of many cells with a
> condition I have to call setFillBackgroundColor??
> 2) Why does this create a generic format? When I call
> createPatternFormatting() it shows up in the LibreOffice Calc Styles and
> Formatting section as ConditionalStyle_1. Can I name this style?
> 3) Why does this look like a regular style in the Calc program with many
> attributes I can set, but the API only allows me to set the fill color?
>
> On Mon, Dec 12, 2016 at 4:33 PM, Nick Burch <apa...@gagravarr.org> wrote:
>
>> On Mon, 12 Dec 2016, Eric Douglas wrote:
>>
>>> I found one sample that shows how to code the condition using
>>> org.apache.poi.ss.usermodel.SheetConditionalFormatting.addConditionalFormatting()
>>> to put in the formula that would color each cell if it's in an even
>>> numbered row, but I'm having trouble figuring out the API to apply the
>>> formula to every cell on the worksheet.
>>>
>>
>> For every cell on a sheet, just give a cellrangeaddress that covers the
>> whole extent
>>
>> For every formula cell, you'd need to loop over all cells checking the
>> cell type, then add just those
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
>> For additional commands, e-mail: user-h...@poi.apache.org
>>
>>
>
