setting/getting default column width

[Thomas Nygårds]

When I run the (fixed) ./doc/charts/scatter.py file below

from openpyxl import Workbook
from openpyxl.chart import (
    ScatterChart,
    Reference,
    Series,
)

wb = Workbook()
ws = wb.active

rows = [
    ['Size', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    ws.append(row)

chart = ScatterChart()
chart.scatterStyle = "marker"
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'

xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
    values = Reference(ws, min_col=i, min_row=1, max_row=7)
    series = Series(values, xvalues, title_from_data=True)
    chart.series.append(series)

ws.add_chart(chart, "A10")

wb.save("scatter.xlsx")

the file scatter.xlsx (attached) is created.
the width of the chart becomes what the defaults in the chart class is set to

   # from  ...\chart\_chart.py
    width = 15 # in cm, approx 5 rows (Actually it should say columns here...)
    height = 7.5 # in cm, approx 14 rows

I can of course set these values via

chart.width = 15
chart.height = 7.5

The question is how do I set this to the size of exactly 5 columns and 14 rows.
I see some definitions

# from ...\utils\units.py

#constants

DEFAULT_ROW_HEIGHT = 15.  # Default row height measured in point size.
BASE_COL_WIDTH = 13 # in characters
DEFAULT_COLUMN_WIDTH = 51.85 # in points, should be characters

The value of DEFAULT_COLUMN_WIDTH does not make sense and the description is unclear..
Converting 51.85 with function "points_to_pixels" from the same file I get 70.
But if I open the attached file in excel all the columns are 80 pixels wide.
sheet1.xml has the property defaultColWidth="11.42578125" which does not make any sense to me either...
what unit has this? it seems to small to be a dxa, EMU or pixels and to big to be inch or cm

How do I convert cell width to cm in a secure way?
Right now I use a constant  of "2.117" which corresponds to the unknown default column width.

[Charlie Clark]

Am .09.2015, 13:58 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> *The question is *how do I set this to the size of *exactly *5 columns
> and 14 rows.

The simple answer is that you don't. Charts are effectively overlaid on
top of a worksheet and have a screen-based system which corresponds
roughly to certain default column and row sizes.

There are, however, different anchor possibilities:

* anchored to the top-left cell (this is the default) with a width and
height that more or less correspond to rows and columns
* anchored top-left and bottom-right, which may be want you want. Such
charts will grow and shrink as the cells and columns between the anchors
change size
* use absolute (screen-based) positioning

I think that top-left is currently hard-wired into the code but it's
fairly easy to fix this. Just haven't got round to it yet.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Thomas Nygårds]

I think that top-left is currently hard-wired into the code but it's  
fairly easy to fix this. Just haven't got round to it yet.

Ok, yes it looks like it is.


I am now using my measured hardcoded constants that seem to work for what I have, for now:

# standard width openpyxl=2.117 cm/10.71 chars/ 80 pixels
# standard height openpyxl =0.529 cm/15 points/20 pixels
COLUMN_WIDTH_CM = 2.117
ROW_HEIGHT_CM = 0.529

What bothers me is that I do not know where they come from in the code...
It should be possible to get them somehow., shouldn't it?

[Charlie Clark]

Am .09.2015, 09:16 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> Ok, yes it looks like it is.

This can probably be changed to a default anchor type that can be
overwritten if required. Haven't got round to this yet as it also affects
images.

> I am now using my measured hardcoded constants that seem to work for
> what I
> have, for now:
> # standard width openpyxl=2.117 cm/10.71 chars/ 80 pixels
> # standard height openpyxl =0.529 cm/15 points/20 pixels
> COLUMN_WIDTH_CM = 2.117
> ROW_HEIGHT_CM = 0.529

> What bothers me is that I do not know where they come from in the code...
> It should be possible to get them somehow., shouldn't it?

Nope: it is entirely dependent upon the environment where the sheet is
opened: operating system and typeface are the key factors. Microsoft has
published the logic for determining column width. There is a worksheet
method `point_pos` that tries to calculate position but it's basically a
game you can't win.

In 2.3 I've gone for a top-left anchor with metric chart sizing as
possibly the easiest to work with for people unfamiliar with things like
EMUs, and completely avoids the offsets that are usually thrown in. The
hope is that most people will be happy with being able to position their
chart reliably and size it approximately. For anything else you're going
to have to understand how anchors work.

[Thomas Nygårds]

    There is a worksheet  
    method `point_pos` that tries to calculate position but it's basically a  
    game you can't win.

 

Ok. :( sometimes I get frustrated with MS Excel and the format.
I cant even imagine the rage you must have felt..

    This can probably be changed to a default anchor type that can be  
    overwritten if required. Haven't got round to this yet as it also affects  
    images.

But when I look in drawing\spreadsheet_drawing.py  in
SpreadsheetDrawing._write(self)


   

def _write(self):
        """        create required structure and the serialise        """
        anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", target='../charts/chart%s.xml' % obj._id)
                row, col = coordinate_to_tuple(obj.anchor)
                anchor = OneCellAnchor()
                anchor._from.row = row -1
                anchor._from.col = col -1
                anchor.ext.width = cm_to_EMU(obj.width)
                anchor.ext.height = cm_to_EMU(obj.height)
                anchor.graphicFrame = self._chart_frame(idx)
            elif isinstance(obj, Image):
                rel = Relationship(type="image", target='../media/image%s.png' % obj._id)
                anchor = obj.drawing.anchor                anchor.pic = self._picture_frame(idx)

            anchors.append(anchor)
            self._rels.append(rel)

        self.oneCellAnchor = anchors
        tree = self.to_tree()
        tree.set('xmlns', SHEET_DRAWING_NS)
        return tree

looks quite "easy" to make charts support anchors directly.  You have already done the necesary classes. And you separate the class types in the _write method already.
It might look someting like this (untested)

def _write(self):
        """        create required structure and the serialise        """
        one_cell_anchors = []
        two_cell_anchors = []
        absolute_anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", target='../charts/chart%s.xml' % obj._id)
                anchor=obj.anchor
                anchor.graphicFrame = self._chart_frame
            elif isinstance(obj, Image):
                rel = Relationship(type="image", target='../media/image%s.png' % obj._id)
                anchor = obj.drawing.anchor                
                anchor.pic = self._picture_frame(idx)

            if isinstance(anchor, OneCellAnchor):
                one_cell_anchors.append(anchor)                        
            elif isinstance(anchor, TwoCellAnchor):
                two_cell_anchors.append(anchor)    
            else:          
                #AbsoluteAnchor
                absolute_anchors.append(anchor)                
            self._rels.append(rel)

        self.oneCellAnchor = one_cell_anchors
        self.twoCellAnchor = two_cell_anchors
        self.absoluteAnchor= absolute_anchors
       
        tree = self.to_tree()
        tree.set('xmlns', SHEET_DRAWING_NS)
        return tree

Well, the WorkSheet.add_chart()  method might look something like this (untested):
 

def add_chart(self, chart, anchor=None, anchor_top_left=None, anchor_bottom_right=None, pos_x=None, pos_y=None, ext_x=None, ext_y=None):
        """
        Add a chart to the sheet
        Optionally provide a cell for the top-left anchor,
        or the top-left and bottom-ight anchor
        or absolute x- and y-positions together with width and hight
        """        
        chart_anchor=None
        if anchor is not None:
            row, col = coordinate_to_tuple(anchor)
            chart_anchor = OneCellAnchor()
            chart_anchor._from.row = row -1
            chart_anchor._from.col = col -1
            chart_anchor.ext.width = cm_to_EMU(obj.width)
            chart_anchor.ext.height = cm_to_EMU(obj.height)
        elseif anchor_top_left is not None or anchor_bottom_right is not None:
            if anchor_top_left is not None and anchor_bottom_right is not None:
                row1, col1 = coordinate_to_tuple(anchor_top_left)
                row2, col2 = coordinate_to_tuple(anchor_bottom_right)
                chart_anchor=TwoCellAnchor(_from=AnchorMarker(col=col1,row=row1), to=AnchorMarker(col=col2, row=row2), editAs="twoCell")
            else:
                msg = "If you provide a value for "\
                    "'anchor_top_left' you must also provide a value for 'anchor_bottom_right'"
                raise InsufficientCoordinatesException(msg)    
        elif pos_x is not None or pos_y is not None or ext_x is not None or ext_y is not None:
            if pos_x is not None or pos_y is not None or ext_x is not None and ext_y is not None:
                pos = Point2D(pos_x, pos_y)
                ext = PositiveSize2D(ext_x, ext_y)
                chart_anchor=AbsoluteAnchor(pos=pos, ext=ext)
            else:
                msg = "If you provide a value for "\
                    "'pos_x' you must also provide a value for 'pos_y' *and* 'ext_x' *and* 'ext_y'"
                raise InsufficientCoordinatesException(msg)    
        if chart_anchor is not None
            chart.anchor = chart_anchor
        self._charts.append(chart)
        self._parent._charts.append(ref(chart))

 

[Thomas Nygårds]

Shoudl i instalkl tox and do a PR
or am I missing something?

[Charlie Clark]

>> There is a worksheet
>> method `point_pos` that tries to calculate position but it's
>> basically
>> a
>> game you can't win.
>>
>
> Ok. :( sometimes I get frustrated with MS Excel and the format.
> I cant even imagine the rage you must have felt..

The format is a pile of crap but you have to get over that. The coordinate
system is one thing, the inconsistencies and naming convention vagaries
are quite another! But, as it says in the estimable film "The School for
Scoundrels": don't get mad, get even!

I file bugs on the specification and wrote a code generator, which I'll be
talking about at next month's PyCon France in Pau, and without which the
new charting stuff simply wouldn't be possible. And at least one other
person seems to have been able to use the generator and live!

Any bugs, such as the perennial "why are the styles of my merged cells
borked?" which aren't covered by the specification are deferred until we
get an answer.

Yes, something like that is what is intended. That code was whipped up
simply to see if all the generated code really worked and it basically did.

> Well, the WorkSheet.add_chart() method might look something like this
> (untested):

Your code is far too complex. This needs thinking through in terms of the
API for client code. It's probably simpler to leave this as it is and
allow client code to overwrite.

ws.add_chart(my_chart) # default is a oneCellAnchor for which should
suffice in most cases
my_chart.anchor = TwoCellAnchor(…) # when you need something speical.

And hope we can get the plumbing to work when write the the relevent
WSDrawing component: anchors are dumb.

add_chart() probably just needs to check for the existence of anchor and
otherwise add the standard one.

One issue might be that, at least in theory, the same chart can be used on
different worksheets. However, the code certainly doesn't behave like
that. In fact, I might at some point rearrange the packaging so that all
worksheet dependencies get packaged with the worksheet. Would make the
housekeeping a whole lot easier!

Please feel free to make a PR (with tested tests! ;-) )

[Thomas Nygårds]

I file bugs on the specification and wrote a code generator, which I'll be  
talking about at next month's PyCon France in Pau, and without which the  
new charting stuff simply wouldn't be possible. And at least one other  
person seems to have been able to use the generator and live!

Code generator! Thats nice! Will your presentation be made available online afterwards?
 

Your code is far too complex. This needs thinking through in terms of the  
API for client code. It's probably simpler to leave this as it is and  
allow client code to overwrite.  

ws.add_chart(my_chart) # default is a oneCellAnchor for which should  
suffice in most cases
my_chart.anchor = TwoCellAnchor(…) # when you need something speical.

 
I was thinking along those lines at first. It is always a trade-off if you want the user to easilty find how to customize the code.
or if he has to have a litle deeper knowledge to do it. I agree with you though, your approach is used in other places in the package and IS cleaner. It takes a little time to get into the philosophy of each project.
 

Please feel free to make a PR (with tested tests! ;-) )

 
Will se when I get my environment up and running. Got the computer and a new windows image installed. That's a first step!
Is there a nice getting started with tox (I use the built in unittest module only....) or is it straight-forward to run?

[Charlie Clark]

Am .09.2015, 16:06 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> Code generator! Thats nice! Will your presentation be made available
> online afterwards?

Don't get too excited! AFAIK the talks are being recorded and will be
available afterwards.

You can the code a run anytime, eg.

python openpyxl/develop/classify.py CT_AutoFilter

But I also often talk about stuff at our local Python Düsseldorf meetings
(if you can understand German):
https://www.youtube.com/user/PyDDF

>> Your code is far too complex. This needs thinking through in terms of
>> the
>>
>> API for client code. It's probably simpler to leave this as it is and
>> allow client code to overwrite.
>
>
>> ws.add_chart(my_chart) # default is a oneCellAnchor for which should
>> suffice in most cases
>> my_chart.anchor = TwoCellAnchor(…) # when you need something speical.
>>
> I was thinking along those lines at first. It is always a trade-off if
> you
> want the user to easilty find how to customize the code.
> or if he has to have a litle deeper knowledge to do it. I agree with you
> though, your approach is used in other places in the package and IS
> cleaner. It takes a little time to get into the philosophy of each
> project.
>
>>
>> Please feel free to make a PR (with tested tests! ;-) )
>>
> Will se when I get my environment up and running. Got the computer and a
> new windows image installed. That's a first step!
> Is there a nice getting started with tox (I use the built in unittest
> module only....) or is it straight-forward to run?

pytest will run the tests, tox will run all the tests for different
configurations. It's a bit of a pain setting things up in Windows but the
documentation now covers it, I hope. Windows remains not particularly
suitable for Python development in my view.