def test_openpyxl(wb): ws = wb.create_sheet("Юникод шит") start = time.clock() bold_font = Font(bold=True) italic_font = Font(italic=True) grey_background = PatternFill(bgColor='d3d3d3', fill_type='solid') for i in range(ROWS): row = [] for j in range(COLS): # row, col, data cell_obj = WriteOnlyCell(ws=ws, value=i + j) if i % 2 == 0: if i: cell_obj.font = italic_font cell_obj.fill = grey_background else: cell_obj.font = bold_font row.append(cell_obj) ws.append(row) ws.column_dimensions["A"].width = 30.0 ws.column_dimensions["B"].width = 30.0 ws.column_dimensions["C"].width = 50.0 ws.column_dimensions["D"].width = 50.0 ws.column_dimensions["E"].width = 50.0 start_close = time.clock() wb.save("openpyxl.xlsx") end_close = time.clock() end = time.clock() print("Close time:", format(end_close - start_close, ".2f"), "Total:", format(end - start, ".2f"))
def format_output(val,eachformat, eachstyle): "Returns a excel cell with the data formated as specified" new_cell = WriteOnlyCell(xls_sheet, value = "init") new_cell.style = eachstyle if val==None: val="None" elif eachformat == None: pass elif eachformat == "OLE": val = ole_timestamp(val) new_cell.number_format = 'YYYY MMM DD' elif eachformat.startswith("OLE:"): val = ole_timestamp(val) val = val.strftime(eachformat[4:]) elif eachformat=="FILE": val = file_timestamp(val) new_cell.number_format = 'YYYY MMM DD' elif eachformat.startswith("FILE:"): val = file_timestamp(val) val = val.strftime(eachformat[5:]) elif eachformat.lower() == "lookup_id": val = id_table.get(val, "No match in srum lookup table for %s" % (val)) elif eachformat.lower() == "lookup_luid": val = lookup_luid(val) elif eachformat.lower() == "lookup_sid": val = "%s (%s)" % (val, lookup_sid(val)) elif eachformat.lower() == "seconds": val = val/86400.0 new_cell.number_format = 'dd hh:mm:ss' elif eachformat.lower() == "md5": val = hashlib.md5(str(val)).hexdigest() elif eachformat.lower() == "sha1": val = hashlib.sha1(str(val)).hexdigest() elif eachformat.lower() == "sha256": val = hashlib.sha256(str(val)).hexdigest() elif eachformat.lower() == "base16": if type(val)=="<type 'int'>": val = hex(val) else: val = str(val).encode("hex") elif eachformat.lower() == "base2": if type(val)==int: val = bin(val) else: try: val = int(str(val),2) except : val = val new_cell.comment = Comment("Warning: Unable to convert value %s to binary." % (val),"srum_dump") elif eachformat.lower() == "interface_id" and options.reghive: val = interface_table.get(str(val),"") elif eachformat.lower() == "interface_id" and not options.reghive: val = val new_cell.comment = Comment("WARNING: Ignoring interface_id format command because the --REG_HIVE was not specified.", "srum_dump") else: val = val new_cell.comment = Comment("WARNING: I'm not sure what to do with the format command %s. It was ignored." % (eachformat), "srum_dump") new_cell.value = val return new_cell
def _to_worksheet(self, ws): ws.title = self.ds.name # write headers headers = [] for header in self.headers: cell = WriteOnlyCell(ws, value=header) cell.font = COLUMN_HEADER_FONT headers.append(cell) ws.append(headers) for row in self.row_it(): ws.append(row) return ws
def create_return_template_workbook(return_type): wb = Workbook(write_only=True) for resource in return_type.resources: schema = Schema(resource.get("schema")) ws = wb.create_sheet() ws.title = resource.get("title", resource.get("name")) headers = [] for header in schema.headers: cell = WriteOnlyCell(ws, value=header) cell.font = COLUMN_HEADER_FONT headers.append(cell) ws.append(headers) return wb
def to_workbook(sheet_name, headers, rows): wb = Workbook(write_only=True) ws = wb.create_sheet() ws.title = sheet_name header_cells = [] for header in headers: cell = WriteOnlyCell(ws, value=header) cell.font = ReportHelper.COLUMN_HEADER_FONT header_cells.append(cell) ws.append(header_cells) for row in rows: ws.append(row) return wb
def _create_cell(worksheet, value): color_fill = None if issubclass(type(value), numpy.integer): value = int(value) elif isinstance(value, numpy.bytes_): value = value.decode() elif hasattr(value, 'shape') or isinstance(value, (tuple, list)): value, color_fill = _indi_snp_gt_to_str(value) cell = WriteOnlyCell(worksheet, value=value) if color_fill is not None: cell.fill = color_fill return cell
def _append_excel_row(self, worksheet, data, header=False): if header: font = self.header_font else: font = self.data_font if not font: row = data else: row = [] for cell in data: cell = WriteOnlyCell(worksheet, cell) cell.font = font row.append(cell) worksheet.append(row)
def fill_cell(working_sheet, value, negrita=False): # TODO add colors depending on the day (labor or not) try: cell = WriteOnlyCell(working_sheet, value=value) cell.style = Style( font=Font(name="Calibri", size=11, bold=negrita), border=Border( left=Side(border_style=borders.BORDER_THIN, color="FF000000"), right=Side(border_style=borders.BORDER_THIN, color="FF000000"), top=Side(border_style=borders.BORDER_THIN, color="FF000000"), bottom=Side(border_style=borders.BORDER_THIN, color="FF000000"), ), ) cell.alignment = Alignment(horizontal="center", vertical="center") except: pass return cell
def export_xlsx(modeladmin, request, queryset): """Export to XLSX action for list of Players.""" # import required OpenDocument packages from openpyxl import Workbook from openpyxl.styles import Font from openpyxl.writer.write_only import WriteOnlyCell # initialize empty document workbook = Workbook(write_only=True) sheet = workbook.create_sheet('Registrovaní hráči') # update column width for item in 'ABCDE': sheet.column_dimensions[item].width = 20 # initialize header font = Font(bold=True) header = list() for item in ['Rasa', 'Přezdívka', 'Jméno', 'Příjmení', 'Věk']: cell = WriteOnlyCell(sheet, value=item) cell.font = font header.append(cell) sheet.append(header) # populate content dataset = queryset.order_by('race', 'nick', 'surname') for item in dataset: data = [ item.race.name, item.nick, item.name, item.surname, item.age ] sheet.append(data) response = HttpResponse( content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=registrace.xlsx' workbook.save(response) return response
def _writerow(rowvalues, header=False): row = [] _ws = _xlinfo["ws"] for i, v in enumerate(rowvalues): cell = WriteOnlyCell(_ws, value=v) if header: cell.fill = hdrCellFill cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) colLetter = chr(ord('A') + i) _ws.column_dimensions[colLetter] = ColumnDimension(_ws, customWidth=True) _ws.column_dimensions[colLetter].width = headerWidths.get(v, 20) else: cell.alignment = Alignment(horizontal="right" if isinstance(v, _NUM_TYPES) else "center" if isinstance(v, bool) else "left", vertical="top", wrap_text=isinstance(v, str)) row.append(cell) _ws.append(row)
def get(self, request, **kwargs): if self.model == self.MODEL_LAT_LONG: headers = self.COMMON_HEADERS + self.LAT_LONG_HEADERS elif self.model == self.MODEL_EASTING_NORTHING: headers = self.COMMON_HEADERS + self.EASTING_NORTHING_HEADERS else: return HttpResponseBadRequest( "Unknown site template model {}. Must be one of {}.".format( self.model, [self.MODEL_LAT_LONG, self.MODEL_EASTING_NORTHING])) wb = Workbook(write_only=True) ws = wb.create_sheet() ws.title = 'Sites' cells = [] for header in headers: cell = WriteOnlyCell(ws, value=header) cell.font = self.HEADER_FONT cells.append(cell) ws.append(cells) file_name = 'Sites_template_' + self.model return WorkbookResponse(wb, file_name=file_name)
def save_xlsx_template(file_path, data, header=None, col_id=None): """将输入保存到 Excel 文件中。使用文件模板 全部保存为文本。 Args: file_path (str): xlsx 文件的路径 data (list[list]): 要保存的数据,二维 header (list): 第一行 col_id (list[int]): data 中列号到 xlsx 中列号的映射 """ if len(data) <= 0: return save_xlsx(file_path, data, header, col_id) cd = os.path.dirname(os.path.abspath(__file__)) num_col = len(data[0]) if num_col == 9: shutil.copy(os.path.join(cd, 'data/prototype_list.xlsx'), file_path) elif num_col == 12: shutil.copy(os.path.join(cd, 'data/prototype_pair.xlsx'), file_path) elif num_col == 8: shutil.copy(os.path.join(cd, 'data/prototype_ui.xlsx'), file_path) else: return save_xlsx(file_path, data, header, col_id) if col_id is None: col_id = list(range(0, num_col)) max_col_id = max(col_id) workbook = openpyxl.load_workbook(file_path) sheet = workbook.get_sheet_by_name('sheet 1') # 格式 fonts = [sheet.cell(row=1, column=i + 1).font.copy() for i in col_id] fills = [sheet.cell(row=1, column=i + 1).fill.copy() for i in col_id] alignments = [ sheet.cell(row=1, column=i + 1).alignment.copy() for i in col_id ] number_formats = [ sheet.cell(row=1, column=i + 1).number_format for i in col_id ] # 写入内容 row_id = 1 for row in data: row_cells = [ '', ] * (max_col_id + 1) for j in range(0, num_col): cell = WriteOnlyCell(sheet, value=str(row[j])) cell.font = fonts[j] cell.fill = fills[j] cell.alignment = alignments[j] cell.number_format = number_formats[j] row_cells[col_id[j]] = cell sheet.append(row_cells) row_id += 1 workbook.save(file_path)
def write_excel(rows, filename, header_rows=['Headline 2', 'Headline 3']): workbook = Workbook() worksheet = workbook["Sheet"] col_widths = [max(8, len(v) + 4) for v in rows[0]] for i, _ in enumerate(rows[0]): worksheet.column_dimensions[get_column_letter(i + 1)].width = col_widths[i] for row_index, row in enumerate(rows): if row_index < len(header_rows): cells = [WriteOnlyCell(worksheet, value=v) for v in row] for cell in cells: cell.style = header_rows[row_index] worksheet.append(cells) else: worksheet.append(row) workbook.save(filename)
def add_updated(self, dataold, datanew, change): for i in change: temp_old = WriteOnlyCell(self.updated, dataold[i]) temp_new = WriteOnlyCell(self.updated, datanew[i]) temp_old.font = Font(color=RED) temp_new.font = Font(color="008000") dataold[i] = temp_old datanew[i] = temp_new self.updated.append(dataold) self.updated.append(datanew) self.updated.append([""])
elif len(row[14]) >= 100: longs.append(row[6]) continue else: try: dd = fill_form(driver, row, head) loaded.append(row[6]) except: errors.append([row[6], 'Поле не определено']) wb_out = Workbook(write_only=True) ws_write = wb_out.create_sheet('Загружено') ws_err = wb_out.create_sheet('Ошибки') ws_date = wb_out.create_sheet('Паспорт выдан раньше 14') ws_long = wb_out.create_sheet('Кем выдан > 100 симв') for ik, jkl in enumerate(loaded): cell = WriteOnlyCell(ws_write, value=jkl) ws_write.append([cell]) for ik, jkl in enumerate(errors): cell = WriteOnlyCell(ws_err, value=jkl[0]) cell2 = WriteOnlyCell(ws_err, value=jkl[1]) ws_err.append([cell, cell2]) for ik, jkl in enumerate(date_err): cell = WriteOnlyCell(ws_date, value=jkl) ws_date.append([cell]) for ik, jkl in enumerate(longs): cell = WriteOnlyCell(ws_long, value=jkl) ws_long.append([cell]) f = sys.argv[1].replace(sys.argv[1].split('/')[-1], 'err_' + sys.argv[1].split('/')[-1]) wb_out.save(f) driver.close()
def members_excel_export(): community = g.community attributes = [attrgetter(a) for a in MEMBERS_EXPORT_ATTRS] BaseModel = current_app.db.Model wb = openpyxl.Workbook() if wb.worksheets: wb.remove_sheet(wb.active) ws_title = _(u'%(community)s members', community=community.name) ws_title = ws_title.strip() if len(ws_title) > 31: # sheet title cannot exceed 31 char. max length ws_title = ws_title[:30] + u'…' ws = wb.create_sheet(title=ws_title) row = 0 cells = [] cols_width = [] for col, label in enumerate(MEMBERS_EXPORT_HEADERS, 1): value = text_type(label) cell = WriteOnlyCell(ws, value=value) cell.font = HEADER_FONT cell.alignment = HEADER_ALIGN cells.append(cell) cols_width.append(len(value) + 1) ws.append(cells) for membership_info in _members_query().all(): row += 1 cells = [] for col, getter in enumerate(attributes): value = None try: value = getter(membership_info) except AttributeError: pass if isinstance(value, (BaseModel, Role)): value = text_type(value) cell = WriteOnlyCell(ws, value=value) cells.append(value) # estimate width value = text_type(cell.value) width = max(len(l) for l in value.split(u'\n')) + 1 cols_width[col] = max(width, cols_width[col]) ws.append(cells) # adjust columns width MIN_WIDTH = 3 MAX_WIDTH = openpyxl.utils.units.BASE_COL_WIDTH * 4 for idx, width in enumerate(cols_width, 1): letter = openpyxl.utils.get_column_letter(idx) width = min(max(width, MIN_WIDTH), MAX_WIDTH) ws.column_dimensions[letter].width = width fd = BytesIO() wb.save(fd) fd.seek(0) response = current_app.response_class(fd, mimetype=XLSX_MIME) filename = u'{}-members-{}.xlsx'.format( community.slug, strftime("%d:%m:%Y-%H:%M:%S", gmtime())) response.headers['content-disposition'] = \ u'attachment;filename="{}"'.format(filename) return response
print sheet["A1"].value print sheet["A1"].column print sheet["A1"].row print sheet.cell(row=1,column=2).value # for #Zapis ft = Font(color=colors.RED) wb = Workbook() ws = wb.active ws.font = ft ws.title = "TYTAASD" ws["B2"] = 43 ws["B6"] = "=A5+A6" cell = WriteOnlyCell(ws, value="hello world") cell.font = Font(name='Courier', size=36) cell.comment = Comment(text="A comment", author="Author's Name") ws.append([cell, 3.14, 4]) cell2 = ws.cell(row=1,column=2).value = "DUPA" cell2 = ws.cell(row=1,column=2).font = Font(name='Calibri', size=55,color=colors.RED) wb.save("asd2.xlsx")
#Now create the worksheet in the new xls file with the same name as the template print "\n\nCreating Sheet " + each_sheet if not options.quiet: print "Processing %s records with %s fields." % ( ese_table.number_of_records, ese_table.number_of_columns), print "While you wait, did you know ...\n" + ads.next() xls_sheet = target_wb.create_sheet(title=each_sheet) #Now copy the header values and header formats from the template to the new worksheet header_row = [] for eachcolumn in range(1, len(ese_template_fields) + 1): cell_value = template_sheet.cell(row=4, column=eachcolumn).value cell_style = template_sheet.cell(row=4, column=eachcolumn).style new_cell = WriteOnlyCell(xls_sheet, value=cell_value) new_cell.style = cell_style header_row.append(new_cell) xls_sheet.append(header_row) #Create a dictionary to lookup column numbers based on a column name column_lookup = dict([(x.name, index) for index, x in enumerate(ese_table.columns)]) #For each row in the ESE database for ese_row_num in range(ese_table.number_of_records): if ese_table.name == "GiveMeAName110": import pdb pdb.set_trace() xls_row = [] #For each column in our template spreadsheet for eachcolumn, eachformat, eachstyle in zip(ese_template_fields,
def append_row(self, sheet, values): row = [] for value in values: cell = WriteOnlyCell(sheet, value=self.prepare_value(value)) row.append(cell) sheet.append(row)
#Now create the worksheet in the new xls file with the same name as the template print "\nCreating Sheet " + each_sheet if not options.quiet: try: ad = ads.next() except: ad = "Thanks for using srum_dump!" print "While you wait, did you know ...\n" + ad + "\n" xls_sheet = target_wb.create_sheet(title=each_sheet) #Now copy the header values and header formats from the template to the new worksheet header_row = [] for eachcolumn in range(1, len(ese_template_fields) + 1): cell_value = template_sheet.cell(row=4, column=eachcolumn).value cell_style = template_sheet.cell(row=4, column=eachcolumn).style new_cell = WriteOnlyCell(xls_sheet, value=cell_value) new_cell.style = cell_style header_row.append(new_cell) xls_sheet.append(header_row) #Until we get an empty row retrieve the rows from the ESE table and process them row_num = 1 #Init to 1, first row will be 2 in spreadsheet (1 is headers) while True: try: ese_row = ese_db.getNextRow(ese_table) except Exception as e: print "Skipping corrupt row in the %s table. The last good row was %s." % ( each_sheet, row_num) continue if ese_row == None: break #The row is retrieved now use the template to figure out which ones you want and format them
def start(self, tag, attrib): if tag == 'sheet': if not self._current_ws: self._current_ws = self.wb.active if 'title' in attrib: self._current_ws.title = attrib['title'] else: index = int(attrib.get('index')) if 'index' in attrib else None self._current_ws = self.wb.create_sheet(title=attrib.get( 'title', None), index=index) self._row = 0 elif tag == 'columns': start = column_index_from_string(attrib['start']) end = column_index_from_string(attrib.get('end', attrib['start'])) for i in range(start, end + 1): self._current_ws.column_dimensions[get_column_letter( i)].width = int(attrib.get('width')) / 7.0 elif tag == 'row': self._row_buf = [] self._col = 0 elif tag == 'cell': self._cell = WriteOnlyCell(self._current_ws) for attr, value in iteritems(attrib): if attr == 'font': self._cell.font = self._get_font(value) elif attr == 'fill': self._cell.fill = self._get_fill(value) elif attr == 'alignment': self._cell.alignment = self._get_alignment(value) elif attr == 'ref-id': self._refs[value] = CellRef(self, self._row, self._col) elif attr == 'ref-append': self._refs[value] = self._refs.get(value, []) self._refs[value].append( CellRef(self, self._row, self._col)) elif attr == 'fmt': self._cell.number_format = value elif attr == 'rowspan': self._current_ws.merge_cells(start_row=self._row + 1, start_column=self._col + 1, end_row=self._row + int(value), end_column=self._col + 1) elif attr == 'colspan': self._current_ws.merge_cells(start_row=self._row + 1, start_column=self._col + 1, end_row=self._row + 1, end_column=self._col + int(value)) ctype = attrib.get('type', 'unicode') if ctype not in ['unicode', 'number', 'date']: raise ValueError(u'Unknown cell type {ctype}.'.format( ctype=ctype, )) self._cell_type = ctype try: self._cell_date_format = attrib.get('date-fmt') except KeyError: raise ValueError(u"Specify 'date-fmt' attribute for 'date'" u" type") elif tag == 'style': style = NamedStyle(name=attrib['name']) if 'font' in attrib: style.font = self._get_font(attrib['font']) if 'fill' in attrib: style.fill = self._get_fill(attrib['fill']) self.wb.add_named_style(style)
def createheaders(self, sheet, type): title = [] title.append(WriteOnlyCell(sheet, "Changes")) title.append(WriteOnlyCell(sheet, "Employee ID")) title.append(WriteOnlyCell(sheet, "Last Name")) title.append(WriteOnlyCell(sheet, "First Name")) title.append(WriteOnlyCell(sheet, "PL Name")) title.append(WriteOnlyCell(sheet, "Sex")) title.append(WriteOnlyCell(sheet, "Birthdate")) title.append(WriteOnlyCell(sheet, "Process Level")) title.append(WriteOnlyCell(sheet, "Department")) title.append(WriteOnlyCell(sheet, "R Name")) title.append(WriteOnlyCell(sheet, "Date Hired")) for i in title: i.font = Font(bold=True) if type == "Updated": sheet.append(title) else: sheet.append(title[1:])
def downloadFamMatches(): import StringIO output = StringIO.StringIO() done = set() fileFormat = bottle.request.query.fileFormat titleRow = [bottle.request.query.workDB,'','','',bottle.request.query.matchDB,'',''] if fileFormat == 'xlsx': from openpyxl import Workbook from openpyxl.writer.write_only import WriteOnlyCell from openpyxl.styles import colors from openpyxl.styles import Border, Side, Font, Color, PatternFill, Alignment from openpyxl.utils import get_column_letter wb = Workbook(write_only=True) ws = wb.create_sheet() ws.title = "RGD matches" greyFill = PatternFill(start_color='DDDDDD', end_color='DDDDDD', fill_type='solid') greenFill = PatternFill(start_color='50FF50', end_color='50FF50', fill_type='solid') yellowFill = PatternFill(start_color='FFFF50', end_color='FFFF50', fill_type='solid') redFill = PatternFill(start_color='FF5050', end_color='FF5050', fill_type='solid') thick = Side(border_style="thick", color="000000") thin = Side(border_style="thin", color="000000") rowVals = [] for val in titleRow: cell = WriteOnlyCell(ws, value=val.encode("utf-8")) cell.font = Font(bold=True) rowVals.append(cell) ws.append(rowVals) else: import csv CSV = csv.writer(output, dialect='excel') CSV.writerow([s.encode("utf-8") for s in titleRow]) for fmatch in common.config['fam_matches'].find({'status': {'$in': list(common.statOK.union(common.statManuell))}}): rows = famDisp(None, None, fmatch) line = False lines=0 for r in rows: lines += 1 if lines == 1: continue try: k1 = r[1].split('<br/>')[1] except: k1 = '' try: k2 = r[5].split('<br/>')[1] except: k2 = '' key = k1+';'+k2 done.add(key) if 'Ignorerad' in r[0]: ignRelI = True else: ignRelI = False if 'Ignorerad' in r[8]: ignRelII = True else: ignRelII = False ignFam = False #remove html-code for buttons try: if 'Ignorerad' in r[4]: ignFam = True r[4] = r[4].split('<')[0] if ign: r[4] += ' Ignorerad' except: pass if r == ['', '', '', '', '', '', '', '', '']: line = True if fileFormat == 'xlsx': continue if fileFormat == 'xlsx': rowVals = [] i=0 green = False yellow = False red = False #if r[4].endswith(('EjMatch', 'EjOK', 'rEjOK')): red = True #elif r[4].endswith(('Manuell', 'rManuell')): yellow = True #elif r[4].endswith(('Match', 'OK', 'rOK')): green = True if ('EjMatch' in r[4]) or ('EjOK' in r[4]): red = True elif ('Match' in r[4]) or ('OK' in r[4]): green = True elif 'Manuell' in r[4]: yellow = True for val in r[1:8]: i+=1 if i == 4: #separator between workDB and matchDB cell = WriteOnlyCell(ws, value=val) cell.border = Border(top=thin, left=thick, right=thick, bottom=thin) else: cell = WriteOnlyCell(ws, value=val.replace('<br/>', "\n").rstrip().encode("utf-8")) cell.alignment = Alignment(wrapText=True) cell.border = Border(top=thin, left=thin, right=thin, bottom=thin) if lines <=2: #head cell.font = Font(bold=True) cell.border = Border(top=thick, left=thin, right=thin, bottom=thick) cell.fill = greyFill elif line: if i == 4: #separator between workDB and matchDB cell.border = Border(top=thick, left=thick, right=thick, bottom=thin) else: cell.border = Border(top=thick, left=thin, right=thin, bottom=thin) if (i == 4) and ignFam: cell.fill = redFill elif (i==1) and ignRelI: cell.fill = redFill elif (i==8) and ignRelII: cell.fill = redFill elif green: cell.fill = greenFill elif yellow: cell.fill = yellowFill elif red: cell.fill = redFill rowVals.append(cell) #line = False ws.append(rowVals) else: CSV.writerow([s.replace('<br/>', "\n").rstrip().encode("utf-8") for s in r]) if fileFormat != 'xlsx': CSV.writerow(['#####', '#####', '#####', '#####', '#####', '|', '#####', '#####', '#####']) #List matched persons without families for persmatch in common.config['matches'].find({'status': {'$in': list(common.statOK.union(common.statManuell))}}): if persmatch['pwork']['refId']+';'+persmatch['pmatch']['refId'] in done: continue rows = [] rows.append(['', u'Namn/refId', u'Född', u'Död', '', u'Namn/refId', u'Född', u'Död', '']) rows.append(persMatchDisp('Person', persmatch)) head = True for r in rows: #remove html-code for buttons try: r[4] = r[4].split('<')[0] except: pass if fileFormat == 'xlsx': rowVals = [] i=0 green = False yellow = False red = False if r[4].endswith(('EjMatch', 'EjOK', 'rEjOK')): red = True elif r[4].endswith(('Manuell', 'rManuell')): yellow = True elif r[4].endswith(('Match', 'OK', 'rOK')): green = True for val in r[1:8]: i+=1 if i==4: #separator between workDB and matchDB cell = WriteOnlyCell(ws, value=val) cell.border = Border(top=thin, left=thick, right=thick, bottom=thin) else: cell = WriteOnlyCell(ws, value=val.replace('<br/>', "\n").rstrip().encode("utf-8")) cell.alignment = Alignment(wrapText=True) cell.border = Border(top=thin, left=thin, right=thin, bottom=thin) if head: cell.font = Font(bold=True) cell.border = Border(top=thick, left=thin, right=thin, bottom=thick) cell.fill = greyFill if green: cell.fill = greenFill elif yellow: cell.fill = yellowFill elif red: cell.fill = redFill rowVals.append(cell) head = False line = False ws.append(rowVals) else: CSV.writerow([s.replace('<br/>', "\n").rstrip().encode("utf-8") for s in r]) if fileFormat != 'xlsx': CSV.writerow(['#####', '#####', '#####', '#####', '#####', '|', '#####', '#####', '#####']) # if fileFormat == 'xlsx': #doesn't really work? for i, column_width in enumerate([8,10,25,25,25,1,25,25,25]): ws.column_dimensions[get_column_letter(i+1)].width = (column_width + 2) * 1.2 wb.save(output) #Download filn = 'RGD_' + bottle.request.query.workDB + '-' + bottle.request.query.matchDB + '.' + fileFormat response.add_header('Expires', '0') response.add_header('Cache-Control', "must-revalidate, post-check=0, pre-check=0") response.set_header('Content-Type', "application/force-download") response.add_header('Content-Type', "application/octet-stream") response.add_header('Content-Type', "application/download") response.add_header('Content-Disposition', 'attachment; filename='+filn) response.add_header('Content-Transfer-Encoding', 'binary') return output.getvalue()