读取Excel与OLEDB C＃文件，当它被其它进程 [英] Read Excel file with OleDB c#, when it is used by other process

问题描述

我想读一个Excel每2秒文件，该文件是越来越被其他应用程序的RTD更新。

I am trying to read an excel file every 2 seconds, This file is getting updated by other RTD application.

我能够读取由OLEDB连接这个文件，但是当我试图读取它每2秒问题就来了。输出10尝试它能够只读和其他尝试4-5次，它会抛出异常。

I am able to read this file by Oledb connection, but problem comes when i am trying to read it every 2 seconds. Out of 10 attempts it is able to read 4-5 times only and at other attempts ,it throws exception.

连接字符串

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\nids\shes.xlsm;Extended Properties="Excel 12.0 Macro;HDR=Yes;IMEX=1"

代码

//opening connection to excel file

using (OleDbConnection connection = new OleDbConnection(constr))//constr = connection string
{
    try
    {  
        connection.Open();
        isconopen = true;
    }
    catch
    {
        dispatcherTimer2.Start();

        connection.Close();
        isconopen = false;
    }

    // If connection is ok , then query sheet  
    if (isconopen == true)
    {
        strcon = "SELECT * FROM [" + dsheet + "]";

        using (OleDbDataAdapter adapter = new OleDbDataAdapter(strcon, connection))
        {

            try
            {


                adapter.Fill(result);
                isread = true;
                adapter.Dispose();

                connection.Close();
            }
            catch 
            {

                isread = false;
                dispatcherTimer2.Start();

                adapter.Dispose();
                connection.Close();

            }

        }
     }

    //if able to retrieve data then call some other function 
    if (isread == true)
    {
        converToCSV(0);// for further processing
    }

请帮帮我吧，我是从最近1个月尝试此。请请请请帮我。

Please help me , i am trying this from last 1 month. Please please please please help me out

推荐答案

在默认情况下可悲的是OLEDB驱动程序将打开文件只当它是你不能打开它在别人，哪怕只是阅读使用

Sadly OleDB driver by default will open file exclusively then you can't open it when it's in use by someone else, even just for reading.

两点考虑：

• 其他应用程序可能会在几毫秒内完成其与文件工作，所以这是很好的尝试重新


• 驱动程序将锁定（通过OLEDB所以你不能打开它的两倍）写总是打开的文件，但它的读取（这样你就可以将它复制）共享。

不过我建议你应该首先尝试在短暂的停顿后，将其打开，如果它仍然在使用（你不能等待更多），那么你可以复制并打开。

That said I suggest you should first try to open it after a short pause, if it's still in use (and you can't wait more) then you can make a copy and open that.

让我假设你有你的代码在 HandlExcelFile（）功能：

Let me assume you have your code in a HandlExcelFile() function:

void HandleExcelFile(string path)
{
    try
    {
        // Function will perform actual work
        HandleExcelFileCore(path); 
    }
    catch (Exception) // Be more specific
    {
        Thread.Sleep(100); // Arbitrary

        try
        {
            HandleExcelFileCore(path);
        }
        catch (Exception)
        {
            string tempPath = Path.GetTempFileName();
            File.Copy(path, tempPath);

            try
            {
                HandleExcelFileCore(tempPath);
            }
            finally
            {
                File.Delete(tempPath);
            }
        }
    }
}

代码。是有点难看所以只是认为这是一个起点，编写自己的函数

Code is little bit ugly so just consider it a starting point to write your own function.

注意事项：

• 重试是不是这样的坏事，它是解决这类问题的常用方法。这是，例如，什么样的Windows外壳程序（和它的更加正常，网络）。


• 如果应用程序没有关闭该文件，那么你可以复制（和读取）的老的数据。如果你总是需要最先进的最新数据，那么你只有一个选择：等待。如果你可以假设未保存的数据属于前一时间段（T - 1，在数字电子在信号边沿上的时钟边沿），那么就去做，生活幸福

• Retry isn't such bad thing and it's a common way to solve this kind of problems. It's, for example, what Windows shell does (and it's even more normal with networks).
• If application didn't close the file then you may copy (and read) old data. If you always need most up-to-date data then you have only one choice: wait. If you can assume that unsaved data belongs to the previous time frame (T - 1, as in digital electronic when signal edge is on clock edge) then just do it and live happy.

未经测试的解决方案：

我没有尝试这个，所以你必须自己做。 Actuallly（我最初是错的），你可以打开一个只读连接（通过扩展属性）。如果这仅适用于连接或两个文件句柄和连接它不记录。反正让我们尝试连接字符串更改为：

I didn't try this so you have to do it by yourself. Actuallly (I was initially wrong) you can open a read-only connection (through extended properties). It's not documented if this apply to connection only or both file handle and connection. Anyway let's try to change your connection string to:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\nids\shes.xlsm;Extended Properties="Excel 12.0 Macro;HDR=Yes;IMEX=1;ReadOnly=true"

只是增加了一个只读= TRUE 在扩展属性的结束。

其他的解决方案：

• 浮现在我的脑海里是唯一的替代解决方案...手动读取Excel文件（这样你就可以打开它只是读）。这就是说，即使在这种情况下，其他应用程序可能没有写入新的数据（所以你会读到的老的一个）。


• 请不要使用计时器在所有。改变你的设计中使用，只有当通知已经改变了 FileSystemWatcher的，你将读取该文件。


• 在当前适用的.. 。只是不要使用共享文件作为IPC机制！那么，你可能无法改变第二个应用程序，这可能不是你的情况。


• 请不要使用OLEDB读取Microsoft Excel文件，有很多第三部分免费程序库，不要公开的科技文件，排它锁，例如这个。


• 请而不是直接从文件中读取数据，如果Microsoft Excel中的实例总是处于运行状态，您可以使用COM互操作来获得通知。检查有关COM加载项这个一般文章并的这个的，看你怎么可以使用Office互操作的C＃应用​​程序附加到现有的Microsoft Excel实例。

• The only alternative solution that comes to my mind is to...manually read Excel file (so you can open it just for reading). That said even in this case the other application may have not written new data (so you'll read old one).
• Don't use a timer at all. Change your design to use a FileSystemWatcher, you'll read the file only when notified it has been changed.
• When applicable...just do not use a shared file as IPC mechanism! Well, you may not be able to change 2nd application so this may not be your case.
• Do not use OleDB to read Microsoft Excel files, there are many 3rd part free libraries that don't open file with exclusive lock, for example this one.
• Do not read data directly from files, if Microsoft Excel instance is always running you can use COM interop to get notifications. Check this general article about COM add-ins and this to see how you can attach your C# application with Office Interop to an existing Microsoft Excel instance.

这篇关于读取Excel与OLEDB C＃文件，当它被其它进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！