poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: SV: Adding images to Excel sheet with existing Chart - is this possible?
Date Mon, 24 Aug 2009 08:09:20 GMT

Must admit that I used to use JExcelAPI myself before moving over to POI and
liked it a lot. There are things it does that POI does not and it takes a
slightly different approach to that adopted by POI. Must admit that I cannot
remember how well it works with images/charts as it is such a long time
since I used it and I never needed to manipulate images at that time.

You do have other options of course, and they have varying dgrees of
attractivness, success;

* Aspose have a commercial product that offers to manipulate Office files -
including Excel of course. I have never used it so canno0t speak to it's
capabilities and of course, you would have to pay for it.
* OpenOffice exposes an API that allows you to work with those files it has
filters for. Wjilst I have used it for simple tasks - search and replace for
example in Word documents - I have never built large, complex workbooks with
it and so, again, cannot really speak to it's suitability. There are other
issues with this aproach however indlucing speed of execution, the fact that
the connection to the OpenOffice application can be dropped so adding to the
complexity of your client code.
* OLE. In many ways this is the 'best' option as you are manipulating Excel
directly; therefore, anything you can do using Excel yourself can be
automated using OLE. However, this technique is limited in applicability to
stand alone PCs running Windows in my experience. Additionally, it is quite
slow and Microsoft themselves explicitly advise against adopting the
strategy for a number of reasons - chief amongst them being - IMO - an
inability to trap and handle errors (Excel simply pops open a dialog box and
waits for human intervention).

Good luck. Sorry I could not help but if I find anything out, I will post to
the list.


Mark B

Erik Lundström wrote:
> Hi Mark,
> Thanks for your effort! 
> Yes, I will indeed post a follow-up question on the dev forum regarding
> this.
> Another thing I was considering trying out is to have the chart on another
> sheet initially in the Excel template, i.e. writing all data and images to
> the sheet, and afterwards "moving" the chart to its correct position (from
> some irrelevant sheet to the correct one, where the images are). But I do
> not know if this is possible at all (I have zero experience regarding
> working with charts in Excel POI). I'll let you know if I get around to
> try this out.
> In parallel, I will also investigate what the other Excel open source
> project I know of has to offer regarding this problem: JExcelAPI.
> Best
> Erik
> -----Ursprungligt meddelande-----
> Från: MSB [mailto:markbrdsly@tiscali.co.uk] 
> Skickat: den 23 augusti 2009 13:12
> Till: user@poi.apache.org
> Ämne: Re: Adding images to Excel sheet with existing Chart - is this
> possible?
> Had the chance to play around with some code this morning and I know why
> the
> images are missing when you try to add them to a sheet that contains a
> chart
> - the required records are simply not created. I also think that I know
> why;
> I think that the drawing records are parsed when the method is called and
> this both corrupts the chart data and the drawing aggregate records
> themselves.
> This is only supposition on my part but I think it means that the API may
> not be able to handle inserting images into sheets that already contain
> charts or other complex drawing objects. However, I think you would be
> wise
> to post a follow-up question to the developers forum as I am not able to
> provide a definitive answer myself.
> Yours
> Mark B
> Erik Lundström wrote:
>> Hello,
>> I'm struggling with a case where I have to write images to an Excel
>> Workbook, when there are chart(s) present in the Excel template file.
>> (I have seen another message here on the mailing list labeled "Adding
>> Image in Sheet while Diagram already exists in other does not work". I
>> don't know if my problem is another form of the same problem; I couldn't
>> manage to download the code from that author. I am sorry if I'm repeating
>> the same question again, but I still haven't found a solution or
>> workaround to my problem, whereas I think the other poster did).
>> Anyway, my case is this (download link to standalone example including
>> images and Excel templates is below):
>> I have an Excel template, consisting of two sheets. There is one chart on
>> Sheet0, and one on Sheet1.
>> There is data (numerical values, texts) being written to various cells on
>> these two sheets. The num cells are the base for the charts.
>> On Sheet1, I want to write four images. This is where my problem
>> surfaces.
>> Using the intended Excel template, the image writing using POI screws up
>> Sheet1 completely.
>> Using various versions of my initial Excel template (basically stripping
>> it down), I came to the depressing conclusion that there is a Catch22
>> here: Charts must be present from scratch in the Excel template file (as
>> by the given limitations in the POI HSSF+XSSF project), but writing
>> images
>> to a Workbook where there are charts just isn't possible. This conclusion
>> was made when I removed the chart from Sheet1 (the sheet where I want to
>> write images) - the images still don't appear.
>> But when starting out with a completely blank Excel file, and just adding
>> a chart to Sheet0, I do manage to write images to Sheet1. Well, the
>> "real"
>> Excel template might of course have references and bindings that I'm not
>> aware of - I am neither an Excel expert nor the author of that Excel
>> file.
>> So it seems that charts can/might be present on OTHER sheets than the one
>> where images are written? At least this is my humble suspicion after
>> struggling with this for some time.
>> I would be very grateful if somebody here could clarify the image/chart
>> problem! Can one make them co-exist, or must I use an external workaround
>> (like having a macro reading the images afterwards to the Sheet).
>> I have been using POI 3.5 Beta6, and my "Excel viewer/template editor"
>> was
>> MS Excel 2007 (on Vista).
>> Source is here (standalone example, images and the various Excel
>> templates
>> I have tried):
>> http://uploading.com/files/HOJ1VIKT/POITest.zip.html
>> Thanks
>> Erik
> -- 
> View this message in context:
> http://www.nabble.com/Adding-images-to-Excel-sheet-with-existing-Chart---is-this-possible--tp25076212p25102278.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

View this message in context: http://www.nabble.com/Adding-images-to-Excel-sheet-with-existing-Chart---is-this-possible--tp25076212p25112101.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

View raw message