给定一个数字和一个Excel文件，计算其中一列数字相加之和等于给定的数字

#!/usr/bin/python
# encoding:utf-8

from Tkinter import *
import win32ui
import xlrd
import xlwt
import tkMessageBox
import itertools #获取数列子集函数
from pyExcelerator import *

file_is_open = []
htbh = []  #合同编号
yhqyf = [] #用户签约方
htmc = [] #合同名称
htzje = [] #合同总金额
kpzje = [] #开票总金额
whkje = [] #未回款金额
whkje_shuju = [] #筛选出来的相关未回款金额数据合集
htbh_shuju = [] #筛选出来的相关合同编号数据合集
htmc_shuju = [] #筛选出来的相关合同名称数据合集
htzje_shuju = [] #筛选出来的相关合同总金额合集
jieguoji = [] #最终筛选出来的相关结果存放位置
cal_num = 0 #计算次数

def open_file():
	dlg = win32ui.CreateFileDialog(1)  # 1表示打开文件对话框
	dlg.SetOFNInitialDir('')  # 设置打开文件对话框中的初始显示目录
	dlg.DoModal()
	filename = dlg.GetPathName()  # 获取选择的文件名称
	print filename.decode('gbk')
	# print excel_data.get_sheets

	try:
		excel_data = xlrd.open_workbook(filename)  # 打开选择的execl文件
	except:
		print '没有选择文件！'
		return
	if filename.decode('gbk') in file_is_open:  # 如果文件被读取了，弹框报错
		print filename.decode('gbk')
		for tt in range(0, len(file_is_open)):
			print file_is_open[tt]
		tkMessageBox.showinfo('info', '文件已经读取过，请选择新文件')
		return
	file_is_open.append(filename.decode('gbk'))  # 如果文件没有读取过，存入到文件列表中
	if excel_data.nsheets > 1:  # 如果sheet页大于一个，则生成选择窗口选择相关的页面进行处理
		xuanze = Tk()  # 建立选择sheet的窗口
		xuanze.title("选择sheet页")
		# xuanze.resizable(width=True, height=True)
		Label(xuanze, text=u'请选择需要统计的sheet页').pack()
		# print excel_data.nsheets   #打印excel中sheet页的个数
		for i in excel_data.sheet_names():  # 建立相关的sheet页按钮
			b = Button(xuanze, text=i, command=lambda t=xuanze, c=excel_data, m=i: duqushuju(t, c, m), width=30,
			           height=2).pack()
		Button(xuanze, text='取消', command=xuanze.destroy, width=30, height=2).pack()
		xuanze.mainloop()
	else:
		duqushuju('', excel_data, excel_data.sheet_names()[0])  # 只有一个sheet页的excel直接读取

def int2bin(n, count=20):
	"""returns the binary of integer n, using count number of digits"""
	return "".join([str((n >> y) & 1) for y in range(count - 1, -1, -1)])

def duqushuju(xuanze, excel_data, i):
	if xuanze:  # 如果传过来的窗口界面非空，则将选择窗口关闭
		xuanze.destroy()

	table_shuju = excel_data.sheet_by_name(i)  # 按照按钮选择的sheet页面读取内容
	excel_nrows = table_shuju.nrows  # 获得sheet页总行数
	excel_ncols = table_shuju.ncols  # 获得sheet页总列数
	col_htbh = 0  #合同编号
	col_yhqyf = 0 #用户签约方
	col_htmc = 0 #合同名称
	col_htzje = 0 #合同总金额
	col_kpzje = 0 #开票总金额
	col_whkje = 0 #未回款金额
	for row_num in range(0, excel_nrows):  # 循环读取数据
		for col_num in range(0, excel_ncols):
			if table_shuju.cell_value(row_num, col_num) == u'合同编号':  # 找到表头，读取相关列信息
				for lieming in range(0, excel_ncols):
					if table_shuju.cell_value(row_num, lieming) == u'合同编号':
						col_htbh = lieming
					if table_shuju.cell_value(row_num, lieming) == u'用户签约方':
						col_yhqyf = lieming
					if table_shuju.cell_value(row_num, lieming) == u'合同名称':
						col_htmc = lieming
					if table_shuju.cell_value(row_num, lieming) == u'合同总金额':
						col_htzje = lieming
					if table_shuju.cell_value(row_num, lieming) == u'开票总金额':
						col_kpzje = lieming
					if table_shuju.cell_value(row_num, lieming) == u'未回款金额':
						col_whkje = lieming
			continue
		if(col_whkje == 0):
			continue
		htbh.append(table_shuju.cell_value(row_num, col_htbh))  # 合同编号
		yhqyf.append(table_shuju.cell_value(row_num, col_yhqyf))  # 用户签约方
		htmc.append(table_shuju.cell_value(row_num, col_htmc))  # 合同名称
		htzje.append(table_shuju.cell_value(row_num, col_htzje))  # 合同总金额
		kpzje.append(table_shuju.cell_value(row_num, col_kpzje)) # 开票总金额
		whkje.append(table_shuju.cell_value(row_num, col_whkje))  # 未回款金额
	tkMessageBox.showinfo('info', '文件数据读取结束')

