Python best library for Excel reports & review of existing code

[MasterOfDestr]

I need to create excel report (update data in existing file) based on variety source data (also excel files). All data will be .xlsx. In general steps that I'll do are:

1.Download settings information;
2.Create "starting" reports based on settings information;
3.Filter & Copy & paste data to created reports (in some of the cases data starts from as example B3:T10 - so not always A1);
4.Copy & paste (autofill) formulas.
5.Launch macro (if necessary I can try rewrite macro code in Python)
I've tried to use openpyxl and pandas but I find it as not stable solution. As example if I run code for 1st time it's working but in 2nd approach I receive errors like

-"BadZipFile: File is not a zip file" -> (if I upload starting excel that will be updated then issue disappears);
-"template = load_workbook("_MAT_TEMPLATE.xlsx") KeyError: "There is no item named '[Content_Types].xml' in the archive" -> again uploading file resolves the issue;
-ValueError: I/O operation on closed file.-> issue when I want to save excel after pasting pandas dataframe - not solved yet.

As I haven't work on such complex topic yet, could you advice what library I could use for working on such task? I've tired openpyxl and pandas but I feel that they are somehow unstable. I've learned about Spire.XLS but seems that this is paid solution. If you also know already existing project on github that I could use as "inspiration" I would be also grateful.

Best regards, Jakub

What I did so far is

from  openpyxl.workbook import workbook
from  openpyxl import load_workbook 
import pandas as pd
import xlsxwriter
xlPY = load_workbook("FX_rates_PY.xlsx")
wsPY = xlPY["englisch"]
FXPY = wsPY["B7:u32"]

template = load_workbook("_MAT_TEMPLATE.xlsx")
templateFXPY = template["FX rates_PY"]
materials = template["Materials"]
dest_rangePY = templateFXPY["A1:T26"]
templateFXCY = template["FX rates_CY"]
dest_rangeCY = templateFXCY["A1:T26"]

for i, row in enumerate(FXPY):
    for j, cell in enumerate(row):
        dest_rangePY[i][j].value=cell.value   
    
xlCY = load_workbook("FX_rates_CY.xlsx")
wsCY = xlCY["englisch"]
FXCY = wsCY["B7:U32"]

for i, row in enumerate(FXCY):
    for j, cell in enumerate(row):
        dest_rangeCY[i][j].value=cell.value   
    
    
cover_sheet = template["Cover"]
cover_sheet["C66"] = "Y"
cover_sheet["C55"] = "Y"


Month = cover_sheet['c7'].value
CurrentYear = cover_sheet['c5'].value

Bot_input = template["BOT Input"]
data_values = []
for row in Bot_input:
    data_values.append([cell.value for cell in row])

PlantDataClean = pd.DataFrame(data_values[1:], columns=data_values[0])
PlantDataClean = PlantDataClean[PlantDataClean["GRIP Plant"].notna()] 
PlantDataClean['GRIP Plant'] = PlantDataClean['GRIP Plant'].astype(int)


print(PlantDataClean)


erp_value = PlantDataClean['ERP'].values[0]
SAP_Plant_value = PlantDataClean['SAP Plant'].values[0]
print(SAP_Plant_value)

from openpyxl.utils.dataframe import dataframe_to_rows


if erp_value == "BKP":
    BKP = load_workbook("BKP_Material List.xlsx")
    BKPsheet = BKP["Sheet1"]

    data_values = []
    for row in BKPsheet:
        data_values.append([cell.value for cell in row])
    BKPsheetPD = pd.DataFrame(data_values[1:], columns=data_values[0])
    BKPsheetPDfilter = BKPsheetPD[BKPsheetPD["Plant"] ==                 
SAP_Plant_value]
    BKPsheetPDfilter =BKPsheetPDfilter["Material"]
    print(BKPsheetPDfilter)


    BKPsheetPDfilter.to_excel('_MAT_TEMPLATE.xlsx',sheet_name="sheet1")
    #,startcol=2,startrow=0,header=None)

else:
    print("S4 not avaiable yet")    
template.save("_MAT_TEMPLATE.xlsx")

[Danishhafeez]

For a more stable and robust solution, you might consider using the xlwings library. xlwings is a powerful library that allows you to interact with Excel files directly from Python. It provides a Pythonic way to automate Excel tasks, manipulate data, and run macros.

[b]Installation: Install xlwings using pip:

