OpenPyXL Tutorial: Create Excel, save Excel, add sheet, write different types of data

Article directory

  • I. Introduction
  • 2. Use OpenPyXl to create Excel files
  • 3. Save the Excel file
  • Fourth, create and save Excel complete code example
  • 5. Add a worksheet to an Excel file
  • 6. Complete code example for adding a worksheet
  • Seven, write different types of data
  • 8. Complete code example for writing time, text, numbers and formulas
  • 9. Detailed explanation of cell data format setting

1. Foreword

The content we are going to explain today includes the following aspects. If you think it is okay, I hope you can give a valuable like, favorite or subscription:

  • Create and save Excel files
    • Create a new Excel file: Create a new Excel file through openpyxl and add the default worksheet.
    • Add worksheet to Excel file: Learn to add multiple worksheets in the newly created Excel file and set the name of the worksheet.
    • Save Excel file to local: learn how to save the modification of Excel file to local file.
  • Data writing and formatting
    • Write data to cells: Master the method of writing data into cells, which can be different types of data such as text, numbers, or dates. Includes writing text, writing numbers, writing date (time), writing formula.
    • Setting cell values and data types: Learn how to set cell values and data types to ensure data is interpreted and displayed correctly. (About number_format.)

2. Use OpenPyXl to create Excel files

  1. Import the required modules: Import the openpyxl module in the Python script to use its functions.
import openpyxl
  1. Create an Excel file and worksheet: Use the openpyxl.Workbook() method to create a new Excel file and use the .active attribute to get the default worksheet.
# Create a new Excel file
workbook = openpyxl. Workbook()

# Get the default worksheet
sheet = workbook. active
  1. Operate the worksheet: Now you can operate the default worksheet, such as adding data, setting cell values, setting cell styles, etc.
# Set the value in cell A1
sheet['A1'] = "Hello"

# set value in cell B1
sheet['B1'] = "World"

3. Save the Excel file

  1. Save the Excel file: When you’re done with your data, make sure to save your Excel file.
# save the Excel file
workbook.save("E:\UserData\Desktop\example-1.xlsx")

4. Create and save Excel complete code example

import openpyxl

# Create a new Excel file
workbook = openpyxl. Workbook()

# Get the default worksheet
sheet = workbook. active

# set value in cell A1
sheet['A1'] = "Hello"

# set value in cell B1
sheet['B1'] = "World"

# save the Excel file
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")

5. Add worksheet to Excel file

When using the openpyxl library to add multiple worksheets in a newly created Excel file and set the name of the worksheets, you can follow the steps below:

  1. Import the required modules: Import the openpyxl module in the Python script to use its functions.
import openpyxl
  1. Create an Excel file: Use the openpyxl.Workbook() method to create a new Excel file.
# Create a new Excel file
workbook = openpyxl. Workbook()
  1. Add multiple sheets and set the name: Use the .create_sheet() method to add multiple sheets and use the .title attribute to set the sheet’s name.
# Add a worksheet and set the name
sheet1 = workbook.create_sheet("Worksheet 1")
sheet2 = workbook.create_sheet("Worksheet 2")
sheet3 = workbook.create_sheet("Worksheet 3")
  1. Manipulating worksheets: Perform operations on each worksheet, such as adding data, setting cell values, etc.
# Set value in cell A1 of Sheet1
sheet1['A1'] = "Ah ah ah"

# Set value in cell B2 of Sheet2
sheet2['B2'] = "Hey Hey Hey"

# Set value in cell C3 of Sheet3
sheet3['C3'] = "Slightly slightly"
  1. Save the Excel file: When you’re done with your data, make sure to save your Excel file.
# save the Excel file
workbook.save("r"E:\UserData\Desktop\example-1.xlsx"")

6. Add worksheet complete code example

import openpyxl

# Create a new Excel file
workbook = openpyxl. Workbook()

# Add worksheet and set name
sheet1 = workbook.create_sheet("Worksheet 1")
sheet2 = workbook.create_sheet("Worksheet 2")
sheet3 = workbook.create_sheet("Worksheet 3")

# Set value in cell A1 of Sheet1
sheet1['A1'] = "Ah ah ah"

# Set value in cell B2 of Sheet2
sheet2['B2'] = "Hey Hey Hey"

# Set value in cell C3 of Sheet3
sheet3['C3'] = "Slightly slightly"

# save the Excel file
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")

7. Write different types of data

  1. Create an Excel file and worksheet: Use the openpyxl.Workbook() method to create a new Excel file and get the default worksheet.
