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)
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)
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
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
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()
