python module openpyxl reads and writes .xlsx files

There are many modules for Python to operate Excel, and each has its own advantages and disadvantages. Different modules support different operations and file types. The following is the support of each module:

xlrd: xlrd reads .xls files
xlwings: xlwings reads and writes Excel files
xlwt: xlwt writes .xls file

1. Installation

pip install openpyxl

2. Basic use

import openpyxl

workbook = openpyxl.load_workbook('excel1.xlsx') #Open the Excel file and return the Workbook object
sheet = workbook['sheet1'] #Get the worksheet
A_cell_list = sheet['A'] #Get the cells in column A sheet['A1:B5']: Get the cell objects in the specified range.

for A_cell in A_cell_list:
    print(A_cell.value) #Get the value in the cell

3. Methods and attributes

openpyxl.load_workbook(filename, read_only=False, data_only=False, keep_vba=False) Opens an Excel file and returns a Workbook object

import openpyxl
'''
filename: The file name or path of the Excel file to be loaded. Can be a relative path or an absolute path.
read_only: Specifies whether to load Excel files in read-only mode. The default is False, which loads the file in read-write mode. If set to True, the file is loaded in read-only mode, which improves loading speed but prevents modification of the file.
data_only: specifies whether to load only the value of the cell and not the formula. The default is False, which loads both cell values and formulas. If set to True, only the cell's value is loaded, formulas are ignored.
keep_vba: Specifies whether to keep VBA macro code. The default is False, which does not preserve VBA macro code. If set to True, VBA macro code will be preserved when loaded.
'''
workbook = openpyxl.load_workbook('excel1.xlsx') #Open the Excel file and return the Workbook object

openpyxl.Workbook(write_only=False,iso_dates=False) creates a new workbook object
workbook.close() closes the file
workbook.save(filename) saves the modification to the Excel file
workbook.sheetnames: Returns a list of the names of all worksheets in the workbook
workbook.active: Returns the currently active worksheet
workbook.create_sheet(title=None, index=None): create a new worksheet
workbook.copy_worksheet(worksheet): Copy the specified worksheet.
workbook.remove(sheet): delete the specified worksheet
sheet.title: name of the worksheet
sheet.max_row: The maximum number of rows in the worksheet
sheet.max_column: Maximum number of columns in the worksheet
sheet.dimensions: Get the dimensions of the worksheet, that is, the range of cells containing the data
sheet.cell(row, column, value=None): Returns the cell object of the specified row and column
sheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): Iterate cells in the worksheet by rows
sheet.iter_cols(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): Iterate cells in the worksheet by columns
sheet.merge_cells(range_string): Merge cells within the specified range
sheet.unmerge_cells(range_string): Unmerge cells within the specified range
sheet.insert_rows(idx, amount=1): Insert the specified number of rows at the specified position
sheet.delete_rows(idx, amount=1): Delete the specified number of rows at the specified position
sheet.insert_cols(idx, amount=1): Insert the specified number of columns at the specified position
sheet.delete_cols(idx, amount=1): Delete the specified number of columns at the specified position
sheet.column_dimensions: Set and manage the properties of a single column, column_dimensions = sheet.column_dimensions[A’]

column_dimensions.width: Indicates the width of the column, which can be set to an integer value, the unit is the number of characters
column_dimensions.hidden: Indicates whether the column is hidden, which can be set to a Boolean value. True means hidden, False means displayed.
column_dimensions.auto_size: Indicates whether the column automatically adjusts its size to fit the content. It can be set to a Boolean value. True means automatic adjustment, False means no automatic adjustment.
column_dimensions.best_fit: Indicates whether the column automatically adjusts to the best fit width based on the content. It can be set to a Boolean value. True means automatic adjustment, False means no automatic adjustment.
column_dimensions.collapsed: Set whether the column is collapsed, Boolean value, True means folded, False means expanded
column_dimensions.outline_level: Set the outline level of the column, integer value
column_dimensions.style: Set the column style, which can be a str type style name
column_dimensions.number_format: Set the number format of the column, which can be a str type number format code

sheet.row_dimensions: Set and manage the properties of a single row, row_dimensions[ = sheet.row_dimensions[1]

height: Set the height of the row in points (points)
auto_size: Sets whether the height of the row automatically adjusts to fit the content, Boolean value, True means automatic adjustment, False means no automatic adjustment
hidden: Set whether the row is hidden, Boolean value, True means hidden, False means displayed
outline_level: Set the outline level of the line, integer value
collapsed: Set whether the row is collapsed, Boolean value, True means folded, False means expanded
default_height: Get or set the default row height.
group(): Group the specified range of rows into an outline group
ungroup(): Ungroup the specified range of rows
copy(): Creates a copy of the row dimension object so that modifications can be made on the copy without affecting the original object

cell.value: the value of the cell
cell.row: the row number of the cell
cell.column: the column number of the cell
cell.coordinate: the coordinates of the cell
cell.data_type: cell type
cell.offset(row_offset, column_offset): Returns the offset cell relative to the current cell
cell.has_style: Check if the cell has a style
cell.clear(): Clear the content and style of the cell
cell.check_error(): checks whether the cell contains an error value
cell.number_format: Get or set the number format of the cell
cell.comment: get or set the comment of the cell
cell.font: the font setting of the cell

cell.font.name: Indicates the font name, which can be a string. Default value is None
cell.font.size: Indicates the font size, which can be an integer. Default value is None
cell.font.bold: Indicates whether to bold, can be a Boolean value. Default value is None
cell.font.italic: indicates whether italic, can be a Boolean value. Default value is None
cell.font.underline: Indicates whether there is an underline, which can be a string, such as
‘single’, ‘double’, ‘singleAccounting’, etc. Default value is None
cell.font.strikethrough: Indicates whether there is a strikethrough, which can be a Boolean value. Default value is None
cell.font.color: Represents the font color, which can be an RGB value or a string of color name. Default value is None
cell.font.copy(): Create a copy of the font style object
cell.font.to_tree(): Converts a font style object to an xml tree representation

cell.fill: cell fill settings

cell.fill.fill_type: Indicates the fill type, which can be solid’, gradient’, pattern’ or None. Default value is None
cell.fill.start_color: Indicates the starting color of filling, which can be an RGB value or a string of color name. Default value is None
cell.fill.end_color: Indicates the end color of the fill, which can be an RGB value or a string of color name. Default value is None
cell.fill.fgColor: Represents the foreground color of the fill, which can be an RGB value or a string of color names. Default value is None
cell.fill.bgColor: Represents the background color of the fill, which can be an RGB value or a string of color names. Default value is None
cell.fill.patternType: Represents the fill pattern type, which can be none’, solid’, darkGray’, mediumGray’, lightGray’, gray125’, gray0625’ or None. Default value is None
cell.fill.copy(): Create a copy of the fill style object
cell.fill.to_tree(): Convert the fill style object to the representation of an xml tree

cell.border: the border setting of the cell

cell.border.left: Indicates the style of the left border, which can be a Side object or None. Default value is None
cell.border.right: Indicates the style of the right border, which can be a Side object or None. Default value is None
cell.border.top: Indicates the style of the top border, which can be a Side object or None. Default value is None
cell.border.bottom: Indicates the style of the bottom border, which can be a Side object or None. Default value is None
cell.border.diagonal: Indicates the style of the diagonal line, which can be a Side object or None. Default value is None
cell.border.diagonal_direction: Indicates the direction of the diagonal, which can be an integer value (angle) or None. Default value is None
cell.border.copy(): Creates a copy of the border style object.
cell.border.to_tree(): Converts a border style object to a representation of an xml tree
The Side class is the class used to represent the border line style in openpyxl

cell.alignment: the alignment setting of the cell

cell.alignment.horizontal: Indicates the horizontal alignment. The optional values are left’, center’, right’, justify’, and distributed’. Default value is None
cell.alignment.vertical: Indicates the vertical alignment, the optional values are ‘top’, ‘center’, ‘bottom’, ‘justify’, ‘distributed’. Default value is None
cell.alignment.text_rotation: Indicates the text rotation angle, the value range is -90 to 90, the default value is 0
cell.alignment.wrap_text: Indicates whether to enable text wrapping, a boolean value, the default value is False
cell.alignment.shrink_to_fit: indicates whether to automatically shrink the cell to fit the text, boolean value, the default value is False
cell.alignment.indent: indicates the indentation level, an integer value, the default value is 0
cell.alignment.copy(): Create a copy of the alignment object
cell.alignment.to_tree(): Converts the alignment object to a representation of an xml tree

Fourth, set the style

font settings

 import openpyxl
from openpyxl. styles import Font

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
name: Font name, such as 'Arial', 'Calibri', etc.
size: Font size, in points.
bold: Whether to bold the font, which can be set to True or False.
italic: whether to use italic fonts, can be set to True or False.
underline: underline style, can be set to one of the following values
            Font.UNDERLINE_SINGLE: single underline.
            Font.UNDERLINE_DOUBLE: double underline.
            Font.UNDERLINE_SINGLE_ACCOUNTING Accounting single underline.
            Font.UNDERLINE_DOUBLE_ACCOUNTING: Accounting double underline.
color: font color, you can use RGB value (such as 'FF0000' means red)
strikethrough: whether to use strikethrough, can be set to True or False.
superscript: whether to use superscript, can be set to True or False.
subscript: whether to use subscript, can be set to True or False.
'''
font = Font(name='Arial', size=12, color='FF0000', bold=True)

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.font = font

workbook.save('test.xlsx')

Fill settings

 import openpyxl
from openpyxl. styles import PatternFill

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']
'''
fill_type: fill type, can be set to one of the following values
          'none': No padding.
          'solid': solid color fill.
          'gray125': Gray 125 fill.
          'linearGradient': linear gradient fill.
          'path': path padding.
start_color: start color, you can use RGB value (such as 'FF0000' means red)
end_color: end color, only applicable to certain fill types (such as linear gradient fill), RGB values can be used
fgColor: foreground color, used for solid color filling, can use RGB value
bgColor: background color, used for solid color filling, can use RGB value
'''
fill = PatternFill(fill_type='solid', fgColor='FFFF00')
for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.fill = fill

workbook.save('test.xlsx')

Border settings

 import openpyxl
from openpyxl. styles import Border,Side

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
Brder parameters
left: Left border style, which can be set as an instance of the Side class, used to define the style, color and thickness of the border line
right: the right border style, which can also be set as an instance of the Side class
top: top border style, which can also be set as an instance of the Side class
bottom: the bottom border style, which can also be set as an instance of the Side class
diagonal: Diagonal border style, which can also be set as an instance of the Side class
diagonal_direction: Diagonal direction, can be set to one of the following values
                    'none': no diagonal
                    'up': diagonal from bottom left to top right
                    'down': diagonal from top left to bottom right
Side parameters
border_style: border style, can be set to one of the following values
              'none': no border
              'thin': thin border
              'medium': medium thick border
              'thick': thick border
color: border color, you can use RGB value (such as 'FF0000' means red)
'''
border = Border(left=Side(border_style='thin', color='ff0000'),
                right=Side(border_style='thin', color='ff0000'),
                top=Side(border_style='thin', color='ff0000'),
                bottom=Side(border_style='thin', color='ff0000'))

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.border = border

workbook.save('test.xlsx')

alignment-settings

 import openpyxl
from openpyxl. styles import Alignment

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
horizontal: horizontal alignment, can be set to one of the following values
            'general': General alignment
            'left': left aligned
            'center': center alignment
            'right': right aligned
            'fill': fill alignment
            'justify': Justify both ends
            'centerContinuous': continuous center alignment
            'distributed': distributed alignment
vertical: vertical alignment, can be set to one of the following values
          'top': top alignment
          'center': center alignment
          'bottom': bottom alignment
          'justify': Justify both ends
          'distributed': distributed alignment
textRotation: Text rotation angle, which can be set to an integer value, indicating the rotation angle in units of 90 degrees
wrapText: Boolean value indicating whether to automatically wrap text
shrinkToFit: Boolean value indicating whether to automatically shrink to fit the cell size
indent: indent level, which can be set to an integer value, indicating the number of characters for indentation
'''
alignment = Alignment(horizontal='center', vertical='center')

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.alignment = alignment

workbook.save('test.xlsx')

Five, openpyxl.utils auxiliary tools

get_column_letter(column_index): Convert the column index into the corresponding letter representation. For example, get_column_letter(1) returns A’, get_column_letter(27) returns AA’
column_index_from_string(column_letter): Convert column letter representation to corresponding index. For example, column_index_from_string(A’) returns 1, column_index_from_string(AA’) returns 27
coordinate_from_string(cell_coordinate): Converts the cell coordinate string into a tuple of row index and column index. For example, coordinate_from_string(A1’) returns (1, 1)
get_column_letter_range(start_column, end_column): Generates the column letter range between the start column and the end column. For example, get_column_letter_range(A’, C’) returns [A’, B’, C’]
range_boundaries(range_string): Get the coordinates of the starting cell and ending cell of the range string. For example, range_boundaries(A1:C3’) returns (A1’, C3’)
quote_sheetname(sheetname): Add quotes around the sheet name to avoid problems caused by special characters
rows_from_range(range_string): Generator that gets row index from range string. For example, the rows_from_range(A1:C3’) generator will generate 1, 2, 3
cols_from_range(range_string): Generator that gets column index from range string. For example, the cols_from_range(A1:C3’) generator will generate 1, 2, 3
coordinate_to_tuple(coordinate): Converts the cell coordinate string into a tuple of row index and column index. For example, coordinate_to_tuple(A1’) returns (1, 1)
get_column_letter.doc: The documentation string for the get_column_letter method, providing a description and examples of the method
column_index_from_string.doc: The documentation string for the column_index_from_string method, providing a description and examples of the method