运行环境:win7
python版本:python3.3
# -*- coding: utf-8 -*-
import xdrlib ,sys
import xlrd
import xlwt3
def open_excel(file):
try:
with xlrd.open_workbook(file) as data:
return data
except IOError as err:
print('File error: ' + str(err))
#help function
def function_help(num,tables):
temp_list = []
for row in tables:
if row['凭证号码'] == num:
temp_list.append(row)
for r in temp_list:
if r['借贷方'] == '贷':
s1 = ''
for r2 in temp_list:
if r2['借贷方'] == '借':
s1 = s1 + r2['科目'] + ','
r['对方科目'] = s1[:-1]
elif r['借贷方'] == '借':
s1 = ''
for r2 in temp_list:
if r2['借贷方'] == '贷':
s1 = s1 + r2['科目'] + ','
r['对方科目'] = s1[:-1]
return temp_list
#write the data to the excel by index
def dealWithExcel(tables):
result_tables = []
num = ''
for row in tables:
if row['凭证号码'] == num:
continue
else:
num = row['凭证号码']
temp_list = function_help(num,tables)
result_tables.extend(temp_list)
return result_tables
def main():
#open the excel to be dealed with
if len(sys.argv)!= 2:
print("use this tool with format: dealWithExcel.exe filename")
return
data = open_excel(sys.argv[1])
table = data.sheets()[0]
nrows = table.nrows #行数
ncols = table.ncols #列数
colnames = table.row_values(0) #某一行数据
tables =[]
for rownum in range(1,nrows):
row = table.row_values(rownum)
if row:
app = {}
for i in range(len(colnames)):
app[colnames[i]] = row[i]
tables.append(app)
#deal with the data
result_tables = dealWithExcel(tables)
#write the result back to a new excel
file = xlwt3.Workbook()
table = file.add_sheet('sheet1')
table.write(0,0,'凭证号码')
table.write(0,1,'科目')
table.write(0,2,'对方科目')
table.write(0,3,'借贷方')
r = 1
for row in result_tables:
table.write(r,0,row['凭证号码'])
table.write(r,1,row['科目'])
table.write(r,2,row['对方科目'])
table.write(r,3,row['借贷方'])
r = r + 1
file.save('D:\\对方科目.xlsx')
if __name__=="__main__":
main()
cx_Freeze 4.3
一个例子