Article directory
- Openpyxl operates Excel files
-
-
-
- 1. Install openpyxl third-party library
- 2.openpyxl reads Excel files
- 3. Flexibly obtain data in Excel files
- 4.Openpyxl writes Excel files
- 5. Flexibly write and operate Excel files
Openpyxl operates Excel files
1. Install openpyxl third-party library
pip install openpyxl -i Python domestic mirror source address (searched in Baidu, if the protocol name cannot be used, change it to https)
Or permanently change the pip package management tool installation address
pip/pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
Then pip install package name
2.openpyxl reads Excel files
import openpyxl
# This third-party library can only operate Excel files with the suffix .xlsx
# 1. Open the Excel file and obtain a workbook object
# 1) openpyxl.open (excel file path)
# 2) openpyxl.load_workbook (excel file path)
wb = openpyxl.open('./files/data.xlsx')
# 2. Get the table names of all worksheets in the workbook
# workbook object.sheetnames
names = wb.sheetnames
print(names)
# 3. Get the worksheet
# 1) Workbook object.active - Get the active table (the selected table) in the current Excel file
# 2) Workbook object [table name] - Get the worksheet corresponding to the specified table name
sheet1 = wb.active
print(sheet1)
sheet2 = wb['original data']
print(sheet2)
# 4. Get the maximum number of rows and columns
# worksheet.max_row - Get the maximum number of rows in the specified worksheet
# worksheet.max_column - Get the maximum number of columns in the specified worksheet
m_r = sheet2.max_row
m_c = sheet2.max_column
print(m_r, m_c)
# 5. Get cells
# Worksheet.cell(row number, column number)
cell1 = sheet2.cell(2, 1)
cell2 = sheet2.cell(19, 3)
# 6. Get the content in the cell
#cell.value
print(cell1.value, cell2.value)
3. Flexible acquisition of data in Excel files
import openpyxl
wb = openpyxl.load_workbook('files/data.xlsx')
sheet = wb['original data']
# sheet = wb.active
# 1. Get all the data in the specified row
row = 4
for col in range(1, sheet.max_column + 1):
cell = sheet.cell(row, col)
print(cell.value)
print('-----------------------------Gorgeous dividing line------------- ----------------')
# 2. Get all the data in the specified column
col=2
for row in range(1, sheet.max_row + 1):
cell = sheet.cell(row, col)
print(cell.value)
print('-----------------------------Gorgeous dividing line------------- ----------------')
# 3. Get all the scores of each student
for row in range(1, sheet.max_row + 1):
for col in range(1, sheet.max_column + 1):
cell = sheet.cell(row, col)
print(cell.value)
print('------------------')
# 4. Get all the grades of each student and save them in a list. The elements in the list are dictionaries.
# [{'Name': 'Li Nan', 'English': 70, 'Office software operation': 96, 'E-commerce': 73, 'Computer basics': 79}, ]
all_students = []
for row in range(2, sheet.max_row + 1):
stu = {<!-- -->}
for col in range(1, sheet.max_column + 1):
data_cell = sheet.cell(row, col)
key_cell = sheet.cell(1, col)
# stu[key_cell.value] = data_cell.value
stu.setdefault(key_cell.value, data_cell.value)
all_students.append(stu)
print(all_students)
4.openpyxl writes Excel files
import openpyxl
# 1. Create a workbook (create a new excel file)
wb = openpyxl.Workbook()
wb1 = openpyxl.load_workbook('files/data.xlsx')
# 2. Create a new worksheet
# 1) Create a new worksheet with the default name
sheet1 = wb.create_sheet()
# 2) Create a new worksheet using the specified name
sheet2 = wb.create_sheet('student')
# 3) Insert the worksheet with the specified name before the specified position
sheet3 = wb.create_sheet('teacher', 0)
# 4) Create a new table in the existing workbook
if 'movie information' not in wb1.sheetnames:
sheet4 = wb1.create_sheet('movie information')
# 3. Delete worksheet
# Workbook.remove(worksheet object)
if 'Sheet1' in wb1.sheetnames:
wb1.remove(wb1['Sheet1'])
# 4. Modify cell content
#Cell object.value = data
sheet5 = wb1['original data']
# 1) Add content
sheet5.cell(1, 6).value = 'Average score'
# 2) Modify content
sheet5.cell(6, 2).value = '(absent)'
# 3) Delete cell content
sheet5.cell(2, 2).value = None
# Save the excel file
wb.save('files/data2.xlsx')
wb1.save('files/data.xlsx')
5. Flexible writing and operation of Excel files
data = [
{<!-- -->'Name': 'Li Nan', 'English': 78, 'Office software operation': 96, 'E-commerce': 73, 'Computer basics': 79},
{<!-- -->'Name': 'Fang Peng', 'English': 63, 'Office software operation': 94, 'E-commerce': 91, 'Computer basics': 78},
{<!-- -->'Name': 'Li Lei', 'English': 89, 'Office software operation': 65, 'E-commerce': 0, 'Computer Basics': 0},
{<!-- -->'Name': 'Wang Xiaoruo', 'English': 81, 'Office software operation': 77, 'E-commerce': 73, 'Computer basics': 80},
{<!-- -->'Name': 'Chen Yu', 'English': 0, 'Office software operation': 87, 'E-commerce': 84, 'Computer basics': 88},
{<!-- -->'Name': 'Shi Lu', 'English': 91, 'Office software operation': 95, 'E-commerce': 84, 'Computer basics': 80},
{<!-- -->'Name': 'Zhang Ying', 'English': 73, 'Office software operation': 92, 'E-commerce': 92, 'Computer basics': 89},
{<!-- -->'Name': 'Cheng Xiao', 'English': 80, 'Office software operation': 86, 'E-commerce': 0, 'Computer Basics': 0},
{<!-- -->'Name': 'Wang Li', 'English': 72, 'Office software operation': 55, 'E-commerce': 88, 'Computer basics': 70},
{<!-- -->'Name': 'Zhao Junli', 'English': 69, 'Office software operation': 69, 'E-commerce': 63, 'Computer basics': 76},
{<!-- -->'Name': 'Wang Ming', 'English': 96, 'Office software operation': 80, 'E-commerce': 85, 'Computer Basics': 64},
{<!-- -->'Name': 'Li Li', 'English': 64, 'Office software operation': 0, 'E-commerce': 74, 'Computer basics': 86},
{<!-- -->'Name': 'Zhang Fan', 'English': 72, 'Office software operation': 64, 'E-commerce': 97, 'Computer basics': 74},
{<!-- -->'Name': 'Zhang Shanshan', 'English': 60, 'Office software operation': 95, 'E-commerce': 69, 'Computer basics': 61},
{<!-- -->'Name': 'Liu Lili', 'English': 78, 'Office software operation': 93, 'E-commerce': 81, 'Computer basics': 0},
{<!-- -->'Name': 'Shi Jieqing', 'English': 62, 'Office software operation': 63, 'E-commerce': 64, 'Computer basics': 70},
{<!-- -->'Name': 'Lu Yao', 'English': 0, 'Office software operation': 0, 'E-commerce': 0, 'Computer Basics': 63},
{<!-- -->'Name': 'Li Guiming', 'English': 84, 'Office software operation': 90, 'E-commerce': 87, 'Computer basics': 99}
]
data1 = [
{<!-- -->'name': 'Li Nan', 'gender': 'Female', 'score': 1000, 'email': '[email protected]'},
{<!-- -->'name': 'Zhao Junli', 'gender': 'Male', 'score': 567, 'email': '[email protected]'},
{<!-- -->'name': 'Zhang Fan', 'gender': 'Male', 'score': 478, 'email': '[email protected]'},
{<!-- -->'name': 'Wang Xiaoruo', 'gender': 'Female', 'score': 672, 'email': '[email protected]'},
{<!-- -->'name': 'Li Guiming', 'gender': 'Male', 'score': 325, 'email': '[email protected]'}
]
import openpyxl
import os
# 1. If the corresponding excel file already exists, open the file. If it does not exist, create it.
if os.path.exists('files/python data analysis.xlsx'):
wb = openpyxl.load_workbook('files/python data analysis.xlsx')
else:
wb = openpyxl.Workbook()
# 2. If the student information table already exists, get this table. If it does not exist, create this table.
if 'Student information' in wb.sheetnames:
stu_sheet = wb['student information']
else:
stu_sheet = wb.create_sheet('student information')
#Create the table corresponding to the contact information
contact_sheet = wb.create_sheet('student contact information')
# 3. Write data into the table
# 1) Write the header (the first row of content into the worksheet)
col=1
for key in data[0]:
# print(key, col)
stu_sheet.cell(1, col).value = key
col + = 1
# 2) Write all student information starting from the second line
row=2
for study in data:
col=1
for key in stu:
stu_sheet.cell(row, col).value = stu[key]
col + = 1
row + = 1
# ============Write the second set of data==========
# 1) Write the header (the first row of content into the worksheet)
col=1
for key in data1[0]:
# print(key, col)
contact_sheet.cell(1, col).value = key
col + = 1
# 2) Write all student information starting from the second line
row=2
for stu in data1:
col=1
for key in stu:
contact_sheet.cell(row, col).value = stu[key]
col + = 1
row + = 1
# save data
wb.save('files/python data analysis.xlsx')
