def df_write(df, range_name, ws=None):
"""Write a dataframe to a cell.
df_write(df, range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
Range(ws.name, range_name, index=False).value = df # without pd indices
def clearcontents_ws(ws=None):
"""Clear entire worksheet, ex: clearcontents_ws(ws=worksheetobj).
clearcontents_ws(ws=None)
"""
ws = Sheet.active() if ws is None else ws
ws.clear_contents()
def clearcontents_ws(ws=None):
"""Clear entire worksheet, ex: clearcontents_ws(ws=worksheetobj).
clearcontents_ws(ws=None)
"""
ws = Sheet.active() if ws is None else ws
ws.clear_contents()
def clearcontents(range_name, ws=None):
"""Clear a table started in a range cell, ex: clearcontents('a1').
clearcontents(range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
Range(ws.name, range_name).table.clear_contents()
def clear_ws(ws=None):
"""Clear a table started in a range cell, ex: clearcontents('a1').
clear_ws(ws=None)
"""
ws = Sheet.active() if ws is None else ws
ws.clear()
def df_write(df, range_name, ws=None):
"""Write a dataframe to a cell.
df_write(df, range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
Range(ws.name, range_name, index=False).value = df # without pd indices
def clearcontents(range_name, ws=None):
"""Clear a table started in a range cell, ex: clearcontents('a1').
clearcontents(range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
Range(ws.name, range_name).table.clear_contents()
def clear_ws(ws=None):
"""Clear a table started in a range cell, ex: clearcontents('a1').
clear_ws(ws=None)
"""
ws = Sheet.active() if ws is None else ws
ws.clear()
def list_write(range_name, value_list, ws=None):
"""Write a list vertially.
list_write(range_name, value_list, ws=None)
"""
ws = Sheet.active() if ws is None else ws
# Turn list into a column
value_column = [[e] for e in value_list]
Range(ws.name, range_name).value = value_column
def list_read(range_name, ws=None):
"""Read a list vertially.
list_read(range_name, value_list, ws=None)
"""
ws = Sheet.active() if ws is None else ws
# Range(range_name).options(transpose=True).value = value_list
datalist = Range(ws.name, range_name).vertical.value
return datalist
def df_read(range_name, ws=None):
"""Return dataframe from range name and Sheet.; ex: df = df_read('a1').
df_read(range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
data = Range(ws.name, range_name).table.value
df = pd.DataFrame(data[1:], columns=data[0])
return df
def df_read(range_name, ws=None):
"""Return dataframe from range name and Sheet.; ex: df = df_read('a1').
df_read(range_name, ws=None)
"""
ws = Sheet.active() if ws is None else ws
data = Range(ws.name, range_name).table.value
df = pd.DataFrame(data[1:], columns=data[0])
return df
def list_read(range_name, ws=None):
"""Read a list vertially.
list_read(range_name, value_list, ws=None)
"""
ws = Sheet.active() if ws is None else ws
# Range(range_name).options(transpose=True).value = value_list
datalist = Range(ws.name, range_name).vertical.value
return datalist
def list_write(range_name, value_list, ws=None):
"""Write a list vertially.
list_write(range_name, value_list, ws=None)
"""
ws = Sheet.active() if ws is None else ws
# Turn list into a column
value_column = [[e] for e in value_list]
Range(ws.name, range_name).value = value_column
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug("insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'",
len(df), len(df.columns),
str(top_left_cell), Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def insert_one_dataframe(self, df, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.DataFrame` object.
"""
if not len(df):
return
index = cell_specification.get('index', False)
header = cell_specification.get('header', False)
top_left_cell = cell_specification.get('top_left_cell', 'A0')
logger.debug(
"insert %d by %d rows/cols dataframe "
"at cell '%s' in sheet '%s'", len(df), len(df.columns),
str(top_left_cell),
Sheet.active(self.wkb).name)
Range(top_left_cell, index=index, header=header).value = df
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def insert_one_series(self, series, cell_specification):
"""
Populate the current workbook given a single
:class=:`pandas.Series` object.
"""
if not len(series):
return
# contiguous cells
#TODO: (use vertical and horizontal properties of xlwings)
# non-contiguous user-defined cells
for cs in cell_specification.get('cells', []):
ws = cs.get('worksheet') or Sheet.active(self.wkb).name
content = self.jinja_env.from_string(
cs['content']).render(**series)
logger.debug("insert content '%s' at cell '%s' in sheet '%s'",
content, cs['cell'], ws)
Range(ws, cs['cell']).value = content
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
def test_add_after(self):
Sheet.add(after=Sheet.count())
assert_equal(Sheet(Sheet.count()).name, Sheet.active().name)
Sheet.add(after=1)
assert_equal(Sheet(2).name, Sheet.active().name)
def test_clear_active_sheet(self):
Range('G10').value = 22
Sheet.active().clear()
cell = Range('G10').value
assert_equal(cell, None)
def test_activate(self):
Sheet('Sheet2').activate()
assert_equal(Sheet.active().name, 'Sheet2')
Sheet(3).activate()
assert_equal(Sheet.active().index, 3)
def autofit(ws=None):
"""Autofit columns of worksheet
"""
ws = Sheet.active() if ws is None else ws
ws.autofit(axis='columns')
def autofit(ws=None):
"""Autofit columns of worksheet
"""
ws = Sheet.active() if ws is None else ws
ws.autofit(axis='columns')
def apply_render_block(self, render_block, query_context=None,
**kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip()
)
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(
pseries, render_block['cell_specification']
)
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [b for b in self.render_blocks
if b['name'] == block_name][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified"
)
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(
df, render_block['cell_specification']
)
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
def apply_render_block(self, render_block, query_context=None, **kwargs):
"""
Apply a single render block in the specification file.
- `query_context` (mappable or None) is a context used when
rendering the database query with jinja2 (optional).
- **kwargs is used to overwrite any key/value pair
in the render block.
"""
# override render_block key/val with kwargs
render_block.update(kwargs)
logger.info("processing render block '%s'",
render_block.get('name', '<unamed>'))
# query the DB into a pandas DataFrame
if query_context is None:
query_context = dict()
query_template = self.jinja_env.from_string(
render_block['query'].strip())
query = query_template.render(**query_context)
logger.debug("rendered query: \n'''\n%s\n'''", query)
df = pd.read_sql(query, self.db_engine)
logger.debug("query returned %d record(s)", len(df))
# TODO: calculate extra columns and add it to the DataFrame
# activate worksheet if provided
ws_name = render_block['cell_specification'].get('worksheet') or None
if ws_name is not None:
ws2reactivate_name = Sheet.active(self.wkb).name
Sheet(ws_name, wkb=self.wkb).activate()
# apply the render_block, apply recusively included blocks,
# and save the rendered workbook(s) if needed
apply_by_row = render_block.get('apply_by_row', False)
save_as = render_block.get('save_as', None)
if apply_by_row and save_as is not None:
logger.info("%d file(s) to generate", len(df))
if apply_by_row:
for row, pseries in df.iterrows():
self.insert_one_series(pseries,
render_block['cell_specification'])
for item in render_block.get('include', []):
if isinstance(item, dict):
block_name = item.pop('render_block')
override_vars = item
else:
block_name = item
override_vars = {}
block = [
b for b in self.render_blocks
if b['name'] == block_name
][0]
self.apply_render_block(block,
query_context=pseries,
**override_vars)
if save_as is not None:
tpl = save_as['filename']
filename = self.jinja_env.from_string(tpl).render(
**pseries)
self.save_current_wkb(filename)
# save to pdf
if save_as.get('export_pdf', False):
filename_pdf = os.path.splitext(filename)[0] + '.pdf'
if ws_name is None:
logger.error(
"(export to pdf) no worksheet specified")
else:
self.save_current_wkb_as_pdf(filename_pdf, ws_name)
# re-open the template, re-activate the worksheet
self.close_current_wkb()
self.open_template_as_current_wkb()
if ws_name is not None:
Sheet(ws_name, wkb=self.wkb).activate()
else:
self.insert_one_dataframe(df, render_block['cell_specification'])
# TODO: include and save_as in this case
# re-activate former worksheet if needed
if ws_name is not None:
Sheet(ws2reactivate_name, wkb=self.wkb).activate()
