poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 52122] [PATCH] conditional formatting based on formula not (re-)calculated properly
Date Mon, 07 Mar 2016 09:45:07 GMT
https://bz.apache.org/bugzilla/show_bug.cgi?id=52122

carlo.dellacqua <carlo.dellacqua@ext.piksel.it> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|WORKSFORME                  |---

--- Comment #8 from carlo.dellacqua <carlo.dellacqua@ext.piksel.it> ---
I've downloaded the last version of POI (poi-bin-3.14, poi-src-3.14) and the
problem is still present; 
to solve the problem I've changed the source file
CFRecordsAggregate.java
adding after line 74
    header = pHeader;
the call to
    header.setNeedRecalculation(true);

The following is the test class I use to create the xls file

package test;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

public class TestExcelConditionalFormattingByFormula_2 {
    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")};
              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.");
      }
}

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message