Python Excel

Excel

Spreadsheets

CSV files - use the standard csv library
Microsoft Excel files (various versions and formats)
Open Office / Libre Office Calc

Python Excel

Python Excel
openpyxl
xlsxwriter
xlrd
xlwt
xlutils using xlrd and xlwt. Mostly obsolete.

Create an Excel file from scratch

Workbook
active
save
column_dimensions

import openpyxl
import datetime

wb = openpyxl.Workbook()

ws = wb.active

ws['A1'] = 42

ws['A2'] = datetime.datetime.now()
#ws.column_dimensions['A'].width = 20.0

wb.save("first.xlsx")

Worksheets in Excel

create_sheet

import openpyxl
import datetime

wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 42
ws.title = "First"

ws2 = wb.create_sheet()
ws2.title = "Second sheet"
ws2['A1'] = datetime.datetime.now()
ws2.sheet_properties.tabColor = "1072BA"

wb.save("two_worksheets.xlsx")

Add expressions to Excel

Nothing special needed.

import openpyxl
import datetime

wb = openpyxl.Workbook()

ws = wb.active

ws['A1'] = 19
ws['A2'] = 23

ws['A3'] = "=A1+A2"

wb.save("expression.xlsx")

Format field

import openpyxl
import datetime

wb = openpyxl.Workbook()

ws = wb.active

ws['A1'] = 123456.78
ws['A2'] = 123456.78
ws['A3'] = 123456.78
ws['A4'] = -123456.78
ws['A5'] = datetime.datetime.now()
ws.column_dimensions['A'].width = 20.0

ws['A2'].number_format = '0.00E+00'
ws['A3'].number_format = '#,##0_);[RED](#,##0)'
ws['A4'].number_format = '#,##0_);[RED](#,##0)'

wb.save("format.xlsx")

Number series and chart

import openpyxl

wb = openpyxl.Workbook()

ws = wb.active
ws.title = "Chart"

a = ["First", 20, 28, 30, 37, 18, 47]
b = ["Second", 35, 30, 40, 40, 38, 35]

# write them as columns
for i in range(len(a)):
    ws.cell(row=i+1, column=1).value = a[i]
    ws.cell(row=i+1, column=2).value = b[i]

lc = openpyxl.chart.LineChart()
lc.title = "Two Lines Chart"
#lc.style=13
data = openpyxl.chart.Reference(ws,
                                min_col=1,
                                min_row=1,
                                max_col=2,
                                max_row=len(a))
lc.add_data(data, titles_from_data=True)

ws.add_chart(lc, "D1")
wb.save("chart.xlsx")

Read Excel file

import openpyxl
wb = openpyxl.load_workbook(filename = 'chart.xlsx')
for ws in wb.worksheets:
    print(ws.title)

ws = wb.worksheets[0]
print(ws['A1'].value)

Update Excel file

import openpyxl

wb = openpyxl.load_workbook(filename = 'chart.xlsx')
for ws in wb.worksheets:
    print(ws.title)

ws = wb.worksheets[0]
c = ["Third", 40, 20, 35, 25, 20, 35]

for i in range(len(c)):
    ws.cell(row=i+1, column=3).value = c[i]

lc = openpyxl.chart.LineChart()
lc.title = "Three Lines Chart"
data = openpyxl.chart .Reference(ws,
                                 min_col=1,
                                 min_row=1,
                                 max_col=3,
                                 max_row=len(c))
lc.add_data(data, titles_from_data=True)

ws.add_chart(lc, "H15")

wb.save("chart.xlsx")

Barchart

import openpyxl
import random
from openpyxl.chart import BarChart, Series, Reference

wb = openpyxl.Workbook()
ws = wb.active

randomList1 = []
randomList2 = []
randomList3 = []
randomList4 = []

for i in range(0,12):
    randomList1.append(random.randint(0,100))
    randomList2.append(random.randint(0, 100))
    randomList3.append(random.randint(0, 100))
    randomList4.append(random.randint(0, 100))
randomList1.insert(0,"Bananas")
randomList2.insert(0,"Pears")
randomList3.insert(0,"Apples")
randomList4.insert(0,"Kiwis")

print(f"""Random number list1: {randomList1}
        Random number list2: {randomList2}
        Random number list3: {randomList3}
        Random number list4: {randomList4}""")

months = ['Fruit','Jan', 'Feb', 'March', 'April', 'May', 'June', 'July', 'Aug', 'Sept', 'Oct', 'Nov', 'Dec']

rows = [
    months,
    randomList1,
    randomList2,
    randomList3,
    randomList4,
]

for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "Fruit Count per Month"
chart1.y_axis.title = 'Fruit Number'
chart1.x_axis.title = 'Fruit Type'

data = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=13)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A11")

wb.save("row_10.xlsx")

Exercise: Excel

Create a series of 10 random numbers between 1 and 100 and save them in an Excel file in a column.
Create a graph showing the values.
Add a second series of 10 random numbers, add them to the Excel file as a second column next to the first one.
Add a 3rd colum containing the average of the first two columns.
Update the graph to include all 3 number serieses

Keyboard shortcuts

Python Excel