How To Create / Load Excel File In Python Using Openpyxl

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.

  1. First, you should open a terminal and run command pip3 install openpyxl to 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
  2. After install, you can run the command pip3 show openpyxl to 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: [email protected]
    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.

  1. 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)
  2. 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
  3. 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.

  1. 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.
  2. 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', '[email protected]'), ('Tom', '987', '[email protected]'))
    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', '[email protected]')
('Tom', '987', '[email protected]')
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, [email protected], 
Tom, 987, [email protected], 
********* Excel Sheet Name :  User Salary  *********
********* Excel Sheet Name :  User Info  *********

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.