preface
When you need to do a lot of repeated operations on Excel every day, if you only rely on manual work, it is not only a waste of time, but also very boring. Fortunately, Python provides us with many modules for operating excel, which can free us from tedious work.
Today, I'll share with you a module openpyxl for rapid processing of Excel. Its functions are more complete than other modules, which is enough to deal with daily problems.
openpyxl installation
Enter directly at the command prompt.
pip install openpyxl
Or use watercress image installation.
pip install -i https://pypi.douban.com/simple openpyxl
After the installation is successful, let's see how to use it
Open / create Workbook
Sample Workbook
Worksheet [first prize]
Worksheet [Second Prize]
(1) Open local Workbook
>>> from openpyxl import load_workbook
>>> wb = load_workbook('List of winners.xlsx')
(2) Create an empty Workbook
>>> from openpyxl import Workbook >>> wb1 = Workbook()
Accessing worksheets
To create a new worksheet, you can specify the insertion position (0: first, 1: end).
>>> wb.create_sheet('new_sheet', 0)
<Worksheet "new_sheet">
Gets all worksheets in the workbook.
>>> wb.sheetnames ['new_sheet', 'the first prize', 'second award']
Use the list derivation traversal to get all sheet names.
>>> [sheet.title for sheet in wb] ['new_sheet', 'the first prize', 'second award']
Use wb[sheetname] to get a worksheet object
>>> wb['second award'] <Worksheet "second award">
Gets the active sheet (that is, the sheet that appears first when the workbook is opened).
>>> wb.active <Worksheet "the first prize">
Get worksheet row and column information.
>>> sheet1 = wb['the first prize'] >>> sheet1.max_column 7 >>> sheet1.max_row 6
Get cell information
Access a cell
>>> sheet1['D3'] <Cell 'the first prize'.D3> >>> sheet1.cell(row=3, column=4) <Cell 'the first prize'.D3>
If the value parameter is added to access the cell format, the value of the current cell will be modified.
>>> sheet1.cell(3, 4).value 'be based on Spark,Python Information extraction and management of medical staff based on' >>> sheet1.cell(3, 4, value='Python') <Cell 'the first prize'.D3> >>> sheet1.cell(3, 4).value 'Python'
Get the value, coordinates, row index and column index of the cell.
>>> sheet1['D3'].value 'Python' >>> sheet1['D3'].coordinate 'D3' >>> sheet1['D3'].row 3 >>> sheet1['D3'].column 4
Access multiple cells
The slice is used to access multiple cells. The slice here is different from the list slice. The list slice is closed before opening, and the slice in Excel is closed before closing and closed after closing.
(1) Select the cells in the A1:B2 range.
>>> sheet1['A1':'B2'] ((<Cell 'the first prize'.A1>, <Cell 'the first prize'.B1>), (<Cell 'the first prize'.A2>, <Cell 'the first prize'.B2>))
Select a single column of data.
>>> sheet1['D']
(<Cell 'the first prize'.D1>,
...
<Cell 'the first prize'.D6>)
Select column B and C data.
>>> sheet1['B:C']
((<Cell 'the first prize'.B1>,
...
<Cell 'the first prize'.B6>),
(<Cell 'the first prize'.C1>,
...
<Cell 'the first prize'.C6>))
Select single line data.
>>> sheet1[3]
(<Cell 'the first prize'.A3>,
<Cell 'the first prize'.B3>,
...
<Cell 'the first prize'.F3>,
<Cell 'the first prize'.G3>)
Select row 2 and 3 data.
>>> sheet1[2:3]
((<Cell 'the first prize'.A2>,
...
<Cell 'the first prize'.G2>),
(<Cell 'the first prize'.A3>,
...
<Cell 'the first prize'.G3>))
Traversal to get data
Traverse the specified range (B2:C3) data by row.
>>> 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
Traverse 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
If ITER_ rows()/iter_ Specify the parameter values in cols()_ Only = true, only the value of the cell will be returned
Traverse all data by row.
>>> tuple(sheet1.rows) ((<Cell 'the first prize'.A1>, ... <Cell 'the first prize'.G1>), ... ... (<Cell 'the first prize'.A6>, ... <Cell 'the first prize'.G6>))
Traverse all data by column.
>>> tuple(sheet1.columns) ((<Cell 'the first prize'.A1>, ... <Cell 'the first prize'.A6>), ... ... (<Cell 'the first prize'.G1>, ... <Cell 'the first prize'.G6>))
Modify worksheet
Cell assignment
Add a new column to calculate the number of 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(','))
Use the formula to assign a value to the cell, and H7 count the total number of authors.
>>> sheet1['H7'] = '=SUM(H1:H6)'
Append a row of data
Use the list to pass in values in order.
>>> sheet1.append([str(n) for n in range(6)])
Specify column index using dictionary: column value.
>>> sheet1.append({'A':'1','C':'3'})
Insert blank line
Insert a blank row, idx row index and the insertion position at the specified position; amount inserts the number of blank lines
>>> sheet1.insert_rows(idx=2, amount=2)
Delete sheet
>>> wb.remove(wb['new_sheet'])
Save Workbook
>>> wb.save('List of winners V1.xlsx')
modify style
typeface
Set the font format of B2 cell to, and the color can use 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
Cell background color
>>> from openpyxl.styles import PatternFill, colors
>>> sheet1["A2"].fill = PatternFill("solid", fgColor=colors.BLUE)
>>> sheet1["A3"].fill = PatternFill("solid", fgColor='FF66CC')
Alignment
Set the vertical center and horizontal center of data in D2.
>>> from openpyxl.styles import Alignment >>> sheet1['D2'].alignment = Alignment(horizontal='center', vertical='center')
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
Merge / split cells
To merge cells, you only need to specify the cell coordinates in the upper left and lower right corners.
>>> sheet.merge_cells('A1:C3')
After splitting cells, the value of the merged range is assigned to cell A1 in the upper left corner.
>>> sheet.unmerge_cells('A1:C3')
For those who have just started Python or want to start python, you can contact the author through the small card below to communicate and learn together. They all come from novices. Sometimes a simple question card takes a long time, but they may suddenly realize it at the touch of others, and sincerely hope that everyone can make common progress. There are also nearly 1000 sets of resume templates and hundreds of e-books waiting for you to get them!