A tutorial on Python's use of OPENPYXL to read and write Excel

Python uses OPENPYXL to read and write Excel files

This is a third-party library that can handle xlsx the format of an Excel file. pip install openpyxlinstallation. If you use Aanconda, you should bring your own.

Reading Excel files

You need to import related functions.

From OPENPYXL import load_workbook# is read-write by default and can be specified write_only and read_only for truewb = Load_workbook (' mainbuilding33.xlsx ') if required

The default open file is read-write, and you can specify the parameter if necessary read_only True .

get worksheet--sheet

# get all sheet name print (Wb.get_sheet_names ()) # Get Sheeta_sheet = Wb.get_sheet_by_name (' Sheet1 ') by sheet name # get sheet name print ( A_sheet.title) # Get the sheet that is currently being displayed, or you can use Wb.get_active_sheet () sheet = wb.active

Get cell

# Get the value of a cell, and observe that Excel finds that it is also preceded by the alphabetical order of the numbers, that is, column after row b4 = sheet[' B4 ']# return print (f ' ({B4.column}, {B4.row}) is {b4.value} ')  # The number returned is the int # # In addition to the subscript to obtain, you can also use the cell function, replaced by a number, this represents b2b4_too = Sheet.cell (row=4, column=2) print (B4_too.value)

b4.columnReturns B b4.row 4, which is the value of that cell. In addition, the cell has a property coordinate , like B4, which returns coordinates B4 .

Get maximum row and maximum columns

# Get maximum column and maximum line print (Sheet.max_row) print (Sheet.max_column)

Get rows and columns

• sheet.rowsis the generator, which is the data for each row, and each row is wrapped by a tuple.

• sheet.columnsSimilar, but inside is each tuple is the cell of each column.

# because by row, so return A1, B1, C1 such order for row in sheet.rows:for cells in Row:print (cell.value) # A1, A2, A3 Order for column in Sheet.colu Mns:for cell in Column:print (Cell.value)

The above code will get the data for all the cells. What if you want to get the data for a row? Give it an index, because sheet.rows it is a generator type, you cannot use an index, you can use the index after you convert it to a list, and then list(sheet.rows)[2] you get the tuple object to the second row.

For cell in list (sheet.rows) [2]:p rint (cell.value)

How do I get a cell of any interval?

You can use the range function, as shown below, to get all the cells with A1 as the upper-left corner and B3 as the lower-right corner of the rectangular area. Note that range starting with 1, because in OPENPYXL, in order to be consistent with the way in which Excel is expressed, it is not the habit of programming languages to represent the first value with 0.

For I in range (1, 4): for J in range (1, 3):p rint (Sheet.cell (row=i, Column=j))        # Out<cell Mainbuilding33. A1><cell Mainbuilding33. B1><cell Mainbuilding33. A2><cell Mainbuilding33. B2><cell Mainbuilding33. A3><cell Mainbuilding33. B3>

You can also use it as you would with slices. sheet['A1':'B3']returns a tuple that is internal to a tuple, consisting of cells from each row.

For Row_cell in sheet[' A1 ': ' B3 ']:for cells in Row_cell:print (cell) for        cell in sheet[' A1 ': ' B3 ']:p rint (cell) # Out (< Cell Mainbuilding33. A1>, <cell mainbuilding33. b1>) (<cell mainbuilding33. A2>, <cell mainbuilding33. b2>) (<cell mainbuilding33. A3>, <cell mainbuilding33. b3>)

Gets the column number according to the letter, returns the letter according to the column number

Need to be imported, these two functions exist in theopenpyxl.utils

From openpyxl.utils import Get_column_letter, column_index_from_string# returns the letter print (Get_column_letter (2)) according to the number of the column  # b# number Print (column_index_from_string (' D ')) # 4 According to the Letter return column  

Writing data to Excel

Worksheet related

Need to importWorkBook

From OPENPYXL Import WORKBOOKWB = Workbook ()

This creates a new worksheet (just not yet saved).

To specify write-only mode , you can specify parameters write_only=True . The general default writable readable mode is available.