def shaixuan():
	if len(file_is_open) == 0:
		tkMessageBox.showinfo('info', '先读取文件！！')
		return
	if len(whkje) == 0:
		tkMessageBox.showinfo('info', '文件中找不到相关数据，请检查是否存在“合同编号”、“合同名称”、“开票总金额”、“未回款金额”等列！！')
		return
	for i in range(1,len(whkje)):
		if (yhqyf[i] == gongsimingcheng.get().strip()) and (kpzje[i] > 0) and whkje[i] > 0 :
			whkje_shuju.append(whkje[i])
			htbh_shuju.append(htbh[i])
			htmc_shuju.append(htmc[i])

def tongjishuju():
	if (gongsimingcheng.get() == '')  or (float(jieguo.get()) <= 0):
		tkMessageBox.showinfo('info','请填写相关参数进行计算')
		return
	shaixuan()
	if len(whkje_shuju) == 0:
		tkMessageBox.showinfo('info', '没有需要计算的数据，请检查开票总金额和未回款金额！！')
		return
	# if len(whkje_shuju) > 20:
	# 	tkMessageBox.showinfo('info', '符合条件的数据大于20个了，小心电脑内存被占满，减少点吧')
	# 	exit()

	w = Workbook()  # 创建一个工作簿
	sheet1 = w.add_sheet(u'结果统计')  # 创建员工工作统计表
	style = XFStyle()  #设置单元格样式
	borders = xlwt.Borders()
	borders.left = 1
	borders.right = 1
	borders.top = 1
	borders.bottom = 1
	style.borders=borders
	for i in range(10):  # 设置列宽
		sheet1.col(i).width = 256 * 15
	style.num_format_str='0.00'
	write_line = 1
	# chaxunguocheng.set('共计%0f个循环，请稍等' % 2**len(whkje_shuju))

	num_shuju = len(whkje_shuju)
	tkMessageBox.showinfo('info', num_shuju)
	zongshu = 2 ** num_shuju
	while i < zongshu :
		qiuhe=0.0
		whkje_tmp = []
		htbh_tmp = []
		htmc_tmp = []
		pailie = int2bin(i,num_shuju)
		for j in range(num_shuju):
			qiuhe=qiuhe +  whkje_shuju[j] * int(pailie[j])
			if pailie[j] == '0':
				whkje_tmp.append(whkje_shuju[j])
				htbh_tmp.append(htbh_shuju[j])
				htmc_tmp.append(htmc_shuju[j])
		if qiuhe == float(jieguo.get()):
			for t in range(len(whkje_tmp)):
				sheet1.write(write_line, t + 1, htbh_tmp[t], style)
				sheet1.write(write_line+1, t+1, htmc_tmp[t],style)
				sheet1.write(write_line+2, t+1, whkje_tmp[t],style)
				write_line = write_line + 4



	# for kk in range(len(whkje_shuju)):
	# 	whkje_zuhe = []
	# 	htbh_zuhe = []
	# 	htmc_zuhe = []
	# 	whkje_zuhe =list(itertools.combinations(whkje_shuju,kk))
	# 	htbh_zuhe = list(itertools.combinations(htbh_shuju, kk))
	# 	htmc_zuhe = list(itertools.combinations(htmc_shuju, kk))
	# 	for jj in range(len(whkje_zuhe)):
	# 		xiangjia = 0
	# 		for tt in range(len(whkje_zuhe[jj])):
	# 			xiangjia = xiangjia + whkje_zuhe[jj][tt]
	# 		# print xiangjia,jieguo.get()
	# 		if xiangjia == float(jieguo.get()):
	# 			# print htbh_zuhe[jj],htmc_zuhe[jj],whkje_zuhe[jj]
	# 			sheet1.write(write_line, 0, u'合同编号：', style)
	# 			sheet1.write(write_line + 1, 0, u'合同名称：')
	# 			sheet1.write(write_line + 2, 0, u'未回款金额：', style)
	# 			for qq in range(len(whkje_zuhe[jj])):
	# 				sheet1.write(write_line, qq+1, htbh_zuhe[jj][qq],style)
	# 				sheet1.write(write_line+1, qq+1, htmc_zuhe[jj][qq],style)
	# 				sheet1.write(write_line+2, qq+1, whkje_zuhe[jj][qq],style)
	# 			write_line = write_line + 4

	if write_line == 1:
		tkMessageBox.showinfo('info', '没有数字相加满足条件，请检查')
		jiemian.destroy()
		exit()

	dlg = win32ui.CreateFileDialog(0, None, None, 0, "*.xls")
	dlg.SetOFNInitialDir('')
	dlg.DoModal()
	file_name = dlg.GetPathName()
	if 'xls' not in file_name:
		file_name = file_name + '.xls'
	w.save(file_name)
	tkMessageBox.showinfo('info', '写入完成')
	jiemian.destroy()
	exit()

