How to use Python for Excel automation?

1. Commonly used libraries for Python to operate Excel

Before you start operating Excel, you need to install Python and some related libraries. You can use pip to install the following libraries, or use a professional python client: pycharm to quickly install python and related libraries.

• pandas: for processing Excel files and data

• openpyxl: for reading and writing Excel files

• xlrd: used to read Excel files

• xlwt: used to write Excel files

1. Use the The third-party library openpyxl

openpyxl is a Python library used to read and write Excel 2010 xlsx/xlsm/xltx/xltm files. It can read and write Excel files, supports multiple worksheets, charts, and more.

Sample code:

import openpyxl

# 打开 Excel 文件
workbook = openpyxl.load_workbook('example.xlsx')

# 获取所有工作表名
sheet_names = workbook.sheetnames
print(sheet_names)

# 获取指定工作表
sheet = workbook['Sheet1']

# 获取单元格数据
cell = sheet['A1']
print(cell.value)

# 修改单元格数据
sheet['A1'] = 'Hello World'

# 保存 Excel 文件
workbook.save('example.xlsx')

2. Use third-party libraries xlrd and xlwt

xlrd and xlwt are used to read and write Excel files respectively, supporting multiple worksheets , but Excel 2010 xlsx/xlsm/xltx/xltm format is not supported.

Sample code:

import xlrd
import xlwt

# 打开 Excel 文件
workbook = xlrd.open_workbook('example.xls')

# 获取所有工作表名
sheet_names = workbook.sheet_names()
print(sheet_names)

# 获取指定工作表
sheet = workbook.sheet_by_name('Sheet1')

# 获取单元格数据
cell = sheet.cell(0, 0)
print(cell.value)

# 修改单元格数据
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet('Sheet1')
new_sheet.write(0, 0, 'Hello World')
new_workbook.save('example.xls')

3. Use the pandas library

pandas is a Python library for data analysis. It can also be used to read and write Excel files. It supports multiple Worksheet, but Excel 2010 xlsx/xlsm/xltx/xltm format is not supported.

Sample code:

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('example.xls', sheet_name='Sheet1')

# 获取单元格数据
value = df.iloc[0, 0]
print(value)

# 修改单元格数据
df.iloc[0, 0] = 'Hello World'
df.to_excel('example.xls', index=False)

2. 10 common methods for operating excel in Python

1. Read Excel files

Use read_excel in the pandas library () function can read Excel files. The sample code is as follows:

import pandas as pd

# 读取Excel文件
df = pd.read_excel('example.xlsx')

2. Write to Excel file

Use the to_excel() function in the pandas library to write data to an Excel file. The sample code is as follows:

import pandas as pd

# 将数据写入Excel文件
df.to_excel('example.xlsx', index=False)

3. Insert rows or columns

Use the append() function in the pandas library to insert rows or columns. The sample code is as follows:

import pandas as pd

# 插入行
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df = df.append({'A': 4, 'B': 7}, ignore_index=True)

# 插入列
df['C'] = [7, 8, 9, 10]

4. Delete rows or columns

Use the drop() function in the pandas library to delete rows or columns. The sample code is as follows:

import pandas as pd

# 删除行
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df = df.drop(1)

# 删除列
df = df.drop('B', axis=1)

5. Modify the cell value

Use the at() function or .iat() function in the pandas library to modify the cell value. The sample code is as follows:

import pandas as pd

# 修改单元格值
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df.at[1, 'B'] = 7

# 使用.iat()函数修改单元格值
df.iat[0, 1] = 8

6. Find the cell value

Use the .loc() function or .iloc() function in the pandas library to find the cell value. The sample code is as follows:

import pandas as pd

# 查找单元格值
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
value = df.loc[1, 'B']

# 使用.iloc()函数查找单元格值
value = df.iloc[1, 1]

7. Sorting data

Use the sort_values() function in the pandas library to sort the data. The sample code is as follows:

import pandas as pd

# 对数据进行排序
df = pd.DataFrame({'A': [1, 3, 2], 'B': [4, 6, 5]})
df = df.sort_values(by='A')

8. Merge data

Use the merge() function in the pandas library to merge data. The sample code is as follows:

import pandas as pd

# 合并数据
df1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df2 = pd.DataFrame({'A': [1, 2, 4], 'C': [7, 8, 9]})
df = pd.merge(df1, df2, on='A')

9. Grouping data

Use the groupby() function in the pandas library to group data. The sample code is as follows:

import pandas as pd

# 分组数据
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
                   'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
                   'C': [1, 2, 3, 4, 5, 6, 7, 8]})
grouped = df.groupby(['A', 'B'])

10. Calculate data statistics

Use the describe() function in the pandas library to calculate data statistics. The sample code is as follows:

import pandas as pd

# 计算数据统计量
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
desc = df.describe()

The above is the detailed content of How to use Python for Excel automation?. For more information, please follow other related articles on the PHP Chinese website!