openpyxl学习

# *
# *  @author Zhaoliang Ye 叶昭良(zl_ye@qny.chng.com.cn)
# *  @version V0.1
# *  @Title: testOpenpyxl.py
# *  @Description: 测试openpyxl读写
# *  @Time: 2022/2/10 8:56
# *
from pathlib import Path
from openpyxl import Workbook
path=Path()
for file in path.glob("./products/*.xlsx"):
  print(file)

## 官网写入文件小测试  https://openpyxl.readthedocs.io/en/stable/index.html
wb=Workbook()
ws=wb.active
ws['A1']=42
ws.append([1,2,3,4,5,6,7,8,9])
import datetime
ws['A2']=datetime.datetime.now()
wb.save("sampleWrite.xlsx")


## 读取文件小测试
import openpyxl as xl
from openpyxl.chart import BarChart, Reference

wb=xl.load_workbook('testOpenpyxl.xlsx')
sheet=wb['Sheet1']
cell1=sheet['a1']
cell=sheet.cell(1,1)
for row in range(2,sheet.max_row + 1):
  print(f'sheet.cell(row,3)={sheet.cell(row,3)}')
  cell=sheet.cell(row,3)
  correctedIncome=cell.value*0.95
  correctedIncomeCell=sheet.cell(row,4)
  correctedIncomeCell.value=correctedIncome

## 绘图
values=Reference(sheet,
                 min_row=2,
                 max_row=sheet.max_row,
                 min_col=4,
                 max_col=4)

chart=BarChart()
chart.add_data(values)
sheet.add_chart(chart,'e2')
wb.save('translatetestOpenpyXL.xlsx')