Create file
Workbook() – create an excel file
Use openpyxl to create the file. Just import the Workbook class
# create an excel file from openpyxl import Workbook wb = Workbook()
Workbook.create_sheet() – create an excel workbook
Workbook.create_sheet() can create a workbook with a custom name. The parameter in the brackets is the name of the workbook, and another parameter is the location of the workbook, which can also be omitted
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet(‘User Information Sheet’, 0) # Create a user information sheet and place it first, as shown below
Multiple workbooks can be created and they are arranged sequentially.
Worksheet.title – Rename the workbook
Use the Worksheet.title property to change the sheet name
from openpyxl import Workbook
wb = Workbook() # create excel file
ws1 = wb.create_sheet('user information sheet', 0) # create workbook user information sheet first
ws2 = wb.create_sheet('bank_info') # create workbook bank_info
ws1.title = 'user_info' # Change the name of the workbook user information table
'''Save it with the code, as shown in the figure below, the user information table is renamed to user_info'''
ws.sheet_properties.tabColor = ” ” modify title background
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
ws1.sheet_properties.tabColor = 'FF6666' # Change the title background to the specified RRGGBB color code
'''Save it with the code, as shown in the figure below'''
Workbook.sheetname – View all workbooks in excel
Returns a list containing the names of all workbooks under the excel file
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
print(wb. sheetnames)
Print Results ['User Information Sheet', 'Sheet']
Workbook.copy_worksheet() – Create a copy of a worksheet
The Workbook.copy_worksheet() method creates a copy of a worksheet within a single workbook. Only cells (including values, styles, hyperlinks, and comments) and certain sheet properties (including dimensions, formatting, and properties) are copied. All other workbook/sheet properties are not copied – e.g. images, charts.
Get modified data
Access a cell
In excel, you can find the content through the location, and you can also use the code to find the content through the location.
Worksheet[ ]
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
ws1['A1'] = 'XWenXiang' # ws1['A1'] represents the A1 position in the table, we can directly assign data to it
'''Save it with the code, as shown in the figure below'''
Worksheet.cell() inserts data at a fixed position
----'In addition to directly obtaining the position, you can also use the Worksheet.cell() method'-----
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
ws1.cell(row=4, column=2, value=10)
This method assigns values by specifying coordinates, where the row parameter represents the number of rows, column represents the number of columns, and value is the filled data.
'''Save it with the code, as shown in the figure below'''
Worksheet.append()
Worksheet.append() can write multiple data at the same time.
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
ws1.append(['name', 'age', 'gender'])
ws1.append(['XWenXiang', '15', 'man']) # use [] to enclose
'''Save it with the code, as shown in the figure below'''
Formula
The formula used in the module is the same as the formula in excel
ws1['A6'] = '=sum(A4:A5)' # sum A4 and A5 and write to A6
Access multiple cells
Cell ranges can be accessed using slices
x = ws['A1':'C2'] x1 = ws['C'] x2 = ws['C:D'] x3 = ws[10] x4 = ws[5:10]
Worksheet.values – get cell values
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet('User Information Sheet', 0)
ws1.append(['name', 'age', 'gender'])
ws1.append(['XWenXiang', '15', 'man'])
for row in ws1.values:
print(row)
Print result:
('name', 'age', 'gender')
('XWenXiang', '15', 'man')
Save the file
The easiest and safest way to save a workbook is to use the Workbook.save() method of the Workbook object.
None of the previous code samples added save code.
wb = Workbook()
wb.save('balances.xlsx') # balances.xlsx is the saved path, that is, the file name.
Save it after editing.
Open file
openpyxl.load_workbook() – read excel file
As with Workbook writing, use openpyxl.load_workbook() to open an existing workbook.
from openpyxl import load_workbook # import load_workbook
wb = load_workbook('ex_a.xlsx', read_only=True, data_only=True) # ex_a.xlsx is the file name
print(wb.sheetnames) # print the names of all workbooks
Read workbook data
Method 1
from openpyxl import load_workbook
\t
wb = load_workbook('ex_a.xlsx', read_only=True, data_only=True)
ws = wb['user information table'] # Get the workbook object
print(ws['A1'].value) # Get the value of A1 in the workbook object
Method 2
from openpyxl import load_workbook
\t
wb = load_workbook('ex_a.xlsx', read_only=True, data_only=True)
ws = wb['user information table']
print(ws.cell(row=2, column=1).value) # Get values in the form of rows and columns
\t
Method 3 (read line by line)
from openpyxl import load_workbook
\t
wb = load_workbook('ex_a.xlsx', read_only=True, data_only=True)
ws = wb['user information table']
for row in ws.rows: # Get the data of each row
for data in row: # Get the data of the cells in each row
print(data.value) # print the value of the cell
If you want to load and append an existing xlsx document, you must import load_workbook
from openpyxl import load_workbook
# Load an existing excel document
workbook = load_workbook('test.xlsx')
# Get the first worksheet
sheet = workbook. active
# add a whole row of data
sheet.append(['user id','gender','nickname','city code','province code'])
# save
workbook. save()
When adding data, it is generally added line by line, which is very convenient and efficient
sheet.append(['user id','gender','nickname','city code','province code']) sheet.append(['78459037','1','Roger Zhuo','441200','440000'])
Read data
For reading, open a worksheet in the same way
workbook = openpyxl.load_workbook("Required data for core indicators of innovation points.xlsx")
# You can use the sheetnames attribute of the workbook object to get which tables in the excel file have data
table = workbook. active
# Get the maximum number of columns
rows = table.max_row
# Get the maximum number of rows
cols = table.max_col
If you want to get the data of which row and column, then use this
table.cell(4, 5).value
That is, get the data of the 4th row and 5th column