Python sales

Sheet1

Reading Data from Excel Spreadsheets

Program Zero - Just read a sheet's title, its dimensions, and cell A1

# demo program for reading Excel data

from openpyxl import load_workbook

workbook = load_workbook ("demofile.xlsx", read_only=True)

sheet = workbook.active

print ("Sheet Title =", sheet.title) # title of this sheet

print ("Cell A1 =", sheet["A1"]) # not really want you want

print ("Cell A1's value =", sheet["A1"].value) # you want the value

# which cells are used?

print ("The sheet uses cells",sheet.calculate_dimension())

Program One - Use a for loop to read every cell of a spreadsheet

# for loop to read all cells

# each cell has three parts: column,row,value

from openpyxl import load_workbook

workbook = load_workbook ("demofile.xlsx", read_only=True)

sheet = workbook.active

for row in sheet.rows: # for each row used by this sheet

for cell in row: # get each cell

print("Row", cell.row, end=" ")

print("Column",cell.column, end=" = ")

print(cell.value)

Program Two - Read just values

Notice when the program above is run it says cell C3 contains a formula. If you want just the

values then change the load command to:

workbook = load_workbook ("demofile.xlsx",

read_only=True,

data_only=True)

Instead of reading every row, we can pick which rows and columns that we want. We can either

read row-by-row or column-by-column.

Given the spreadsheet:

Why Hello There

12 34 56

Program Three creates:

---- Next Row ----

Why

Hello

There

---- Next Row ----

12

34

56

Program Four creates:

---- Next Column ----

Why

12

---- Next Column ----

Hello

34

---- Next Column ----

There

56

Program Three # demo of iter_rows

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx")

sheet = workbook.active

# grab rows 1-3 and all 7 columns

cellData = sheet.iter_rows(min_row=1, max_row=2,

min_col=1, max_col=3)

# iterate through each row

for nextRow in cellData :

print ("---- Next Row ----")

for cell in nextRow :

print(cell.value)

Program Four # demo of iter_cols

from openpyxl import load_workbook

workbook = load_workbook ("SalesData.xlsx")

sheet = workbook.active

# grab rows 1-3 and all 7 columns

cellData = sheet.iter_cols(min_row=1, max_row=2,

min_col=1, max_col=3)

# iterate through each row

for nextCol in cellData :

print ("---- Next Column ----")

for cell in nextCol :

print(cell.value)

Notice that iter_rows and iter_cols gives us back a bunch of data. It is a bit tricky to put the cell

values out of that data. In the examples above, two nested for loops were used to pull out the cell

values. There are other ways to take values out of a big grid of data.

The iter_rows functions gives us back a list of lists. (Actually, it's a tuple generator.) Given the

spreadsheet above, iter_rows builds some data where the first item looks like this:

(Why, Hello, There)

We can put those values in a variable named nextRow, then grab each piece with their index.

For example, nextRow[0], nextRow[1], nextRow[2]. So, nextRow[0] would have Why the first

time through the loop and 12 the second time through the loop.

Program Five -- demo15.py

Print cells A1:C4 of our SalesData. The four rows show up on four separate lines. The print

adds a few dashes between the values so the columns stand apart more clearly.

# grab rows 1-4 and columns 1-3

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx")

sheet = workbook.active

gridData = sheet.iter_rows(min_row=1, max_row=4,

min_col=1, max_col=3,

values_only=True)

# gridData is not a list, so this print doesn't show much

print (gridData)

print ("-=-=-=-=-=-")

# show the three items for each row

for nextRow in gridData :

print (nextRow[0],nextRow[1],nextRow[2], sep=" --- ")

Program Six

Now let's grab just one column and print all the values in that column.

Notice that max_row was not given, so iter_rows will give us every row that has data.

# grab rows from column 7 (total sales)

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx")

sheet = workbook.active

# grab column G, except title cell G1

gridData = sheet.iter_rows ( min_row=2,

min_col=7, max_col=7,

values_only=True )

# print the value of each cell in that data

for nextRow in gridData :

print ( nextRow[0] )

And finally, instead of printing everything in row 7, let's put the values into a list. We can do

lots of things with a list. In this example, we use the built-in max function to find our biggest

particular sale.

Program Seven - demo17.py

# Make a *list* of Total Sales

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx")

sheet = workbook.active

# grab column G, except title cell G1

gridData = sheet.iter_rows ( min_row=2,

min_col=7, max_col=7,

values_only=True)

# convert that data into a list

salesList = []

for nextRow in gridData :

salesList.append(nextRow[0])

# print the max value in the list

print("Largest Sale was", max(salesList) )

So, now we know the amount of our biggest sale. But, we don't know when that sale was made

or who made it. That data was in columns we did not retrieve. Matching that big sale to a

salesperson is our next class.

Sheet1