+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: tables in Excel (/thread-41388.html)
tables in Excel - MaartenRo - Jan-06-2024
Hi,
I have an Excel workbook with two sheets, called "Sheet1" and "Sheet2" with both 2 columns and 4 rows of data. They also have column headers. I would like to have a python script that creates a new sheet named "Sheet3" that puts the data from both sheets in a table that can be filtered and sorted. The tables should be sorted on age from low to high.
Any input is much appreciated!
RE: tables in Excel - Pedroski55 - Jan-06-2024
That sounds fairly easy.
Read all about it here!
ws2 = wb.create_sheet("Mysheet", 0) # insert at first positionThen you can loop through the rows and columns of each sheet to copy values to another sheet.
RE: tables in Excel - sgrey - Jan-06-2024
(Jan-06-2024, 06:35 AM)MaartenRo Wrote: Hi,
I have an Excel workbook with two sheets, called "Sheet1" and "Sheet2" with both 2 columns and 4 rows of data. They also have column headers. I would like to have a python script that creates a new sheet named "Sheet3" that puts the data from both sheets in a table that can be filtered and sorted. The tables should be sorted on age from low to high.
Any input is much appreciated!
I am not really an expert in working with excel files, but it would be something like this, I guess. It makes a new sheet and inserts sorted data into it. I am not sure if you need anything else on top of it. You need to install pandas and openpyxl packages for it.
import pandas as pd
df1 = pd.read_excel('Map1.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('Map1.xlsx', sheet_name='Sheet2')
combined_df = pd.concat([df1, df2])
combined_df = combined_df.sort_values(by='age')
with pd.ExcelWriter('Map1.xlsx', engine='openpyxl', mode='a') as writer:
combined_df.to_excel(writer, sheet_name='Sheet3', index=False)
RE: tables in Excel - deanhystad - Jan-06-2024
The openpyxl documentation has an example of making a table.
https://openpyxl.readthedocs.io/en/latest/worksheet_tables.html