2

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?

Amani
  • 16,245
  • 29
  • 103
  • 153

4 Answers4

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()
schoolie
  • 452
  • 3
  • 9
0

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
Community
  • 1
  • 1
tobspr
  • 8,200
  • 5
  • 33
  • 46
  • 1
    What do you mean by 'TableName', because all I have is a single Excel sheet with the first row filled with column names. – Amani Apr 28 '16 at 15:46
  • It would be nice if you could give an example with xlwings in your answer how to generate the `TableName` part that you used in the first place. `TableName` represents an _Excel table_ (unfortunately the term _Excel table_ seems to be a bit ambiguous, in Microsoft Excel it means that thing which happens when clicking 'Home' tab -> 'Format as table' in Microsoft Excel). Because the problem is, if `TableName` is not defined (see previous comment), the code doesn't work. – mouwsy Aug 27 '23 at 20:48
0

Here's an answer using only the xlwings python library without providing the table name:

import xlwings as xw

#load excelfile
workbook = xw.Book('name_of_excelfile.xlsx')

#get data from sheet "sheet_name" in "name_of_excelfile.xlsx" workbook
sheet = workbook.sheets['Sheet1']

#store first row(header row) data in a list
header_row = sheet.range('1:1').value

#get index of column named "column_name" from header_row list
column_index = header_row.index('column_name') + 1

#get excel column letter from index of column
column_letter = xw.utils.col_name(column_index)

#get the data of column "column_name" as a list
column_data = sheet.range(f'{column_letter}2').expand('down').value
digitalguy99
  • 443
  • 4
  • 13
0

I think currently the most efficient way is using:

import xlwings as xw

wb = xw.Book("test.xlsx")
ws = wb.sheets[0]

a_col = ws[ws["1:1"].api.Find("column_name").Address].expand("down")
print(a_col.value)

although it would be nice if a single function for a_col would be implementent in xlwings (corresponding to df["column_name"] in pandas).
Replace column_name with the characters (i.e. the column name) you want to find.

mouwsy
  • 1,457
  • 12
  • 20