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