Bug 60346 - Changing sheet order in SXSSF worksheet made from template corrupts embeddings if comments are present
Summary: Changing sheet order in SXSSF worksheet made from template corrupts embedding...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-06 11:24 UTC by laszlo szenthe
Modified: 2016-11-21 20:30 UTC (History)
0 users



Attachments
Template with embedding to reproduce the bug. (14.09 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-11-06 11:24 UTC, laszlo szenthe
Details

Note You need to log in before you can comment on or make changes to this bug.
Description laszlo szenthe 2016-11-06 11:24:28 UTC
Created attachment 34423 [details]
Template with embedding to reproduce the bug.

1) Create an SXSSF workbook from template that has an embedding.
2) Create a sheet
3) Move sheet to first position
4) Create a cell and set a comment
5) Write out the workbook

Result: the embedding from the template lost its formula, so it is not possible to open from Excel.

(Order of step 3 and 4 can be changed)

Sample template attached.


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Szenthe László
 */
public class TestAttach {

    public static final String WORKDIR = "C:\\tmp";
    
    public static void main(String[] args) throws Throwable {
        try {

            // Open the template workbook containing an embedding
            SXSSFWorkbook wb = new SXSSFWorkbook(new XSSFWorkbook(new File( WORKDIR + File.separator + "xlsx.xlsx")));

            // Create a sheet
            Sheet sheet = wb.createSheet("test");
            
            // Insert into first position
            wb.setSheetOrder("test", 0);
            
            // Create a cell with comment attached
            Row row = sheet.createRow(1);
            Cell cell = row.createCell(1);

            CreationHelper factory = wb.getCreationHelper();
            Drawing drawing = sheet.createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();
            anchor.setCol1(cell.getColumnIndex());
            anchor.setCol2(cell.getColumnIndex() + 3);
            anchor.setRow1(row.getRowNum());
            anchor.setRow2(row.getRowNum() + 6);

            
            Comment comment;
            comment = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString("comment");
            comment.setString(str);
            //comment.setAuthor("Author");

            // Assign the comment to the cell
            cell.setCellComment(comment);
            
            
            
            // Write out the workbook
            wb.write(new FileOutputStream(new File(WORKDIR, "out.xlsx")));
            
        } catch (IOException | InvalidFormatException ex) {
            ex.printStackTrace();
        }

    }
}