openpyxl一键报告

  1. openpyxl一键汇总
  2. openpyxl一键读取
  3. openpyxl一键报告

源文件下载

文件格式:

January 2020 Sales
Global Sales Report
Location Oranges Apples Bananas Blueberries Total
Toronto $7,651.00 $4,422.00 $8,580.00 $3,679.00 $24,332.00
Los Angeles $273.00 $2,998.00 $9,890.00 $7,293.00 $20,454.00
Atlanta $3,758.00 $6,752.00 $4,599.00 $4,149.00 $19,258.00
New York $4,019.00 $8,796.00 $8,486.00 $9,188.00 $30,489.00

openpyxl一键汇总

  1. 循环读取文件

  2. 汇总到一个表,减少打开次数

        # *
        # *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
        # *  @version V0.1
        # *  @Title: assembleProducts.py
        # *  @Description: 吧products的excel组合起来
        # *  @Time: 2022/2/10 10:17
        # *
        import pandas as pd
        import os
    
        dir_path=os.path.dirname(os.path.realpath(__file__))
        print(f'当前路径: {dir_path}')
        pro_path=os.path.join(dir_path,"products\\")
        print(f'当前路径: {pro_path}')
    
        combined=pd.DataFrame()
    
        for file in os.listdir(pro_path):
            print(f'当前文件:{file}')
            if file.lower().endswith(".xlsx"):
                df=pd.read_excel(os.path.join(pro_path,file),engine="openpyxl",skiprows=3)
                combined=combined.append(df,ignore_index=True)
        combined.to_excel(os.path.join(dir_path,"output\\combined.xlsx"))
        

openpyxl一键读取

  1. 循环读取文件
  2. 读取特别字段,并在列表中显示
# *
# *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
# *  @version V0.1
# *  @Title: readProducts.py
# *  @Description: 只读取每个文件F5数据
# *  @Time: 2022/2/10 10:37
# *
import os
import openpyxl

dir_path=os.path.dirname(os.path.realpath(__file__))
print(f'根路径: {dir_path}')
pro_path=os.path.join(dir_path,"products\\")
print(f'当前路径: {pro_path}')


values=[]

for file in os.listdir(pro_path):
    print(f'当前文件:{file}')
    if file.lower().endswith(".xlsx"):
        wb=openpyxl.load_workbook(os.path.join(pro_path,file))
        sheet=wb["Sheet1"]
        value=sheet["F5"].value
        values.append(value)

print(f'All the excel F5 value is {values}')

openpyxl一键汇总

  1. 制作word模板,存为templates.docx,如下
{{title}}
{{author}}
{{day}}/{{month}}/{{year}}

Index	Velocity	Cp	Ct
{%tr for item in table_contents %}
{{item.Index}}	{{item.Velocity}}	{{item.Cp}}	{{item.Ct}}
{%tr endfor %}


{{image}}
Figure1: graph of power
  1. 制作excel表源数据
Velocity Shaft power [kW] Rotor speed [rpm] Aerodynamic torque [Nm] Thrust force [N] Tip speed ratio [.] Cp Ct Torque coefficient [.]
3 45.4557 8.25344 52592.7 37830.3 13.3825 0.405489 1.0124 0.0303
3.1 51.9009 8.25344 60049.8 39527 12.9508 0.419608 0.990659 0.0324
3.2 58.726 8.25344 67946.5 41248.5 12.5461 0.431653 0.970203 0.034405
3.3 65.9366 8.25344 76289.2 42994 12.1659 0.441915 0.950898 0.036324
3.4 73.5388 8.25344 85085.1 44767.1 11.8081 0.450645 0.932729 0.038164
3.5 81.5217 8.25344 94321.3 46560.4 11.4707 0.457955 0.915451 0.039924
3.6 89.9503 8.25344 104073 48384.4 11.1521 0.464354 0.899196 0.041638
3.7 98.7935 8.25344 114305 50225.3 10.8507 0.469761 0.883636 0.043293
  1. 处理excel,绘图,报告
