Exemple #1
0
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"))
Exemple #2
0
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
Exemple #4
0
 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
Exemple #5
0
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
Exemple #6
0
 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
Exemple #7
0
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
Exemple #8
0
    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)
Exemple #9
0
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
Exemple #10
0
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
Exemple #11
0
            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 _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)
Exemple #13
0
 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)
Exemple #14
0
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)
Exemple #15
0
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([""])
Exemple #17
0
 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()
Exemple #18
0
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
Exemple #19
0
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")

Exemple #20
0
    #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,
Exemple #21
0
 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)
Exemple #22
0
    #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
Exemple #23
0
    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:])
Exemple #25
0
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()