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 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 _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 _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 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()