Print (Wb.get_sheet_names ())  # provides a default name sheet table, office2016 under the new provide default sheet1# direct assignment can be changed to work table names Sheet.title = ' Sheet1 ' # Create a new worksheet, you can specify an index, arrange its position in the workbook appropriately wb.create_sheet (' Data ', index=1)  # is scheduled to the second sheet, index=0 is the first position # Delete a worksheet wb.remove ( Sheet) del Wb[sheet]

Write cells

You can use formulas, too.

# Assign values directly to the cell line sheet[' A1 ' = ' good ' # B9 write average sheet[' B9 '] = ' =average (b2:b8) '

But if it is read to add data_only=True this to read B9 return is the number, if not add this parameter, will return the formula itself ' =average (B2:B8) '

Append function

You can add more than one row of data at a time, starting with a blank line in the first row (all blank lines below).

# Add a row of row = [1, 2, 3, 4, 5]sheet.append (Row) # # To add multiple lines of rows = [    [' Number ', ' data1 ', ' data2 '], [2, +, +], [3,    40, [4, +], [    5, [6], 5], [    7, 50, 10],

Because a append function can only be written by rows . If we want to write by column. Can append meet demand? If the above list is nested as a matrix. Just transpose the matrix to the right. It zip() can be implemented using functions, but the internal list becomes a tuple. are iterative objects and do not affect them.

List (Zip (*rows)) # out[(' number ', 2, 3, 4, 5, 6, 7), (' Data1 ', Max, Max,,, +), (' Data2 ', 30, 25, 30, 10, 5, 10)]

Explain the above list(zip(*rows)) first break *rows the list, equivalent to fill in a number of parameters, zip from a list to extract the 1th value into a tuple, and then extract the 2nd value from each list to combine into a tuple, until the shortest list After the last value is finished, the value of the longer list is discarded, in other words, the last tuple count is determined by the shortest length of each parameter (the object can be iterated). For example, now casually delete a value, the shortest list length of 2,data2 that column (vertical view) of the values are all discarded.

rows = [    [' Number ', ' data1 ', ' data2 '],    [2, +], [3, +, +], [4, +, +], [    5,    6, 25],    [7, 10],]# out[(' number ', 2, 3, 4, 5, 6, 7), (' Data1 ', 40, 40, 50, 30, 25, 50)]

The last zip returns a Zip object that does not see the data. It list 's good to use the conversion. zipyou can write data in columns easily by using the.

Save File

Always remember to save the file after all the actions have been completed. Specifies the path and file name, with the suffix name xlsx .

Wb.save (R ' D:\example.xlsx ')

Set cell style--style

Import the required classes firstfrom openpyxl.styles import Font, colors, Alignment

You can specify font correlation, color, and alignment, respectively.

Font

Bold_itatic_24_font = Font (name= ' etc line ', size=24, Italic=true, Color=colors. RED, Bold=true) sheet[' A1 '].font = Bold_itatic_24_font

The above code specifies the equal line 24th bold italic, the font color red. Use the properties of the cell directly to font assign the Font object to it.

Alignment

It is also a direct use of the cell's properties aligment , which specify vertical centering and horizontal centering. In addition to the center, you can also use parameters such as right、left .

# Set the data in B1 vertically centered and horizontally centered sheet[' B1 '].alignment = Alignment (horizontal= ' center ', vertical= ' center ')

Set row height and column widths

Sometimes the data is too long to display, you need to lengthen the height of the cell.

# line 2nd row High sheet.row_dimensions[2].height = 40# C column width sheet.column_dimensions[' c '].width = 30

Merging and splitting cells

The so-called merged cell, which is the base of the cell in the upper-left corner of the merged region, overrides the other cells to make it a large cell.

Instead, the value of the large cell is returned to the original upper-left position after the cell is split.

# merge cells, write data to the upper left corner sheet.merge_cells (' B1:g1 ') # Merge several cells in a row sheet.merge_cells (' a1:c3 ') # merge cells in a rectangular area

After merging, you can only write data to the upper-left corner, which : is the left coordinate of the interval.

If the cells that are to be merged have data, only the upper-left corner of the data is preserved, and others are discarded. In other words , if the data is not written in the upper-left corner before merging, there will be no data in the merged cell.

The following is the code that splits the cells. After splitting, the value returns to the A1 position.

Sheet.unmerge_cells (' a1:c3 ')

Here is the commonly used to say, specific to see the OPENPYXL document