Tweet
Tweet

Slide 1

Slide 1 text

Creating Excel files with Python and XlsxWriter John McNamara Emutex Ltd

Slide 2

Slide 2 text

whoami John McNamara Software developer at Emutex Ltd http://www.emutex.com http://github.com/jmcnamara

Slide 3

Slide 3 text

Why Excel • Bosses like it • Useful as a data source • More useful with formatting • Input/output source for Pandas • Can be misused: Excel as a database

Slide 4

Slide 4 text

Available Python modules • csv.py Readers and writers in core libs • xlwt/xlrd Mature, stable modules, mainly XLS support • openpyxl Reads and writes XLSX files • xlsxwriter New module from early 2013

Slide 5

Slide 5 text

Excel 2003 : xls Excel 2007 : xlsx Excel File Formats xlwt openpyxl xlsxwriter xlrd Read Write

Slide 6

Slide 6 text

XlsxWriter • Write Excel XLSX files only • Doesn’t read or re-write Excel files • Adds many new features • Uses core modules only • Python 2.5, 2.6, 2.7, 3.1, 3.2, 3.3, PyPy, Jython

Slide 7

Slide 7 text

Why another module • Why not? • Other modules support some but not all features • XlsxWriter adds support for: charts, autofilters, tables, data validation, merged cells, rich text, conditional formatting, defined names, images, cell comments, sparklines, outlines

Slide 8

Slide 8 text

Getting Started • Install: $ sudo pip install xlsxwriter • Clone or fork: $ git clone [email protected]:jmcnamara/XlsxWriter.git $ cd XlsxWriter $ make test $ sudo python setup.py install • Read: https://xlsxwriter.readthedocs.org

Slide 9

Slide 9 text

Hello World.xlsx

Slide 10

Slide 10 text

Hello World.xlsx import xlsxwriter workbook = xlsxwriter.Workbook('hello_world.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 'Hello world') workbook.close()

Slide 11

Slide 11 text

Hello World.xlsx import xlsxwriter workbook = xlsxwriter.Workbook('hello_world.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 'Hello world') workbook.close()

Slide 12

Slide 12 text

Hello World.xlsx import xlsxwriter workbook = xlsxwriter.Workbook('hello_world.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 'Hello world') worksheet.write(2, 1, 'Hello world') workbook.close()

Slide 13

Slide 13 text

Hello World.xlsx import xlsxwriter workbook = xlsxwriter.Workbook('hello_world.xlsx') worksheet = workbook.add_worksheet() worksheet.write(0, 0, 'Hello world') worksheet.write(2, 1, 'Hello world') worksheet.write('C5', 'Hello world') workbook.close()

Slide 14

Slide 14 text

Cell Formatting

Slide 15

Slide 15 text

Cell Formatting import xlsxwriter workbook = xlsxwriter.Workbook('formatting.xlsx') worksheet = workbook.add_worksheet() italic = workbook.add_format({'italic': True}) bold = workbook.add_format({'bold': True, 'font_color': '#9CB640'}) worksheet.write(0, 0, 'Hello') workbook.close()

Slide 16

Slide 16 text

Cell Formatting import xlsxwriter workbook = xlsxwriter.Workbook('formatting.xlsx') worksheet = workbook.add_worksheet() italic = workbook.add_format({'italic': True}) bold = workbook.add_format({'bold': True, 'font_color': '#9CB640'}) worksheet.write(0, 0, 'Hello') worksheet.write(1, 0, 'Hello', italic) workbook.close()

Slide 17

Slide 17 text

Cell Formatting import xlsxwriter workbook = xlsxwriter.Workbook('formatting.xlsx') worksheet = workbook.add_worksheet() italic = workbook.add_format({'italic': True}) bold = workbook.add_format({'bold': True, 'font_color': '#9CB640'}) worksheet.write(0, 0, 'Hello') worksheet.write(1, 0, 'Hello', italic) worksheet.write(2, 0, 'Hello', bold) workbook.close()

Slide 18

Slide 18 text

Cell Formatting set_font_name() set_font_size() set_font_color() set_bold() set_italic() set_underline() set_font_strikeout() set_font_script() set_num_format() set_locked() set_hidden() set_align() set_center_across() set_text_wrap() set_rotation() set_indent() set_shrink() set_text_justlast() set_pattern() set_bg_color() set_fg_color() set_border() set_bottom() set_top() set_left() set_right() set_border_color() set_bottom_color() set_top_color() set_left_color() set_right_color()

Slide 19

Slide 19 text

Types

Slide 20

Slide 20 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') workbook.close()

Slide 21

Slide 21 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') workbook.close()

Slide 22

Slide 22 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') workbook.close()

Slide 23

Slide 23 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) workbook.close()

Slide 24

Slide 24 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) workbook.close()

Slide 25

Slide 25 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) worksheet.write(4, 0, date(2013, 10, 13), date_format) workbook.close()

