[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

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

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 !


mvp_badge
MVP

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


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

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

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 !


mvp_badge
MVP

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


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

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

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

    PivotTable Tools

  2. Click Analyze > Options.

    Options button on the Analyze tab

  3. On the Data tab, check the Refresh data when opening the file box.

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