Bug 65398

Created attachment 37910 [details]
source Excel file OOXML (XSSF), source Excel file BIFF (HSSF) and source picture file

If a HSSFPatriarch read from existant HSSFWorkbook contains a chart then every manipulation of that HSSFPatriarch through Apache POI code - either adding a picture, creating a comment or creating a text box - leads to a corrupt workbook which Excel is not able to open. This issue is in HSSF only. In XSSF all that works properly.

To reproduce take my provided code and the provided resources. In HSSF (Excel.xls) after running the code ExcelNew.xls is corrupt. The code is made so that each part (add picture, add comment, add text box) can easily commentted out.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.util.IOUtils;

import java.io.*;

class ExcelHSSFPatriarchWithChart {

 public static void main(String[] args) throws Exception {

  //Workbook wb  = WorkbookFactory.create(new FileInputStream("./Excel.xlsx")); String filePath = "./ExcelNew.xlsx";
  Workbook wb  = WorkbookFactory.create(new FileInputStream("./Excel.xls")); String filePath = "./ExcelNew.xls";

  CreationHelper helper = wb.getCreationHelper();
  ClientAnchor anchor;

  Sheet sheet = wb.getSheetAt(0); // sheet contains a chart

///* add picture start
  //add picture data to this workbook.
  InputStream is = new FileInputStream("./sample.jpg");
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
  is.close();
  //add a picture shape
  anchor = helper.createClientAnchor();
  //set top-left corner of the picture,
  //subsequent call of Picture#resize() will operate relative to it
  anchor.setCol1(3);
  anchor.setRow1(2);
  Picture pict = drawing.createPicture(anchor, pictureIdx);
  //auto-size picture relative to its top-left corner
  pict.resize();
//*/ //add picture end

///* add comment start
  Row row = sheet.createRow(19); // row 20
  Cell cell = row.createCell(3); // D20
  // When the comment box is visible, have it show in a 1x3 space
  anchor = helper.createClientAnchor();
  anchor.setCol1(cell.getColumnIndex());
  anchor.setCol2(cell.getColumnIndex()+1);
  anchor.setRow1(row.getRowNum());
  anchor.setRow2(row.getRowNum()+3);
  // Create the comment and set the text+author
  Comment comment = drawing.createCellComment(anchor);
  RichTextString str = helper.createRichTextString("Hello, World!");
  comment.setString(str);
  comment.setAuthor("Apache POI");
  // Assign the comment to the cell
  cell.setCellComment(comment);
//*/ //add comment end


///* add text box start
  if (drawing instanceof HSSFPatriarch) {
   HSSFPatriarch patriarch = (HSSFPatriarch)drawing;
   HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2));
   textbox.setString(new HSSFRichTextString("This is a test") );
  } else if (drawing instanceof XSSFDrawing) {
   XSSFDrawing xssfDrawing = (XSSFDrawing)drawing;
   XSSFTextBox textbox = xssfDrawing.createTextbox(new XSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2));
   textbox.setText(new XSSFRichTextString("This is a test") );
  }
//*/ //add text box end

  FileOutputStream out = new FileOutputStream(filePath);
  wb.write(out);
  out.close();
  wb.close();

 }
}