利用openpyxl操作表格

下载openpyxl

1	pip install openpyxl

输出单元格信息

import openpyxl as xl
wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
cell = sheet['a1']
#cell = sheet.cell(1,1) 这句代码和上一句是等价的，都指代A1单元格
print(cell.value)

这段代码，输出的就是 transaction_id这个信息

打印一列的表格内容

import openpyxl as xl
from openpyxl.chart import BarChart ,Reference
wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
cell = sheet['a1']
for row in range(2,sheet.max_row+1):
    cell = sheet.cell(row,3)
    correct_price = cell.value*0.9			# 计算新数据
    correct_price_cell = sheet.cell(row,4)	# 每行的第四列上，指定一段区域
    correct_price_cell.value = correct_price# 然后再第四列中放入计算的新数据
    print(correct_price)

对单元格计算并保存

1	wb.save('transactions3.xlsx') # 运用save方法，在里面写新的文件名称

制作图表

import openpyxl as xl
# 制作图表，要引进这两个模块
from openpyxl.chart import BarChart ,Reference
wb = xl.load_workbook('transactions.xlsx')
sheet = wb['Sheet1']
for row in range(2,sheet.max_row+1):
    cell = sheet.cell(row,3)
    correct_price = cell.value*0.9
    correct_price_cell = sheet.cell(row,4)
    correct_price_cell.value = correct_price
    print(correct_price)
# 筛选我们的数据，数据范围是第二行到最后一行的第四列，也就是我们新建立的内容
values = Reference(sheet,
              min_row=2,
              max_row= sheet.max_row,
              min_col=4,
              max_col=4)
# 新建一个柱状图对象
chart = BarChart()
# 把值添加到chart当中去
chart.add_data(values)
# 把表添加到e2单元格中去
sheet.add_chart(chart,'e2')
# 保存
wb.save('transactions3.xlsx')

美化代码

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)
        correct_price = cell.value*0.9
        correct_price_cell = sheet.cell(row,4)
        correct_price_cell.value = correct_price
        print(correct_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)