You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Hannes Erven <ha...@erven.at> on 2015/09/03 14:42:59 UTC
How to find all CellComments in XLS[X]
Hi folks,
I need to scan all comments in a given XSLX file (preferably also XLS).
It seems that the only way to access comments is
Sheet.getCellComment(int row, int column); so I need to find all row and
cell numbers to iterate through them.
There is just one problem: if a comment is specified outside the range
of cells in a given row, getFirstCellNum/getLastCellNum only consider
"present" cells and will ignore comments outside that range.
Consider this simple sheet:
A1: String "Hi"
A2: no values, but a comment
A3: String "there"
For row indizes 0 and 2, getFirstCellNum and getLastCellNum report 0.
For row index 1, they report -1; but getCellComment(1,0) will return the
comment as expected.
Excel does create a physical row for rows with only a comment, and even
if there are no additional rows "behind", so it's just a matter of
detecting the right column ranges to query for a given row.
Anyways, I'd prefer having access to XSSFSheet's "sheetComments" object
(or similar) and iterate only through the existing comments, rather than
checking each cell's coordinates, wading through tons of cells just to
find a handful of comments.
Is there a solution to find all comments? Would it be safe to add public
access to CommentsTable and/or its commentRefs?
Thanks & best regards,
-hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to find all CellComments in XLS[X]
Posted by Dominik Stadler <do...@gmx.at>.
Ok, thanks, I will take a look.
Dominik.
On Fri, Sep 11, 2015 at 6:23 PM, Hannes Erven <ha...@erven.at> wrote:
> Hi Dominik,
>
>
>> can you put that into a bug so we can discuss in more detail there and
>>
>> have some references for changelogs et.al., I'd probably start with a
>> patch similar to what you already added here and only add more stuff
>> if there is more interest out there for something like that.
>
>
> For the list archives, bug created and patch attached:
>
> https://bz.apache.org/bugzilla/show_bug.cgi?id=58365
>
>
>
> Thanks for your consideration!
>
> Best regards,
>
> -hannes
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to find all CellComments in XLS[X]
Posted by Hannes Erven <ha...@erven.at>.
Hi Dominik,
> can you put that into a bug so we can discuss in more detail there and
> have some references for changelogs et.al., I'd probably start with a
> patch similar to what you already added here and only add more stuff
> if there is more interest out there for something like that.
For the list archives, bug created and patch attached:
https://bz.apache.org/bugzilla/show_bug.cgi?id=58365
Thanks for your consideration!
Best regards,
-hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to find all CellComments in XLS[X]
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
can you put that into a bug so we can discuss in more detail there and
have some references for changelogs et.al., I'd probably start with a
patch similar to what you already added here and only add more stuff
if there is more interest out there for something like that.
Dominik.
On Fri, Sep 4, 2015 at 12:01 AM, Hannes Erven <ha...@erven.at> wrote:
> Hi Mark and Dominik,
>
>
>> I did not actually try it, but you should be able to get the
>>
>> CommentsTable already now via something like the following:
>>
>> for(POIXMLDocumentPart p : sheet.getRelations()){
>> if(p instanceof CommentsTable) {
>> CommentsTable sheetComments = (CommentsTable)p;
>> ...
>> }
>> }
>
>
> Thank you very much for yor replies and suggestions. While the above does
> indeed do the trick, I feel it depends too heavily on POI internals
> (CTComments) to extract useful data.
>
> I took a shot at adding a public method to extract the comment locations,
> please see the attached patch.
>
>
> Would you consider adding that and/or an additional method to extract a
> Map<REF,Comment> (with REF being String or CellReference)?
> If yes, I'll happily put together a patch that supports all HSSF, XSSF and
> HXSSF.
> If no, I'll just stick with Dominik's suggestion.
>
>
> Thanks again,
> best regards
>
> -hannes
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to find all CellComments in XLS[X]
Posted by Hannes Erven <ha...@erven.at>.
Hi Mark and Dominik,
> I did not actually try it, but you should be able to get the
> CommentsTable already now via something like the following:
>
> for(POIXMLDocumentPart p : sheet.getRelations()){
> if(p instanceof CommentsTable) {
> CommentsTable sheetComments = (CommentsTable)p;
> ...
> }
> }
Thank you very much for yor replies and suggestions. While the above
does indeed do the trick, I feel it depends too heavily on POI internals
(CTComments) to extract useful data.
I took a shot at adding a public method to extract the comment
locations, please see the attached patch.
Would you consider adding that and/or an additional method to extract a
Map<REF,Comment> (with REF being String or CellReference)?
If yes, I'll happily put together a patch that supports all HSSF, XSSF
and HXSSF.
If no, I'll just stick with Dominik's suggestion.
Thanks again,
best regards
-hannes
Re: How to find all CellComments in XLS[X]
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
I did not actually try it, but you should be able to get the
CommentsTable already now via something like the following:
for(POIXMLDocumentPart p : sheet.getRelations()){
if(p instanceof CommentsTable) {
CommentsTable sheetComments = (CommentsTable)p;
...
}
}
Dominik.
On Thu, Sep 3, 2015 at 5:13 PM, Mark Beardsley <ma...@tiscali.co.uk> wrote:
> My only concern with allowing access to the CoomentsTable is the potential
> for drastically breaking the workbook file. With a reference to the
> CommentsTable in hand it would be possible to add new comments and remove
> existing ones and I am concerned that the logic is not in place to deal with
> all of the internal references necessary to link comments to rows etc.
>
> What might make sense is to create a method that returns a List or array
> containing the comments themselves. This would be read only, of course, and
> protect the workbook from modifications that might otherwise break it.
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-find-all-CellComments-in-XLS-X-tp5720014p5720024.html
> Sent from the POI - Dev mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: How to find all CellComments in XLS[X]
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
My only concern with allowing access to the CoomentsTable is the potential
for drastically breaking the workbook file. With a reference to the
CommentsTable in hand it would be possible to add new comments and remove
existing ones and I am concerned that the logic is not in place to deal with
all of the internal references necessary to link comments to rows etc.
What might make sense is to create a method that returns a List or array
containing the comments themselves. This would be read only, of course, and
protect the workbook from modifications that might otherwise break it.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-find-all-CellComments-in-XLS-X-tp5720014p5720024.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org