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/02/26 17:31:50 UTC
Dynamic number of rows selection for chart in Excel
First things first.
I am brand new to POI and it's a wonderful job you all did.
I am using an Excel file as template and using POI to modify my data and in
turn the chart. It worked perfect the first time.
Surprisingly the simple documentation on the POI web site was good enough
to achieve this task.
Now I was wondering if I could make this chart select the dynamic number of
rows. I tried the following and it didn't work:
1. Create Excel file and hide few rows at the end filled with '0' values.
Create a chart using non-hidden and hidden rows. Excel will show only the
visible data in the chart. Reset the values of hidden rows using POI. But,
was unable to make these rows visible from POI.
2. Create Excel file with data and chart. Insert rows in the middle of the
data using POI. But, excel does not extend the selection as this static.
3. Keep multiple charts as hidden sheets in Excel file. Delete the ones you
don't need and un hide the ones you want. This is not possible from POI
(either bugs waiting for 3.0 are lack of features).
Please do not consider this as criticism. I am just trying to find a solution.
Thank you,
Sri
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org
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
RE: Dynamic number of rows selection for chart in Excel
Posted by Srinadh Karumuri <sk...@bbn.com>.
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 Kais Dukes <kd...@kaisdukes.com>.
Glad to have been of assistance :-)
-- Kais
-----Original Message-----
From: Srinadh Karumuri [mailto:skarumur@bbn.com]
Sent: 27 February 2004 12: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
Re: Dynamic number of rows selection for chart in Excel
Posted by Srinadh Karumuri <sk...@bbn.com>.
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 Glen Stampoultzis <gs...@iinet.net.au>.
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>.
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
RE: Dynamic number of rows selection for chart in Excel
Posted by Kais Dukes <kd...@kaisdukes.com>.
Have you considered using a named range for the chart data source?
-- Kais
-----Original Message-----
From: Srinadh Karumuri [mailto:skarumur@bbn.com]
Sent: 26 February 2004 16:32
To: poi-user@jakarta.apache.org
Subject: Dynamic number of rows selection for chart in Excel
First things first.
I am brand new to POI and it's a wonderful job you all did.
I am using an Excel file as template and using POI to modify my data and in
turn the chart. It worked perfect the first time.
Surprisingly the simple documentation on the POI web site was good enough
to achieve this task.
Now I was wondering if I could make this chart select the dynamic number of
rows. I tried the following and it didn't work:
1. Create Excel file and hide few rows at the end filled with '0' values.
Create a chart using non-hidden and hidden rows. Excel will show only the
visible data in the chart. Reset the values of hidden rows using POI. But,
was unable to make these rows visible from POI.
2. Create Excel file with data and chart. Insert rows in the middle of the
data using POI. But, excel does not extend the selection as this static.
3. Keep multiple charts as hidden sheets in Excel file. Delete the ones you
don't need and un hide the ones you want. This is not possible from POI
(either bugs waiting for 3.0 are lack of features).
Please do not consider this as criticism. I am just trying to find a
solution.
Thank you,
Sri
---------------------------------------------------------------------
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