How to add data to existing Excel spreadsheet tables with OpenPyXL.

[chaitanyasinghal]

Thank you for this video it really help me for inserting the data in existing worksheet

[Crossbow]

they changed openpyxl, now tables are as objects, so more like a dictionaries now. (still make sure to name your tables)
this is working for mid 2020:

from openpyxl import load_workbook

filename = 'test.xlsx'

wb = load_workbook(filename)

ws = wb.worksheets[0]

#ws_tables = []

ws['E6'] = 'VW'
ws['F6'] = 'Polo'

tmp = [ws.tables for ws in wb.worksheets]
tables = [{v.name:v} for t in tmp for v in t.values()]

for table in tables:
#ws_tables.append(table)
if list(table.keys())[0] == 'Cars':
table['Cars'].ref = 'E2:F6'
print(table['Cars'].name, table['Cars'].ref)

wb.save(filename)

[tg-ch-]

Thanks friend <3 i'm from Brazil and only you helped in my project. skkskskskkss

[laurentbuhot]

Hello Conny,
Thank you very much, I spent a lot of time to try to do this without success.
Tuto very helpfull, thank you so much

[SG-gmkq]

Thanks man, I am automating my job now.

[hectors]

Nice video but it would be nice to be able to add data without having do know the existing dimensions. Is there a way to get that information programmatically and eliminate the need to open the Excel file each time?

[ahmadmojeed]

Really a nice video, I've been trying to create a dropdown for weeks and i'm still not getting it. moreover, once i do wb.save(filename), it saves only an xlsx file from the folder and not all.

[srikrishnanr]

Can you please explain if i need to insert a vlookup formula in different sheet with diffrent set of cell range is it possible ?

[ramsdawg]

As some of the other commenters have mentioned, it seems like an openpyxl update no longer works with your method. For me, I can print all the table information with print(ws._tables["Table1"]) for example. However, using .name or .ref returns errors and I therefore can't append the table reference.

Example: print(table.name) like you did in the for loop returns an attribute error. Does it still work for you? I'm using MacOS if it matters.

Edit: I've had success creating a new table using the Table() function which also does what I need. The main difference is that it's another step defining the table characteristics to make a new one. Oh well.


from openpyxl.worksheet.table import Table

table_variable = Table('fill out characteristics')
ws.add_table(table_variable)

[alejandrogramajoc]

Hello, OMG! awesome tutorial. However I have a question Is there a way or do you have a tutorial like this on how to use formulas ? I mean what I need, is to add a column but also I need to introduce a formula into to every cell of the column I will add.

[stonesupermaster]

Hello! thanks a lot, I was wondering if this method is possible with google colab, thanks in advance!!

[nagendravishwamitra]

Sir how to create an vlookup function between two work sheet using openpyxl, please help

[Kig_Ama]

How can Ican delete or add any row/columns within Excel sheets using Python, e.g the headers are in the third row and I want to delete the two first rows so that the third row becomes the first one. How do I this with using Python?

[hindyal_masaeed]

can you provide us with the notebook please?

[Galonet]

Hi there!
I've been trying to do it for so many time and I still haven't accomplished. I don't know if it's my Excel or Python version, or some conflict in my computer. I always get a message "We found a problem in the content of the file... " Could you help me?

[martinascoy]

Hello Conny, thank you for your video. I am starting with python.
I tried to applied your code in excel file, but it show me the following error:

print(table.name)
AttributeError: 'str' object has no attribute 'name'

Can you help me please? Thank you!!

[bilalmsd]

how to add the data in the existing name table 'source', I tried to add the data to the named table 'source' but later when I open the file data is there but named table "source" isn't there anymore but data is. I need named table 'source' as well. how can I do that ? @Conny Söderholm

[Mr_Sder]

Conny, could you please explain to me how to add or insert columns without crashing in the table?

[eiriknordstrand]

Great tutorial. Thanks!
This guide works well for openpyxl version <= 3.0.3, but it fails in openpyxl version >= 3.0.4
Why is that?

[aliazarkasb]

I think it works with the previous version of OpenPyXL and running loop in ws._tables doesn't work anymore