python 操作excel

发表于 | 分类于

安装第三方库：

1
pip install openpyxl

存Excel文件：

1
2
3
4
5
6
7
8
9
10
from openpyxl import Workbook
 
workbook = Workbook()
booksheet = workbook.active     #获取当前活跃的sheet,默认是第一个sheet
#存第一行单元格cell(1,1)
booksheet.cell(1,1).value = 6   #这个方法索引从1开始
booksheet.cell("B1").value = 7
#存一行数据
booksheet.append([11,87])
workbook.save("test_openpyxl.xlsx")

读Excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from openpyxl import load_workbook
 
workbook = load_workbook('D:\\Py_exercise\\test_openpyxl.xlsx')
#booksheet = workbook.active                #获取当前活跃的sheet,默认是第一个sheet
sheets = workbook.get_sheet_names()         #从名称获取sheet
booksheet = workbook.get_sheet_by_name(sheets[0])
 
rows = booksheet.rows
columns = booksheet.columns
#迭代所有的行
for row in rows:
    line = [col.value for col in row]
 
#通过坐标读取值
cell_11 = booksheet.cell('A1').value
cell_11 = booksheet.cell(row=1, column=1).value