writing to excel

Introduction to openpyxl

CSCI 350

Python Libraries  Over 150,000 libraries available.  most are free, some not free

 Lots of number crunching libraries  statistics  data visualization

 Finance

 Engineering, Computer Vision, Image Processing, Machine Learning, etc.

openpyxl  Open Source library to read and write Excel files.

 This library is not a standard part of Python. It must be added to your machine.

 The pip program (which is part of the standard Python distribution) is a package manager that will add a library to your machine.  On the command line of your computer (not inside

Python shell) run

pip install openpyxl

 See lecture notes in Blackboard for detailed installation instructions.

Excel Terminology  Workbook - the excel file

 Sheet - the sheet you are  A workbook can have several sheets.

 Remember the little tabs in the bottom left of Excel?

 You can name the sheets.

 Columns are letters: A, B, C, ...

 Rows are numbered 1, 2, 3, ...

 Cell - for example A1

Steps to Read or Write Excel data 1) Create a variable with the workbook  could be a new empty workbook  could be a workbook from a file

2) Select which sheet you want to work with  The "Active" sheet is the top sheet.

3) Read and Write cells or groups of cells

4) Save your changes to the Excel file

Lots of Demo Programs 1) Just write two cells

2) Use a for loop to write cells

3) Create a file with two sheets

4) Use a formula

5) Add a Bar Chart

# program to demonstrate creating

# a new Excel file

from openpyxl import Workbook

# create variable for the empty sheet

workbook = Workbook()

sheet = workbook.active

# write stuff into some cells

sheet["A1"] = "Hi Mom"

sheet["A2"] = 99

# save the sheet to a file

workbook.save("DemoOne.xlsx")

print ("done")

# use a for loop to create cell data

from openpyxl import Workbook

# create variables for the empty sheet

workbook = Workbook()

sheet = workbook.active

# write into 10 cells in column A

for x in range (1, 11) :

cell = "A" + str(x) # A1 A2 ... A10

sheet[cell] = x # A1=1 A2=2...

# save the sheet to a file

workbook.save("DemoTwo.xlsx")

print ("done")

# create two different sheets

from openpyxl import Workbook

# rename the default sheet

workbook = Workbook()

sheet1 = workbook.active

sheet1.title = "One"

# write something into sheet one

sheet1["A1"] = "This is sheet one"

# create a second sheet

sheet2 = workbook.create_sheet("Two")

sheet2["C3"] = "This is sheet two"

# save the sheets to a file

workbook.save("DemoThree.xlsx")

print ("done")

# demo of using a formula

from openpyxl import Workbook

#from openpyxl.utils import FORMULAE

# write numbers to cells B1:B10

workbook = Workbook()

sheet = workbook.active

for x in range(1,11) :

cell = "B" + str(x)

sheet[cell] = x

# insert the average formula

sheet["C5"] = "=AVERAGE(B1:B10)"

# save the sheet to a file

workbook.save("DemoFour.xlsx")

print ("done")

# add a bar chart

from openpyxl import Workbook

from openpyxl.chart import BarChart, Reference

# create some data to graph

workbook = Workbook()

sheet = workbook.active

sheet["A2"] = "Insurance"

sheet["A3"] = "State Farm"

sheet["A4"] = "All State"

sheet["A5"] = "Progressive"

sheet["A6"] = "USAA"

sheet["A7"] = "Farmers"

sheet["B2"] = "Customers"

sheet["B3"] = 183

sheet["B4"] = 129

sheet["B5"] = 95

sheet["B6"] = 48

sheet["B7"] = 97

# Create the Bar Chart

chart = BarChart()

data = Reference(worksheet=sheet,

min_row=2, max_row=7,

min_col=1, max_col=2)

chart.add_data(data, titles_from_data=True)

# Add category labels

categs = Reference(worksheet=sheet,

min_row=3, max_row=7,

min_col=1, max_col=1)

chart.set_categories(categs)

# insert the chart onto the sheet

sheet.add_chart(chart, "D2")

Other things you can do

 Styles ◦colors, fonts, borders, etc.

 pictures

 other types of graphs

