Hello, I got two problems when I use POI 3.7 or 3.8beta2 to open and edit an existing XLSX document (see attachment) created with Microsoft Office 2010. First, after using the following code to add additional images to an existing sheet, all existing shapes in the sheet are erased:
File templateWorkbookFile = ApachePoiReport
.getFileFromClassLoader(templateWorkbookFileName);
if (templateWorkbookFile == null) {
throw new IllegalStateException(
"Error - spreadsheetFile is null.");
}
// open template workbook
InputStream templateWorkbookFileInputStream =
new FileInputStream(templateWorkbookFile);
XSSFWorkbook workbook =
new XSSFWorkbook(templateWorkbookFileInputStream);
XSSFSheet sheet = workbook.getSheet(sheetName);
// create 300 pictures
XSSFDrawing drawingPatriach = sheet.createDrawingPatriarch();
// get picture file
File imageFile = getFileFromClassLoader(imageFileName);
if (imageFile == null) {
throw new IllegalStateException("Error - imageFile is null.");
}
for (int row = 4, column = 0; row < 304; row++) {
InputStream imageFileInputStream =
new FileInputStream(imageFile);
byte[] imageData = new byte[(int)imageFile.length()];
imageFileInputStream.read(imageData);
imageFileInputStream.close();
int pictureIndex = workbook.addPicture(imageData,
Workbook.PICTURE_TYPE_JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setCol1(column);
anchor.setRow1(row);
XSSFPicture picture =
drawingPatriach.createPicture(anchor, pictureIndex);
// auto-size picture relative to its top-left corner
picture.resize();
}
Second, after using the following code to add textboxes, rectangles or ellipses
to an existing sheet and saving the XLSX document, when opening the XLSX
document with Microsoft Excel 2010, it is telling me that it needs to repair
the document and erases all inserted shapes from the document:
// insert 900 textboxes and format it
Font textBoxFont = workbook.createFont();
textBoxFont.setFontName("Arial");
textBoxFont.setFontHeightInPoints((short) 8);
textBoxFont.setColor(IndexedColors.RED.getIndex());
for (int row = 4, column = 1; row < 904; row++) {
XSSFCreationHelper creationHelper = workbook.getCreationHelper();
XSSFClientAnchor anchor = creationHelper.createClientAnchor();
anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
anchor.setCol1(column);
anchor.setRow1(row);
anchor.setDx2(1023); // 100 %
anchor.setDy2(255); // 100 %
XSSFTextBox textbox =
drawingPatriach.createTextbox(anchor);
textbox.setNoFill(true);
textbox.setLineStyle(HSSFShape.LINESTYLE_NONE);
XSSFRichTextString textboxText = new XSSFRichTextString(
textfield);
textboxText.applyFont(textBoxFont);
textbox.setText(textboxText);
}
// insert 2000 rectangles
for (int row = 4, column = 12; row < 2004; row++) {
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
anchor.setCol1(column);
anchor.setRow1(row);
anchor.setDx2(1023); // 100 %
anchor.setDy2(255); // 100 %
XSSFSimpleShape rectangle =
drawingPatriach.createSimpleShape(anchor);
rectangle.setShapeType(ShapeTypes.RECT);
rectangle.setFillColor(Color.RED.getRed(),
Color.RED.getGreen(),
Color.RED.getBlue());
rectangle.setLineStyle(HSSFShape.LINESTYLE_SOLID);
rectangle.setLineStyleColor(Color.BLUE.getRed(),
Color.BLUE.getGreen(),
Color.BLUE.getBlue());
}
// insert 2000 circles
for (int row = 4, column = 13; row < 2004; row++) {
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
anchor.setCol1(column);
anchor.setRow1(row);
anchor.setDx2(1023); // 100 %
anchor.setDy2(255); // 100 %
XSSFSimpleShape rectangle = ((XSSFDrawing)drawingPatriach)
.createSimpleShape((XSSFClientAnchor)anchor);
rectangle.setShapeType(ShapeTypes.ELLIPSE);
rectangle.setFillColor(Color.RED.getRed(),
Color.RED.getGreen(),
Color.RED.getBlue());
rectangle.setLineStyle(HSSFShape.LINESTYLE_SOLID);
rectangle.setLineStyleColor(Color.BLUE.getRed(),
Color.BLUE.getGreen(),
Color.BLUE.getBlue());
}
Does anyone know if these are known issues or might I do something wrong?
Best regards,
Jens Götz
cycleplan_template.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
