CODEWITHSUNDEEP
c#export-to-excel
KayzerSoze
KayzerSoze

Reputation: 93

Excel file won't save

After opening and altering an Excel File, I try to save it, using:

excelApp.ActiveWorkbook.Save();

But the data doesn't seems to save.

 private void ExportResultsToExcel()
 {
     string fullFilename = Regex.Match(Path, @".*\\([^\\]+$)").Groups[1].Value;
     string fileName = fullFilename.Substring(0, fullFilename.Length - 5);
     var templatePath = Path.Replace(fileName, "SolutionTemplate");
     var solutionPath = Path.Replace(fileName, fileName+"_Solution");
     System.IO.File.Copy(templatePath, solutionPath);

     var excelApp = new Excel.Application();
     var workbooks = excelApp.Workbooks;
     var workbook = workbooks.Open(solutionPath, 0, true, 5, "", "", true,
                                            XlPlatform.xlWindows, "\t", false,
                                            false, 0, true, 1, 0);
     var workSheets = workbook.Worksheets;
     var workSheet = (Worksheet) workSheets.Item[1];
     var rowIndex = 2;
     excelApp.DisplayAlerts = false;
     excelApp.ScreenUpdating = false;
     excelApp.Visible = false;
     excelApp.UserControl = false;
     excelApp.Interactive = false;
     foreach (var product in DemandData.Keys)
     {
         workSheet.Cells[rowIndex, 1] = product;
         workSheet.Cells[rowIndex, 2] = Result[product][0];
         workSheet.Cells[rowIndex, 3] = Result[product][1];
         workSheet.Cells[rowIndex, 4] = Result[product][2];
         workSheet.Cells[rowIndex, 5] = Result[product][3];
         workSheet.Cells[rowIndex, 6] = Result[product][4];
         workSheet.Cells[rowIndex, 7] = Result[product][5];
         workSheet.Cells[rowIndex, 8] = Result[product][6];
         workSheet.Cells[rowIndex, 9] = Result[product][7];
         workSheet.Cells[rowIndex, 10] = Result[product][8];
         workSheet.Cells[rowIndex, 11] = Result[product][9];
         workSheet.Cells[rowIndex, 12] = Result[product][10];
         workSheet.Cells[rowIndex, 13] = Result[product][11];
         workSheet.Cells[rowIndex, 14] = Result[product][12];
         rowIndex++;
     }
     int hWnd = excelApp.Application.Hwnd;
     //workbook.Save();
     excelApp.ActiveWorkbook.Save();
     Marshal.ReleaseComObject(workSheets);
     Marshal.ReleaseComObject(workSheet);
     Marshal.ReleaseComObject(workbooks);
     Marshal.ReleaseComObject(workbook);
     workbook.Close();
     excelApp.Quit();
     TryKillProcessByMainWindowHwnd(hWnd);

 }

Upvotes: 0

Views: 1695

Answers (1)

MethodMan
MethodMan

Reputation: 18843

I believe I see your issue.. in your open() method, you are opening it up as ReadOnly you could try the following change this 

var workbook = workbooks.Open(solutionPath, 0, true, 5, "", "", true,
                                            XlPlatform.xlWindows, "\t", false,
                                            false, 0, true, 1, 0);

to 

var workbook = workbooks.Open(solutionPath, 0, false, 5, "", "", true,
                                            XlPlatform.xlWindows, "\t", false,
                                            false, 0, true, 1, 0);

Pass in false for the ReadOnly parameter of the Workbooks.Open method so that you can save it later using Workbook.Save method.

the other suggestions would be to also move the close to this location

 workbook.Close();
 Marshal.ReleaseComObject(workSheets);
 Marshal.ReleaseComObject(workSheet);
 Marshal.ReleaseComObject(workbooks);
 Marshal.ReleaseComObject(workbook);

look at Marshal.ReleaseComObject in MSDN and you could also set the objects = to null as well just to be safe after calling the 4 Marshal.ReleaseComObject() methods

Microsoft Reference MarshalReleaseComObject

Upvotes: 2

Related Questions