注意:
注意xlsx的格式,wps一定另存为xlsx才可以,不然就出错。
操作表格
1,在终端里面安装openpyxl
pip install openpyxl
import openpyxl as xl from openpyxl.chart import BarChart,Reference #注意xlsx的格式,wps另存为xlsx才可以,奇怪 wb = xl.load_workbook('transaction1.xlsx') sheet = wb['Sheet1'] cell = sheet['A1'] cell = sheet.cell(1,1) print(cell.value) print(sheet.max_row) 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
输出柱状图
#输出柱状图 chart_values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4) chart = BarChart() chart.add_data(chart_values) sheet.add_chart(chart,'E2')
自动化处理:
#改为函数,可以做自动化处理,只要传入文件名即可。 import openpyxl as xl from openpyxl.chart import BarChart,Reference 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 chart_values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4) chart = BarChart() chart.add_data(chart_values) sheet.add_chart(chart,'E2') wb.save(filename)