python excel

Question 1

Method to open the excel spreadsheet

Answer 1

openpyxl.load_workbook()

Question 2

Name of module you need to import to work with excel spreadsheets

Answer 2

openpyxl

Question 3

What parameters does the load_workbook method need?

Answer 3

the file path

Question 4

How to select first available worksheet

Answer 4

wb_obj = openpyxl.load_workbook(path)

sheet_obj = wb_obj.active

Question 5

What is the integer of the first row & column?

Answer 5

1, 1

Question 6

How to create a cell object

Answer 6

Using the sheet object’s cell method:

cell_obj = sheet_obj.cell(row = 1, column = 1)

Question 7

Display the value inside each cell object

Answer 7

print(cell_obj.value)

Question 8

Get value of total rows & columns

Answer 8

row = sheet_obj.max_row

column = sheet_obj.max_column

Question 9

Print all values from first column

Answer 9

for i in range(1, row+1):
cell_obj = sheet_obj.cell(row = i, column = 1)
print(cell_obj.value)

Question 10

print all values from first row

Answer 10

Same as printing columns but you have range(1, column+1) and (row = 1, column = i)

Question 11

Create a new empty xl file

Answer 11

from openpyxl import Workbook
workbook = Workbook()
workbook.save(filename=”file-name.xlsx”)

Question 12

assign value to cell object

Answer 12

c1 = sheet.cell(row = 1, column = 1)
c1.value = “Hello”

Question 13

access a cell object by its actual excel name

Answer 13

c3 = sheet[‘A2’]

A2 means column = 1 & row = 2

Question 14

append data to sheet

Answer 14

data = ((1, 2, 3),(4, 5, 6))

for row in data:
sheet.append(row)

Question 15

make cell A7 set to a formula that sums the values in A1, A2, A3, A4, A5

Answer 15

sheet[‘A7’] = ‘= SUM(A1:A5)’