topic Re: Excel(.xlsx) formula refresh after loading data in Talend Studio

Excel(.xlsx) formula refresh after loading data

Spasupuleti1641121123 — Fri, 15 Nov 2024 23:05:27 GMT

Hi, all

could you please help on below scenorio,

I am updating excel sheets(.xlsx) with new data, After updating with new data the formula fields are not being updated, If i enable check box "Re-calculate formula" talend is taking too much long time for one file(40mins).like wise i need to update 700 files. because of this i need to open all the file and clik ctrl+alt+f9 to update formula fields.

kindly help on this.. I want design talend code to update data along with formula fields..

regards,

sudheer

Re: Excel(.xlsx) formula refresh after loading data

Anonymous — Fri, 25 Mar 2022 08:19:01 GMT

Hello,

When you use normal excel without any formula via tfileoutputexcel, does it work fast? So only the excel with attached formula is taking so much of time?

Could you please check and confirm what is calculation mode in excel , is it automatic or manual?

Best regards

Sabrina

Re: Excel(.xlsx) formula refresh after loading data

Spasupuleti1641121123 — Fri, 25 Mar 2022 09:09:38 GMT

example:

Input excel file:

A, B, C

1, 2, 3

3, 4, 5

target Excel file:

A, B, C, C1, C2

2, 1, 1, 2*A, 3*B

If i fill A,B,C columns in target table from source table with talend job, C1,C2 columns in target table are not updating according to formula..

->if i enable check box Recalculate formula, in tFileOutputExcel job is taking 30min for one file. because each file containing 30k to 80k records.. that is why i am not supporting to enable recalculate formula in OutputExcel..

i am requesting you is there any way to fix this issue..

After succesfull completion of job i want see correct updated data in ouput file along with C1,C2 columns..

Thanks,

Sudheer.

Re: Excel(.xlsx) formula refresh after loading data

Anonymous — Fri, 25 Mar 2022 09:33:45 GMT

Hello,

I'm not sure this use case can be achieved in talend built-in component.

You could have a look at some custom components tFileExcel* (a collection of components) from Talend Exchange and it is written by our community member Jan Lolling.

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%252F1%252Fproducts%252F542&pi=marketplace%252F1%252Fproducts%252F542%252Fitems%252F3017

Here is online documentation about: TalendHelpCenter: How to install and update a custom component

Best regards

Sabrina

Re: Excel(.xlsx) formula refresh after loading data

gjeremy1617088143 — Fri, 25 Mar 2022 11:03:41 GMT

Hi As far i can see in the java code Talend is using the org.apache.POI library to recalculate formula,

and use this to recalculate all the formula :

private void evaluateFormulaCell() {

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {

sheet = wb.getSheetAt(sheetNum);

for (Row r : sheet) {

for (Cell c : r) {

if (c.getCellTypeEnum() == CellType.FORMULA) {

evaluator.evaluateFormulaCellEnum(c);

}

wb is a workbook object that contain the workbook

and here is the recalculate formula in POI documentation :

FileInputStream fis = new FileInputStream("/somepath/test.xls");Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

for (Sheet sheet : wb) {

for (Row r : sheet) {

for (Cell c : r) {

if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {

evaluator.evaluateFormulaCell(c);

}

as you can see it's very similar

The org.APACHE.POI library seems to be the most used in java project and one of the best.

https://poi.apache.org/components/spreadsheet/eval.html

Re: Excel(.xlsx) formula refresh after loading data

gjeremy1617088143 — Fri, 25 Mar 2022 11:59:59 GMT

@Jan Lolling can surely advise you, if the custom component he made Will bring better performance on formula recalculation or if he know a workaround.

Re: Excel(.xlsx) formula refresh after loading data

Spasupuleti1641121123 — Fri, 25 Mar 2022 13:21:20 GMT

Hi,

while using the tFileExcelworkbookOpen/Save, tFileExcelsheetInput/Output components.. same issue is being repeated.