Accessing Excel columns by names in xlwings

Question

In pandas Excel columns can be accessed using names that are assigned in the first row of the sheet. How can this be achieved in xlwings?

Answer 1

You can use Pandas as a converter as of xlwings 0.7.0. for an example workbook like this:

A  B  C
1  4  7
2  5  8
3  6  9

This code will read the table in and allow you to access the data via column headers. The key is the .options(pd.DataFrame, index=False) bit. That particular call will return a Pandas DataFrame, with a default index.

More info on xlwings converters here.

import xlwings as xw
import pandas as pd

def calc():    
    # Create a reference to the calling Excel xw.Workbook
    wb = xw.Workbook.caller() 

    table = xw.Range('A1').table.options(pd.DataFrame, index=False).value

    # Access columns as attributes of the Pandas DataFrame
    print table.A
    print table.B

    # Access columns as column labels of the Pandas DataFrame
    print table['A']
    print table['B']

if __name__ == '__main__':
    path = "test.xlsm"        
    xw.Workbook.set_mock_caller(path)
    calc()

Answer 2

You can use square brackets to access the columns, as suggested here:

import xlwings as xw
wb = xw.Workbook.active()
xw.Range('TableName[ColumnName]').value