Complete tutorial on Python processing Excel via OpenPyXL

OpenPyXL is a powerful Python library for processing Excel files, allowing reading, editing, and creating Excel workbooks and worksheets. Whether it requires automated processing of large amounts of data or creating beautiful reports, OpenPyXL is a powerful tool. This article will introduce in detail the various features of OpenPyXL, including reading, writing, style settings, etc., as well as a large amount of sample code to help you understand in depth.

Install OpenPyXL

To get started with OpenPyXL, you need to install it first.

Install using pip:

pip install openpyxl

After the installation is complete, you can import OpenPyXL and start processing Excel files.

import openpyxl

Open and create a workbook

Open an existing workbook

With OpenPyXL, you can open an existing Excel workbook and then read and edit it.

Here is an example of opening a workbook:

import openpyxl

# Open the workbookworkbook = openpyxl.load_workbook('')

# Get a worksheetsheet = workbook['Sheet1']

# Read cell datacell_value = sheet['A1'].value
print(cell_value)

Create a new workbook

You can use OpenPyXL to create a new Excel workbook and then write the data to it.

Here is an example of creating a new workbook:

import openpyxl

# Create a new workbookworkbook = ()

# Get the default worksheetsheet = 

# Write data to cellsheet['A1'] = 'Hello'
sheet['B1'] = 'World'

# Save the workbook('new_example.xlsx')

Read and write data

Read cell data

You can use OpenPyXL to read cell data in a worksheet.

Here are some examples:

import openpyxl

workbook = openpyxl.load_workbook('')
sheet = workbook['Sheet1']

# Read cell datacell_value = sheet['A1'].value
print(cell_value)

# Read data through row and column indexescell_value = (row=2, column=3).value
print(cell_value)

Write data to cell

To write data to a worksheet, you can simply assign a value to the cell.

Here is an example:

import openpyxl

workbook = ()
sheet = 

# Write data to cellsheet['A1'] = 'Hello'
(row=2, column=2, value='World')

# Save the workbook('new_example.xlsx')

Operation worksheet

OpenPyXL can also operate worksheets, including creation, copying, deleting, etc.

Here are some examples:

Create a new worksheet

You can create a new worksheet and add it to your workbook:

import openpyxl

workbook = ()

# Create a new worksheetnew_sheet = workbook.create_sheet(title='NewSheet')

# Save the workbook('new_example.xlsx')

Copy the worksheet

To copy an existing worksheet, you can usecopy_worksheetmethod:

import openpyxl

workbook = openpyxl.load_workbook('')

# Copy the worksheetcopied_sheet = workbook.copy_worksheet(workbook['Sheet1'])
copied_sheet.title = 'Copy of Sheet1'

# Save the workbook('example_with_copy.xlsx')

Delete worksheets

To delete a worksheet, useremovemethod:

import openpyxl

workbook = openpyxl.load_workbook('')

# Delete worksheetsdel workbook['Sheet2']

# Save the workbook('example_without_sheet2.xlsx')

Set style

OpenPyXL also supports style settings, which can set fonts, background colors, borders, etc. Here are some examples:

Set font style

import openpyxl
from  import Font

workbook = ()
sheet = 

# Create font stylesfont = Font(name='Arial', bold=True, size=14)

# Apply font styles to cellssheet['A1'].font = font

# Save the workbook('example_with_font.xlsx')

Set background color

import openpyxl
from  import PatternFill

workbook = 

()
sheet = 

# Create background color stylefill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# Apply background color to cellsheet['B2'].fill = fill

# Save the workbook('example_with_fill.xlsx')

Set borders

import openpyxl
from  import Border, Side

workbook = ()
sheet = 

# Create border styleborder = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

# Apply borders to cellssheet['C3'].border = border

# Save the workbook('example_with_border.xlsx')

Summarize

Python OpenPyXL is a powerful library for processing Excel files. Whether it is using Excel files in office automation or requiring large amounts of data analysis, OpenPyXL is a powerful tool. In this article, we introduce how to install OpenPyXL, and then share how to open, create workbooks, read and write data, and how to manipulate worksheets and style them.

Through this article, I learned how to use OpenPyXL to open existing Excel files, read and edit data, and also learned how to create new workbooks, write data into them, and how to operate worksheets, including creating, copying, and deleting worksheets. In addition, I learned how to style, including fonts, background colors, and borders to beautify Excel files.

With Python OpenPyXL, various Excel files can be easily processed, thereby improving office efficiency and data processing capabilities. Whether it is daily work or data analysis, OpenPyXL will become a right-hand assistant.

This is the end of this article about this complete tutorial on Python processing Excel through OpenPyXL. For more related Python OpenPyXL content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!