Bug 53207 - A formula can not clear by [setCellFormula(null)] in Shared formulas.
Summary: A formula can not clear by [setCellFormula(null)] in Shared formulas.
Status: RESOLVED CLOSED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC Windows Vista
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-05-09 09:55 UTC by Arimitsu
Modified: 2021-11-27 10:20 UTC (History)
2 users (show)



Attachments
files for test input (12.79 KB, application/x-zip)
2012-05-09 09:55 UTC, Arimitsu
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Arimitsu 2012-05-09 09:55:16 UTC
Created attachment 28749 [details]
files for test input

[attachment : files.zip]
ok.xlsx : D1-D12 has formula
ng.xlsx : D1-D12 has shared formula

A result file[result_ng.xlsx] has break formula.
Open and Rescue by Excel, cells[D11-D12] formula is gone.

The setCellFormula method can not clear formula like this.
(*Exclude the case where only D1 is cleared.)
----------------------------------------------------
<sheetData>
 ..
 <c r="D1" s="2">
  <f t="shared" ref="D1:D12" si="0">B1*C1</f>
 ..
----------------------------------------------------

Test code.

public static void test() {
  formulaTest("ok.xlsx");
  formulaTest("ng.xlsx");
}

public static void formulaTest(String file) {
  ConditionalFormattingTest test = new ConditionalFormattingTest(file);
  test.init();
  XSSFSheet sheet = (XSSFSheet)test.getSheet(0);		
  for (int i=0; i<10; i++) {
    sheet.getRow(i).getCell(3).setCellFormula(null);
  }		
  test.writeBook("result_" + file);
}
Comment 1 PJ Fanning 2021-11-27 10:20:01 UTC
POI V5.1.0 has fixes for shared formulas. Reopen this if they do not help.