CODEWITHSUNDEEP
pythonexcelpandasxlwings
Osinaga
Osinaga

Reputation: 71

Append rows to excel table using python, without clobbering formulas

so I am writing a script that grabs data from an excel sheet, processes it a bit, and then appends it to a different table on a different excel file. the target table has some pure data columns, and some formula columns. I want to append new rows, while keeping the formulas intact. the problem is that when i try to pull data from the target table to append the data i have, i just get the values, not the formulas, and when i .update() the table I just get values, and the formulas are gone.

my code looks something like this (pardon the spanish variable names):

import pandas as pd

import xlwings as xw

planilla = 'planilla.xlsx'

sheet = 'Hoja1'

tabla = "empleados"


with xw.App(visible=True) as xl:
    book = xl.books.open(planilla)
    
    rng = book.sheets[sheet].tables[tabla].range

    df=  rng.expand().options(pd.DataFrame , index = False).value
        
    newrow={"nam" :"Carlos",
            'años' : 23.0,
            "trabajo" : "actor",
            "sueldo": 100.0
}

    df2= pd.DataFrame([newrow],index=["10"])
    
    conc = pd.concat((df,df2))
    
    
    book.sheets[sheet].tables[tabla].update(conc, index=False)
    book.save()
    book.close()

lets assume that there are 2 more columns that are something like "=[@sueldo]*[@años]"or whatever formula

The code works, it runs without erros, but the formulas are gone, they are replaced by the values that excel calculated before the first run of the code. i need the formulas to stay after i append the rows. is this possible?

Upvotes: 0

Views: 109

Answers (2)

moken
moken

Reputation: 6620

Xlwings holds formulas under the cell attribute 'formula' and 'formula2'. The cell attribute 'value' only ever contains the cell value. Therefore when extracting the data using

rng.expand().options(pd.DataFrame , index = False).value

any cells with a formula will be the evaluation of the formula and not the formula itself.
E.g. for cell A1 containing a formula =SUM(B1+C1)
wsheet['A1'].formula is the formula =SUM(B1+C1)
wsheet['A1'].value is always the value of the sum of B1 and C1

Either way your method seems over complex given you just want to add another row to an existing Excel Table.
There is no need to copy the table data, add to it, then paste back to Excel using Pandas.
Since you are utilising the Excel app with Xlwings, just add the new row to the end of the existing Table and Excel will automatically include the row in that Table.

The example code below gets the range of the table e.g. 'A6:D17'
From that determines the column and row for the next row of the Table, e.g. 'A18'
Writes the new data to the Sheet at this co-ordinate, and Excel includes the row in that Table.
Existing data in the Table is not changed and formulas remain as is.

Example Code;
Note: I have used an Openpyxl Utility for converting some co-ordinates information for convenience. This example otherwise does not use the Openpyxl module.

import xlwings as xw
import openpyxl

planilla = 'planilla.xlsx'
sheet = 'Hoja1'
tabla = "empleados"

with xw.App(visible=True) as xl:
    book = xl.books.open(planilla)
    wsheet = book.sheets[sheet]

    ### Get the range of the existing Table named tabla
    table_range = wsheet.range(tabla).expand('table').address

    ### Use top left cell and bottom right cell to get the coord for the next row to be added 
    table_tlc, table_brc = table_range.replace('$', '').split(':')
    tl_col = openpyxl.utils.cell.coordinate_from_string(table_tlc)[0]
    tl_row = openpyxl.utils.cell.coordinate_from_string(table_brc)[1]

    ### Create next row coordinates
    next_row = f"{tl_col}{tl_row+1}"

    ### Add the new data to this row 
    wsheet.range(next_row).value = [["Carlos", 23.0, "actor", 100.0]]

    ### Save the workbook
    book.save()
    book.close()

Upvotes: 2

Mr Jxtr
Mr Jxtr

Reputation: 118

I may be wrong but....

This is probably because we cannot pull a sheet from excel to Pandas with the formula intact. (this is because formulas in excel is not a valid data type in Pandas or SQL etc.) we can only pull data that is in a valid data type i.e string, int, float, etc.

I suggest you copy the sheet with formulas into another sheet and make sure to "paste values" only and try to update or append your new data to that new sheet. This way you can keep your formulas in the fist sheet. You can then use those formulas on the new sheet with a new data once you open excel again.

Upvotes: 0

Related Questions