Anybody willing to take a look at this? Anything else I could try? Or just some hints where to look in the source code?
My app generates XLS files like this
. load static "template" XLS file (with formatting and auto-filters) containing
a single sheet
. add logo to the first sheet
. fill in data
. write the result
This was working fine until I needed to fill multiple sheets (number of sheets
being variable).
I tried to clone the first sheet as often as necessary.
However all I am getting is an IIOOBE:
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 2, Size:
1
at java.util.ArrayList.RangeCheck(ArrayList.java:546)
at java.util.ArrayList.get(ArrayList.java:321)
at
org.apache.poi.hssf.model.DrawingManager2.getDrawingGroup(DrawingManager2.java:125)
at
org.apache.poi.hssf.model.DrawingManager2.allocateShapeId(DrawingManager2.java:71)
at
org.apache.poi.hssf.record.EscherAggregate.convertPatriarch(EscherAggregate.java:871)
at
org.apache.poi.hssf.record.EscherAggregate.convertUserModelToRecords(EscherAggregate.java:704)
at
org.apache.poi.hssf.record.EscherAggregate.getRecordSize(EscherAggregate.java:502)
at org.apache.poi.hssf.model.Sheet.preSerialize(Sheet.java:1517)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1238)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1158)
(using latest POI release, i.e. 3.6)
I tried various workarounds, but never got it working correctly.
Without adding the logo all works well.
See below for a self-contained example - the attached XLS template must be in
the working folder
when running the code.
I tried digging into the POI source code, but quickly got lost.
Any ideas?
Thanks
Stephen
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
/**
* Tests adding a picture with POI to an existing Excel in conjunction with
cloning of sheets.
* Fails with an exception (using POI 3.6) - but see comment below for
attempted workarounds.
*/
public class PictureTest {
public static void main(String[] args) throws IOException {
byte[] pngData = createPng();
HSSFWorkbook workbook = loadWorkbook("poi-picture-test.xls");
workbook.cloneSheet(0); // Causes IndexOutOfBoundsException on
workbook.write()
// If comment is removed there will be no exception, but a broken
picture on second sheet:
// workbook = reloadWorkbook(workbook);
addPictureToAllSheets(workbook, pngData);
// This was a workaround (comment the call above to
addPictureToAllSheets() and enable the call below).
// It is working when the file is opened in Excel 2007.
// However a user reported missing auto-filters when opened in Excel
2000:
// addNewPictureToAllSheets(workbook, pngData);
saveWorkbook(workbook, "poi-picture-result.xls");
}
private static void addPictureToAllSheets(HSSFWorkbook workbook, byte[]
pngData) {
int pictureIndex = workbook.addPicture(pngData,
HSSFWorkbook.PICTURE_TYPE_PNG);
int sheetCount = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetCount; ++sheetIndex) {
addPicture(workbook, sheetIndex, pictureIndex);
}
}
private static void addNewPictureToAllSheets(HSSFWorkbook workbook, byte[]
pngData) {
int sheetCount = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetCount; ++sheetIndex) {
int pictureIndex = workbook.addPicture(pngData,
HSSFWorkbook.PICTURE_TYPE_PNG);
addPicture(workbook, sheetIndex, pictureIndex);
}
}
private static HSSFWorkbook reloadWorkbook(HSSFWorkbook workbook) throws
IOException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
ByteArrayInputStream bais = new
ByteArrayInputStream(baos.toByteArray());
return new HSSFWorkbook(bais);
}
private static void addPicture(HSSFWorkbook workbook, int sheetIndex, int
pictureIndex) {
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
Drawing drawing = sheet.createDrawingPatriarch();
CreationHelper helper = workbook.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setRow1(0);
anchor.setCol1(0);
Picture pict = drawing.createPicture(anchor, pictureIndex);
pict.resize();
}
private static HSSFWorkbook loadWorkbook(String xlsPath) throws IOException
{
FileInputStream fis = new FileInputStream(xlsPath);
HSSFWorkbook workbook = new HSSFWorkbook(fis, true);
fis.close();
return workbook;
}
private static void saveWorkbook(HSSFWorkbook workbook, String filePath)
throws IOException {
FileOutputStream fos = new FileOutputStream(filePath);
try {
workbook.write(fos);
}
finally {
close(fos);
}
}
private static byte[] createPng() throws IOException {
BufferedImage image = new BufferedImage(250, 40,
BufferedImage.TYPE_INT_ARGB);
Graphics2D g = (Graphics2D) image.getGraphics();
g.setFont(new Font("SansSerif", Font.BOLD, 20));
g.setStroke(new BasicStroke(3.0f, BasicStroke.CAP_ROUND,
BasicStroke.JOIN_ROUND));
g.setPaint(Color.ORANGE);
g.fillRect(0, 0, 300, 50);
g.setPaint(Color.WHITE);
g.drawString("Fortis IT-Services GmbH", 8, 30);
g.dispose();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(image, "PNG", baos);
byte[] data = baos.toByteArray();
return data;
}
private static void close(Closeable closeable) {
try {
closeable.close();
}
catch (IOException ignore) {
}
}
}
poi-picture-test.xls
Description: poi-picture-test.xls
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
