poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bengt Rodehav <be...@rodehav.com>
Subject Re: Set comment width in pixels
Date Tue, 14 Jun 2016 07:54:44 GMT
OK - thanks.

/Bengt

2016-06-13 11:35 GMT+02:00 Javen O'Neal <javenoneal@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 <bengt@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 <markbrdsly@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
> > >
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message