时间戳

首页 > 解决方案 > 在现有 Excel 文件中添加一行并保存

c# - 在现有 Excel 文件中添加一行并保存

问题描述

我在现有 Excel 文件中保存新行时遇到问题，它引发异常并显示“写保护”。我的 Excel 文件未打开，或者我使用此代码创建并保存了它。


            ...
            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;

也许我无法保存它，因为它在我打开它以更改它时处于打开状态。所以删除并再次保存可能吗？或者只是像在普通 Excel 文件中添加一行并关闭它后保存它一样保存。

          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();


          }

标签： c#excel

解决方案

文件访问。阅读？

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

我的工作只是：

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

完整版：

 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);
     }
  }

推荐阅读