How to use the openpyxl.utils.get_column_letter function in openpyxl

To help you get started, we’ve selected a few openpyxl examples, based on popular ways it is used in public projects.

Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.

github BLKSerene / Wordless / src / wordless_widgets / wordless_table.py View on Github external
self.style_cell_num(cell, self.table.item(row_item, col))
                                else:
                                    self.style_cell_text(cell, self.table.item(row_item, col))

                            self.progress_updated.emit(self.tr(f'Exporting table ... ({row_cell + 1} / {len_rows})'))
                    else:
                        # Horizontal Headers
                        for col in range(self.table.columnCount()):
                            cell = worksheet.cell(1, 2 + col) 
                            cell.value = self.table.horizontalHeaderItem(col).text()

                            self.style_header_horizontal(cell, self.table.horizontalHeaderItem(col))

                            worksheet.column_dimensions[openpyxl.utils.get_column_letter(2 + col)].width = self.table.horizontalHeader().sectionSize(col) / dpi_horizontal * 13 + 3

                        worksheet.column_dimensions[openpyxl.utils.get_column_letter(1)].width = self.table.verticalHeader().width() / dpi_horizontal * 13 + 3

                        # Vertical Headers
                        for row_cell, row_item in enumerate(self.rows_export):
                            cell = worksheet.cell(2 + row_cell, 1)
                            cell.value = self.table.verticalHeaderItem(row_item).text()

                            self.style_header_vertical(cell, self.table.verticalHeaderItem(row_item))

                        # Cells
                        for row_cell, row_item in enumerate(self.rows_export):
                            for col in range(self.table.columnCount()):
                                cell = worksheet.cell(2 + row_cell, 2 + col)

                                cell_val = self.table.item(row_item, col).text()
                                # Remove illegal characters
                                cell_val = re.sub(openpyxl.cell.cell.ILLEGAL_CHARACTERS_RE, '', cell_val)
github abilian / abilian-sbe / abilian / sbe / apps / communities / views / views.py View on Github external
cell = WriteOnlyCell(ws, value=value)
            cells.append(value)

            # estimate width
            value = str(cell.value)
            width = max(len(l) for l in value.split("\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 = "{}-members-{}.xlsx".format(
        community.slug, strftime("%d:%m:%Y-%H:%M:%S", gmtime())
    )
    response.headers["content-disposition"] = f'attachment;filename="{filename}"'

    return response
github Arelle / Arelle / arelle / ViewFile.py View on Github external
for iCol, col in enumerate(cols):
                cell = self.xlsxWs.cell(row=self.xlsxRow+1, column=iCol+1)
                if asHeader:
                    cell.value = col.replace('\u00AD','') # remove soft-breaks
                    cell.alignment = Alignment(horizontal="center", vertical="center")
                    cell.fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=Color("00FFBF5F"))
                else:
                    cell.value = col if isinstance(col,(str,int,float,Decimal)) else str(col)
                    if iCol == 0 and self.treeCols and treeIndent > 0:
                        cell.alignment = Alignment(indent=treeIndent)
                if self.xlsxColWrapText and iCol < len(self.xlsxColWrapText) and self.xlsxColWrapText[iCol]:
                    cell.alignment = Alignment(wrap_text=True)
            if lastColSpan and cell is not None:
                self.xlsxWs.merge_cells(range_string='%s%s:%s%s' % (utils.get_column_letter(iCol+1),
                                                                    self.xlsxRow+1,
                                                                    utils.get_column_letter(iCol+lastColSpan),
                                                                    self.xlsxRow+1))
            if asHeader and self.xlsxRow == 0:
                self.xlsxWs.freeze_panes = self.xlsxWs["A2"] # freezes row 1 and no columns
            self.xlsxRow += 1
        elif self.type == HTML:
            tr = etree.SubElement(self.tblElt, "{http://www.w3.org/1999/xhtml}tr")
            td = None
            for i, col in enumerate(cols + [None for emptyCol in range(self.numHdrCols - colSpan + 1 - len(cols))]):
                attrib = {}
                if asHeader:
                    attrib["class"] = "tableHdr"
                    colEltTag = "{http://www.w3.org/1999/xhtml}th"
                else:
                    colEltTag = "{http://www.w3.org/1999/xhtml}td"
                    attrib["class"] = "tableCell"
                if i == 0:
github bcgov / gwells / app / backend / registries / views_v2.py View on Github external
def list(self, request, **kwargs):
        queryset = self.get_queryset()
        filtered_queryset = self.filter_queryset(queryset)

        mime_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

        wb = openpyxl.Workbook()
        ws = wb.active
        ws.append(REGISTRY_EXPORT_HEADER_COLUMNS)
        for i, column_name in enumerate(REGISTRY_EXPORT_HEADER_COLUMNS):
            col_letter = get_column_letter(i + 1)
            ws.column_dimensions[col_letter].width = len(column_name)
        for row in build_row(filtered_queryset):
            ws.append([str(col) if col else '' for col in row])
        response = HttpResponse(content=save_virtual_workbook(wb), content_type=mime_type)
        response['Content-Disposition'] = 'attachment; filename="registry.xlsx"'
        return response
