You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Bengt Rodehav <be...@rodehav.com> on 2016/06/07 12:28:07 UTC
Set comment width in pixels
Hello,
I generated comments for some cells in the Excel document. But, it seems
like the width of the comment depends on the width of the cell which is
very unintuitive. I know how wide (in pixels) the comment need to be to
work visually but sometimes it is attached to a wide cell and sometimes to
a very narrow cell. How can I set the comment width in pixels?
I found this on Stack Overflow but no answer unfortunately:
http://stackoverflow.com/questions/27960873/autosize-excel-comments-with-apache-poi
I use this code to generate comments:
CreationHelper factory = wb.getCreationHelper();
Drawing drawing = theSheet.createDrawingPatriarch();
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(theCell.getColumnIndex());
anchor.setCol2(theCell.getColumnIndex() + 1);
anchor.setRow1(theCell.getRowIndex());
anchor.setRow2(theCell.getRowIndex() + 3);
// Create the comment and set the text+author
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("Comment string");
comment.setString(str);
comment.setAuthor("Author");
/Bengt
Re: Set comment width in pixels
Posted by Bengt Rodehav <be...@rodehav.com>.
OK - thanks.
/Bengt
2016-06-13 11:35 GMT+02:00 Javen O'Neal <ja...@gmail.com>:
> To better understand what the XLSX format can store, both to decipher the
> <v:shape> XML you pasted in the previous email and to learn about any
> optional tags that weren't in your snippet, you can skim through the
> OOXML schema.
>
> How to get the schemas:
> If you check out and build POI, the schema will located in
> ooxml-lib/OfficeOpenXML-XMLSchema.zip/sml-comments.xsd.
> Direct download:
>
> http://www.ecma-international.org/publications/files/ECMA-ST/Office%20Open%20XML%201st%20edition%20Part%204%20(PDF).zip
> ,
> open the downloaded OfficeOpenXML-Part4.zip and extract
> OfficeOpenXML-XMLSchema.zip
> Inside that zip, files starting with sml (spreadsheet markup language)
> refer to the Excel OOXML standard. You'll also need to search through vml
> files, the Vector Markup Language that defines how to draw the Comments on
> the Sheet canvas
>
> In the OOXML schema, CT is short for ComplexType, while ST is short for
> SimpleType.
>
> To get started, look at sml-comments.xsd, vml-main.xsd, and
> vml.spreadsheetDrawing.xsd, searching for comment, shape, textbox,
> clientdata, anchor, width, height
>
> It may be easier to understand the XML format by reformatting the
> whitespace from the xml files extracted from an xlsx:
>
> <xml xmlns:v="urn:schemas-microsoft-com:vml"
> xmlns:o="urn:schemas-microsoft-com:office:office"
> xmlns:x="urn:schemas-microsoft-com:office:excel">
> <o:shapelayout v:ext="edit">
> <o:idmap v:ext="edit" data="1"/>
> </o:shapelayout>
> <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202"
> path="m,l,21600r21600,l21600,xe">
> <v:stroke joinstyle="miter"/>
> <v:path gradientshapeok="t" o:connecttype="rect"/>
> </v:shapetype>
> <v:shape id="_x0000_s1025" type="#_x0000_t202"
> style='position:absolute;
> margin-left:59.25pt;margin-top:1.5pt;width:802.5pt;height:129pt;z-index:1;
> visibility:visible;mso-wrap-style:tight' fillcolor="#ffffe1"
> o:insetmode="auto">
> <v:fill color2="#ffffe1"/>
> <v:shadow color="black" obscured="t"/>
> <v:path o:connecttype="none"/>
> <v:textbox style='mso-direction-alt:auto'>
> <div style='text-align:left'></div>
> </v:textbox>
> <x:ClientData ObjectType="Note">
> <x:Anchor>1, 15, 0, 2, 17, 61, 8, 14</x:Anchor>
> <x:AutoFill>False</x:AutoFill>
> <x:Row>0</x:Row>
> <x:Column>0</x:Column>
> <x:Visible/>
> </x:ClientData>
> </v:shape>
> </xml>
>
>
> On Mon, Jun 13, 2016 at 1:16 AM, Bengt Rodehav <be...@rodehav.com> wrote:
>
> > Hi Mark,
> >
> > I did look at the XML for the drawing associated with the comment. It
> looks
> > somesting like this
> >
> >
> > <xml xmlns:v="urn:schemas-microsoft-com:vml"
> > xmlns:o="urn:schemas-microsoft-com:office:office"
> > xmlns:x="urn:schemas-microsoft-com:office:excel">
> > <o:shapelayout v:ext="edit">
> > <o:idmap v:ext="edit" data="1"/>
> > </o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600"
> > o:spt="202"
> > path="m,l,21600r21600,l21600,xe">
> > <v:stroke joinstyle="miter"/>
> > <v:path gradientshapeok="t" o:connecttype="rect"/>
> > </v:shapetype><v:shape id="_x0000_s1025" type="#_x0000_t202"
> > style='position:absolute;
> >
> >
> margin-left:59.25pt;margin-top:1.5pt;width:802.5pt;height:129pt;z-index:1;
> > visibility:visible;mso-wrap-style:tight' fillcolor="#ffffe1"
> > o:insetmode="auto">
> > <v:fill color2="#ffffe1"/>
> > <v:shadow color="black" obscured="t"/>
> > <v:path o:connecttype="none"/>
> > <v:textbox style='mso-direction-alt:auto'>
> > <div style='text-align:left'></div>
> > </v:textbox>
> > <x:ClientData ObjectType="Note">
> > <x:Anchor>
> > 1, 15, 0, 2, 17, 61, 8, 14</x:Anchor>
> > <x:AutoFill>False</x:AutoFill>
> > <x:Row>0</x:Row>
> > <x:Column>0</x:Column>
> > <x:Visible/>
> > </x:ClientData>
> > </v:shape></xml>
> >
> > There is a with as an attribute to the <shape> element. It seems correct
> > but changing it doesn't help. It's the values in the <anchor> element
> that
> > seems to determine the size. The width attribute is then updated to
> > whatever is calculated using the <anchor>.
> >
> > I'm really not an Excel expert so I'm not the right person to solve this.
> > There also seems to be a large number of comments about Excel bugs
> > regarding sizing (and positioning) of comments when I google the subject.
> >
> > To me it appears like the only way to set the width of a comment (in
> > pixels) is to first calculate how many columns must be spanned and then
> > adding a little extra offset in the last column for the anchor. To make
> > this work I then need to be able to convert between pixels and whatever
> > unit the anchor uses (I found the class org.apache.poi.util.Units which I
> > assume can help me with that) and also to determine the column widths in
> > some kind of convertable unit.
> >
> > I can then create an anchor with a certain width i pixels. Hopefully that
> > works.
> >
> > /Bengt
> >
> >
> >
> >
> >
> >
> > 2016-06-10 16:38 GMT+02:00 Mark Beardsley <ma...@tiscali.co.uk>:
> >
> > > Why not try it to find out what information is stored in the file - the
> > xml
> > > based .xlsx file format would be best for this.
> > >
> > > Looking at
> > >
> > >
> >
> https://support.office.com/en-us/article/Annotate-a-worksheet-by-using-comments-3b7065dd-531a-4ffe-8f18-8d047a6ccae7#bm4
> > > it is certainly possible to resize a comment. Knowing this, I would
> > create
> > > a
> > > workbook with a worksheet that has a comment on a single cell and then
> > save
> > > this away. Resize the comment and save the workbook away again under a
> > > different name. Unzip both and look to see what information is stored
> in
> > > the
> > > files and how they differ one from the other. This should point to the
> > > areas
> > > of the xml markup that need to be addressed and they can then be
> exposed
> > > through the api.
> > >
> > > Once you know how the size of the comment can be specified, it might
> then
> > > be
> > > possible to move on to setting those dimensions using pixels as the
> unit.
> > >
> > >
> > >
> > > --
> > > View this message in context:
> > >
> >
> http://apache-poi.1045710.n5.nabble.com/Set-comment-width-in-pixels-tp5723247p5723321.html
> > > Sent from the POI - User mailing list archive at Nabble.com.
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > > For additional commands, e-mail: user-help@poi.apache.org
> > >
> > >
> >
>
Re: Set comment width in pixels
Posted by Javen O'Neal <ja...@gmail.com>.
To better understand what the XLSX format can store, both to decipher the
<v:shape> XML you pasted in the previous email and to learn about any
optional tags that weren't in your snippet, you can skim through the
OOXML schema.
How to get the schemas:
If you check out and build POI, the schema will located in
ooxml-lib/OfficeOpenXML-XMLSchema.zip/sml-comments.xsd.
Direct download:
http://www.ecma-international.org/publications/files/ECMA-ST/Office%20Open%20XML%201st%20edition%20Part%204%20(PDF).zip,
open the downloaded OfficeOpenXML-Part4.zip and extract
OfficeOpenXML-XMLSchema.zip
Inside that zip, files starting with sml (spreadsheet markup language)
refer to the Excel OOXML standard. You'll also need to search through vml
files, the Vector Markup Language that defines how to draw the Comments on
the Sheet canvas
In the OOXML schema, CT is short for ComplexType, while ST is short for
SimpleType.
To get started, look at sml-comments.xsd, vml-main.xsd, and
vml.spreadsheetDrawing.xsd, searching for comment, shape, textbox,
clientdata, anchor, width, height
It may be easier to understand the XML format by reformatting the
whitespace from the xml files extracted from an xlsx:
<xml xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1"/>
</o:shapelayout>
<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202"
path="m,l,21600r21600,l21600,xe">
<v:stroke joinstyle="miter"/>
<v:path gradientshapeok="t" o:connecttype="rect"/>
</v:shapetype>
<v:shape id="_x0000_s1025" type="#_x0000_t202"
style='position:absolute;
margin-left:59.25pt;margin-top:1.5pt;width:802.5pt;height:129pt;z-index:1;
visibility:visible;mso-wrap-style:tight' fillcolor="#ffffe1"
o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<v:textbox style='mso-direction-alt:auto'>
<div style='text-align:left'></div>
</v:textbox>
<x:ClientData ObjectType="Note">
<x:Anchor>1, 15, 0, 2, 17, 61, 8, 14</x:Anchor>
<x:AutoFill>False</x:AutoFill>
<x:Row>0</x:Row>
<x:Column>0</x:Column>
<x:Visible/>
</x:ClientData>
</v:shape>
</xml>
On Mon, Jun 13, 2016 at 1:16 AM, Bengt Rodehav <be...@rodehav.com> wrote:
> Hi Mark,
>
> I did look at the XML for the drawing associated with the comment. It looks
> somesting like this
>
>
> <xml xmlns:v="urn:schemas-microsoft-com:vml"
> xmlns:o="urn:schemas-microsoft-com:office:office"
> xmlns:x="urn:schemas-microsoft-com:office:excel">
> <o:shapelayout v:ext="edit">
> <o:idmap v:ext="edit" data="1"/>
> </o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600"
> o:spt="202"
> path="m,l,21600r21600,l21600,xe">
> <v:stroke joinstyle="miter"/>
> <v:path gradientshapeok="t" o:connecttype="rect"/>
> </v:shapetype><v:shape id="_x0000_s1025" type="#_x0000_t202"
> style='position:absolute;
>
> margin-left:59.25pt;margin-top:1.5pt;width:802.5pt;height:129pt;z-index:1;
> visibility:visible;mso-wrap-style:tight' fillcolor="#ffffe1"
> o:insetmode="auto">
> <v:fill color2="#ffffe1"/>
> <v:shadow color="black" obscured="t"/>
> <v:path o:connecttype="none"/>
> <v:textbox style='mso-direction-alt:auto'>
> <div style='text-align:left'></div>
> </v:textbox>
> <x:ClientData ObjectType="Note">
> <x:Anchor>
> 1, 15, 0, 2, 17, 61, 8, 14</x:Anchor>
> <x:AutoFill>False</x:AutoFill>
> <x:Row>0</x:Row>
> <x:Column>0</x:Column>
> <x:Visible/>
> </x:ClientData>
> </v:shape></xml>
>
> There is a with as an attribute to the <shape> element. It seems correct
> but changing it doesn't help. It's the values in the <anchor> element that
> seems to determine the size. The width attribute is then updated to
> whatever is calculated using the <anchor>.
>
> I'm really not an Excel expert so I'm not the right person to solve this.
> There also seems to be a large number of comments about Excel bugs
> regarding sizing (and positioning) of comments when I google the subject.
>
> To me it appears like the only way to set the width of a comment (in
> pixels) is to first calculate how many columns must be spanned and then
> adding a little extra offset in the last column for the anchor. To make
> this work I then need to be able to convert between pixels and whatever
> unit the anchor uses (I found the class org.apache.poi.util.Units which I
> assume can help me with that) and also to determine the column widths in
> some kind of convertable unit.
>
> I can then create an anchor with a certain width i pixels. Hopefully that
> works.
>
> /Bengt
>
>
>
>
>
>
> 2016-06-10 16:38 GMT+02:00 Mark Beardsley <ma...@tiscali.co.uk>:
>
> > Why not try it to find out what information is stored in the file - the
> xml
> > based .xlsx file format would be best for this.
> >
> > Looking at
> >
> >
> https://support.office.com/en-us/article/Annotate-a-worksheet-by-using-comments-3b7065dd-531a-4ffe-8f18-8d047a6ccae7#bm4
> > it is certainly possible to resize a comment. Knowing this, I would
> create
> > a
> > workbook with a worksheet that has a comment on a single cell and then
> save
> > this away. Resize the comment and save the workbook away again under a
> > different name. Unzip both and look to see what information is stored in
> > the
> > files and how they differ one from the other. This should point to the
> > areas
> > of the xml markup that need to be addressed and they can then be exposed
> > through the api.
> >
> > Once you know how the size of the comment can be specified, it might then
> > be
> > possible to move on to setting those dimensions using pixels as the unit.
> >
> >
> >
> > --
> > View this message in context:
> >
> http://apache-poi.1045710.n5.nabble.com/Set-comment-width-in-pixels-tp5723247p5723321.html
> > Sent from the POI - User mailing list archive at Nabble.com.
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
>
Re: Set comment width in pixels
Posted by Bengt Rodehav <be...@rodehav.com>.
Hi Mark,
I did look at the XML for the drawing associated with the comment. It looks
somesting like this
<xml xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1"/>
</o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600"
o:spt="202"
path="m,l,21600r21600,l21600,xe">
<v:stroke joinstyle="miter"/>
<v:path gradientshapeok="t" o:connecttype="rect"/>
</v:shapetype><v:shape id="_x0000_s1025" type="#_x0000_t202"
style='position:absolute;
margin-left:59.25pt;margin-top:1.5pt;width:802.5pt;height:129pt;z-index:1;
visibility:visible;mso-wrap-style:tight' fillcolor="#ffffe1"
o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<v:textbox style='mso-direction-alt:auto'>
<div style='text-align:left'></div>
</v:textbox>
<x:ClientData ObjectType="Note">
<x:Anchor>
1, 15, 0, 2, 17, 61, 8, 14</x:Anchor>
<x:AutoFill>False</x:AutoFill>
<x:Row>0</x:Row>
<x:Column>0</x:Column>
<x:Visible/>
</x:ClientData>
</v:shape></xml>
There is a with as an attribute to the <shape> element. It seems correct
but changing it doesn't help. It's the values in the <anchor> element that
seems to determine the size. The width attribute is then updated to
whatever is calculated using the <anchor>.
I'm really not an Excel expert so I'm not the right person to solve this.
There also seems to be a large number of comments about Excel bugs
regarding sizing (and positioning) of comments when I google the subject.
To me it appears like the only way to set the width of a comment (in
pixels) is to first calculate how many columns must be spanned and then
adding a little extra offset in the last column for the anchor. To make
this work I then need to be able to convert between pixels and whatever
unit the anchor uses (I found the class org.apache.poi.util.Units which I
assume can help me with that) and also to determine the column widths in
some kind of convertable unit.
I can then create an anchor with a certain width i pixels. Hopefully that
works.
/Bengt
2016-06-10 16:38 GMT+02:00 Mark Beardsley <ma...@tiscali.co.uk>:
> Why not try it to find out what information is stored in the file - the xml
> based .xlsx file format would be best for this.
>
> Looking at
>
> https://support.office.com/en-us/article/Annotate-a-worksheet-by-using-comments-3b7065dd-531a-4ffe-8f18-8d047a6ccae7#bm4
> it is certainly possible to resize a comment. Knowing this, I would create
> a
> workbook with a worksheet that has a comment on a single cell and then save
> this away. Resize the comment and save the workbook away again under a
> different name. Unzip both and look to see what information is stored in
> the
> files and how they differ one from the other. This should point to the
> areas
> of the xml markup that need to be addressed and they can then be exposed
> through the api.
>
> Once you know how the size of the comment can be specified, it might then
> be
> possible to move on to setting those dimensions using pixels as the unit.
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Set-comment-width-in-pixels-tp5723247p5723321.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Set comment width in pixels
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Why not try it to find out what information is stored in the file - the xml
based .xlsx file format would be best for this.
Looking at
https://support.office.com/en-us/article/Annotate-a-worksheet-by-using-comments-3b7065dd-531a-4ffe-8f18-8d047a6ccae7#bm4
it is certainly possible to resize a comment. Knowing this, I would create a
workbook with a worksheet that has a comment on a single cell and then save
this away. Resize the comment and save the workbook away again under a
different name. Unzip both and look to see what information is stored in the
files and how they differ one from the other. This should point to the areas
of the xml markup that need to be addressed and they can then be exposed
through the api.
Once you know how the size of the comment can be specified, it might then be
possible to move on to setting those dimensions using pixels as the unit.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Set-comment-width-in-pixels-tp5723247p5723321.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set comment width in pixels
Posted by Bengt Rodehav <be...@rodehav.com>.
Hi Andi,
Interesting code in the URL you mentioned. It was quite hard to follow
though...
I wasn't sure how I could use this for comments. Does Excel support
specifiying the width of a commnent in pixels and not in number of columns?
Is it just a limitation in POI or is it a limitation in Excel? If Excel
supports it, I would love an API for this.
I don't need exact positioning but I need to be able to specify the size
(in my case the width) of the comment independent of the columns' widths.
/Bengt
2016-06-08 9:40 GMT+02:00 Andreas Beeker <ki...@apache.org>:
> Hi Bengt,
>
> I guess comments are similar to pictures, but I haven't tested it out yet.
>
> > Question then is how do you determine the width of a column in pixels?
> This is how you do it for pictures:
> org.apache.poi.ss.util.ImageUtils.setPreferredSize(...)
>
> I only had a quickview through the ecma schemas, but when it comes to
> exact positioning,
> it looks like there's no such thing as anchors (only refs) for comments,
> otherwise a
> OneCellAnchor (http://stackoverflow.com/questions/19291948) might be
> another solution ...
> (If this would work and is interesting to you, we can change the
> visibility of the methods, to get rid of the reflections ...)
>
> Andi
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Set comment width in pixels
Posted by Andreas Beeker <ki...@apache.org>.
Hi Bengt,
I guess comments are similar to pictures, but I haven't tested it out yet.
> Question then is how do you determine the width of a column in pixels?
This is how you do it for pictures: org.apache.poi.ss.util.ImageUtils.setPreferredSize(...)
I only had a quickview through the ecma schemas, but when it comes to exact positioning,
it looks like there's no such thing as anchors (only refs) for comments, otherwise a
OneCellAnchor (http://stackoverflow.com/questions/19291948) might be another solution ...
(If this would work and is interesting to you, we can change the visibility of the methods, to get rid of the reflections ...)
Andi
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set comment width in pixels
Posted by Bengt Rodehav <be...@rodehav.com>.
Thanks for your reply Dominik.
However, this only seems to enable putting the anchor in a certain place in
a cell. What I would like is not to specify the width of the comment as "a
number of cells" but as pixels (or some other unit). I need to know that
the comment text actually fits into the comment.
The problem with specifying the width in "number of columns" is that it
doesn't make the width determinable - it varies depending on the width of
this (and the following columns). It's very strange to specify the width in
number of columns I think.
The only thing I can think of is, if it is possible to determine the width
of a column in pixels, to:
1. Start with one column width
2. If the width is too small then try with two columns width
3. If the width is still too small then try with three columns width
4. And so on
Question then is how do you determine the width of a column in pixels?
If you want the width to be exactly a number of pixels (and not just wide
enough as above) then I guess that the dX1/dX2/dY1/dY2 could be used.
/Bengt
2016-06-07 22:20 GMT+02:00 Dominik Stadler <do...@gmx.at>:
> Hi,
>
> Did you try to work with the setDx1/setDy1/... methods? That might allow to
> do what you are looking for here when you take into account how it works.
>
> From the xlsx-Spec:
>
> "... describes the placement of the drawing within the spreadsheet based
> upon offsets
> as well as a specified column and row. The offset is always in reference to
> the specified anchor cell and
> acts to offset the shape object from being exactly on top of the anchor
> cell. The offset information
> determines the actual placement of the drawing within the spreadsheet while
> the row and column are
> used to specify to which cell the drawing should be anchored. Thus, if the
> anchor cell changes positions
> then the drawing can be moved as well." and
>
> "colOff (Column Offset)
> This element is used to specify the column offset within a cell. The units
> for which this attribute is specified in
> reside within the simple type definition referenced below."
>
> unfortunately I could not find after a quick look what dimension the
> colOffset/rowOffset actually has, so you might need to go by
> trial-and-error a bit to find useful values for your case.
>
> Dominik.
>
> On Tue, Jun 7, 2016 at 2:28 PM, Bengt Rodehav <be...@rodehav.com> wrote:
>
> > Hello,
> >
> > I generated comments for some cells in the Excel document. But, it seems
> > like the width of the comment depends on the width of the cell which is
> > very unintuitive. I know how wide (in pixels) the comment need to be to
> > work visually but sometimes it is attached to a wide cell and sometimes
> to
> > a very narrow cell. How can I set the comment width in pixels?
> >
> > I found this on Stack Overflow but no answer unfortunately:
> >
> >
> >
> http://stackoverflow.com/questions/27960873/autosize-excel-comments-with-apache-poi
> >
> > I use this code to generate comments:
> >
> > CreationHelper factory = wb.getCreationHelper();
> > Drawing drawing = theSheet.createDrawingPatriarch();
> >
> > // When the comment box is visible, have it show in a 1x3 space
> > ClientAnchor anchor = factory.createClientAnchor();
> > anchor.setCol1(theCell.getColumnIndex());
> > anchor.setCol2(theCell.getColumnIndex() + 1);
> > anchor.setRow1(theCell.getRowIndex());
> > anchor.setRow2(theCell.getRowIndex() + 3);
> >
> > // Create the comment and set the text+author
> > Comment comment = drawing.createCellComment(anchor);
> > RichTextString str = factory.createRichTextString("Comment string");
> > comment.setString(str);
> > comment.setAuthor("Author");
> >
> >
> > /Bengt
> >
>
Re: Set comment width in pixels
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Did you try to work with the setDx1/setDy1/... methods? That might allow to
do what you are looking for here when you take into account how it works.
From the xlsx-Spec:
"... describes the placement of the drawing within the spreadsheet based
upon offsets
as well as a specified column and row. The offset is always in reference to
the specified anchor cell and
acts to offset the shape object from being exactly on top of the anchor
cell. The offset information
determines the actual placement of the drawing within the spreadsheet while
the row and column are
used to specify to which cell the drawing should be anchored. Thus, if the
anchor cell changes positions
then the drawing can be moved as well." and
"colOff (Column Offset)
This element is used to specify the column offset within a cell. The units
for which this attribute is specified in
reside within the simple type definition referenced below."
unfortunately I could not find after a quick look what dimension the
colOffset/rowOffset actually has, so you might need to go by
trial-and-error a bit to find useful values for your case.
Dominik.
On Tue, Jun 7, 2016 at 2:28 PM, Bengt Rodehav <be...@rodehav.com> wrote:
> Hello,
>
> I generated comments for some cells in the Excel document. But, it seems
> like the width of the comment depends on the width of the cell which is
> very unintuitive. I know how wide (in pixels) the comment need to be to
> work visually but sometimes it is attached to a wide cell and sometimes to
> a very narrow cell. How can I set the comment width in pixels?
>
> I found this on Stack Overflow but no answer unfortunately:
>
>
> http://stackoverflow.com/questions/27960873/autosize-excel-comments-with-apache-poi
>
> I use this code to generate comments:
>
> CreationHelper factory = wb.getCreationHelper();
> Drawing drawing = theSheet.createDrawingPatriarch();
>
> // When the comment box is visible, have it show in a 1x3 space
> ClientAnchor anchor = factory.createClientAnchor();
> anchor.setCol1(theCell.getColumnIndex());
> anchor.setCol2(theCell.getColumnIndex() + 1);
> anchor.setRow1(theCell.getRowIndex());
> anchor.setRow2(theCell.getRowIndex() + 3);
>
> // Create the comment and set the text+author
> Comment comment = drawing.createCellComment(anchor);
> RichTextString str = factory.createRichTextString("Comment string");
> comment.setString(str);
> comment.setAuthor("Author");
>
>
> /Bengt
>