Reading data from an excel file every 500 milliseconds in c#

Question

I have an excel file that contains some records and that records that refresh within a second. I want to store all records in an SQL Server Database. So My intention is to read data from this file into my database after every 500 milliseconds. I have successfully been able to read the data and store in my database. But after few minutes, excel hangs and is in a not responding state.

Excel._Application excel = (Excel._Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
clsDBOLiveData objDBOLiveData = new clsDBOLiveData();
clsDatabase objDatabase = new clsDatabase();

private static System.Timers.Timer aTimerLive_1;
private static System.Timers.Timer aTimerHist_1;
private static System.Timers.Timer aTimerLive_2;
private static System.Timers.Timer aTimerHist_2;


//Here I declare all variables.

public TradeData()
{
    SetTimer1_Data();
    SetTimer2_Data();
    SetTimerHist_1_Data();
    SetTimerHist_2_Data();
}


private void SetTimerLiveMCX_1_Data()
{
    aTimerLive_1 = new System.Timers.Timer(500);
    aTimerLive_1.Elapsed += OnTimedEvent_1;
    aTimerLive_1.AutoReset = true;
    aTimerLive_1.Enabled = true;
}

private void OnTimedEvent_1(Object source, ElapsedEventArgs e)
{
    try
    {
        Excel.Workbook workbook = excel.Workbooks[1];
        Excel.Worksheet workSheet = workbook.Worksheets.get_Item(1);
        Excel.Range range = workSheet.UsedRange;
        for (int j = 3; j < range.Rows.Count; j = j + 5)
        {
            if ((range.Cells[j, 2] as Excel.Range).Value2 == null || Convert.ToString((range.Cells[j, 2] as Excel.Range).Value2)=="")
                continue;
            clsDBOLiveData objDBOLiveData = new clsDBOLiveData();
            objDBOLiveData.SYMBOL_NAME = (string)(range.Cells[j, 8] as Excel.Range).Value2;
            objDBOLiveData.BAR_TIME= (double)(range.Cells[j, 2] as Excel.Range).Value2;
            objDBOLiveData.HIGH= (decimal)(range.Cells[j, 3] as Excel.Range).Value2;
            objDBOLiveData.LAST = (decimal)(range.Cells[j, 4] as Excel.Range).Value2;
            objDBOLiveData.LOW = (decimal)(range.Cells[j, 5] as Excel.Range).Value2;
            objDBOLiveData.OPEN = (decimal)(range.Cells[j, 6] as Excel.Range).Value2;
            objDBOLiveData.VOLUME = (decimal)(range.Cells[j, 7] as Excel.Range).Value2;
            objDBOLiveData.STATUS = (string)(range.Cells[j, 9] as Excel.Range).Value2;
            string strErrorMgs = "";
            if (!objDatabase.SaveData_1(objDBOLiveData, ref strErrorMgs)){}
        }
    }
    catch(Exception ex)
    {
    }
}

Try to Release or dispose, like in this sample : http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects — , Jul 04 '16 at 09:17

score 1 · Answer 1 · answered Jul 04 '16 at 09:37

1

Dispose the objects.. Excel objects are heavy

answered Jul 04 '16 at 09:37

Ram Moharil

78
7

Would reusing the object not be better then creating and disposing it every 500ms? After every run just reset the values for data integrity? – Riaan van Zyl Jul 04 '16 at 10:21
No..! Be very careful with this. Until you've released a particular COM object, Excel might still keep this Excel file locked. It's important to open the Excel, do your processing, then release the COM objects. Don't leave COM objects open longer than needed. We've (often) had problems with Excel 2013 leaving hidden instances of EXCEL.EXE running, as COM objects haven't been cleaned up properly. – Mike Gledhill Jul 04 '16 at 11:27
Yes.. I agree with Mike.. excel will lock those objects and might give error as can't update the file as it is used by another processes. – Ram Moharil Jul 05 '16 at 11:32

score 0 · Accepted Answer · edited May 23 '17 at 10:28

Oh gosh, a few things here.

First, as the other readers have mentioned. When you're using Excel COM objects, you must release them.

Here's how I would style your code:

Excel.Workbook workbook = null;
Excel.Worksheet workSheet = null;
Excel.Range range = null;
try
{
    workbook = excel.Workbooks[1];
    workSheet = workbook.Worksheets.get_Item(1);
    range = workSheet.UsedRange;
    . . . 
}
catch(Exception ex)
{
}
finally
{
    if (range != null)
        Marshal.FinalReleaseComObject(range);
    if (worksheet != null)
        Marshal.FinalReleaseComObject(worksheet);
    if (workbook != null)
        Marshal.FinalReleaseComObject(workbook);

    //  Garbage collection
    GC.Collect(GC.MaxGeneration, GCCollectionMode.Default, false);
}

Secondly, I really wouldn't trust any lines containing Excel.Range and Value2 in the same line:

objDBOLiveData.SYMBOL_NAME = (string)(range.Cells[j, 8] as Excel.Range).Value2;

Do a Google search for "Excel COM double dot" for (many) explanations as to why this is a bad idea.

Personally, if all of your data is in one Worksheet, I would recommend reading in the entire worksheet into a object[,] array, then loop through the values in there.

object[,] cellValues = (object[,])range.Value2;

You can also convert this variable into an Array if that makes it easier for your code:

System.Array arr = (System.Array)cellValues;
int numOfRows = arr.GetUpperBound(0);
int numOfColumns = arr.GetUpperBound(1);

Reading all of the cell data in one go is more efficient, with less chance of COM objects being left around.

One last thing: be careful with UsedRange.

Excel.Range range = workSheet.UsedRange;

We've had examples where this has returned enormous Ranges (eg 16,000 columns, when there's only actually 7 columns of data) which, in turn, crashes our code later on, as it attempts to iterate through enormous (blank) ranges of cells.

Have a read of the following StackOverflow article to describe the only safe way to find which cells in your spreadsheet really do contain data:

Alternative to UsedRange

Phew.

Now Its woks fine.But recently I faced a new problem. Managed Debugging Assistant 'RaceOnRCWCleanup' has detected a problem in 'D:\TestPrograms\TestData\bin\Debug\TestData.vshost.exe'. Additional information: An attempt has been made to free an RCW that is in use. The RCW is in use on the active thread or another thread. Attempting to free an in-use RCW can cause corruption or data loss. I cannot solved this problem.Any suggestion... — Sumit Manna, Jul 17 '16 at 06:31

Reading data from an excel file every 500 milliseconds in c#

2 Answers2