Dynamic number of rows selection for chart in Excel

[Srinadh Karumuri]

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

[Kais Dukes]

Have you considered using a named range for the chart data source?

-- Kais

-----Original Message-----
From: Srinadh Karumuri [mailto:***@bbn.com]
Sent: 26 February 2004 16:32
To: poi-***@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-***@jakarta.apache.org
For additional commands, e-mail: poi-user-***@jakarta.apache.org

[Srinadh Karumuri]

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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

[Glen Stampoultzis]

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

Post by Srinadh Karumuri
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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

---------------------------------------------------------------------

Glen Stampoultzis
***@iinet.net.au
http://members.iinet.net.au/~gstamp/glen/

[Srinadh Karumuri]

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

Post by Glen Stampoultzis
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

Post by Srinadh Karumuri
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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

---------------------------------------------------------------------

Glen Stampoultzis
http://members.iinet.net.au/~gstamp/glen/

[Kais Dukes]

Glad to have been of assistance :-)

-- Kais

-----Original Message-----
From: Srinadh Karumuri [mailto:***@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

Post by Glen Stampoultzis
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

Post by Srinadh Karumuri
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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

---------------------------------------------------------------------

Glen Stampoultzis
http://members.iinet.net.au/~gstamp/glen/

[Srinadh Karumuri]

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.

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-----
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

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

Post by Srinadh Karumuri
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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

---------------------------------------------------------------------

Glen Stampoultzis
http://members.iinet.net.au/~gstamp/glen/

[Srinadh Karumuri]

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

Post by Srinadh Karumuri
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.
Alpha Numeric
A 1
B 2
C 3
D 4
- Create a 'line' graph with these values.
E 5
your chart will not include this row.
- 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.

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-----
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

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

Post by Srinadh Karumuri
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

Post by Kais Dukes
Have you considered using a named range for the chart data source?
-- Kais

---------------------------------------------------------------------

Glen Stampoultzis
http://members.iinet.net.au/~gstamp/glen/

---------------------------------------------------------------------