Bug 60346

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();
        }

    }
}