Updating an Excel worksheet in C #

Question

I have an excel file that has pivot tables and charts in "Sheet 1" that reference the data from "Sheet 2", which in turn point to the records in the SQL Server table.

I wrote an SSIS job to populate a SQL Server base table, and then updated the excel sheet using the 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 excel, it still shows the data from the previous download. And after I click "Update All" in the excel file, the data will be updated. Is there any kind of error proof method to update all data in excel using C #.

Answer 1

I was able to update using XLRefresh.exe at http://metacpan.org/pod/Win32::Excel::Refresh

Answer 2

After the update, try changing the code to

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

instead

 excelWorkbook.Save(); 

May help in your case.

Answer 3

Here is the code to protect and hide the excel sheet. Use the required namespaces as shown below

 using System; using System.Data; using Microsoft.CSharp; // This is also used when we use Excel using System.Collections; // This is required if you are using array list using Excel=Microsoft.Office.Interop.Excel; // Used when we work with Excel 

Initialize Excel application, Filepath is a string variable containing a password

 string FilePath = @"C:\Filename.xlsx"; Excel.Application ExcelApp = new Excel.Application(); // Initialize Excel Application ExcelApp.DisplayAlerts = false; Excel.Workbook WB = ExcelApp.Workbooks.Open(FilePath); // Initialize Excel Workbook 

Then you can create a function or do it directly using the code below, toRefresh in this code is an Arraylist that contains a list of sheets needed to be updated.

In this function we pass the book (WB)

 RefreshSheets(WB); 

Here is the function definition: using (Excel.Workbook WB) in the function below, C # knows that we are passing WB, which is a working Excel application.

 public void RefreshSheets(Excel.Workbook WB) { WB.RefreshAll(); //WB.Save(); } 

Please let me know if this helps.