Slide 26

Slide 26 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) worksheet.write(4, 0, date(2013, 10, 13), date_format) worksheet.write(5, 0, '=PI()') workbook.close()

Slide 27

Slide 27 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) worksheet.write(4, 0, date(2013, 10, 13), date_format) worksheet.write(5, 0, '=PI()') worksheet.write(6, 0, 'http://python.com') workbook.close()

Slide 28

Slide 28 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) worksheet.write(4, 0, date(2013, 10, 13), date_format) worksheet.write(5, 0, '=PI()') worksheet.write(6, 0, 'http://python.com') worksheet.write(7, 0, True) workbook.close()

Slide 29

Slide 29 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write(0, 0, 'Hello world') worksheet.write(1, 0, 'Это фраза на русском!') worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) worksheet.write(4, 0, date(2013, 10, 13), date_format) worksheet.write(5, 0, '=PI()') worksheet.write(6, 0, 'http://python.com') worksheet.write(7, 0, True) workbook.close()

Slide 30

Slide 30 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write (0, 0, 'Hello world') worksheet.write (1, 0, 'Это фраза на русском!') worksheet.write (2, 0, 123) worksheet.write (3, 0, 123.456) worksheet.write (4, 0, date(2013, 10, 13), date_format) worksheet.write (5, 0, '=PI()') worksheet.write (6, 0, 'http://python.com') worksheet.write (7, 0, True) workbook.close()

Slide 31

Slide 31 text

Types from datetime import date import xlsxwriter workbook = xlsxwriter.Workbook('types.xlsx') worksheet = workbook.add_worksheet() date_format = workbook.add_format({'num_format': 'd mmm yyyy'}) worksheet.write_string (0, 0, 'Hello world') worksheet.write_string (1, 0, 'Это фраза на русском!') worksheet.write_number (2, 0, 123) worksheet.write_number (3, 0, 123.456) worksheet.write_datetime(4, 0, date(2013, 10, 13), date_format) worksheet.write_formula (5, 0, '=PI()') worksheet.write_url (6, 0, 'http://python.com') worksheet.write_boolean (7, 0, True) workbook.close()

Slide 32

Slide 32 text

Formulas

Slide 33

Slide 33 text

Formulas worksheet.write_formula('A1', '=1+2') worksheet.write_formula('A2', '=A1') worksheet.write_formula('A3', '{=SUM(B1:C1*B2:C2)}') worksheet.write_formula('A4', '=VLOOKUP("Acme", A2:D6, 3, FALSE)')

Slide 34

Slide 34 text

Images

Slide 35

Slide 35 text

Images import xlsxwriter workbook = xlsxwriter.Workbook('image.xlsx') worksheet = workbook.add_worksheet() worksheet.insert_image(0, 0, 'logo.png') workbook.close()

Slide 36

Slide 36 text

Conditional Formatting

Slide 37

Slide 37 text

