In this article, I will show you how to use python library openpyxl to easily create an excel workbook, add a worksheet to the workbook, insert cell data and save the workbook object to a local excel file. It will also show you how to read a local excel file and load all the excel sheet and data in the excel file.
1. Install Openpyxl.
- First, you should open a terminal and run command
pip3 install openpyxlto install it like below.$ pip3 install openpyxl Collecting openpyxl Downloading openpyxl-3.0.5-py2.py3-none-any.whl (242 kB) |████████████████████████████████| 242 kB 98 kB/s Collecting jdcal Downloading jdcal-1.4.1-py2.py3-none-any.whl (9.5 kB) Collecting et-xmlfile Downloading et_xmlfile-1.0.1.tar.gz (8.4 kB) Building wheels for collected packages: et-xmlfile Building wheel for et-xmlfile (setup.py) ... done Created wheel for et-xmlfile: filename=et_xmlfile-1.0.1-py3-none-any.whl size=8915 sha256=61bfb776bc64f6b3103029ad0c11830cb4f2887e8c3a72ce7799d5daccf63a11 Stored in directory: /Users/songzhao/Library/Caches/pip/wheels/e2/bd/55/048b4fd505716c4c298f42ee02dffd9496bb6d212b266c7f31 Successfully built et-xmlfile Installing collected packages: jdcal, et-xmlfile, openpyxl Successfully installed et-xmlfile-1.0.1 jdcal-1.4.1 openpyxl-3.0.5 - After install, you can run the command
pip3 show openpyxlto verify the library has been installed successfully.$ pip3 show openpyxl Name: openpyxl Version: 3.0.5 Summary: A Python library to read/write Excel 2010 xlsx/xlsm files Home-page: https://openpyxl.readthedocs.io Author: See AUTHORS Author-email: charlie.clark@clark-consulting.eu License: MIT Location: /Users/songzhao/opt/anaconda3/envs/env_ipython_example/lib/python3.7/site-packages Requires: et-xmlfile, jdcal Required-by:
2. Create Excel File.
In openpyxl, one excel file is represented by an openpyxl.Workbook object. Below is the steps to create the Workbook object.
# first import openpyxl.Workbook class. from openpyxl import Workbook # create a Workbook object. work_book = Workbook()
3. Create Excel Sheet.
After you create the Workbook object, you can invoke it’s active attribute to get the initial Worksheet object which create the first excel sheet in the excel file.
You can also invoke the Workbook object’s create_sheet() method to create other excel sheet. And if you want to get all excel sheet names, you can invoke Workbook object’s sheetnames attribute.
You can also set worksheet tab color use Worksheet.sheet_properties.tabColor attribute.
# invoke Workbooc.active property to create the initial work sheet. initial_work_sheet = work_book.active # invoke Workbook object's create_sheet() method to create a openpyxl.Worksheet object. work_sheet_1 = work_book.create_sheet(work_sheet_title, work_sheet_position) # get all work sheet names of this Workbook object. work_sheet_names = work_book.sheetnames # set Worksheet tab color. work_sheet_1.sheet_properties.tabColor = "FF0000"
4. Insert Data Into Excel File.
There are three methods to insert data into an excel file.
- Use the worksheet’s append() method, you should pass a list object to the append() method. The list object contains one row’s value, each list element contains one cell’s value in the row.
work_sheet.append(row_title_list)
- Insert data by each cell’s name. The first row’s cell names are A1, B1, C1, etc. The second row’s cell names are A2, B2, C2, etc. To get one cell’s name, you can click the cell, and then get the cell name in the upper left drop-down list of the excel file.
work_sheet['A1'] = 100
- Use the worksheet’s cell() method. You should pass the cell row, column number, and the cell value to the cell() method. And then you can fill the cell with any color which you want.
cell = work_sheet.cell(row=1, column=2, value='hello') # fill the cell with red color. red_fill = PatternFill(start_color='FFFF0000', end_color='0000FFFF', fill_type='solid') cell.fill = red_fill
5. Save Excel File.
After you create the Workbook object, create a Worksheet in it and insert data into the excel sheet, you can save the data to a real Excel file by calling the Workbook object’s save() method and pass an excel file path and name.
work_book.save(file_name)
6. Load Excel File Data.
You can use openpyxl package’s load_workbook() method to load existing excel file data. You need to pass the excel file path to the load_workbook() method, and the method will return a Workbook object.
# first import the load_workbook method. from openpyxl import load_workbook work_book = load_workbook(file_name)
7. Use Openpyxl To Manage Excel File Example.
7.1 Manage Excel File Example Introduction.
This example will create two excel files.
- test_default_excel.xlsx : It is created in create_default_excel_file function. It has three worksheets (Sheet, Sheet1, Sheet2).
Sheet1 has four rows, and the first row is the title row, it is filled with red color.
Sheet2 has three rows, the first row is the title row that filled with blue color. - test_excel.xlsx : It is created in create_excel_file function. It has three worksheets (User Account, User Salary, User Info). Each worksheet tab has a different color.
The User_Account sheet has three rows, and it’s tab color is red. User Salary sheet tab color is blue. User Info sheet tab color is green.
7.2 Manage Excel File Example Pydev Project File Structure.
$ tree ./ ./ ├── __init__.py ├── create_load_excel_file.py ├── data │ ├── test_default_excel.xlsx │ ├── test_excel.xlsx
The python file name is create_load_excel_file.py. It contains 6 methods, you can see code comments to learn each method.
7.3 Example File Source Code.
create_load_excel_file.py
'''
Created on Sep 23, 2020
@author: songzhao
'''
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
import os
import string
default_excel_file_name = 'test_default_excel.xlsx'
excel_file_name = 'test_excel.xlsx'
'''
This function will create a default excel file. The excel file has three work sheet (Sheet, Sheet1, Sheet2).
Sheet1 has
'''
def create_default_excel_file(file_name=default_excel_file_name):
# create a Workbook object.
work_book = Workbook()
# invoke Workbooc.active property to create the initial work sheet.
initial_work_sheet = work_book.active
# work book has a initial work sheet.
initial_sheet_names = work_book.sheetnames
print(initial_sheet_names)
# create 2 work sheet object.
work_sheet_1 = work_book.create_sheet()
work_sheet_2 = work_book.create_sheet()
print("Initial work sheet title :", initial_work_sheet.title)
print("Work sheet 1 title :", work_sheet_1.title)
print("Work sheet 2 title :", work_sheet_2.title)
# insert data in work_sheet_1.
row_title_list = ('Student Name', 'Class Room', 'Score')
row_data_list = (('Mike', 'Class Room 1', 100),('John', 'Class Room 2', 99),('Steve', 'Class Room 3', 100))
add_row_data_by_cell_coordinate(work_sheet_1, row_title_list, row_data_list)
# insert data in work_sheet_2
row_title_list = ('Teacher Name', 'Teacher Salary')
row_data_list = (('Tom', 9000),('Jerry', 18000))
add_row_data_by_cell_name(work_sheet_2, row_title_list, row_data_list)
# save the Workbook object to a local excel file.
work_book.save(file_name)
'''
This function will create another excel file, and put the work sheet as your required order.
'''
def create_excel_file(file_name=excel_file_name):
# create a Workbook object.
work_book = openpyxl.Workbook()
# get the initial work sheet object.
initial_work_sheet = work_book.active
# set the initial work sheet title.
initial_work_sheet.title = 'User Info'
# set the initial work sheet tab color.
initial_work_sheet.sheet_properties.tabColor = '00FF00'
# create the second work sheet with title and put it as the first work sheet..
work_sheet_1 = work_book.create_sheet("User Account", 0)
# set the second work sheet tab color.
work_sheet_1.sheet_properties.tabColor = "FF0000"
# add data to the second work sheet.
row_title_list = ('User Name', 'Password', 'Email')
row_data_list = (('Jerry', '12345678', 'jerry@gmail.com'), ('Tom', '987', 'tom@gmail.com'))
add_row_data_by_append(work_sheet_1, row_title_list, row_data_list)
# create the third work sheet, set it position at one before the last work sheet.
work_sheet_2 = work_book.create_sheet("User Salary", -1)
# set the third work sheet tab color.
work_sheet_2.sheet_properties.tabColor = "0000FF"
work_book.save(file_name)
'''
Insert excel data use Worksheet's append() method.
'''
def add_row_data_by_append(work_sheet, row_title_list, row_data_list):
# insert excel table title.
work_sheet.append(row_title_list)
for row_data in row_data_list:
print(row_data)
work_sheet.append(row_data)
'''
Insert cell data by cell name.
'''
def add_row_data_by_cell_name(work_sheet, row_title_list, row_data_list):
# add excel row title.
row_title_list_len = len(row_title_list)
if row_title_list_len > 0:
for i in range(row_title_list_len):
# get cell name('A1', 'B1', 'C1'...... etc. )
col_name = string.ascii_uppercase[i] + str(1)
# get cell value.
col_value = row_title_list[i]
# assign cell value to related cell by cell name.
work_sheet[col_name] = col_value
# Fill cell with blue color.
#https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/fills.py
blue_fill = PatternFill(start_color='0000FFFF', end_color='FFFF0000', fill_type='solid')
work_sheet[col_name].fill = blue_fill
# add excel row data.
row_data_list_len = len(row_data_list)
if row_data_list_len > 0:
for i in range(row_data_list_len):
row_data = row_data_list[i]
col_len = len(row_data)
for j in range(col_len):
col_name = string.ascii_uppercase[j] + str(i + 2)
col_value = row_data[j]
work_sheet[col_name] = col_value
'''
Insert row data in to excel sheet use cell's coordinate.
'''
def add_row_data_by_cell_coordinate(work_sheet, row_title_list, row_data_list):
# add excel row title.
row_title_list_len = len(row_title_list)
if row_title_list_len > 0:
for i in range(row_title_list_len):
# get cell row and column number.
cell_row = 1
cell_col = i + 1
# get cell value.
cell_value = row_title_list[i]
# insert cell value at special row and column.
cell = work_sheet.cell(row=cell_row, column=cell_col, value=cell_value)
# Fill cell with red color.
#https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/fills.py
red_fill = PatternFill(start_color='FFFF0000', end_color='0000FFFF', fill_type='solid')
cell.fill = red_fill
# add excel row data.
row_data_list_len = len(row_data_list)
if row_data_list_len > 0:
for i in range(row_data_list_len):
row_data = row_data_list[i]
col_len = len(row_data)
for j in range(col_len):
cell_row = i + 2
cell_col = j + 1
cell_value = row_data[j]
work_sheet.cell(row=cell_row, column = cell_col, value=cell_value)
'''
Load an exist excel file and read the excel row data.
'''
def load_excel_file(file_name):
# check whether the excel exist or not.
if os.path.exists(file_name):
print('Excel file ', file_name, ' exist. ')
# load the excel file and return the Workbook object.
work_book = load_workbook(file_name)
# get all excel sheet name list.
work_sheet_names = work_book.sheetnames
print('Excel file ', file_name,' has following work sheet :')
print(work_sheet_names)
# loop in each excel sheet.
for sheet_name in work_sheet_names:
print("********* Excel Sheet Name : ", sheet_name, ' *********')
# loop excel sheet rows.
for row in work_book[sheet_name].iter_rows():
row_str = ''
# loop row cells.
for cell in row:
# add each cell value to the row string.
row_str = row_str + cell.value + ', '
print(row_str)
else:
print('Excel file ', file_name, ' do not exist. ')
if __name__ == '__main__':
create_default_excel_file()
create_excel_file()
load_excel_file(excel_file_name)
Below is the above source code execution output.
['Sheet']
Initial work sheet title : Sheet
Work sheet 1 title : Sheet1
Work sheet 2 title : Sheet2
('Jerry', '12345678', 'jerry@gmail.com')
('Tom', '987', 'tom@gmail.com')
Excel file test_excel.xlsx exist.
Excel file test_excel.xlsx has following work sheet :
['User Account', 'User Salary', 'User Info']
********* Excel Sheet Name : User Account *********
User Name, Password, Email,
Jerry, 12345678, jerry@gmail.com,
Tom, 987, tom@gmail.com,
********* Excel Sheet Name : User Salary *********
********* Excel Sheet Name : User Info *********