如果excel文件是打开的，我们如何避免在写流期间从c#到excel的excel文件只读

本文关键字：excel 文件 只读 我们 何避免 如果 | 更新日期: 2023-09-27 18:07:22

在从c#写入值到excel时，同时excel文件打开它显示the error that the file is in readonly，那么我们如何能够避免在从c#到excel的写入流中出现错误

    //Get all the sheets in the workbook
 mWorkSheets = mWorkBook.Worksheets;
 //Get the allready exists sheet
 mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
 Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
 int colCount = range.Columns.Count;

 int rowCount = range.Rows.Count+1;
 for (int index = 0; index < NoOfRecords; index++)
 {
     for (int j = 0; j < colCount; j++)
     {                       
         mWSheet1.Cells[(rowCount) + index, j + 1] ="'"+Convert.ToString(ResultsData.Rows[index][j].ToString());
     }
 }

 mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
 Missing.Value, Missing.Value, Missing.Value,
 Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);

如果excel文件是打开的，我们如何避免在写流期间从c#到excel的excel文件只读

最简单的方法是将eas保存到不同的文件名，然后在成功保存后删除原始文件。比如打开mysheet_xlsx并保存MySheet_Updated.XLSX 

 string newPath = path.Replace(Path.GetFileNameWithoutExtension(path),Path.GetFileNameWithoutExtension(path)+"_Updated)";
 try{
    mWorkBook.SaveAs(newPath,Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,Missing.Value,Missing.Value, Missing.Value);
   mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
   File.Delete(path); 
   File.Move(newPath,path);
   }
 catch(Exception e){
      Console.WriteLine(e.Message+"'n"+e.Source);
 }

    public System.Data.DataTable CorruptedExcel(string path, string savedFile) {
    try
    {
        Missing missing = Missing.Value;
       Excel.Application excel = new Excel.Application();
       Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(path, CorruptLoad: Microsoft.Office.Interop.Excel.XlCorruptLoad.xlRepairFile);
        var connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";Extended Properties='"Excel 8.0;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text'"";
        //var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text'""; ;
        using (var conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
                var adapter = new OleDbDataAdapter(cmd);

                adapter.Fill(dt);
            }
        }
        return dt;
    }
    catch (Exception e)
    {
        throw new Exception("ReadingExcel: Excel file could not be read! Check filepath.'n"
            + e.Message + e.StackTrace);
    }
}
相关文章：