preface

When you need to do a lot of repetitive Excel operations every day, it can be time-consuming and tedious to do them manually, but Python provides a number of modules for working with Excel that free up our hands from the tedious work.

Today, I would like to share with you a quick Excel module OpenPyXL, which is more complete than other modules, enough to deal with daily problems.

Openpyxl installation

Enter directly at the command prompt.

pip install openpyxl
Copy the code

Or use douban image installation.

pip install -i https://pypi.douban.com/simple openpyxl
Copy the code

After the installation is successful, how to use it

Open/create a workbook

Sample workbook Worksheet [First Prize]Worksheet [Second Prize]

(1) Open the local workbook

>>> from openpyxl import load_workbook
>>> wb = load_workbook('List of winners. XLSX')
Copy the code

(2) Create an empty workbook

>>> from openpyxl import Workbook
>>> wb1 = Workbook()
Copy the code

Access to worksheets

Create a new worksheet, specifying where to insert it (0: first, -1: last).

>>> wb.create_sheet('new_sheet'.0)
<Worksheet "new_sheet">
Copy the code

Gets all the worksheets in the workbook.

>>> wb.sheetnames
['new_sheet'.'First Prize'.'Second prize']
Copy the code

Use list-derived traversal to get all worksheet names.

>>> [sheet.title for sheet in wb]
['new_sheet'.'First Prize'.'Second prize']
Copy the code

Use WB [SheetName] to get a worksheet object

>>> wb['Second prize']
<Worksheet "Second prize">
Copy the code

Get the active table (that is, open the worksheet that appears first in the workbook).

>>> wb.active
<Worksheet "First Prize">
Copy the code

Gets the worksheet row and column information.

>>> sheet1 = wb['First Prize']
>>> sheet1.max_column
7
>>> sheet1.max_row
6
Copy the code

Get cell information

Access a cell

>>> sheet1['D3']
<Cell 'First Prize'.D3>
>>> sheet1.cell(row=3, column=4)
<Cell 'First Prize'.D3>
Copy the code

Adding the value parameter to access cell format modifies the value of the current cell.

>>> sheet1.cell(3.4).value
'Extraction and Management of Medical Staff Information Based on Spark and Python'
>>> sheet1.cell(3.4, value='Python')
<Cell 'First Prize'.D3>
>>> sheet1.cell(3.4).value
'Python'
Copy the code

Gets the cell’s value, coordinates, row index, column index.

>>> sheet1['D3'].value
'Python'
>>> sheet1['D3'].coordinate
'D3'
>>> sheet1['D3'].row
3
>>> sheet1['D3'].column
4
Copy the code

Access multiple cells

Using slices to access multiple cells is different from a list slice, which is front closed and then open, as in Excel, which is front closed and then closed.

(1) Select cells in area A1:B2.

>>> sheet1['A1':'B2']
((<Cell 'First Prize'.A1>,
  <Cell 'First Prize'.B1>),
 (<Cell 'First Prize'.A2>,
  <Cell 'First Prize'.B2>))
Copy the code

Select single column data.

>>> sheet1['D']
(<Cell 'First Prize'.D1>,
    ...
 <Cell 'First Prize'.D6>)
Copy the code

Select columns B and C.

 >>> sheet1['B:C']
((<Cell 'First Prize'.B1>,
    ...
  <Cell 'First Prize'.B6>),
 (<Cell 'First Prize'.C1>,
    ...
  <Cell 'First Prize'.C6>))
Copy the code

Select a single row of data.

>>> sheet1[3]
(<Cell 'First Prize'.A3>,
 <Cell 'First Prize'.B3>,
    ...
 <Cell 'First Prize'.F3>,
 <Cell 'First Prize'.G3>)
Copy the code

Select row 2,3.

>>> sheet1[2:3]
((<Cell 'First Prize'.A2>,
    ...
  <Cell 'First Prize'.G2>),
 (<Cell 'First Prize'.A3>,
    ...
  <Cell 'First Prize'.G3>))
Copy the code

Iterate to get the data

Row through the specified range (B2:C3) data.

>>> for row in  sheet1.iter_rows(min_row=2, max_row=3, min_col=2, max_col=3) :for cell in  row:
            print(cell.coordinate)

B2
C2
B3
C3
Copy the code

Traverses the specified range (B2:C3) data by column.

>>> for col in  sheet1.iter_cols(min_row=2, max_row=3, min_col=2, max_col=3) :for cell in col:
            print(cell.coordinate)

B2
B3
C2
C3
Copy the code

ifiter_rows()/iter_cols()Specified parameters invalues_only=True, then only the value of the cell will be returned

Walk through all the data line by line.

>>> tuple(sheet1.rows)
((<Cell 'First Prize'.A1>,
  ...
  <Cell 'First Prize'.G1>), ... . (<Cell'First Prize'.A6>,
  ...
  <Cell 'First Prize'.G6>))
Copy the code

Iterate through all data by column.

>>> tuple(sheet1.columns)
((<Cell 'First Prize'.A1>,
  ...
  <Cell 'First Prize'.A6>), ... . (<Cell'First Prize'.G1>, 
  ...
  <Cell 'First Prize'.G6>))
Copy the code

Modify worksheet

Cell assignment

Add a column to count authors.

>>> for row_index in range(2, sheet1.max_row + 1):
	      sheet1.cell(row_index, 8).value = len(sheet1.cell(row_index, 6).value.split(', '))
Copy the code

The formula is used to assign values to cells, and H7 counts the total number of authors.

>>> sheet1['H7'] = '=SUM(H1:H6)'
Copy the code

Append a row of data

Use lists to pass in values in order.

>>> sheet1.append([str(n) for n in range(6)])
Copy the code

Use a dictionary to specifyColumn index: column value 。

>>> sheet1.append({'A':'1'.'C':'3'})
Copy the code

Insert blank line

Insert blank row at specified position, idX row index, insert position; Amount Number of blank rows inserted

>>> sheet1.insert_rows(idx=2, amount=2)
Copy the code

Delete worksheet

>>> wb.remove(wb['new_sheet'])
Copy the code

Save workbook

>>> wb.save('List of winners v1.xlsx')
Copy the code

Modify the style

The font

Set B2 cell font format to, color can be hexadecimal color code.

>>> from openpyxl.styles import Font

>>> new_font = Font(name=Microsoft Yahei, size=20, color='3333CC', bold=True)
>>> sheet1['B2'].font = new_font
Copy the code

Cell background color

>>> from openpyxl.styles import PatternFill, colors
>>> sheet1["A2"].fill = PatternFill("solid", fgColor=colors.BLUE)
>>> sheet1["A3"].fill = PatternFill("solid", fgColor='FF66CC')
Copy the code

alignment

Center data vertically and horizontally in D2.

>>> from openpyxl.styles import Alignment
>>> sheet1['D2'].alignment = Alignment(horizontal='center', vertical='center')
Copy the code

Row height/column width

Set the height of row 2 to 40 and the width of column C to 20.

>>> sheet1.row_dimensions[2].height = 40
>>> sheet1.column_dimensions['C'].width = 20
Copy the code

Merge/split cells

To merge cells, you only need to specify the coordinates of the upper-left and lower-right cells.

>>> sheet.merge_cells('A1:C3')
Copy the code

After splitting the cell, the value of the merged region is assigned to the upper-left cell A1.

>>> sheet.unmerge_cells('A1:C3')
Copy the code

For startersPythonOr they want to get startedPythonFriends, can be through the belowSmall cards contact the authorSometimes a simple question is stuck for a long time, but it may be a bit of others will suddenly see the light, heartfelt hope that we can make progress together.