excel 相关操作

自动办公之表格计算及生成条形图

导入openpyxl模块（pypi官网复制pip指令下载）

import openpyxl as xl
from openpyxl.chart import BarChart,Reference

wb = xl.load_workbook("test.xlsx")
sheet = wb[Sheet1]
#访问单元格
#cell = sheet[a1]
# cell = sheet.cell(1,1)

for row in range(2,sheet.max_row+1):
    cell = sheet.cell(row,3)
    corrected_price = cell.value * 0.9
    corrected_price_cell = sheet.cell(row,4)
    corrected_price_cell.value = corrected_price

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(test2.xlsx)

实现效果如图所示：

如果想要实现多个表格的处理，就需要定义为一个函数

import openpyxl as xl
from openpyxl.chart import BarChart,Reference
from pathlib import  Path

def process_workbook(filename):
	wb = xl.load_workbook(filename)
	sheet = wb[Sheet1]
	
	for row in range(2,sheet.max_row+1):
    cell = sheet.cell(row,3)
    corrected_price = cell.value * 0.9
    corrected_price_cell = sheet.cell(row,4)
    corrected_price_cell.value = corrected_price

	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(filename)

path = Path()//设置路径
//处理所有文件后缀为xlsx的表格
for file in path.glob(*.xlsx):
    process_workbook(file)