async def post(self): try: output = BytesIO() workbook = Workbook(output, {'in_memory': True}) sheet_accuracy = workbook.add_worksheet(name='题目正确率统计') title_format = workbook.add_format({'font_size': 12, 'bold': '1', 'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei'}) data_format = workbook.add_format({'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei'}) percent_format = workbook.add_format({'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei'}) percent_format.set_num_format('0.00%') sheet_accuracy.set_column(0, 0, 6) sheet_accuracy.set_column(1, 1, 24) sheet_accuracy.set_column(2, 2, 12) sheet_accuracy.set_column(3, 3, 64) sheet_accuracy.set_column(4, 4, 36) sheet_accuracy.set_column(5, 7, 12) # 标题 sheet_accuracy.write_row(0, 0, data=['序号', '题目ID', '题号', '题目标题', '正确答案', '总作答次数', '正确次数', '正确率'], cell_format=title_format) index = 1 # 起始行 report_data = await self.__do_get_report_data() if report_data: while await report_data.fetch_next: data = report_data.next_object() if data: sheet_accuracy.set_row(index, cell_format=title_format) sheet_accuracy.write_number(index, 0, index, data_format) # 题目属性 subject = data.subject_list[0] if data.subject_list else None if subject: sheet_accuracy.write_string(index, 1, subject.custom_code if subject.custom_code else '-', data_format) sheet_accuracy.write_string(index, 2, subject.code if subject.code else '-', data_format) sheet_accuracy.write_string(index, 3, subject.title if subject.title else '-', data_format) # 选项属性 for opt in data.subject_option_list: if opt.correct: sheet_accuracy.write_string(index, 4, '%s、%s' % (opt.sort, opt.title) if opt.title else '-', data_format) correct = data.correct if data.correct else 0 total = data.total if data.total else 0 sheet_accuracy.write_number(index, 5, total, data_format) sheet_accuracy.write_number(index, 6, correct, data_format) sheet_accuracy.write(index, 7, correct / total if total > 0 else 0, percent_format) index += 1 workbook.close() self.set_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') self.set_header('Content-Disposition', 'attachment; filename=%s' % ( 'subject_accuracy_statistics_%s.xlsx' % datetime.datetime.now().strftime('%Y%m%d%H%M%S%f'))) self.write(output.getvalue()) self.finish() except Exception: logger.error(traceback.format_exc())
def wr_xlsx(fout_xlsx, xlsx_data, **kws): """Write a spreadsheet into a xlsx file.""" if xlsx_data: from xlsxwriter import Workbook workbook = Workbook(fout_xlsx) worksheet = workbook.add_worksheet() # Cell formatting fmt_hdr = workbook.add_format({ 'top': 1, 'bottom': 1, 'left': 0, 'right': 0, 'bold': True }) fmt_txt = workbook.add_format({'border': 0}) flds = xlsx_data[0]._fields if 'fld2col_widths' in kws: _set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds) # Print header hdrs = flds if 'hdrs' not in kws else kws['hdrs'] for col_idx, hdr in enumerate(hdrs): worksheet.write(0, col_idx, hdr, fmt_hdr) row_data_start = 1 # Print data row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt, **kws) workbook.close() sys.stdout.write(" {:>5} items WROTE: {}\n".format( row_idx - row_data_start, fout_xlsx)) else: sys.stdout.write(" 0 items. NOT WRITING {}\n".format(fout_xlsx))
def _write_excel_rows(rows: list, workbook: Workbook, worksheet: Worksheet) -> int: excel_longest_word = '' for i, row in enumerate(rows, 1): row_format = None if i == 1: row_format = workbook.add_format({ 'bold': True, 'bg_color': '#cccccc', }) elif i % 2 == 0: row_format = workbook.add_format({ 'bg_color': '#f1f1f1', }) range_cells = 'A%s:I10' % i row_longest_word = find_longest_word(row) if len(row_longest_word) > len(excel_longest_word): excel_longest_word = row_longest_word worksheet.write_row(range_cells, row, row_format) self.update_state(state='PROGRESS', meta={ 'current': i, 'total': self.total_progress, 'status': 'In progress...', }) return len(excel_longest_word)
def generate_pricelist(**options): output = BytesIO() book = Workbook(output) sheet = book.add_worksheet() row = 0 col = 0 merge_format = book.add_format({ 'align':'center', 'valign':'vcenter', 'bold': 1, }) url_format = book.add_format({ 'font_color': 'blue', 'underline': 1 }) user = options['user'][0] if options.get('user') else None if options.get('filters'): filters = options.get('filters') for product in Product.objects.filter(**filters): sheet.write(row, col, product.model) # sheet.write(row, col + 1, product.name) sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru')) sheet.write(row, col + 2, product.big_opt_price) row += 1 else: for cat in Category.objects.filter(active=True): if cat.is_leaf_node(): sheet.merge_range(row,col,row,2,cat.names(lang='ru'),merge_format) sheet.write(row, col, cat.names(lang='ru')) row +=1 if not user: products = Product.objects.filter(category=cat,is_available=True) else: products = Product.objects.filter(category=cat,is_available=True,storage=1) for product in products: sheet.write(row, col, product.model) # sheet.write(row, col + 1, product.name) sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru')) if not user: sheet.write(row, col + 2, product.retail_price) else: sheet.write(row, col + 2, product.big_opt_price) row += 1 book.close() output.seek(0) if options.get('name'): path = STATIC_ROOT + options.get('name') + '.xlsx' else: path = 'static/pricelist_opt.xlsx' if user else 'static/pricelist.xlsx' with open(path,'wb') as f: f.write(output.read()) print('Завершено.')
def prepare_bold_and_border(workbook: Workbook, is_bold: bool, is_border: bool): bold_value = 1 if is_bold else 0 border_value = 1 if is_border else 0 bold = workbook.add_format({"bold": bold_value, "border": border_value}) border = workbook.add_format({"border": border_value}) return bold, border
def write_finish(wb: xlsxwriter.Workbook, ws: xlsxwriter.Workbook.worksheet_class): values, formats = set(cells_value.keys()), set(cells_format.keys()) for c in values.difference(formats): ws.write(c, cells_value[c]) for c in values.intersection(formats): ws.write(c, cells_value[c], wb.add_format(cells_format[c])) for c in formats.difference(values): ws.write_blank(c, None, wb.add_format(cells_format[c]))
def write_bookings_to_excel_file(self, booking_path): print('\nwriting to excel...') workbook = Workbook(os.path.join(booking_path, 'bookings.xlsx')) worksheet = workbook.add_worksheet() worksheet.set_column(2, 3, 50) worksheet.set_column(1, 1, 9) worksheet.set_column(4, 4, 9) bold = workbook.add_format({'bold': True}) cell_format = workbook.add_format({ 'bold': True, 'italic': True, 'font_color': 'blue' }) cell_money = workbook.add_format({ 'bold': True, 'italic': True, 'font_color': 'blue', 'num_format': '#,##0.00' }) money = workbook.add_format({'num_format': '#,##0.00'}) row = 0 worksheet.write(row, 0, 'Code', bold) # 3 --> row number, column number, value worksheet.write(row, 1, 'Price', bold) worksheet.write(row, 2, 'Hotel', bold) worksheet.write(row, 3, 'Address', bold) worksheet.write(row, 4, 'Retail', bold) worksheet.write(row, 5, 'Profit', bold) row += 1 worksheet.write(row, 0, 'BEST', cell_money) worksheet.write(row, 1, self.cheap[1], cell_format) worksheet.write(row, 2, self.cheap[0], cell_format) worksheet.write(row, 3, self.cheap[2], cell_format) worksheet.write_formula(1, 4, '=1.374*B2', cell_money) worksheet.write_formula(1, 5, '=E2-B2', cell_money) row += 1 for i, option in enumerate(self.options): if i < 9: worksheet.write(row, 0, 'AA0' + str(i + 1)) else: worksheet.write(row, 0, 'AA' + str(i + 1)) worksheet.write(row, 1, option[1], money) worksheet.write(row, 2, option[0]) worksheet.write(row, 3, option[2]) worksheet.write_array_formula('E3:E31', '{=1.374*B3:B31}', money) worksheet.write_array_formula('F3:F31', '{=E3:E31-B3:B31}', money) row += 1 workbook.close() # fixme WARNING: # in order for xlsxwriter to create the spreadsheet, the workbook must be closed # right at the end. If it closes after it won't create it, check it by closing it after the line: # self.send_attachment (spreadsheet) spreadsheet = '//home/jmartorell/Booking/bookings/bookings.xlsx' self.send_attachment(spreadsheet)
def schedule_creator(start, end): delta = end - start time = str(datetime.datetime.now().hour) + str(datetime.datetime.now().minute) + str(datetime.datetime.now().second) year_list = [] month_list = [] date_list = [] year_key = [] month_key = [] date_dict = {} week_col = [] schedule_book = Workbook(str(start.year)+str(start.month)+str(start.day)+"_"+time+'.xlsx') worksheet = schedule_book.add_worksheet('Schedule') cell_format = schedule_book.add_format({'bold':1,'align':'center','valign':'vcenter','border':1}) blank_format = schedule_book.add_format() blank_format.set_left(7) blank_format.set_right(7) week_format = schedule_book.add_format() week_format.set_right(12) border_format = schedule_book.add_format() border_format.set_right(5) for i in range(0,delta.days): date = start + datetime.timedelta(days = i) if calendar.weekday(date.year,date.month,date.day) == 5 or calendar.weekday(date.year,date.month,date.day) == 6: continue year_list.append(date.year) month_list.append(date.month) date_list.append(date) count = 0 for i in year_list: if i not in date_dict.keys(): date_dict[i] = {} if month_list[count] not in date_dict[i].keys(): date_dict[i][month_list[count]] = [] date_dict[i][month_list[count]].append(date_list[count]) count += 1 col = 1 for year_key in date_dict.keys(): worksheet.merge_range(0,col,0,col + year_list.count(year_key)-1,year_key,cell_format) for month_key in date_dict[year_key].keys(): worksheet.merge_range(1,col,1,col + len(date_dict[year_key][month_key])-1,calendar.month_name[month_key],cell_format) for i in date_dict[year_key][month_key]: worksheet.write(2,col,calendar.day_abbr[calendar.weekday(i.year,i.month,i.day)],cell_format) worksheet.write(3,col,i.day,cell_format) if calendar.weekday(i.year,i.month,i.day) == 4: week_col.append(col) col += 1 worksheet.set_column(1,col,None,blank_format) for i in week_col: worksheet.set_column(i,i,None,week_format) worksheet.set_column(0,0,None,border_format) worksheet.set_column(col-1,col-1,None,border_format) worksheet.set_h_pagebreaks([79]) worksheet.set_v_pagebreaks([35]) schedule_book.close()
def write_df_to_excel(df, header=None, filename_end=None, bold_row_0=False, bold_col_0=False): """ Writes a dataframe with a datetime index to a file. Takes in an optional header and extra label for the filename. The first rows and columns can be bolded in the saved excel file with bold_[row/col]_0. :param pd.DataFrame df: Pandas dataframe to write to file **must have a datetime index :param list header: list of headers to write to the file if desired. Usually, it's ['date'] + df.columns.tolist() :param str filename_end: a string to append to the filename if desired, otherwise the current date is used only :param bool bold_row_0: True to bold the first row, defaults to false/non-bold :param bool bold_col_0: True to bold the first column, defaults to false/non-bold :return: None; writes to file """ from xlsxwriter import Workbook df.index = df.index.map(lambda x: datetime.strftime( x, '%Y-%m-%d %H:%M:%S')) # change index to ISO8601 time datestamp = datetime.now().strftime("%Y_%m_%d") filename = f"{datestamp}_{filename_end}.xlsx" if filename_end else f"{datestamp}_data.xlsx" workbook = Workbook(filename) workbook.nan_inf_to_errors = True worksheet = workbook.add_worksheet('Data') row_0_fmt = workbook.add_format( {'bold': bold_row_0}) # create row formats to apply based on inputs col_0_fmt = workbook.add_format({'bold': bold_col_0}) if header: for col, head in enumerate(header): worksheet.write(0, col, head, row_0_fmt) row_count = 1 if header else 0 # start on row 1 if a header was provided for row in df.iterrows(): col_count = 1 worksheet.write(row_count, 0, row[0], col_0_fmt) # write index to first col for col in row[ 1]: # row[1] is all values that aren't the index - pandas df.iterrows() specific worksheet.write(row_count, col_count, col) col_count += 1 row_count += 1 workbook.close()
class ExcelManager(): def __init__(self, path): # Create Workbook self.workbook = Workbook(path) # Create Sheet self.sheet = self.workbook.add_worksheet() def close(self): try: self.workbook.close() except: print("Please close the excel file!") def write(self, cell, value, options=None): self.sheet.write(cell, value, options) def get_xlsx_file(self, prices_dic): alphabet = list("ABCDEFGHIJKLMNOPQRSTUVWXYZ") names = [price["name"] for price in prices_dic] links = [price["link"] for price in prices_dic] prices = [price["price"] for price in prices_dic] updated = [price["updated"] for price in prices_dic] # Configure Formats bold = self.workbook.add_format({ "bold": True, }) money = self.workbook.add_format({'num_format': '#,##0.00 €'}) # Define columns self.write("A1", "Name:", bold) self.write("B1", "Link:", bold) self.write("C1", "Preis:", bold) self.write("D1", "Updated:", bold) # Write names for index, name in enumerate(names, 2): self.write("A" + str(index), name) # Write links for index, link in enumerate(links, 2): self.write("B" + str(index), link) # Write prices for index, price in enumerate(prices, 2): self.write("C" + str(index), price, money) # Write update time for index, updated in enumerate(updated, 2): self.write("D" + str(index), updated) # Close / Save file self.close()
def write_xlsx(serializer, headers, title_text): output = StringIO.StringIO() workbook = Workbook(output) worksheet_s = workbook.add_worksheet('Summary') # Here we will adding the code to add data title = workbook.add_format({ 'bold': True, 'font_size': 14, 'align': 'center', 'valign': 'vcenter' }) header = workbook.add_format({ 'bg_color': '#92d050', 'color': 'black', 'align': 'center', 'valign': 'top', 'border': 1 }) cell = workbook.add_format({ 'align': 'left', 'valign': 'top', 'text_wrap': False, 'border': 1 }) hearder_len = len(headers) header_range = xl_range(0, 0, 0, hearder_len - 1) worksheet_s.merge_range(header_range, title_text, title) # column header for col, field in enumerate(headers): worksheet_s.write(1, col, field[1], header) worksheet_s.set_column(col, col, width=field[2]) # column content for row, line in enumerate(serializer.data, start=2): # Write a line for each record for col, field in enumerate(headers): keys = field[0].split('.') value = line for key in keys[:-1]: value = value[key] if "|" in keys[-1]: key, flt = keys[-1].split("|") value = field_display(value[key], flt) else: value = value[keys[-1]] worksheet_s.write(row, col, value, cell) workbook.close() xlsx_data = output.getvalue() # xlsx_data contains the Excel file return xlsx_data
def make_xlsx_response(sheets, basename): output = StringIO() workbook = Workbook(output) header_format = workbook.add_format({'bold': True, 'border': 1}) for title, rows in sheets.items(): if not len(rows): continue worksheet = workbook.add_worksheet(title) row_idx = 0 headers = None for row in rows: if headers is None: headers = row.keys() for c, cell in enumerate(headers): worksheet.write(row_idx, c, cell, header_format) row_idx += 1 col_idx = 0 for cell in headers: worksheet.write(row_idx, col_idx, row.get(cell)) col_idx += 1 row_idx += 1 worksheet.freeze_panes(1, 0) workbook.close() output.seek(0) fn = basename + '.xlsx' headers = {'Content-Disposition': 'attachment; filename=%s' % fn} return Response(output, mimetype=XLSX_MIME, headers=headers)
def weekly_report(): date = request.args.get('date') if date: dt = datetime.strptime(date, '%Y-%m-%d').date() else: dt = datetime.now().date() start = dt- timedelta(days=dt.weekday()) # start of the week end = start + timedelta(days=6) # end of the week timesheets = Timesheet.query.filter(Timesheet.user_id == current_user.id, db.func.date(Timesheet.date)>=start, db.func.date(Timesheet.date)<=end).order_by(Timesheet.date).all() output = BytesIO() workbook = Workbook(output, {'in_memory': True}) worksheet = workbook.add_worksheet('weekly timesheet') row = 1 total = 0 bold = workbook.add_format({'bold': True}) for timesheet in timesheets: worksheet.write(row, 0, datetime.strftime(timesheet.date, "%m/%d/%Y")) subject_task = timesheet.subject.name if timesheet.task: subject_task += f", {timesheet.task.name}" worksheet.write(row, 1, subject_task) worksheet.write(row, 2, timesheet.workhours) worksheet.write(row, 3, timesheet.description) total = add_workhours(total, timesheet.workhours) row += 1 worksheet.write(0, 0, "Sum", bold) worksheet.write(0, 2, total, bold) for i in range(4): set_column_autowidth(worksheet, i) workbook.close() output.seek(0) return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":f"attachment;filename=weekly_report_{current_user.username}_{date}.xlsx"})
def ParsetoExcel(oldfilename, newfilename, Format, oldLines, newLines, LineDifference, percentage): outworkbook = Workbook('CodeTracker_Mohiddin.xlsx', {'strings_to_numbers': True}) DataSheet = outworkbook.add_worksheet() cell_format = outworkbook.add_format({ 'bold': True, 'bg_color': '#e6f2ff', 'border': 1, 'border_color': '#000000' }) #Datacell_format = outworkbook.add_format({'border':1,'border_color':'#000000'}) DataSheet.write(0, 0, 'OLDFILENAME', cell_format) DataSheet.write(0, 1, 'NEWFILENAME', cell_format) DataSheet.write(0, 2, 'EXTENSION_FORMAT', cell_format) DataSheet.write(0, 3, 'NO_OF_OLDLINES', cell_format) DataSheet.write(0, 4, 'NO_OF_NEWLINES', cell_format) DataSheet.write(0, 5, 'LINE_DIFFERENCE', cell_format) DataSheet.write(0, 6, 'CHANGE_PERCENTAGE', cell_format) DataSheet.write(0, 7, 'RE_USE_PERCENTAGE', cell_format) DataSheet.write(0, 8, 'MODULE_NAME', cell_format) for row_index, row_value in enumerate( zip(oldfilename, newfilename, Format, oldLines, newLines, LineDifference, percentage, ReusePercent, Modulename)): for col_index, col_value in enumerate(row_value): DataSheet.write(row_index + 1, col_index, col_value) outworkbook.close()
def __create_common_excel(self, first_column_list, options={}): """ 生成excel 模板 :param first_column_list: 单位列名 :param options: :return: """ output = BytesIO() workbook = Workbook(output, {'in_memory': True}) # sheet 1名称 worksheet_subject = workbook.add_worksheet( options['sheet_name1'] if 'sheet_name1' in options else None) line_index = 0 # 起始line first_column_num = len(first_column_list) # 列总数 worksheet_subject.set_column(0, first_column_num - 1, 14) column_format = workbook.add_format({ 'bold': '1', 'valign': 'vcenter', 'align': 'center', 'font_name': 'Microsoft YaHei' }) worksheet_subject.set_row(line_index, 20, column_format) for index, column in enumerate(first_column_list): worksheet_subject.write_string(line_index, index, column, column_format) workbook.close() return output.getvalue()
class XlsWriter(Writer): def write(self): super().write() self.workbook = Workbook(self.filename + ".xlsx") self.cell_formats = {} for key, value in self.colours.items(): self.cell_formats[key] = self.workbook.add_format() self.cell_formats[key].set_bg_color(value) for sheet in self.sheets: self.write_worksheet(sheet) self.workbook.close() def write_worksheet(self, name): worksheet = self.workbook.add_worksheet(name) r = 0 c = 0 for header in self.sheet_data[name]["headers"]: cell = worksheet.write(r, c, header, self.cell_formats["s"]) c += 1 c = 0 r += 1 for row in self.sheet_data[name]["rows"]: c = 0 for col in row: if c >= len(self.sheet_data[name]["colours"]): cell_format = "p" else: cell_format = self.sheet_data[name]["colours"][c] cell = worksheet.write(r, c, col, self.cell_formats[cell_format]) c += 1 r += 1
def write_cell_errors(xlsxname, header, lines, cells_to_mark): workbook = Workbook(join(SPREADSHEETS_DIR, xlsxname)) cell_fmt = workbook.add_format() cell_fmt.set_bg_color('cyan') worksheet = workbook.add_worksheet() write_sheet(worksheet, cell_fmt, header, lines, cells_to_mark) workbook.close()
def sqlExcel2(): global window, canvas, paper, filename, inImage, outImage, inW, inH, outW, outH outfilename = asksaveasfile(parent=window, mode='wb', defaultextension="*.xlsx", filetypes=(("xlsx파일", "*.xlsx"), ("모든파일", "*.*"))) wb = Workbook(outfilename) ws = wb.add_worksheet(os.path.basename(filename)) with open(filename, 'rb') as fReader: # 워크시트의 열 너비 / 행 높이 지정 ws.set_column(0, inW, 1.0) # 0.34 for row in range(inH): ws.set_row(row, 9.5) # 0.35 for i in range(inW): for j in range(inH): data = inImage[i][j] # 기존에 있던 inImage에서 출력 # data 셀 배경색 지정 #000000~FFFFFF if data[0] <= 15: # 15 이하일 경우, 1자리 수이기 때문에 0을 추가 hexStr = '#' + ('0' + hex(data[0])[2:]) else: hexStr = '#' + (hex(data[0])[2:]) # 16진수 변환 후, R(2자리) if data[1] <= 15: hexStr += ('0' + hex(data[1])[2:]) # G(2자리) else: hexStr += hex(data[1])[2:] if data[2] <= 15: hexStr += ('0' + hex(data[2])[2:]) # B(2자리) else: hexStr += hex(data[2])[2:] cell_format = wb.add_format() # RGB코드는 #을 앞에 cell_format.set_bg_color(hexStr) ws.write(i, j, '', cell_format) wb.close()
def export_xlsx(request): output = io.BytesIO() workbook = Workbook(output, {'in_memory': True}) stores_worksheet = workbook.add_worksheet('stores') products_worksheet = workbook.add_worksheet('products') stores_worksheet.write('A1', 'Name') stores_worksheet.write('B1', 'Address') for i, store in enumerate(Store.objects.all()): stores_worksheet.write(i + 1, 0, store.name) stores_worksheet.write(i + 1, 1, store.address) products_worksheet.write('A1', 'Name') products_worksheet.write('B1', 'Weight') products_worksheet.write('C1', 'Brand') products_worksheet.write('D1', 'Price') products_worksheet.write('E1', 'Expiration Date') dateFormat = workbook.add_format({'num_format': 'dd/mm/yy'}) for i, product in enumerate(Product.objects.all()): products_worksheet.write(i + 1, 0, product.name) products_worksheet.write(i + 1, 1, product.weight) products_worksheet.write(i + 1, 2, product.brand) products_worksheet.write(i + 1, 3, product.price) products_worksheet.write(i + 1, 4, product.expirationDate, dateFormat) workbook.close() output.seek(0) response = HttpResponse( output.read(), content_type= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = "attachment; filename=testapp.xlsx" return response
def write_excel(f_name, data): print("Writing to Excel file..") row = 0 wb = Workbook(f_name) sheet1 = wb.add_worksheet('Sheet 1') bold = wb.add_format({'bold': True, 'bg_color': 'silver'}) # Add header for x in data.keys(): column = 0 for y in data[x].keys(): sheet1.write(row, column, y, bold) column += 1 break row += 1 # Add data for x in data.keys(): column = 0 for y in data[x].keys(): #sheet1.write(row, column, x) sheet1.write(row, column, data[x][y]) column += 1 print(x, "-", y, "-", data[x][y], row, column) row += 1 wb.close()
def write_data_excel(data1, data2, path): # print ('data1:', data1) # print ('data2:', data2) name = path.split('/')[-1] create_time = time.strftime('%Y-%m-%d-%H-%M', time.localtime(time.time())) pure_name = '%s_%s.xlsx' % (name, create_time) file_name = os.path.join(path, pure_name) # print("file_name:", file_name) new_f = Workbook(file_name) sheet1 = new_f.add_worksheet(create_time) sheet1_format = new_f.add_format({}) sheet1_format.set_text_wrap() sheet1.set_column(0, 0, 25) sheet1.set_column(1, 4, 60) for i in range(len(data1)): if i: sheet1.set_row(i, 125) print "start to insert image {}".format(data2[i]) sheet1.insert_image(i, 0, data2[i]) for j in range(len(data1[i])): if i == 0: sheet1.set_row(0, 18) headers = ['PRODUCE NAME', 'PRODUCE PRICE', 'THE LINK'] sheet1.write(0, j + 1, headers[j]) else: try: sheet1.write(i, (j + 1), data1[i][j], sheet1_format) except: pass new_f.close() return file_name
def imprimirCabecera(workbook: xlsxwriter.Workbook, ws, config: tripConfig, tipo: int): # Damos formato final formato_titulo = workbook.add_format({ "bold": 1, "border": 1, "align": "center", "valign": "vcenter", "font_size": 14, }) formato_cabecera = workbook.add_format({ "bold": 1, "border": 1, "align": "center", "valign": "vcenter", "font_size": 13, }) ancho_cabecera = 0 if tipo == 1: ancho_cabecera = 10 if tipo == 2: ancho_cabecera = 8 ws.set_paper(9) ws.merge_range(0, 0, 2, 1, "", formato_titulo) ws.merge_range(0, 2, 0, ancho_cabecera, "LISTA DE ZARPE - " + ws.get_name(), formato_titulo) ws.merge_range( 1, 2, 1, ancho_cabecera, f"Embarcación: {config.nave} | Placa: {config.placa} | {config.ruta}", formato_cabecera, ) ws.merge_range( 2, 2, 2, ancho_cabecera, f"Trayecto: {config.trayecto} | Fecha: {config.fecha}", formato_cabecera, )
def process(self, items: List[Any]) -> None: workbook = Workbook('deaditions.xlsx') worksheet = workbook.add_worksheet() worksheet.set_column(0, 9, 15) bold = workbook.add_format({'bold': 1}) date = workbook.add_format({'num_format': 'dd.mm.yyyy HH:ss'}) ranking = workbook.add_format({'num_format': '#.##'}) worksheet.write('A1', 'Name', bold) worksheet.write('B1', 'Link', bold) worksheet.write('C1', 'Available', bold) worksheet.write('D1', 'Description', bold) worksheet.write('E1', 'Editors', bold) worksheet.write('F1', 'Ranking', bold) worksheet.write('G1', 'Published (manual)', bold) worksheet.write('H1', 'Lastseen (manual)', bold) worksheet.write('I1', 'Published (wayback)', bold) worksheet.write('J1', 'Lastseen (wayback)', bold) row = 0 for item in items: row += 1 worksheet.write_string(row, 0, item.name) worksheet.write_url(row, 1, item.href, string='Link') worksheet.write_string(row, 2, str(item.meta.available)) worksheet.write_string(row, 3, item.meta.description) worksheet.write_string(row, 4, item.meta.editors or '') worksheet.write_number(row, 5, item.meta.ranking, ranking) if item.meta.published.man: worksheet.write_number(row, 6, item.meta.published.man) if item.meta.lastseen.man: worksheet.write_number(row, 7, item.meta.lastseen.man) if item.meta.published.ia: worksheet.write_datetime(row, 8, item.meta.published.ia, date) if item.meta.lastseen.ia: worksheet.write_datetime(row, 9, item.meta.lastseen.ia, date) worksheet.write(row + 1, 0, 'Deaditions', bold) worksheet.write(row + 1, 9, '=ROW()-1', bold) workbook.close()
def create_sheet_with_resightings(workbook_name: str, resightings: list) -> Workbook: """Places resightings in workbook with named columns, treating images specially""" if not resightings: return workbook = Workbook(workbook_name) sheet = workbook.add_worksheet() # Format object to center data and wrap text cell_format = workbook.add_format() cell_format.set_shrink() cell_format.set_text_wrap() cell_format.set_align("center") cell_format.set_align("vcenter") # Add column headings for field_name, col_index in FIELDS.items(): sheet.write(0, col_index, field_name) # Set starting column width (modifies from range col_index -> col_index) if "Image" in field_name: # Images need a wider column sheet.set_column(col_index, col_index, 45, cell_format) else: sheet.set_column(col_index, col_index, 12, cell_format) # For each resighting, slot the data in each field into its corresponding cell for row_num, resight in enumerate(resightings, 1): sheet.set_row(row_num, 160) # Set row height for field, data in resight.items(): if field in FIELDS: # image data needs special handling # TODO: save image in a directory as well if "Image" in field: # Read from json as string binary = io.BytesIO(base64.b64decode(data)) img = Image.open(binary) # Resize to fit Excel img.thumbnail((300, 300), Image.ANTIALIAS) resized = io.BytesIO() img.save(resized, format=img.format) # Insert into correct cell sheet.insert_image( row_num, FIELDS[field], "image", {"image_data": resized, "positioning": 1} ) # Write other data, converting if necessary else: if field == "Timestamp": # Convert epoch time to month/day/year data = datetime.datetime.fromtimestamp(data).strftime("%m/%d/%Y %H:%M:%S") sheet.write(row_num, FIELDS[field], data) else: print("Field not recognized: {}".format(field)) return workbook
def write_comments_to_xlsx(xlsx_file_name, comments): workbook = Workbook(xlsx_file_name) worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': True}) worksheet.set_column('A:A', 55) worksheet.set_column('B:B', 25) worksheet.set_column('C:C', 100) cell_format = workbook.add_format() col = 0 for comment_number, comment in enumerate(comments): row = comment_number worksheet.write_url( row, col, f"https://www.instagram.com/{comment['username']}", bold) worksheet.write(row, col + 1, comment['created_at']) worksheet.write(row, col + 2, comment['text'], cell_format) workbook.close() logging.info(f'Saved {len(comments)} comments to {xlsx_file_name}')
def genera_e_salva(self, nome='File.xlsx', scadenza=None, ordina_fogli=True, save_to_memory=False): """ Genera il file e lo salva su database. :param nome: Il nome del file da allegare (opzionale, default 'File.xlsx'). :param scadenza: Scadenza del file. Domani. :return: """ generatore = GeneratoreNomeFile('allegati/') zname = generatore(self, nome) self.prepara_cartelle(MEDIA_ROOT + zname) if save_to_memory: workbook = Workbook(self.output) else: workbook = Workbook(MEDIA_ROOT + zname) bold = workbook.add_format({'bold': True}) # Per ogni foglio for foglio in [x for x in self.fogli]: # Aggiunge il foglio worksheet = workbook.add_worksheet(foglio.nome) # Aggiunge l'intestazione for col, testo in enumerate(foglio.intestazione): worksheet.write(0, col, str(testo), bold) # Aggiunge il contenuto for riga, colonne in enumerate(foglio.contenuto): riga += 1 # Indice shiftato per intestazione for colonna, testo in enumerate(colonne): if isinstance(testo, datetime): testo = testo.strftime("%d/%m/%Y %H:%M") if isinstance(testo, date): testo = testo.strftime("%d/%m/%Y") if testo == ", ": # Rimuove campi ', ' testo = "" worksheet.write(riga, colonna, str(testo)) if ordina_fogli: workbook.worksheets_objs.sort(key=lambda x: x.name) workbook.close() if save_to_memory: self.output.seek(0) else: self.file = zname self.nome = nome self.scadenza = scadenza or domani() self.save()
def write_mismatch(filename, etalon, tocomp, mismatch): workbook = Workbook(join(SPREADSHEETS_DIR, get_filebase(filename)+'.xlsx')) cell_fmt = workbook.add_format() cell_fmt.set_bg_color('cyan') worksheet = workbook.add_worksheet(name='test') write_sheet(worksheet, cell_fmt, *tocomp, cells_to_mark=mismatch) worksheet = workbook.add_worksheet(name='etalon') write_sheet(worksheet, cell_fmt, *etalon) workbook.close()
def generar_layout(datos, salida): wb = Workbook(salida) ws = wb.add_worksheet('Layout') encabezados = ''' Nombre Inicio Longitud Tipo de dato capturado '''.split('\n')[1:-1] # Escribir encabezados col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper() col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper() format_enc = wb.add_format({ 'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg }) format_ren1 = wb.add_format({'border': 1}) format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2}) for col, encabezado in enumerate(encabezados): ws.write(0, col, encabezado, format_enc) # Escribir datos del diccionario for renglon, registro in enumerate(datos, 1): formato = format_ren1 if renglon % 2 == 0 else format_ren2 # Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap') ws.write(renglon, 0, registro.etiqueta, formato) ws.write(renglon, 1, int(registro.inicio), formato) ws.write(renglon, 2, int(registro.longitud), formato) ws.write(renglon, 3, registro.tipo_cap, formato) # Aplicando formato a la hoja ws.freeze_panes(1, 0) ws.autofilter('A1:D1') ws.set_column(0, 0, 55) ws.set_column(1, 1, 8) ws.set_column(2, 2, 10) ws.set_column(3, 3, 25) ws.hide_gridlines(2) wb.close() click.launch(salida) print("Layout generado")
def get_writer_format(workbook: xlsxwriter.Workbook, style: ColumnStyle) -> Union[Tuple[Format, int], None]: if style: wb_format = workbook.add_format({ 'text_wrap': 1, 'valign': 'top' }) if style['wrap_text'] else None return wb_format, style['width'] else: return None
def dispatch(dataset): buf = StringIO() wb = Workbook(buf, {'constant_memory': True, 'in_memory': True, 'default_date_format': 'yyyy/mm/dd hh:mm:ss'} ) format_ = wb.add_format(FORMAT) # dataset metadata ws = wb.add_worksheet('Global attributes') write_metadata(ws, dataset, 0, 0, format_) # 1D grids for grid in [g for g in walk(dataset, GridType) if len(g.shape) == 1]: logger.debug('Grid {}'.format(grid.name)) ws = wb.add_worksheet(grid.name) # headers ws.write(0, 0, grid.dimensions[0], format_) ws.write(0, 1, grid.name, format_) # data for j, data in enumerate(grid.data): for i, value in enumerate(numpy.asarray(data)): ws.write(i+1, 1-j, value) # add var metadata write_metadata(ws, grid, 0, 2, format_) # sequences for seq in walk(dataset, SequenceType): logger.debug('Sequence {}'.format(seq.name)) ws = wb.add_worksheet(seq.name) # add header across the first row for j, var_ in enumerate(seq.keys()): ws.write(0, j, var_, format_) # add data in the subsequent rows for i, row in enumerate(seq.data): for j, value in enumerate(row): ws.write(i+1, j, value) # add var metadata in columns to the right of the data n = 0 j = len(seq.keys())+1 for child in seq.children(): logger.debug("Child {}".format(child.name)) ws.merge_range(n, j, n, j+1, child.name, format_) n = write_metadata(ws, child, n+1, j, format_)+1 wb.close() return buf
def adjust_rows_height(cls, df: pd.DataFrame, wb: Workbook, worksheet: Worksheet): bold_fmt = wb.add_format({'bold': True}) worksheet.set_row(0, cell_format=bold_fmt) for idx, row in df.iterrows(): height = max([1 + sum([1 for ch in str(c or '') if ch == '\n']) for c in row]) height = min(height, cls.MAX_ROW_HEIGHT) worksheet.set_row(idx + 1, height * worksheet.default_row_height)
def generar_layout(datos, salida): wb = Workbook(salida) ws = wb.add_worksheet('Layout') encabezados = ''' Nombre Inicio Longitud Tipo de dato capturado '''.split('\n')[1:-1] # Escribir encabezados col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper() col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper() format_enc = wb.add_format({'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg}) format_ren1 = wb.add_format({'border': 1}) format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2}) for col, encabezado in enumerate(encabezados): ws.write(0, col, encabezado, format_enc) # Escribir datos del diccionario for renglon, registro in enumerate(datos, 1): formato = format_ren1 if renglon % 2 == 0 else format_ren2 # Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap') ws.write(renglon, 0, registro.etiqueta, formato) ws.write(renglon, 1, int(registro.inicio), formato) ws.write(renglon, 2, int(registro.longitud), formato) ws.write(renglon, 3, registro.tipo_cap, formato) # Aplicando formato a la hoja ws.freeze_panes(1, 0) ws.autofilter('A1:D1') ws.set_column(0, 0, 55) ws.set_column(1, 1, 8) ws.set_column(2, 2, 10) ws.set_column(3, 3, 25) ws.hide_gridlines(2) wb.close() click.launch(salida) print("Layout generado")
def medicamentos_topOrganizacionesPorPedidoExcel(request): datos = request.session['estadistica']['excel']['datos'] medicamento = request.session['estadistica']['excel']['medicamento'] excel = io.BytesIO() workbook = Workbook(excel, {'in_memory': True}) worksheet = workbook.add_worksheet() titulo = workbook.add_format({ 'font_name':'Arial', 'font_size': 12, 'font_color': 'navy', 'bold': True }) encabezado = workbook.add_format({ 'font_name': 'Arial', 'bold': True }) alignLeft = workbook.add_format({ 'align': 'left', }) worksheet.write('A1:B1', 'Organizaciones mas demandantes del medicamento '+medicamento+' (por pedido)', titulo) worksheet.set_column('B:B', 40) worksheet.set_column('C:C', 20) worksheet.write('A2', '#', encabezado) worksheet.write('B2', 'Organizacion', encabezado) worksheet.write('C2', 'Cantidad', encabezado) fila = 2 tope = len(datos) for i in range(0, tope): worksheet.write(fila, 0, i + 1, alignLeft) worksheet.write(fila, 1, datos[i]['organizacion'], alignLeft) worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft) fila += 1 workbook.close() excel.seek(0) response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = "attachment; filename=OrganizacionesMasDemandantesDeMedicamentoPorPedido.xlsx" return response
def pedidosAlaboratorio_topLabConMasSolicitudesPedidosExcel(request): datos = request.session['estadistica']['excel'] excel = io.BytesIO() workbook = Workbook(excel, {'in_memory': True}) worksheet = workbook.add_worksheet() titulo = workbook.add_format({ 'font_name':'Arial', 'font_size': 12, 'font_color': 'navy', 'bold': True }) encabezado = workbook.add_format({ 'font_name':'Arial', 'bold': True }) alignLeft = workbook.add_format({ 'align':'left', }) worksheet.write('A1:B1', 'Laboratorios con mas solicitudes (por medicamento)', titulo) worksheet.set_column('B:B', 40) worksheet.set_column('C:C', 20) worksheet.write('A2', '#', encabezado) worksheet.write('B2', 'Laboratorio', encabezado) worksheet.write('C2', 'Cantidad', encabezado) fila = 2 tope = len(datos) for i in range(0, tope): worksheet.write(fila, 0, i + 1, alignLeft) worksheet.write(fila, 1, datos[i]['laboratorio'], alignLeft) worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft) fila += 1 workbook.close() excel.seek(0) response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = "attachment; filename=LaboratoriosConMasSolicitudesDeMedicamentos.xlsx" return response
def GetDefaultCellFormat(work_book: Workbook) -> Format: if not Workbook: return None format_dict = { 'bold': 1, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': 'yellow' } return work_book.add_format(format_dict)
def toExcel(self, outFileName): """ Write the distribution fitter results to an Excel spreadsheet :param outFileName: output spreadsheet name :type outFileName: str :return: Nothing :rtype: None """ workbook = Workbook(outFileName, {'constant_memory': True}) workbook.use_zip64() # allow large size Excels just in case wks = workbook.add_worksheet('Distribution Fitting') hdrFmt = workbook.add_format({ 'bold': True, 'underline': True, 'align': 'center' }) resultFormats = [workbook.add_format({'num_format' : fmtStr}) \ for fmtStr in ['0.000000', '0.0000%']] row = 0 wks.set_column(0, 0, 11) wks.set_column(1, 1, 8, resultFormats[0]) wks.set_column(2, 2, 10.6, resultFormats[1]) for col, headerName in enumerate(self.getHeaderList()): wks.write_string(row, col, headerName, hdrFmt) for distrName, (results, params) in self.result.iteritems(): row += 1 col = 0 wks.write_string(row, col, distrName) for col, (result, outFormat) in \ enumerate(itertools.izip(results, resultFormats), col+1): wks.write_number(row, col, result, outFormat) for col, paramValue in enumerate(params, col + 1): wks.write_number(row, col, paramValue) workbook.close()
def wr_xlsx(fout_xlsx, xlsx_data, **kws): """Write a spreadsheet into a xlsx file.""" if xlsx_data: from xlsxwriter import Workbook workbook = Workbook(fout_xlsx) worksheet = workbook.add_worksheet() # Cell formatting fmt_hdr = workbook.add_format({'top':1, 'bottom':1, 'left':0, 'right':0, 'bold':True}) fmt_txt = workbook.add_format({'border':0}) flds = xlsx_data[0]._fields if 'fld2col_widths' in kws: _set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds) # Print header hdrs = flds if 'hdrs' not in kws else kws['hdrs'] for col_idx, hdr in enumerate(hdrs): worksheet.write(0, col_idx, hdr, fmt_hdr) row_data_start = 1 # Print data row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt, **kws) workbook.close() sys.stdout.write(" {:>5} items WROTE: {}\n".format(row_idx-row_data_start, fout_xlsx)) else: sys.stdout.write(" 0 items. NOT WRITING {}\n".format(fout_xlsx))
def toExcel(self, outFileName): """ Write the distribution fitter results to an Excel spreadsheet :param outFileName: output spreadsheet name :type outFileName: str :return: Nothing :rtype: None """ workbook = Workbook(outFileName, {'constant_memory': True}) workbook.use_zip64() # allow large size Excels just in case wks = workbook.add_worksheet('Distribution Fitting') hdrFmt = workbook.add_format({'bold' : True, 'underline' : True, 'align' : 'center'}) resultFormats = [workbook.add_format({'num_format' : fmtStr}) \ for fmtStr in ['0.000000', '0.0000%']] row = 0 wks.set_column(0, 0, 11) wks.set_column(1, 1, 8, resultFormats[0]) wks.set_column(2, 2, 10.6, resultFormats[1]) for col, headerName in enumerate(self.getHeaderList()): wks.write_string(row, col, headerName, hdrFmt) for distrName, (results, params) in self.result.iteritems(): row += 1 col = 0 wks.write_string(row, col, distrName) for col, (result, outFormat) in \ enumerate(itertools.izip(results, resultFormats), col+1): wks.write_number(row, col, result, outFormat) for col, paramValue in enumerate(params, col+1): wks.write_number(row, col, paramValue) workbook.close()
def get_query_result_excel(self, sql, excelFile, header='True'): ''' Build an excel file from result set of the sql command \n Example: get query result excel select * from customer test.xlsx ''' cur = self._dbconnection.cursor() cur.execute(sql + ';') rows = cur.fetchall() print(cur.description) workbook = Workbook(excelFile) sheet = workbook.add_worksheet() format = workbook.add_format({'bold': True}) if header.lower() == 'true': for i, val in enumerate([column[0] for column in cur.description]): sheet.write(0, i, self._f(val), format) #Write header column for r, row in enumerate(rows): for c, s in enumerate(row): sheet.write(r+1, c, self._f(s)) # Write table data workbook.close()
def xlsx_generate(file_name, sheets): log.info(' -> %s', file_name) with open(file_name, 'w') as fh: workbook = Workbook(fh) header_format = workbook.add_format({ 'bold': True, 'border': 1 }) for title, rows in sheets: if not len(rows): continue headers = get_headers(rows) if not len(headers): continue worksheet = workbook.add_worksheet(title) worksheet.strings_to_numbers = False worksheet.strings_to_urls = False worksheet.strings_to_formulas = False row_idx = 0 headers = None for row in rows: if headers is None: headers = row.keys() for c, cell in enumerate(headers): worksheet.write(row_idx, c, cell, header_format) row_idx += 1 col_idx = 0 for cell in headers: worksheet.write(row_idx, col_idx, row.get(cell)) col_idx += 1 row_idx += 1 worksheet.freeze_panes(1, 0) workbook.close()
def generate_change_template(request): stream = BytesIO() book = Workbook(stream) bold = book.add_format({'bold': True}) # create sheet for user input sheet = book.add_worksheet('Updates') # set headers sheet.write('A1', 'category (like destID)', bold) sheet.write('B1', 'title (like input cat)', bold) sheet.write('C1', 'url (like output cat)', bold) rows = 400 # no idead sheet.data_validation(1, 1, rows, 1, { 'validate': 'list', 'source': 'Valid_Categories' }) # set width sheet.set_column('A:A', 25) sheet.set_column('B:B', 25) sheet.set_column('C:C', 25) # Create sheet containing data for user validations # sheet = book.add_worksheet('Validations') # sheet.write('A1', 'Categories', bold) categories = Category.objects.all() for i, cat in enumerate(categories): sheet.write(i + 1, 0, cat.slug) # book.define_name('Valid_Categories', '=Validations!$A$2:$A${}'.format(1 + categories.count())) # sheet.set_column('A:A', 10) # sheet.set_column('B:B', 2) book.close() stream.seek(0) response = HttpResponse(stream, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachement; filename="page_add_template.xlsx"' return response
def make_xlsx_response(sheets, basename): output = StringIO() workbook = Workbook(output) header_format = workbook.add_format({ 'bold': True, 'border': 1 }) for title, rows in sheets.items(): if not len(rows): continue worksheet = workbook.add_worksheet(title) row_idx = 0 headers = None for row in rows: if headers is None: headers = row.keys() for c, cell in enumerate(headers): worksheet.write(row_idx, c, cell, header_format) row_idx += 1 col_idx = 0 for cell in headers: worksheet.write(row_idx, col_idx, row.get(cell)) col_idx += 1 row_idx += 1 worksheet.freeze_panes(1, 0) workbook.close() output.seek(0) fn = basename + '.xlsx' headers = {'Content-Disposition': 'attachment; filename=%s' % fn} return Response(output, mimetype=XLSX_MIME, headers=headers)
def testYieldCurveFwdsGeneration(): # ========================================================================== # A_Auto: USD_IRS_LIBOR_3M # Test based on Auto Zero rates # zeroRates=[0.00233531255143132, # 0.00233531255143297, # 0.00233531255143352, # 0.00233531255143333, # 0.00297455023730465, # 0.00319723236979883, # 0.00330765830399476, # 0.00691586090809845, # 0.01085698247648050, # 0.01394073189902450, # 0.01633917521905550, # 0.02000659612723410, # 0.02346660204636610, # 0.02685361531988290, # 0.02854781579990930, # 0.02975553109452430] # Test based on Manual Zero rates zeroRates = [ 0.00236843676021893, 0.00236821394576394, 0.00236797582662841, 0.00236773777132554, 0.00290042675432344, 0.00308558288679761, 0.00330592988104989, 0.00691281016439806, 0.01085462048320750, 0.01393891008113780, 0.01633775879077160, 0.02000467532244330, 0.02346505198474200, 0.02685243199737300, 0.02854687951971270, 0.02975479160795850 ] yieldCurve = FYC.yieldCurveFactory(times=ZERO_TIMES, zeros=zeroRates, type='linearFwd') runTime = dt.strptime('20141128.230000', dtFmtLog) runDate = runTime.date() # same as outDates # make sure the first out date is the end of the following month nextDate = Cal.NextEndOfMonth(runDate) startDate = nextDate if nextDate.month == runDate.month else runDate dfltFwdDates = SortedList(unfoldGen(func = Cal.NextEndOfMonth, initArg = startDate, numSteps = NUMBER_FWD_MONTHS)) cal = Cal() fwdDates = [[cal.AddSplitTenor(baseDate = baseDate, tenorSize = x[0], tenorUnit = x[1]) for x in [[0,'D']] + TENORS] for baseDate in dfltFwdDates] fwdTimes = [cal.DateTimesToFloats(dateTimes = fwdDateList, baseDate = runDate) for fwdDateList in fwdDates] outFwds = np.empty((len(fwdTimes[0])-1,NUMBER_FWD_MONTHS)) for idx, fwdTimesAtDate in enumerate(fwdTimes): outFwds[:,idx] \ = yieldCurve.getFwds( times = fwdTimesAtDate[1:], baseTime = fwdTimesAtDate[0]) rateTable = np.column_stack((zeroRates, outFwds)) # outFwds = np.maximum(0., outFwds) workbook = Workbook('tests/test_YieldCurve.xlsx',{'constant_memory': True}) headerFormat = workbook.add_format({'bold' : True, 'font_name': 'Arial', 'font_size': 8}) rateFormat = workbook.add_format({'num_format': '0.000%', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) stringFormat = workbook.add_format({'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) dateFormat = workbook.add_format({'bold' : False, 'num_format': 'mm/dd/yyyy', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) headerDateFormat = workbook.add_format({'bold' : True, 'num_format': 'mm/dd/yyyy', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) # ========================================================================== wks = workbook.add_worksheet("Forwards") row = 1 initCol = 2 col = initCol wks.write_string(row, col, 'Maturities/Dates', headerFormat) col +=1 wks.write_datetime(row, col, runDate, headerDateFormat) for outDate in fwdDates: col +=1 wks.write_datetime(row, col, outDate[0], headerDateFormat) # write values for tenor, rateRow in zip(TENORS, rateTable): row += 1 col = initCol # print row, col, tenor wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), headerFormat) col += 1 # print rateRow for rate in rateRow: # print row, col, rate wks.write_number(row, col, rate,rateFormat) col += 1 # ========================================================================== # Format desired output crvName = "USD_IRS_LIBOR_3M" scenarioName = "A_Base" # for baseDate in dfltFwdDates # for tenor in TENORS wks = workbook.add_worksheet("Comparision") initRow = 0 initCol = 0 headerString = ['Curve','Scenario','Base Date', 'Tenor', 'Rate'] wks.write_row(initRow, initCol, headerString, headerFormat) col = initCol row = initRow + 1 for tenor, rateRow in zip(TENORS, outFwds): for baseDate, rate in zip (dfltFwdDates, rateRow): col=initCol wks.write_string(row, col, crvName, stringFormat) col +=1 wks.write_string(row, col, scenarioName, stringFormat) col +=1 wks.write_datetime(row, col, baseDate, dateFormat) col +=1 wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), stringFormat) col +=1 wks.write_number(row, col, rate, rateFormat) row +=1 workbook.close()
class WrXlsx(object): """Class to store/manage Excel spreadsheet parameters.""" dflt_fmt_hdr = {'top':0, 'bottom':0, 'left':0, 'right':0, 'bold':True} def __init__(self, fout_xlsx, nt_flds, **kws): # KEYWORDS FOR WRITING DATA: self.vars = WrXlsxParams(nt_flds, **kws) # Workbook from xlsxwriter import Workbook self.workbook = Workbook(fout_xlsx) self.fmt_hdr = self.workbook.add_format(self.dflt_fmt_hdr) self.fmt_txt = { 'plain': self.workbook.add_format(self.vars.fmt_txt_arg[0]), 'plain bold': self.workbook.add_format(self.vars.fmt_txt_arg[3]), 'very light grey' : self.workbook.add_format(self.vars.fmt_txt_arg[1]), 'light grey' :self.workbook.add_format(self.vars.fmt_txt_arg[2])} def wr_title(self, worksheet, row_idx=0): """Write title (optional).""" if self.vars.title is not None: return self.wr_row_mergeall(worksheet, self.vars.title, self.fmt_hdr, row_idx) return row_idx def wr_row_mergeall(self, worksheet, txtstr, fmt, row_idx): """Merge all columns and place text string in widened cell.""" hdridxval = len(self.vars.hdrs) - 1 worksheet.merge_range(row_idx, 0, row_idx, hdridxval, txtstr, fmt) return row_idx + 1 def wr_hdrs(self, worksheet, row_idx): """Print row of column headers""" for col_idx, hdr in enumerate(self.vars.hdrs): worksheet.write(row_idx, col_idx, hdr, self.fmt_hdr) row_idx += 1 return row_idx def wr_data(self, xlsx_data, row_idx, worksheet): """Write data into xlsx worksheet.""" fld2fmt = self.vars.fld2fmt # User may specify to skip rows based on values in row prt_if = self.vars.prt_if # User may specify a subset of columns to print or # a column ordering different from the _fields seen in the namedtuple prt_flds = self.vars.prt_flds if self.vars.sort_by is not None: xlsx_data = sorted(xlsx_data, key=self.vars.sort_by) try: for data_nt in xlsx_data: fmt_txt = self._get_fmt_txt(data_nt) if prt_if is None or prt_if(data_nt): # Print an xlsx row by printing each column in order. for col_idx, fld in enumerate(prt_flds): try: # If fld "format_txt" present, use value for formatting, but don't print. val = getattr(data_nt, fld, "") # Optional user-formatting of specific fields, eg, pval: "{:8.2e}" # If field value is empty (""), don't use fld2fmt if fld2fmt is not None and fld in fld2fmt and val != "" and val != "*": val = fld2fmt[fld].format(val) worksheet.write(row_idx, col_idx, val, fmt_txt) except: raise RuntimeError(self._get_fatal_rcv(row_idx, col_idx, fld, val)) row_idx += 1 except RuntimeError as inst: import sys import traceback traceback.print_exc() sys.stdout.write("\n **FATAL in wr_data: {MSG}\n\n".format(MSG=str(inst))) sys.exit() return row_idx @staticmethod def _get_fatal_rcv(row, col, fld, val): """Return an informative message with details of xlsx write attempt.""" import traceback traceback.print_exc() return "ROW({R}) COL({C}) FIELD({F}) VAL({V})".format(R=row, C=col, F=fld, V=val) def add_worksheet(self): """Add a worksheet to the workbook.""" worksheet = self.workbook.add_worksheet() if self.vars.fld2col_widths is not None: self.set_xlsx_colwidths(worksheet, self.vars.fld2col_widths, self.vars.prt_flds) return worksheet def _get_fmt_txt(self, data_nt=None): """Return format for text cell.""" if data_nt is None or not self.vars.b_format_txt: return self.fmt_txt.get('plain') format_txt_val = getattr(data_nt, "format_txt") if format_txt_val == 1: return self.fmt_txt.get("very light grey") if format_txt_val == 2: return self.fmt_txt.get("light grey") fmt = self.fmt_txt.get(format_txt_val) if fmt is not None: return fmt return self.fmt_txt.get('plain') def get_fmt_section(self): """Grey if printing header GOs and plain if not printing header GOs.""" if self.vars.b_format_txt: return self.fmt_txt.get("light grey") return self.fmt_txt.get("plain bold") @staticmethod def set_xlsx_colwidths(worksheet, fld2col_widths, fldnames): """Set xlsx column widths using fld2col_widths.""" for col_idx, fld in enumerate(fldnames): col_width = fld2col_widths.get(fld, None) if col_width is not None: worksheet.set_column(col_idx, col_idx, col_width)
class XlsxReport(): ''' 导出Excel报表,主要的用法: data=(('data11','data12'), ('data21','data22')) columns=[{'header':'标题1', 'width':11,}, {'header':'标题2', 'width':13, 'format':'currency'}] with XlsxReport('xlsx_name')as rpt: rpt.add_table('A1','sheetname',columns=columns,data=data) ''' def __init__(self,*args,**kwargs): self.book=Workbook(*args,**kwargs) self.formats={} self.add_formats(DefaultFormats) self.sheet=None self.sheets={} def __enter__(self): return self def __exit__(self,_type,value,trace): self.close() def ensure_sheet(self,sheet): self.get_sheet(sheet) if not sheet in self.sheets: self.sheets[sheet]=Sheet(self) return self.sheets.get(sheet) def get_sheet(self,sheetname): '''获取工作表,并设为默认工作表''' if isinstance(sheetname,Worksheet): return sheetname for sheet in self.book.worksheets(): if sheet.name==sheetname:break else: sheet=self.book.add_worksheet(sheetname) self.sheet=sheet return sheet def set_row(self,row,height=None,format=None,options={}): if format: format=self.formats.get(format,format) self.sheet.set_row(row,height,format,options) @convert_column_args def set_column(self,firstcol,lastcol,width=None, format=None,options={}): if format: format=self.formats.get(format,format) self.sheet.set_column(firstcol,lastcol,width,format,options) @convert_range_args def mwrite(self,first_row,first_col,last_row,last_col,\ value,format=None): '''合并写入到默认的工作表中''' if format: format=self.formats.get(format,format) self.sheet.merge_range(first_row,first_col,last_row,last_col, value,format) @convert_range_args def write_formulas(self,*args,**kwargs): pass @convert_cell_args def write_formula(self,row,col,formula,format=None,value=0): if format: format=self.formats.get(format,format) self.sheet.write_formula(row,col,formula,format,value) @convert_cell_args def write(self,row,col,value,format=None): '''单一单元格写入''' if format: format=self.formats.get(format,format) self.sheet.write(row,col,value,format) @convert_cell_args def rwrite(self,row,col,values,format=None): '''按行写入''' if format: format=self.formats.get(format,format) self.sheet.write_row(row,col,values,format) def close(self): '''关闭文件''' self.book.close() def add_formats(self,args): '''添加格式''' if isinstance(args,dict): args=args.items() self.formats.update({name:self.book.add_format(format) for \ name,format in args}) @convert_range_args def add_table(self,first_row,first_col,last_row,last_col,\ sheet=None,**kwargs): '''添加图表,如sheet为空,则使用默认的工作表''' sheet=self.get_sheet(sheet)if sheet else self.sheet columns=kwargs.get('columns') if columns: new_columns=[] for idx,column in enumerate(columns): if 'width' in column: sheet.set_column("{0}:{0}".format( xl_col_to_name(idx+first_col)),\ column.get('width')) format=column.get("format") if format and isinstance(format,str): new_column=column.copy() new_column['format']=self.formats.get(format) new_columns.append(new_column) else: new_columns.append(column) kwargs['columns']=new_columns last_col=first_col+len(columns)-1 if 'data' in kwargs: last_row=first_row+len(kwargs['data']) if kwargs.get('total_row',False): last_row+=1 sheet.add_table(first_row,first_col,last_row,last_col,kwargs)
def process(self, files): # Get the filename from the already-open file handle filename = self.fout.name # Close the existing file as the XlsxWriter library handles that for us self.fout.close() # Create the new workbook workbook = Workbook(filename) # Define some common styles/ formats bold = workbook.add_format({ 'bold': 1, }) # Create the main worksheet where all the raw data is shown main_sheet = workbook.add_worksheet('Raw Data') # Write the header main_sheet.write(0, 0, 'IP', bold) # The IP field can be either ip_str or ipv6 so we treat it differently main_sheet.set_column(0, 0, 20) row = 0 col = 1 for field in self.fields: name = self.field_names.get(field, field.capitalize()) main_sheet.write(row, col, name, bold) col += 1 row += 1 total = 0 ports = defaultdict(int) for banner in iterate_files(files): try: # Build the list that contains all the relevant values data = [] for field in self.fields: value = self.banner_field(banner, field) data.append(value) # Write those values to the main workbook # Starting off w/ the special "IP" property main_sheet.write_string(row, 0, get_ip(banner)) col = 1 for value in data: main_sheet.write(row, col, value) col += 1 row += 1 except Exception: pass # Aggregate summary information total += 1 ports[banner['port']] += 1 summary_sheet = workbook.add_worksheet('Summary') summary_sheet.write(0, 0, 'Total', bold) summary_sheet.write(0, 1, total) # Ports Distribution summary_sheet.write(0, 3, 'Ports Distribution', bold) row = 1 col = 3 for key, value in sorted(ports.items(), reverse=True, key=lambda kv: (kv[1], kv[0])): summary_sheet.write(row, col, key) summary_sheet.write(row, col + 1, value) row += 1
def csv_to_xlsx(): """""" # xlsxwriter format info: http://xlsxwriter.readthedocs.io/format.html aqua = '#31849B' gold = '#DDD9C3' gray = '#D8D8D8' light_gold = '#EEECE1' light_gray = '#F2F2F2' white = 'white' money_format = '$#,##0' int_format = '#,##0' float_format = '#,##0.00' wb = Workbook(EXCEL_PATH, {'strings_to_numbers': True}) header_format = wb.add_format({ 'align': 'center', 'bg_color': aqua, 'bold': True, 'font_color': white }) footer_format = wb.add_format({ 'bg_color': aqua, 'bold': True, 'font_color': white, 'underline': 1 }) for x, tbl in enumerate(STATS_TABLES): csv_path = join(CSV_DIR, '{}.csv'.format(tbl)) ws = wb.add_worksheet(SHEET_BASENAME.format(SHEET_TYPE[x])) ws.freeze_panes(1, 0) with open(csv_path) as stats_csv: stats_reader = csv.reader(stats_csv) rows = len(list(stats_reader)) stats_csv.seek(0) header = next(stats_reader) cols = len(header) for j, item in enumerate(header): ws.write(0, j, HEADER_MAP[item], header_format) for i, row in enumerate(stats_reader, 1): # set row colors if i == 1: bg_color = gold elif i <= 4: bg_color = light_gold elif i % 4 == 1: bg_color = gray else: bg_color = light_gray for j, item in enumerate(row): cell_format = wb.add_format({ 'bg_color': bg_color, 'border': 1, # solid, width 1 'border_color': gray }) # format numbers/set alignment if j == 2: cell_format.set_align('right') elif j in (3, 5): cell_format.set_num_format(int_format) elif j in (4, 7): cell_format.set_num_format(money_format) elif j in (6, 8): cell_format.set_num_format(float_format) ws.write(i, j, item, cell_format) ws.merge_range(rows, 0, rows, cols - 1, '') ws.write_url(rows, 0, METADATA_URL, footer_format, FOOTNOTE) wb.close()