pythonopenpyxl的使用方法

目录

• 生成新Excel
• 创建Sheet的三种方法
• 给Sheet中某cell赋值
• 赋值cell的值
• 读取Excel
• 加载文件
• 获取sheet
• 一个例子
  
• 首先创建一个tab页
  
• 收集数据
• 保存总成绩
  
• 总结
  

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()

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)

wb.save(filename=dest_filename)

ws1 = wb.create_sheet("Mysheet") # 在末尾添加
# or
ws2 = wb.create_sheet("Mysheet", 0) # 在开头添加
# or
ws3 = wb.create_sheet("Mysheet", -1) # 在倒数第二位添加

ws2["F5"] = 3.14

ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

from openpyxl import load_workbook
wb = load_workbook(filename = "empty_book.xlsx")
sheet_ranges = wb["range names"]
print(sheet_ranges["D18"].value)

wb = load_workbook(filename = "empty_book.xlsx")

sheet_ranges = wb["range names"]

ws = wb["总成绩"]

if not "总成绩" in wb.sheetnames:
    wb.create_sheet("总成绩")
ws = wb["总成绩"]

# 对每个tab中的分数数据进行收集，放入对应的数据行中

for pos, tabName in enumerate(wb.sheetnames):
    if tabName != "总成绩":
        wstt = wb[tabName]
        for row in wstt.iter_rows(min_row=2, values_Only=True):
            for ind, code in enumerate(ws["A"]):
                if code.value == row[0]:
                    ws.cell(ind + 1, 4 + pos, row[2])
                    break

for po, row in enumerate(ws.iter_rows(min_row=2, values_Only=True)):
    ws.cell(po + 1, 3, 0 + row[3] + row[4] + row[5] + row[6] + row[7] + row[8] + row[9])

ws.cell(po + 1, 3, 0 + row[3] + row[4] + row[5] + row[6] + row[7] + row[8] + row[9])
TypeError: unsupported operand type(s) for +: "int" and "NoneType"