python使用openpyxl操作excel

 

def getDataFromExcel(excelPath):
    file = load_workbook(excelPath)
    sheetnames = file.sheetnames
    print(sheetnames)
    ws = file[sheetnames[0]]
    for i in range(2, ws.max_row+1):
        print(ws.cell(i, 1).value)
    file.close()

 

def initExcel():
    file_path = "test.xlsx"
    file = load_workbook(file_path)
    table = file["Sheet1"]
    #写入
    for i in range(2, 22):
        table["C" + str(i)] = "123"
    file.save(file_path)
　　 # 读取
　　 print(table["E33"].value)
    file.close()

读取公式的计算结果：

# 重启应用 安装win32com，命令为python -m pip install pypiwin32
from win32com.client import Dispatch

def openExcel(filename):
    xlApp = Dispatch("Excel.Application")
    xlApp.Visible = False
    xlBook = xlApp.Workbooks.Open(filename)
    xlBook.Save()
    xlBook.Close()

openExcel("C:/test.xlsx")
# 必须使用data_only=True，否则打印出来的是公式
file1 = load_workbook(file_path, data_only=True)
table1 = file1["Step 2"]
print(table1["E33"].value)