You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hepzibah Rajulah <he...@yahoo.com> on 2008/04/02 08:24:38 UTC
changing Graph Title through POI
Hi all ,
Is it possible to modify the graph title through POI
Regards
HR
Re: changing Graph Title through POI
Posted by Yegor Kozlov <ye...@dinom.ru>.
Not yet.
Yegor
> Hi all ,
> Is it possible to modify the graph title through POI
> Regards
> HR
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: changing Graph Title through POI
Posted by Princess <he...@yahoo.com>.
thanks Russ..
--HR
On Wed, Apr 2, 2008 at 3:40 PM, <rr...@us.imshealth.com> wrote:
> In our case, we have a template with the Graph on it. You can use Named
> Ranges to change the data that is plotted on the Graph. To change the
> title, you'll need to get the "SeriesTextRecord". If your Chart Title
> is formatted (e.g. Bold, Underline, etc), there is a second record type
> (ChartTitleFormatRecord) that contains the Font and character
> positions(similar to the Rich Text Format runs).
>
> In my sample below, I have a workbook with a single sheet with a single
> graph but the principle is the same. In this example, it's replacing
> the word "Foo" with the 3rd parameter and the word "Bar" in the title
> with the 4th parameter. Parameters 1 and 2 are input and output file
> names.
>
> The POI modification is to expose the set of records from the Sheet
> (ws.getRecords()). There may be a better way to do this, I was just
> trying to demonstrate that changing the title was possible. Next, loop
> through the records to find the "SeriesTextRecord" and the
> ChartTitleFormatRecord. The ChartTitleFormatRecord will only exist if
> the title is formatted. The chart title format is a series of position
> / font index pairs, so if you change the text, you have to change the
> positions or the formatting will be applied to the wrong characters.
> That's what "modifyFormatRun" is doing.
>
> -Russ
>
> public static void main(String[] args) {
>
> try {
>
> InputStream myxls = new FileInputStream(args[0]);
>
> PMHSSFWorkbook wb = new PMHSSFWorkbook(myxls);
>
> if (args.length >= 4) {
>
> HSSFSheet hssfws = wb.getSheetAt(0);
>
> Sheet ws = hssfws.getSheet();
>
> records = ws.getRecords();
>
> //find the charttitleformat record and the seriestext
> records
>
> ChartTitleFormatRecord ctfr = null;
>
> SeriesTextRecord str = null;
>
> for(int k=0;k<records.size();k++) {
>
> if (records.get(k).getClass() ==
> ChartTitleFormatRecord.class) {
>
> ctfr = (ChartTitleFormatRecord)records.get(k);
>
> } else if (records.get(k).getClass() ==
> SeriesTextRecord.class) {
>
> str = (SeriesTextRecord)records.get(k);
>
> } else {
>
> }
>
> }
>
> if ((str != null) && (ctfr != null)) {
>
> String title = str.getText();
>
> short oldPos = (short)title.indexOf("Foo");
>
> ctfr.modifyFormatRun(oldPos,(short)(args[2].length()));
>
> title = title.replaceFirst("Foo",args[2]);
>
> oldPos = (short)title.indexOf("Bar");
>
> ctfr.modifyFormatRun(oldPos,(short)(args[3].length()));
>
> title = title.replaceFirst("Bar",args[3]);
>
> str.setText(title);
>
> str.setTextLength((byte)title.length());
>
> }
>
> }
>
> if (args.length >= 2) {
>
> OutputStream outxls = new FileOutputStream(args[1]);
>
> wb.write(outxls);
>
> }
>
> } catch (FileNotFoundException fnfe){
>
> System.out.println(fnfe.getMessage());
>
> } catch (IOException ioe){
>
> System.out.println(ioe.getMessage());
>
> }
>
> }
>
>
> -----Original Message-----
> From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
> Behalf Of Hepzibah Rajulah
> Sent: Wednesday, April 02, 2008 7:40 AM
> To: POI Users List
> Subject: Re: changing Graph Title through POI
>
> Wow nice can you please give me the hacking stuff
>
> HR
>
> On Wed, Apr 2, 2008 at 1:26 PM, <rr...@us.imshealth.com> wrote:
>
> > We've used POI to change Excel Chart titles but it did involve a bit
> > of hacking to get at the lower-level record types.
> >
> > -Russ
> >
> > -----Original Message-----
> > From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
> > Behalf Of Hepzibah Rajulah
> > Sent: Wednesday, April 02, 2008 2:25 AM
> > To: user@poi.apache.org
> > Subject: changing Graph Title through POI
> >
> > Hi all ,
> >
> > Is it possible to modify the graph title through POI
> >
> > Regards
> >
> > HR
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional
>
> > commands, e-mail: user-help@poi.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
RE: changing Graph Title through POI
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 2 Apr 2008 rrogers@us.imshealth.com wrote:
> In our case, we have a template with the Graph on it. You can use Named
> Ranges to change the data that is plotted on the Graph. To change the
> title, you'll need to get the "SeriesTextRecord". If your Chart Title
> is formatted (e.g. Bold, Underline, etc), there is a second record type
> (ChartTitleFormatRecord) that contains the Font and character
> positions(similar to the Rich Text Format runs).
Thanks for finding this info. I've put some support into HSSFChart (in
scratchpad) to get and change chart and series titles. Hopefully this'll
be of use
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: changing Graph Title through POI
Posted by rr...@us.imshealth.com.
In our case, we have a template with the Graph on it. You can use Named
Ranges to change the data that is plotted on the Graph. To change the
title, you'll need to get the "SeriesTextRecord". If your Chart Title
is formatted (e.g. Bold, Underline, etc), there is a second record type
(ChartTitleFormatRecord) that contains the Font and character
positions(similar to the Rich Text Format runs).
In my sample below, I have a workbook with a single sheet with a single
graph but the principle is the same. In this example, it's replacing
the word "Foo" with the 3rd parameter and the word "Bar" in the title
with the 4th parameter. Parameters 1 and 2 are input and output file
names.
The POI modification is to expose the set of records from the Sheet
(ws.getRecords()). There may be a better way to do this, I was just
trying to demonstrate that changing the title was possible. Next, loop
through the records to find the "SeriesTextRecord" and the
ChartTitleFormatRecord. The ChartTitleFormatRecord will only exist if
the title is formatted. The chart title format is a series of position
/ font index pairs, so if you change the text, you have to change the
positions or the formatting will be applied to the wrong characters.
That's what "modifyFormatRun" is doing.
-Russ
public static void main(String[] args) {
try {
InputStream myxls = new FileInputStream(args[0]);
PMHSSFWorkbook wb = new PMHSSFWorkbook(myxls);
if (args.length >= 4) {
HSSFSheet hssfws = wb.getSheetAt(0);
Sheet ws = hssfws.getSheet();
records = ws.getRecords();
//find the charttitleformat record and the seriestext
records
ChartTitleFormatRecord ctfr = null;
SeriesTextRecord str = null;
for(int k=0;k<records.size();k++) {
if (records.get(k).getClass() ==
ChartTitleFormatRecord.class) {
ctfr = (ChartTitleFormatRecord)records.get(k);
} else if (records.get(k).getClass() ==
SeriesTextRecord.class) {
str = (SeriesTextRecord)records.get(k);
} else {
}
}
if ((str != null) && (ctfr != null)) {
String title = str.getText();
short oldPos = (short)title.indexOf("Foo");
ctfr.modifyFormatRun(oldPos,(short)(args[2].length()));
title = title.replaceFirst("Foo",args[2]);
oldPos = (short)title.indexOf("Bar");
ctfr.modifyFormatRun(oldPos,(short)(args[3].length()));
title = title.replaceFirst("Bar",args[3]);
str.setText(title);
str.setTextLength((byte)title.length());
}
}
if (args.length >= 2) {
OutputStream outxls = new FileOutputStream(args[1]);
wb.write(outxls);
}
} catch (FileNotFoundException fnfe){
System.out.println(fnfe.getMessage());
} catch (IOException ioe){
System.out.println(ioe.getMessage());
}
}
-----Original Message-----
From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
Behalf Of Hepzibah Rajulah
Sent: Wednesday, April 02, 2008 7:40 AM
To: POI Users List
Subject: Re: changing Graph Title through POI
Wow nice can you please give me the hacking stuff
HR
On Wed, Apr 2, 2008 at 1:26 PM, <rr...@us.imshealth.com> wrote:
> We've used POI to change Excel Chart titles but it did involve a bit
> of hacking to get at the lower-level record types.
>
> -Russ
>
> -----Original Message-----
> From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
> Behalf Of Hepzibah Rajulah
> Sent: Wednesday, April 02, 2008 2:25 AM
> To: user@poi.apache.org
> Subject: changing Graph Title through POI
>
> Hi all ,
>
> Is it possible to modify the graph title through POI
>
> Regards
>
> HR
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org For additional
> commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: changing Graph Title through POI
Posted by Hepzibah Rajulah <he...@yahoo.com>.
Wow nice can you please give me the hacking stuff
HR
On Wed, Apr 2, 2008 at 1:26 PM, <rr...@us.imshealth.com> wrote:
> We've used POI to change Excel Chart titles but it did involve a bit of
> hacking to get at the lower-level record types.
>
> -Russ
>
> -----Original Message-----
> From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
> Behalf Of Hepzibah Rajulah
> Sent: Wednesday, April 02, 2008 2:25 AM
> To: user@poi.apache.org
> Subject: changing Graph Title through POI
>
> Hi all ,
>
> Is it possible to modify the graph title through POI
>
> Regards
>
> HR
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
RE: changing Graph Title through POI
Posted by rr...@us.imshealth.com.
We've used POI to change Excel Chart titles but it did involve a bit of
hacking to get at the lower-level record types.
-Russ
-----Original Message-----
From: hepzibahr@googlemail.com [mailto:hepzibahr@googlemail.com] On
Behalf Of Hepzibah Rajulah
Sent: Wednesday, April 02, 2008 2:25 AM
To: user@poi.apache.org
Subject: changing Graph Title through POI
Hi all ,
Is it possible to modify the graph title through POI
Regards
HR
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org