You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Stephen Friedrich <st...@fortis-it.eu> on 2010/02/22 17:19:30 UTC
Re: Bug when adding picture to cloned sheet?!
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) {
}
}
}
Re: Bug when adding picture to cloned sheet?!
Posted by anjsag <an...@ibsplc.com>.
I too want a solution for copying pictures to cloned sheet .
have tried cloning the sheet and adding the sheet to the same workbook. But
the picture in the base sheet is not copied to rest of the sheet. It gets
copied to one sheet and the rest of the sheet shows a picture object with a
message " Image cannot be displayed . Your computer have not enough memory
to open the image or the image may have been corrupted....."
Is there any way to copy the pictures in base sheet to other sheets as
well...What could be the issue ..
Is it an issue with POI ?
I am using poi-3.6-20091214.jar & working with Excel 2007
Thanks
AnjSag
--
View this message in context: http://old.nabble.com/Bug-when-adding-picture-to-cloned-sheet-%21-tp27178667p29172129.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Bug when adding picture to cloned sheet?!
Posted by MSB <ma...@tiscali.co.uk>.
Sorry to say that I do not have any idea of what could be causing the problem
but I would simply modify my template file were I in the same position. It
may be possible to take an educated guess at the maximum number of sheets
you are likely to need to create, and it seems that one solution would be to
generate a template file using Excel with that number of sheets. Then, once
you have built the workbook using POI, you can simply delete the unused
sheets.
This does not solve the question of why the cloneSheet() method is failing
but it could at least offer a workaround and allow the developers time to
address the particular issue.
Yours
Mark B
stephen.friedrich wrote:
>
> 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) {
> }
> }
> }
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
--
View this message in context: http://old.nabble.com/Bug-when-adding-picture-to-cloned-sheet-%21-tp27178667p27691502.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org