Hi,
I have an Excel Spreadsheet which I have external data coming into from an SQL Server 2000 database. I.e, I have done “Data -> Import External Data -> Import Data” from within excel, pointed to an SQL Server database, setup an SQL query and then chosen to bring back the data into one of the worksheets in my Excel file.
I now need to load this saved Excel file up in a C# application using Aspose.Excel.
I have achieved this first part very easily (nice one guys - easy to use API!). However the external data is not updating itself in the worksheet. Eg. if I change the data in the SQL database and then re-run my application, the Aspose.Excel object loads up the Excel file fine but its still got the old data in.
In Excel itself when I have the file open, then change the database contents in the background, I know that I have to do a “right click -> Refresh Data” on the worksheet to update the data. Is there any way to do this in Aspose.Excel - to effectively get the data to update properly.
Regards
Chris.
Hi Chris,
Aspose.Excel cannot execute the SQL query in your Excel file. You can try two approaches to solve this problem:
1. If your users can access your database while opening the excel file, please select “Refresh data on file open” option in “Data Range Properties”.
2. If they cannot acces the DB while opening the file, you can try to retrieve data in your program and use Cells.ImportDataTable to put data into the excel file instead of using external sql data source.
Hi Laurence,
For option 1, the application is server based so yes, it will have access to the database when the Excel file is loaded. However, I have tried setting the “refresh data on file open” to ticked in the “Data Range Properties” and it still didn’t work. When I load the file up in Excel itself it asks me if I want to “Enable Automatic Refresh” so I’m possibly guessing its trying to launch the same dialog when I’m opening it up in aspose - is there anyway to switch off warning dialogs? There are options to do this in the full Excel COM component, however we are unable to make use of this due to clients not being allowed to install the full Office suite on a production server.
Thanks
Chris
Hi again Laurence,
I’ve now tried both the options above and neither has worked form.
A note on the first option about the startup dialog - I managed to turn that off indefinately using a registry setting (QuerySetting).
For option 2, I need to give you the background on the excel file. It has 5 worksheets. The last four contain the data gained from the database queries (one db query per sheet and it fills the first column with data) and the first one has calculations in that perform DCount functions on each of the filled columns in the other four sheets. This produces 4 calculated values in the first sheet - essentially the number of items bought back from each query. I’ve sucessfully done what you said for option 2 - requery the data in my program and then used Cells.ImportDataTable. But the values in the first sheet aren’t being recalculated after I’ve done the import of the data.
Is there any function to force a recalculation on the first sheet?
Regards
Chris
- About option 1, I will check to see if the warning dialog can be disabled.
2. In option 2, do you mean the values in the DCount formula are not re-calculated? Have you set the Calculation mode to Automatic in Tools->Options->Calculation?