Test class to reproduce the problem

TestExcelConditionalFormattingByFormula_2.java (text/plain), 5.21 KB, created by carlo.dellacqua on 2015-01-09 11:34:58 UTC

(hide)

Description: Test class to reproduce the problem

Filename:

MIME Type:

Creator: carlo.dellacqua

Created: 2015-01-09 11:34:58 UTC

Size: 5.21 KB

patch

obsolete

>package carlo; > >import java.io.FileOutputStream; >import java.io.IOException; > >import org.apache.poi.hssf.usermodel.HSSFCell; >import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule; >import org.apache.poi.hssf.usermodel.HSSFPatternFormatting; >import org.apache.poi.hssf.usermodel.HSSFRichTextString; >import org.apache.poi.hssf.usermodel.HSSFRow; >import org.apache.poi.hssf.usermodel.HSSFSheet; >import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting; >import org.apache.poi.hssf.usermodel.HSSFWorkbook; >import org.apache.poi.hssf.util.HSSFColor; >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.ConditionalFormattingRule; >import org.apache.poi.ss.usermodel.IndexedColors; >import org.apache.poi.ss.usermodel.PatternFormatting; >import org.apache.poi.ss.usermodel.Row; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.SheetConditionalFormatting; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.ss.util.CellReference; > >/* >I've been encountering a problem very similar to that described in here. >When setting conditional formatting in a sheet by means of POI, if I choose to set a rule with a formula >that contain a reference to a cell that is not present in the range defined by "CellRangeAddress" object, >the cells included in this region (the CellRangeAddress object) will not update to the formatting associated to the rule. >Better to post some code: >------------------------------------------------------------------- >SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); > >ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A$15>75"); > >PatternFormatting fill1 = rule1.createPatternFormatting(); >fill1.setFillBackgroundColor(IndexedColors.BLUE.index); >fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); > >CellRangeAddress[] regions = {CellRangeAddress.valueOf("A18:A20")}; > >sheetCF.addConditionalFormatting(regions, rule1); >------------------------------------------------------------------- > >If I change the formula in "$A$18>75", that is if the cell involved in the formula is included in the region defined >by CellRangeAddress object, opening the file with excel the cells are formatted correctly. > */ >public class TestExcelConditionalFormattingByFormula_2 { > /** > * @param args > * @throws IOException > */ > public static void main(String[] args) throws IOException { > FileOutputStream fos = null; > try { > Workbook workbook = new HSSFWorkbook(); > Sheet sheet = workbook.createSheet("Conditional Formatting Test"); > sheet.setColumnWidth(0, 256 * 10); > sheet.setColumnWidth(1, 256 * 10); > sheet.setColumnWidth(2, 256 * 10); > > // Create some content. > // row 0 > Row row = sheet.createRow(0); > > Cell cell0 = row.createCell(0); > cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell0.setCellValue(100); > > Cell cell1 = row.createCell(1); > cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell1.setCellValue(120); > > Cell cell2 = row.createCell(2); > cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell2.setCellValue(130); > > // row 1 > row = sheet.createRow(1); > > cell0 = row.createCell(0); > cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell0.setCellValue(200); > > cell1 = row.createCell(1); > cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell1.setCellValue(220); > > cell2 = row.createCell(2); > cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell2.setCellValue(230); > > // row 2 > row = sheet.createRow(2); > > cell0 = row.createCell(0); > cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell0.setCellValue(300); > > cell1 = row.createCell(1); > cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell1.setCellValue(320); > > cell2 = row.createCell(2); > cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); > cell2.setCellValue(330); > > // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank. > SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting(); > > ConditionalFormattingRule rule = formatting.createConditionalFormattingRule("$A$1>75"); > > PatternFormatting pattern = rule.createPatternFormatting(); > pattern.setFillBackgroundColor(IndexedColors.BLUE.index); > pattern.setFillPattern(PatternFormatting.SOLID_FOREGROUND); > > CellRangeAddress[] range = {CellRangeAddress.valueOf("B2:C2" /* "A2:A3" */)}; > CellRangeAddress[] range2 = {CellRangeAddress.valueOf("B1:C1")}; > > formatting.addConditionalFormatting(range, rule); > formatting.addConditionalFormatting(range2, rule); > > // Write file. > fos = new FileOutputStream("conditional-sheet.xls"); > workbook.write(fos); > } finally { > if (fos != null) { > try { > fos.close(); > } catch (IOException x) { > } > } > } > > System.out.println("ready."); > } > >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]