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