Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to use openpyxl module in Python". In daily operation, I believe many people have doubts about how to use openpyxl module in Python. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use openpyxl module in Python". Next, please follow the editor to study!

Installation method

Install using pip or through a specialized python IDE such as pyCharm

For the pip installation method, enter pip install openpyxl on the command line

Basic use

The first step is to import the openpyxl module

Import openpyxl reads Excel documents

The file is read by calling the method load_workbook (filename), which also has a read_only parameter to set the file opening mode, which is readable and writable by default, and this method will eventually return a data object of workbook

# the file must be in xlsx format. If it is in other formats, you can use win32-assisted conversion wb = openpyxl.load_workbook ('example.xlsx') (1) to get the worksheet before execution

There will be many Excel worksheets in each sheet table, so you need to select a worksheet before operating on the table.

# get all worksheet names (return a list) sheets = wb.get_sheet_names () # get a specific worksheet sheet = wb.get_sheet_by_name ('Sheet2') # get the table name of the worksheet sheet_name = sheet.title# generally speaking, most tables use the worksheet displayed when opened, then you can use active to get the current worksheet sheet = wb.active (2) get the cell

The operation on the Excel table ultimately falls on the operation of the cell. There are two ways to get the cell: sheet [column row name] and sheet.cell (row,column).

# get a = sheet ['A2'] # through sheet [column Row name] # get b = sheet.cell (1,2) # through sheet.cell (row,column), that is, sheet [' B1'] # get the cell content print (a.value) # get the column and row print of the cell ('an is'+ str ((a.column.row)

It should be noted that the parameters in sheet.cell (row,column) are rows and columns, respectively, and must be integers. If listed as English letters, you can use column_index_from_string (char) in openpyxl.utils for alphanumeric conversion. By the way, get_column_letter (number) can also be used for conversion between numbers and letters.

From openpyxl.utils import get_column_letter, column_index_from_string# alphanumeric conversion of columns c_num = column_index_from_string ('B') # c_num = 2c_char = get_column_letter (5) # c_char ='E' (3) get rows and columns

When dealing with Excel tables, it may be necessary to traverse the table. Openpyxl provides a row and column generator (sheet.rows and sheet.columns). These two generators contain data for each row (or column), and each row (or column) is wrapped by a tuple, thus traversing rows and columns can be easily completed.

# iterate through the rows, output A1 Magi B1Magic C1 for row in sheet.rows: for cell in row: print (cell.value) # traverse the columns, and output A1 Magi A2 Magi A3 for column in sheet.columns: for cell in column: print (cell.value)

When learning, it is also found that a row or column can also be traversed through list (sheet.rows) index, and it is worth noting that since sheet.rows (or sheet.columns) is a generator type, it cannot be called directly, so it needs to be converted to a list type, and then traversed through the index.

# traversing a specific line for cell in list (sheet.rows) [0]: print (cell.value)

At the same time, you can also traverse the range of a given cell by using sheet [row values: row values]

# traversing a range of cells through for spaces in sheet ['A1cells _ range _ B2']: for cell in spaces: print (cell.value)

In addition, sometimes we may need to determine the size of the table, that is, to get the maximum values for the rows and columns of the table, which can be obtained using max_row and max_column

# get the maximum column and maximum row print (sheet.max_row) print (sheet.max_column) write to the Excel document

As described at the beginning of reading, the default way to open it is readable and writable, so you can write directly after reading the Excel document using load_workbook (filename). In addition, if you need to create a new Excel file, you can use the Workbook () method, which automatically provides an sheet worksheet. For deleting a worksheet, you can delete it using the remove (sheet) method of the workbook object

# create a new Excel document wb = openpyxl.Workbook () # Delete a worksheet wb.remove (sheet) (1) write to the cell

Get the worksheet as before, if you read it using load_workbook (filename), you can write to the cell directly through sheet [row value] after getting the worksheet. When learning, you can also input the formula in Excel to assign values, but you should note that you need to add the parameter data_only=True when reading the file, so that the number can be returned, otherwise the string will be returned, that is, the formula itself.

# Direct assignment sheet ['A1']. Value = formula assignment sheet ['A6']. Value =' = SUM (A1:A5)'

Alternatively, you can use sheet.append (parameters) to write one or more lines

# write one line row = [1, 2, 3, 4, 5] sheet.append (row) # write multiple lines rows = [['ID',' Name', 'Department'], [' 001, 'Lee','CS'], [' 002, 'John','MA'], [' 003, 'Amy','IS']] sheet.append (rows) (II) Save the file

After writing the file, use workbook.save (path+filename) to save it, but be aware that the file extension must be in xlsx format

# Save the file to the current directory wb.save ('new_file.xlsx') to set the cell style

Cell styles mainly include fonts, borders, colors and alignments, all of which are located in the openpyxl.styles library

# Import fonts, borders, colors and alignment related libraries from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment (1) fonts

Set the font style through the sheet cell font property

# set font style to Times New Roman, size 16, bold, italic, blue sheet ['A1']. Font = Font (name='Times New Roman', size=16, bold=True, italic=True, color=colors.BLUE) (II) alignment

Set the text alignment style through the sheet cell alignment property

# set the alignment of text in the cell through the parameters horizontal and vertical. In addition, the setting values can also be left and rightsheet ['B1']. Alignment = Alignment (horizontal='center',vertical='center') (III) border

Set the font style through the sheet cell border property

# first set the types of lines in the four directions of the border left, right, top, bottom= [Side (style='thin', color='000000')] * "then pass the directional lines as parameters to the Border method sheet ['C1']. Border = Border (left=left, right=right, top=top, bottom=bottom) (4) set row height and column width

The length and size of rows and columns can be set by row_dimensions [serial number]. Height and column_dimensions [label]. Width

# set row height sheet.row_dimensions [1] .height = 2 sets column width sheet.column_dimensions ['D'] .width = 15.5 (v) merge and split cells

The merging and splitting of cells are mainly realized through sheet's merge_cells (args1:args2) and unmerge_cells (args1:args2) methods.

Of course, in addition to diagonal rectangle regionalization merge, you can also merge a row or column, as long as you modify the parameters accordingly. Note, however, that the text content displayed in the merged cell is the contents of the top-left cell before the merge, while the contents of other cells are automatically cleared.

# merge cell sheet.merge _ cells ('A1purB2') # split cell sheet.unmerge _ cells (' A1purB2') this ends the study of "how to use the openpyxl module in Python", hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.