Introduction to openpyxl

CSCI 350

Python Libraries  Over 150,000 libraries available.  most are free, some not free

 Lots of number crunching libraries  statistics  data visualization

 Finance

 Engineering, Computer Vision, Image Processing, Machine Learning, etc.

openpyxl  Open Source library to read and write Excel files.

 This library is not a standard part of Python. It must be added to your machine.

 The pip program (which is part of the standard Python distribution) is a package manager that will add a library to your machine.  On the command line of your computer (not inside

Python shell) run

pip install openpyxl

 See lecture notes in Blackboard for detailed installation instructions.

Excel Terminology  Workbook - the excel file

 Sheet - the sheet you are  A workbook can have several sheets.

 Remember the little tabs in the bottom left of Excel?

 You can name the sheets.

 Columns are letters: A, B, C, ...

 Rows are numbered 1, 2, 3, ...

 Cell - for example A1

Steps to Read or Write Excel data 1) Create a variable with the workbook  could be a new empty workbook  could be a workbook from a file

2) Select which sheet you want to work with  The "Active" sheet is the top sheet.

3) Read and Write cells or groups of cells

4) Save your changes to the Excel file

Lots of Demo Programs 1) Just write two cells

2) Use a for loop to write cells

3) Create a file with two sheets

4) Use a formula

5) Add a Bar Chart

# program to demonstrate creating

# a new Excel file

from openpyxl import Workbook

# create variable for the empty sheet

workbook = Workbook()

sheet = workbook.active

# write stuff into some cells

sheet["A1"] = "Hi Mom"

sheet["A2"] = 99

# save the sheet to a file

workbook.save("DemoOne.xlsx")

print ("done")

# use a for loop to create cell data

from openpyxl import Workbook

# create variables for the empty sheet

workbook = Workbook()

sheet = workbook.active

# write into 10 cells in column A

for x in range (1, 11) :

cell = "A" + str(x) # A1 A2 ... A10

sheet[cell] = x # A1=1 A2=2...

# save the sheet to a file

workbook.save("DemoTwo.xlsx")

print ("done")

# create two different sheets

from openpyxl import Workbook

# rename the default sheet

workbook = Workbook()

sheet1 = workbook.active

sheet1.title = "One"

# write something into sheet one

sheet1["A1"] = "This is sheet one"

# create a second sheet

sheet2 = workbook.create_sheet("Two")

sheet2["C3"] = "This is sheet two"

# save the sheets to a file

workbook.save("DemoThree.xlsx")

print ("done")

# demo of using a formula

from openpyxl import Workbook

#from openpyxl.utils import FORMULAE

# write numbers to cells B1:B10

workbook = Workbook()

sheet = workbook.active

for x in range(1,11) :

cell = "B" + str(x)

sheet[cell] = x

# insert the average formula

sheet["C5"] = "=AVERAGE(B1:B10)"

# save the sheet to a file

workbook.save("DemoFour.xlsx")

print ("done")

# add a bar chart

from openpyxl import Workbook

from openpyxl.chart import BarChart, Reference

# create some data to graph

workbook = Workbook()

sheet = workbook.active

sheet["A2"] = "Insurance"

sheet["A3"] = "State Farm"

sheet["A4"] = "All State"

sheet["A5"] = "Progressive"

sheet["A6"] = "USAA"

sheet["A7"] = "Farmers"

sheet["B2"] = "Customers"

sheet["B3"] = 183

sheet["B4"] = 129

sheet["B5"] = 95

sheet["B6"] = 48

sheet["B7"] = 97

# Create the Bar Chart

chart = BarChart()

data = Reference(worksheet=sheet,

min_row=2, max_row=7,

min_col=1, max_col=2)

chart.add_data(data, titles_from_data=True)

# Add category labels

categs = Reference(worksheet=sheet,

min_row=3, max_row=7,

min_col=1, max_col=1)

chart.set_categories(categs)

# insert the chart onto the sheet

sheet.add_chart(chart, "D2")

Other things you can do

 Styles ◦colors, fonts, borders, etc.

 pictures

 other types of graphs