You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Srinadh Karumuri <sk...@bbn.com> on 2004/03/04 16:37:00 UTC
RE: Dynamic number of rows selection for chart in Excel
Simon,
The solution to this issue is within Excel. Nothing to do with Java/POI. We
just have to create a chart in Excel which will accept the new rows you add
at the end automatically.
Here are the steps to create the sample:
- Creat a test data with 'Alpha' on one column and 'Numeric' on the second:
Alpha Numeric
A 1
B 2
C 3
D 4
- Create a 'line' graph with these values.
- Try adding another row:
E 5
your chart will not include this row.
Steps for naming the Range:
- Select A to D and press Insert->Name->Create->'Check only Top Row'->OK
This will create a named range called 'Alpha'
- Select 1 to 4 and press Insert->Name->Create->'Check only Top Row'->OK
This will create a named range called 'Numeric'
- Now right click on your chart->Source Data->'Series Tab'
Change all the hard coded ranges to the variable names.
As an alternative if you name the range first you can set the 'Series'
while creating the chart for the first time as well. I just did it in two
steps to demonstrate the static and dynamic behavior.
Hope it helps.
Sri
PS: I am attaching the Excel sample. But this email should be descriptive
enough to create it yourself.
At 04:24 AM 3/4/2004, Simon Niederberger wrote:
>Hi Sri
>
> >From the POI mail list I saw that you found a good solution to change
>the source data range for charts using POI. I'm trying to do the same
>and have looked into the named ranges, am however currently stumbling
>over the Excel 2000 "feature" of replacing the name with the current
>range of the named range.
>
>If you could send me a copy of your "test" Excel sheet along with the
>crucial lines of java code I'd be immensly grateful. (I don't really
>understand your solution from the two formulas posted in the mail
>below.)
>
>Thanks
>Simon
>
>-----Original Message-----
>From: Srinadh Karumuri [mailto:skarumur@bbn.com]
>Sent: Freitag, 27. Februar 2004 13:57
>To: POI Users List
>Subject: Re: Dynamic number of rows selection for chart in Excel
>
>
>Thanks Kais,
>
>The named range with functions in it worked.
>Alpha =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
>Numeric =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
>
>
>Thanks Glen,
>Hmmm! I do have to get my feet wet. I will try the VB next time.
>
>Sri
>
>Glen Stampoultzis wrote:
>
> >
> > Perhaps you can use a bit of VB script to update the range.
> >
> > Alternatively you can try hook directly into the low level records and
> > build the support yourself.
> >
> > Regards,
> >
> > Glen
> >
> > At 09:48 AM 27/02/2004, Srinadh Karumuri wrote:
> >
> >> I tried 'named range' but it didn't work either.
> >> If I select a larger range (leaving empty rows in the bottom) for the
> >> name then the chart is showing the empty area as well. Which is not
> >> acceptable.
> >>
> >> I also observed that named range behaves just like regular range. In
> >> fact Excel 2000 automatically changes the Name to the range in the
> >> SourceData...->DataRange.
> >>
> >>
> >> I am attaching a sample excel file.
> >>
> >> Thank you in advance,
> >> Sri
> >> At 11:42 AM 2/26/2004, you wrote:
> >>
> >>> Have you considered using a named range for the chart data source?
> >>>
> >>> -- Kais
> >>
> >>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> >> For additional commands, e-mail: poi-user-help@jakarta.apache.org
> >
> >
> >
> > Glen Stampoultzis
> > gstamp@iinet.net.au
> > http://members.iinet.net.au/~gstamp/glen/
> >
RE: Dynamic number of rows selection for chart in Excel
Posted by Srinadh Karumuri <sk...@bbn.com>.
Sorry I missed the crucial part:
As soon as you complete creating the Named ranges and before you set the
'Source data ...' please redefine the names as below:
- Press Insert->Name->Define->Click on 'Alpha'->Change the value for
'Refers to' as below:
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
- Press Insert->Name->Define->Click on 'Numeric'->Change the value for
'Refers to' as below:
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
Thank you,
Sri
At 10:37 AM 3/4/2004, Srinadh Karumuri wrote:
>Simon,
>
>The solution to this issue is within Excel. Nothing to do with Java/POI.
>We just have to create a chart in Excel which will accept the new rows you
>add at the end automatically.
>
>Here are the steps to create the sample:
>- Creat a test data with 'Alpha' on one column and 'Numeric' on the second:
>Alpha Numeric
> A 1
> B 2
> C 3
> D 4
>- Create a 'line' graph with these values.
>- Try adding another row:
> E 5
> your chart will not include this row.
>
>Steps for naming the Range:
>- Select A to D and press Insert->Name->Create->'Check only Top Row'->OK
> This will create a named range called 'Alpha'
>- Select 1 to 4 and press Insert->Name->Create->'Check only Top Row'->OK
> This will create a named range called 'Numeric'
>- Now right click on your chart->Source Data->'Series Tab'
> Change all the hard coded ranges to the variable names.
>
>As an alternative if you name the range first you can set the 'Series'
>while creating the chart for the first time as well. I just did it in two
>steps to demonstrate the static and dynamic behavior.
>
>Hope it helps.
>Sri
>PS: I am attaching the Excel sample. But this email should be descriptive
>enough to create it yourself.
>
>At 04:24 AM 3/4/2004, Simon Niederberger wrote:
>>Hi Sri
>>
>> >From the POI mail list I saw that you found a good solution to change
>>the source data range for charts using POI. I'm trying to do the same
>>and have looked into the named ranges, am however currently stumbling
>>over the Excel 2000 "feature" of replacing the name with the current
>>range of the named range.
>>
>>If you could send me a copy of your "test" Excel sheet along with the
>>crucial lines of java code I'd be immensly grateful. (I don't really
>>understand your solution from the two formulas posted in the mail
>>below.)
>>
>>Thanks
>>Simon
>>
>>-----Original Message-----
>>From: Srinadh Karumuri [mailto:skarumur@bbn.com]
>>Sent: Freitag, 27. Februar 2004 13:57
>>To: POI Users List
>>Subject: Re: Dynamic number of rows selection for chart in Excel
>>
>>
>>Thanks Kais,
>>
>>The named range with functions in it worked.
>>Alpha =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
>>Numeric =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
>>
>>
>>Thanks Glen,
>>Hmmm! I do have to get my feet wet. I will try the VB next time.
>>
>>Sri
>>
>>Glen Stampoultzis wrote:
>>
>> >
>> > Perhaps you can use a bit of VB script to update the range.
>> >
>> > Alternatively you can try hook directly into the low level records and
>> > build the support yourself.
>> >
>> > Regards,
>> >
>> > Glen
>> >
>> > At 09:48 AM 27/02/2004, Srinadh Karumuri wrote:
>> >
>> >> I tried 'named range' but it didn't work either.
>> >> If I select a larger range (leaving empty rows in the bottom) for the
>> >> name then the chart is showing the empty area as well. Which is not
>> >> acceptable.
>> >>
>> >> I also observed that named range behaves just like regular range. In
>> >> fact Excel 2000 automatically changes the Name to the range in the
>> >> SourceData...->DataRange.
>> >>
>> >>
>> >> I am attaching a sample excel file.
>> >>
>> >> Thank you in advance,
>> >> Sri
>> >> At 11:42 AM 2/26/2004, you wrote:
>> >>
>> >>> Have you considered using a named range for the chart data source?
>> >>>
>> >>> -- Kais
>> >>
>> >>
>> >>
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> >> For additional commands, e-mail: poi-user-help@jakarta.apache.org
>> >
>> >
>> >
>> > Glen Stampoultzis
>> > gstamp@iinet.net.au
>> > http://members.iinet.net.au/~gstamp/glen/
>> >
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: poi-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org