13 Working with Excel Spreadsheets Flashcards
How do you
- Open an excel worksheet
- Get any sheet from the workbook?
- Get the active sheet?
- Get any and all sheetnames
How do you
- Open an excel worksheet
»>import openpyxl
»> wb = openpyxl.load_workbook(‘file.xlsx’) - Get any sheet from the workbook?
»>sheet = wb[‘sheetName’] - Get the active sheet?
»>sheet = wb.active - Get any and all sheetnames
»>sheet.title
»>wb.sheetnames
How can you create a cell object?
Name 4x attributes that can be used on the cell object
- Choose sheet
»> sheet = wb[‘Sheet1’] - Create cell object
»>co = sheet[‘A1’]
Useful methods:
co. value
co. row
co. column
co. coordinates->A1
Give a rundown of all the functions, methods and data types involved in reading a cell out of a spreadsheet file (1-8)
- > > > import openpyxl
- Create workbook object by calling the open function
»>wb = openpyxl.load_workbook() - Create a worksheet objectt with the active sheet or sheetnames attributes
»>sheet = wb[‘sheetName’] OR
»>sheet = wb.active - Create a cell object using indexing or the cell() sheet method with row and column keyword
c = sheet[‘A1’] / sheet[‘A1’:’B3’] OR
c = sheet.cell(row1, column=2) - Read the Cell object’s value attribute.
c. value
c. coordinate
c. row / column
How can you create and save an excel File?
> > > import openpyxl
wb = openpyxl.load_workbook(‘example.xlsx’)
sheet = wb.active
sheet.title = ‘Spam Spam Spam’
wb.save(‘example_copy.xlsx’) # Save the workbook.
How can you write to a cell and remove or create sheets??
Write to Cell: (Similar to writing to a dictionary)
»>sheet[‘A1] = ‘Text’
Create Sheet:
»>wb.create_sheet(index=0, title=’First Sheet’) OR
»> wb.create_sheet() # Default: sheetX and always last sheet in wb
Del Sheet:
del wb[‘sheetName’]
How can you find out the file size? (max columns and rows)
> > > wb = openpyxl.load_workbook(‘example.xlsx’)
sheet = wb[‘Sheet1’]
sheet.max_row
sheet.max_column
How do you change the font? (Explain the structure)
> > > from openpyxl.styles import Font
Create a Font!
fontVariable = Font(kwargs=Desctiption)
Possible kwargs:
- name (string)
- size (integer)
- bold (boolean)
- italic (boolean)
E.g.
»_space;> sheet = wb[‘Sheet’]
»> italic24Font = Font(size=24, italic=True) # Create a font.
»> sheet[‘A1’].font = italic24Font
How do you include formulas, when writing to cells?
> > > sheet = wb.active
sheet[‘A3’] = ‘=SUM(A1:A2)’
wb.save(‘writeFormula.xlsx’)
The formula itself is as it would be written in Excel
What value type do row/column_dimensions resemble?
How can you use them to change row/ column dimensions?
They resemble dictionary like values
row[i] -> integer
column[‘x’] -> letter
sheet. row_dimensions[1].height = 70
sheet. column_dimensions[‘B’].width = 20
How do you merge cells? How can you undo merged cells?
> > > sheet.merge_cells(‘A1:D3’)
> > > sheet.unmerge_cells(‘A1:D3’)
Explain Freezing Panes
How do you unfreeze?
All cells above and left of the defines cell are frozen, e.g. to freeze row 1 and Column A:
»>sheet.freeze_panes = ‘B2’
unfreeze:
»>sheet.freeze_panes = ‘A1’
Is it possible to create charts via Python?
Yes - but its so complicated, one should always look up how to do it!
> > > import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 11): # create some data in column A
… sheet[‘A’ + str(i)] = i
…
refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1,
max_row=10)
seriesObj = openpyxl.chart.Series(refObj, title=’First series’)
> > > chartObj = openpyxl.chart.BarChart()
chartObj.title = ‘My Chart’
chartObj.append(seriesObj)
> > > sheet.add_chart(chartObj, ‘C5’)
wb.save(‘sampleChart.xlsx’)
How can you change a whole column or row, e.g. to switch the font?
> > > for row in range(1, N+1):
curCellCol = sheet.cell(row=row+1, column=1)
curCellCol.font = fontObj
