python excel pandas openpyxl

 

 

1.遇到的问题

1.1、给带公式的excel写入数据，文件未保存状态，读值为None。

解决方式1：

from win32com.client import Dispatch

# 使用win32com自动打开文件并保存
def just_open(filename):
    xlApp = Dispatch("Excel.Application")
    xlApp.Visible = False
    xlBook = xlApp.Workbooks.Open(filename)
    xlBook.Save()
    xlBook.Close()

xlsx_name = r"D:\zmm\PC\PC_AT\performance.xlsx"
just_open(xlsx_name)
wb = load_workbook(xlsx_name, data_only=True)
ws = wb['spec']
cell = ws["F60"]
print(cell.value)

 

解决方式2:

 

import xlwings as xw
def xlsx_calculate(file_name):
    """
    使用 xlwings 打开 Excel 文件，并且对公式重新计算
    :return:
    """
    # # 需要重载一下这个文件，调整一下激活页
    # workbook = load_workbook(file_name)
    # workbook.active = 2
    # for sheet in workbook:
    #     if sheet.title == 'spec':
    #         sheet.sheet_view.tabSelected = True
    #     else:
    #         sheet.sheet_view.tabSelected = False
    # workbook.save(file_name)

    app = xw.App(visible=False)
    workbook = app.books.open(file_name)
    # 1. 保证openpyxl做的修改保存到了文件
    workbook.save()
    # # 2. 触发所有公式重新计算
    # workbook.app.calculate()
    # # 3. 保存公式计算后的结果
    # workbook.save()
    # 4. 关闭
    workbook.close()
    app.quit()

 

 2.表格操作

2.1、写入excel

pandas:

test_list = [temp.split() for temp in test_date]
df1 = pd.DataFrame(test_list)
with pd.ExcelWriter(xlsx_name, mode='a', engine="openpyxl", if_sheet_exists="replace") as writer:
    df1.to_excel(writer, sheet_name='stream', header=False, index=False)

 

openpyxl:

# 加载工作簿和工作表
wb = load_workbook(xlsx_name)
ws = wb['spec']
cell = ws["D60"]
cell.value = 10000
# 保存工作簿
wb.save(xlsx_name)
wb.close()

 

 

 

2.2、读取excel

pandas

    p = pd.read_excel(xlsx_name, sheet_name="spec")
    spec_datas = p.to_dict(orient="records")

openpxl

wb = load_workbook(xlsx_name, data_only=True)
ws = wb['spec']
cell = ws["F60"]
print(cell.value)