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 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([""])
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 _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 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 _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 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 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 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")
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()