You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Lakshmi Ramakrishnan <la...@gmail.com> on 2012/10/05 03:47:12 UTC
Re: Creating charts from a 'template'
Ive been in a similar situation.. But in my case, we had a template prepared by some of my other team-mates, more familiar with excel. The template file was in format . It basically expected raw data in a specific sheet and all formulas are based off of this "source" sheet.
I just took this file, created an instance of workbook using xssfworkbook - searched for this source sheet in this workbook and simply created rows and cols in this sheet, populated data.
Remember to set workbook's recalculate-on-open (or something along those lines) to true - it won't work without this.
Some issues - cant reuse the instance.. Need to create new instance every time from the template file. This operation is the biggest time and resource sink!
Hope this helps!
Lakshmi Ramakrishnan
On Oct 4, 2012, at 8:28 PM, Mathew Johnston <ma...@telus.com> wrote:
> Hi,
>
> I know (I think) that POI doesn't yet offer an XSSF API for chart
> manipulation, but, I figure that I don't really need one. Instead,
> I'm wondering if this would be a reasonable approach:
>
> 1. Using Excel, create a workbook with 'template' charts
> 2. Open that workbook using POI
> 3. For each chart I want to create
> 3. a. Clone the relevant template into a new sheet
> 3. b. Make note of the corresponding new Chart XML file name/number
> 3. c. Write series data table into additional new sheets
> 3. d. I can then open the XSLX ZIP and directly (programatically) edit
> the chart XML file to update series references to point to the
> new data sheet.
> 4. User opens the workbook and it automatically updates the chart data
> (I won't bother to edit cached values in the XML)
>
> Does this seem like a viable approach?
>
> I'm totally new to POI, so I'm fairly unfamiliar with the API. My
> strategy depends on being able to blindly clone a chart object and
> any linked drawings (some of my charts have added connector lines
> drawn in them to provide a diagonal x=y guideline).
>
> I don't see an obvious way to duplicate a shape (I'm assuming I'm
> setting out to copy an XSSFShape?). How does one go about that?
> After that I'd need to identify the corresponding new Chart#.xml in
> the ZIP - possible?
>
> Alternative approaches?
>
> Thanks very much for any advice you can offer!
>
> Mat
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Creating charts from a 'template'
Posted by Lakshmi Ramakrishnan <la...@gmail.com>.
I meant to say format of Template was .xlsm
Lakshmi
Sent from my iPhone
On Oct 4, 2012, at 9:47 PM, Lakshmi Ramakrishnan <la...@gmail.com> wrote:
> Ive been in a similar situation.. But in my case, we had a template prepared by some of my other team-mates, more familiar with excel. The template file was in format . It basically expected raw data in a specific sheet and all formulas are based off of this "source" sheet.
>
> I just took this file, created an instance of workbook using xssfworkbook - searched for this source sheet in this workbook and simply created rows and cols in this sheet, populated data.
>
> Remember to set workbook's recalculate-on-open (or something along those lines) to true - it won't work without this.
>
> Some issues - cant reuse the instance.. Need to create new instance every time from the template file. This operation is the biggest time and resource sink!
>
> Hope this helps!
>
> Lakshmi Ramakrishnan
>
> On Oct 4, 2012, at 8:28 PM, Mathew Johnston <ma...@telus.com> wrote:
>
>> Hi,
>>
>> I know (I think) that POI doesn't yet offer an XSSF API for chart
>> manipulation, but, I figure that I don't really need one. Instead,
>> I'm wondering if this would be a reasonable approach:
>>
>> 1. Using Excel, create a workbook with 'template' charts
>> 2. Open that workbook using POI
>> 3. For each chart I want to create
>> 3. a. Clone the relevant template into a new sheet
>> 3. b. Make note of the corresponding new Chart XML file name/number
>> 3. c. Write series data table into additional new sheets
>> 3. d. I can then open the XSLX ZIP and directly (programatically) edit
>> the chart XML file to update series references to point to the
>> new data sheet.
>> 4. User opens the workbook and it automatically updates the chart data
>> (I won't bother to edit cached values in the XML)
>>
>> Does this seem like a viable approach?
>>
>> I'm totally new to POI, so I'm fairly unfamiliar with the API. My
>> strategy depends on being able to blindly clone a chart object and
>> any linked drawings (some of my charts have added connector lines
>> drawn in them to provide a diagonal x=y guideline).
>>
>> I don't see an obvious way to duplicate a shape (I'm assuming I'm
>> setting out to copy an XSSFShape?). How does one go about that?
>> After that I'd need to identify the corresponding new Chart#.xml in
>> the ZIP - possible?
>>
>> Alternative approaches?
>>
>> Thanks very much for any advice you can offer!
>>
>> Mat
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org