Failed to save Excel file using C #

Question

I am trying to modify and save data in Excel. Using the code below, I access the worksheet by making changes and then saving the file. I can not save the file. Here is my code:

Application excel = new Application(); excel.Visible=true; Workbook wb = (Workbook)excel.Workbooks.Open(@"C:\Users\dnyanesh.wagh\Desktop\BookExcel1.xlsx"); Worksheet ws = (Worksheet)wb.Worksheets[1]; ws.Cells[1, 1] = "sagar"; ws.Cells[2, 1] = "sagar"; ws.Cells[3, 1] = "sagar"; wb.Save(); wb.close(); 

I get this error: "a file named" BookExcel1.xlsx "already exists in this place. Do you want to replace it?"

So, I changed the code to:

 Workbook wb = (Workbook)excel.Workbooks.Open(@"C:\Users\dnyanesh.wagh\Desktop\BookExcel1.xlsx",0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);); 

Then an error occurs: "BookExcel1.xlsx changes by username .open as read only." If I click the cancel button, I get the above exception with "Exception from HRESULT: 0x800A03EC"

I also tried:

 wb.SaveAs(@"C:\Users\dnyanesh.wagh\Desktop\BookExcel1.xlsx"); wb.Close(true,null,null); 

From this I get the same error, with the above file showing the changes.

Can someone tell me how can I save a file with changes?

Answer 1

This is because the file I was trying to open was already open in the excel application. In my code, I did not close the excel application for some conditions. That's why he opened it with read-only rights. Therefore, after the update, I could not save this file. To open it using read-write, you must first close this file. After that, you can open it and easily perform read and write operations in the file.

You can close and view all excel applications that are already open using the following code:

 Application excel = (Application)Marshal.GetActiveObject("Excel.Application"); Workbooks wbs = excel.Workbooks; foreach (Workbook wb in wbs) { Console.WriteLine(wb.Name); // print the name of excel files that are open wb.Save(); wb.Close(); } excel.Quit(); 

At the top, add the following code and make a link to Microsoft.Office.Interop.Excel

 using Microsoft.Office.Interop.Excel; using System.Runtime.InteropServices; 

Answer 2

Make sure you do not have the Excel.exe runtime yet. In addition, you must open the book so that it is edited.

This code works:

 string txtLocation = Path.GetFullPath(InputFile); object _missingValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook theWorkbook = excel.Workbooks.Open(txtLocation, _missingValue, false, _missingValue, _missingValue, _missingValue, true, _missingValue, _missingValue, true, _missingValue, _missingValue, _missingValue); //refresh and calculate to modify theWorkbook.RefreshAll(); excel.Calculate(); theWorkbook.Save(); theWorkbook.Close(true); excel.Quit(); 

Answer 3

This issue is for backward compatible sheet (.xls) instead of .xlsx.

To allow opening sheets in the version before 2007, it cannot contain more than 65 thousand lines. You can check the number of lines in yours using Ctrl + down arrow until you reach the bottom. If you try to get a range greater than this number of rows, this will create an error