def get_xlsx(self, data, book, sheet):
try:
wb = load_workbook(r'./' + book + '.xlsx')
except:
wb = Workbook()
try:
wb.remove_sheet(wb.get_sheet_by_name("Sheet"))
except:
pass
try:
ws = wb.create_sheet(sheet)
except:
self.text.insert(1.0, '工作表名不能重复')
try:
rows = len(data)
cols = len(data[0])
i = 1
for rx in range(rows):
for cx in range(cols):
ws.cell(row=rx + 1, column=cx + 1).value = data[rx][cx]
wb.save(filename=r'./' + book + '.xlsx')
self.text.insert(
1.0,
'\n' + book + '.' + sheet + '(收到' + str(rows - 1) + '条数据)')
except:
wb.save(filename=r'./' + book + '.xlsx')
self.text.insert(1.0, '\n' + book + '.' + sheet + '(收到0条数据)')
return 0
def open_xls_as_xlsx(filename):
# first open using xlrd
book_input = xlrd.open_workbook(filename)
book_output = Workbook()
book_output.remove_sheet(book_output.active)
for sheet_in in book_input.sheets():
nrows = sheet_in.nrows
ncols = sheet_in.ncols
name = sheet_in.name
sheet_out = book_output.create_sheet()
sheet_out.title = name
for row in xrange(0, nrows):
for col in xrange(0, ncols):
colLetter = chr(ord('A')+col)
currCell = colLetter + repr(row+1)
sheet_out[currCell] = sheet_in.cell_value(row, col)
return book_output
class XlReport:
""" Wrapper for openpyxl
Using xlsx because xls has limitations and ods has no good python
library
"""
def __init__(self, file_name="Report"):
""" file_name does not need an extention """
if file_name.endswith('.xls'):
file_name = file_name[:-4]
elif file_name.endswith('xlsx'):
file_name = file_name[:-5]
file_name = file_name.replace(
' ', '_') # Some browsers don't deal well with spaces in downloads
self.workbook = Workbook()
self.workbook.remove_sheet(self.workbook.get_active_sheet())
self.file_name = file_name
def add_sheet(self,
data,
title=None,
header_row=None,
heading=None,
auto_width=False,
max_auto_width=50):
""" Add a sheet with data to workbook
title: sheet name
header_row: List - Column header (bold with bottom border)
heading: Sheet heading (very top of sheet)
auto_width: will ESTIMATE the width of each column by counting
max chars in each column. It will not work with a formula.
max_auto_width: is the max number of characters a column to be
"""
sheet = self.workbook.create_sheet()
if title:
sheet.title = unicode(title)
if heading:
sheet.append([unicode(heading)])
if header_row:
header_row = map(unicode, header_row)
sheet.append(header_row)
row = sheet.get_highest_row()
for i, header_cell in enumerate(header_row):
cell = sheet.cell(row=row - 1, column=i)
cell.style.font.bold = True
cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN
for row in data:
row = map(unicode, row)
sheet.append(row)
if auto_width:
column_widths = []
for row in data:
row = map(unicode, row)
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(cell) > column_widths[i]:
column_widths[i] = len(cell)
else:
column_widths += [len(cell)]
for i, column_width in enumerate(column_widths):
if column_width > 3:
if column_width < max_auto_width:
# * 0.9 estimates a typical variable width font
sheet.column_dimensions[get_column_letter(
i + 1)].width = column_width * 0.9
else:
sheet.column_dimensions[get_column_letter(
i + 1)].width = max_auto_width
def save(self, filename):
self.workbook.save(settings.MEDIA_ROOT + filename)
def as_download(self):
""" Returns a django HttpResponse with the xlsx file """
myfile = StringIO.StringIO()
myfile.write(save_virtual_workbook(self.workbook))
response = HttpResponse(
myfile.getvalue(),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name
response['Content-Length'] = myfile.tell()
return response
class _OpenpyxlWriter(ExcelWriter):
engine = 'openpyxl'
supported_extensions = ('.xlsx', '.xlsm')
def __init__(self, path, **engine_kwargs):
# Use the openpyxl module as the Excel writer.
from openpyxl.workbook import Workbook
super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)
# Create workbook object with default optimized_write=True.
self.book = Workbook()
# Openpyxl 1.6.1 adds a dummy sheet. We remove it.
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
def save(self):
"""
Save workbook to disk.
"""
return self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = self._convert_to_style(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
@classmethod
def _convert_to_style(cls, style_dict):
"""
converts a style_dict to an openpyxl style object
Parameters
----------
style_dict: style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk)
.__setattr__('border_style', nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
class _OpenpyxlWriter(ExcelWriter):
engine = "openpyxl"
supported_extensions = (".xlsx", ".xlsm")
def __init__(self, path, engine=None, mode="w", **engine_kwargs):
# Use the openpyxl module as the Excel writer.
from openpyxl.workbook import Workbook
super().__init__(path, mode=mode, **engine_kwargs)
if self.mode == "a": # Load from existing workbook
from openpyxl import load_workbook
book = load_workbook(self.path)
self.book = book
else:
# Create workbook object with default optimized_write=True.
self.book = Workbook()
if self.book.worksheets:
try:
self.book.remove(self.book.worksheets[0])
except AttributeError:
# compat - for openpyxl <= 2.4
self.book.remove_sheet(self.book.worksheets[0])
def save(self):
"""
Save workbook to disk.
"""
return self.book.save(self.path)
@classmethod
def _convert_to_style(cls, style_dict):
"""
Converts a style_dict to an openpyxl style object.
Parameters
----------
style_dict : style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk).__setattr__(
"border_style", nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
@classmethod
def _convert_to_style_kwargs(cls, style_dict):
"""
Convert a style_dict to a set of kwargs suitable for initializing
or updating-on-copy an openpyxl v2 style object.
Parameters
----------
style_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'font'
'fill'
'border' ('borders')
'alignment'
'number_format'
'protection'
Returns
-------
style_kwargs : dict
A dict with the same, normalized keys as ``style_dict`` but each
value has been replaced with a native openpyxl style object of the
appropriate class.
"""
_style_key_map = {"borders": "border"}
style_kwargs = {}
for k, v in style_dict.items():
if k in _style_key_map:
k = _style_key_map[k]
_conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None)
new_v = _conv_to_x(v)
if new_v:
style_kwargs[k] = new_v
return style_kwargs
@classmethod
def _convert_to_color(cls, color_spec):
"""
Convert ``color_spec`` to an openpyxl v2 Color object.
Parameters
----------
color_spec : str, dict
A 32-bit ARGB hex string, or a dict with zero or more of the
following keys.
'rgb'
'indexed'
'auto'
'theme'
'tint'
'index'
'type'
Returns
-------
color : openpyxl.styles.Color
"""
from openpyxl.styles import Color
if isinstance(color_spec, str):
return Color(color_spec)
else:
return Color(**color_spec)
@classmethod
def _convert_to_font(cls, font_dict):
"""
Convert ``font_dict`` to an openpyxl v2 Font object.
Parameters
----------
font_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'name'
'size' ('sz')
'bold' ('b')
'italic' ('i')
'underline' ('u')
'strikethrough' ('strike')
'color'
'vertAlign' ('vertalign')
'charset'
'scheme'
'family'
'outline'
'shadow'
'condense'
Returns
-------
font : openpyxl.styles.Font
"""
from openpyxl.styles import Font
_font_key_map = {
"sz": "size",
"b": "bold",
"i": "italic",
"u": "underline",
"strike": "strikethrough",
"vertalign": "vertAlign",
}
font_kwargs = {}
for k, v in font_dict.items():
if k in _font_key_map:
k = _font_key_map[k]
if k == "color":
v = cls._convert_to_color(v)
font_kwargs[k] = v
return Font(**font_kwargs)
@classmethod
def _convert_to_stop(cls, stop_seq):
"""
Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
suitable for initializing the ``GradientFill`` ``stop`` parameter.
Parameters
----------
stop_seq : iterable
An iterable that yields objects suitable for consumption by
``_convert_to_color``.
Returns
-------
stop : list of openpyxl.styles.Color
"""
return map(cls._convert_to_color, stop_seq)
@classmethod
def _convert_to_fill(cls, fill_dict):
"""
Convert ``fill_dict`` to an openpyxl v2 Fill object.
Parameters
----------
fill_dict : dict
A dict with one or more of the following keys (or their synonyms),
'fill_type' ('patternType', 'patterntype')
'start_color' ('fgColor', 'fgcolor')
'end_color' ('bgColor', 'bgcolor')
or one or more of the following keys (or their synonyms).
'type' ('fill_type')
'degree'
'left'
'right'
'top'
'bottom'
'stop'
Returns
-------
fill : openpyxl.styles.Fill
"""
from openpyxl.styles import PatternFill, GradientFill
_pattern_fill_key_map = {
"patternType": "fill_type",
"patterntype": "fill_type",
"fgColor": "start_color",
"fgcolor": "start_color",
"bgColor": "end_color",
"bgcolor": "end_color",
}
_gradient_fill_key_map = {"fill_type": "type"}
pfill_kwargs = {}
gfill_kwargs = {}
for k, v in fill_dict.items():
pk = gk = None
if k in _pattern_fill_key_map:
pk = _pattern_fill_key_map[k]
if k in _gradient_fill_key_map:
gk = _gradient_fill_key_map[k]
if pk in ["start_color", "end_color"]:
v = cls._convert_to_color(v)
if gk == "stop":
v = cls._convert_to_stop(v)
if pk:
pfill_kwargs[pk] = v
elif gk:
gfill_kwargs[gk] = v
else:
pfill_kwargs[k] = v
gfill_kwargs[k] = v
try:
return PatternFill(**pfill_kwargs)
except TypeError:
return GradientFill(**gfill_kwargs)
@classmethod
def _convert_to_side(cls, side_spec):
"""
Convert ``side_spec`` to an openpyxl v2 Side object.
Parameters
----------
side_spec : str, dict
A string specifying the border style, or a dict with zero or more
of the following keys (or their synonyms).
'style' ('border_style')
'color'
Returns
-------
side : openpyxl.styles.Side
"""
from openpyxl.styles import Side
_side_key_map = {"border_style": "style"}
if isinstance(side_spec, str):
return Side(style=side_spec)
side_kwargs = {}
for k, v in side_spec.items():
if k in _side_key_map:
k = _side_key_map[k]
if k == "color":
v = cls._convert_to_color(v)
side_kwargs[k] = v
return Side(**side_kwargs)
@classmethod
def _convert_to_border(cls, border_dict):
"""
Convert ``border_dict`` to an openpyxl v2 Border object.
Parameters
----------
border_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'left'
'right'
'top'
'bottom'
'diagonal'
'diagonal_direction'
'vertical'
'horizontal'
'diagonalUp' ('diagonalup')
'diagonalDown' ('diagonaldown')
'outline'
Returns
-------
border : openpyxl.styles.Border
"""
from openpyxl.styles import Border
_border_key_map = {
"diagonalup": "diagonalUp",
"diagonaldown": "diagonalDown"
}
border_kwargs = {}
for k, v in border_dict.items():
if k in _border_key_map:
k = _border_key_map[k]
if k == "color":
v = cls._convert_to_color(v)
if k in ["left", "right", "top", "bottom", "diagonal"]:
v = cls._convert_to_side(v)
border_kwargs[k] = v
return Border(**border_kwargs)
@classmethod
def _convert_to_alignment(cls, alignment_dict):
"""
Convert ``alignment_dict`` to an openpyxl v2 Alignment object.
Parameters
----------
alignment_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'horizontal'
'vertical'
'text_rotation'
'wrap_text'
'shrink_to_fit'
'indent'
Returns
-------
alignment : openpyxl.styles.Alignment
"""
from openpyxl.styles import Alignment
return Alignment(**alignment_dict)
@classmethod
def _convert_to_number_format(cls, number_format_dict):
"""
Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
initializer.
Parameters
----------
number_format_dict : dict
A dict with zero or more of the following keys.
'format_code' : str
Returns
-------
number_format : str
"""
return number_format_dict["format_code"]
@classmethod
def _convert_to_protection(cls, protection_dict):
"""
Convert ``protection_dict`` to an openpyxl v2 Protection object.
Parameters
----------
protection_dict : dict
A dict with zero or more of the following keys.
'locked'
'hidden'
Returns
-------
"""
from openpyxl.styles import Protection
return Protection(**protection_dict)
def write_cells(self,
cells,
sheet_name=None,
startrow=0,
startcol=0,
freeze_panes=None):
# Write the frame cells using openpyxl.
sheet_name = self._get_sheet_name(sheet_name)
_style_cache = {}
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
if _validate_freeze_panes(freeze_panes):
wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1,
column=freeze_panes[1] + 1)
for cell in cells:
xcell = wks.cell(row=startrow + cell.row + 1,
column=startcol + cell.col + 1)
xcell.value, fmt = self._value_with_fmt(cell.val)
if fmt:
xcell.number_format = fmt
style_kwargs = {}
if cell.style:
key = str(cell.style)
style_kwargs = _style_cache.get(key)
if style_kwargs is None:
style_kwargs = self._convert_to_style_kwargs(cell.style)
_style_cache[key] = style_kwargs
if style_kwargs:
for k, v in style_kwargs.items():
setattr(xcell, k, v)
if cell.mergestart is not None and cell.mergeend is not None:
wks.merge_cells(
start_row=startrow + cell.row + 1,
start_column=startcol + cell.col + 1,
end_column=startcol + cell.mergeend + 1,
end_row=startrow + cell.mergestart + 1,
)
# When cells are merged only the top-left cell is preserved
# The behaviour of the other cells in a merged range is
# undefined
if style_kwargs:
first_row = startrow + cell.row + 1
last_row = startrow + cell.mergestart + 1
first_col = startcol + cell.col + 1
last_col = startcol + cell.mergeend + 1
for row in range(first_row, last_row + 1):
for col in range(first_col, last_col + 1):
if row == first_row and col == first_col:
# Ignore first cell. It is already handled.
continue
xcell = wks.cell(column=col, row=row)
for k, v in style_kwargs.items():
setattr(xcell, k, v)
def print_mat_desag(MATA, MATB, incertitude, UP_list_desag, EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from copy import copy
from time import time
from scipy.sparse import lil_matrix, find
filename = "MAT.xlsx"
wb = Workbook(encoding="mac_roman")
Ws = wb.active
wb.remove_sheet(Ws)
# creation de la matrice A avec sa matrice d'incertitude
Ws = Worksheet(wb, title="Processus")
for UP in UP_list_desag:
line = [UP, UP_list_desag[UP]["origine"]]
line.append(UP_list_desag[UP]["process"])
line.append(str(UP_list_desag[UP]["enfants"]))
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="A")
Ws.freeze_panes = "B2"
mat = MATA.toarray()
header = ["A"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="incertitude A")
Ws.freeze_panes = "B2"
mat = incertitude["technology"]
header = ["incertitude A"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
# Creation de B avec sa matrice d'incertitude
Ws = Worksheet(wb, title="Flux elem")
Ws.freeze_panes = "A2"
header = ["id", "compartment", "substance", "subcompartment"]
Ws.append(header)
for EF in EF_list:
L = [EF_list.index(EF)]
L.append(EF)
Ws.append(L)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="B")
Ws.freeze_panes = "B2"
mat = MATB.toarray()
header = ["B"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat)):
line = [row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title="incertitude B")
Ws.freeze_panes = "B2"
mat = incertitude["intervention"]
header = ["incertitude B"]
for UP in UP_list_desag:
header.append(UP_list_desag[UP]["process"])
Ws.append(header)
for row in range(len(mat[0])):
line = [row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
wb.save(filename)
print "fichier enregistre : MAT.xlsx"
def test_remove_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.remove_sheet(new_sheet)
assert new_sheet not in wb.worksheets
ws4.cell(row=ws_line,
column=ws_col).value = curSheet_delphi.cell(
row=row_line, column=6).value
if curSheet_delphi.cell(
row=row_line, column=2).value == curSheet_delphi.cell(
row=row_line + 1, column=2).value: #same item？
ws_line += 1
# print(str(currentDir+'\\delphi\\'+tableName))
else:
#save the current item table for delphi
tableName = curSheet_delphi.cell(row=row_line,
column=2).value
#print(str(currentDir+'\\delphi\\'+tableName))
#wb.save(filename=str(currentDir+'\\delphi\\'+tableName))
wb.remove_sheet(wb.get_sheet_by_name('Sheet'))
#wb.remove_sheet( )
wb.save(filename=str(currentDir + '\\' + tableName +
'.xlsx'))
#create
wb = Workbook()
ws1 = wb.create_sheet(0)
ws1.title = u'功能匹配度'
ws1.cell(row=1, column=1).value = 'No.'
ws1.cell(row=1, column=2).value = '需求类别'
ws1.cell(row=1, column=3).value = '客户需求'
ws1.cell(row=1,
column=4).value = '第' + str(ws_col - 3) + '位专家'
ws1['A1'].style = 'Accent3'
ws1['B1'].style = 'Accent3'
ws1['C1'].style = 'Accent3'
def export_to_spreadsheet(qs, save_to=None):
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
# styles
header_font = Font(
name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(
name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
thin_black_side = Side(style='thin', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(
horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = '# ### ### ##0'
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
}
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
'number_format': number_format,
}
empty = ""
col_year = 1
col_month = 2
col_dps = 3
col_zs = 4
col_numerator = 5
col_denominator = 6
col_value = 7
col_human = 8
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
wb = Workbook()
wb.remove_sheet(wb.active)
logger.info("exporting {} records".format(qs.count()))
# one sheet per indicator
for indicator in Indicator.objects.all():
ws = wb.create_sheet()
ws.title = "#{}".format(indicator.number)
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
row = 1
# write header
std_write(row, col_year, "Année", header_style)
std_write(row, col_month, "Mois", header_style)
std_write(row, col_dps, "DPS", header_style)
xl_set_col_width(ws, col_dps, 7)
std_write(row, col_zs, "ZS", header_style)
xl_set_col_width(ws, col_zs, 7)
std_write(row, col_numerator, "Numérateur", header_style)
xl_set_col_width(ws, col_numerator, 3)
std_write(row, col_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_denominator, 3)
std_write(row, col_value, "Valeur", header_style)
std_write(row, col_human, "Valeur (affich.)", header_style)
xl_set_col_width(ws, col_human, 3)
row += 1
for record in qs.filter(indicator=indicator).iterator():
std_write(row, col_year, record.period.year, std_style)
std_write(row, col_month, record.period.month, std_style)
std_write(row, col_dps,
getattr(record.entity.get_dps(), 'short_name', empty),
std_style)
std_write(row, col_zs,
getattr(record.entity.get_zs(), 'short_name', empty),
std_style)
std_write(row, col_numerator, record.numerator, std_style)
std_write(row, col_denominator, record.denominator, std_style)
std_write(row, col_value, record.value, std_style)
std_write(row, col_human, record.human(), std_style)
row += 1
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
def export_to_spreadsheet(qs, save_to=None):
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
# styles
header_font = Font(name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
thin_black_side = Side(style='thin', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = '# ### ### ##0'
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
}
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
'number_format': number_format,
}
empty = ""
col_year = 1
col_month = 2
col_dps = 3
col_zs = 4
col_numerator = 5
col_denominator = 6
col_value = 7
col_human = 8
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
wb = Workbook()
wb.remove_sheet(wb.active)
logger.info("exporting {} records".format(qs.count()))
# one sheet per indicator
for indicator in Indicator.objects.all():
ws = wb.create_sheet()
ws.title = "#{}".format(indicator.number)
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
row = 1
# write header
std_write(row, col_year, "Année", header_style)
std_write(row, col_month, "Mois", header_style)
std_write(row, col_dps, "DPS", header_style)
xl_set_col_width(ws, col_dps, 7)
std_write(row, col_zs, "ZS", header_style)
xl_set_col_width(ws, col_zs, 7)
std_write(row, col_numerator, "Numérateur", header_style)
xl_set_col_width(ws, col_numerator, 3)
std_write(row, col_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_denominator, 3)
std_write(row, col_value, "Valeur", header_style)
std_write(row, col_human, "Valeur (affich.)", header_style)
xl_set_col_width(ws, col_human, 3)
row += 1
for record in qs.filter(indicator=indicator).iterator():
std_write(row, col_year, record.period.year, std_style)
std_write(row, col_month, record.period.month, std_style)
std_write(row, col_dps,
getattr(record.entity.get_dps(), 'short_name', empty),
std_style)
std_write(row, col_zs,
getattr(record.entity.get_zs(), 'short_name', empty),
std_style)
std_write(row, col_numerator, record.numerator, std_style)
std_write(row, col_denominator, record.denominator, std_style)
std_write(row, col_value, record.value, std_style)
std_write(row, col_human, record.human(), std_style)
row += 1
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
class XlWorkBook:
def __init__(self):
self.wsByName = {}
self.wsByPyWs = {}
self.wsByXlWs = {}
self.wb = Workbook()
pyws = self.wb.active
name = pyws.title
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
self.activeSheet = name
#--------------------------------------------------------------------
def Read(self,filename):
self.wsByName = {}
self.wsByPyWs = {}
self.wsByXlWs = {}
self.wb = load_workbook(filename)
for item in self.wb.worksheets:
pyws = item
name = item.title
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
self.activeSheet = self.wb.active.title
#--------------------------------------------------------------------
def CreateXlWorkSheet(self,name):
if (name not in self.wsByName):
self.wb.create_sheet(name)
pyws = self.wb.get_sheet_by_name(name)
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
return self.wsByName[name][1]
else:
logging.error('Attempting to create duplicate name in workbook: ' + name)
return None
#--------------------------------------------------------------------
def CreateXlChrtSheet(self,name):
if (name not in self.wsByName):
pycs = self.wb.create_chartsheet(name)
#pycs = self.wb.get_sheet_by_name(name)
xlcs = XlChrtSheet(self.wb,pycs,name)
self.wsByName[name] = (pycs,xlcs)
#self.wsByPyWs[pycs] = (xlcs,name)
self.wsByXlWs[xlcs] = (pycs,name)
return self.wsByName[name][1]
else:
logging.debug.error('Attempting to create duplicate name in workbook: ' + name)
return None
#--------------------------------------------------------------------
# def CreateSheet(self,name):
# if (name not in self.wsByName):
# self.wb.create_sheet(name)
# pyws = self.wb.get_sheet_by_name(name)
# xlws = XlWorkSheet(self.wb,pyws,name)
#
# self.wsByName[name] = (pyws,xlws)
# self.wsByPyWs[pyws] = (xlws,name)
# self.wsByXlWs[xlws] = (pyws,name)
#
# return self.wsByName[name][1]
#
# else:
# debug.error('Attempting to create duplicate name in workbook: ' + name)
# return None
#--------------------------------------------------------------------
def GetActiveSheet(self):
pyws = self.wb.active
name = pyws.title
if (name in self.wsByName):
if (name == self.activeSheet):
return self.wsByName[name][1]
else:
logging.debug('XlWorkBook data corrupt')
else:
logging.debug('XlWorkBook data corrupt')
#--------------------------------------------------------------------
def SetName(self,ws,name):
if (name not in self.wsByName):
xlws = ws
pyws,orig = self.wsByXlWs[xlws]
pyws.title = name
xlws.name = name
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
if (self.activeSheet == orig):
self.activeSheet = name
del self.wsByName[orig]
self.wsByName[name] = (pyws,xlws)
else:
debug.error('Attempting to create duplicate name in workbook: ' + name)
return self.wsByName[name][1]
#--------------------------------------------------------------------
def GetSheetByName(self,name):
return self.wsByName[name][1]
#--------------------------------------------------------------------
def RemoveSheetByName(self,name):
self.wb.remove_sheet(self.wb.get_sheet_by_name(name))
#--------------------------------------------------------------------
def Save(self,name):
self.wb.save(name)
class ExcelGenerator(object):
def __init__(self, publication_extracts: List[PubExtract], language_pack, excel_config):
self.publication_extracts = publication_extracts
self.language_pack = language_pack
self.__CURRENT_ROW = 3
self.__LEFT_COLUMNS = ['B', 'C', 'D', 'E']
self.__RIGHT_COLUMNS = ['G', 'H', 'I', 'J']
self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS
self.excel_config = excel_config
# a new sheet is created for each publication so the program won't
# use the first sheet (it will be empty). Hence, it it removed
self.workbook = Workbook()
self.workbook.remove_sheet(worksheet=self.workbook.get_active_sheet())
def create_excel_doc(self):
if len(self.publication_extracts) == 0:
return
print('creating excel document...')
for publication_extract in self.publication_extracts:
print(f"preparing mwb({self.language_pack.lang_key}) {publication_extract.pub_month}/{publication_extract.pub_year}", end=' ')
self._add_populated_sheet(publication_extract)
print('[\033[92mOK\033[0m]')
file_name = 'WREX_{}_{}.xlsx'.format(datetime.now().strftime("%m-%d-%Y_%H:%M"), self.language_pack.lang_key)
self.workbook.save(file_name)
print('done...', f"saved to '{file_name}'")
def _add_populated_sheet(self, publication_extract: PubExtract):
sheet = self.workbook.create_sheet(publication_extract.pub_name)
meetings_count = 0
self._insert_sheet_title(
sheet,
self.language_pack.get_month_name(publication_extract.pub_month) + ' ' + publication_extract.pub_year)
for meeting in publication_extract.meetings:
self._insert_header_content(meeting.week_span, sheet)
self._insert_section(meeting.treasures_section, sheet)
self._insert_section(meeting.ministry_section, sheet)
self._insert_section(meeting.christian_section, sheet)
self._insert_footer_content(sheet)
meetings_count += 1
if meetings_count == 3:
self.__CURRENT_ROW = 5
self.__ACTIVE_COLUMNS = self.__RIGHT_COLUMNS
self._finalize_styling(sheet)
# reset indexes and make them ready for the next sheet
self.__CURRENT_ROW = 3
self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS
def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__LEFT_COLUMNS[0] + str(current_row)
end_cell = self.__RIGHT_COLUMNS[3] + str(current_row)
sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 2
self._style_sheet_title(sheet)
def _style_sheet_title(self, sheet: Worksheet):
cell = self.__ACTIVE_COLUMNS[0] + str(3)
sheet[cell].font = Font(bold=True, size=self.excel_config.SHEET_TITLE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='center', vertical='center')
def _insert_header_content(self, week_span: str, sheet: Worksheet):
# week span
week_span_row = self.__CURRENT_ROW
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = re.sub('[-|–]', ' – ', week_span)
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# chairman
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.chairman
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# opening prayer
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.opening_prayer
self.__CURRENT_ROW += 1
self._style_header_content(week_span_row, sheet)
def _style_header_content(self, week_span_row: int, sheet: Worksheet):
alignment = Alignment(horizontal='left', vertical='center')
border = Border(bottom=Side(border_style='thin'))
# week span
cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = alignment
# chairman
cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row + 1)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 1) # last cell
sheet[cell].border = border
# opening prayer
cell = self.__ACTIVE_COLUMNS[2] + str(week_span_row + 2)
sheet[cell].font = Font(size=self.excel_config.SMALL_FONT_SIZE)
sheet[cell].alignment = alignment
cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 2) # last cell
sheet[cell].border = border
def _insert_section_title(self, meeting_section: MeetingSection, sheet: Worksheet):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
sheet[start_cell] = meeting_section.title
# by default the right most cell at which merging cells ends is on the 5th column (including the offset)
end_cell = self.__ACTIVE_COLUMNS[3] + current_row
# but if the section is 'IMPROVE_IN_MINISTRY' and the user requests hall-dividing
# rows then it changes to the 3rd row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and \
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
self._insert_hall_divider(sheet, meeting_section.section_kind)
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section_title(self.__CURRENT_ROW, sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
def _style_section_title(self, title_row: int, sheet: Worksheet, section_kind: SectionKind):
if section_kind == SectionKind.TREASURES:
bg_color = self.excel_config.TREASURES_SECTION_TITLE_BG_COLOR
elif section_kind == SectionKind.IMPROVE_IN_MINISTRY:
bg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR
else:
bg_color = self.excel_config.CHRISTIAN_LIFE_SECTION_TITLE_BG_COLOR
cell = self.__ACTIVE_COLUMNS[0] + str(title_row)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='left', vertical='center')
sheet[cell].fill = PatternFill(patternType='solid', fgColor=bg_color)
sheet[cell].border = Border(top=Side(border_style='thin'))
def _insert_hall_divider(self, sheet, section_kind: SectionKind):
current_row = str(self.__CURRENT_ROW)
main_hall_cell = self.__ACTIVE_COLUMNS[2] + current_row
second_hall_cell = self.__ACTIVE_COLUMNS[3] + current_row
sheet[main_hall_cell] = self.language_pack.main_hall
sheet[second_hall_cell] = self.language_pack.second_hall
self._style_hall_divider(self.__CURRENT_ROW, sheet, section_kind)
def _style_hall_divider(self, divider_row: int, sheet: Worksheet, section_kind: SectionKind):
main_hall_cell = self.__ACTIVE_COLUMNS[2] + str(divider_row)
second_hall_cell = self.__ACTIVE_COLUMNS[3] + str(divider_row)
font = Font(bold=False, size=self.excel_config.SMALL_FONT_SIZE)
alignment = Alignment(horizontal='center', vertical='center')
if section_kind == SectionKind.IMPROVE_IN_MINISTRY:
fg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR
else:
fg_color = self.excel_config.DEFAULT_BG_COLOR
fill = PatternFill(patternType='solid', fgColor=fg_color)
border = Border(top=Side(border_style='thin'))
sheet[main_hall_cell].font = font
sheet[main_hall_cell].alignment = alignment
sheet[main_hall_cell].fill = fill
sheet[main_hall_cell].border = border
sheet[second_hall_cell].font = font
sheet[second_hall_cell].alignment = alignment
sheet[second_hall_cell].fill = fill
sheet[second_hall_cell].border = border
def _insert_section(self, meeting_section: MeetingSection, sheet: Worksheet):
self._insert_section_title(meeting_section, sheet)
bible_reading = self.language_pack.bible_reading
for presentation in meeting_section.presentations:
if meeting_section.section_kind == SectionKind.TREASURES:
if (bible_reading in presentation) and self.excel_config.INSERT_HALL_DIVISION_LABELS:
self._insert_hall_divider(sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[1] + current_row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and\
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
else:
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = presentation
if (bible_reading not in presentation) or \
(bible_reading in presentation and not self.excel_config.INSERT_HALL_DIVISION_LABELS):
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section(self.__CURRENT_ROW, sheet)
self.__CURRENT_ROW += 1
def _style_section(self, presentation_row: int, sheet: Worksheet):
cell = self.__ACTIVE_COLUMNS[1] + str(presentation_row)
border = Border(bottom=Side(border_style='thin'))
alignment = Alignment(horizontal='center', vertical='center')
sheet[cell].font = Font(size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='left', vertical='center')
sheet[cell].border = border
# underline and center the last two cells of a presentation row (that is where presenter names go)
cell = self.__ACTIVE_COLUMNS[2] + str(presentation_row)
sheet[cell].border = border
sheet[cell].alignment = alignment
cell = self.__ACTIVE_COLUMNS[3] + str(presentation_row)
sheet[cell].border = border
sheet[cell].alignment = alignment
def _insert_footer_content(self, sheet: Worksheet):
footer_row = self.__CURRENT_ROW
current_row = str(self.__CURRENT_ROW)
cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[cell] = self.language_pack.reader
self.__CURRENT_ROW += 1
current_row = str(self.__CURRENT_ROW)
cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[cell] = self.language_pack.concluding_prayer
self.__CURRENT_ROW += 3
self._style_footer_content(footer_row, sheet)
def _style_footer_content(self, footer_row: int, sheet: Worksheet):
font = Font(size=self.excel_config.SMALL_FONT_SIZE)
alignment = Alignment(horizontal='left', vertical='center')
border = Border(bottom=Side(border_style='thin'))
cell = self.__ACTIVE_COLUMNS[2] + str(footer_row)
sheet[cell].font = font
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(footer_row)
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[2] + str(footer_row + 1)
sheet[cell].font = font
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(footer_row + 1)
sheet[cell].border = border
def _finalize_styling(self, sheet: Worksheet):
sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH)
sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42
sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42
for rows in sheet.iter_rows(min_row=3):
for row in rows:
sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
def saveTableToExelle(rFilesDir):
# get reports from FilingSummary
reports = []
try:
fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml"))
for rElt in fsdoc.iter(tag="Report"):
reports.append(Report(rElt.findtext("LongName"),
rElt.findtext("ShortName"),
rElt.findtext("HtmlFileName")))
except (EnvironmentError,
etree.LxmlError) as err:
print("FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err))
wb = Workbook(encoding='utf-8')
# remove predefined sheets
for sheetName in wb.get_sheet_names():
ws = wb.get_sheet_by_name(sheetName)
if ws is not None:
wb.remove_sheet(ws)
sheetNames = set() # prevent duplicates
for reportNum, report in enumerate(reports):
sheetName = report.shortName[:31] # max length 31 for excel title
if sheetName in sheetNames:
sheetName = sheetName[:31-len(str(reportNum))] + str(reportNum)
sheetNames.add(sheetName)
ws = wb.create_sheet(title=sheetName)
try:
# doesn't detect utf-8 encoding the normal way, pass it a string
#htmlSource = ''
#with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh:
# htmlSource = fh.read()
#rdoc = html.document_fromstring(htmlSource)
rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName))
row = -1
mergedAreas = {} # colNumber: (colspan,lastrow)
for tableElt in rdoc.iter(tag="table"):
# skip pop up tables
if tableElt.get("class") == "authRefData":
continue
if tableElt.getparent().tag == "div":
style = tableElt.getparent().get("style")
if style and displayNonePattern.match(style):
continue
colWidths = {}
for rowNum, trElt in enumerate(tableElt.iter(tag="tr")):
# remove passed mergedAreas
for mergeCol in [col
for col, mergedArea in mergedAreas.items()
if mergedArea[1] > rowNum]:
del mergedAreas[mergeCol]
col = 0
for coltag in ("th", "td"):
for cellElt in trElt.iter(tag=coltag):
if col == 0:
row += 1 # new row
if col in mergedAreas:
col += mergedAreas[col][0] - 1
text = cellElt.text_content()
colspan = intCol(cellElt, "colspan", 1)
rowspan = intCol(cellElt, "rowspan", 1)
#if col not in colWidths:
# colWidths[col] = 10.0 # some kind of default width
for elt in cellElt.iter():
style = elt.get("style")
if style and "width:" in style:
try:
kw, sep, width = style.partition("width:")
if "px" in width:
width, sep, kw = width.partition("px")
width = float(width) * 0.67777777
else:
width = float(width)
colWidths[col] = width
except ValueError:
pass
if rowspan > 1:
mergedAreas[col] = (colspan, row + rowspan - 1)
cell = ws.cell(row=row,column=col)
if text:
cell.value = text
if numberPattern.match(text):
cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT
else:
cell.style.alignment.wrap_text = True
if colspan > 1 or rowspan > 1:
ws.merge_cells(start_row=row, end_row=row+rowspan-1, start_column=col, end_column=col+colspan-1)
cell.style.alignment.vertical = Alignment.VERTICAL_TOP
if coltag == "th":
cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
cell.style.font.bold = True
cell.style.font.size = 9 # some kind of default size
col += colspan
for col, width in colWidths.items():
ws.column_dimensions[get_column_letter(col+1)].width = width
except (EnvironmentError,
etree.LxmlError) as err:
print("{0}: directory {1} error: {2}".format(report.htmlFileName, rFilesDir, err))
wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
def write_excel(excel_name, result_dicts):
from openpyxl.workbook import Workbook
from openpyxl.styles import Alignment
alignment = Alignment(
wrap_text=True, # 自动换行
)
#ExcelWriter,里面封装好了对Excel的写操作
from openpyxl.writer.excel import ExcelWriter
#get_column_letter函数将数字转换为相应的字母，如1-->A,2-->B
from openpyxl.utils import get_column_letter
from openpyxl.reader.excel import load_workbook
if os.path.isfile(excel_name):
# #读取excel2007文件
wb = load_workbook(excel_name)
else:
#新建一个workbook
wb = Workbook()
#设置文件输出路径与名称
dest_filename = excel_name
# # 获取第一个sheet
ws = wb.get_active_sheet()
if ws != None:
wb.remove_sheet(ws)
ws = wb.create_sheet('Sheet1')
#第一个sheet是ws
# ws = wb.worksheets[0]
# #设置ws的名称
# ws.title = "sheet1"
line = 1
print(u'定位写入坐标')
while ws.cell(line, 1).value:
# print(ws.cell("A%s" % line).value)
line += 1
print(u'从第%s行开始写入' % line)
#Title
col = 1
ws.cell(line, col).value = u'Chapter'
col = col + 1
ws.cell(line, col).value = u'ReqId'
col = col + 1
ws.cell(line, col).value = u'Title'
col = col + 1
ws.cell(line, col).value = u'Content'
ws.column_dimensions['D'].width = 50.0
line += 1
for i, result in enumerate(result_dicts):
#print(u'正在写入第%s条数据到excel' % (i+1))
try:
print(u'正在写入 %s' % result['ReqId'])
except UnicodeEncodeError:
clr.print_red_textprint("UnicodeEncodeError")
ws.cell(line, 2).value = result['ReqId']
ws.cell(line, 3).value = result['Title']
if (len(result['Content']) > 300):
ws.cell(line, 4).value = result['Content'][:300] + " ... "
else:
ws.cell(line, 4).value = result['Content']
ws.cell(line, 4).alignment = alignment
line += 1
#最后保存文件
wb.save(filename=dest_filename)
def saveTableToExelle(rFilesDir):
# get reports from FilingSummary
reports = []
try:
fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml"))
for rElt in fsdoc.iter(tag="Report"):
reports.append(
Report(rElt.findtext("LongName"), rElt.findtext("ShortName"),
rElt.findtext("HtmlFileName")))
except (EnvironmentError, etree.LxmlError) as err:
print("FilingSummary.xml: directory {0} error: {1}".format(
rFilesDir, err))
wb = Workbook(encoding='utf-8')
# remove predefined sheets
for sheetName in wb.get_sheet_names():
ws = wb.get_sheet_by_name(sheetName)
if ws is not None:
wb.remove_sheet(ws)
sheetNames = set() # prevent duplicates
for reportNum, report in enumerate(reports):
sheetName = report.shortName[:31] # max length 31 for excel title
if sheetName in sheetNames:
sheetName = sheetName[:31 - len(str(reportNum))] + str(reportNum)
sheetNames.add(sheetName)
ws = wb.create_sheet(title=sheetName)
try:
# doesn't detect utf-8 encoding the normal way, pass it a string
#htmlSource = ''
#with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh:
# htmlSource = fh.read()
#rdoc = html.document_fromstring(htmlSource)
rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName))
row = -1
mergedAreas = {} # colNumber: (colspan,lastrow)
for tableElt in rdoc.iter(tag="table"):
# skip pop up tables
if tableElt.get("class") == "authRefData":
continue
if tableElt.getparent().tag == "div":
style = tableElt.getparent().get("style")
if style and displayNonePattern.match(style):
continue
colWidths = {}
for rowNum, trElt in enumerate(tableElt.iter(tag="tr")):
# remove passed mergedAreas
for mergeCol in [
col for col, mergedArea in mergedAreas.items()
if mergedArea[1] > rowNum
]:
del mergedAreas[mergeCol]
col = 0
for coltag in ("th", "td"):
for cellElt in trElt.iter(tag=coltag):
if col == 0:
row += 1 # new row
if col in mergedAreas:
col += mergedAreas[col][0] - 1
text = cellElt.text_content()
colspan = intCol(cellElt, "colspan", 1)
rowspan = intCol(cellElt, "rowspan", 1)
#if col not in colWidths:
# colWidths[col] = 10.0 # some kind of default width
for elt in cellElt.iter():
style = elt.get("style")
if style and "width:" in style:
try:
kw, sep, width = style.partition(
"width:")
if "px" in width:
width, sep, kw = width.partition(
"px")
width = float(width) * 0.67777777
else:
width = float(width)
colWidths[col] = width
except ValueError:
pass
if rowspan > 1:
mergedAreas[col] = (colspan, row + rowspan - 1)
cell = ws.cell(row=row, column=col)
if text:
cell.value = text
if numberPattern.match(text):
cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT
else:
cell.style.alignment.wrap_text = True
if colspan > 1 or rowspan > 1:
ws.merge_cells(start_row=row,
end_row=row + rowspan - 1,
start_column=col,
end_column=col + colspan - 1)
cell.style.alignment.vertical = Alignment.VERTICAL_TOP
if coltag == "th":
cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
cell.style.font.bold = True
cell.style.font.size = 9 # some kind of default size
col += colspan
for col, width in colWidths.items():
ws.column_dimensions[get_column_letter(col +
1)].width = width
except (EnvironmentError, etree.LxmlError) as err:
print("{0}: directory {1} error: {2}".format(
report.htmlFileName, rFilesDir, err))
wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
class XlReport:
""" Wrapper for openpyxl
Using xlsx because xls has limitations and ods has no good python
library
"""
def __init__(self, file_name="Report"):
""" file_name does not need an extention """
if file_name.endswith('.xls'):
file_name = file_name[:-4]
elif file_name.endswith('xlsx'):
file_name = file_name[:-5]
file_name = file_name.replace(' ', '_') # Some browsers don't deal well with spaces in downloads
self.workbook = Workbook()
self.workbook.remove_sheet(self.workbook.get_active_sheet())
self.file_name = file_name
def add_sheet(self, data, title=None, header_row=None, heading=None, auto_width=False, max_auto_width=50):
""" Add a sheet with data to workbook
title: sheet name
header_row: List - Column header (bold with bottom border)
heading: Sheet heading (very top of sheet)
auto_width: will ESTIMATE the width of each column by counting
max chars in each column. It will not work with a formula.
max_auto_width: is the max number of characters a column to be
"""
sheet = self.workbook.create_sheet()
if title:
sheet.title = unicode(title)
if heading:
sheet.append([unicode(heading)])
if header_row:
header_row = map(unicode, header_row)
sheet.append(header_row)
row = sheet.get_highest_row()
for i, header_cell in enumerate(header_row):
cell = sheet.cell(row=row-1, column=i)
cell.style.font.bold = True
cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN
for row in data:
row = map(unicode, row)
sheet.append(row)
if auto_width:
column_widths = []
for row in data:
row = map(unicode, row)
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(cell) > column_widths[i]:
column_widths[i] = len(cell)
else:
column_widths += [len(cell)]
for i, column_width in enumerate(column_widths):
if column_width > 3:
if column_width < max_auto_width:
# * 0.9 estimates a typical variable width font
sheet.column_dimensions[get_column_letter(i+1)].width = column_width * 0.9
else:
sheet.column_dimensions[get_column_letter(i+1)].width = max_auto_width
def save(self, filename):
self.workbook.save(settings.MEDIA_ROOT + filename)
def as_download(self):
""" Returns a django HttpResponse with the xlsx file """
myfile = StringIO.StringIO()
myfile.write(save_virtual_workbook(self.workbook))
response = HttpResponse(
myfile.getvalue(),
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name
response['Content-Length'] = myfile.tell()
return response
try:
fsdoc = etree.parse(os.path.join(rFilesDir, u"FilingSummary.xml"))
for rElt in fsdoc.iter(tag=u"Report"):
reports.append(Report(rElt.findtext(u"LongName"),
rElt.findtext(u"ShortName"),
rElt.findtext(u"HtmlFileName")))
except (EnvironmentError,
etree.LxmlError), err:
print u"FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err)
wb = Workbook(encoding=u'utf-8')
# remove predefined sheets
for sheetName in wb.get_sheet_names():
ws = wb.get_sheet_by_name(sheetName)
if ws is not None:
wb.remove_sheet(ws)
sheetNames = set() # prevent duplicates
for reportNum, report in enumerate(reports):
sheetName = report.shortName[:31] # max length 31 for excel title
if sheetName in sheetNames:
sheetName = sheetName[:31-len(unicode(reportNum))] + unicode(reportNum)
sheetNames.add(sheetName)
ws = wb.create_sheet(title=sheetName)
try:
# doesn't detect utf-8 encoding the normal way, pass it a string
#htmlSource = ''
#with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh:
# htmlSource = fh.read()
class TSSummary:
colName = \
[
'METRICS',
' Code Based',
' Key Accounts',
' Ericsson',
' Nokia',
' ALU',
' All Others',
' Regional Key Accounts',
' Qualcomm',
' AT&T',
' Sprint',
' Carriers',
' AT&T',
' Sprint',
' T-Mobile',
' Small Cell',
' Qualcomm',
' Intel',
' Parallel',
' SpiderCloud',
' Modular Instruments',
' Qor',
' Ter',
' Semiconductor',
' Air',
' Van',
' Others',
' OTH',
' COB',
' TTT',
' Overhead',
' X4x',
' X1x',
' Team Based',
' DMR',
' MI',
' Total Hours',
' All',
' Permenent',
' Contract',
' Labour',
' Travel',
' Standby',
' FAEs'
]
def __init__(self,fname):
self.filename = fname
self.sslist = [] # list of spreadsheets by week
self.tsdict = {} # dict of timesheet data by week
self.week = None # current week number
self.year = None # year of query
self.swb = None # summary workbook
self.metrics = {} # summary metrics
#---------------------------------------------------------------------
def Process(self,tsdata,year,week,region,team):
self.year = year
self.week = week
self.Sort(tsdata,week)
for i,week in enumerate(self.sslist):
wsDate = Calendar.week[i+1]
self.tsdict[wsDate] = []
for j,ssdata in enumerate(week):
ts = Timesheet(ssdata, wsDate)
ts.ReadFile()
self.tsdict[wsDate].append(ts)
self.createWorkbook(region)
self.writeRawDataSheet(region,team)
self.saveWorkbook()
#---------------------------------------------------------------------
def createWorkbook(self,region):
self.swb = Workbook()
self.swb.create_sheet(region + ' Charts')
self.swb.create_sheet(region + ' Tables')
self.swb.create_sheet(region + ' Metrics')
self.swb.create_sheet(region + ' Data')
self.swb.remove_sheet(self.swb.get_sheet_by_name('Sheet'))
#---------------------------------------------------------------------
def saveWorkbook(self):
self.swb.save(self.filename)
#---------------------------------------------------------------------
def setCell(self,cell,align,fmt,value):
if (align == 'C'):
align = Alignment(horizontal='center',vertical='center')
elif (align == 'L'):
align = Alignment(horizontal='left',vertical='center')
elif (align == 'R'):
align = Alignment(horizontal='right',vertical='center')
else:
align = Alignment(horizontal='right',vertical='center')
side = Side(style='thin')
border = Border(left=side,right=side,top=side,bottom=side)
#style = Style(border=border,alignment=align,number_format=fmt)
#cell.style = style
if (fmt == 'F'):
fmt = '0.00'
cell.number_format = fmt
cell.alignment = align.copy()
cell.border = border.copy()
cell.value = value
#---------------------------------------------------------------------
def flagCell(self,cell):
side = Side(style='medium',color=RED)
border = Border(left=side,right=side,top=side,bottom=side)
#style = Style(border=border)
#cell.style = style
cell.border = border.copy()
#---------------------------------------------------------------------
def writeRawDataSheet(self,region,team):
ws_name = region + ' Data'
ws = self.swb.get_sheet_by_name(ws_name)
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 3
ws.column_dimensions['D'].width = 5
ws.column_dimensions['E'].width = 3
ws.column_dimensions['F'].width = 11
ws.column_dimensions['G'].width = 11
ws.column_dimensions['H'].width = 7
ws.column_dimensions['I'].width = 5
ws.column_dimensions['J'].width = 5
ws.column_dimensions['K'].width = 5
ws.column_dimensions['L'].width = 8
ws.column_dimensions['M'].width = 10
ws.column_dimensions['N'].width = 80
wsRow = 2
wsCol = 2
#-------------------------------------------------------------------
# Process each week
#-------------------------------------------------------------------
for weekIndex in range(1,self.week+1):
wsDate = Calendar.week[weekIndex]
tslist = self.tsdict[wsDate]
metrics = Metrics(wsDate)
#-----------------------------------------------------------------
# Process timesheet specific informaton
#-----------------------------------------------------------------
for ts in tslist:
fae = ts.ssdata.fae
#---------------------------------------------------------------
# Process each entry in the timesheet
#---------------------------------------------------------------
for entry in ts.entries:
self.setCell(ws.cell(row=wsRow,column=wsCol+ 0),'L','G',fae.fullname.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 1),'C','G',fae.laborType.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 2),'C','G',fae.team.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 3),'C','G',fae.loc.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 4),'C','G',ts.ssdata.wsDate.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 5),'C','D',entry.date.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 6),'C','G',entry.code.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 7),'C','G',entry.location.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 8),'C','G',entry.activity.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 9),'C','G',entry.product.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+10),'R','F',entry.hours.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+11),'C','G',entry.workType.GetWSVal())
# Flag as red things to check
if (entry.code.GetVal() == 'OTH'):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 6))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.code.GetVal() == 5):
if (entry.location.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7))
if (entry.activity.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 8))
if (entry.product.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9))
if (entry.location.GetVal() == 128):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.product.GetVal() == 22):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.hours.GetVal() == None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+10))
if (entry.workType.GetVal() == None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+11))
# TODO: if hours == 0 or hours > 12 FLAG
# TODO: Blue Strips
# TODO: Check Cust vs Loc
# TODO: Add Local/Remote
# TODO: Add Local Text (AM-NE, AM-BA, etc)
metrics.Update(fae,entry)
# end of entry
wsRow += 1
#---------------------------------------------------------------
# end of a week
self.metrics[wsDate] = metrics
wsRow += 2
#-----------------------------------------------------------------
# Write out raw metrics
#-----------------------------------------------------------------
#for i in range(1,self.week+1):
# wsDate = Calendar.week[i]
# metrics = self.metrics[wsDate]
# metrics.Log()
wsRow = 2
wsCol = 2
ws_name = region + ' Metrics'
ws = self.swb.get_sheet_by_name(ws_name)
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 10
i = 1
while (i < len(TSSummary.colName)):
self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',TSSummary.colName[i])
i += 1
for fae in team.list:
name = fae.fullname.GetVal()
self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',' ' + name)
i += 1
for i in range(1,self.week+1):
wsDate = Calendar.week[i]
metrics = self.metrics[wsDate]
self.setCell(ws.cell(row= 2,column=wsCol+0+i),'C','G',str(wsDate))
self.setCell(ws.cell(row= 3,column=wsCol+0+i),'C','G',str(i))
self.setCell(ws.cell(row= 6,column=wsCol+0+i),'R','F',metrics.codes.kam.erc)
self.setCell(ws.cell(row= 7,column=wsCol+0+i),'R','F',metrics.codes.kam.nok)
self.setCell(ws.cell(row= 8,column=wsCol+0+i),'R','F',metrics.codes.kam.alu)
self.setCell(ws.cell(row= 9,column=wsCol+0+i),'R','F',metrics.codes.kam.oth)
self.setCell(ws.cell(row=11,column=wsCol+0+i),'R','F',metrics.codes.rka.qcm)
self.setCell(ws.cell(row=12,column=wsCol+0+i),'R','F',metrics.codes.rka.att)
self.setCell(ws.cell(row=13,column=wsCol+0+i),'R','F',metrics.codes.rka.spr)
self.setCell(ws.cell(row=15,column=wsCol+0+i),'R','F',metrics.codes.car.att)
self.setCell(ws.cell(row=16,column=wsCol+0+i),'R','F',metrics.codes.car.spr)
self.setCell(ws.cell(row=17,column=wsCol+0+i),'R','F',metrics.codes.car.tmo)
self.setCell(ws.cell(row=19,column=wsCol+0+i),'R','F',metrics.codes.smc.qcm)
self.setCell(ws.cell(row=20,column=wsCol+0+i),'R','F',metrics.codes.smc.itl)
self.setCell(ws.cell(row=21,column=wsCol+0+i),'R','F',metrics.codes.smc.prw)
self.setCell(ws.cell(row=22,column=wsCol+0+i),'R','F',metrics.codes.smc.spd)
self.setCell(ws.cell(row=24,column=wsCol+0+i),'R','F',metrics.codes.mod.qor)
self.setCell(ws.cell(row=25,column=wsCol+0+i),'R','F',metrics.codes.mod.ter)
self.setCell(ws.cell(row=27,column=wsCol+0+i),'R','F',metrics.codes.sem.air)
self.setCell(ws.cell(row=28,column=wsCol+0+i),'R','F',metrics.codes.sem.van)
self.setCell(ws.cell(row=30,column=wsCol+0+i),'R','F',metrics.codes.oth.oth)
self.setCell(ws.cell(row=31,column=wsCol+0+i),'R','F',metrics.codes.oth.cob)
self.setCell(ws.cell(row=32,column=wsCol+0+i),'R','F',metrics.codes.oth.ttt)
self.setCell(ws.cell(row=34,column=wsCol+0+i),'R','F',metrics.codes.ovr.x4x)
self.setCell(ws.cell(row=35,column=wsCol+0+i),'R','F',metrics.codes.ovr.x1x)
self.setCell(ws.cell(row=37,column=wsCol+0+i),'R','F',metrics.team.dmr)
self.setCell(ws.cell(row=38,column=wsCol+0+i),'R','F',metrics.team.mi)
self.setCell(ws.cell(row=40,column=wsCol+0+i),'R','F',metrics.total.tot)
self.setCell(ws.cell(row=41,column=wsCol+0+i),'R','F',metrics.total.prm)
self.setCell(ws.cell(row=42,column=wsCol+0+i),'R','F',metrics.total.con)
self.setCell(ws.cell(row=43,column=wsCol+0+i),'R','F',metrics.total.lbr)
self.setCell(ws.cell(row=44,column=wsCol+0+i),'R','F',metrics.total.trv)
self.setCell(ws.cell(row=45,column=wsCol+0+i),'R','F',metrics.total.sby)
rowoffs = 0
for fae in team.list:
name = fae.fullname.GetVal()
if (name in metrics.fae.dict):
hours = metrics.fae.dict[name].hours
else:
hours = None
self.setCell(ws.cell(row=47+rowoffs,column=wsCol+0+i),'R','F',hours)
if (hours == None):
weDate = wsDate + datetime.timedelta(days=4)
sDate = team.dict[name].startDate.GetVal()
tDate = team.dict[name].endDate.GetVal()
if (wsDate >= sDate and weDate <= tDate):
self.flagCell(ws.cell(row=27+rowoffs,column=wsCol+0+i))
rowoffs += 1
#-----------------------------------------------------------------------
def Sort(self,tsdata,week):
self.sslist = []
for i in range(1,week+1):
list = []
dict = {}
wsDate = Calendar.week[i]
dict = tsdata.weeks[wsDate]
for key,value in dict.items():
list.append(value)
self.sslist.append(sorted(list))
def print_mat_desag(MATA, MATB,incertitude, UP_list_desag,EF_list):
from openpyxl.workbook import Workbook
from openpyxl.worksheet import Worksheet
from copy import copy
from time import time
from scipy.sparse import lil_matrix, find
filename = 'MAT.xlsx'
wb = Workbook(encoding = 'mac_roman')
Ws=wb.active
wb.remove_sheet(Ws)
#creation de la matrice A avec sa matrice d'incertitude
Ws = Worksheet(wb, title = 'Processus')
for UP in UP_list_desag:
line=[UP,UP_list_desag[UP]['origine']]
line.append(UP_list_desag[UP]['process'])
line.append(str(UP_list_desag[UP]['enfants']))
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'A')
Ws.freeze_panes = 'B2'
mat=MATA.toarray()
header = ['A']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'incertitude A')
Ws.freeze_panes = 'B2'
mat=incertitude['technology']
header = ['incertitude A']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
#Creation de B avec sa matrice d'incertitude
Ws = Worksheet(wb, title = 'Flux elem')
Ws.freeze_panes = 'A2'
header = ['id','compartment',
'substance',
'subcompartment']
Ws.append(header)
for EF in EF_list:
L=[EF_list.index(EF)]
L.append(EF)
Ws.append(L)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'B')
Ws.freeze_panes = 'B2'
mat=MATB.toarray()
header = ['B']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat)):
line=[row]
for col in range(len(mat[row])):
line.append(mat[row][col])
Ws.append(line)
wb.add_sheet(Ws)
Ws = Worksheet(wb, title = 'incertitude B')
Ws.freeze_panes = 'B2'
mat=incertitude['intervention']
header = ['incertitude B']
for UP in UP_list_desag:
header.append(UP_list_desag[UP]['process'])
Ws.append(header)
for row in range(len(mat[0])):
line=[row]
for col in range(len(mat)):
line.append(mat[col][row])
Ws.append(line)
wb.add_sheet(Ws)
wb.save(filename)
print 'fichier enregistre : MAT.xlsx'
def generate_validation_tally_for(entity, period, save_to=None):
from dmd.models.DataRecords import DataRecord
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
light_gray = 'FFDEDEDE'
# styles
header_font = Font(
name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(
name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
thin_black_side = Side(style='thin', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(
horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
left_alignment = Alignment(
horizontal='left',
vertical='center')
number_format = '# ### ### ##0'
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
}
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
'number_format': number_format,
}
name_style = {
'font': std_font,
'border': std_border,
'alignment': left_alignment,
'number_format': number_format,
}
odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray)
col_number = 1
col_indicator = 2
col_numerator = 3
col_denominator = 4
col_human = 5
col_valid = 6
col_new_numerator = 7
col_new_denominator = 8
last_col = col_new_denominator
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
title = "Validation {dps} {period}".format(
dps=entity.short_name, period=period.strid)
logger.info(title)
wb = Workbook()
wb.remove_sheet(wb.active)
# write a sheet for each ZS
for zs in entity.get_children():
ws = wb.create_sheet()
ws.title = zs.short_name
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
# write header
row = 1
std_write(row, col_number, "#", header_style)
xl_set_col_width(ws, col_number, 1.6)
std_write(row, col_indicator, "Indicateur", header_style)
xl_set_col_width(ws, col_indicator, 37.3)
std_write(row, col_numerator, "Numérateur", header_style)
xl_set_col_width(ws, col_numerator, 2.8)
std_write(row, col_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_denominator, 3)
std_write(row, col_human, "Valeur.", header_style)
std_write(row, col_valid, "OK ?.", header_style)
std_write(row, col_new_numerator, "Numérateur.", header_style)
xl_set_col_width(ws, col_new_numerator, 2.8)
std_write(row, col_new_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_new_denominator, 3)
row += 1
# one row per indicator
for indicator in Indicator.objects.all():
dr = DataRecord.get_or_none(
period=period,
entity=zs,
indicator=indicator,
only_validated=False)
std_write(row, col_number, indicator.number, std_style)
std_write(row, col_indicator, indicator.name, name_style)
std_write(row, col_numerator,
"" if dr is None else dr.numerator, std_style)
std_write(row, col_denominator,
"" if dr is None else dr.denominator, std_style)
std_write(row, col_human,
"" if dr is None else dr.human(), std_style)
std_write(row, col_valid, "", std_style)
std_write(row, col_new_numerator, "", std_style)
std_write(row, col_new_denominator, "", std_style)
row += 1
# apply even/odd style
for r in range(1, row):
if r % 2 == 0:
for c in range(1, last_col + 1):
ws.cell(row=r, column=c).fill = odd_fill
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
class _OpenpyxlWriter(ExcelWriter):
engine = 'openpyxl'
supported_extensions = ('.xlsx', '.xlsm')
def __init__(self, path, engine=None, mode='w', **engine_kwargs):
# Use the openpyxl module as the Excel writer.
from openpyxl.workbook import Workbook
super().__init__(path, mode=mode, **engine_kwargs)
if self.mode == 'a': # Load from existing workbook
from openpyxl import load_workbook
book = load_workbook(self.path)
self.book = book
else:
# Create workbook object with default optimized_write=True.
self.book = Workbook()
if self.book.worksheets:
try:
self.book.remove(self.book.worksheets[0])
except AttributeError:
# compat - for openpyxl <= 2.4
self.book.remove_sheet(self.book.worksheets[0])
def save(self):
"""
Save workbook to disk.
"""
return self.book.save(self.path)
@classmethod
def _convert_to_style(cls, style_dict):
"""
converts a style_dict to an openpyxl style object
Parameters
----------
style_dict : style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk)
.__setattr__('border_style', nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
@classmethod
def _convert_to_style_kwargs(cls, style_dict):
"""
Convert a style_dict to a set of kwargs suitable for initializing
or updating-on-copy an openpyxl v2 style object
Parameters
----------
style_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'font'
'fill'
'border' ('borders')
'alignment'
'number_format'
'protection'
Returns
-------
style_kwargs : dict
A dict with the same, normalized keys as ``style_dict`` but each
value has been replaced with a native openpyxl style object of the
appropriate class.
"""
_style_key_map = {
'borders': 'border',
}
style_kwargs = {}
for k, v in style_dict.items():
if k in _style_key_map:
k = _style_key_map[k]
_conv_to_x = getattr(cls, '_convert_to_{k}'.format(k=k),
lambda x: None)
new_v = _conv_to_x(v)
if new_v:
style_kwargs[k] = new_v
return style_kwargs
@classmethod
def _convert_to_color(cls, color_spec):
"""
Convert ``color_spec`` to an openpyxl v2 Color object
Parameters
----------
color_spec : str, dict
A 32-bit ARGB hex string, or a dict with zero or more of the
following keys.
'rgb'
'indexed'
'auto'
'theme'
'tint'
'index'
'type'
Returns
-------
color : openpyxl.styles.Color
"""
from openpyxl.styles import Color
if isinstance(color_spec, str):
return Color(color_spec)
else:
return Color(**color_spec)
@classmethod
def _convert_to_font(cls, font_dict):
"""
Convert ``font_dict`` to an openpyxl v2 Font object
Parameters
----------
font_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'name'
'size' ('sz')
'bold' ('b')
'italic' ('i')
'underline' ('u')
'strikethrough' ('strike')
'color'
'vertAlign' ('vertalign')
'charset'
'scheme'
'family'
'outline'
'shadow'
'condense'
Returns
-------
font : openpyxl.styles.Font
"""
from openpyxl.styles import Font
_font_key_map = {
'sz': 'size',
'b': 'bold',
'i': 'italic',
'u': 'underline',
'strike': 'strikethrough',
'vertalign': 'vertAlign',
}
font_kwargs = {}
for k, v in font_dict.items():
if k in _font_key_map:
k = _font_key_map[k]
if k == 'color':
v = cls._convert_to_color(v)
font_kwargs[k] = v
return Font(**font_kwargs)
@classmethod
def _convert_to_stop(cls, stop_seq):
"""
Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
suitable for initializing the ``GradientFill`` ``stop`` parameter.
Parameters
----------
stop_seq : iterable
An iterable that yields objects suitable for consumption by
``_convert_to_color``.
Returns
-------
stop : list of openpyxl.styles.Color
"""
return map(cls._convert_to_color, stop_seq)
@classmethod
def _convert_to_fill(cls, fill_dict):
"""
Convert ``fill_dict`` to an openpyxl v2 Fill object
Parameters
----------
fill_dict : dict
A dict with one or more of the following keys (or their synonyms),
'fill_type' ('patternType', 'patterntype')
'start_color' ('fgColor', 'fgcolor')
'end_color' ('bgColor', 'bgcolor')
or one or more of the following keys (or their synonyms).
'type' ('fill_type')
'degree'
'left'
'right'
'top'
'bottom'
'stop'
Returns
-------
fill : openpyxl.styles.Fill
"""
from openpyxl.styles import PatternFill, GradientFill
_pattern_fill_key_map = {
'patternType': 'fill_type',
'patterntype': 'fill_type',
'fgColor': 'start_color',
'fgcolor': 'start_color',
'bgColor': 'end_color',
'bgcolor': 'end_color',
}
_gradient_fill_key_map = {
'fill_type': 'type',
}
pfill_kwargs = {}
gfill_kwargs = {}
for k, v in fill_dict.items():
pk = gk = None
if k in _pattern_fill_key_map:
pk = _pattern_fill_key_map[k]
if k in _gradient_fill_key_map:
gk = _gradient_fill_key_map[k]
if pk in ['start_color', 'end_color']:
v = cls._convert_to_color(v)
if gk == 'stop':
v = cls._convert_to_stop(v)
if pk:
pfill_kwargs[pk] = v
elif gk:
gfill_kwargs[gk] = v
else:
pfill_kwargs[k] = v
gfill_kwargs[k] = v
try:
return PatternFill(**pfill_kwargs)
except TypeError:
return GradientFill(**gfill_kwargs)
@classmethod
def _convert_to_side(cls, side_spec):
"""
Convert ``side_spec`` to an openpyxl v2 Side object
Parameters
----------
side_spec : str, dict
A string specifying the border style, or a dict with zero or more
of the following keys (or their synonyms).
'style' ('border_style')
'color'
Returns
-------
side : openpyxl.styles.Side
"""
from openpyxl.styles import Side
_side_key_map = {
'border_style': 'style',
}
if isinstance(side_spec, str):
return Side(style=side_spec)
side_kwargs = {}
for k, v in side_spec.items():
if k in _side_key_map:
k = _side_key_map[k]
if k == 'color':
v = cls._convert_to_color(v)
side_kwargs[k] = v
return Side(**side_kwargs)
@classmethod
def _convert_to_border(cls, border_dict):
"""
Convert ``border_dict`` to an openpyxl v2 Border object
Parameters
----------
border_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'left'
'right'
'top'
'bottom'
'diagonal'
'diagonal_direction'
'vertical'
'horizontal'
'diagonalUp' ('diagonalup')
'diagonalDown' ('diagonaldown')
'outline'
Returns
-------
border : openpyxl.styles.Border
"""
from openpyxl.styles import Border
_border_key_map = {
'diagonalup': 'diagonalUp',
'diagonaldown': 'diagonalDown',
}
border_kwargs = {}
for k, v in border_dict.items():
if k in _border_key_map:
k = _border_key_map[k]
if k == 'color':
v = cls._convert_to_color(v)
if k in ['left', 'right', 'top', 'bottom', 'diagonal']:
v = cls._convert_to_side(v)
border_kwargs[k] = v
return Border(**border_kwargs)
@classmethod
def _convert_to_alignment(cls, alignment_dict):
"""
Convert ``alignment_dict`` to an openpyxl v2 Alignment object
Parameters
----------
alignment_dict : dict
A dict with zero or more of the following keys (or their synonyms).
'horizontal'
'vertical'
'text_rotation'
'wrap_text'
'shrink_to_fit'
'indent'
Returns
-------
alignment : openpyxl.styles.Alignment
"""
from openpyxl.styles import Alignment
return Alignment(**alignment_dict)
@classmethod
def _convert_to_number_format(cls, number_format_dict):
"""
Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
initializer.
Parameters
----------
number_format_dict : dict
A dict with zero or more of the following keys.
'format_code' : str
Returns
-------
number_format : str
"""
return number_format_dict['format_code']
@classmethod
def _convert_to_protection(cls, protection_dict):
"""
Convert ``protection_dict`` to an openpyxl v2 Protection object.
Parameters
----------
protection_dict : dict
A dict with zero or more of the following keys.
'locked'
'hidden'
Returns
-------
"""
from openpyxl.styles import Protection
return Protection(**protection_dict)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
freeze_panes=None):
# Write the frame cells using openpyxl.
sheet_name = self._get_sheet_name(sheet_name)
_style_cache = {}
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
if _validate_freeze_panes(freeze_panes):
wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1,
column=freeze_panes[1] + 1)
for cell in cells:
xcell = wks.cell(
row=startrow + cell.row + 1,
column=startcol + cell.col + 1
)
xcell.value, fmt = self._value_with_fmt(cell.val)
if fmt:
xcell.number_format = fmt
style_kwargs = {}
if cell.style:
key = str(cell.style)
style_kwargs = _style_cache.get(key)
if style_kwargs is None:
style_kwargs = self._convert_to_style_kwargs(cell.style)
_style_cache[key] = style_kwargs
if style_kwargs:
for k, v in style_kwargs.items():
setattr(xcell, k, v)
if cell.mergestart is not None and cell.mergeend is not None:
wks.merge_cells(
start_row=startrow + cell.row + 1,
start_column=startcol + cell.col + 1,
end_column=startcol + cell.mergeend + 1,
end_row=startrow + cell.mergestart + 1
)
# When cells are merged only the top-left cell is preserved
# The behaviour of the other cells in a merged range is
# undefined
if style_kwargs:
first_row = startrow + cell.row + 1
last_row = startrow + cell.mergestart + 1
first_col = startcol + cell.col + 1
last_col = startcol + cell.mergeend + 1
for row in range(first_row, last_row + 1):
for col in range(first_col, last_col + 1):
if row == first_row and col == first_col:
# Ignore first cell. It is already handled.
continue
xcell = wks.cell(column=col, row=row)
for k, v in style_kwargs.items():
setattr(xcell, k, v)
def generate_validation_tally_for(entity, period, save_to=None):
from dmd.models.DataRecords import DataRecord
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
light_gray = 'FFDEDEDE'
# styles
header_font = Font(name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
thin_black_side = Side(style='thin', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
left_alignment = Alignment(horizontal='left', vertical='center')
number_format = '# ### ### ##0'
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
}
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
'number_format': number_format,
}
name_style = {
'font': std_font,
'border': std_border,
'alignment': left_alignment,
'number_format': number_format,
}
odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray)
col_number = 1
col_indicator = 2
col_numerator = 3
col_denominator = 4
col_human = 5
col_valid = 6
col_new_numerator = 7
col_new_denominator = 8
last_col = col_new_denominator
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
title = "Validation {dps} {period}".format(dps=entity.short_name,
period=period.strid)
logger.info(title)
wb = Workbook()
wb.remove_sheet(wb.active)
# write a sheet for each ZS
for zs in entity.get_children():
ws = wb.create_sheet()
ws.title = zs.short_name
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
# write header
row = 1
std_write(row, col_number, "#", header_style)
xl_set_col_width(ws, col_number, 1.6)
std_write(row, col_indicator, "Indicateur", header_style)
xl_set_col_width(ws, col_indicator, 37.3)
std_write(row, col_numerator, "Numérateur", header_style)
xl_set_col_width(ws, col_numerator, 2.8)
std_write(row, col_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_denominator, 3)
std_write(row, col_human, "Valeur.", header_style)
std_write(row, col_valid, "OK ?.", header_style)
std_write(row, col_new_numerator, "Numérateur.", header_style)
xl_set_col_width(ws, col_new_numerator, 2.8)
std_write(row, col_new_denominator, "Dénominateur", header_style)
xl_set_col_width(ws, col_new_denominator, 3)
row += 1
# one row per indicator
for indicator in Indicator.objects.all():
dr = DataRecord.get_or_none(period=period,
entity=zs,
indicator=indicator,
only_validated=False)
std_write(row, col_number, indicator.number, std_style)
std_write(row, col_indicator, indicator.name, name_style)
std_write(row, col_numerator, "" if dr is None else dr.numerator,
std_style)
std_write(row, col_denominator,
"" if dr is None else dr.denominator, std_style)
std_write(row, col_human, "" if dr is None else dr.human(),
std_style)
std_write(row, col_valid, "", std_style)
std_write(row, col_new_numerator, "", std_style)
std_write(row, col_new_denominator, "", std_style)
row += 1
# apply even/odd style
for r in range(1, row):
if r % 2 == 0:
for c in range(1, last_col + 1):
ws.cell(row=r, column=c).fill = odd_fill
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
time2 = time.time()
print("Creation Time for all Functional Tables was ", time2-time1, "seconds.")
##Function 2: create_tabs
time1 = time.time()
for key in sorted(list(sheet_dict.keys()), reverse = True):
create_tabs(sheet_dict[key], key)
create_tabs(fullTable, 'Headcount Summary Sorted')
time2 = time.time()
#print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable)
print("Creation Time for ALL tabs was ", time2-time1, "seconds.")
#remove 'Sheet' worksheet, that gets created by default
target.remove_sheet(target.get_sheet_by_name("Sheet")) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name
#Make headers bold; format will probably be the later home of functions for number & alignment formatting
import format
format.bold_headers_footers(target)
##Writing that worksheet to a file
##Moved later in file, so Exceptions sheet could be written
end = time.time()
dur = end - start
print("Total processing time", dur)
def funcSheets_check_figures(d, ft):
'''
def test_remove_sheet():
wb = Workbook()
new_sheet = wb.create_sheet(0)
wb.remove_sheet(new_sheet)
assert new_sheet not in wb.worksheets
class _Openpyxl1Writer(ExcelWriter):
engine = 'openpyxl1'
supported_extensions = ('.xlsx', '.xlsm')
openpyxl_majorver = 1
def __init__(self, path, engine=None, **engine_kwargs):
if not openpyxl_compat.is_compat(major_ver=self.openpyxl_majorver):
raise ValueError('Installed openpyxl is not supported at this '
'time. Use {0}.x.y.'.format(
self.openpyxl_majorver))
# Use the openpyxl module as the Excel writer.
from openpyxl.workbook import Workbook
super(_Openpyxl1Writer, self).__init__(path, **engine_kwargs)
# Create workbook object with default optimized_write=True.
self.book = Workbook()
# Openpyxl 1.6.1 adds a dummy sheet. We remove it.
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
def save(self):
"""
Save workbook to disk.
"""
return self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
style = None
if cell.style:
style = self._convert_to_style(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = self.datetime_format
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = self.date_format
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' %
(cletterstart, startrow + cell.row + 1,
cletterend, startrow + cell.mergestart + 1))
# Excel requires that the format of the first cell in a merged
# range is repeated in the rest of the merged range.
if style:
first_row = startrow + cell.row + 1
last_row = startrow + cell.mergestart + 1
first_col = startcol + cell.col + 1
last_col = startcol + cell.mergeend + 1
for row in range(first_row, last_row + 1):
for col in range(first_col, last_col + 1):
if row == first_row and col == first_col:
# Ignore first cell. It is already handled.
continue
colletter = get_column_letter(col)
xcell = wks.cell("%s%s" % (colletter, row))
for field in style.__fields__:
xcell.style.__setattr__(
field, style.__getattribute__(field))
@classmethod
def _convert_to_style(cls, style_dict):
"""
converts a style_dict to an openpyxl style object
Parameters
----------
style_dict: style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk).__setattr__(
'border_style', nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(
num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook() # optimized_write=True)
# open pyxl 1.6.1 adds a dummy sheet remove it
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
"""
Write given formated cells into Excel an excel sheet
Parameters
----------
cells : generator
cell of formated data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
startrow: upper left cell row to dump data frame
startcol: upper left cell column to dump data frame
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writecells_xlsx(cells, sheet_name, startrow, startcol)
else:
self._writecells_xls(cells, sheet_name, startrow, startcol)
def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):
from openpyxl.cell import get_column_letter
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = CellStyleConverter.to_xlsx(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
def _writecells_xls(self, cells, sheet_name, startrow, startcol):
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.add_sheet(sheet_name)
self.sheets[sheet_name] = wks
style_dict = {}
for cell in cells:
val = _conv_value(cell.val)
num_format_str = None
if isinstance(cell.val, datetime.datetime):
num_format_str = "YYYY-MM-DD HH:MM:SS"
if isinstance(cell.val, datetime.date):
num_format_str = "YYYY-MM-DD"
stylekey = json.dumps(cell.style)
if num_format_str:
stylekey += num_format_str
if stylekey in style_dict:
style = style_dict[stylekey]
else:
style = CellStyleConverter.to_xls(cell.style, num_format_str)
style_dict[stylekey] = style
if cell.mergestart is not None and cell.mergeend is not None:
wks.write_merge(startrow + cell.row,
startrow + cell.mergestart,
startcol + cell.col,
startcol + cell.mergeend,
val, style)
else:
wks.write(startrow + cell.row,
startcol + cell.col,
val, style)
print("Creation Time for all Functional Tables was ", time2 - time1,
"seconds.")
##Function 2: create_tabs
time1 = time.time()
for key in sorted(sheet_dict.keys(), reverse=True):
create_tabs(sheet_dict[key], key)
create_tabs(fullTable, 'Headcount Summary Sorted')
time2 = time.time()
#print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable)
print("Creation Time for ALL tabs was ", time2 - time1, "seconds.")
#remove 'Sheet' worksheet, that gets created by default
target.remove_sheet(
target.get_sheet_by_name("Sheet")
) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name
#Make headers bold; format will probably be the later home of functions for number & alignment formatting
# import format
# format.bold_headers_footers(target)
##Writing that worksheet to a file
##Moved later in file, so Exceptions sheet could be written
end = time.time()
dur = end - start
print("Total processing time", dur)
def funcSheets_check_figures(d, ft):
class _OpenpyxlWriter(ExcelWriter):
engine = 'openpyxl'
supported_extensions = ('.xlsx', '.xlsm')
def __init__(self, path, engine=None, **engine_kwargs):
# Use the openpyxl module as the Excel writer.
from openpyxl.workbook import Workbook
super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)
# Create workbook object with default optimized_write=True.
self.book = Workbook()
# Openpyxl 1.6.1 adds a dummy sheet. We remove it.
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
def save(self):
"""
Save workbook to disk.
"""
return self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
# Write the frame cells using openpyxl.
from openpyxl.cell import get_column_letter
sheet_name = self._get_sheet_name(sheet_name)
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
style = None
if cell.style:
style = self._convert_to_style(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = self.datetime_format
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = self.date_format
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
# Excel requires that the format of the first cell in a merged
# range is repeated in the rest of the merged range.
if style:
first_row = startrow + cell.row + 1
last_row = startrow + cell.mergestart + 1
first_col = startcol + cell.col + 1
last_col = startcol + cell.mergeend + 1
for row in range(first_row, last_row + 1):
for col in range(first_col, last_col + 1):
if row == first_row and col == first_col:
# Ignore first cell. It is already handled.
continue
colletter = get_column_letter(col)
xcell = wks.cell("%s%s" % (colletter, row))
for field in style.__fields__:
xcell.style.__setattr__(
field, style.__getattribute__(field))
@classmethod
def _convert_to_style(cls, style_dict):
"""
converts a style_dict to an openpyxl style object
Parameters
----------
style_dict: style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk)
.__setattr__('border_style', nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
class ExcelWriter(object):
"""
Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
openpyxl for xlsx. See DataFrame.to_excel for typical usage.
Parameters
----------
path : string
Path to xls file
"""
def __init__(self, path):
self.use_xlsx = True
if path.endswith('.xls'):
self.use_xlsx = False
import xlwt
self.book = xlwt.Workbook()
self.fm_datetime = xlwt.easyxf(
num_format_str='YYYY-MM-DD HH:MM:SS')
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
else:
from openpyxl.workbook import Workbook
self.book = Workbook() # optimized_write=True)
# open pyxl 1.6.1 adds a dummy sheet remove it
if self.book.worksheets:
self.book.remove_sheet(self.book.worksheets[0])
self.path = path
self.sheets = {}
self.cur_sheet = None
def save(self):
"""
Save workbook to disk
"""
self.book.save(self.path)
def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
"""
Write given formated cells into Excel an excel sheet
Parameters
----------
cells : generator
cell of formated data to save to Excel sheet
sheet_name : string, default None
Name of Excel sheet, if None, then use self.cur_sheet
startrow: upper left cell row to dump data frame
startcol: upper left cell column to dump data frame
"""
if sheet_name is None:
sheet_name = self.cur_sheet
if sheet_name is None: # pragma: no cover
raise Exception('Must pass explicit sheet_name or set '
'cur_sheet property')
if self.use_xlsx:
self._writecells_xlsx(cells, sheet_name, startrow, startcol)
else:
self._writecells_xls(cells, sheet_name, startrow, startcol)
def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):
from openpyxl.cell import get_column_letter
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.create_sheet()
wks.title = sheet_name
self.sheets[sheet_name] = wks
for cell in cells:
colletter = get_column_letter(startcol + cell.col + 1)
xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
xcell.value = _conv_value(cell.val)
if cell.style:
style = CellStyleConverter.to_xlsx(cell.style)
for field in style.__fields__:
xcell.style.__setattr__(field,
style.__getattribute__(field))
if isinstance(cell.val, datetime.datetime):
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
elif isinstance(cell.val, datetime.date):
xcell.style.number_format.format_code = "YYYY-MM-DD"
# merging requires openpyxl latest (works on 1.6.1)
# todo add version check
if cell.mergestart is not None and cell.mergeend is not None:
cletterstart = get_column_letter(startcol + cell.col + 1)
cletterend = get_column_letter(startcol + cell.mergeend + 1)
wks.merge_cells('%s%s:%s%s' % (cletterstart,
startrow + cell.row + 1,
cletterend,
startrow + cell.mergestart + 1))
def _writecells_xls(self, cells, sheet_name, startrow, startcol):
if sheet_name in self.sheets:
wks = self.sheets[sheet_name]
else:
wks = self.book.add_sheet(sheet_name)
self.sheets[sheet_name] = wks
style_dict = {}
for cell in cells:
val = _conv_value(cell.val)
num_format_str = None
if isinstance(cell.val, datetime.datetime):
num_format_str = "YYYY-MM-DD HH:MM:SS"
if isinstance(cell.val, datetime.date):
num_format_str = "YYYY-MM-DD"
stylekey = json.dumps(cell.style)
if num_format_str:
stylekey += num_format_str
if stylekey in style_dict:
style = style_dict[stylekey]
else:
style = CellStyleConverter.to_xls(cell.style, num_format_str)
style_dict[stylekey] = style
if cell.mergestart is not None and cell.mergeend is not None:
wks.write_merge(startrow + cell.row,
startrow + cell.mergestart,
startcol + cell.col,
startcol + cell.mergeend,
val, style)
else:
wks.write(startrow + cell.row,
startcol + cell.col,
val, style)
