本文主要描述python处理execl文件,并对数据进行处理
python 处理execl文件需要使用到execl处理的相关库文件,这里我们选择openpyxl库;
安装 pip install openpyxl
下面我们使用openpyxl 对xlsx文件进行打开,读等操作;
lwb = openpyxl.load_workbook(file_name) 打开一个xlsx文件
sheet = lwb['Sheet1'] 加载相关的工作表
for row in sheet.iter_rows(): 遍历xlsx行
for cell in row: 遍历行中的
print(cell.row,cell.column,cell.value)
cell.row
上面的代码实现了读取xlsx文件功能;这处理数据的第一步,读取数据,第二步我们要计算数据;
本文要计算的是一个违约金的计算,xlsx文件格式如下所示
第一列是序号,第二列是时间段(货物卖出时间),第三列是该笔货物的金额,第四列是该笔货物的应回款时间,第五列是回款的时间,第六列是回款的金额
计算逻辑是实际回款要一笔笔的还完实销金额,直到还完实销金额,再还下一笔实销金额,若超出实销金额的应回款日期,那么该笔钱是要算违约金的
整个代码如下所示
import openpyxl
import time,datetime
file_name = "test.xlsx"
lwb = openpyxl.load_workbook(file_name)
sheet = lwb['Sheet1']
data = []
for row in sheet.iter_rows():
row_data = dict()
i = 0
for cell in row:
if cell.row == 1:
continue
#print(cell.row, cell.column, cell.value)
#if cell.column == 4:
# row_data[cell.column] = row_data[2]+datetime.timedelta(days=45)
row_data[cell.column] = cell.value
#print(type(i))
i = i+1
if cell.row == 1:
continue;
print(row_data)
if row_data[2] != None:
print(row_data[2] )
#row_data[2] = datetime.datetime.strptime(row_data[2], "%Y-%m-%d")
#print(row_data[4] )
if row_data[4] != None:
row_data[4] = row_data[2] +datetime.timedelta(45) #违约时间,默认45天
data.append(row_data)
#print(row_data)
#print(data)
#res3 = datetime.datetime.strptime('2018-10-11', "%Y-%m-%d")
#res1 = datetime.datetime.strptime('2018-9-11', "%Y-%m-%d")
#print('res3',res3+datetime.timedelta(days=45))
cur_m = data[0][3]
cur_d = data[0][2]
sj=[] #实销金额
for row in data :
da={}
if row[2] != None:
da['d'] = row[2] #实销时间
da['m'] = row[3] #实销金额
da['wd'] = row[4] #违约日期
sj.append(da)
i = 0
for dict in sj:
print(i,dict)
i= i+1
index = 0
hk = [] #回款金额
for row in data :
da={}
if row[5] != None:
da['d'] = row[5] #回款时间
da['m'] = row[6] #回款金额
hk.append(da)
i = 0
for dict in hk:
print(i,dict)
i= i+1
index = 0
sum_m = 0.000
j = 0
for dic in sj:
cur_date = datetime.timedelta(0)
last_date = datetime.timedelta(45)
j= j+1
#print(dic['wd'])
#print('index',index,dic['m'] )
only_weiyuejin = 0.0
for i in range(index,len(hk)):
#print(hk[index] )
cur_m = 0.000
one_m = dic['m']
dic['m'] =round( dic['m'] - hk[index]['m'],2)
#print( hk[index]['d'] - dic['d'] ,dic['m'] ,one_m)
#print('xxx',hk[index]['d'] ,dic['wd'])
cur_date = hk[index]['d'] - dic['wd'] #计算是否违约,超出了违约日期即认为违约
#print(cur_date.days)
if cur_date.days > 0:
if dic['m'] <= 0.00 and one_m > 0.00:
cur_m = one_m * cur_date.days *0.12/360 #当该回款能够支付实销金额时违约金计算
else:
cur_m = hk[index]['m']* cur_date.days *0.12/360 #当该回款不够支付实销金额时违约金计算,计算该笔回款的时间
#offset_day = cur_date - last_date
only_weiyuejin = only_weiyuejin + cur_m #每笔违约金累加,算出总违约金
sum_m = sum_m + cur_m
#print('cur_m ',cur_m,sum_m)
#print(dic['m'],hk[index]['m'])
if dic['m'] <= 0.00:
hk[index]['m'] = dic['m'] * -1 #该笔钱足够支付当前违约金,并且还有剩余,那么结余就可支付下一笔实销金额
#print(dic['m'] ,index,"ahhdhhd")
break;
index = index + 1 #不足支付实销,跳到下一笔回款继续还钱
if dic['m'] > 0.00: #所有回款不足支付实销,那么算一个截止日期的总违约金
#print(dic['m'], hk[index-1]['d'] )
cur_date = hk[index-1]['d'] - dic['wd']
cur_m = dic['m'] * cur_date.days *0.12/360
sum_m = sum_m + cur_m
only_weiyuejin = only_weiyuejin + cur_m
print(j,only_weiyuejin)
print(sum_m) #打印总违约金