Conditional Formatting import xlsxwriter wb = xlsxwriter.Workbook('conditional_format.xlsx') ws = wb.add_worksheet() high = wb.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) low = wb.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'}) data = [ [88, 25, 33, 23, 67, 13], [24, 100, 20, 88, 29, 33], [6, 57, 88, 28, 10, 26], [73, 78, 1, 96, 26, 45], [36, 54, 22, 66, 81, 90], ] for row, row_data in enumerate(data): ws.write_row(row, 0, row_data) ws.conditional_format('A1:F5', {'type': 'cell', 'criteria': '>=', 'value': 50, 'format': high}) ws.conditional_format('A1:F5', {'type': 'cell', 'criteria': '<', 'value': 50, 'format': low}) wb.close()

Slide 38

Slide 38 text

Charts

Slide 39

Slide 39 text

Charts Area stacked percent_stacked Bar stacked percent_stacked Column stacked percent_stacked Line Pie Radar with_markers filled Scatter straight_with_markers straight smooth_with_markers smooth Stock

Slide 40

Slide 40 text

Charts import xlsxwriter workbook = xlsxwriter.Workbook('chart.xlsx') worksheet = workbook.add_worksheet() # Add the worksheet data to be plotted. data = [10, 40, 50, 20, 10, 50] worksheet.write_column('A1', data) # Create a new chart object. chart = workbook.add_chart({'type': 'area'}) # Add a series to the chart. chart.add_series({'values': '=Sheet1!$A$1:$A$6'}) # Insert the chart into the worksheet. worksheet.insert_chart('C1', chart) workbook.close()

Slide 41

Slide 41 text

Charts chart = workbook.add_chart({'type': 'area'})

Slide 42

Slide 42 text

Charts chart = workbook.add_chart({'type': 'bar'})

Slide 43

Slide 43 text

Charts chart = workbook.add_chart({'type': 'column'})

Slide 44

Slide 44 text

Charts chart = workbook.add_chart({'type': 'line'})

Slide 45

Slide 45 text

Charts chart = workbook.add_chart({'type': 'pie'})

Slide 46

Slide 46 text

Charts chart = workbook.add_chart({'type': 'radar'})

Slide 47

Slide 47 text

Charts • Configurability

Slide 48

Slide 48 text

Charts Stacked chart with captions

Slide 49

Slide 49 text

Charts Change chart styles

Slide 50

Slide 50 text

Charts Add trendlines to charts

Slide 51

Slide 51 text

Charts Format data points

Slide 52

Slide 52 text

Charts Secondary axes

Slide 53

Slide 53 text

Autofilters

Slide 54

Slide 54 text

Autofilters import xlsxwriter workbook = xlsxwriter.Workbook('autofilter.xlsx') worksheet = workbook.add_worksheet() # Add a format for the headers. header_format = workbook.add_format({'bold': 1, 'bg_color': '#C6EFCE'}) # Populate the worksheet data. # See the xlsxwriter docs for a full example. ... # Make the columns wider. worksheet.set_column('A:D', 12) # Format the header row. worksheet.set_row(0, 20, header_format) # Set the autofilter. worksheet.autofilter('A1:D51') workbook.close()

Slide 55

Slide 55 text

Tables

Slide 56

Slide 56 text

Tables • Group a range of cells into a single entity • Apply a uniform formatting across the cells • Refer to the table in formulas worksheet.add_table('B3:F7', {options})

Slide 57

Slide 57 text

Data Validation

Slide 58

Slide 58 text

Data Validation • Restrict data entry to certain ranges • Raise errors/warning within Excel • Allow selection from drop down lists data_validation( 'B25', {'validate': 'integer', 'criteria': 'between', 'minimum': 1, 'maximum': 10})

Slide 59

Slide 59 text

Cell Comments

Slide 60

Slide 60 text

Cell Comments • Add comments to cells worksheet.write('A1', 'Hello') worksheet.write_comment('A1', 'This is a comment')

Slide 61

Slide 61 text

Sparklines

Slide 62

Slide 62 text

Sparklines • Mini charts within cells to show trends • Invented by Edward Tufte

Slide 63

