Refreshing Excel Sheet in C#

Question

I have an excel file which has got pivot tables and charts on "Sheet 1" referring data from "Sheet 2" which in turn points to records in SQL Server table.

I have written a SSIS job to populate underlying SQL Server table and then refresh the excel sheet using following code.

//At this point, sql server table is already populated with data.
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
 excelApp.DisplayAlerts = false;
 excelApp.Visible = false;
 Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value);

try
        {               
            excelWorkbook.RefreshAll();
            excelWorkbook.RefreshAll();
            excelWorkbook.RefreshAll();
            excelWorkbook.Save();
        }
        finally
        {
            excelWorkbook.Close(false, workbookPath, null);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
            excelWorkbook = null;
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;

The problem is when i open the excel, it still shows data from previous load. And after i click "Refresh All" in excel file, data gets refreshed. Is there any fool proof method to refresh all the data in excel using C#.

you should add a catch clause, just to check if there is not an exception. With this code, a failure would be silent. — Steve B
@SteveB I have a catch block. I have not included it in above code snippet. — Phoenix
An Excel file,by nature, cannot refresh itself without opening and running a macro or hitting some buttons. Thats not possible. Once you save the file, that gets saved to the Hard disk or where ever you store that file. Let alone Excel, you cant do that with any other file type. Automatic refresh-ability is not built into Excel. Write a macro and schedule it with the windows scheduler on a predefined schedule. That would make it automatic IMO. — rvphx

S.A S.A · Accepted Answer · 2013-06-05T14:15:42

After doing the refresh Try changing the code to

excelWorkbook.SaveCopyAS("Your File save location with FileName");

instead of

excelWorkbook.Save();

Might help in your case.

Refreshing Excel Sheet in C#

3 Answers