workbook = openpyxl. Workbook()
sheet = workbook. active
  1. Write different types of data:
  • Write text (string): Use single or double quotes to wrap the text, and then assign it to the target cell.
sheet['A1'] = "Hello, world!"
  • Write number: directly assign the number to the target cell.
sheet['B1'] = 12345
  • Writing Dates: You can use Python’s datetime module to handle dates and assign date objects to target cells.
from datetime import datetime
sheet['C1'] = datetime(2023, 8, 10)
  • Write Formula: Use the formula string to assign the formula to the target cell.
sheet['D1'] = '=SUM(B1:B2)'
  1. Save the Excel file: After the data writing operation is complete, save the Excel file.
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")

8. Complete code example for writing time, text, numbers and formulas

import openpyxl
from datetime import datetime

# Create a new Excel file
workbook = openpyxl. Workbook()
sheet = workbook. active

# write text
sheet['A1'] = "Hello, world!"

# write numbers
sheet['B1'] = 12345

# write date
sheet['C1'] = datetime(2023, 8, 10)

# Write the formula
sheet['D1'] = '=SUM(B1:B2)'

# save the Excel file
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")

9. Detailed explanation of cell data format setting

Detailed explanation about number_format
sheet.number_format is a property in the openpyxl library for setting the number format of cells in an Excel sheet. By setting this property, you can control the display format of the numeric data in the cell, including decimal places, currency symbols, percentages, etc.

The following is a detailed explanation about sheet.number_format:

  1. Attribute description: number_format is the attribute of the worksheet object, which is used to control the display format of the numerical data in the cell. It works on cells with numeric data such as integers, decimals, dates, etc.
  2. Syntax: The syntax of the sheet.number_format property is as follows:
sheet['A1'].number_format = 'Format Code'

where A1 is the coordinate of the cell to be formatted, and formatcode is a string representing the desired number format.

  1. Format Code: A format code is a specific string that defines the display format for numeric data. Format codes are based on Excel’s custom formats, which can include the following:
    • Number Format: Controls the number of decimal places, thousands separator, etc.
    • Currency Format: Set the currency symbol, decimal places, etc.
    • Percent Format: Displays the number as a percentage.
    • Date and time format: Set how the date and time are displayed.
  2. Examples: Here are some examples showing how to use sheet.number_format to set different number formats:
  • Format integers:
sheet['A1'].number_format = '0'
  • Format floating-point numbers with two decimal places:
sheet['B1'].number_format = '0.00'
  • Set the currency format:
sheet['C1'].number_format = '$#,##0.00'
  • Format percentages:
sheet['D1'].number_format = '0.00%'
  • Set the date format:
sheet['E1'].number_format = 'yyyy-mm-dd'
  1. Escaping special characters: In format codes, certain characters need to be enclosed in double quotes in order to be represented correctly in the string, such as currency symbols.
  2. Default: If the sheet.number_format property is not set, cells will be displayed in the common format by default.
  3. Note: The exact syntax and options for format codes are similar to formatting in Excel.

Full sample code:

import openpyxl

# Create a new Excel file
workbook = openpyxl. Workbook()
sheet = workbook. active

# set number format
sheet['A1'] = 12345.6789
sheet['A1'].number_format = '0.00'

sheet['B1'] = 0.75
sheet['B1'].number_format = '0%'

sheet['C1'] = 42000
sheet['C1'].number_format = '$#,##0.00'

sheet['D1'] = 44518
sheet['D1'].number_format = 'yyyy-mm-dd'

# save the Excel file
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")

Sometimes you need to use the style object in the openpyxl.styles module to set the format of the cell. Here is a correct example code to format a cell:

import openpyxl
from openpyxl. styles import NamedStyle

# Create a new Excel file
workbook = openpyxl. Workbook()
sheet = workbook. active

# set style object
currency_style = NamedStyle(name='currency', number_format='$#,##0.00')
percentage_style = NamedStyle(name='percentage', number_format='0.00%')

# Set the style object to the worksheet
sheet['A1'].style = currency_style
sheet['B1'].style = percentage_style

# fill cells
sheet['A1'] = 12345.6789
sheet['B1'] = 0.75

# save the Excel file
workbook.save(r"E:\UserData\Desktop\example-1.xlsx")
  • In the above code, we use openpyxl.styles.NamedStyle to create a style object, and then apply the style to the cell through the .style property. This formats the cell correctly.
  • When creating a style object using NamedStyle, you can customize the name of the style and set different formatting options as needed.