# *
# *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
# *  @version V0.1
# *  @Title: autoMateReport.py
# *  @Description: 自动生成报告
# *  @Time: 2022/2/10 10:58
# *

import os
import openpyxl
#:https://stackoverflow.com/questions/15025978/win32com-client-error/15051499
import win32com.client
import PIL
from PIL import ImageGrab,Image
from openpyxl.chart import LineChart, Reference
import time

dir_path=os.path.dirname(os.path.realpath(__file__))
print(f'根路径: {dir_path}')
file_path=os.path.join(dir_path,"input\\HZ93PowerCurves.xlsx")
file_out_path=os.path.join(dir_path,"output\\HZ93PowerCurvesout.xlsx")
print(f'当前路径: {file_path}')

wb=openpyxl.load_workbook(file_path)
## wb.create_sheet("newSheet")
#
print(f'wb.sheetnames={wb.sheetnames}')
sheet=wb["Sheet1"]
for row in range(2,sheet.max_row+1):
    velocity=sheet.cell(row,1)
    speed=sheet.cell(row,3)
    powerCp=sheet.cell(row,7)
    ## powerCp.value=float(velocity.value)*float(speed.value)
# https://www.osgeo.cn/openpyxl/charts/chart_layout.html
#https://blog.csdn.net/hunter_wyh/article/details/78498323
#values=Reference(sheet,min_row=2,max_row=sheet.max_row,min_col=7,max_col=7)
x=Reference(sheet,min_row=2,max_row=sheet.max_row,min_col=1)
values=Reference(sheet,min_row=1,max_row=sheet.max_row,min_col=7,max_col=8)
print(f'values={values}')
chart=LineChart()
chart.y_axis.title="Cp-Ct"
chart.x_axis.title="Velocity/(m/s)"
chart.add_data(values)
chart.set_categories(x)
chart.varyColors=True
chart.legend.position="r"
sheet.add_chart(chart,"e2")

wb.save(file_out_path)

###  拷贝excel表中图片!
## OSError: failed to open clipboard
#  pip install pillow --upgrade
xlApp = win32com.client.Dispatch('Excel.Application')
# for fname in filelist:
#     xlbook = XlsClass(xlApp, fname)
#     # do something with xlbook
#     xlbook.close()
output_image="output/chart.png"
# operation = win32com.client.Dispatch("Excel.Application")
# operation.DisplayAlert=0

# 老写法 可运行
workbook_2 =  xlApp.Workbooks.Open(file_out_path)
# 新写法
# workbook_2 =  XlsClass(xlApp,output_image)
sheet_2=workbook_2.Sheets(1)
for x,chart in enumerate(sheet_2.Shapes):
    chart.Copy()
    image=ImageGrab.grabclipboard()
    image.save(output_image,'png')
    # time.sleep(1)
    pass

print(f'type(workbook_2)={type(workbook_2)}')
workbook_2.Close()
#workbook_2.Close(True)
#xlApp.Quit()


## 导入word
##
from docx.shared import Cm,Inches,Mm,Emu
from docx import Document
from docxtpl import  DocxTemplate,InlineImage
import datetime


template =DocxTemplate("input/template.docx")
table_contents=[]
for i in range(2,sheet.max_row+1):
    table_contents.append(
        {
            "Index":i-1,
            "Velocity": sheet.cell(i,1).value,
            "Cp":sheet.cell(i,7).value,
            "Ct":sheet.cell(i,8).value,
        }
    )

## import saved figure
image=InlineImage(template,"output/chart.png",Cm(16))

## Declare template variable
context={
    "title":"Automate report of HZ93",
    "author":"yzl",
    "day": datetime.datetime.now().strftime("%d"),
    "month": datetime.datetime.now().strftime("%b"),
    "year": datetime.datetime.now().strftime("%Y"),
    "table_contents": table_contents,
    "image": image,
}


## Render automated report
template.render(context)
template.save("output/Automated_Report.docx")
Related
叶昭良
叶昭良
Engineer of offshore wind turbine technique research

My research interests include distributed energy, wind turbine power generation technique , Computational fluid dynamic and programmable matter.