[Advanced Excel] Refresh data in Excel File

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

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  

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

Solution

Steps to update the Advanced Excel Package.

  1. Open integration studio
  2. Download Advanced Excel
  3. Add action 
  4. Name Action and add input parameter Object
  5. Click update code

  6. Depending on you .net development tool visual studio will be started

  7. In the interface file you new action is created


  8. In the advanced_excel.Cs file the empty function is created:


  9. Write you custom code:

  10. ExcelPackage ee = ssWorkbook as ExcelPackage;
                ee.Workbook.Calculate();

  11. Build you new solution (menu item build)
  12. 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

Solution

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

  1. Open integration studio
  2. Download Advanced Excel
  3. Add action 
  4. Name Action and add input parameter Object
  5. Click update code

  6. Depending on you .net development tool visual studio will be started

  7. In the interface file you new action is created


  8. In the advanced_excel.Cs file the empty function is created:


  9. Write you custom code:

  10. ExcelPackage ee = ssWorkbook as ExcelPackage;
                ee.Workbook.Calculate();

  11. Build you new solution (menu item build)
  12. 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 !


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

Erik Brzozowski wrote:

Steps to update the Advanced Excel Package.

  1. Open integration studio
  2. Download Advanced Excel
  3. Add action 
  4. Name Action and add input parameter Object
  5. Click update code

  6. Depending on you .net development tool visual studio will be started

  7. In the interface file you new action is created


  8. In the advanced_excel.Cs file the empty function is created:


  9. Write you custom code:

  10. ExcelPackage ee = ssWorkbook as ExcelPackage;
                ee.Workbook.Calculate();

  11. Build you new solution (menu item build)
  12. 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