Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
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)
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
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:
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
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
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)
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
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:
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])