[Advanced Excel] Refresh data in Excel File

Back to Forums

Lucas Leveau

Solved

Question

Forge

Import/Export Data

HI everyone,

Is there a way to refresh data in an Excel file from OutSystems ?

My app is updating a template excel file which has a cell (lets call it "A1 "to make it clear) that is equal to another cell (lets call this one "B2")

So i am changing the value of "B2" so its supposed to change the value of "A1" too but it's not happening.

I have to manually refresh the excel file using the refresh button in the ribbon of Excel app.

Is there a way to do this "refresh" action with OutSystems so the Excel file is automatically refreshed ? Using another module or not?

Thanks in advance

Lucas

23 May 2019

Erik Brzozowski

Solution

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

See solution in context

24 May 2019

2 replies

Last reply 24 May 2019

Show thread

Hide thread

Lucas Leveau

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

Thanks a lot Erik !

I was already doing this but that confirmed how to do it correctly !

Thanks for the step by step tutorial !

It will help a lot !

Thanks for the time you took for me !

Have a great day !

24 May 2019

Hanno

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

Very thorough explanation, thank you Erik

24 May 2019

Erik Brzozowski

Hello Lucas,

I dont think this is already available in the advanced Excel package. However the advanced excel package is build with (EPPlus). According to the documentation of EPplus there is a refresh option.

https://github.com/JanKallman/EPPlus/wiki/Formula-Calculation

You could try to update the Advanced Excel Package and add a function your self.

Erik

24 May 2019

Lucas Leveau

Hi Erik,

Yeah there is no refresh action available in Advanced Excel package today.

Your link could be helpful but my problem is : I want to refresh my data because i have a Pivot Table that takes values in another sheet and if I change this data i need to refresh the file to see my pivot table updated

Maybe whyno creating myself this function but i have no idea how to create it? where to start ?

Thanks for your response

Lucas

24 May 2019

Erik Brzozowski

Solution

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

24 May 2019

2 replies

Last reply 24 May 2019

Show thread

Hide thread

Lucas Leveau

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

Thanks a lot Erik !

I was already doing this but that confirmed how to do it correctly !

Thanks for the step by step tutorial !

It will help a lot !

Thanks for the time you took for me !

Have a great day !

24 May 2019

Hanno

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

Open integration studio
Download Advanced Excel
Add action
Name Action and add input parameter Object
Click update code
Depending on you .net development tool visual studio will be started
In the interface file you new action is created
In the advanced_excel.Cs file the empty function is created:
Write you custom code:
ExcelPackage ee = ssWorkbook as ExcelPackage;
ee.Workbook.Calculate();
Build you new solution (menu item build)
Go back to integration studio an click one click publish

Note, I have not tested the code (ExcelPackage ee = ssWorkbook as ExcelPackage; ee.Workbook.Calculate();) But there are the steps to update an .net Extention

Very thorough explanation, thank you Erik

24 May 2019

Lucas Leveau

for those who want, it's possible to enable auto refresh at the launch of an excel file

and store this excel file in the resources

24 May 2019

1 reply

23 Mar 2021

Show thread

Hide thread

Fábio Godinho

Yes, it's also a great workaround that works :)

This is only for PivotTables, but maybe it can help someone else out there! :P

from: https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2

Refresh data automatically when opening the workbook

Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
Click Analyze > Options.
On the Data tab, check the Refresh data when opening the file box.

23 Mar 2021

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines