during writing values to an excel from c#, at the same time the excel file is open it shows the error that the file is in readonly
, so how can we able to avoid that error during write stream from c# to the Excel
在从c#向excel写入值时,同时打开excel文件会显示文件处于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);
2 个解决方案
#1
2
The easiest way is to SAVEAS to a different file name, then delete the original file on successful save. Like open MySheet.XLSX and save MySheet_Updated.XLSX
最简单的方法是将SAVEAS转换为其他文件名,然后在成功保存时删除原始文件。像打开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);
}
#2
0
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);
}
}
#1
2
The easiest way is to SAVEAS to a different file name, then delete the original file on successful save. Like open MySheet.XLSX and save MySheet_Updated.XLSX
最简单的方法是将SAVEAS转换为其他文件名,然后在成功保存时删除原始文件。像打开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);
}
#2
0
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);
}
}