python openpyxl

openpyxl 不能处理xls结尾的excel文件,只能处理xlsx结尾的文件.

pandas 1.1.5和xlrd 1.1.0 可以处理xls及xlsx格式的文件.其它版本能否处理需要确认.

 

部分内容

# 1 新建excel
from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active

ws['A1'] = '我们'
ws['B1'] = '是'
ws.append(['中国人'])

wb.save('sample3.xlsx')

# 2显示文档中包含的表单名称
from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')

print('wb.sheetnames', wb.sheetnames)  # wb.sheetnames ['我是第一个表单', '我是第二个表单']

# 3 获取单元格值
from openpyxl import load_workbook
wb = load_workbook('sample.xlsx')

# 根据表单sheet名字获取对应的sheet对象
ws2 = wb.get_sheet_by_name(u"我是第二个表单")
print(ws2["B1"].value, 222)  # 牛肉 222


# 4 获取excel某sheet的行数、列数, 根据行数获取某单元格的值
from openpyxl import load_workbook

wb = load_workbook('app01_car.xlsx')

ws = wb.get_sheet_by_name('app01_car')
nums_row = ws.max_row
nums_column = ws.max_column

for current_row in range(2, nums_row + 1):
    A1_value = ws[current_row][0].value

# 5 批量获取单元格内容
from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')
ws2 = wb.get_sheet_by_name(u"我是第二个表单")
# print([cell.value for cell in ws2['1']])  # ['小样', '牛肉', '大排', '猪肉']
# print([cell.value for cell in ws2['A']])  # ['小样', '小样1', '小样2', '小样3', '小样4']

# print([cell for cell in ws2.iter_rows()])

sheet_list = []

for row in ws2.iter_rows():
    row_list = [cell.value for cell in row]
    sheet_list.append(row_list)
print('sheet_list', sheet_list)

# 6 批量更新: 获取某个区间行的数据
for current_row in range(2, nums_row + 1):
    current_row_value = [cell.value for cell in ws[current_row]]