Harmonise chart reference and sheet coordinates.

Issue #277 resolved
Daniele S. created an issue

I use this code to create a chart ( I tried with LineChart and BarChart with similar results ).

        # LINE GRAPH
        if ws_chart:
            ws_chart.title = "Crashes per day Graph"

            values  = Reference(ws, (0, 1), (i, 1), 'n', NumberFormat.FORMAT_NUMBER)
            labels  = Reference(ws, (0, 0), (i, 0), 's')

            series = Series(values, labels=labels, title="Crashes per Day Trend")

            chart  = BarChart()

Excel screenshot. http://i.imgur.com/L45PP3O.png

After repairing the file works ok but I cannot ship a report like that.

OS. Windows 7 64bit EXCEL: 2007 Python: 2.7.6 OpenPyXl: 1.8.3

Comments (11)

  1. CharlieC

    Thanks for the report and sorry it's not working for you. One thing I found when working on this is that it is very easy to get the size of the reference different to the range of cells covered. This will produce unreadable contents with a misleading error message from Excel. Don't know if that's the case here but you might want to check.

    Can you supply either a full bit of code or sample file (with dummy data)?

    NB. you don't need to supply the cell data-type and number formatting should be passed in as a keyword argument only if you need something other than standard, ie. dates.

  2. CharlieC

    Getting the same error with Excel 2010 (on Windows but not on Mac) which makes me think it's the reference / range problem I had working on this: developing on Mac and everything seems hunkydory only for it to go pear-shaped on Windows.

    I think the root cause is 1-indexing. So A1 is (0,0) for the reference but (1,1) for the worksheet so your final reference is to a non-existent cell. In addition you're incrementing your counter after creating the cells, you actually out by two. Enumerate is your friend here but even then having to add one here or take it away there is a mess. Certainly need to clean this up. Been too busy so far with all the other problems but tripped over it myself often enough.

    values  = Reference(ws, (0, 1), (i-2, 1))
    labels  = Reference(ws, (0, 0), (i-2, 0))

    Will work.

    On a sidenote: I think we will probably soon drop official support for Office 2007 due to lack of testing possibilities.

  3. CharlieC

    More compact version of the body of your script. I assume you're using a dictionary to mimic real data structures? Otherwise simply sorting your list on the first term would make things even easier.

            cpd = OrderedDict(sorted(DATA, key=lambda t: t[0]))
            for idx, dt in enumerate(cpd):
                ws.append([dt, cpd[dt]])
            values  = Reference(ws, (0, 1), (idx, 1))
            labels  = Reference(ws, (0, 0), (idx, 0))
  4. Daniele S. reporter

    Thanks, works like a charm! And I like the compact version better. A (unrelated) question: can I position the chart programmatically? At the moment it overlaps with the data.

  5. CharlieC

    There are some attributes you can set. You'll need to look at the source as to how they work. I'm hoping to improve on these in 1.9 They're currently set as my defaults.

    Positioning is not helped by Excel's insane units of measurement. For example, you can accurately set the vertical position but not the horizontal one. See the units.py for more information.

    It would be really nice to have a list of stuff that's missing from the documentation and the examples in /samples.

  6. Log in to comment