my_sheetnames = wb.sheetnames # return list object
# Get a sheet from the workbook
sheet = wb[my_sheetnames[0]] # sheet3 for example
# Get the sheet's title as a string
my_titles = sheet.title
# Get the active sheet
anotherSheet = wb.active
# Getting a cell from the sheet
cell_A1 = sheet['A1']
# Get the value from the cell
cell_A1_value = sheet['A1'].value
# Get the row, column, coordinate from the cell
cell_A1_row = sheet['A1'].row
cell_A1_column = sheet['A1'].column
cell_A1_coordinate = sheet['A1'].coordinate
area_cells = sheet['A1':'C3'] # tuple of all the cell objects
# Charts
# 1. Create a Reference object from a rectangular selection of cells.
# 2. Create a Series object by passing in the Reference object.
# 3. Create a Chart object.
# 4. Append the Series object to the Chart object.
# 5. Add the Chart object to the Worksheet object, optionally specifying which cell should be
# the top-left corner of the chart.
# Ex.: BarChart()
# Charts
import openpyxl.chart
wb_chart = openpyxl.Workbook()
sheet_chart = wb_chart.active
for i in range(1, 11):
sheet_chart['A' + str(i)] = i
cell_B1 = sheet.cell(row=1, column=2) # if add argument 'value=' it'll change the value of cell
# Get the highest row number
sheet_max_row = sheet.max_row
# Get the highest column number
sheet_max_column = sheet.max_column
# Converting between column letters and numbers
from openpyxl.utils import get_column_letter, column_index_from_string
col_letter = get_column_letter(1)
col_max_letter = get_column_letter(sheet.max_column)
index_letter = column_index_from_string('A') # Get A's number
# Get the rows, columns
# Using the rows return a tuple of tuples. Inner tuples - row.
# Using the columns return a tuple of tuples. Inner tuples - the cell object in a particular column.
# Convert to list with the list() function. Use index in the larger tuple.
# Ex.: to get the tuple that represents row 1
tuple_row_1 = list(sheet.rows)[0]
# Ex.: to get the tuple that represents column B
tuple_column_B = list(sheet.columns)[1]
wb_new.create_sheet() # Add a new sheet
wb_new.create_sheet(index=0, title='First sheet') # Create a new sheet at index 0
wb_new.create_sheet(index=2, title='Middle sheet') # Create a new sheet at index 2
del wb_new['Middle sheet'] # Remember to call the save() method to save changes
# Writing Values to Cells
# Writing values to cells is much like writing values to keys in a dictionary.
sheet_new['A1'] = 'Hello, world!'
print(sheet_new['A1'].value)
# Module openpyxl.styles
# Setting the Font Style of Cells
from openpyxl.styles import Font
wb_style = openpyxl.Workbook()
sheet_style = wb_style['Sheet']
italic24Font = Font(size=24, italic=True, name='Calibri') # Create a font.
sheet_style['A1'].font = italic24Font # Apply the font to A1.
sheet_style['A1'] = 'Hello, world!'
wb_style.save('styles.xlsx')
# Formulas
# Add formulas to cell just like any normal value.
wb_formulas = openpyxl.Workbook()
sheet_formulas = wb_formulas.active
sheet_formulas['A1'] = 200
sheet_formulas['A2'] = 300
sheet_formulas['A3'] = '=SUM(A1:A2)' # Set the formula
wb_formulas.save('writeFormula.xlsx')
# Setting Row Height and Column Width
wb_dimension = openpyxl.Workbook()
sheet_dimension = wb_dimension.active
sheet_dimension['A1'] = 'Tall row'
sheet_dimension['B2'] = 'Wide column'
sheet_dimension.row_dimensions[1].height = 70 # Set the height
sheet_dimension.column_dimensions['B'].width = 20 # Set the width
sheet_dimension.column_dimensions['C'].hidden = True # Hide the column 'C'
wb_dimension.save('dimensions.xlsx')
# Freezing Panes
# All rows above and columns to the left of this cell will be frozen
# To unfreeze all panes, set freez_panes to None or 'A1'
wb_freeze = openpyxl.load_workbook('produceSales.xlsx')
sheet_freeze = wb_freeze.active
sheet_freeze.freeze_panes = 'A2' # Freeze the rows above A2.
wb_freeze.save('freezeExample.xlsx')
