How to refresh external data connection?

Aspose.Cells Product Family
1

Hi,

I have a workbook containing an external data connection: a simple SELECT statement from a SQL Server, that fills a worksheet.

I need to open this file with Aspose.Cells, refresh data and save the file. I didn’t find any Refresh() function on DataConnections or ListObjects classes.

I’ve set RefreshOnLoad property in Excel but the file isn’t refreshed when opening via Aspose, it is only resfreshed when opening with Excel.

Thank you in advance for your help
Jerome

2

Hi,

Thanks for your posting and using Aspose.Cells.

Aspose.Cells cannot fetch data from database. However, Microsoft Excel can do so. So refresh on load should work ok. Will you please share your console application project? It will be helpful, if you share a simple excel file, simple data and simple sample code so that we could investigate and fix this issue easily and speedily. Thanks for your cooperation in this regard. Have a good day.

3

Hi,

Thank you for your answer. You will find attached the file “example.xslx”, it contains an external connection to a database.

My code is just :
using (Workbook wb = new Workbook(EXCEL_FILE))
{
wb.CalculateFormula();
wb.Save(EXCEL_NEW_FILE);
}

The new file is exactly the same as the orginal, no data are refreshed.
If I open the new file with Excel, data are refreshed.

Regards,
Jerome

4

Hi,


Thanks for your posting and using Aspose.Cells.

From your issue description, it means, everything is ok. Because you say, data is refreshed when you open the New Excel file in Microsoft Excel, so this is right. However, if data was not refreshed in Microsoft Excel when New Excel File is opened up, then that would be a bug.

Please remember, Aspose.Cells cannot refresh data on its own. It cannot fetch data from database and insert it into excel. So if your source excel file was working fine in Microsoft Excel and your output excel file is also working fine (i.e refreshing data) in Microsoft Excel, then it is not a bug but correct behavior.

5

Hi,

My need is :

  1. The user creates an Excel file with external data connection
  2. The excel file is refresh on the server. I’ll imagine with Aspose.Cells
  3. The result file is opened in Aspose.Cells.GridWeb

So if I understand correctly this scenatio is impossible ?

Regards,

Jerome

6

Hi,


Well, yes, your understanding is correct. Aspose.Cells does not support to refresh connections in the Excel file to extract/update data. This feature is also not on our roadmap so it won’t be supported in near future as well.

We are sorry for any inconvenience caused!
7

Thank for your answer even if I would have preferred another one :slight_smile:

8
Hi,

Thanks for your posting and using Aspose.Cells.

By the way, you can extract data from your database using ADO.NET into DataTable and then import that DataTable into your Excel file using Aspose.Cells.

Please see this article that shows how to import data into worksheets from Data Table

( http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets )

9

Hi,

Is refreshing external connections (or a subset like SQL) still not on the roadmap? If so, why not? Is there a solid technical reason, or is it simply a strategic/management/marketing decision?

Thank you!
david.

10

@dcatteeu

Thanks for considering Aspose APIs.

This is beyond the scope of Aspose.Cells. You must import your data into data table and then import your data table into worksheet.

Please see the following subsection of the article for more help.