charts with openpyxl
nil...@googlemail.com
Just curious. Is it possible to generate simple charts with openpyxl ?
Nils
Eric Gazoni
there is some support for doing charts in openpyxl.
If you're in a hurry, you can try to read the test_chart.py file (in the
tests package or online here:
https://bitbucket.org/ericgazoni/openpyxl/src/6055f698327b/openpyxl/tests/test_chart.py),
and start hacking.
The difficult part is to define your reference using numerical
coordinates (it does not support the 'A1:B2' notation yet), but you can
use openpyxl.cell.coordinate_from_string to get them ;-)
As for now, there is support for Scatter, Line, and Bar charts, but I
must admit I haven't tested them all yet myself.
That might be a good idea for me to start putting examples somewhere as
well :D
Cheers,
Eric
Le 25/10/11 19:08, nil...@googlemail.com a �crit :
nil...@googlemail.com
Thank you for your prompt response. An example for a bar chart would
be awesome.
Cheers
Nils
> there is some support for doingchartsin openpyxl.
> If you're in a hurry, you can try to read the test_chart.py file (in the
>
> The difficult part is to define your reference using numerical
> coordinates (it does not support the 'A1:B2' notation yet), but you can
> use openpyxl.cell.coordinate_from_string to get them ;-)
>
>
> That might be a good idea for me to start putting examples somewhere as
> well :D
>
> Cheers,
> Eric
>
Eric Gazoni
this might help get you started:
from openpyxl.workbook import Workbook
from openpyxl.chart import BarChart, Serie, Reference
def make_chart():
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row = i, column = 0).value = i**2
chart = BarChart()
chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
ws.add_chart(chart)
wb.save('my_workbook.xlsx')
make_chart()
Works fine for me, except the chart is stuck in the top-left corner of
the worksheet under OOCalc, so your mileage may vary.
Cheers,
Eric
Le 25/10/11 20:51, nil...@googlemail.com a �crit :
nil...@googlemail.com
Is it possible to create stacked column bars with openpyxl ?
Cheers,
Nils
On Oct 25, 10:53 pm, Eric Gazoni <eric.gaz...@gmail.com> wrote:
> Hey,
>
> this might help get you started:
>
> from openpyxl.workbook import Workbook
> from openpyxl.chart import BarChart, Serie, Reference
>
> def make_chart():
> wb = Workbook()
> ws = wb.get_active_sheet()
> ws.title = u'data'
> for i in range(10):
> ws.cell(row = i, column = 0).value = i**2
> chart = BarChart()
> chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
> ws.add_chart(chart)
>
> wb.save('my_workbook.xlsx')
>
> make_chart()
>
> Works fine for me, except the chart is stuck in the top-left corner of
> the worksheet under OOCalc, so your mileage may vary.
>
> Cheers,
> Eric
>
Eric
I've been looking closer to the charts module, apparently it's not possible to do so for now, but it would be easy to build it.
Is it an urgent need or can it wait a few days ? Because I'm already in the middle of something for now :p
If it can wait, then just add a new ticket to the bug tracker, and I'll take care of it when I have some time.
Cheers,
Eric
nil...@googlemail.com
I have filed a ticket.
https://bitbucket.org/ericgazoni/openpyxl/issue/81/support-of-stacked-column-bar-charts
Thank you very much.
BTW, how can I modify the color of the bars ?
Nils
On 26 Okt., 13:00, Eric <eric.gaz...@gmail.com> wrote:
> Hi,
> I've been looking closer to the charts module, apparently it's not possible
> to do so for now, but it would be easy to build it.
>
> Is it an urgent need or can it wait a few days ? Because I'm already in the
> middle of something for now :p
>
> If it can wait, then just add a new ticket to the bug tracker, and I'll take
> care of it when I have some time.
>
> Cheers,
> Eric
>
Eric Gazoni
from the openpyxl.style module, then define your color as :
c = Color('FFFFFF')
and then apply it to your serie this way:
...
s = Serie(values=values, color=c)
...
Let me know if this works (I'm not too familiar with charts actually :p)
Cheers,
Eric
Le 26/10/11 19:06, nil...@googlemail.com a �crit :
nil...@googlemail.com
from openpyxl.chart import BarChart, Serie, Reference
def make_chart():
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row = i, column = 0).value = i**2
chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),color=c))
wb.save('my_workbook.xlsx')
make_chart()
File "/usr/lib64/python2.6/xml/etree/ElementTree.py", line 830, in
_escape_attrib
_raise_serialization_error(text)
File "/usr/lib64/python2.6/xml/etree/ElementTree.py", line 777, in
_raise_serialization_error
"cannot serialize %r (type %s)" % (text, type(text).__name__)
TypeError: cannot serialize 'FFFFFF' (type Color)
On 26 Okt., 20:16, Eric Gazoni <eric.gaz...@gmail.com> wrote:
> Well, I have not tested it yet, but apparently, you can import Color
> from the openpyxl.style module, then define your color as :
>
> c = Color('FFFFFF')
>
> and then apply it to your serie this way:
>
> ...
> s = Serie(values=values, color=c)
> ...
>
> Let me know if this works (I'm not too familiar with charts actually :p)
>
> Cheers,
> Eric
>
> > Hi Eric,
>
> > I have filed a ticket.
Damian Dimmich
I'm aware that there have been a few mails about this & some tickets
created, but I'm looking to read in some style information from an excel
sheet.
As far as I can tell individual cell styles aren't currently being read
and defaults are put into places.
I have been trying to figure out where I would need to start looking to
read in style information for cells. Any pointers would be helpful -
I'm still familiarizing myself with the code. Would you be able to
point me at where I should start? As this work is for a client I hope I
can make some headway on this and contribute a few patches!
Thanks for all the good work on openpyxl to date!
Damian
Eric Gazoni
the styles is a fairly complex beast :)
You're looking for a way to get the styles from the XML content of a workbook right ? There are two files to look after:
- in styles.xml, you'll get the definitions of those styles
- in sheetXYZ.xml you'll get the number of the style associated with each cell (if any)
What's a bit tricky, is that styles are read from the XML file in
the order they're written, which means style 0 is the first style
in the list, and so on.
For example, let's take the "empty-with-styles.xlsx" file in
openpyxl/tests/test_data/genuine:
in xl/worksheets/sheet1.xml you'll find this:
<row r="3" spans="1:1">
<c r="A3" s="4">
<v>3.14</v>
</c>
</row>
s="4" means you'll have to look at style #5 in the list in
xl/styles.xml:
<cellXfs count="5">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"
xfId="0"/>
<xf numFmtId="10" fontId="0" fillId="0" borderId="0"
xfId="0" applyNumberFormat="1"/>
<xf numFmtId="14" fontId="0" fillId="0" borderId="0"
xfId="0" applyNumberFormat="1"/>
<xf numFmtId="20" fontId="0" fillId="0" borderId="0"
xfId="0" applyNumberFormat="1"/>
<xf numFmtId="2" fontId="0" fillId="0" borderId="0"
xfId="0" applyNumberFormat="1"/>
</cellXfs>
This tells us it's using
- number format "2"
- font "0"
- fill "0"
- border "0"
- xf "0"
Number format "2" is ... not in the styles.xml, because it's a
built-in number format, so you'll have to read through a 3500
pages PDF file to know it means ... "0.00", it's the basic
numeric format.
Then, the font used is described in the styles.xml, it's the
first (and only) one defined: Calibri 11pt
<fonts count="1">
<font>
<sz val="11"/>
<color theme="1"/>
<name val="Calibri"/>
<family val="2"/>
<scheme val="minor"/>
</font>
</fonts>
Fill is the first fill defined:
<fills count="2">
<fill>
<patternFill patternType="none"/>
</fill>
<fill>
<patternFill patternType="gray125"/>
</fill>
</fills>
Border is also the first defined, and looks like no border at
all:
<borders count="1">
<border>
<left/>
<right/>
<top/>
<bottom/>
<diagonal/>
</border>
</borders>
Xf is the first Xf defined and it's the normal style:
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
So now, to read the proper values into openpyxl, I think it would
require some reverse-engineering of many Excel files with
different styles, fonts, borders and fills, to see which XML
attributes and entities are modified/created for each kind of
style, and which values correspond to each desired style.
That's some of a big job, and that's mainly why it remained
untouched for so long. However, I assume the PHPExcel team (which
is great) has already done most (if not all) of this work, then it
could be possible to get the structures and values from the
PHPExcel source instead of re-inventing the wheel.
Here it is, hope this helps, I'm not fully sure about the Xf id's
though it's been some time I've read the OOXML specs ;-)
Cheers,
Eric
dariusz....@gmail.com
Charlie Clark
> Hi Erci,
> I have just found this thread. I use your library in my day-to-day tasks,
> thank you for your work! I am in need of creating charts in Excel file.
> However running following code
> from openpyxl.workbook import Workbook
> from openpyxl.chart import BarChart, Serie, Reference
> def make_chart():
> wb = Workbook()
> ws = wb.get_active_sheet()
> ws.title = 'data'
> for i in range(10):
> ws.cell(row = i, column = 0).value = i**2
> chart = BarChart()
> chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
> ws.add_chart(chart)
> wb.save('my_workbook.xlsx')
> make_chart()
fixed in my pull request.
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
dariusz....@gmail.com
Charlie Clark
> Hi Charlie,
> after installation of your pull request all is working fine! Thank you!