openpyxl操控Excel文件

多谢网友chenyinglu给出这么好的点子,这位网友综合用到pandas,openpyxl,datetime,itchat、smpt和email模块来解决实际问题&提高工作效率,太专业我只挑了看得懂用得到的部分。当时正在头疼excel部分的思考题,看这个例子实在太好了,就直接在正文里加了一节解释excel里的计算公式。

每次听到网友把python用到实处解决实际问题都很高兴!

excel单元格可以做些简单的计算,只需在单元格里先输入=,然后跟计算公式。比如’=sum(A1:B3)’,意为计算A1B3区域单元格的和: 

ws = wb.create_sheet('Formula')

ws['A1'] = 200
ws['A2'] = 300
ws['A3'] = '=SUM(A1:A2)' # 计算单元格A1到A2的和

rawData.xlsx里记录一些股票交易信息,见表格rawData.xlsx的截图: 

用程序把D列观察日、F列预警价和H列当前状态填上。要求:

      D列观察日这一列放当前的日期;

      F列预警价这一列的值按平仓价*1.2算;

      H列当前状态列情况略微复杂一些,需要条件判断,具体见流程图:

如果是填写excel表格,那么在excel表格上我们会进行如下操作:  

单元格D2填写:=Today()

单元格F2填写:=E2*1.2

单元格H2填写:=IF (G2<=F2, IF (G2<=E2, “平仓”,”预警”), “”)

在程序中用表单对象的方法函数cell(column=, row=, value=)填写单元格内容时,value=的内容跟excel表格的填写内容相同,只不过是以字符串形式填入,例如单元格内容的内容属性.value=’=Today()’ 

程序会用循环一行一行往里填数据,行数row作为循环变量跳过表头行取234、……数据行。在excel表格里的单元格F2填写=E2*1.2,因为单元格对象value属性接收的是字符串,所以行数2要先用str()转成字符串儿后才汇入给value赋值的字符串中:

      value=’=E’+str(2)+’*1.2’

推而广之,行号用rowS

     value=’=E’+str(rowS)+’*1.2’

同理excel的单元格H2输入=IF (G2<=F2, IF (G2<=E2, “平仓”,”预警”), “”),在代码里需要把行号rowS由数字转成字符串,再合并回去:

     value='=IF(G'+rowS+' <= F'+rowS+', IF(G'+rowS+' <= E'+rowS+', "平仓", "预警"), "")'

另外填写计算公式时列号用的是字母,而在表单对象取单元格的cell()里参数column=却要求放数字。column_index_from_string()可以把列字母转成列数字,使用这个函数前需要先引进:

     from openpyxl.utils import column_index_from_string

代码运行后自动把数据计算出来填进表格:

# --------------用公式自动计算和填写单元格---------------------
import openpyxl
from openpyxl.utils import column_index_from_string

wb = openpyxl.load_workbook('rawData.xlsx')		# 打开工作表生成工作簿对象
rawSheet = wb.active		                        # 生成表单对象

for row in range(2, rawSheet.max_row+1):	        # 越过标题行,遍历数据行

    rowS = str(row)					# 将行数转成字母

    rawSheet.cell(row=row, column=column_index_from_string('D'), value='=Today()')		
    # 填写观察日列,放今日日期

    rawSheet.cell(row=row, column=column_index_from_string('F'), value='=E'+rowS+'*1.2')	
    # 填写预警价列, 平仓价 * 1.2

    rawSheet.cell(row=row, column=column_index_from_string('H'),
value='=IF(G'+rowS+' <= F'+rowS+', IF(G'+rowS+' <= E'+rowS+', "平仓", "预警"), "")')			
# 填写当前状态列

wb.save('processedData.xlsx')	                        # 保存战果