CODEWITHSUNDEEP
pythonexcelxlwings
Stephen C
Stephen C

Reputation: 2036

Format Table in xlwings

How do you format a table in xlwings? For example, if I wanted to add the excel style "Light Blue, Table Style Light 2" to a Range object from xlwings.

Since it isn't in the xlwings docs, I believe it would look something like this (after connecting to the wb and everything):

xw.Range('A1').api. #Something goes here

As a follow-up question, how would one learn what sorts of options you have after grabbing the api object? The only places I have seen this done is in other SO questions and here which is basically just a compilation of SO answers to these sorts of questions.

(That way we don't have to keep bothering you, Felix ;) )

Upvotes: 3

Views: 5231

Answers (4)

mouwsy
mouwsy

Reputation: 1943

Updated: My answer is an improvement on Stephen Cowley's answer and KAM's answer, which also integrates some advancement of the xlwings package in the last few years. You can use ws.tables.add() to format a table. Use used_range to select the full range of a worksheet filled with content or ws.range("A1").expand() or ws.range("A1:C5") to more explicitly define a range. See this example:

import xlwings as xw

path = r"test.xlsx"

with xw.App(visible=False):
    wb = xw.Book(path)
    ws = wb.sheets[0]
    ws.tables.add(ws.used_range, table_style_name='TableStyleMedium6')
    
    wb.save(path)
    wb.close()

After formatting the table, the excel file will be saved and closed.

Upvotes: 1

KAM
KAM

Reputation: 71

Adding on to Stephen Cowley's and mouwsy's answers, you can select a specific range of cells for your table instead of just the used range, and format the table in one step.

wb = xw.('filename.xlsx')            # Initialize a WorkBook
sht = wb.sheets['sheet_name']        # Grab the desired Sheet

# Select your custom range and style the table
tbl = sht.tables.add(sht.range('A1:A100'), table_style_name='TableStyleMedium6')

Upvotes: 7

Guoliang
Guoliang

Reputation: 1

doing sheet.range('D1').select() first would help you move from A1 to other cell (like using your mouse) so you can put your table anywhere you want instead of only A1

Upvotes: 0

Stephen C
Stephen C

Reputation: 2036

I found a solution. Sharing it with the community in case anyone else is interested in doing the same thing. Solution is based on a question asked and answered by himself here.

import xlwings as xw

wb = xw.('filename.xlsx')            # Initialize a WorkBook
sht = wb.sheets['sheet_name']        # Grab the desired Sheet
tbl = sht.api.ListObjects.add()      # Adds table to Excel (returning a Table)
tbl.TableStyle = "TableStyleMedium5" # Set table styling

NOTE: The weird part about this is that this puts a table starting wherever your cursor was in the Excel document (last time you saved it while open? Unsure about this). I had no luck trying to change the location of the cursor via Python. So if anyone figures out how to do that, I'd definitely be interested. For example, unless you can change the cursor location, you couldn't use my solution to make two separate tables on the same sheet.

"Workaround" (AKA hack): I opted to save data from the sheet, delete and remake the sheet, and put the data back. On new sheets, your cursor is automatcially placed in A1, so calling the above code after moving the cursor in this way would make A1 (and surrounding cells) into the table, which is what I needed.

Upvotes: 4

Related Questions