OK - thanks. /Bengt
2016-06-13 11:35 GMT+02:00 Javen O'Neal <javenon...@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 <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 > > > > > > > > >
