def test_number_format(DummyWorksheet, Cell):
ws = DummyWorksheet
ws.parent._number_formats.add("dd--hh--mm")
cell = Cell(ws, column="A", row=1)
cell.number_format = "dd--hh--mm"
assert cell.number_format == "dd--hh--mm"
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
self.ws.formula_attributes[coordinate] = {'t': formula_type}
si = formula.get('si') # Shared group index for shared formulas
if si:
self.ws.formula_attributes[coordinate]['si'] = si
ref = formula.get('ref') # Range for shared formulas
if ref:
self.ws.formula_attributes[coordinate]['ref'] = ref
style = {}
if style_id is not None:
style_id = int(style_id)
style = self.styles[style_id]
column, row = coordinate_from_string(coordinate)
cell = Cell(self.ws, column, row, **style)
self.ws._add_cell(cell)
if value is not None:
if data_type == 'n':
value = cell._cast_numeric(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
else:
if data_type == 'inlineStr':
data_type = 's'
child = element.find(self.INLINE_STRING)
if child is None:
child = element.find(self.INLINE_RICHTEXT)
if child is not None:
value = child.text
if self.guess_types or value is None:
cell.value = value
else:
cell._value=value
cell.data_type=data_type
def test_date_format_on_non_date():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = datetime.now()
cell.value = 'testme'
eq_('testme', cell.value)
def test_date_format_on_non_date():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, "A", 1)
cell.value = datetime.now()
cell.value = "testme"
eq_("testme", cell.value)
def test_time(value, expected):
wb = Workbook(guess_types=True)
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = value
assert cell.value == expected
assert cell.TYPE_NUMERIC == cell.data_type
def underline_border_cell(val, ws):
underline_border = Border(bottom=Side(style='thin'))
c = Cell(ws, value=val)
c.font = Font(size=11, bold=True)
c.border = underline_border
return c
def test_set_get_date():
today = datetime(2010, 1, 18, 14, 15, 20, 1600)
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = today
eq_(today, cell.value)
def test_timedelta():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = timedelta(days=1, hours=3)
assert cell.value == 1.125
assert cell.TYPE_NUMERIC == cell.data_type
def test_timedelta():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = timedelta(days=1, hours=3)
eq_(cell.value, 1.125)
eq_(cell.TYPE_NUMERIC, cell.data_type)
def test_is_date():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, "A", 1)
cell.value = datetime.now()
eq_(cell.is_date(), True)
cell.value = "testme"
eq_("testme", cell.value)
eq_(cell.is_date(), False)
def test_is_date():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = datetime.now()
eq_(cell.is_date(), True)
cell.value = 'testme'
eq_('testme', cell.value)
assert cell.is_date() is False
def put_text(cell: Cell, text, font=None, border=None, alignment=None):
cell.value = text
if font:
cell.font = font
if border:
cell.border = border
if alignment:
cell.alignment = alignment
return cell
def test_is_date():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = datetime.now()
assert cell.is_date() == True
cell.value = 'testme'
assert 'testme' == cell.value
assert cell.is_date() is False
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = -13.5
cell.style.number_format.format_code = '0.00_);[Red]\(0.00\)'
assert cell.is_date() is False
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = -13.5
cell.style = cell.style.copy(number_format=NumberFormat('0.00_);[Red]\(0.00\)'))
assert cell.is_date() is False
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, "A", 1)
cell.value = -13.5
cell.style.number_format.format_code = "0.00_);[Red]\(0.00\)"
eq_(cell.is_date(), False)
def setStyle(ws, value, style):
# Silence the warning about using a composite Style object instead of Font.
with warnings.catch_warnings():
warnings.simplefilter("ignore", UserWarning)
try:
result = [Cell(ws, column='A', row = 1, value=item) for item in value]
for cell in result:
cell.style = style
except TypeError:
result = Cell(ws, column='A', row = 1, value=value)
result.style = style
return result
def test_data_type_check():
cell = Cell(None, "A", 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value(".0e000")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("-0.e-0")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("1E")
eq_(Cell.TYPE_STRING, cell._data_type)
def test_data_type_check():
cell = Cell(None, 'A', 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value('.0e000')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('-0.e-0')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('1E')
eq_(Cell.TYPE_STRING, cell._data_type)
def test_data_type_check():
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value('.0e000')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('-0.e-0')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('1E')
eq_(Cell.TYPE_STRING, cell._data_type)
def test_data_type_check():
ws = build_dummy_worksheet()
cell = Cell(ws, "A", 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value(".0e000")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("-0.e-0")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("1E")
eq_(Cell.TYPE_STRING, cell._data_type)
def test_data_type_check():
class Ws(object):
encoding = 'utf-8'
cell = Cell(Ws(), 'A', 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value('.0e000')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('-0.e-0')
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value('1E')
eq_(Cell.TYPE_STRING, cell._data_type)
def test_data_type_check():
class Ws(object):
encoding = "utf-8"
cell = Cell(Ws(), "A", 1)
cell.bind_value(None)
eq_(Cell.TYPE_NULL, cell._data_type)
cell.bind_value(".0e000")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("-0.e-0")
eq_(Cell.TYPE_NUMERIC, cell._data_type)
cell.bind_value("1E")
eq_(Cell.TYPE_STRING, cell._data_type)
def set_col_widths(self):
from openpyxl.utils.cell import get_column_letter
from openpyxl.cell import Cell
TYPE_STRING = Cell.TYPE_STRING
for idx, width in sorted(self._col_widths.iteritems()):
letter = get_column_letter(idx + 1)
self.sheet.column_dimensions[letter].width = 1 + min(width, 50)
for row in self._rows:
values = []
for val in row:
if val:
value = val.value
cell = Cell(self.sheet, column='A', row=1)
if isinstance(value, basestring):
cell.set_explicit_value(value, data_type=TYPE_STRING)
else:
cell.value = value
cell.style = val.style
else:
cell = val
values.append(cell)
self.sheet.append(values)
self._rows[:] = ()
def print_sales_content(collection, ws):
# Table Header
table_headers = ['Date', 'O.R. #', 'Name', 'Amount', 'Remarks']
table_header_cells = []
for h in table_headers:
c = underline_border_cell(h, ws)
table_header_cells.append(c)
r = [''] + table_header_cells
ws.append(r)
sales_total = 0
for item in collection:
if item.label == 'Cemetery Lot':
amount = item.lot_area * item.price_per_sq_mtr
# elif item.label == 'Cremation': # todo no amount for cremation yet
# amount = 0
elif item.label == 'Columbary':
amount = item.price if item.price is not None else 0
amount_formatted = 'P {:20,.2f}'.format(amount)
amount_formatted_cell = Cell(ws, value=amount_formatted)
amount_formatted_cell.style = Style(alignment=Alignment(horizontal='right'))
client_name = item.client.get_full_name() if item.client is not None else ''
sales_total += amount
ws.append(['', item.date_purchased, item.or_no, client_name, amount_formatted_cell, item.label])
# Sales Total
total_label_cell = Cell(ws, value='TOTAL')
total_label_cell.font = Font(size=12, color='FFFF0000')
total_cell = Cell(ws, value='P {:20,.2f}'.format(sales_total))
total_cell.font = Font(size=12, color='FFFF0000')
total_cell.border = total_border
total_cell.alignment = Alignment(horizontal='right')
ws.append(['', '', '', total_label_cell, total_cell])
def test_illegal_chacters():
from openpyxl.exceptions import IllegalCharacterError
from openpyxl.compat import range
from itertools import chain
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1)
# The bytes 0x00 through 0x1F inclusive must be manually escaped in values.
illegal_chrs = chain(range(9), range(11, 13), range(14, 32))
for i in illegal_chrs:
with pytest.raises(IllegalCharacterError):
cell.value = chr(i)
with pytest.raises(IllegalCharacterError):
cell.value = "A {0} B".format(chr(i))
cell.value = chr(33)
cell.value = chr(9) # Tab
cell.value = chr(10) # Newline
cell.value = chr(13) # Carriage return
cell.value = " Leading and trailing spaces are legal "
def test_initial_value():
cell = Cell(None, 'A', 1, value='17.5')
eq_(cell.TYPE_NUMERIC, cell.data_type)
def process_xls(data, config=None):
header = data['header']
title = header['title']
origin = data['dataOrigin']
book = Workbook()
sheet = book.active
doc_id = unique_id()
files_path = config.get('files', 'path')
if 'logoURL' in header:
try:
response = requests.get(header['logoURL'], stream=True)
logo = Image(response.raw)
logo = Image(logo.image.resize((100, 100)))
except requests.ConnectionError as cerror:
print(cerror, file=sys.stderr)
else:
logo = None
hdr_bkg_color = header['backgroundColor']
header_bkg = PatternFill(fill_type="solid",
start_color=hdr_bkg_color,
end_color=hdr_bkg_color)
colformats = []
coltypes = []
has_formats = False
columns = data.get('columns', [])
try:
for col in columns:
colfmt = col.get('format', None)
coltype = col.get('type', None)
colformats.append(colfmt)
coltypes.append(coltype)
has_formats = True
except TypeError:
pass
if origin == 'array':
rows = data['rows']
cell = Cell(sheet, value=title)
cell.alignment = Alignment(horizontal='center',
vertical='center')
sheet.append(['', '', '', cell])
sheet.merge_cells('A1:C1')
sheet.merge_cells('D1:G1')
for row in rows:
cells = []
for value in row:
cell = Cell(sheet, value=value)
cells.append(cell)
sheet.append(cells)
else:
db = data['database']
sql_query = data['sqlQuery']
url_callback = data['urlCallback']
title = data['title']
columns = data['columns']
"""
conn = pg_connect(host=db['host'],
database=db['name'],
password=db['password'],
user=db['user'])
cursor = conn.cursor()
cursor.execute(sql_query)
"""
index = 0
is_first = True
for row in cursor:
if is_first:
sheet.merge_cells('A1:C1')
sheet.merge_cells('D1:G1')
sheet.append(['', '', '', cell])
if logo:
sheet.add_image(logo, 'A1')
headcells = []
for col in columns:
cell = Cell(sheet, value=col['label'])
cell.fill = header_bkg
coltype = col.get('type', None)
colfmt = col.get('format', None)
columns_format.append(colfmt)
columns_type.append(coltype)
headcells.append(cell)
sheet.append(headcells)
is_first = False
#sheet.row_dimensions[0].height = 300
sheet.row_dimensions[1].height = 72
sheet.append(row)
index += 1
outfile = '{}/{}.xlsx'.format(files_path, doc_id)
book.save(outfile)
return doc_id
def cell(self, column, row):
return Cell(self, column, row)
def test_set_bad_type():
cell = Cell(None, 'A', 1)
cell.set_value_explicit(1, 'q')
def test_data_type_check(value, datatype):
ws = build_dummy_worksheet()
ws.parent._guess_types = True
cell = Cell(ws, 'A', 1)
cell.value = value
assert cell.data_type == datatype
def test_data_type_check(value, datatype):
ws = build_dummy_worksheet()
ws.parent._guess_types = True
cell = Cell(ws, 'A', 1)
cell.value = value
assert cell.data_type == datatype
def __getitem__(self, value):
if self.cell is None:
self.cell = Cell(self, 'A', 1)
return self.cell
def wrapper(*args, **kwargs):
value = f(*args, **kwargs)
cell = Cell(worksheet=Workbook().active, column=0, row=0, value=value)
ret = openpyxl_args.copy() if openpyxl_args else {}
ret.update(value=cell.internal_value if convert else value)
return ret
def setup_class(cls):
wb = Workbook()
ws = Worksheet(wb)
cls.cell = Cell(ws, 'A', 1)
def standard():
c = Cell(None, "A", "0", None)
value = value / divider
corrected_acids.add(
str(row[start_col_idx].value))
if type(value) in [int, float]:
value = value * PARAMETERS['Multiplier']
if data['Type'] == 'Urine' and value != 'n.a.' and row_idx > start_row_idx + 2:
try:
value = float(value) / float(
data['Creatinine'])
except TypeError:
print(
'ERROR: Creatinine value for ID %s is not number.'
% file_id)
amounts[acid_name] = value
if acid_name in acid_norms:
new_cell = Cell(new_ws)
new_cell.value = value
total_cell = Cell(total_ws)
total_cell.value = value
lbound, rbound = acid_norms[acid_name]
if float(value) < float(lbound):
new_cell.fill = yellow_fill
total_cell.fill = yellow_fill
elif float(value) <= float(rbound):
new_cell.fill = green_fill
total_cell.fill = green_fill
else:
new_cell.fill = red_fill
total_cell.fill = red_fill
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
self._col_count += 1
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
if formula_type != "shared":
self.ws.formula_attributes[coordinate] = dict(
formula.attrib)
else:
si = formula.get(
'si') # Shared group index for shared formulas
# The spec (18.3.1.40) defines shared formulae in
# terms of the following:
#
# `master`: "The first formula in a group of shared
# formulas"
# `ref`: "Range of cells which the formula applies
# to." It's a required attribute on the master
# cell, forbidden otherwise.
# `shared cell`: "A cell is shared only when si is
# used and t is `shared`."
#
# Whether to use the cell's given formula or the
# master's depends on whether the cell is shared,
# whether it's in the ref, and whether it defines its
# own formula, as follows:
#
# Shared? Has formula? | In ref Not in ref
# ========= ==============|======== ===============
# Yes Yes | master impl. defined
# No Yes | own own
# Yes No | master impl. defined
# No No | ?? N/A
#
# The ?? is because the spec is silent on this issue,
# though my inference is that the cell does not
# receive a formula at all.
#
# For this implementation, we are using the master
# formula in the two "impl. defined" cases and no
# formula in the "??" case. This choice of
# implementation allows us to disregard the `ref`
# parameter altogether, and does not require
# computing expressions like `C5 in A1:D6`.
# Presumably, Excel does not generate spreadsheets
# with such contradictions.
if si in self.shared_formula_masters:
trans = self.shared_formula_masters[si]
value = trans.translate_formula(coordinate)
else:
self.shared_formula_masters[si] = Translator(
value, coordinate)
style_array = None
if style_id is not None:
style_id = int(style_id)
style_array = self.styles[style_id]
if coordinate:
row, column = coordinate_to_tuple(coordinate)
else:
row, column = self._row_count, self._col_count
cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array)
self.ws._cells[(row, column)] = cell
if value is not None:
if data_type == 'n':
value = _cast_number(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
else:
if data_type == 'inlineStr':
child = element.find(self.INLINE_STRING)
if child is not None:
data_type = 's'
richtext = Text.from_tree(child)
value = richtext.content
if self.guess_types or value is None:
cell.value = value
else:
cell._value = value
cell.data_type = data_type
def setup_class(cls):
class Ws(object):
encoding = 'utf-8'
cls.cell = Cell(Ws(), 'A', 1)
def test_set_bad_type():
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1)
with pytest.raises(ValueError):
cell.set_explicit_value(1, 'q')
def test_quote_prefix(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column="A", row=1, quotePrefix=True)
assert cell.quotePrefix is True
def test_set_bad_type():
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1)
cell.set_explicit_value(1, 'q')
def WriteOnlyCell(ws=None, value=None):
return Cell(worksheet=ws, column='A', row=1, value=value)
def get_value_from_placement_dict(attrib, placement_dict, week):
black_solid_fill = PatternFill("solid", fgColor="3b3b3b")
dark_grid_fill = PatternFill("darkGrid", fgColor="3b3b3b")
wb = Workbook()
ws = wb.active
return_cell = Cell(ws)
return_cell.value == ''
if attrib == 'fields':
return_cell.value == ''
if re.search('\d{1,2}', str(attrib)):
plan_weeks = placement_dict.get('plan_weeks')
fact_weeks = list()
for value in placement_dict['postclick']:
fact_weeks.append(int(value['weeknumber']))
if attrib in plan_weeks:
return_cell.fill = dark_grid_fill
if attrib in fact_weeks:
return_cell.fill = dark_grid_fill
if attrib in plan_weeks and attrib in fact_weeks:
return_cell.fill = black_solid_fill
if attrib in ('platform_site', 'description', 'format', 'plan_impressions',
'plan_reach', 'plan_clicks', 'plan_views', 'plan_budget'):
return_cell.value = placement_dict.get(attrib)
if attrib in ('fact_impressions', 'fact_clicks', "fact_reach",
"fact_views", 'fact_budget'):
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
return_cell.value = value.get(attrib)
if attrib == "plan_cpm":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_impressions"):
return_cell.value = placement_dict.get(
"plan_budget") * 1000 / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpt":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_reach") is not None:
return_cell.value = placement_dict.get(
"plan_budget") * 1000 / placement_dict.get("plan_reach")
else:
return_cell.value = "N/A"
if attrib == "plan_ctr":
if placement_dict.get(
"plan_clicks") is not None and placement_dict.get(
"plan_impressions") is not None:
return_cell.value = placement_dict.get(
"plan_clicks") / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpc":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_clicks") is not None:
return_cell.value = placement_dict.get(
"plan_budget") / placement_dict.get("plan_clicks")
else:
return_cell.value = "N/A"
if attrib == "plan_vtr":
if placement_dict.get("plan_views") != 'N/A' and placement_dict.get(
"plan_impressions") is not None:
return_cell.value = placement_dict.get(
"plan_views") / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpv":
if placement_dict.get(
"plan_budget"
) is not None and placement_dict.get("plan_views") != 'N/A':
return_cell.value = placement_dict.get(
"plan_budget") / placement_dict.get("plan_views")
else:
return_cell.value = "N/A"
if attrib == "fact_cpm":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get(
"fact_budget") * 1000 / value.get("fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpt":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_reach") is not None:
return_cell.value = value.get(
"fact_budget") * 1000 / value.get("fact_reach")
else:
return_cell.value = "N/A"
if attrib == "fact_ctr":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_clicks") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get("fact_clicks") / value.get(
"fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpc":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_clicks") is not None:
return_cell.value = value.get("fact_budget") / value.get(
"fact_clicks")
else:
return_cell.value = "N/A"
if attrib == 'fact_vtr':
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_views") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get("fact_views") / value.get(
"fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpv":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_views") is not None:
return_cell.value = value.get("fact_budget") / value.get(
"fact_views")
else:
return_cell.value = "N/A"
if attrib == 'period':
return_cell.value = str(len(
placement_dict.get('plan_weeks'))) + " weeks"
if attrib == 'fact_impressions_adriver':
if placement_dict.get('adriver_id') is not None:
return_cell.value = get_adriver_value(
placement_dict.get('adriver_id'), week)
else:
return_cell.value = "N/A"
return return_cell
def process_xls(data, config=None):
header = data['header']
title = header['title']
origin = data['dataOrigin']
book = Workbook()
sheet = book.active
doc_id = unique_id()
files_path = config.get('files', 'path')
if 'logoURL' in header:
try:
response = requests.get(header['logoURL'], stream=True)
logo = Image(response.raw)
logo = Image(logo.image.resize((100, 100)))
except requests.ConnectionError as cerror:
print(cerror, file=sys.stderr)
else:
logo = None
hdr_bkg_color = header['backgroundColor']
header_bkg = PatternFill(fill_type="solid",
start_color=hdr_bkg_color,
end_color=hdr_bkg_color)
colformats = []
coltypes = []
has_formats = False
columns = data.get('columns', [])
try:
for col in columns:
colfmt = col.get('format', None)
coltype = col.get('type', None)
colformats.append(colfmt)
coltypes.append(coltype)
has_formats = True
except TypeError:
pass
if origin == 'array':
rows = data['rows']
cell = Cell(sheet, value=title)
cell.alignment = Alignment(horizontal='center',
vertical='center')
sheet.append(['', '', '', cell])
sheet.merge_cells('A1:C1')
sheet.merge_cells('D1:G1')
for row in rows:
cells = []
for value in row:
cell = Cell(sheet, value=value)
cells.append(cell)
sheet.append(cells)
else:
db = data['database']
sql_query = data['sqlQuery']
url_callback = data['urlCallback']
title = data['title']
columns = data['columns']
"""
conn = pg_connect(host=db['host'],
database=db['name'],
password=db['password'],
user=db['user'])
cursor = conn.cursor()
cursor.execute(sql_query)
"""
index = 0
is_first = True
for row in cursor:
if is_first:
sheet.merge_cells('A1:C1')
sheet.merge_cells('D1:G1')
sheet.append(['', '', '', cell])
if logo:
sheet.add_image(logo, 'A1')
headcells = []
for col in columns:
cell = Cell(sheet, value=col['label'])
cell.fill = header_bkg
coltype = col.get('type', None)
colfmt = col.get('format', None)
columns_format.append(colfmt)
columns_type.append(coltype)
headcells.append(cell)
sheet.append(headcells)
is_first = False
#sheet.row_dimensions[0].height = 300
sheet.row_dimensions[1].height = 72
sheet.append(row)
index += 1
outfile = '{}/{}.xlsx'.format(files_path, doc_id)
book.save(outfile)
return doc_id
def cell(self, column, row):
column = get_column_letter(column)
return Cell(self, column, row)
def test_set_bad_type():
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1)
with pytest.raises(ValueError):
cell.set_explicit_value(1, 'q')
def test_pivot_button(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column="A", row=1, pivotButton=True)
assert cell.pivotButton is True
def get_audit_log_workbook(ws, program):
# helper for indicator name column
def _indicator_name(indicator):
if indicator.results_aware_number:
return u'{} {}: {}'.format(
_('Indicator'),
str(indicator.results_aware_number),
str(indicator.name),
)
else:
return u'{}: {}'.format(
_('Indicator'),
str(indicator.name),
)
# helper for result level column
def _result_level(indicator):
if indicator.leveltier_name and indicator.level_display_ontology:
return u'{} {}'.format(
str(indicator.leveltier_name),
str(indicator.level_display_ontology),
)
elif indicator.leveltier_name:
return str(indicator.leveltier_name)
else:
return ''
header = [
Cell(ws, value=_("Date and Time")),
# Translators: Number of the indicator being shown
Cell(ws, value=_('Result Level')),
Cell(ws, value=_('Indicator')),
Cell(ws, value=_('User')),
Cell(ws, value=_('Organization')),
# Translators: Part of change log, indicates the type of change being made to a particular piece of data
Cell(ws, value=_('Change type')),
# Translators: Part of change log, shows what the data looked like before the changes
Cell(ws, value=_('Previous entry')),
# Translators: Part of change log, shows what the data looks like after the changes
Cell(ws, value=_('New entry')),
# Translators: Part of change log, reason for the change as entered by the user
Cell(ws, value=_('Rationale'))
]
title = Cell(ws, value=_("Change log"))
title.font = Font(size=18)
ws.append([title,])
ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=len(header))
subtitle = Cell(ws, value=program.name)
subtitle.font = Font(size=18)
ws.append([subtitle,])
ws.merge_cells(start_row=2, end_row=2, start_column=1, end_column=len(header))
header_font = Font(bold=True)
header_fill = PatternFill('solid', 'EEEEEE')
for h in header:
h.font = header_font
h.fill = header_fill
ws.append(header)
alignment = Alignment(
horizontal='general',
vertical='top',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0
)
for row in program.audit_logs.all().order_by('-date'):
prev_string = u''
for entry in row.diff_list:
if entry['name'] == 'targets':
for k, target in entry['prev'].items():
prev_string += str(target['name']) + u": " + str(target['value']) + u"\r\n"
else:
prev_string += str(entry['pretty_name']) + u": "
prev_string += str(entry['prev'] if entry['prev'] else _('N/A')) + u"\r\n"
new_string = u''
for entry in row.diff_list:
if entry['name'] == 'targets':
for k, target in entry['new'].items():
new_string += str(target['name']) + u": " + str(target['value']) + u"\r\n"
else:
new_string += str(entry['pretty_name']) + u": "
new_string += str(entry['new'] if entry['new'] else _('N/A')) + u"\r\n"
xl_row = [
Cell(ws, value=row.date),
Cell(ws, value=str(_result_level(row.indicator)) if row.indicator else _('N/A')),
Cell(ws, value=str(_indicator_name(row.indicator)) if row.indicator else _('N/A')),
Cell(ws, value=str(row.user.name)),
Cell(ws, value=str(row.organization.name)),
Cell(ws, value=str(row.pretty_change_type)),
Cell(ws, value=str(prev_string)),
Cell(ws, value=str(new_string)),
Cell(ws, value=str(row.rationale))
]
for cell in xl_row:
cell.alignment = alignment
ws.append(xl_row)
for rd in ws.row_dimensions:
rd.auto_size = True
for cd in ws.column_dimensions:
cd.auto_size = True
widths = [20, 12, 50, 20, 15, 20, 40, 40, 40]
for col_no, width in enumerate(widths):
ws.column_dimensions[utils.get_column_letter(col_no + 1)].width = width
return ws
def dummy_cell(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column=1, row=1)
return cell
def cell(self, column, row):
return Cell(self, row=row, column=column)
def mk_cell(value):
cell = Cell(ws)
cell.number_format = '@' # Ensure text cell format
cell.set_explicit_value(value)
return cell
def test_repr():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
assert repr(cell), '<Cell Sheet1.A1>' == 'Got bad repr: %s' % repr(cell)
def test_number_format(DummyWorksheet, Cell):
ws = DummyWorksheet
ws.parent._number_formats.add("dd--hh--mm")
cell = Cell(ws, column="A", row=1, numFmtId=164)
assert cell.number_format == "dd--hh--mm"
from pympler.muppy import print_summary from openpyxl.cell import Cell d = [Cell(None, 'A', 1) for i in range(100000)] print_summary()
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
self.ws.formula_attributes[coordinate] = {'t': formula_type}
si = formula.get('si') # Shared group index for shared formulas
if si:
self.ws.formula_attributes[coordinate]['si'] = si
ref = formula.get('ref') # Range for shared formulas
if ref:
self.ws.formula_attributes[coordinate]['ref'] = ref
style = {}
if style_id is not None:
style_id = int(style_id)
style = self.styles[style_id]
column, row = coordinate_from_string(coordinate)
cell = Cell(self.ws, column, row, **style)
self.ws._add_cell(cell)
if value is not None:
if data_type == 'n':
value = cell._cast_numeric(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
else:
if data_type == 'inlineStr':
data_type = 's'
child = element.find(self.INLINE_STRING)
if child is None:
child = element.find(self.INLINE_RICHTEXT)
if child is not None:
value = child.text
if self.guess_types or value is None:
cell.value = value
else:
cell._value=value
cell.data_type=data_type
def cell(value):
return Cell(Worksheet(Workbook()), value=value)
def style_single_cell(self, cell: Cell, style: Style) -> None:
cell.border = style.border
cell.alignment = style.alignment
cell.font = style.font
def test_initial_value():
ws = build_dummy_worksheet()
cell = Cell(ws, 'A', 1, value='17.5')
assert cell.TYPE_STRING == cell.data_type
def setup_class(cls):
cls.cell = Cell(None, 'A', 1)