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 15:37:00 GMT
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/
> >


Mime
View raw message