openpyxl 添加数据的时候不需要复制表格,这点尤为方便。
下面是在表格末尾添加数据,由于添加的数据已没有原本的单元格样式,所以需要设置他的单元格样式与上文保持一致。
pandas 如何获取所需数据的索引(判断条件可以改变,结果为列表)
import openpyxl
from openpyxl.styles import PatternFill, Alignment
import pandas as pd
# 更改写入数据的样式,与已有数据样式保持一致
# line为行数
def style(line):
# 数据格式
# tm = ws['H2'].number_format
# print(tm)
# 背景颜色
fl = PatternFill("solid", fgColor='fde9d9')
# 水平居右
al = Alignment(horizontal='right',)
ws['A{}'.format(line)].alignment = al
ws['A{}'.format(line)].number_format = ws['A2'].number_format
ws['B{}'.format(line)].number_format = ws['B2'].number_format
ws['C{}'.format(line)].fill = fl
ws['C{}'.format(line)].number_format = ws['C2'].number_format
ws['D{}'.format(line)].number_format = ws['D2'].number_format
ws['E{}'.format(line)].fill = fl
ws['E{}'.format(line)].number_format = ws['E2'].number_format
ws['F{}'.format(line)].number_format = ws['F2'].number_format
ws['G{}'.format(line)].number_format = ws['G2'].number_format
ws['H{}'.format(line)].fill = fl
ws['H{}'.format(line)].number_format = ws['H2'].number_format
ws['I{}'.format(line)].number_format = ws['I2'].number_format
ws['J{}'.format(line)].number_format = ws['J2'].number_format
ws['K{}'.format(line)].number_format = ws['K2'].number_format
ws['L{}'.format(line)].number_format = ws['L2'].number_format
ws['M{}'.format(line)].number_format = ws['M2'].number_format
ws['N{}'.format(line)].number_format = ws['N2'].number_format
ws['O{}'.format(line)].number_format = ws['O2'].number_format
ws['P{}'.format(line)].number_format = ws['P2'].number_format
ws['Q{}'.format(line)].number_format = ws['Q2'].number_format
ws['R{}'.format(line)].number_format = ws['R2'].number_format
ws['S{}'.format(line)].number_format = ws['S2'].number_format
# 使用pandas解析表格数据 取出所需数据(date = dpos[n-1]['日期'])进行计算
df = pd.read_excel(file_path_1, 'Sheet1')
dpos.update(df.to_dict(orient="index"))
n = len(dpos)
# 可以求得所需数据的索引
# df[df['日期'] == '2018-10-10'].index[0]
# 使用openpyxl解析表格 在最后插入当天的数据
wb = openpyxl.load_workbook(file_path_1)
ws = wb['Sheet1']
# 在末尾添加一行数据
ws.append([A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S])
line = n+2
style(line)
wb.save(file_path_1)
# 修改数据,直接修改即可
# ws['A6'] = 'china'