def asSpreadSheet(self, dataframe, row_headers, col_headers, title): '''save the table as an xls file. Multiple files of the same Renderer/Tracker combination are distinguished by the title. ''' self.debug("%s: saving %i x %i table as spread-sheet'" % (id(self), len(row_headers), len(col_headers))) is_hierarchical = isinstance(dataframe.index, pandas.core.index.MultiIndex) split = is_hierarchical and len(dataframe.index.levels) > 1 quick = len(dataframe) > 10000 if quick and not split: # quick writing, only append method works wb = openpyxl.Workbook(optimized_write=True) def fillWorksheet(ws, dataframe, title): ws.append([""] + list(col_headers)) for x, row in enumerate(dataframe.iterrows()): ws.append([path2str(row[0])] + list(row[1])) # patch: maximum title length seems to be 31 ws.title = title[:30] else: # do it cell-by-cell, this might be slow wb = openpyxl.Workbook(optimized_write=False) def fillWorksheet(ws, dataframe, title): # regex to detect rst hypelinks regex_link = re.compile('`(.*) <(.*)>`_') # write row names for row, row_name in enumerate(dataframe.index): # rows and columns start at 1 c = ws.cell(row=row + 2, column=1) c.value = row_name # write columns for column, column_name in enumerate(dataframe.columns): # set column title # rows and columns start at 1 c = ws.cell(row=1, column=column + 2) c.value = column_name # set column values dataseries = dataframe[column_name] if dataseries.dtype == object: for row, value in enumerate(dataseries): c = ws.cell(row=row + 2, column=column + 2) value = str(value) if value.startswith('`'): c.value, c.hyperlink =\ regex_link.match(value).groups() else: c.value = value else: for row, value in enumerate(dataseries): c = ws.cell(row=row + 2, column=column + 2) c.value = value # patch: maximum title length seems to be 31 ws.title = re.sub("/", "_", title)[:30] if len(wb.worksheets) == 0: wb.create_sheet() if split: # create separate worksheets for nested indices nlevels = len(dataframe.index.levels) paths = map(tuple, DataTree.unique( [x[:nlevels - 1] for x in dataframe.index.unique()])) ws = wb.worksheets[0] ws.title = 'Summary' ws.append( [""] * (nlevels - 1) + ["Worksheet", "Rows"]) for row, path in enumerate(paths): # select data frame as cross-section work = dataframe.xs(path, axis=0) title = path2str(path) if len(title) > 30: title = "sheet%i" % row ws.append(list(path) + [title, len(work)]) c = ws.cell(row=row + 1, column=nlevels) # this does not work in oocalc c.hyperlink = "#%s!A1" % title fillWorksheet(wb.create_sheet(), work, title=title) else: fillWorksheet(wb.worksheets[0], dataframe, title=title) # write result block lines = [] lines.append("`%i x %i table <#$xls %s$#>`__" % (len(row_headers), len(col_headers), title)) lines.append("") r = ResultBlock("\n".join(lines), title=title) r.xls = wb self.debug("%s: saved %i x %i table as spread-sheet'" % (id(self), len(row_headers), len(col_headers))) return r