You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Varghese Antony <va...@gmail.com> on 2012/08/24 12:08:35 UTC
How to get location and size of graphs to be displayed from HSSF, for
excel 2003?
Hi,
Currently i am trying to display the charts in excel 2003 using the
HSSFChart APIs. But i am facing problem in finding the location and size
of the charts to be displayed.
I tried the getX(), getY(), getWidth(), setHeight() APIs of "ChartRecord"
but it always return constant values 0,0,X,X for all charts. I also checked
the values getting stored in
LegendRecord. i.e ( private int field_1_xAxisUpperLeft;
private int field_2_yAxisUpperLeft;
private int field_3_xSize;
private int field_4_ySize;) but this values are constant for
a particular chart even if its location is changed.. So how to get the
location and size for a chart.
Do i have to extend hssf/record/chart by adding PosRecord for handling the
Pos field in a graph, will it solve problem , is there any other method?
Thanks
Varghese
Re: How to get location and size of graphs to be displayed from HSSF,
for excel 2003?
Posted by Varghese Antony <va...@gmail.com>.
Hi,
Currently i have found a way to get the position/size information of
charts. I have modified the function createAggregate in the file
EscherAggregate.java.
In the current code the escherrecords are formed only if a combination of
(DrawingRecord & ObjRecord) are found adjacently. Suppose an excel sheet
contains
two shapes then the record list may contain some thing like below and
position/size information of shaped are decoded properly
----record
----record
DrawingRecord
ObjRecord
DrawingRecord
ObjRecord
----record
----record etc
But suppose if an excel 2003 contains two *charts* the records list is
having some order as below
----record
----record
DrawingRecord
ObjRecord
----record
----record
|
|
----record
----record
DrawingRecord
ObjRecord
----record
----record etc
But this is not taken care in the function createAggregate . This function
will take care of only adjacent (DrawingRecord & ObjRecord) pairs.
If I modify the above function to handle the scenario mentioned above for
charts then i am able to get the position/size information of all charts.
Please confirm wether this modification is the correct way of fixing this
problem
Thanks
varghese
On Tue, Aug 28, 2012 at 3:25 PM, Varghese Antony <vargheseantony.v@gmail.com
> wrote:
> Hi,
> I have dumped the records and found that we can get the height and width
> from the record ChartRecord.
>
> But I am not able to get the information regarding the left and top values
> of the chart.. For *shapes* we are getting the position and dimension
> information
> from EscherClientAnchorRecord contained in EscherContainerRecord.
>
> While debuging i found that if the excel sheet is containing only one
> chart then i am able to get the information about the chart
> from EscherClientAnchorRecord
> very accurately.
>
> But if there is two charts then i am able to get the information about
> only one chart from EscherClientAnchorRecord contained
> in EscherContainerRecord.
>
> Below is the eschercontainerrecord structure for a xls(2003) file having
> two charts. it is having postion/dimension of only one chart. Is there
> any way to get the information about the second chart in the sheet
>
> org.apache.poi.ddf.EscherContainerRecord (DgContainer):
> isContainer: true
> version: 0x000F
> instance: 0x0000
> recordId: 0xF002
> numchildren: 2
> children:
> Child 0:
> org.apache.poi.ddf.EscherDgRecord:
> RecordId: 0xF008
> Version: 0x0000
> Instance: 0x0001
> NumShapes: 3
> LastMSOSPID: 1038
>
> Child 1:
> org.apache.poi.ddf.EscherContainerRecord (SpgrContainer):
> isContainer: true
> version: 0x000F
> instance: 0x0000
> recordId: 0xF003
> numchildren: 2
> children:
> Child 0:
> org.apache.poi.ddf.EscherContainerRecord (SpContainer):
> isContainer: true
> version: 0x000F
> instance: 0x0000
> recordId: 0xF004
> numchildren: 2
> children:
> Child 0:
> org.apache.poi.ddf.EscherSpgrRecord:
> RecordId: 0xF009
> Version: 0x0001
> Instance: 0x0000
> RectX: 0
> RectY: 0
> RectWidth: 0
> RectHeight: 0
>
> Child 1:
> org.apache.poi.ddf.EscherSpRecord:
> RecordId: 0xF00A
> Version: 0x0002
> ShapeType: 0x0000
> ShapeId: 1024
> Flags: GROUP|PATRIARCH (0x00000005)
>
>
> Child 1:
> org.apache.poi.ddf.EscherContainerRecord (SpContainer):
> isContainer: true
> version: 0x000F
> instance: 0x0000
> recordId: 0xF004
> numchildren: 4
> children:
> Child 0:
> org.apache.poi.ddf.EscherSpRecord:
> RecordId: 0xF00A
> Version: 0x0002
> ShapeType: 0x00C9
> ShapeId: 1037
> Flags: HAVEANCHOR|HASSHAPETYPE (0x00000A00)
>
> Child 1:
> org.apache.poi.ddf.EscherOptRecord:
> isContainer: false
> version: 0x0003
> instance: 0x0006
> recordId: 0xF00B
> numchildren: 0
> properties:
> propNum: 127, RAW: 0x007F, propName:
> protection.lockagainstgrouping, complex: false, blipId: false, value:
> 31785220 (0x01E50104)
> propNum: 191, RAW: 0x00BF, propName:
> text.sizetexttofitshape, complex: false, blipId: false, value: 524296
> (0x00080008)
> propNum: 385, RAW: 0x0181, propName: fill.fillcolor,
> complex: false, blipId: false, value: 134217806 (0x0800004E)
> propNum: 447, RAW: 0x01BF, propName: fill.nofillhittest,
> complex: false, blipId: false, value: 1048592 (0x00100010)
> propNum: 896, propName: groupshape.shapename, complex:
> true, blipId: true, data:
> 00: 43, 00, 68, 00, 61, 00, 72, 00, 74, 00, 20, 00, 34, 00,
> 00, 00,
> propNum: 959, RAW: 0x03BF, propName: groupshape.print,
> complex: false, blipId: false, value: 131072 (0x00020000)
>
> Child 2:
> org.apache.poi.ddf.EscherClientAnchorRecord:
> RecordId: 0xF010
> Version: 0x0000
> Instance: 0x0000
> Flag: 0
> Col1: 5
> DX1: 0
> Row1: 6
> DY1: 0
> Col2: 12
> DX2: 512
> Row2: 20
> DY2: 102
> Extra Data:
> No Data
>
> Child 3:
> org.apache.poi.ddf.EscherClientDataRecord:
> RecordId: 0xF011
> Version: 0x0000
> Instance: 0x0000
> Extra Data:
> No Data
>
>
> Thanks
> varghese
>
>
>
>
>
>
>
> On Fri, Aug 24, 2012 at 4:06 PM, Yegor Kozlov <ye...@dinom.ru>wrote:
>
>> Use org.apache.poi.hssf.dev.BiffViewer to get an idea how Excel stores
>> location of charts.
>> Dump a file with a chart and examine what records hold the positioning
>> information.
>>
>> Yegor
>>
>> On Fri, Aug 24, 2012 at 2:08 PM, Varghese Antony
>> <va...@gmail.com> wrote:
>> > Hi,
>> >
>> > Currently i am trying to display the charts in excel 2003 using the
>> > HSSFChart APIs. But i am facing problem in finding the location and
>> size
>> > of the charts to be displayed.
>> >
>> > I tried the getX(), getY(), getWidth(), setHeight() APIs of
>> "ChartRecord"
>> > but it always return constant values 0,0,X,X for all charts. I also
>> checked
>> > the values getting stored in
>> >
>> > LegendRecord. i.e ( private int field_1_xAxisUpperLeft;
>> > private int field_2_yAxisUpperLeft;
>> > private int field_3_xSize;
>> > private int field_4_ySize;) but this values are constant
>> for
>> > a particular chart even if its location is changed.. So how to get the
>> > location and size for a chart.
>> >
>> > Do i have to extend hssf/record/chart by adding PosRecord for handling
>> the
>> > Pos field in a graph, will it solve problem , is there any other method?
>> >
>> >
>> > Thanks
>> > Varghese
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: How to get location and size of graphs to be displayed from HSSF,
for excel 2003?
Posted by Varghese Antony <va...@gmail.com>.
Hi,
I have dumped the records and found that we can get the height and width
from the record ChartRecord.
But I am not able to get the information regarding the left and top values
of the chart.. For *shapes* we are getting the position and dimension
information
from EscherClientAnchorRecord contained in EscherContainerRecord.
While debuging i found that if the excel sheet is containing only one chart
then i am able to get the information about the chart
from EscherClientAnchorRecord
very accurately.
But if there is two charts then i am able to get the information about only
one chart from EscherClientAnchorRecord contained
in EscherContainerRecord.
Below is the eschercontainerrecord structure for a xls(2003) file having
two charts. it is having postion/dimension of only one chart. Is there
any way to get the information about the second chart in the sheet
org.apache.poi.ddf.EscherContainerRecord (DgContainer):
isContainer: true
version: 0x000F
instance: 0x0000
recordId: 0xF002
numchildren: 2
children:
Child 0:
org.apache.poi.ddf.EscherDgRecord:
RecordId: 0xF008
Version: 0x0000
Instance: 0x0001
NumShapes: 3
LastMSOSPID: 1038
Child 1:
org.apache.poi.ddf.EscherContainerRecord (SpgrContainer):
isContainer: true
version: 0x000F
instance: 0x0000
recordId: 0xF003
numchildren: 2
children:
Child 0:
org.apache.poi.ddf.EscherContainerRecord (SpContainer):
isContainer: true
version: 0x000F
instance: 0x0000
recordId: 0xF004
numchildren: 2
children:
Child 0:
org.apache.poi.ddf.EscherSpgrRecord:
RecordId: 0xF009
Version: 0x0001
Instance: 0x0000
RectX: 0
RectY: 0
RectWidth: 0
RectHeight: 0
Child 1:
org.apache.poi.ddf.EscherSpRecord:
RecordId: 0xF00A
Version: 0x0002
ShapeType: 0x0000
ShapeId: 1024
Flags: GROUP|PATRIARCH (0x00000005)
Child 1:
org.apache.poi.ddf.EscherContainerRecord (SpContainer):
isContainer: true
version: 0x000F
instance: 0x0000
recordId: 0xF004
numchildren: 4
children:
Child 0:
org.apache.poi.ddf.EscherSpRecord:
RecordId: 0xF00A
Version: 0x0002
ShapeType: 0x00C9
ShapeId: 1037
Flags: HAVEANCHOR|HASSHAPETYPE (0x00000A00)
Child 1:
org.apache.poi.ddf.EscherOptRecord:
isContainer: false
version: 0x0003
instance: 0x0006
recordId: 0xF00B
numchildren: 0
properties:
propNum: 127, RAW: 0x007F, propName:
protection.lockagainstgrouping, complex: false, blipId: false, value:
31785220 (0x01E50104)
propNum: 191, RAW: 0x00BF, propName:
text.sizetexttofitshape, complex: false, blipId: false, value: 524296
(0x00080008)
propNum: 385, RAW: 0x0181, propName: fill.fillcolor,
complex: false, blipId: false, value: 134217806 (0x0800004E)
propNum: 447, RAW: 0x01BF, propName: fill.nofillhittest,
complex: false, blipId: false, value: 1048592 (0x00100010)
propNum: 896, propName: groupshape.shapename, complex:
true, blipId: true, data:
00: 43, 00, 68, 00, 61, 00, 72, 00, 74, 00, 20, 00, 34, 00, 00,
00,
propNum: 959, RAW: 0x03BF, propName: groupshape.print,
complex: false, blipId: false, value: 131072 (0x00020000)
Child 2:
org.apache.poi.ddf.EscherClientAnchorRecord:
RecordId: 0xF010
Version: 0x0000
Instance: 0x0000
Flag: 0
Col1: 5
DX1: 0
Row1: 6
DY1: 0
Col2: 12
DX2: 512
Row2: 20
DY2: 102
Extra Data:
No Data
Child 3:
org.apache.poi.ddf.EscherClientDataRecord:
RecordId: 0xF011
Version: 0x0000
Instance: 0x0000
Extra Data:
No Data
Thanks
varghese
On Fri, Aug 24, 2012 at 4:06 PM, Yegor Kozlov <ye...@dinom.ru> wrote:
> Use org.apache.poi.hssf.dev.BiffViewer to get an idea how Excel stores
> location of charts.
> Dump a file with a chart and examine what records hold the positioning
> information.
>
> Yegor
>
> On Fri, Aug 24, 2012 at 2:08 PM, Varghese Antony
> <va...@gmail.com> wrote:
> > Hi,
> >
> > Currently i am trying to display the charts in excel 2003 using the
> > HSSFChart APIs. But i am facing problem in finding the location and size
> > of the charts to be displayed.
> >
> > I tried the getX(), getY(), getWidth(), setHeight() APIs of
> "ChartRecord"
> > but it always return constant values 0,0,X,X for all charts. I also
> checked
> > the values getting stored in
> >
> > LegendRecord. i.e ( private int field_1_xAxisUpperLeft;
> > private int field_2_yAxisUpperLeft;
> > private int field_3_xSize;
> > private int field_4_ySize;) but this values are constant
> for
> > a particular chart even if its location is changed.. So how to get the
> > location and size for a chart.
> >
> > Do i have to extend hssf/record/chart by adding PosRecord for handling
> the
> > Pos field in a graph, will it solve problem , is there any other method?
> >
> >
> > Thanks
> > Varghese
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: How to get location and size of graphs to be displayed from HSSF,
for excel 2003?
Posted by Yegor Kozlov <ye...@dinom.ru>.
Use org.apache.poi.hssf.dev.BiffViewer to get an idea how Excel stores
location of charts.
Dump a file with a chart and examine what records hold the positioning
information.
Yegor
On Fri, Aug 24, 2012 at 2:08 PM, Varghese Antony
<va...@gmail.com> wrote:
> Hi,
>
> Currently i am trying to display the charts in excel 2003 using the
> HSSFChart APIs. But i am facing problem in finding the location and size
> of the charts to be displayed.
>
> I tried the getX(), getY(), getWidth(), setHeight() APIs of "ChartRecord"
> but it always return constant values 0,0,X,X for all charts. I also checked
> the values getting stored in
>
> LegendRecord. i.e ( private int field_1_xAxisUpperLeft;
> private int field_2_yAxisUpperLeft;
> private int field_3_xSize;
> private int field_4_ySize;) but this values are constant for
> a particular chart even if its location is changed.. So how to get the
> location and size for a chart.
>
> Do i have to extend hssf/record/chart by adding PosRecord for handling the
> Pos field in a graph, will it solve problem , is there any other method?
>
>
> Thanks
> Varghese
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org