pip install xlwings

1. Basic Usage: Here's a simplified example of how you can use xlwings to copy data from one Excel file to another:
   

import xlwings as xw

# Open the source workbook
wb_source = xw.Book('source.xlsx')
ws_source = wb_source.sheets['Sheet1']

# Open the destination workbook
wb_dest = xw.Book('destination.xlsx')
ws_dest = wb_dest.sheets['Sheet1']

# Copy data from source to destination
data = ws_source.range('A1').expand().value
ws_dest.range('A1').value = data

# Save changes
wb_dest.save()

i hope Using xlwings, you can automate your Excel tasks more reliably and efficiently compared to openpyxl and pandas. Additionally, xlwings has excellent documentation and a large community, making it easier to find help and examples online.

[Pedroski55]

Put a sample of the XL files you want to read and extract data from.

Maybe also stepwise what you want to do, for clarity.

If your files contain formulas, there is a flag data_only=True which will, as it implies, only get the value of the formula, not the formula.

wb = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)

I'm sure you can do what you want to do with openpyxl! (Auch auf Englisch!)

[MasterOfDestr]

For a more stable and robust solution, you might consider using the xlwings library. xlwings is a powerful library that allows you to interact with Excel files directly from Python. It provides a Pythonic way to automate Excel tasks, manipulate data, and run macros.

[b]Installation: Install xlwings using pip:

pip install xlwings

1. Basic Usage: Here's a simplified example of how you can use xlwings to copy data from one Excel file to another:
   

import xlwings as xw

# Open the source workbook
wb_source = xw.Book('source.xlsx')
ws_source = wb_source.sheets['Sheet1']

# Open the destination workbook
wb_dest = xw.Book('destination.xlsx')
ws_dest = wb_dest.sheets['Sheet1']

# Copy data from source to destination
data = ws_source.range('A1').expand().value
ws_dest.range('A1').value = data

# Save changes
wb_dest.save()

i hope Using xlwings, you can automate your Excel tasks more reliably and efficiently compared to openpyxl and pandas. Additionally, xlwings has excellent documentation and a large community, making it easier to find help and examples online.

I've tested xlwings today and it's little bit annoying in usage:
- if range that I want to copy have empty cells, then it crashes (so in this case data needs to be copied separately)
- if I have text in range then it crashes (again in this case data needs to be copied separately)
If I messed up and conclusions are wrong, please correct me.

I'm also not sure how long it will take to prepare whole report as it seems that xlwings opens excels and then "works" on them.

[MasterOfDestr]

For a more stable and robust solution, you might consider using the xlwings library. xlwings is a powerful library that allows you to interact with Excel files directly from Python. It provides a Pythonic way to automate Excel tasks, manipulate data, and run macros.

[b]Installation: Install xlwings using pip:

pip install xlwings

1. Basic Usage: Here's a simplified example of how you can use xlwings to copy data from one Excel file to another:
   

import xlwings as xw

# Open the source workbook
wb_source = xw.Book('source.xlsx')
ws_source = wb_source.sheets['Sheet1']

# Open the destination workbook
wb_dest = xw.Book('destination.xlsx')
ws_dest = wb_dest.sheets['Sheet1']

# Copy data from source to destination
data = ws_source.range('A1').expand().value
ws_dest.range('A1').value = data

# Save changes
wb_dest.save()

i hope Using xlwings, you can automate your Excel tasks more reliably and efficiently compared to openpyxl and pandas. Additionally, xlwings has excellent documentation and a large community, making it easier to find help and examples online.

I'll try to prepare "dummy" excel as I cannot share original data.
In the meantime, how I can recognize issues with excel after copy&pasting data using excel?

Repaired Records: External formula reference from /xl/externalLinks/externalLink5.xml part (Cached values from external formula reference)</repairedRecord><repairedRecord>
Repaired Records: Drawing from /xl/drawings/drawing7.xml part (Drawing shape)</repairedRecord><repairedRecord>
Repaired Records: Drawing from /xl/drawings/drawing8.xml par</repairedRecord><repairedRecord xml:space="preserve">

Additionally how I can paste pandas dataframe to excel without deleting all other sheets? If I use code such thing happens...

    BKPsheetPDfilter.to_excel("_MAT_TEMPLATE.xlsx",sheet_name="Materials"
    ,startcol=2,startrow=0,header=None)