github Spine-project / Spine-Toolbox / spinetoolbox / excel_import_export.py View on Github external
def read_2d(ws, start_row=1, end_row=1, start_col=1, end_col=1):
    """Reads a 2d area from worksheet into a list of lists where each line is
    the inner list.

    Args:
        ws (openpyxl.workbook.worksheet): Worksheet to look in
        start_row (Integer): start row to read, 1-indexed (as excel)
        end_row (Integer): row to read to, 1-indexed (as excel)
        start_col (Integer): start column to read, 1-indexed (as excel)
        end_col (Integer): end column to read to, 1-indexed (as excel)

    Returns:
        (List) List of all lines read.
    """
    end_col = get_column_letter(end_col)
    start_col = get_column_letter(start_col)
    xl_index = '{}{}:{}{}'.format(start_col, start_row, end_col, end_row)
    values = [[inner.value for inner in outer] for outer in ws[xl_index]]
    return values
github ForomePlatform / anfisa / export / excel.py View on Github external
def _createKeySheet(self):
        ws = self.workbook.create_sheet("key")
        for idx, title in enumerate(["Column", "Definition", "Mapping"]):
            ws.cell(row = 1, column = idx + 1, value = title)
            ws.column_dimensions[
                openpyxl.utils.get_column_letter(idx + 1)].width = 50
        ws.freeze_panes = 'A2'
        for row, key, value, style, def_value in self.mapping:
            if not value:
                continue
            cell = ws.cell(row = row + 1, column = 1, value = value)
            ws.cell(row = row + 1, column = 2, value = def_value)
            ws.cell(row = row + 1, column = 3, value = key)
            _setStyle(cell, style)
github czcorpus / kontext / lib / plugins / export / default_xlsx.py View on Github external
def writerow(self, line_num, *lang_rows):
        row = []
        if line_num is not None:
            row.append(line_num)
        for lang_row in lang_rows:
            row += self._import_row(lang_row)
        for i in range(1, len(row) + 1):
            col = get_column_letter(i)
            value, cell_format = self._import_value(row[i - 1], i - 1)
            cell = self._sheet['%s%s' % (col, self._curr_line)]
            cell.value = value
            cell.number_format = cell_format
        self._curr_line += 1
github Arelle / Arelle / arelle / plugin / saveLoadableExcel.py View on Github external
if not hAlign: hAlign = "center"
            if not vAlign: vAlign = "center"
        else:
            cell.font = cellFont
            if not hAlign: hAlign = "left"
            if not vAlign: vAlign = "top"
        if borders:
            cell.border = Border(top=Side(border_style="thin"),
                                 left=Side(border_style="thin"),
                                 right=Side(border_style="thin"),
                                 bottom=Side(border_style="thin"))
        cell.alignment = Alignment(horizontal=hAlign, vertical=vAlign, wrap_text=True, indent=indent)
            
    # sheet 1 col widths
    for i, hdr in enumerate(conceptsWsHeaders):
        colLetter = get_column_letter(i+1)
        conceptsWs.column_dimensions[colLetter] = ColumnDimension(conceptsWs, customWidth=True)
        conceptsWs.column_dimensions[colLetter].width = headerWidths.get(hdr[1], 40)                                   
        
    # sheet 2 headers
    for i, hdr in enumerate(dtsWsHeaders):
        colLetter = get_column_letter(i+1)
        dtsWs.column_dimensions[colLetter] = ColumnDimension(conceptsWs, customWidth=True)
        dtsWs.column_dimensions[colLetter].width = hdr[1]
        writeCell(dtsWs, 1, i+1, hdr[0], hdr=True)
        
    # referenced taxonomies
    conceptsRow = 1
    dtsRow = 3
    # identify extension schema
    extensionSchemaDoc = None
    if dts.modelDocument.type == ModelDocument.Type.SCHEMA:
github bcgov / gwells / app / backend / wells / management / commands / export.py View on Github external
with open(csv_file, 'w') as csvfile:
            csvwriter = csv.writer(csvfile, dialect='excel')

            values = []
            cells = []
            # Write the headings
            for index, field in enumerate(cursor.description):
                fieldName = field[0]
                values.append(fieldName)
                cell = WriteOnlyCell(worksheet, value=fieldName)
                cell.font = Font(bold=True)
                cells.append(cell)
            columns = len(values)

            for index, value in enumerate(values):
                worksheet.column_dimensions[get_column_letter(index+1)].width = len(value) + 2

            worksheet.append(cells)
            csvwriter.writerow(values)

            # Write the values
            row_index = 0
            for row, record in enumerate(ResultIter(cursor)):
                values = []
                num_values = 0
                for col, value in enumerate(record):
                    if not (value == "" or value is None):
                        num_values += 1
                    if type(value) is str:
                        # There are lots of non-printable characters in the source data that can cause
                        # issues in the export, so we have to clear them out.
                        v = ''.join([s for s in value if s in string.printable])