OleDbConnection locks Excel xls file if the file is corrupted

Question

I have a legacy code that imports Excel (*.xls) to our db, then move the file to specific directory after processing.

The code works fine except in one case, when the file is corrupted (even MS Excel cannot open it)! What happens in this case is that an System.AccessViolationException thrown at opening the connection!

Here is how the code looks like:

        string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""", filePath);
        OleDbConnection connection = new OleDbConnection(connectionString);
        try
        {
            connection.ConnectionString = connectionString;
            connection.Open(); //<<<--- exception throws here
            //file processing
        }
        catch (Exception e)
        {
            //exception handling
        }
        finally
        {
            connection.Close();
            connection.Dispose();
            connection = null;
            GC.Collect();
        }

Here is the exception details...

System.AccessViolationException was caught
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=System.Data
  StackTrace:
       at System.Data.Common.UnsafeNativeMethods.IDBInitializeInitialize.Invoke(IntPtr pThis)
       at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()

As you can see, I am catching this exception and process it, then when the code try to move the file to another directory, I got the following exception:

System.IO.IOException occurred
  Message=The process cannot access the file because it is being used by another process.
  Source=mscorlib
  StackTrace:
       at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
       at System.IO.__Error.WinIOError()
       at System.IO.File.Move(String sourceFileName, String destFileName)

I tried to use another library, like LinqToExcel, but found it internally uses the same implementation like mine, then it is has the same problem!

I tried also to run garbage collector after the connection is closed (as you see in the above code) but faced the same problem!

Any Idea?

score 2 · Accepted Answer · answered Mar 31 '15 at 18:53

I tried to play around the main solution in the question with no results :(

I even checked the .NET Framework code and can see file handles somewhere in the code, but unfortunately failed to debug the code :(

I tried to decompile the .NET Framework code but failed too :(

Finally. it ends that I should use another solution, and since depending on the existence of MS Office in production machine is not an option, I went to ExcelDataReader, the open source library that reads *.xls files as binary streams, and here is how the final code looks like:

using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream, true))
    {
        excelReader.IsFirstRowAsColumnNames = true;
        var excelFileDataSet = excelReader.AsDataSet();
        var sheetDataTable = excelFileDataSet.Tables["sheetName"];
        //other file processing code...
    }
}

And this solution works for me!

score 1 · Answer 2 · answered Mar 29 '15 at 05:20

I have the same problem right now, my only solution its read the excel file using Microsoft.Office.Interop.Excel and set the MsoFileValidationMode = msoFileValidationSkip;

Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook;

        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

        **xlApp.FileValidation = MsoFileValidationMode.msoFileValidationSkip;** 

        xlWorkbook = xlApp.Workbooks.Open(@"C:\my file.xls");
        Excel.Sheets xlWorksheet = xlWorkbook.Worksheets;

        Excel.Worksheet worksheet = (Excel.Worksheet)xlWorksheet.get_Item(3);
        for (int i = 1; i <= 10; i++)
        {

            Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString()); ; //UsedRange;
            System.Array myvalues = (System.Array)range.Cells.Value2;


            string[] strArray = ConvertToStringArray(myvalues);

            foreach (string item in strArray)
            {   
                MessageBox.Show(item);
            }

        }

... works well

Seems that Excel should be installed in production machine! Also, Does this solution have tight to certain Office versions? — Sameh Deabes, Mar 29 '15 at 06:09

OleDbConnection locks Excel xls file if the file is corrupted

2 Answers2