XLwings goodies

xlw

import xlwings as xw

import pandas as pd

class xlw:
    """
    xlwings Excel helper class
    parameters:
        file: excel file as posixpath path

    usage::
        xlw(file)
        xlw(file).tables['tbl']
    """
    
    def __init__(self, file):
        self.file = file
        self.tbl = {}

    @property
    def book(self):
        '''
        property for book object
        returns:
            xlwings book object
        '''
        if xw.apps:
            for app in xw.apps:
                if not self.file.name in [i.name for i in app.books]:
                    return xw.Book(self.file)
                else:
                    return xw.books(self.file.name)
        else:
            return xw.Book(self.file)
        
    @property
    def tables(self):
        '''
        property for tables in the book object
        returns:
            DataFrame from table names as dictionary with keys as table_names and values as DataFrame
        usage:
            xlw(file).tables['table_name']
        '''
        for e in self.book.sheets:
            for j in e.tables:
                t=None
                t=j.range.options(pd.DataFrame,index=0,header=1).value
                #remove totals row
                if j.totals_row_range:
                    t=t[:-1]
                self.tbl[j.name]=t
                
        return self.tbl