You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2016/10/08 17:54:55 UTC
[Bug 60227] New: Cannot update more than 2 existing formulas
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Bug ID: 60227
Summary: Cannot update more than 2 existing formulas
Product: POI
Version: 3.15-FINAL
Hardware: PC
OS: Windows NT
Status: NEW
Severity: blocker
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: hasitha.gunasinghe@iicorporate.com
I am reading a file using the following code
String strFile = "C:\\usr\\_excel_\\test.xlsx";
FileInputStream file = new FileInputStream(new File(strFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
Then I try to update an existing formula on column 1 for more than 2 rows using
for(int index = 0 ; index < 10; index++){
Row row = sheet.getRow(index);
Cell cell = row.getCell(0);
cell.setCellFormula("SUM(200)");
}
It updates the cell with the new formula, but when we try to open the excel
file, I get
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error072480_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\eurest (21).xlsm'</summary><removedRecords
summary="Following is a list of removed records:">
<removedRecord>Removed Records: Shared formula from /xl/worksheets/sheet6.xml
part</removedRecord>
<removedRecord>Removed Records: Formula from /xl/calcChain.xml part
(Calculation properties)</removedRecord></removedRecords></recoveryLog>
NOTE: it works well if I change the for loop condition to index<3 and update
only 2 cells.
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #10 from Javen O'Neal <on...@apache.org> ---
Do you still get a corrupted workbook if you replace
> cell.setCellFormula("SUM(200)");
with
> cell.setCellValue("text");
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #8 from Hasitha <ha...@iicorporate.com> ---
(In reply to Javen O'Neal from comment #6)
> Created attachment 34341 [details]
> output workbook from unit test in comment 5
I get the following error when I try to open the attached excel file
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_03.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\bug60227-out
(1).xlsx'</summary><additionalInfo><info>Excel completed file level validation
and repair. Some parts of this workbook may have been repaired or
discarded.</info></additionalInfo><removedRecords summary="Following is a list
of removed records:"><removedRecord>Removed Records: Shared formula from
/xl/worksheets/sheet1.xml part</removedRecord><removedRecord>Removed Records:
Formula from /xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |DUPLICATE
Status|NEW |RESOLVED
--- Comment #14 from Yegor Kozlov <ye...@dinom.ru> ---
*** This bug has been marked as a duplicate of bug 61869 ***
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #7 from Hasitha <ha...@iicorporate.com> ---
Yes. I do get the following error.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\test (1).xlsx'</summary><removedRecords
summary="Following is a list of removed records:"><removedRecord>Removed
Records: Shared formula from /xl/worksheets/sheet1.xml
part</removedRecord><removedRecord>Removed Records: Formula from
/xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #3 from Javen O'Neal <on...@apache.org> ---
Are you sure that the error message Excel gives you is from the attached test
file? The error record refers to sheet6.xml and Eutelsat (21).xlsm
Seems like the error message may be coming from a different file.
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #13 from Javen O'Neal <on...@apache.org> ---
Potentially related or duplicate: bug 47570
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #12 from Javen O'Neal <on...@apache.org> ---
Potential duplicate: bug 58106
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #2 from Hasitha <ha...@iicorporate.com> ---
Created attachment 34339
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34339&action=edit
all you need is a few formulas defined in the sheet.
all you need is a few formulas defined in the sheet. Try to update more than 3
existing formulas and we get the error when trying to open the .xlsx file.
Thanks
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Javen O'Neal <on...@apache.org> ---
Can you attach the file needed to reproduce this bug?
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #4 from Hasitha <ha...@iicorporate.com> ---
The error message was copied over from a different file. But you should be able
to reproduce the error from the attached file. Were you able to reproduce ?
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #5 from Javen O'Neal <on...@apache.org> ---
I had no issues opening the file in LibreOffice after executing the following
code.
Looking at the error message from comment 0, there may be an issue with
overwritten shared formulas not getting removed from the workbook. However, the
example workbook (attachment 34339) does not appear to have these shared
formulas.
Added to TestXSSFBugs.java:
@Test
public void test60227() throws Exception {
XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new
File("/tmp/bug60227.xlsx"));
Sheet sheet = wb.getSheetAt(0);
for (int index = 0 ; index < 10; index++){
Row row = sheet.getRow(index);
Cell cell = row.getCell(0);
cell.setCellFormula("SUM(200)");
}
OutputStream fos = new FileOutputStream("/tmp/bug60227-out.xlsx");
wb.write(fos);
fos.close();
wb.close();
}
Using the test.xlsx file from attachment 34339 and the unit test that I wrote
based on your description from comment 0, do you still get an error in Excel,
and if you do, can you submit that error message?
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |hasitha.gunasinghe@iicorpor
| |ate.com
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #15 from Yegor Kozlov <ye...@dinom.ru> ---
Fixed in r1818818
Updating a shared formula should now preserve all references to it from other
cells. I ran your test and Excel 2013 opens the output without any issues.
Yegor
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #11 from Hasitha <ha...@iicorporate.com> ---
(In reply to Javen O'Neal from comment #10)
> Do you still get a corrupted workbook if you replace
> > cell.setCellFormula("SUM(200)");
> with
> > cell.setCellValue("text");
No.I do not get the error if I do cell.setCellValue("text");
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #6 from Javen O'Neal <on...@apache.org> ---
Created attachment 34341
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34341&action=edit
output workbook from unit test in comment 5
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
--- Comment #9 from Hasitha <ha...@iicorporate.com> ---
(In reply to Javen O'Neal from comment #5)
> I had no issues opening the file in LibreOffice after executing the
> following code.
> Looking at the error message from comment 0, there may be an issue with
> overwritten shared formulas not getting removed from the workbook. However,
> the example workbook (attachment 34339 [details]) does not appear to have
> these shared formulas.
>
> Added to TestXSSFBugs.java:
> @Test
> public void test60227() throws Exception {
> XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new
> File("/tmp/bug60227.xlsx"));
> Sheet sheet = wb.getSheetAt(0);
> for (int index = 0 ; index < 10; index++){
> Row row = sheet.getRow(index);
> Cell cell = row.getCell(0);
> cell.setCellFormula("SUM(200)");
> }
> OutputStream fos = new FileOutputStream("/tmp/bug60227-out.xlsx");
> wb.write(fos);
> fos.close();
> wb.close();
> }
>
> Using the test.xlsx file from attachment 34339 [details] and the unit test
> that I wrote based on your description from comment 0, do you still get an
> error in Excel, and if you do, can you submit that error message?
yes. I do get the error when I ran your unit test code and opened the file in
Microsoft Excel.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\test (1).xlsx'</summary><removedRecords
summary="Following is a list of removed records:"><removedRecord>Removed
Records: Shared formula from /xl/worksheets/sheet1.xml
part</removedRecord><removedRecord>Removed Records: Formula from
/xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>
--
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
[Bug 60227] Cannot update more than 2 existing formulas
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Hasitha <ha...@iicorporate.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Priority|P2 |P1
--
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
[Bug 60227] Overwriting all cells containing a shared formula
results in a corrupted workbook
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60227
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Cannot update more than 2 |Overwriting all cells
|existing formulas |containing a shared formula
| |results in a corrupted
| |workbook
--
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