openpyxl 使用
由于 xlrd 和xlwt 不在更新并阉割了自己的能力来推荐使用这个库，那就使用这个库吧。
安装
pip install openpyxl
# 如果涉及图片处理
pip install pillow
写入
from openpyxl import Workbook # Workbook excel 对象
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active # 激活工作区 == 返回一个默认sheet页
# 其他创建sheet 页方法
ws1 = wb.create_sheet("Mysheet")
ws2 = wb.create_sheet("Mysheet", 0)
ws3 = wb.create_sheet("Mysheet", -1)
# 更改sheet名
ws.title = "New Title"
# 调整列宽
ws.column_dimensions['A'].width = 20.0
# 调整行高
ws.row_dimensions[1].height = 40
# 默认情况下，包含此标题的选项卡的背景色为白色。您可以通过向工作表提供RRGGBB颜色代码来更改此设置。床单属性。tabColor属性
ws.sheet_properties.tabColor = "1072BA"
# 通过名字取值
ws3 = wb["New Title"]
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
AA
wb.save(filename = dest_filename)
插入图片
from openpyxl.drawing.image import Image
wb = Workbook
ws = actuve
img = Image("aa.png")
# 设置图片宽高
size = (90,90)
img.width,img.height = size
ws.add_image(img,"A1")
wb.save("test.xlsx")from openpyxl import Workbook
wb = Workbook()读取
from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)
def read_answer_file(answer_file):
"""
读答案数据
return
{A1:[],B1:[]} A1 必须文件名 文件名必须唯一
"""
wb = load_workbook(filename=answer_file)
sheet = wb[wb.sheetnames[0]]
print(sheet.max_row)
print(sheet.max_column)
columns = sheet.columns
print(list(columns))
r_dict = {}
for index, row in enumerate(sheet.rows):
if index == 0:
r_dict = {i.value: [] for i in row}
for index, row in enumerate(sheet.columns):
row_list = [i.value for i in row]
if row_list[0] in r_dict:
r_dict[row_list[0]] = row_list[1:]
print("答案数据读取：", r_dict)
return r_dict