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 <markbrd...@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-unsubscr...@poi.apache.org > > For additional commands, e-mail: user-h...@poi.apache.org > > > > >
