add a row in an existing Excel file and save it-CodePudding

I'm having problems saving new rows in my existing Excel file, it throws an exception and says "write-protected". My Excel file is not open or something I created it with this code and saved it.


            ...
            xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
            #endregion

            endState = 7;
            wasSuccessful = true;

Maybe I cant save it because it's open while I opened it to change it. So delete and save again maybe? or just save like you would save it after adding a row more in a normal Excel file and close it.

          if (System.IO.File.Exists(path))
          {

            Excel.Application xlApp = new Excel.Application();
            object misValue = System.Reflection.Missing.Value;

            //open existing Excel file
            var wb = xlApp.Workbooks.Open(path, FileMode.Open, FileAccess.Read);

            //get Sheet
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

            //get the last Row who is filled
            Excel.Range xlRange = (Excel.Range)ws.get_Range("A"   ws.Rows.Count, Type.Missing);
            int LastRow = xlRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;

            //def. next free row
            int newRow = LastRow   1;

            //fill Cells
            ws.Cells[newRow, 1] = LastRow - 3;
            ws.Cells[newRow, 2] = BLZ;
            ws.Cells[newRow, 3] = DK;
            ws.Cells[newRow, 4] = KPA;
            ws.Cells[newRow, 5] = Produktschlüssel;
            ws.Cells[newRow, 6] = KMAUSTextressource;
            ws.Cells[newRow, 7] = Versandschlüssel;
            ws.Cells[newRow, 8] = Kartennummer;
            ws.Cells[newRow, 9] = Verarbeitungshinweis;

            ws.Cells[newRow, 10] = Anrede;
            ws.Cells[newRow, 11] = Kundenname1;
            ws.Cells[newRow, 12] = Kundenname2;
            ws.Cells[newRow, 13] = AnschriftZusatz;
            ws.Cells[newRow, 14] = Straße;
            ws.Cells[newRow, 15] = PLZuWohnort;
            ws.Cells[newRow, 16] = alternativAusland;

            ws.Cells[newRow, 26] = BLZ;
            ws.Cells[newRow, 27] = DK;


            xlApp.DisplayAlerts = false;
            wb.SaveAs(path, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            wb.Close(true, path);


            //wb.Close(true, misValue, misValue);
            xlApp.Quit();


          }

CodePudding user response：

FileAccess.Read?

//open existing Excel file
var wb = xlApp.Workbooks.Open(path, FileMode.Open, FileAccess.Read);

I've things work with just:

Excel.Workbook workbook = excel.Workbooks.Open(@"C:\MyExcelFile.xlsx"); // No other arguments

Complete version:

 using Excel = Microsoft.Office.Interop.Excel;

 void DoWorkWithExcel()
 {
     Excel.Application excel = null;
     Excel.Workbook workbook = null;
     Excel.Worksheet worksheet = null;

     string excelFile = @"C:\MyExcelFile.xlsx";

     try
     {
         excel = new Excel.Application { Visible = true, DisplayAlerts = false };

         workbook = excel.Workbooks.Open(excelFile);
         worksheet = (Excel.Worksheet)workbook.Worksheets[1];
         
         int newRow = worksheet.Range["A"   worksheet.Rows.Count, Type.Missing]
                               .End[Excel.XlDirection.xlUp].Row   1;

         // Fill you cells
         worksheet.Cells[newRow, 1] = newRow - 4; // Your LastRow   1 - 3;
         worksheet.Cells[newRow, 2] = BLZ;
         worksheet.Cells[newRow, 3] = DK;
         // And others...

         // Save changes
         workbook.Save();
     }
     catch (Exception ex) // Or System.Runtime.InteropServices.COMException
     {
         // Handle it or log or do nothing
     }
     finally
     {   
         // Close Book and Excel and release COM Object
         workbook?.Close(0);
         excel?.Quit();
         Marshal.ReleaseComObject(excel);
     }
  }