def __init__(self, fileName): if os.path.exists(fileName) == False: # 判断文件是否存在,存在则跳过,不存在则新建 self.book = Workbook(encoding='utf-8', style_compression=0) self.sheet = self.book.add_sheet('豆瓣标签结果', cell_overwrite_ok=True) ## Column Width Determine Begin for A in range(100): self.sheet.col(A).width = 4800 ## Column Width Determine Begin self.sheet.write(0, 0, '编号', tittle_style) self.sheet.write(0, 1, '用户', tittle_style) self.sheet.write(0, 2, '标签', tittle_style) self.sheet.write(0, 3, '次数', tittle_style) self.sheet.write(0, 4, '标注过', tittle_style) self.rowx = 1 self.index = 1 # sheet.write_merge(0, 5, 0, 1, "哈哈哈", normal_style) else: rb = open_workbook(fileName, formatting_info=True) # 注意这里的workbook首字母是小写 r_sheet = rb.sheet_by_index(0) self.rowx = r_sheet.nrows firstColsValue = r_sheet.col_values(0)[::-1] for index in firstColsValue: if str(index).__eq__("") == False: self.index = int(index) break self.index = self.index + 1 # 管道作用 self.book = copy(rb) # 通过get_sheet()获取的sheet有write()方法 self.sheet = self.book.get_sheet(0) self.sheet.write(0, 7, 'lalal', tittle_style) return
def export_as_xls(modeladmin, request, queryset): """ Generic xls export admin action. """ if not request.user.is_staff: raise PermissionDenied opts = modeladmin.model._meta wb = Workbook() ws0 = wb.add_sheet('0') col = 0 field_names = [] # write header row for field in opts.fields: ws0.write(0, col, field.name) field_names.append(field.name) col = col + 1 row = 1 # Write data rows for obj in queryset: col = 0 for field in field_names: val = unicode(getattr(obj, field)).strip() ws0.write(row, col, val) col = col + 1 row = row + 1 f = StringIO() wb.save(f) f.seek(0) response = HttpResponse(f.read(), content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=%s.xls' % unicode(opts).replace('.', '_') return response
def export_investdata_excel(self, request): item_list = self.filter_queryset(self.get_queryset()) data = [] for con in item_list: project = con.project id = con.id project_id = project.id project_name = project.name if con.is_futou: is_futou = "复投" else: is_futou = "首投" invest_time = con.invest_time invest_mobile = con.invest_mobile invest_amount = con.invest_amount invest_term = con.invest_term settle_amount = con.settle_amount return_amount = '' result = '' if con.state == '0': result = '否' #return_amount = con.return_amount elif con.state == '1': result = '是' return_amount = con.return_amount source = con.get_source_display() remark = con.remark data.append([ id, project_id, project_name, is_futou, invest_time, invest_mobile, invest_amount, invest_term, settle_amount, result, return_amount, source, remark ]) w = Workbook() # 创建一个工作簿 ws = w.add_sheet(u'待审核记录') # 创建一个工作表 title_row = [ u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额', u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注' ] for i in range(len(title_row)): ws.write(0, i, title_row[i]) row = len(data) style1 = easyxf(num_format_str='YY/MM/DD') for i in range(row): lis = data[i] col = len(lis) for j in range(col): if j == 4: ws.write(i + 1, j, lis[j], style1) else: ws.write(i + 1, j, lis[j]) sio = BytesIO() w.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=导出表格.xls' response.write(sio.getvalue()) return response
def export_project_statis(request): item_list = [] item_list = ProjectStatis.objects.all() project_state = request.GET.get("project_state", None) if project_state == 'start' or project_state == 'finish': item_list = item_list.filter(project__state=project_state) data = [] for con in item_list: id = con.project_id time = con.project.time.strftime("%Y-%m-%d") finish_time = con.project.finish_time.strftime( "%Y-%m-%d") if con.project.finish_time else '' title = con.project.name topay_amount = con.project.topay_amount consume = con.consume() ret = con.ret() site_consume = con.site_consume site_ret = con.site_return channel_consume = con.channel_consume channel_ret = con.channel_return state = con.project.get_state_display() data.append([ id, time, finish_time, title, topay_amount, consume, ret, channel_consume, channel_ret, site_consume, site_ret, state ]) w = Workbook() #创建一个工作簿 ws = w.add_sheet(u'账目明细') #创建一个工作表 title_row = [ u'项目编号', u'立项日期', u'结项日期', u'项目名称', u'预计待收/待消耗', u'预计总消耗', u'总返现金额', u'预估渠道消耗', u'渠道返现金额', u'预估网站消耗', u'网站返现金额', u'项目状态' ] for i in range(len(title_row)): ws.write(0, i, title_row[i]) row = len(data) style1 = easyxf(num_format_str='YY/MM/DD') for i in range(row): lis = data[i] col = len(lis) for j in range(col): if j == 1: ws.write(i + 1, j, lis[j], style1) elif j == 2: if lis[j]: ws.write(i + 1, j, lis[j], style1) else: ws.write(i + 1, j, lis[j]) else: ws.write(i + 1, j, lis[j]) sio = StringIO.StringIO() w.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=导出表格.xls' response.write(sio.getvalue()) return response
class HtmlOutputer(object): def __init__(self, fileName): if os.path.exists(fileName) == False: # 判断文件是否存在,存在则跳过,不存在则新建 self.book = Workbook(encoding='utf-8', style_compression=0) self.sheet = self.book.add_sheet('豆瓣标签结果', cell_overwrite_ok=True) ## Column Width Determine Begin for A in range(100): self.sheet.col(A).width = 4800 ## Column Width Determine Begin self.sheet.write(0, 0, '编号', tittle_style) self.sheet.write(0, 1, '用户', tittle_style) self.sheet.write(0, 2, '标签', tittle_style) self.sheet.write(0, 3, '次数', tittle_style) self.sheet.write(0, 4, '标注过', tittle_style) self.rowx = 1 self.index = 1 # sheet.write_merge(0, 5, 0, 1, "哈哈哈", normal_style) else: rb = open_workbook(fileName, formatting_info=True) # 注意这里的workbook首字母是小写 r_sheet = rb.sheet_by_index(0) self.rowx = r_sheet.nrows firstColsValue = r_sheet.col_values(0)[::-1] for index in firstColsValue: if str(index).__eq__("") == False: self.index = int(index) break self.index = self.index + 1 # 管道作用 self.book = copy(rb) # 通过get_sheet()获取的sheet有write()方法 self.sheet = self.book.get_sheet(0) self.sheet.write(0, 7, 'lalal', tittle_style) return def writeData(self, rowx, colx, data): self.sheet.write(rowx, colx, '标注过', tittle_style) def writeDataDefault(self, username, allTags, allBooks): self.sheet.write(self.rowx, 0, self.index, normal_style) self.sheet.write(self.rowx, 1, username, normal_style) for tag in allTags: self.sheet.write(self.rowx, 2, tag, normal_style) self.sheet.write(self.rowx, 3, allBooks[tag].__len__(), normal_style) self.sheet.write(self.rowx, 4, ",".join(allBooks[tag]), normal_style) self.rowx = self.rowx + 1 def save(self, name): self.book.save(name)
class HtmlOutputer(object): def __init__(self,fileName): if os.path.exists(fileName) == False: # 判断文件是否存在,存在则跳过,不存在则新建 self.book = Workbook(encoding='utf-8', style_compression=0) self.sheet = self.book.add_sheet('豆瓣标签结果', cell_overwrite_ok=True) ## Column Width Determine Begin for A in range(100): self.sheet.col(A).width = 4800 ## Column Width Determine Begin self.sheet.write(0, 0, '编号', tittle_style) self.sheet.write(0, 1, '用户', tittle_style) self.sheet.write(0, 2, '标签', tittle_style) self.sheet.write(0, 3, '次数', tittle_style) self.sheet.write(0, 4, '标注过', tittle_style) self.rowx = 1 self.index = 1 # sheet.write_merge(0, 5, 0, 1, "哈哈哈", normal_style) else: rb = open_workbook(fileName, formatting_info=True) # 注意这里的workbook首字母是小写 r_sheet = rb.sheet_by_index(0) self.rowx = r_sheet.nrows firstColsValue = r_sheet.col_values(0)[::-1] for index in firstColsValue: if str(index).__eq__("") == False: self.index = int(index) break self.index = self.index + 1 # 管道作用 self.book = copy(rb) # 通过get_sheet()获取的sheet有write()方法 self.sheet = self.book.get_sheet(0) self.sheet.write(0, 7, 'lalal', tittle_style) return def writeData(self, rowx, colx, data): self.sheet.write(rowx, colx, '标注过', tittle_style) def writeDataDefault(self, username, allTags, allBooks): self.sheet.write(self.rowx, 0, self.index, normal_style) self.sheet.write(self.rowx, 1, username, normal_style) for tag in allTags: self.sheet.write(self.rowx, 2, tag, normal_style) self.sheet.write(self.rowx, 3, allBooks[tag].__len__(), normal_style) self.sheet.write(self.rowx, 4, ",".join(allBooks[tag]), normal_style) self.rowx = self.rowx + 1 def save(self, name): self.book.save(name)
def export_audit_result(request): user = request.user fid = request.GET.get("fid") if fid == '0': #jzy finance = Finance.objects.all() item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time") else: finance = Finance.objects.get(id=fid) item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time") data = [] for con in item_list: # project_name=finance.title project = con.content_object #jzy project_name=project.title #jzy mobile_sub=con.invest_account # time_sub=con.time time_sub=con.invest_time #jzy id=con.id remark= con.remark invest_amount= con.invest_amount term=con.invest_term result = con.get_audit_state_display(), return_amount = '' if con.audit_state!='0' or not con.translist.exists() else str(con.translist.first().transAmount/100.0), reason = '' if con.audit_state!='2' or not con.audited_logs.exists() else con.audited_logs.first().reason, data.append([id, project_name, time_sub, mobile_sub, term, invest_amount, remark, result, return_amount, reason]) w = Workbook() #创建一个工作簿 ws = w.add_sheet(u'待审核记录') #创建一个工作表 title_row = [u'记录ID',u'项目名称',u'投资日期', u'注册手机号' ,u'投资期限' ,u'投资金额', u'备注', u'审核结果',u'返现金额',u'拒绝原因'] for i in range(len(title_row)): ws.write(0,i,title_row[i]) row = len(data) style1 = easyxf(num_format_str='YY/MM/DD') for i in range(row): lis = data[i] col = len(lis) for j in range(col): if j==2: ws.write(i+1,j,lis[j],style1) else: ws.write(i+1,j,lis[j]) sio = StringIO.StringIO() w.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=审核结果.xls' response.write(sio.getvalue()) return response
def __init__(self,fileName): if os.path.exists(fileName) == False: # 判断文件是否存在,存在则跳过,不存在则新建 self.book = Workbook(encoding='utf-8', style_compression=0) self.sheet = self.book.add_sheet('豆瓣标签结果', cell_overwrite_ok=True) ## Column Width Determine Begin for A in range(100): self.sheet.col(A).width = 4800 ## Column Width Determine Begin self.sheet.write(0, 0, '编号', tittle_style) self.sheet.write(0, 1, '用户', tittle_style) self.sheet.write(0, 2, '标签', tittle_style) self.sheet.write(0, 3, '次数', tittle_style) self.sheet.write(0, 4, '标注过', tittle_style) self.rowx = 1 self.index = 1 # sheet.write_merge(0, 5, 0, 1, "哈哈哈", normal_style) else: rb = open_workbook(fileName, formatting_info=True) # 注意这里的workbook首字母是小写 r_sheet = rb.sheet_by_index(0) self.rowx = r_sheet.nrows firstColsValue = r_sheet.col_values(0)[::-1] for index in firstColsValue: if str(index).__eq__("") == False: self.index = int(index) break self.index = self.index + 1 # 管道作用 self.book = copy(rb) # 通过get_sheet()获取的sheet有write()方法 self.sheet = self.book.get_sheet(0) self.sheet.write(0, 7, 'lalal', tittle_style) return
def create_and_write(self, sheet_name, types, row, col, text, savepath): wb = Workbook('utf-8') sheet = wb.add_sheet(sheet_name) if types == 'one': sheet.write(row, col, text) elif types == 'row': for i in range(len(self.content)): sheet.write(row, col + i, self.content[i]) elif types == 'list': for j in range(len(self.content)): for k in range(len(self.content[j])): sheet.write(5 + j, 7 + k, self.content[j][k]) else: raise print('对不起,只能输入one,row,list,否则无法写入任何数据') wb.save(savepath) return self.content
def __init__(self): self.sudoku=[] self.sudokuCheck=[] self.elements=[] self.elementInit() self.wb = Workbook() self.ws0 = self.wb.add_sheet('sudoku1',cell_overwrite_ok=True) self.sw=1 self.cont=0 self.var=[] self.stack=[] self.file= open('log.txt','w')
def start(self): book = Workbook() sheet1 = book.add_sheet('Sheet1') i = 0 while self.p <= 5: zipd = self.getData() #print(type(list(zipd))) for gsmc, zwmc, zwyx, gzdd, gxsj in zipd: sheet1.write(i, 0, gsmc.get_text()) sheet1.write(i, 1, zwmc.get_text()) sheet1.write(i, 2, zwyx.get_text()) sheet1.write(i, 3, gzdd.get_text()) sheet1.write(i, 4, gxsj.get_text()) sheet1.write(i, 5, zwmc.get('href')) #self.sub_url.append(zwmc.get('href')) #zwms = self.getSubdata(zwmc.get('href')) #sheet1.write(i,5,self.getSubdata(zwmc.get('href'))) #太慢 i += 1 #print(type(zwmc)) #return sheet1.col(0).width = 256 * 40 sheet1.col(1).width = 256 * 25 sheet1.col(5).width = 256 * 60 book.save('d:/gup/danwei.xls')
pages = get_url_page() #run_page() house_urls = [] for each_page in pages: house_url = get_house_url_id(each_page) #print '页数:%s, 地址集:%s' %(each_page, house_url) print '页数:', each_page print '房源页面:', house_url house_urls = house_url + house_urls print '房源总页面:', house_urls i = 0 book = Workbook() sheet_sm = book.add_sheet('siming') for each_house in house_urls: time.sleep(3) html = open_url(each_house) time.sleep(3) print '房源排序号:%s' %(i+1) house_estate = get_house_estate(html) print '小区:', house_estate house_type = get_house_type(html) print '类型:', house_type house_area = get_area(html) print '面积:', house_area house_perprice = get_per_price(html) print '单价:', house_perprice house_totalprice = get_total_price(html)
class ExcelWriter(BaseWriter): _sheet_count = 0 def __init__(self, *args, **kw): super(ExcelWriter, self).__init__(*args, **kw) self._wb = Workbook() def add_ideogram_table(self, analyses, widths=None, # configure_table=True, add_title=False, add_header=False, tablenum=1, **kw): sh = self._wb.add_sheet(analyses[0].labnumber) # 'sheet{:02n}'.format(self._sheet_count)) self._sheet_count += 1 cols = ['Status', 'N', 'Power', 'Moles_40Ar', 'Ar40', 'Ar40Er', 'Ar39', 'Ar39Er', 'Ar38', 'Ar38Er', 'Ar37', 'Ar37Er', 'Ar36', 'Ar36Er', 'Blank', 'Ar40', 'Ar40Er', 'Ar39', 'Ar39Er', 'Ar38', 'Ar38Er', 'Ar37', 'Ar37Er', 'Ar36', 'Ar36Er' ] # write header row hrow = 0 borders = Borders() borders.bottom = 2 style = XFStyle() style.borders = borders for i, ci in enumerate(cols): sh.write(hrow, i, ci, style=style) numstyle = XFStyle() numstyle.num_format_str = '0.00' from operator import attrgetter def getnominal_value(attr): def get(x): v = getattr(x, attr) return float(v.nominal_value) return get def getstd_dev(attr): def get(x): v = getattr(x, attr) return float(v.std_dev) return get def getfloat(attr): def get(x): v = getattr(x, attr) return float(v) return get def null_value(): return lambda x: '' attrs = [(attrgetter('step'),), (getfloat('extract_value'),), (null_value(),), (getnominal_value('Ar40'),), (getstd_dev('Ar40'),), (getnominal_value('Ar39'),), (getstd_dev('Ar39'),), (getnominal_value('Ar38'),), (getstd_dev('Ar38'),), (getnominal_value('Ar37'),), (getstd_dev('Ar37'),), (getnominal_value('Ar36'),), (getstd_dev('Ar36'),), (null_value(),), (getnominal_value('Ar40_blank'),), (getstd_dev('Ar40_blank'),), (getnominal_value('Ar39_blank'),), (getstd_dev('Ar39_blank'),), (getnominal_value('Ar38_blank'),), (getstd_dev('Ar38_blank'),), (getnominal_value('Ar37_blank'),), (getstd_dev('Ar37_blank'),), (getnominal_value('Ar36_blank'),), (getstd_dev('Ar36_blank'),), ] for i, ai in enumerate(analyses): for j, arg in enumerate(attrs): if len(arg) == 2: func, style = arg else: func, style = arg[0], Style.default_style sh.write(i + hrow + 1, 1 + j, func(ai), style ) def publish(self): self._wb.save(self.filename)
b = html.find("',", a) return html[a:b] #进入指定房源页面获取房源面积 def get_area(html): a = html.find("area:'") + 6 b = html.find("',", a) return html[a:b] if __name__ == '__main__': city = 'xm' dist = 'huli' total_page = 3 house_ids = get_all_house_ids(city, dist, total_page) book = Workbook() sheet = book.add_sheet(dist) i = 0 for house_id in house_ids: file = 'd://lianjia_xm_house//'+city+'_'+dist+'_' + str(house_id) +'.html' html = get_house_html(file) house_estate = get_house_estate(html).decode('utf-8') print '小区:', house_estate house_type = get_house_type(html).decode('utf-8') print '类型:', house_type house_area = get_area(html).decode('utf-8') print '面积:', house_area house_perprice = get_per_price(html).decode('utf-8') print '单价:', house_perprice house_totalprice = get_total_price(html).decode('utf-8') print '总价:', house_totalprice
def save_excel(city, dist_dict): book = Workbook() for dist in dist_dict: sheet = book.add_sheet(dist) excel_file = 'd://lianjia_house//' + city + '.xls' sheet.write(0, 0, '小区'.decode('utf-8')) sheet.write(0, 1, '总价'.decode('utf-8')) sheet.write(0, 2, '单价'.decode('utf-8')) sheet.write(0, 3, '户型'.decode('utf-8')) sheet.write(0, 4, '面积'.decode('utf-8')) sheet.write(0, 5, '装修'.decode('utf-8')) sheet.write(0, 6, '楼层'.decode('utf-8')) sheet.write(0, 7, '位置'.decode('utf-8')) sheet.write(0, 8, '关注人数'.decode('utf-8')) sheet.write(0, 9, '看房次数'.decode('utf-8')) sheet.write(0, 10, '发布时间'.decode('utf-8')) sheet.write(0, 11, '税费'.decode('utf-8')) i = 1 for page_num in range(1, int(dist_dict[dist]) + 1): file = 'd://lianjia_' + city + '//' + city + '_' + dist + '_' + str( page_num) + '.html' if os.path.exists(file): re_house_regoin = r'"region">(.*?)</a>' re_house_detail = r'"region">.*?</a>(.*?)</div>' re_house_totalprice = r'totalPrice">.*?<span>(.*?)</span>' re_house_perprice = r'data-price="(.*?)">' re_house_poistion = r'class="positionInfo">.*?<a.*?target="_blank">(.*?)</a>' re_house_floor = r'class="positionIcon"></span>(.*?)<a' re_house_publish = r'class="starIcon"></span>(.*?)</div>' re_house_tag = r'<li.*?class="tag".*?>(.*?)</div>' re_sub_house_tag = r'<span.*?class=.*?">(.*?)</span>' house_regoin = house_info(file, re_house_regoin) house_detail = house_info(file, re_house_detail) house_total_price = house_info(file, re_house_totalprice) house_per_price = house_info(file, re_house_perprice) house_position = house_info(file, re_house_poistion) house_floor = house_info(file, re_house_floor) house_publish = house_info(file, re_house_publish) house_tag = house_info(file, re_house_tag) #print house_regoin[1] house_sum = len(house_regoin) print 'per page house nums: %s' % house_sum for seq in range(0, house_sum): house_focus = house_publish[seq].split( '/')[0].strip().strip('人关注') house_look = house_publish[seq].split( '/')[1].strip().strip('共').strip('次带看') house_push_time = house_publish[seq].split('/')[2].strip() house_layout = house_detail[seq].split('|')[1].strip() house_area = house_detail[seq].split('|')[2].strip().strip( '平米') house_fixed = '' for each_house_fixed in house_detail[seq].split('|')[3:]: house_fixed = house_fixed + '' + each_house_fixed house_tax = '' if house_tag[seq] == '': house_tax = house_tag[seq] else: house_tax_sub = house_info(house_tag[seq], re_sub_house_tag) for each_house_tax in house_tax_sub: house_tax = house_tax + each_house_tax + ' ' print '小区:%s 总价:%s 单价:%s 户型:%s 面积: %s 装修: %s 楼层:%s 位置:%s 关注人数: %s 看房次数:%s 发布时间: %s 税费: %s' \ %(house_regoin[seq], house_total_price[seq], house_per_price[seq], house_layout, house_area, house_fixed, house_floor[seq], house_position[seq], house_focus, house_look, house_push_time, house_tax) sheet.write(i, 0, house_regoin[seq].decode('utf-8')) sheet.write(i, 1, house_total_price[seq].decode('utf-8')) sheet.write(i, 2, house_per_price[seq].decode('utf-8')) sheet.write(i, 3, house_layout.decode('utf-8')) sheet.write(i, 4, house_area.decode('utf-8')) sheet.write(i, 5, house_fixed.decode('utf-8')) sheet.write(i, 6, house_floor[seq].decode('utf-8')) sheet.write(i, 7, house_position[seq].decode('utf-8')) sheet.write(i, 8, house_focus.decode('utf-8')) sheet.write(i, 9, house_look.decode('utf-8')) sheet.write(i, 10, house_push_time.decode('utf-8')) sheet.write(i, 11, house_tax.decode('utf-8')) i = i + 1 print 'page %s-------' % page_num #my_logger(page_num) book.save(excel_file) print 'save'
def write(self, stream): wb = WtWorkbook() for sheet in self._sheets: sheet.write(wb) wb.save(stream)
# -*-coding:UTF-8 -*- from pymysql import connect from xlwt.Workbook import Workbook # 链接MySQL数据库 conn = connect(host='192.168.1.4', user='******', password="******", database='cms', port=3306) # 游标 cursor = conn.cursor() # 引出表格 cursor.execute('select * from wang_student') result = cursor.fetchall() print(result) cursor.close() conn.close() wb = Workbook('utf-8')
# -*-coding:UTF-8 -*- from xlwt.Workbook import Workbook from xlwt.Formatting import Alignment from xlwt.Style import XFStyle # wb = Workbook('utf-8') # sheet = wb.add_sheet('test') # sheet.write(2, 2, 'test') # wb.save(r'd:/test.xls') wb = Workbook('utf-8') sheet = wb.add_sheet('test2') title = ['编号', '姓名', '职业', '上级', '入职日期'] title_style = XFStyle() content_style = XFStyle() alignment = Alignment() alignment.horz = alignment.HORZ_CENTER title_style.alignment = alignment content_style.alignment = alignment for i in range(len(title)): sheet.write(0, i, title[i], title_style) content = [[7369, 'SMITH', 'CLERK', 7902, '12/17/1980'], [7499, 'ALLEN', 'SALESMAN', 7698, '2/20/1981'], [7521, 'WARD', 'SALESMAN', 7698, '2/22/1981'], [7566, 'JONES', 'MANAGER', 7839, '4/2/1981'], [7654, 'MARTIN', 'SALESMAN', 7698, '9/28/1981'], [7698, 'BLAKE', 'MANAGER', 7839, '5/1/1981'],
user='******', password="******", database='cms', port=3306) cursor = conn.cursor() cursor.execute('select * from _cai_student_cai') result = cursor.fetchall() #print(result) from xlwt.Formatting import Borders, Font, Alignment, Pattern from xlwt.Style import XFStyle from xlwt.Workbook import Workbook wb = Workbook('utf-8') sheet = wb.add_sheet('学生信息') title = ['编号', '姓名', '生日', '性别'] title_style = XFStyle() border = Borders() border.left = border.THIN border.right = border.THIN border.top = border.THIN border.bottom = border.THIN font = Font() font.bold = True alignment = Alignment() alignment.horz = alignment.HORZ_CENTER
# 对str做分词 str = seg_depart(str) str = str.strip() rowList.append(str) # 添加对应的标签0或1 # rowList.append(0) rowList.append(1) excellist.append(rowList) print(excellist) # excel表格式 book = Workbook() sheet1 = book.add_sheet('Sheet1') row0 = ['review', 'label'] for i in range(len(row0)): sheet1.write(0, i, row0[i]) # 两个for循环,第一个for循环针对写入excel的每行,第二个for循环针对每行的各列 for i, li in enumerate(excellist): print('i:{}, li:{}'.format(i, li)) for j, lj in enumerate(li): sheet1.write(i+1, j, lj) # 数据存入excel文件 book.save('neg_test_excel.xls') book.save('pos_test_excel.xls')
def get_old_deposit_transfers_task(): old_transfers = DepositTransferNew.objects.order_by("user", "-created_at") try: book = Workbook() chongzhi_sheet = book.add_sheet('ChongZhi') koukuan_sheet = book.add_sheet('KouKuan') chongzhi_row = 0 koukuan_row = 0 chongzhi_sheet.write(chongzhi_row, 0, u"客户编号") chongzhi_sheet.write(chongzhi_row, 1, u"金额") chongzhi_sheet.write(chongzhi_row, 2, u"单号") chongzhi_sheet.write(chongzhi_row, 3, u"描述") chongzhi_sheet.write(chongzhi_row, 4, u"时间") chongzhi_sheet.write(chongzhi_row, 5, u"货币") koukuan_sheet.write(koukuan_row, 0, u"客户编号") koukuan_sheet.write(koukuan_row, 1, u"金额") koukuan_sheet.write(koukuan_row, 2, u"单号") koukuan_sheet.write(koukuan_row, 3, u"描述") koukuan_sheet.write(koukuan_row, 4, u"时间") koukuan_sheet.write(koukuan_row, 5, u"货币") koukuan_sheet.write(koukuan_row, 6, u"已扣金额") koukuan_sheet.write(koukuan_row, 7, u"申报应付金额") koukuan_sheet.write(koukuan_row, 8, u"库房应付金额") koukuan_sheet.write(koukuan_row, 9, u"计费重量") koukuan_sheet.write(koukuan_row, 10, u"申报重量") koukuan_sheet.write(koukuan_row, 11, u"申报长度") koukuan_sheet.write(koukuan_row, 12, u"申报宽度") koukuan_sheet.write(koukuan_row, 13, u"申报高度") koukuan_sheet.write(koukuan_row, 14, u"库房重量") koukuan_sheet.write(koukuan_row, 15, u"库房长度") koukuan_sheet.write(koukuan_row, 16, u"库房宽度") koukuan_sheet.write(koukuan_row, 17, u"库房高度") chongzhi_row = 1 koukuan_row = 1 for old_transfer in old_transfers: if old_transfer.amount >= 0.01: chongzhi_sheet.write(chongzhi_row, 0, old_transfer.user.userprofile.customer_number) chongzhi_sheet.write(chongzhi_row, 1, round(Decimal(old_transfer.amount), 2)) chongzhi_sheet.write(chongzhi_row, 2, old_transfer.origin) chongzhi_sheet.write(chongzhi_row, 3, old_transfer.ref) chongzhi_sheet.write(chongzhi_row, 4, old_transfer.created_at.strftime("%Y-%m-%d %H:%M:%S")) chongzhi_sheet.write(chongzhi_row, 5, old_transfer.user.userprofile.deposit_currency_type) chongzhi_row += 1 else: koukuan_sheet.write(koukuan_row, 0, old_transfer.user.userprofile.customer_number) koukuan_sheet.write(koukuan_row, 1, round(Decimal(old_transfer.amount), 2)) koukuan_sheet.write(koukuan_row, 2, old_transfer.origin) koukuan_sheet.write(koukuan_row, 3, old_transfer.ref) koukuan_sheet.write(koukuan_row, 4, old_transfer.created_at.strftime("%Y-%m-%d %H:%M:%S")) koukuan_sheet.write(koukuan_row, 5, old_transfer.user.userprofile.deposit_currency_type) try: intl_parcel = IntlParcel.objects.get(yde_number=old_transfer.origin) koukuan_sheet.write(koukuan_row, 6, round(Decimal(intl_parcel.booked_fee or 0), 2)) #logger.error('axxx') koukuan_sheet.write(koukuan_row, 7, round(Decimal(intl_parcel.get_fee() or 0), 2)) # u"申报应付金额") #logger.error('bxxx') try: real_fee=intl_parcel.get_real_fee() except: real_fee=0 koukuan_sheet.write(koukuan_row, 8, round(Decimal(real_fee), 2)) # u"库房应付金额") #logger.error('cxxx') vweight = intl_parcel.length_cm * intl_parcel.width_cm * intl_parcel.height_cm / 6000 real_vweight = intl_parcel.real_length_cm * intl_parcel.real_width_cm * intl_parcel.real_height_cm / 6000 chargeable_weight = max(w for w in [intl_parcel.weight_kg, vweight, intl_parcel.real_weight_kg, real_vweight]) #logger.error('dxxx') koukuan_sheet.write(koukuan_row, 9, round(Decimal(chargeable_weight), 2)) # u"计费重量") koukuan_sheet.write(koukuan_row, 10, round(Decimal(intl_parcel.weight_kg or 0), 2)) # u"申报重量") koukuan_sheet.write(koukuan_row, 11, round(Decimal(intl_parcel.length_cm or 0), 2)) # u"申报长度") koukuan_sheet.write(koukuan_row, 12, round(Decimal(intl_parcel.width_cm or 0), 2)) # u"申报宽度") koukuan_sheet.write(koukuan_row, 13, round(Decimal(intl_parcel.height_cm or 0), 2)) # u"申报高度") koukuan_sheet.write(koukuan_row, 14, round(Decimal(intl_parcel.real_weight_kg or 0), 2)) # u"库房重量") koukuan_sheet.write(koukuan_row, 15, round(Decimal(intl_parcel.real_length_cm or 0), 2)) # u"库房长度") koukuan_sheet.write(koukuan_row, 16, round(Decimal(intl_parcel.real_width_cm or 0), 2)) # u"库房宽度") koukuan_sheet.write(koukuan_row, 17, round(Decimal(intl_parcel.real_height_cm or 0), 2)) # u"库房高度") except IntlParcel.DoesNotExist: try: retoure = DhlRetoureLabel.objects.get(retoure_yde_number=old_transfer.origin) koukuan_sheet.write(koukuan_row, 6, "DHL Retoure") except DhlRetoureLabel.DoesNotExist: koukuan_sheet.write(koukuan_row, 6, "NOOOOOOOOOO") except DhlRetoureLabel.MultipleObjectsReturned: koukuan_sheet.write(koukuan_row, 6, "Retoure Duplicated") except Exception as e: logger.error(e) koukuan_sheet.write(koukuan_row, 6, "%s" % e) except IntlParcel.MultipleObjectsReturned: koukuan_sheet.write(koukuan_row, 6, "Intl Parcel Duplicated") except Exception as e: logger.error(e) logger.error(intl_parcel.yde_number) koukuan_sheet.write(koukuan_row, 6, "%s" % e) koukuan_row += 1 book.save("/tmp/old_transfers.xls") connection = get_connection(username=None, password=None, fail_silently=False) mail = EmailMultiAlternatives(u"Old deposit transfers", "", u'韵达德国分公司 <*****@*****.**>', ["*****@*****.**"], connection=connection) mail.attach_file("/tmp/old_transfers.xls") mail.send() #return HttpResponse() except Exception as e: logger.error("%s" % e)
str += line str = str.strip() print('str:', str) print('-----------------------------------------') # 对str做分词 str = seg_depart(str) str = str.strip() rowList.append(str) # 添加对应的标签0或1 # rowList.append(0) rowList.append(1) excellist.append(rowList) # excel表格式 book = Workbook() sheet1 = book.add_sheet('Sheet1') row0 = ['review', 'label'] for i in range(len(row0)): sheet1.write(0, i, row0[i]) # 两个for循环,第一个for循环针对写入excel的每行,第二个for循环针对每行的各列 for i, li in enumerate(excellist): print('i:{}, li:{}'.format(i, li)) for j, lj in enumerate(li): sheet1.write(i + 1, j, lj) # 数据存入excel文件 # book.save('neg_fenci_excel.xls') book.save('pos_fenci_excel.xls')
# -*-coding:UTF-8 -*- from xlwt.Workbook import Workbook from xlwt.Style import XFStyle from importlib.resources import contents Workbook wb=Workbook('utf-8') sheet=wb.add_sheet('测试') title = ['编号', '姓名', '职业', '上级', '入职日期'] title_style = XFStyle() for i in range(len(title)): sheet.write(4,i + 4,title[i],title_style) content = [7369,'SMITH','CLERK',7902,12/17/1980] ,[7499,'ALLEN','SALESMAN',7698,2/20/1981] for i in range(len(content)): for j in range(len(content[i])):
def save_excel(city, dist_dict): book = Workbook() for dist in dist_dict: sheet = book.add_sheet(dist) excel_file = 'd://danxia_house//' + city + '.xls' sheet.write(0, 0, '小区'.decode('utf-8')) sheet.write(0, 1, '总价'.decode('utf-8')) sheet.write(0, 2, '单价'.decode('utf-8')) sheet.write(0, 3, '户型'.decode('utf-8')) sheet.write(0, 4, '面积'.decode('utf-8')) sheet.write(0, 5, '朝向'.decode('utf-8')) sheet.write(0, 6, '楼层'.decode('utf-8')) sheet.write(0, 7, '位置'.decode('utf-8')) sheet.write(0, 8, '年限'.decode('utf-8')) sheet.write(0, 9, '看房次数'.decode('utf-8')) sheet.write(0, 10, '其他'.decode('utf-8')) i = 1 for page_num in range(1, int(dist_dict[dist]) + 1): file = 'd://danxia_' + city + '//' + city + '_' + dist + '_' + str( page_num) + '.html' #print file if os.path.exists(file): #房子house_herf re_house_herf = r'<a.*?class="img".*?href="(.*?)".*?target="_blank">' #小区正则规则 re_house_regoin = r'<p.*?class="moudle">.*?<a.*?target="_blank">(.*?)</a>' #房型信息正则规则 re_module = r'<p.*?class="moudle">.*?<a.*?</a>(.*?)</p>' re_sub_module = r'<span>(.*?)</span>' #房屋总价正则规则 re_total_price = r'<div.*?class="percent">.*?<b>(.*?)</b>' #房屋单价正则规则 re_per_price = r'<div.*?class="percent">.*?<span>(.*?)</span>' #看房次数正则规则 re_look = r'<p.*?class="text">.*?<span>(.*?)</span>' #房屋区域正则规则 re_zone = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<a.*?target="_blank">(.*?)</a>' #房屋楼层、年限 re_msg = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<a.*?</a>(.*?)</p>' re_sub_msg = r'<span>(.*?)</span>' re_fix = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<p.*?class=".*?">(.*?)</p>' re_sub_fix = r'<i.*?class=".*?">(.*?)</i>' #房子house_href连接 house_index_ids = house_info(file, re_house_herf) #当前页面房屋所在小区列表 house_regoin = house_info(file, re_house_regoin) #print '-----', house_regoin #当前页户型、面积、朝向信息列表 house_module = house_info(file, re_module) #当前页面房屋总价列表 house_totalprice = house_info(file, re_total_price) #当前页房屋单价列表 house_perprice = house_info(file, re_per_price) #当前页房屋看房次数列表 house_look = house_info(file, re_look) #当前页房屋所在区域列表 house_zone = house_info(file, re_zone) #当前页房屋楼层、年限类别 house_msg = house_info(file, re_msg) #当前页房屋其他信息(有否钥匙等) house_fix = house_info(file, re_fix) #当前页面房屋数 house_length = len(house_regoin) print 'per page house nums: %s' % house_length for seq in range(house_length): house_href = 'http://danxia.com' + house_index_ids[seq] house_sub_module = house_info(house_module[seq], re_sub_module) house_sub_module_length = len(house_sub_module) if len(house_sub_module) == 3: #户型 house_hx = house_sub_module[0] #面积 house_area = house_sub_module[1].strip('平米') #朝向 house_faceto = house_sub_module[2] #若没有朝向信息,则输入为空 if len(house_sub_module) == 2: house_hx = house_sub_module[0] house_area = house_sub_module[1].strip('平米') house_faceto = ' ' #拆分成楼层、年限信息三个列表 house_sub_msg = house_info(house_msg[seq], re_sub_msg) house_sub_msg_length = len(house_sub_msg) if house_sub_msg_length == 2: #楼层 house_floor = house_sub_msg[0] #年限 house_year = house_sub_msg[1] if house_sub_msg_length == 1: #楼层 house_floor = house_sub_msg[0] #年限 house_year = ' ' house_others = '' house_sub_fix = house_info(house_fix[seq], re_sub_fix) for each_house_sub_fix in house_sub_fix: house_others = house_others + each_house_sub_fix + ',' house_others #house_z = house_zone[seq] #print type(house_z.strip('二手房').decode('utf-8')) print '房源链接: %s, 小区:%s, 总价:%s, 单价:%s, 户型:%s, 面积:%s, 朝向:%s, 楼层:%s, 区域:%s, 年限:%s, 看房次数:%s, 其他:%s' \ % (house_href, house_regoin[seq], house_totalprice[seq], house_perprice[seq].strip('元/㎡'), house_hx, house_area, house_faceto, house_floor, house_zone[seq][:-9], house_year.strip('年建'), house_look[seq], house_others) ''' sheet.write(i, 0 ,house_regoin[seq].decode('utf-8')) sheet.write(i, 1 ,house_totalprice[seq].decode('utf-8')) sheet.write(i, 2 ,house_perprice[seq].strip('元/㎡').decode('utf-8')) sheet.write(i, 3 ,house_hx.decode('utf-8')) sheet.write(i, 4 ,house_area.decode('utf-8')) sheet.write(i, 5 ,house_faceto.decode('utf-8')) sheet.write(i, 6 ,house_floor.decode('utf-8')) sheet.write(i, 7 ,house_zone[seq][:-9].decode('utf-8')) sheet.write(i, 8 ,house_year.strip('年建').decode('utf-8')) sheet.write(i, 9 ,house_look[seq].decode('utf-8')) sheet.write(i, 10 ,house_others.decode('utf-8')) i=i+1 ''' print 'page %s-------' % page_num #my_logger(page_num) book.save(excel_file) print 'save'
def __init__(self, *args, **kw): super(ExcelWriter, self).__init__(*args, **kw) self._wb = Workbook()
class sudoku(object): ''' classdocs ''' def __init__(self): self.sudoku=[] self.sudokuCheck=[] self.elements=[] self.elementInit() self.wb = Workbook() self.ws0 = self.wb.add_sheet('sudoku1',cell_overwrite_ok=True) self.sw=1 self.cont=0 self.var=[] self.stack=[] self.file= open('log.txt','w') def elementInit(self): self.elements=[1,2,3,4,5,6,7,8,9] def writeExcel(self): for i in range(0,9): for j in range(0,9): if len(self.sudokuCheck[i][j]) == 1: n=self.sudokuCheck[i][j][0] self.sudoku[i][j]=n self.ws0.write(i,j,n) else: self.ws0.write(i,j,0.0) self.sudoku[i][j]=0.0 def readSudoku(self): wb = open_workbook('test.xls') for s in wb.sheets(): print 'Sheet:',s.name for row in range(s.nrows): values = [] for col in range(s.ncols): values.append(int(s.cell(row,col).value)) self.sudoku.append(values) def globalCheck(self): self.mostConstrainedVariable() ng=30 value=0 for l in self.var: cons=0 cons2=0 if len(self.sudokuCheck[l[0]][l[1]])>1: for k in self.sudokuCheck[l[0]][l[1]]: cons += self.checkRowConstrains(l[0], l[1], k) cons += self.checkColumnConstrains(l[0], l[1], k) cons += self.checkBoxConstrains(l[0], l[1], k) cons2+=1 self.sw=1 else: cons += self.checkRowConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0]) cons += self.checkColumnConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0]) cons += self.checkBoxConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0]) self.sw=1 if cons<ng : ng=cons cons3=cons2 value=copy.deepcopy(l) if(value!=0): sc=copy.deepcopy(self.sudokuCheck) self.file.writelines("----Introduccion de Elemento----\n") self.file.writelines("se introdujo el valor:" + str(self.sudokuCheck[value[0]][value[1]][cons3-1]) +" En la posicion: "+ str(value[0]+1)+","+str(value[1]+1)+ " ya que es la variable mas restringida y menos restrictora\n") self.file.writelines( "--------------------------------\n") self.file.writelines( "----Sudoku----\n") self.file.writelines( str(self.sudoku)+"\n") self.sudoku[value[0]][value[1]]=self.sudokuCheck[value[0]][value[1]][cons3-1] stackItem=[value[0],value[1],sc[value[0]][value[1]]] self.stack.append(stackItem) sc[value[0]][value[1]].pop(cons3-1) self.file.writelines( str(self.sudoku )+"\n") self.file.writelines( "----Sudoku----\n") def checkSudokuCheck(self): for i in range(0,9): for j in range(0,9): if(len(self.sudokuCheck[i][j])==0): self.file.writelines( "----- Backtrack -----\n" ) return 0 return 1 def cycleSudoku(self): cont=0 while self.sw: self.sudokuCheck=[] self.sw=0 self.checkSudoku() cont+=1 self.file.writelines( "---- Numero de Iteraciones ----\n") self.file.writelines(str( cont)+"\n") self.file.writelines( "-------------------------------\n") self.writeExcel() self.wb.save('sudoku.xls') self.file.close() print "El sudoku a sido resuelto\n...\n...\n...\nlog.txt creado\nsudoku.xls creado" def checkSudoku(self): for i in range(0,9): jcheck=[] for j in range(0,9): v=[] if self.sudoku[i][j]==0.0: self.checkRow(i, j) self.checkColumn(i, j) self.checkBox(i, j) v=self.elements self.elementInit() else: v.append(self.sudoku[i][j]) jcheck.append(v) self.sudokuCheck.append(jcheck) self.file.writelines( "----- Matriz de Posibilidades -----\n") for k1 in range(0,9): s=[] for k2 in range(0,9): s.append(self.sudokuCheck[k1][k2]) self.file.writelines( str(s)+"\n") self.file.writelines( "-------------------------\n") if(self.checkSudokuCheck()): self.globalCheck() else: self.sudokuBacktrack() def sudokuBacktrack(self): sw1=1 while sw1: b=self.stack.pop() if len(b[2])!=0: self.file.writelines( "backtracking en la posicion: "+str(b[0]+1)+","+str(b[1]+1)+"... Posibilidades disponibles: "+str(b[2])+"\n") sw1=0 else: self.sudoku[b[0]][b[1]]=0 maxc=30 i2=0 for i in range(0,len(b[2])): conti=0 conti+=self.checkRowConstrains(b[0], b[1], b[2][i]) conti+=self.checkColumnConstrains(b[0], b[1], b[2][i]) conti+=self.checkBoxConstrains(b[0], b[1], b[2][i]) if conti<maxc: maxc=conti i2=i self.sudoku[b[0]][b[1]]=b[2].pop(i2) self.stack.append(b) self.sw=1 def checkRow(self,i,j): for k in range(0,9): if(self.elements.count((self.sudoku[i][k]))>0): self.elements.remove(self.sudoku[i][k]) def checkColumn(self,i,j): for k in range(0,9): if(self.elements.count((self.sudoku[k][j]))>0): self.elements.remove(self.sudoku[k][j]) def checkBox(self,i,j): if i<3: if j<3: i2=0 j2=0 elif j>=3 and j<6: i2=0 j2=3 else: i2=0 j2=6 elif i>=3 and i<6: if j<3: i2=3 j2=0 elif j>=3 and j<6: i2=3 j2=3 else: i2=3 j2=6 else: if j<3: i2=6 j2=0 elif j>=3 and j<6: i2=6 j2=3 else: i2=6 j2=6 for i3 in range(i2,i2+3): for j3 in range(j2,j2+3): if(self.elements.count((self.sudoku[i3][j3]))>0): self.elements.remove(self.sudoku[i3][j3]) def checkRowConstrains(self,i,j,n): rowConst=0 for k in range(0,9): if(k!=j and self.sudokuCheck[i][k].count(n)==1): rowConst += 1 return rowConst def checkColumnConstrains(self,i,j,n): columnConst=0 for k in range(0,9): if(k!=i and self.sudokuCheck[k][j].count(n)==1): columnConst += 1 return columnConst def checkBoxConstrains(self,i,j,n): boxConst=0 if i<3: if j<3: i2=0 j2=0 elif j>=3 and j<6: i2=0 j2=3 else: i2=0 j2=6 elif i>=3 and i<6: if j<3: i2=3 j2=0 elif j>=3 and j<6: i2=3 j2=3 else: i2=3 j2=6 else: if j<3: i2=6 j2=0 elif j>=3 and j<6: i2=6 j2=3 else: i2=6 j2=6 for i3 in range(i2,i2+3): for j3 in range(j2,j2+3): if i3!=i and j3!=j and self.sudokuCheck[i3][j3].count(n)==1: boxConst+=1 return boxConst def mostConstrainedVariable(self): m=10 var=[] for i in range(0,9): for j in range(0,9): if self.sudoku[i][j]==0: if len(self.sudokuCheck[i][j]) <= m: m=len(self.sudokuCheck[i][j]) var.append([i,j]) var2=copy.deepcopy(var) for v in var2: if len(self.sudokuCheck[v[0]][v[1]])>m: var.remove(v) self.var=copy.deepcopy(var)
def _new_workbook(self): return Workbook()
# -*-coding:UTF-8 -*- from xlwt.Formatting import Borders, Font, Alignment, Pattern from xlwt.Style import XFStyle from xlwt.Workbook import Workbook from tkinter import font wb = Workbook() sheet = wb.add_sheet('测试报告') sheet.write(1, 1, '超级无敌龟派气功') wb.save(r'd:\自动化测试.xls') wb = Workbook('utf-8') sheet = wb.add_sheet('高级测试报告') title = ['编号', '姓名', '职业', '上级', '入职日期'] title_style = XFStyle() border = Borders() border.left = border.THIN border.right = border.THIN border.top = border.THIN border.bottom = border.THIN font = Font() font.bold = True alignment = Alignment() alignment.horz = alignment.HORZ_CENTER pattern = Pattern() pattern.pattern = pattern.SOLID_PATTERN
# -*- coding: utf-8 -*- from xlwt.Formatting import Borders, Font, Alignment, Pattern from xlwt.Style import XFStyle from xlwt.Workbook import Workbook wb = Workbook('utf-8') sheet = wb.add_sheet('测试报告') title = ['编号', '姓名', '职业', '上级', '入职日期'] title_style = XFStyle() border = Borders() border.left = border.THIN border.right = border.THIN border.top = border.THIN border.bottom = border.THIN font = Font() font.bold = True alignment = Alignment() alignment.horz = alignment.HORZ_CENTER pattern = Pattern() pattern.pattern = pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x35 title_style.borders = border title_style.font = font title_style.alignment = alignment title_style.pattern = pattern
def export_account_bill_excel(request): user = request.user item_list = [] item_list = AccountBill.objects.all() name = request.GET.get("name", None) if name: item_list = item_list.filter(account__name=name) account = request.GET.get("account", None) if account: item_list = item_list.filter(account_id=account) account_type = request.GET.get("account_type", None) if account_type: item_list = item_list.filter(account__type=account_type) type = request.GET.get("type", None) if type: item_list = item_list.filter(type=type) subtype = request.GET.get("subtype", None) if subtype: item_list = item_list.filter(subtype=subtype) target = request.GET.get("target", None) if target: item_list = item_list.filter(target__contains=target) timeft_0 = request.GET.get("timeft_0", None) timeft_1 = request.GET.get("timeft_1", None) if timeft_0 and timeft_1: s = datetime.datetime.strptime(timeft_0, '%Y-%m-%d') e = datetime.datetime.strptime(timeft_1, '%Y-%m-%d') e += timedelta(days=1) item_list = item_list.filter(time__range=(s, e)) data = [] for con in item_list: time = con.time.strftime("%Y-%m-%d %H:%M") account_id = con.account_id account_type = con.account.get_type_display() account_name = con.account.name bill_type = con.get_type_display() subtype = con.get_subtype_display() target = con.target amount = con.amount balance = con.account.balance remark = con.remark data.append([ time, account_id, account_type, account_name, bill_type, subtype, target, amount, balance, remark ]) w = Workbook() #创建一个工作簿 ws = w.add_sheet(u'账目明细') #创建一个工作表 title_row = [ u'账单时间', u'账户ID', u'账户类型', u'账户名称', u'账单类型', u'收/支类型', u'交易对象', u'交易金额', u'账户余额', u'备注' ] for i in range(len(title_row)): ws.write(0, i, title_row[i]) row = len(data) style1 = easyxf(num_format_str='YY/MM/DD HH:mm') for i in range(row): lis = data[i] col = len(lis) for j in range(col): if j == 0: ws.write(i + 1, j, lis[j], style1) else: ws.write(i + 1, j, lis[j]) sio = StringIO.StringIO() w.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=导出表格.xls' response.write(sio.getvalue()) return response
def handle(self, *args, **kwargs): # Save a file with summary statistics self.log('Saving summary stats...') percent_correct = 1.0 * F('correct_images') / F('user__num_images') percentages = LoginAttempt.objects.filter( right_password=True).annotate( percent_correct=percent_correct).order_by( 'percent_correct').values_list('percent_correct', flat=True) total = len(percentages) middle = total / 2 - 1 if total % 2 == 0: median = sum(percentages[middle:middle + 2]) / 2 else: median = percentages[middle] with default_storage.open('summary.txt', 'w') as f: f.write('\n'.join([ 'Total accounts: %d' % User.objects.count(), 'Total login attempts: %d' % LoginAttempt.objects.count(), 'Total login attempts with invalid passwords: %d' % LoginAttempt.objects.filter(right_password=False).count(), 'Min percentage: %f' % percentages[0], 'Max percentage: %f' % percentages[total - 1], 'Average percentage: %f' % (sum(percentages) / float(total)), 'Median percentage: %f' % median, ])) # Save a file with all the data as an Excel file self.log('Saving all data...') workbook = Workbook() users_sheet = ExcelSheet(workbook, 'Users') fields = ['timestamp', 'username', 'email', 'num_images'] values = User.objects.values_list(*fields) users_sheet.write_headers(fields) users_sheet.add_rows(values) logins_sheet = ExcelSheet(workbook, 'Login Attempts') logins_sheet.write_headers([ 'Timestamp', 'Username', 'Right password?', '# correct images', '# total images', 'Percent correct' ]) images_accuracy = ExcelSheet(workbook, 'NumImages_Accuracy') images_accuracy.write_headers(['Number of Images', 'Percent Correct']) check_sheet = ExcelSheet(workbook, 'Check') check_sheet.write_headers([ 'Number of Images', 'Accuracy Threshold', 'Time', 'Right password' ]) crack_sheet = ExcelSheet(workbook, 'Crack') crack_sheet.write_headers( ['Number of Images', 'Accuracy Threshold', 'Time']) for login in LoginAttempt.objects.annotate( percent_correct=percent_correct): num_images = login.user.num_images logins_sheet.add_row([ login.timestamp, login.user.username, login.right_password, login.correct_images, num_images, login.percent_correct ]) if login.right_password: # only run accuracy for logins with the right password images_accuracy.add_row([num_images, login.percent_correct]) try: benchmarks = login.get_benchmarks() except: continue for threshold, time in enumerate(benchmarks['check']): check_sheet.add_row( [num_images, threshold, time, login.right_password]) for threshold, time in enumerate(benchmarks['crack']): crack_sheet.add_row([num_images, threshold, time]) with default_storage.open('data.xls', 'w+') as f: workbook.save(f)
def export_investdata_excel(request): user = request.user item_list = ProjectInvestData.objects is_futou = request.GET.get("is_futou", None) if is_futou == "true": item_list = item_list.filter(is_futou=True) elif is_futou == "false": item_list = item_list.filter(is_futou=False) state = request.GET.get("state", None) if state: item_list = item_list.filter(state=state) source = request.GET.get("source", None) if source: item_list = item_list.filter(source=source) invest_mobile = request.GET.get("invest_mobile", None) if invest_mobile: item_list = item_list.filter(invest_mobile=invest_mobile) name__contains = request.GET.get("name__contains", None) if name__contains: item_list = item_list.filter(project__name__contains=name__contains) project = request.GET.get("project_id", None) if project: item_list = item_list.filter(project_id=project) investtime_0 = request.GET.get("investtime_0", None) investtime_1 = request.GET.get("investtime_1", None) audittime_0 = request.GET.get("audittime_0", None) audittime_1 = request.GET.get("audittime_1", None) if investtime_0 and investtime_1: s = datetime.datetime.strptime(investtime_0, '%Y-%m-%d') e = datetime.datetime.strptime(investtime_1, '%Y-%m-%d') item_list = item_list.filter(invest_time__range=(s, e)) if audittime_0 and audittime_1: s = datetime.datetime.strptime(audittime_0, '%Y-%m-%d') e = datetime.datetime.strptime(audittime_1, '%Y-%m-%d') item_list = item_list.filter(audit_time__range=(s, e)) item_list = item_list.select_related('project').order_by('invest_time') data = [] for con in item_list: project = con.project id = con.id project_id = project.id project_name = project.name if con.is_futou: is_futou = u"复投" else: is_futou = u"首投" invest_time = con.invest_time invest_mobile = con.invest_mobile invest_amount = con.invest_amount invest_term = con.invest_term settle_amount = con.settle_amount return_amount = '' result = '' if con.state == '0': result = u'是' return_amount = con.return_amount elif con.state == '2': result = u'否' source = con.get_source_display() remark = con.remark data.append([ id, project_id, project_name, is_futou, invest_time, invest_mobile, invest_amount, invest_term, settle_amount, result, return_amount, source, remark ]) w = Workbook() #创建一个工作簿 ws = w.add_sheet(u'待审核记录') #创建一个工作表 title_row = [ u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额', u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注' ] for i in range(len(title_row)): ws.write(0, i, title_row[i]) row = len(data) style1 = easyxf(num_format_str='YY/MM/DD') for i in range(row): lis = data[i] col = len(lis) for j in range(col): if j == 4: ws.write(i + 1, j, lis[j], style1) else: ws.write(i + 1, j, lis[j]) sio = StringIO.StringIO() w.save(sio) sio.seek(0) response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=导出表格.xls' response.write(sio.getvalue()) return response
# -*-coding:UTF-8 -*- from xlwt.Workbook import Workbook wb = Workbook("utf-8") sheet = wb.add_sheet("测试报告") sheet.write(1, 1, '客户无忧自动化测试报告') wb.save(r'd:\自动化测试.xls')
def admin_excel_users(request): book = Workbook() sheet = book.add_sheet('users') row = 0 sheet.write(row, 0, u'电子邮件') sheet.write(row, 1, u'姓名') sheet.write(row, 2, u'历史总单量') sheet.write(row, 3, u'1510单量') sheet.write(row, 4, u'1511单量') sheet.write(row, 5, u'1512单量') sheet.write(row, 6, u'1601单量') sheet.write(row, 7, u'1602单量') sheet.write(row, 8, u'1603单量') sheet.write(row, 9, u'1604单量') sheet.write(row, 10, u'联系电话') sheet.write(row, 11, u'城市') sheet.write(row, 12, u'公司') sheet.write(row, 13, u'地址') row += 1 try: for user in User.objects.all(): try: userprofile=user.userprofile except: continue shipments = IntlParcel.objects.filter(user=user) sheet.write(row, 0, user.email) sheet.write(row, 1, user.get_full_name() or "") sheet.write(row, 2, shipments.exclude(is_deleted=True).count()) sheet.write(row, 3, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="10").count()) sheet.write(row, 4, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="11").count()) sheet.write(row, 5, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="12").count()) sheet.write(row, 6, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="01").count()) sheet.write(row, 7, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="02").count()) sheet.write(row, 8, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="03").count()) sheet.write(row, 9, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="04").count()) address="" tel="" city="" company="" if userprofile and userprofile.street and userprofile.street not in ["-", "_", ]: address = user.get_full_name() + ", " + userprofile.street + " " + (userprofile.hause_number or "") tel=userprofile.tel or "" city=userprofile.city or "" company=userprofile.company or "" elif shipments.count()>0: shipment=shipments.order_by("-created_at")[0] address = shipment.sender_name + ", "+(shipment.sender_name2 or "")+", " + shipment.sender_street + " " + (shipment.sender_hause_number or "") tel=shipment.sender_tel or "" city=shipment.sender_city or "" company=shipment.sender_company or "" sheet.write(row, 10, tel or "") sheet.write(row, 11, city or "") sheet.write(row, 12, company or "") sheet.write(row, 13, address or "") sheet.write(row, 14, userprofile.level and userprofile.level.code or "") row += 1 response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=users-%s.xls' % datetime.now().strftime('%Y%m%d%H%M%S') book.save(response) return response except Exception as e: logger.error(e) return HttpResponse(e)
else: iccpu = '/' compn = temp2[1] compqty = temp2[4] lists.append(sdpn) lists.append(descript) lists.append(wo) lists.append(iccpu) lists.append(compn) lists.append(po) lists.append(orderqty) lists.append(compqty) lists.append(unit + price) excellist.append(lists) #d = {'SDPN':sdpn,'Descript':descript,'WO':wo,'iccpu':iccpu,'component':compn,'PO':po,'Orderqty':orderqty,'unitprice':unit+price+''} #lists.append(d) #print(excellist) book = Workbook() sheet1 = book.add_sheet('Sheet1') row0 = [ 'SD P/N', 'Description', 'WO#', 'IC CPU', 'Component', 'PO#', 'Order Qty', 'Comp qty', 'Unit price' ] for i in range(len(row0)): sheet1.write(0, i, row0[i]) for i, li in enumerate(excellist): #print(li,'------') for j, lj in enumerate(li): sheet1.write(i + 1, j, lj) book.save('d:/alltxt/result.xls') #print('生成excel,目录为:',mypath)
def generate_excel(request,surveyId): survey = Survey.objects.get(id=surveyId) surveyItemCollection = SurveyItem.objects.filter(survey=survey) wb = Workbook() for index,surveyItem in enumerate(surveyItemCollection): surveyReportObject = SurveyReportObject() work_sheet = wb.add_sheet("work-sheet" + str(index)) if surveyItem.item_type == 'SINGLE_CHOICE' or surveyItem.item_type == 'MULTIPLE_CHOICE': surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem) for idx,surveyAnswer in enumerate(surveyAnswerCollection): surveyReportObject.surveyAnswerValueDict[surveyAnswer.id]=0 surveyReportObject.surveyAnswerTextDict[surveyAnswer.id]=surveyAnswer.question_text surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem) for result in surveyResultCollection: if result.survey_result_type == 'STANDARD': surveyReportObject.surveyAnswerValueDict[result.survey_item_answer_item.id] += 1 else: surveyReportObject.surveyOptionValues.append(result.survey_item_answer_value) work_sheet.write(0,0,surveyItem.item_name) for idx,surveyAnswer in enumerate(surveyAnswerCollection): work_sheet.write(1,idx,surveyReportObject.surveyAnswerTextDict[surveyAnswer.id]) work_sheet.write(2,idx,surveyReportObject.surveyAnswerValueDict[surveyAnswer.id]) if len(surveyReportObject.surveyOptionValues) > 0: work_sheet.write(4,0,u"其他答案") for idx,optionValues in enumerate(surveyReportObject.surveyOptionValues): work_sheet.write(5+idx,0,optionValues) elif surveyItem.item_type == 'TEXT' or surveyItem.item_type == 'TEXT_AREA': work_sheet.write(0,0,surveyItem.item_name) surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem) for idx,result in enumerate(surveyResultCollection): work_sheet.write(idx+1,0,result.survey_item_answer_value) elif surveyItem.item_type == 'METRIX': work_sheet.write(0,0,surveyItem.item_name) surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem) # itemValues = surveyAnswerCollection[0].question_value answer_position_dict = {} for idx,answer in enumerate(surveyAnswerCollection): answer_position_dict[answer.id]=1+idx work_sheet.write(1,1+idx,answer.question_text) # for col in range(len(surveyAnswerCollection[0].question_value.split("\n"))): # for row in range(len(answer_position_dict)): # work_sheet.write(2+col,1+row,0) for idx,item_value in enumerate(surveyAnswerCollection[0].question_value.split("\n")): work_sheet.write(2+idx,0,item_value) item_value_dict = {} surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem) for result_idx,result in enumerate(surveyResultCollection): if result.survey_item_answer_value == item_value: if item_value_dict.has_key(result.survey_item_answer_item.id): item_value_dict[result.survey_item_answer_item.id] += 1 else: item_value_dict[result.survey_item_answer_item.id] = 1 for key in item_value_dict.iterkeys(): work_sheet.write(2+idx,answer_position_dict[key],item_value_dict[key]) elif surveyItem.item_type == 'MULTIPLE_TEXT': work_sheet.write(0,0,surveyItem.item_name) surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem) for idx,answer in enumerate(surveyAnswerCollection): resultCollection = [] surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem) for result in surveyResultCollection: if result.survey_item_answer_item == answer: resultCollection.append(result.survey_item_answer_value) work_sheet.write(1,idx,answer.question_text) for result_idx,result in enumerate(resultCollection): work_sheet.write(result_idx+2,idx,result) wb.save('/tmp/' + surveyId + ".xls") f=open('/tmp/' + surveyId + ".xls",'rb') f.seek(0,0) my_data = f.readlines() response = HttpResponse(my_data, content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=report.xls' os.remove('/tmp/' + surveyId + ".xls") return response