Bug 50833

Summary: workbook.write(outputstream) corrupts cell comments for protected workbooks
Product: POI Reporter: Bobby Lawrence <robertl>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: major    
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: sample xls workbook to use with code provided in description

Description Bobby Lawrence 2011-02-25 18:49:20 UTC
Created attachment 26695 [details]
sample xls workbook to use with code provided in description

If you have a workbook that is protected and you open the it and write it to an output stream, all comments in the workbook end up corrupted.
Attached is a sample workbook (protected password is "password") to use with the following code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CommentTest {
	public static void main(String[] args) throws Exception {
		File file = new File("Book1.xls");
		FileInputStream fileInputStream = new FileInputStream(file);
		HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
		workbook.write(new FileOutputStream(new File("Book1_copy.xls")));
	}
}
Comment 1 Nick Burch 2011-02-26 16:44:11 UTC
When you first read the file in, can POI see all your comments fine?

After writing the file out, can POI read it back in and still see the comments? (i.e. are they corrupted for both POI and Excel, or just for Excel?)
Comment 2 Bobby Lawrence 2011-02-28 10:05:06 UTC
Not quite sure why you need me to answer these questions as the bug is easily reproduce-able using the provided code and any Excel doc...
Don't you guys verify these bugs?
I assume that if I don't answer them, this bug report will probably be dismissed because no one takes the initiative to try to answer the questions themselves so I will...

Q: When you first read the file in, can POI see all your comments fine?
A: Yes

Q: After writing the file out, can POI read it back in and still see the comments?
(i.e. are they corrupted for both POI and Excel, or just for Excel?)
A: POI can read the file back in and still see the comments.



Here is a modified version of my original test code that answers your questions:


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CommentTest {
	public static void main(String[] args) throws Exception {
		File file = new File("Book1.xls");
		FileInputStream fileInputStream = new FileInputStream(file);
		HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
		System.out.println(file.getName() + " sheet1.cellA1 comment=" + workbook.getSheetAt(0).getRow(0).getCell(0).getCellComment().getString());
		File newFile = new File("Book1_copy.xls");
		workbook.write(new FileOutputStream(newFile));
		fileInputStream = new FileInputStream(newFile);
		workbook = new HSSFWorkbook(fileInputStream);
		System.out.println(newFile.getName() + " sheet1.cellA1 comment=" + workbook.getSheetAt(0).getRow(0).getCell(0).getCellComment().getString());
	}
}
Comment 3 Nick Burch 2011-03-04 16:05:04 UTC
Everyone here are volunteers, and there are lots of people with bugs and not nearly so many people producing patches to fix them. Plus, you know your problem much better than we do....

In terms of your problem, OpenOffice can open the poi written file just fine, so it looks like we're not doing too much wrong. 

However, your original source file seems to be problematic. When I try to run it through BiffViewer, BiffViewer blows up. When I try to run it through the beta Microsoft validator, that claims not to recognise the file. So, I think there's something odd about your source file.

How was the file generated?
Comment 4 Bobby Lawrence 2011-03-04 16:12:56 UTC
The example spreadsheet I uploaded to the bug was generated with Microsoft Office 2007 and saved as an Excel 97-2003 format.
Comment 5 David Fisher 2011-03-04 16:37:13 UTC
I opened the original file in Mac Excel 2008 and 2011. It opened fine. I did notice that the comment had a different vertical position in the two versions.

In 2008 it was slightly above the top of the top row.

in 2011 it was slightly below the top of the top row.
Comment 6 Nick Burch 2011-03-04 16:39:11 UTC
Hmm, so it ought to be fine then.

BiffViewer is blowing up though with:

java.lang.IllegalArgumentException: Name is too long: k~�����>�`�8��<()�~i:�
2Å$a�Ճp����۵���6��Ӕ��m.����#p1tP>��=� ��ȉa��y��Q1���8�YZwbN�!{��
	at org.apache.poi.hssf.record.WriteAccessRecord.setUsername(WriteAccessRecord.java:104)
	at org.apache.poi.hssf.record.WriteAccessRecord.<init>(WriteAccessRecord.java:72)
	at org.apache.poi.hssf.dev.BiffViewer.createRecord(BiffViewer.java:254)
	at org.apache.poi.hssf.dev.BiffViewer.createRecords(BiffViewer.java:86)
	at org.apache.poi.hssf.dev.BiffViewer.main(BiffViewer.java:416)

Until we get that fixed, we probably can't figure out what's being changed by the read/write from POI to know what it might be that excel doesn't like...
Comment 7 Bobby Lawrence 2011-03-04 16:40:16 UTC
I'm sorry - my comment was incorrect.
The file was created in MS Excel 2010.
Comment 8 karinkreider 2018-12-26 13:31:14 UTC
I'll draw the inference that https://domyessay.today/ doesn't have a decent answer, then, okay?