poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Srinadh Karumuri <skaru...@bbn.com>
Subject RE: Dynamic number of rows selection for chart in Excel
Date Thu, 04 Mar 2004 18:10:08 GMT
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


Mime
View raw message