You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by krishnanand thiyadath <at...@gmail.com> on 2008/09/09 21:52:52 UTC
regarding adding comments
Hi Pierre,
I have a excel file which is created using MS Excel application. I am trying
to read and do some modification in this file using POI. I have facing some
problem in that.
I am using the following code, to add comments to a already existing cell in
a sheet.
In the same sheet, i also have a cell which acts as a drop down ( I mean a
cell with data validation type list and referring a named range).
File f = new File(filePath);
FileInputStream fis = new FileInputStream(f);
POIFSFileSystem fs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFCell cell = sheet.getRow(1).getCell(1);
HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0,
(short)1, 2, (short) 2, 4));
comment.setString(new HSSFRichTextString("Comments Added"));
cell.setCellComment(comment);
FileOutputStream fos = new FileOutputStream(new
File("D://temp//test.xls"));
wb.write(fos);
fos.close();
Finally after writing the file to a new location, when i open the file and
see, the data in the drop down is lost and i am not getting the drop down
arrow also.
Can you please help me on this.
If you need any information, please let me know.
--
with thanks,
krishnanand.
Editing Text Boxes
Posted by la...@videotron.ca.
Hi All,
I have a workbook with a TextBox that I would like to edit. Any changes that I make to the TextObjectRecord using its setStr() call results in an unreadable excel file.
Is there an easier way to edit TextBoxes that I have not found yet?
Thanks in advance,
Laird
Re: Modifying Chart Titles
Posted by "Laird A. Trimble" <la...@videotron.ca>.
Nick,
Thank you for your quick response. I'll figure out how to use the
bugzilla system and create a new bug (with file) tomorrow.
Laird
On 10-Sep-08, at 11:43 AM, Nick Burch wrote:
> On Tue, 9 Sep 2008, laird_trimble@videotron.ca wrote:
>> SeriesTextRecords have a byte text length field that limits the
>> title text length to 256 characters. I presume that this is why
>> 3.1 fails to read in files containing charts with long titles
>> whereas the data length of the UnknownRecord is not limited and
>> 3.0.2 had no problems.
>
> POI 3.1 has a little bit more low level charting support.
>
> Page 398 points at page 850 of the excel spec, which clearly states
> that the SeriesTextRecord is made up of a single byte length, some
> flags, and then the text.
>
>> Since excel allows for chart titles longer than 256 characters, I
>> would presume the 256 character limit imposed by the
>> SeriesTextRecord is a bug. Is this the case? Is there a work
>> around for this problem?
>
> Can you open a new bug in bugzilla, and upload a file that has a
> longer title than 256 bytes? That way we can see what microsoft do
> to get around their rather silly restriction of only using 1 byte
> for the length
>
> Nick
> ---------------------------------------------------------------------
> 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: Modifying Chart Titles
Posted by "Laird A. Trimble" <la...@videotron.ca>.
Nick,
After careful examination of the problem in preparation for submitting
the bug report I have found out two things. The first is that I
cannot count. The second is that my inability to count makes the
problem even worse.
First of all, you are correct in that Excel limits titles to 256
characters. The problem is that POI 3.1 fails to read in excel files
containing charts that have titles with more than 127 characters. I
have filed this as a bug report (#45784).
RecordInputStream.readUnicodeLEString throws an
IllegalArgumentException: Illegal length - asked for -16 but only 130
left. It looks like an issue of using a Java byte to describe and
unsigned 8 bit number.
Laird
On 10-Sep-08, at 11:43 AM, Nick Burch wrote:
> On Tue, 9 Sep 2008, laird_trimble@videotron.ca wrote:
>> SeriesTextRecords have a byte text length field that limits the
>> title text length to 256 characters. I presume that this is why
>> 3.1 fails to read in files containing charts with long titles
>> whereas the data length of the UnknownRecord is not limited and
>> 3.0.2 had no problems.
>
> POI 3.1 has a little bit more low level charting support.
>
> Page 398 points at page 850 of the excel spec, which clearly states
> that the SeriesTextRecord is made up of a single byte length, some
> flags, and then the text.
>
>> Since excel allows for chart titles longer than 256 characters, I
>> would presume the 256 character limit imposed by the
>> SeriesTextRecord is a bug. Is this the case? Is there a work
>> around for this problem?
>
> Can you open a new bug in bugzilla, and upload a file that has a
> longer title than 256 bytes? That way we can see what microsoft do
> to get around their rather silly restriction of only using 1 byte
> for the length
>
> Nick
> ---------------------------------------------------------------------
> 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: Modifying Chart Titles
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 9 Sep 2008, laird_trimble@videotron.ca wrote:
> SeriesTextRecords have a byte text length field that limits the title
> text length to 256 characters. I presume that this is why 3.1 fails to
> read in files containing charts with long titles whereas the data length
> of the UnknownRecord is not limited and 3.0.2 had no problems.
POI 3.1 has a little bit more low level charting support.
Page 398 points at page 850 of the excel spec, which clearly states that
the SeriesTextRecord is made up of a single byte length, some flags, and
then the text.
> Since excel allows for chart titles longer than 256 characters, I would
> presume the 256 character limit imposed by the SeriesTextRecord is a
> bug. Is this the case? Is there a work around for this problem?
Can you open a new bug in bugzilla, and upload a file that has a longer
title than 256 bytes? That way we can see what microsoft do to get around
their rather silly restriction of only using 1 byte for the length
Nick
Modifying Chart Titles
Posted by la...@videotron.ca.
Hi All,
Under 3.0.2 I was able to read and write excel files that had charts with long titles without problem. POI 3.1 fails to read excel files that contain charts with titles longer than 256 characters. With a little digging I found that in 3.1 chart titles are being read as SeriesTextRecords whereas in 3.0.2 they were being read as UnknownRecords.
SeriesTextRecords have a byte text length field that limits the title text length to 256 characters. I presume that this is why 3.1 fails to read in files containing charts with long titles whereas the data length of the UnknownRecord is not limited and 3.0.2 had no problems.
Since excel allows for chart titles longer than 256 characters, I would presume the 256 character limit imposed by the SeriesTextRecord is a bug. Is this the case? Is there a work around for this problem?
Thanks in advance for your help.
Laird
Re: regarding adding comments
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 10 Sep 2008, krishnanand thiyadath wrote:
> I am using the following code, to add comments to a already existing cell in
> a sheet.
> In the same sheet, i also have a cell which acts as a drop down ( I mean a
> cell with data validation type list and referring a named range).
I have a feeling that some of the data validation stuff using the sheet
drawing patriarch. In order to add comments, you have to zap the existing
drawing patriarch, then start with a fresh one, because poi doesn't
support parsing the icky mess which is excel drawing support in existing
files.
I have a feeling you'll need to add all your comments and validation stuff
in one go
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: regarding adding comments
Posted by krishnanand thiyadath <at...@gmail.com>.
Ok No Problem, Thanks Pierre.
On Wed, Sep 10, 2008 at 1:39 AM, Pierre Lavignotte <
pierre.lavignotte@gmail.com> wrote:
> Sorry but I can't help you on that...
> I just know (a little) the DataValidation API because I've face the
> same problem than you few weeks ago.
>
> On Tue, Sep 9, 2008 at 9:52 PM, krishnanand thiyadath
> <at...@gmail.com> wrote:
> > Hi Pierre,
> >
> > I have a excel file which is created using MS Excel application. I am
> trying
> > to read and do some modification in this file using POI. I have facing
> some
> > problem in that.
> >
> > I am using the following code, to add comments to a already existing cell
> in
> > a sheet.
> > In the same sheet, i also have a cell which acts as a drop down ( I mean
> a
> > cell with data validation type list and referring a named range).
> >
> > File f = new File(filePath);
> > FileInputStream fis = new FileInputStream(f);
> > POIFSFileSystem fs = new POIFSFileSystem(fis);
> > HSSFWorkbook wb = new HSSFWorkbook(fs);
> >
> > HSSFSheet sheet = wb.getSheetAt(0);
> > HSSFPatriarch patr = sheet.createDrawingPatriarch();
> > HSSFCell cell = sheet.getRow(1).getCell(1);
> > HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0,
> 0,
> > (short)1, 2, (short) 2, 4));
> > comment.setString(new HSSFRichTextString("Comments Added"));
> > cell.setCellComment(comment);
> >
> > FileOutputStream fos = new FileOutputStream(new
> > File("D://temp//test.xls"));
> > wb.write(fos);
> > fos.close();
> >
> > Finally after writing the file to a new location, when i open the file
> and
> > see, the data in the drop down is lost and i am not getting the drop down
> > arrow also.
> >
> > Can you please help me on this.
> >
> > If you need any information, please let me know.
> >
> > --
> > with thanks,
> > krishnanand.
> >
>
>
>
> --
> Cordialement,
> Pierre Lavignotte
> Ingénieur Conception & Développement
> http://pierre.lavignotte.googlepages.com
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
with thanks,
krishnanand.
Re: regarding adding comments
Posted by Pierre Lavignotte <pi...@gmail.com>.
Sorry but I can't help you on that...
I just know (a little) the DataValidation API because I've face the
same problem than you few weeks ago.
On Tue, Sep 9, 2008 at 9:52 PM, krishnanand thiyadath
<at...@gmail.com> wrote:
> Hi Pierre,
>
> I have a excel file which is created using MS Excel application. I am trying
> to read and do some modification in this file using POI. I have facing some
> problem in that.
>
> I am using the following code, to add comments to a already existing cell in
> a sheet.
> In the same sheet, i also have a cell which acts as a drop down ( I mean a
> cell with data validation type list and referring a named range).
>
> File f = new File(filePath);
> FileInputStream fis = new FileInputStream(f);
> POIFSFileSystem fs = new POIFSFileSystem(fis);
> HSSFWorkbook wb = new HSSFWorkbook(fs);
>
> HSSFSheet sheet = wb.getSheetAt(0);
> HSSFPatriarch patr = sheet.createDrawingPatriarch();
> HSSFCell cell = sheet.getRow(1).getCell(1);
> HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0,
> (short)1, 2, (short) 2, 4));
> comment.setString(new HSSFRichTextString("Comments Added"));
> cell.setCellComment(comment);
>
> FileOutputStream fos = new FileOutputStream(new
> File("D://temp//test.xls"));
> wb.write(fos);
> fos.close();
>
> Finally after writing the file to a new location, when i open the file and
> see, the data in the drop down is lost and i am not getting the drop down
> arrow also.
>
> Can you please help me on this.
>
> If you need any information, please let me know.
>
> --
> with thanks,
> krishnanand.
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org