xl-tables 2.0.0

Read and write excel files using table descriptors.

Use descriptors to get and set Excel table values.

Update

Version 2.0.0 attempts to support openpyxl in the event you do not have Excel on your machine. This support is incomplete and does not have all of the Constants and methods populated. Switching between backends may not be supported.

Another change is how we import the constants. All constants were moved to xl_tables.constants In older versions you could use xl_tables.xlDouble. Now you must use xl_tables.constants.xlDouble

To change the backend call xl_tables.change_backend(windows=True).

Examples

import xl_tables as xl
import datetime

class MyTable(xl.Table):
    label_first = xl.Constant('First Name', (1, 1))  # Constants initialize their value on Table creation
    first_name = xl.Cell((1, 2))
    label_last = xl.Constant('Last Name', (2, 1))
    last_name = xl.Cell(2, 2)
    label_now = xl.Constant('Now', (3, 1))
    now = xl.DateTime(3, 2)
    label_today = xl.Constant('Today', (4, 1))
    today = xl.Date(4, 2)
    label_time = xl.Constant('Time', (5, 1))
    time = xl.Time(5, 2)

    header = xl.Constant(['Data 1', 'Data 2', 'Data 3'], rows=7, row_length=3)
    array_item = xl.RangeItem('A8:C10')  # Contiguous Range is preferable
    array = xl.Range('A8:C10')
    # array_item = xl.RowItem(8, 9, 10, row_length=3)
    # array = xl.Row(8, 9, 10, row_length=3)

tbl = MyTable()

tbl.first_name = 'John'
tbl.last_name = 'Doe'
tbl.now = datetime.datetime.now()
tbl.today = datetime.datetime.today()
tbl.time = datetime.time(20, 1, 1)  # datetime.datetime.now().time()

tbl.array = [(1, 2, 3),
             (4, 5, 6),
             (7, 8, 9)]

# Make a border around the cells in the table
tbl.array_item.Borders(xl.xlEdgeTop).LineStyle = xl.xlDouble

text = '{lbl1} = {opt1}\n' \
       '{lbl2} = {opt2}\n' \
       '{lbl3} = {now}\n' \
       '{lbl4} = {today}\n' \
       '{lbl5} = {time}\n' \
       '\n' \
       '{header}\n' \
       '{arr}\n'.format(lbl1=tbl.label_first, opt1=tbl.first_name, lbl2=tbl.label_last, opt2=tbl.last_name,
                        lbl3=tbl.label_now, now=tbl.now, lbl4=tbl.label_today, today=tbl.today,
                        lbl5=tbl.label_time, time=tbl.time,
                        header=tbl.get_row_text(tbl.header, delimiter=', '),
                        arr=tbl.get_table_text(tbl.array, delimiter=', '))

with open('person_text.txt', 'w') as f:
    f.write(text)
print('===== Manual Text =====')
print(text)
print('===== End =====')

# Short function provided for this
txt = tbl.get_table_text(tbl.array, header=tbl.header, head={tbl.label_first: tbl.first_name,
                                                             tbl.label_last: tbl.last_name,
                                                             tbl.label_now: tbl.now,
                                                             tbl.label_today: tbl.today,
                                                             tbl.label_time: tbl.time})
print('===== Get Table Text =====')
print(txt)
print('===== End =====')

tbl.save('person.txt')  # 'person.txt' or '.tsv' will save every cell separated by '\t'
tbl.save('person.csv')  # 'person.csv' will save every cell separated by ','
tbl.save('person.xlsx')