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 __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 _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_cell(self,
value,
wrap=False,
shrink_to_fit=False,
bold=False,
unlock=False) -> WriteOnlyCell:
cell = WriteOnlyCell(self.work_sheet, value=value)
if bold:
cell.font = self.font_regular_bold
else:
cell.font = self.font_regular
if wrap:
cell.alignment = self.alignment_wrap_text
elif shrink_to_fit:
cell.alignment = self.alignment_shrink_to_fit
if unlock:
cell.protection = Protection(locked=False)
return cell
def write_dataframe_values_to_excel(worksheet, dataframe, start_row=None, wrap_text=True):
"""Write DataFrame values to Excel worksheet
:param worksheet: target Excel worksheet
:type worksheet: openpyxl.worksheet.worksheet.Worksheet
:param dataframe: source dataframe
:type dataframe: pandas.DataFrame
:param start_row: start row index for writing data
:param wrap_text: wrap text in cell
:type wrap_text: boolean
:return: None
"""
# init styles for values
border_side = Side(border_style='thin', color='000000')
border_style = Border(left=border_side, right=border_side, top=border_side, bottom=border_side)
alignment = Alignment(wrap_text=wrap_text)
for row_idx, row_data in enumerate(dataframe.itertuples()):
values_list = []
row_cells = []
for val in row_data:
# value in dataframe may be a tuple in case of existing multiindex and it cannot be correctly written to
# excel worksheet; so convert the tuple into a list and merge it with values list
if isinstance(val, tuple):
values_list += list(val)
else:
values_list.append(val)
for col_idx, val in enumerate(values_list):
cell = WriteOnlyCell(worksheet)
cell.border = border_style
if isinstance(val, Iterable) and not isinstance(val, string_types):
cell.value = ', '.join([str(v) for v in val])
else:
cell.value = None if isnull(val) else val
cell.alignment = alignment
row_cells.append(cell)
if start_row is None:
worksheet.append(row_cells)
else:
worksheet.insert_rows(start_row, amount=1)
for col_idx, cell in enumerate(row_cells):
new_cell = worksheet.cell(row=start_row, column=col_idx+1)
new_cell.value = cell.value
new_cell.border = border_style
new_cell.alignment = alignment
start_row += 1
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 = 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("\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 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 cell(val):
cell = WriteOnlyCell(ws, value=val)
cell.font = Font(name='Calibri', size=FONT_SIZE)
cell.alignment = Alignment(vertical='center')
return cell
