Show FOSDEM: openpyxl

Presenting how to easily use Excel as a container for typed tabular data in Python, performance hints, and a progress status of the library after 3 years of development.

Transcript

1. FOSDEM 2014 Show FOSDEM: openpyxl 1. Eric Gazoni - February 2nd, 2014
2. FOSDEM 2014 | Hi @ericgazoni
3. FOSDEM 2014 | Hi
4. FOSDEM 2014 | What is openpyxl ? Openpyxl in just 71 slides 1.
5. FOSDEM 2014 | What is openpyxl ? A Python library to read and write XLSX files
6. FOSDEM 2014 | What is openpyxl ? A Python library to read and write XLSX files
7. FOSDEM 2014 | What is openpyxl ? A Python library to read and write XLSX files
8. FOSDEM 2014 | What is openpyxl ? Q: Will it work on good ol’ XLS files ? ! A: no, but there is xlrd / xlwt for that
9. FOSDEM 2014 | What is openpyxl ? Emerged in 2010 from the lack of a viable alternative (at the time at least)
10. FOSDEM 2014 | What is openpyxl ? Shaped by the needs of the scientific world
11. FOSDEM 2014 | What is openpyxl ? Inspired by the sad years of my life when I was writing Excel VBA for a living
12. FOSDEM 2014 | What is openpyxl ? OOXML is a good alternative to CSV …
13. FOSDEM 2014 | What is openpyxl ? … well, ok, it’s an insane, nonsensical format …
14. FOSDEM 2014 | What is openpyxl ? … but hey, everyone use it these days
15. FOSDEM 2014 | What is openpyxl ? Typed (numbers, dates)
16. FOSDEM 2014 | What is openpyxl ? Packaged
17. FOSDEM 2014 | What is openpyxl ? Compressed
18. FOSDEM 2014 | What is openpyxl ? Interoperable (decimal separators ?)
19. FOSDEM 2014 | The awesomesauce 3 reasons why openpyxl is your friend 1.
20. FOSDEM 2014 | The awesomesauce Auto type casting (a.k.a. data type guessing) ! (questionable feature)
21. FOSDEM 2014 | The awesomesauce Object-oriented (nested) data structure ! Maps to the internal Excel representation
22. FOSDEM 2014 | The awesomesauce O(1) RAM usage * (* in optimized mode)
23. FOSDEM 2014 | Words are cheap, show us some code ! An openpyxl crash course 1.
24. FOSDEM 2014 | Words are cheap, show us some code ! Basic usage! (https://openpyxl.readthedocs.org)
25. FOSDEM 2014 | Words are cheap, show us some code ! >>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.create_sheet() >>> ws['A4'] = 4 >>> print ws['A1':'C2'] ((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>), (<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>)) >>> wb.save('my_workbook.xlsx')
26. FOSDEM 2014 | Words are cheap, show us some code !
27. FOSDEM 2014 | The awesomesauce import datetime from openpyxl import Workbook ! wb = Workbook() ws = wb.active # set date using a Python datetime ws['A1'] = datetime.datetime(2010, 7, 21) ! print ws['A1'].style.number_format.format_code # returns 'yyyy-mm-dd' ! # set percentage using a string followed by the percent sign ws['B1'] = '3.14%' ! print ws['B1'].value # returns 0.031400000000000004 ! print ws['B1'].style.number_format.format_code # returns '0%'
28. FOSDEM 2014 | Words are cheap, show us some code ! Adding charts
29. FOSDEM 2014 | Words are cheap, show us some code ! >>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.create_sheet() >>> for i in range(10): >>> ws.append([i, i]) >>> chart = ScatterChart() >>> xvalues = Reference(ws, (0, 1), (9, 1)) >>> values = Reference(ws, (0, 0), (9, 0)) >>> series = Series(values, xvalues=xvalues) >>> chart.append(series) >>> ws.add_chart(chart)
30. FOSDEM 2014 | Words are cheap, show us some code !
31. FOSDEM 2014 | Words are cheap, show us some code ! Reading an existing file
32. FOSDEM 2014 | Words are cheap, show us some code ! >>> from openpyxl import load_workbook >>> wb2 = load_workbook('test.xlsx') >>> print wb2.get_sheet_names() ['Sheet2', 'New Title', 'Sheet1']
33. FOSDEM 2014 | Ok, but is it fast ? New study shows fast software is sexy 1.
34. FOSDEM 2014 | Ok, but is it fast ? 3 possible answers
35. FOSDEM 2014 | Ok, but is it fast ? « No », « no » and «eeeh, good enough »
36. FOSDEM 2014 | Ok, but is it fast ? Default mode keeps everything in RAM = memory hog
37. FOSDEM 2014 | Ok, but is it fast ? Optimized mode still spends a lot of time in Python = With more user-friendliness comes more CPU cycles
38. FOSDEM 2014 | Ok, but is it fast ? However, benchmarks are not real life
39. FOSDEM 2014 | Ok, but is it fast ? Known to work well in production environments
40. FOSDEM 2014 | Ok, but is it fast ? But that’s not done yet !
41. FOSDEM 2014 | Ok, but is it fast ? Tuning and benchmarking all the time
42. FOSDEM 2014 | Ok, but is it fast ? If you only write files, try @jmcnamara13’s excellent library XlsxWriter (https://xlsxwriter.readthedocs.org)
43. FOSDEM 2014 | Pimp your ride Slow code killed my startup 1.
44. FOSDEM 2014 | Pimp your ride A: you’re just having fun with a reasonably sized workbook ! Hint: you’re pretty safe
45. FOSDEM 2014 | Pimp your ride B: you’re importing/exporting gazillions of rows/columns
46. FOSDEM 2014 | Pimp your ride Use None instead of ‘’
47. FOSDEM 2014 | Pimp your ride Use row-oriented logic (columns are evil) ! def transpose_columns(cols): return map(lambda *a: list(a), *cols)
48. FOSDEM 2014 | Pimp your ride Don’t hesitate to use optimized modes ! (even if you have to rewrite some code)
49. FOSDEM 2014 | A Co-op game Raise your code quality by being friendly 1.
50. FOSDEM 2014 | A Co-op game Openpyxl is vastly based on reverse-engineering
51. FOSDEM 2014 | A Co-op game Lots of similarly licensed libraries, making cooperation easier
52. FOSDEM 2014 | A Co-op game PHPExcel did the seminal work … ! (Thanks @Mark_Baker)
53. FOSDEM 2014 | A Co-op game … but we diverged in virtually every aspect by now ! (Thanks @ericgazoni) (You’re welcome)
54. FOSDEM 2014 | A Co-op game Neurodebian team made it fly ! (Thanks @yarikoptic and @peircej)
55. FOSDEM 2014 | A Co-op game Adam Morris made it pretty
56. FOSDEM 2014 | A Co-op game John Bovey taught it VBA
57. FOSDEM 2014 | A Co-op game (kidding, we do not support VBA, we just stopped throwing it away)
58. FOSDEM 2014 | A Co-op game Heikki Junes prepared it for the future
59. FOSDEM 2014 | A Co-op game Charlie Clark made it strong
60. FOSDEM 2014 | A Co-op game Commits are roaring
61. FOSDEM 2014 | A Co-op game Contributors are flowing in
62. FOSDEM 2014 | A Co-op game • Pandas • PsycoPy • CubicWeb (timeseries cube) • Tablib • Khan Academy • Python (x,y) • …
63. FOSDEM 2014 | Cool story, now what’s next ? Would you kindly sign this NDA ? 1.
64. FOSDEM 2014 | Cool story, now what’s next ? Target for 1.9: bug fixes ! Expected in April 2014
65. FOSDEM 2014 | Cool story, now what’s next ? 2.0 is coming with backward-incompatible changes ! Expected in 2014
66. FOSDEM 2014 | Cool story, now what’s next ? Used by many people, time to communicate changes responsively
67. FOSDEM 2014 | Cool story, now what’s next ? Better, faster, stronger styles handling
68. FOSDEM 2014 | Cool story, now what’s next ? More Pythonic API (goodbye PHP)
69. FOSDEM 2014 | Cool story, now what’s next ? lxml as default backend ? ! Benchmarks will tell
70. FOSDEM 2014 | Cool story, now what’s next ? Validate all XML before it hits Excel (and displays embarrassing error messages)
71. FOSDEM 2014 | Cool story, now what’s next ? 60+ open bugs (want to step into open source development ?)
72. FOSDEM 2014 | Moar ? http://openpyxl.readthedocs.org/en/latest/#how-to-contribute-code https://groups.google.com/forum/#!forum/openpyxl-users Thanks !