def write_description(self, worksheet, description=None):
if not description:
return
description = WriteOnlyCell(ws=worksheet, value=description)
description.style = self.description_style
worksheet.append((description, ))
worksheet.merge_cells(start_row=description.row,
start_column=description.col_idx,
end_row=description.row,
end_column=description.col_idx +
len(self.columns) - 1)
def get_array_cells(self):
acceptor_cell = WriteOnlyCell(ws_result, value=self.acceptor)
anchor_cell = WriteOnlyCell(ws_result, value=self.anchor)
donor_cell = WriteOnlyCell(ws_result, value=self.donor)
if not self.has_anchor:
acceptor_cell.fill = anchor_cell.fill = donor_cell.fill = PatternFill(
start_color='FFfcf8e3',
end_color='FFfcf8e3',
fill_type='solid')
if not self.has_acceptor:
acceptor_cell.fill = anchor_cell.fill = donor_cell.fill = PatternFill(
start_color='FFf2dede',
end_color='FFf2dede',
fill_type='solid')
return [
acceptor_cell,
# anchor_cell,
donor_cell,
# self.get_anchor_cell(),
self.get_donor_cell(),
self.get_status_cell(),
]
def f_cs_que(data):
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet('Out')
all_ngss = {}
for kt, t in data.items():
ngss = None
row = [int(kt)]
elem = elem_que(t)
for w in t.words:
if w in elem:
cpt = elem.index(w)
c = WriteOnlyCell(ws, value=w.form)
c.fill = patterns[cpt]
if cpt == 0:
ngss = w.lemma
if w.lemma not in all_ngss:
all_ngss[w.lemma] = 1
else:
all_ngss[w.lemma] += 1
row.append(c)
else:
row.append(w.form)
ws.append(row)
ws = wb.create_sheet('NGSS')
ngss_total = sum(all_ngss.values())
for k in sorted(all_ngss, key=all_ngss.get, reverse=True):
ws.append([k, all_ngss[k], all_ngss[k] / ngss_total])
ws = wb.create_sheet('Info')
ws.append(['Total occ NGSS', ngss_total])
ws.append(['Total res', len(data)])
wb.save('CS-que.xlsx')
def __get_cell(self, string, color=None, style=None):
"""
Helper method for log message. This method takes a string as well as color and style
arguments to create a write-only cell.
Args:
string: a string object to be written to the cell's value field.
color: a string object containing a color hex code "######"
style: a string choosing 1 of 3 formatting options (ITALICS, BOLD, UNDERLINED)
"""
if not MODULE_INSTALLED:
return
cell = WriteOnlyCell(self.worksheet, value=string)
if color is not None:
# pylint: disable=E0237
cell.fill = PatternFill("solid", fgColor=color)
# pylint: disable=E0237
cell.font = Font(
name=self.font_name,
bold=(style == self.BOLD),
italic=(style == self.ITALICS),
underline=("single" if style == self.UNDERLINED else "none"),
)
# pylint: disable=E0237
cell.alignment = self.__align
return cell
def get_donor_cell(self):
size = 9
if self.has_acceptor:
cell = WriteOnlyCell(ws_result, value='OK')
cell.fill = PatternFill(start_color='FFdff0d8',
end_color='FFdff0d8',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='3c763d',
bold=True)
cell.comment = Comment(text="Good =)", author=self.author)
else:
cell = WriteOnlyCell(ws_result, value='NO')
cell.fill = PatternFill(start_color='FFf2dede',
end_color='FFf2dede',
fill_type='solid')
cell.font = Font(name=self.font,
size=size,
color='a94442',
bold=True)
cell.comment = Comment(
text="There are no acceptor ({}) on the site: {}".format(
self.acceptor, self.donor),
author=self.author)
return cell
def openpyxl_stream(df):
"""
Write a dataframe straight to disk
"""
wb = Workbook(write_only=True)
ws = wb.create_sheet()
cell = WriteOnlyCell(ws)
cell.style = 'Pandas'
def format_first_row(row, cell):
for c in row:
cell.value = c
yield cell
rows = dataframe_to_rows(df)
first_row = format_first_row(next(rows), cell)
ws.append(first_row)
for row in rows:
row = list(row)
cell.value = row[0]
row[0] = cell
ws.append(row)
wb.save("openpyxl_stream.xlsx")
def test_hyperlink(WriteOnlyWorksheet):
ws = WriteOnlyWorksheet
cell = WriteOnlyCell(ws, 'should have hyperlink')
cell.hyperlink = 'http://bbc.co.uk'
ws.append([])
ws.append([cell])
assert cell.hyperlink.ref == "A2"
ws.close()
def write_ref_headings(self, data):
cells = []
for d in data:
cell = WriteOnlyCell(self._sheet, d)
cell.font = cell.font.copy(bold=True)
cells.append(cell)
self._sheet.append(cells)
self._sheet.merged_cells.ranges.append('A1:G1')
self._written_lines += 1
def f_cs_n_est_n(data):
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet('Out')
all_ngss = {}
all_nco = {}
all_cs = {}
for kt, t in data.items():
ngss = None
row = [int(kt)]
elem = elem_n_est_n(t)
for w in t.words:
if w in elem:
cpt = elem.index(w)
c = WriteOnlyCell(ws, value=w.form)
c.fill = patterns[cpt]
if cpt == 0:
ngss = w.lemma
if w.lemma not in all_ngss:
all_ngss[w.lemma] = 1
else:
all_ngss[w.lemma] += 1
elif cpt == 1:
pass
elif cpt == 2:
nco = w.lemma
if w.lemma not in all_nco:
all_nco[w.lemma] = 1
else:
all_nco[w.lemma] += 1
cs = (ngss, nco)
if cs not in all_cs:
all_cs[cs] = 1
else:
all_cs[cs] += 1
row.append(c)
else:
row.append(w.form)
ws.append(row)
ws = wb.create_sheet('NGSS')
ngss_total = sum(all_ngss.values())
for k in sorted(all_ngss, key=all_ngss.get, reverse=True):
ws.append([k, all_ngss[k], all_ngss[k] / ngss_total])
ws = wb.create_sheet('NCO')
nco_total = sum(all_nco.values())
for k in sorted(all_nco, key=all_nco.get, reverse=True):
ws.append([k, all_nco[k], all_nco[k] / nco_total])
ws = wb.create_sheet('CS')
cs_total = sum(all_cs.values())
for k in sorted(all_cs, key=all_cs.get, reverse=True):
ws.append([*k, all_cs[k], all_cs[k] / cs_total])
ws = wb.create_sheet('Info')
ws.append(['Total occ NGSS', ngss_total])
ws.append(['Total occ NCO', nco_total])
ws.append(['Total diff CS', cs_total])
ws.append(['Total res', len(data)])
wb.save('CS-n_est_n.xlsx')
def get(self, request, *args, **kwargs):
fmt = request.GET.get('format', None)
if fmt is None:
content = self._get_data(request)
return TemplateResponse(request, "bom.html", content)
elif fmt == 'spreadsheet':
# 下载 excel
json = self._get_data(request, in_page=False)
wb = Workbook(write_only=True)
title = "物料清单"
ws = wb.create_sheet(title)
headers = []
for h in self.file_headers:
cell = WriteOnlyCell(ws, value=h)
headers.append(cell)
ws.append(headers)
body_fields = ("id", "parent", "parent_nr", "item", "item_nr",
"effective_start", "effective_end", "qty")
data = []
for i in json["nodes"]:
adict = {}
adict["id"] = i.id
adict["item"] = i.name
adict["item_nr"] = i.nr
if i.parent:
adict["parent"] = i.parent.name
adict["parent_nr"] = i.parent.nr
else:
adict["parent"] = ""
adict["parent_nr"] = ""
adict["effective_start"] = i.effective_start
adict["effective_end"] = i.effective_end
adict["qty"] = i.qty
data.append(adict)
for i in data:
body = []
for field in body_fields:
if field in i:
cell = WriteOnlyCell(ws, value=i[field])
body.append(cell)
ws.append(body)
output = BytesIO()
wb.save(output)
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
content=output.getvalue())
response[
'Content-Disposition'] = "attachment; filename*=utf-8''%s.xlsx" % urllib.parse.quote(
force_str(title))
response['Cache-Control'] = "no-cache, no-store"
return response
def save(self):
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import PatternFill, Alignment
self.check_is_ready()
self.wb = Workbook(write_only=True)
meta_ws = self.wb.create_sheet()
meta_ws.title = "meta"
meta_ws.sheet_properties.tabColor = "8888ff"
meta_ws.column_dimensions['A'].width = 15
meta_ws.column_dimensions['B'].width = 40
meta_ws.column_dimensions['C'].width = 20
meta_ws.column_dimensions['D'].width = 20
meta_ws.column_dimensions['E'].width = 20
self.sections.resources.sort_by_term()
self.load_declares()
self.doc.cleanse()
self._load_resources()
self._clean_doc()
fill = PatternFill(
"solid", fgColor="acc0e0") # PatternFill(patternType='gray125')
table_fill = PatternFill(
"solid", fgColor="d9dce0") # PatternFill(patternType='gray125')
alignment = Alignment(wrap_text=False)
self._doc['Root'].get_or_new_term('Root.Issued').value = datetime_now()
for i, row in enumerate(self.doc.rows, 1):
if row[0] == 'Section' or row[0] == 'Table':
styled_row = []
for c in row + [''] * 5:
cell = WriteOnlyCell(meta_ws, value=c)
cell.fill = fill if row[0] == 'Section' else table_fill
styled_row.append(cell)
meta_ws.append(styled_row)
else:
meta_ws.append(row)
self.wb.save(self.package_path.path)
return self.package_path
def serialise(self, worksheet, record, triggered: Triggered):
linked_cell = WriteOnlyCell(worksheet, self.get_internal_id(record))
linked_cell.hyperlink = self.get_noms_ops_url(record)
linked_cell.style = 'Hyperlink'
row = {
'Notification rule': self.rule_description,
'Internal ID': linked_cell,
}
additional_header = self.additional_headers.get(type(self.rule), None)
if additional_header:
row[additional_header['header']] = triggered.kwargs[additional_header['triggered_kwarg']]
return row
def f_cs_de_inf(data):
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet('Out')
all_ngss = {}
all_inf = {}
all_cs = {}
for kt, t in data.items():
ngss = None
row = [int(kt)]
elem = elem_cs_de_inf(t)
for w in t.words:
if w in elem:
cpt = elem.index(w)
c = WriteOnlyCell(ws, value=w.form)
c.fill = patterns[cpt]
if cpt == 0:
ngss = w.lemma
if w.lemma not in all_ngss:
all_ngss[w.lemma] = 1
else:
all_ngss[w.lemma] += 1
elif cpt == 2: # on ne retient pas le "être" optionnel
cs = (ngss, 'de', w.lemma)
if w.lemma not in all_inf:
all_inf[w.lemma] = 1
else:
all_inf[w.lemma] += 1
if cs not in all_cs:
all_cs[cs] = 1
else:
all_cs[cs] += 1
row.append(c)
else:
row.append(w.form)
ws.append(row)
ws = wb.create_sheet('NGSS')
ngss_total = sum(all_ngss.values())
for k in sorted(all_ngss, key=all_ngss.get, reverse=True):
ws.append([k, all_ngss[k], all_ngss[k] / ngss_total])
ws = wb.create_sheet('INF')
inf_total = sum(all_inf.values())
for k in sorted(all_inf, key=all_inf.get, reverse=True):
ws.append([k, all_inf[k], all_inf[k] / inf_total])
ws = wb.create_sheet('CS')
cs_total = sum(all_cs.values())
for k in sorted(all_cs, key=all_cs.get, reverse=True):
ws.append([*k, all_cs[k], all_cs[k] / cs_total])
ws = wb.create_sheet('Info')
ws.append(['Total occ NGSS', ngss_total])
ws.append(['Total occ CS', cs_total])
ws.append(['Total res', len(data)])
wb.save('CS-inf.xlsx')
def write_title(self, worksheet, title=None):
if not title:
return
title = WriteOnlyCell(ws=worksheet, value=title)
title.style = self.title_style
worksheet.append((title, ))
worksheet.merge_cells(start_row=title.row,
start_column=title.col_idx,
end_row=title.row,
end_column=title.col_idx + len(self.columns) - 1)
def make_sheet(self, title: str, headers: List[str]) -> Worksheet:
sheet = self.workbook.create_sheet(title=title)
sheet.freeze_panes = "A2"
sheet.sheet_properties.filterMode = True
cells = []
for header in headers:
header_ = sanitize_text(header)
cell = WriteOnlyCell(sheet, value=header_)
cell.font = self.HEADER_FONT
cell.fill = self.HEADER_FILL
cells.append(cell)
sheet.append(cells)
return sheet
def write_to_excel2_2(datas, file_full_name):
workbook = openpyxl.Workbook(write_only=True) # 默认无sheet sheet名从'Sheet'开始
workbook.create_sheet() # 创建名为'Sheet'的sheet
for data in datas:
worksheet = workbook.create_sheet() # 创建名为'Sheet1'的sheet
alignment_title = Alignment(horizontal='center')
font_title = Font(bold=True, size=10)
font_content = Font(size=9)
keys = data[0].keys()
row = []
for key in keys:
cell = WriteOnlyCell(worksheet,
str(key) if key is not None else '')
cell.font = font_title
cell.alignment = alignment_title
row.append(cell)
worksheet.append(row)
for js in data:
row = []
for key in keys:
cell = WriteOnlyCell(
worksheet,
str(js[key]) if js[key] is not None else '')
cell.font = font_content
cell.alignment = alignment_title
row.append(cell)
worksheet.append(row)
workbook.remove(workbook[workbook.sheetnames[0]]) # 删除名为'Sheet'的sheet
workbook.save(file_full_name)
log_util.log('file_util.write_to_excel', 'filename:%s' % (file_full_name))
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs):
# Create a workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet(title=report.name)
# Create a named style for the header row
readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle")
readlonlyheaderstyle.fill = PatternFill(fill_type="solid",
fgColor="d0ebfb")
wb.add_named_style(readlonlyheaderstyle)
# Run the query
conn = None
try:
conn = create_connection(request.database)
comment = CellComment(force_text(_("Read only")),
"Author",
height=20,
width=80)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
cursor.execute(sql=cls.getSQL(report.sql))
if cursor.description:
# Write header row
header = []
for f in cursor.description:
cell = WriteOnlyCell(ws, value=f[0])
cell.style = "readlonlyheaderstyle"
cell.comment = comment
header.append(cell)
ws.append(header)
# Add an auto-filter to the table
ws.auto_filter.ref = "A1:%s1048576" % get_column_letter(
len(header))
# Write all output rows
for result in cursor.fetchall():
ws.append(
[_getCellValue(i, request=request) for i in result])
# Write the spreadsheet
wb.save(out)
finally:
if conn:
conn.close()
def _write_row(self, sheet_index, row):
from couchexport.export import FormattedRow
sheet = self.tables[sheet_index]
# Source: http://stackoverflow.com/questions/1707890/fast-way-to-filter-illegal-xml-unicode-chars-in-python
dirty_chars = re.compile(
'[\x00-\x08\x0b-\x1f\x7f-\x84\x86-\x9f\ud800-\udfff\ufdd0-\ufddf\ufffe-\uffff]'
)
def get_write_value(value):
if isinstance(value, (int, float)):
return value
if isinstance(value, bytes):
value = value.decode('utf-8')
elif value is not None:
value = str(value)
else:
value = ''
return dirty_chars.sub('?', value)
write_values = [get_write_value(val) for val in row]
cells = [WriteOnlyCell(sheet, val) for val in write_values]
if self.format_as_text:
for cell in cells:
cell.number_format = numbers.FORMAT_TEXT
if isinstance(row, FormattedRow):
for hyperlink_column_index in row.hyperlink_column_indices:
cells[hyperlink_column_index].hyperlink = cells[
hyperlink_column_index].value
cells[hyperlink_column_index].style = 'Hyperlink'
sheet.append(cells)
def test_write_only_cell(WriteOnlyWorksheet):
ws = WriteOnlyWorksheet
c = WriteOnlyCell(ws, value=5)
ws.append([c])
ws.close()
with open(ws._writer.out, "rb") as src:
xml = src.read()
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="8" defaultRowHeight="15"/>
<sheetData>
<row r="1">
<c t="n" r="A1">
<v>5</v>
</c>
</row>
</sheetData>
<pageMargins bottom="1" footer="0.5" header="0.5" left="0.75" right="0.75" top="1"/>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
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 read_only():
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()
cell_total = WriteOnlyCell(ws, "=SUM(A2:A1048576)")
cell_total.number_format = num_format
cell_str = WriteOnlyCell(ws, "")
ws.append([cell_total, cell_str])
for i in range(2**20 - 1):
cell_cur = WriteOnlyCell(ws, i)
cell_cur.number_format = num_format
cell_pdf = WriteOnlyCell(ws, F"=A{i + 2}/A1")
cell_pdf.number_format = pdf_format
ws.append([cell_cur, cell_pdf])
wb.save("ohmygod.xlsx")
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isgenerator(row) and
not isinstance(row, (list, tuple, range))
):
self._invalid_row(row)
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
row_idx = self._max_row
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
el = Element("row", r='%d' % self._max_row)
col_idx = None
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
tree = write_cell(self, cell, styled)
el.append(tree)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
if col_idx:
self._max_col = max(self._max_col, col_idx)
el.set('spans', '1:%d' % col_idx)
try:
self.writer.send(el)
except StopIteration:
self._already_saved()
def write_description(self, worksheet, description=None):
if not description:
return
description = WriteOnlyCell(ws=worksheet, value=description)
self.template_styles.style_cell(description, self.description_style)
worksheet.append((description,))
def write_to_new_file(self, path):
write_work_book = xl.Workbook(write_only=True)
ws = write_work_book.create_sheet('Sample list')
read_work_sheet = self.workbook['Sample list']
normal_style = self.workbook._named_styles['Normal']
for row in read_work_sheet.iter_rows():
write_row = list()
for cell in row:
write_cell = WriteOnlyCell(ws, value=cell.value)
if cell.font != normal_style.font:
write_cell.font = cell.font
if cell.fill is not None:
write_cell.fill = cell.fill
write_row.append(write_cell)
ws.append(write_row)
write_work_book.save(path)
def write_title(self, worksheet, title=None):
if not title:
return
title = WriteOnlyCell(ws=worksheet, value=title)
self.template_styles.style_cell(title, self.title_style)
worksheet.append((title,))
def _values_to_row(self, values, row_idx):
"""
Convert whatever has been appended into a form suitable for work_rows
"""
cell = WriteOnlyCell(self)
for col_idx, value in enumerate(values, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.column = col_idx
cell.row = row_idx
if cell.hyperlink is not None:
cell.hyperlink.ref = cell.coordinate
yield cell
# reset cell if style applied
if cell.has_style or cell.hyperlink:
cell = WriteOnlyCell(self)
def _to_excel(self, value, row_type=None):
if value in self.BLANK_VALUES:
if self.default is not None:
return self.to_excel(self.default, row_type=row_type)
if self.allow_blank:
return None
raise BlankNotAllowed(WriteOnlyCell())
return self.to_excel(value)
def create_cell(self, worksheet, style_set, value=None, row_type=None):
cell = WriteOnlyCell(
worksheet,
value=self._to_excel(value if value is not None else self.default, row_type=row_type)
)
cell_style = self.cell_styles[row_type]
if cell_style:
style_set.style_cell(cell, cell_style)
return cell
def _create_cell(self, worksheet: WriteOnlyWorksheet, data: dict) -> WriteOnlyCell:
cell = WriteOnlyCell(ws=worksheet, value=data.get('value'))
cell.column = data.get('column')
cell.row = data.get('row')
cell.font = self._create_cell_font(data.get('font'))
cell.alignment = self._dict_to_object(data.get('alignment'), Alignment)
cell.border = self._create_cell_borders(data.get('border'))
return cell
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 _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
def members_excel_export():
community = g.community
attributes = [attrgetter(a) for a in MEMBERS_EXPORT_ATTRS]
BaseModel = db.Model
wb = openpyxl.Workbook()
if wb.worksheets:
wb.remove_sheet(wb.active)
ws_title = _("%(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] + "…"
ws = wb.create_sheet(title=ws_title)
row = 0
cells = []
cols_width = []
for _col, label in enumerate(MEMBERS_EXPORT_HEADERS, 1):
value = str(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 = str(value)
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"] = 'attachment;filename="{}"'.format(
filename
)
return response
def create_cell(self, worksheet, value=None):
cell = WriteOnlyCell(
worksheet,
value=self._to_excel(value if value is not None else self.default))
if self.row_style:
cell.style = self.row_style
return cell