def shaixuan_baifenbi():
	if len(file_is_open) == 0:
		tkMessageBox.showinfo('info', '先读取文件！！')
		return
	if len(htzje) == 0:
		tkMessageBox.showinfo('info', '文件中找不到相关数据，请检查是否存在“合同总金额”、“合同名称”等列！！')
		return
	for i in range(1,len(htzje)):
		if (yhqyf[i] == gongsimingcheng.get().strip()) and (htzje[i] > 0) :
			htzje_shuju.append(htzje[i])
			htbh_shuju.append(htbh[i])
			htmc_shuju.append(htmc[i])


def baifenbi():
	if len(file_is_open) == 0:
		tkMessageBox.showinfo('info', '先读取文件！！')
		return
	if float(baifenbi_num.get()) == 0:
		tkMessageBox.showinfo('info','请填写金额')
		return
	shaixuan_baifenbi()
	if len(htzje_shuju) == 0:
		tkMessageBox.showinfo('info','筛选后找不到相关公司数据，请检查公司名称')
		return
	w = Workbook()  # 创建一个工作簿
	sheet1 = w.add_sheet(u'百分比统计')  # 创建员工工作统计表
	style = XFStyle()  #设置单元格样式
	borders = xlwt.Borders()
	borders.left = 1
	borders.right = 1
	borders.top = 1
	borders.bottom = 1
	style.borders=borders
	for i in range(10):  # 设置列宽
		sheet1.col(i).width = 256 * 15
	style.num_format_str='0.00'
	sheet1.write(0, 0, u'合同编号：', style)
	sheet1.write(0, 1, u'合同名称：', style)
	sheet1.write(0, 2, u'合同总金额：', style)
	sheet1.write(0, 3, u'计算百分比：', style)
	write_line = 1
	# print len(htzje_shuju)
	for jj in range(len(htzje_shuju)):
		sheet1.write(write_line, 0, htbh_shuju[jj],style)
		sheet1.write(write_line, 1, htmc_shuju[jj],style)
		sheet1.write(write_line, 2, htzje_shuju[jj],style)
		sheet1.write(write_line, 3, '%.02f%%' % (float(baifenbi_num.get())*100/float(htzje_shuju[jj])), style)
		write_line = write_line + 1
	dlg = win32ui.CreateFileDialog(0, None, None, 0, "*.xls")
	dlg.SetOFNInitialDir('')
	dlg.DoModal()
	file_name = dlg.GetPathName()
	if 'xls' not in file_name:
		file_name = file_name + '.xls'
	w.save(file_name)
	tkMessageBox.showinfo('info', '写入完成')
	tkMessageBox.OKCANCEL
	jiemian.destroy()
	exit()

jiemian = Tk()
jiemian.geometry('300x460')
jiemian.title("求和")
jiemian.resizable(width=True, height=True)

Label(jiemian).pack()
Button(jiemian, text='选择文件', command=open_file, width=20, height=1).pack()

Label(jiemian, text='\n请填写公司名').pack()
chushishuju0 = StringVar()
chushishuju0.set('')
gongsimingcheng = Entry(jiemian,textvariable=chushishuju0)
gongsimingcheng.pack()
gongsimingcheng['state'] = 'normal'

Label(jiemian, text='\n请填最终结果').pack()
chushishuju1 = StringVar()
chushishuju1.set(0)
jieguo = Entry(jiemian,textvariable=chushishuju1)
jieguo.pack()
jieguo['state'] = 'normal'

chaxunguocheng = StringVar()
# chaxunguocheng.set('查询过程显示')
Label(jiemian,textvariable=chaxunguocheng).pack()
Button(jiemian, text='计算组合选项', command=tongjishuju, width=20, height=1).pack()

chushishuju2 = StringVar()
chushishuju2.set(1)
Label(jiemian, text='\n请填写回款金额').pack()
baifenbi_num = Entry(jiemian,textvariable=chushishuju2)
baifenbi_num.pack()
baifenbi_num['state'] = 'normal'

Label(jiemian,text='').pack()
Button(jiemian, text='计算百分比', command=baifenbi, width=20, height=1).pack()

Label(jiemian,text='').pack()
Button(jiemian, text='退出', command=jiemian.destroy, width=30, height=1).pack()

jiemian.mainloop()