3

Does XLWings allow my to interact with Excel tables (available in Excel 2007 and later via menu or ctrl+t) by table and column names? This does exist in the young, open sourceproject, Pyvot (https://pypi.python.org/pypi/Pyvot). I am hopeful that it is possible in XLWings now or that XLWings will add the functionality, Especially since this open source project is available as a model.

Here is an example from https://pythonhosted.org/Pyvot/tutorial.html. "Pyvot specifically recognizes column names from tables and auto-filters. Pyvot will search all tables in the workbook for a given column name." The example below will return values from visible cells from the City column to a list.

import xl
wb = xl.Workbook(r"c:\temp\cities.xlsx")
wb.get("City").get() #get the column named City
cadvena
  • 1,063
  • 9
  • 17

1 Answers1

5

Square bracket access works, see e.g. here:

For example to get the data of a specific column:

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

Or to get the column including header and Total row, you could do:

xw.Range('TableName[[#All], [ColumnName]]').value
Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • 2
    This seems to be localization dependent, in german version I have to write [#Alle] instead of [#All] and a semicolon instead of a comma. While VBA will only accept the international version, so this may count as bug in xlwings? – Redoute Jun 03 '21 at 11:35
  • @Felix Zumstein, thanks for the answer, but 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, the code doesn't work. – mouwsy Aug 27 '23 at 20:26