Slide 63 text

Code All the Things! • Lots of features

Slide 64

Slide 64 text

Code All the Things! • Lots of features • Useful when you need them • Ignore them when you don’t • Plenty of examples and documentation to get you started

Slide 65

Slide 65 text

How does it work

Slide 66

Slide 66 text

How does it work • 20% of a studio audience guessed Witchcraft

Slide 67

Slide 67 text

How does it work • 20% of a studio audience guessed Witchcraft • Actually a collection of XML files in a Zip file • Can be unzipped using standard utilities • Even modified in-place (if you are desperate)

Slide 68

Slide 68 text

How does it work $ unzip -o -d hello_world hello_world.xlsx Archive: hello_world.xlsx inflating: hello_world/[Content_Types].xml inflating: hello_world/_rels/.rels inflating: hello_world/docProps/app.xml inflating: hello_world/docProps/core.xml inflating: hello_world/xl/sharedStrings.xml inflating: hello_world/xl/styles.xml inflating: hello_world/xl/workbook.xml inflating: hello_world/xl/_rels/workbook.xml.rels inflating: hello_world/xl/theme/theme1.xml inflating: hello_world/xl/worksheets/sheet1.xml

Slide 69

Slide 69 text

How does it work $ unzip -o -d hello_world hello_world.xlsx Archive: hello_world.xlsx inflating: hello_world/[Content_Types].xml inflating: hello_world/_rels/.rels inflating: hello_world/docProps/app.xml inflating: hello_world/docProps/core.xml inflating: hello_world/xl/sharedStrings.xml inflating: hello_world/xl/styles.xml inflating: hello_world/xl/workbook.xml inflating: hello_world/xl/_rels/workbook.xml.rels inflating: hello_world/xl/theme/theme1.xml inflating: hello_world/xl/worksheets/sheet1.xml

Slide 70

Slide 70 text

How does it work $ unzip -o -d hello_world hello_world.xlsx Archive: hello_world.xlsx inflating: hello_world/[Content_Types].xml inflating: hello_world/_rels/.rels inflating: hello_world/docProps/app.xml inflating: hello_world/docProps/core.xml inflating: hello_world/xl/sharedStrings.xml inflating: hello_world/xl/styles.xml inflating: hello_world/xl/workbook.xml inflating: hello_world/xl/_rels/workbook.xml.rels inflating: hello_world/xl/theme/theme1.xml inflating: hello_world/xl/worksheets/sheet1.xml

Slide 71

Slide 71 text

How does it work $ unzip -o -d hello_world hello_world.xlsx Archive: hello_world.xlsx inflating: hello_world/[Content_Types].xml inflating: hello_world/_rels/.rels inflating: hello_world/docProps/app.xml inflating: hello_world/docProps/core.xml inflating: hello_world/xl/sharedStrings.xml inflating: hello_world/xl/styles.xml inflating: hello_world/xl/workbook.xml inflating: hello_world/xl/_rels/workbook.xml.rels inflating: hello_world/xl/theme/theme1.xml inflating: hello_world/xl/worksheets/sheet1.xml $ xmllint --format hello_world/xl/worksheets/sheet1.xml

Slide 72

Slide 72 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml

Slide 73

Slide 73 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 74

Slide 74 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 75

Slide 75 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 76

Slide 76 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 77

Slide 77 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 78

Slide 78 text

How does it work $ unzip -o -d hello_world hello_world.xlsx ... $ xmllint --format hello_world/xl/worksheets/sheet1.xml 0

Slide 79

Slide 79 text

How does it work • It works well • XlsxWriter does the hard work so you don’t have to

Slide 80

Slide 80 text

close() • Next time you need to write an Excel file with Python try XlsxWriter • Clone it on Github, submit issues, add stars http://github.com/jmcnamara/XlsxWriter • Read the documentation https://xlsxwriter.readthedocs.org PDF tutorial, cookbook and manual

Slide 81

Slide 81 text

Thank You John McNamara Emutex Ltd