0

I have Excel file has 4 sheets, all sheets have huge number of records around 144564 record.

In my code, fill_data method will call 4 methods, each method read one sheet. the normal time it takes now is 7.02 minutes.

fill_data ()
{
    Excel.Application ExcelApplication;
    Excel.Workbooks ExcelWorkbooks;
    Excel.Workbook ExcelWorkbook1;

    ExcelApplication = new Excel.Application();  /// ApplicationClass()
    ExcelWorkbooks = ExcelApplication.Workbooks;
    try
    {
                ExcelWorkbook1 = ExcelApplication.Workbooks.Open(fileName1, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
    }
    catch
    {
                return;
    }

    active1 = ExcelWorkbook1.Worksheets.get_Item(1); // students list
    active2 = ExcelWorkbook2.Worksheets.get_Item(2);
    active3 = ExcelWorkbook3.Worksheets.get_Item(3);
    active4 = ExcelWorkbook4.Worksheets.get_Item(4);

    fill_students();
    fill_instructors();
    fill_exams();
    fill_rooms();

}

 public static void fill_students(){
      Excel.Worksheet active = active1;
      //Read all Cells at Sheet
 }

I try to use the Parallel feature to make the 4 methods run in parallel, instead of explicitly call methods I use invoke at Parallel class. As the following:

fill_data ()
{
    .....
    Parallel.Invoke(
            () => fill_students(),
            () => fill_instructors(),
            () => fill_exams(),
            () => fill_rooms()
        );
}

There is no enhancement on the executing time! it was 7.12 minutes! So what is going wrong? how to minimize this time?

  • 1
    The Excel COM calls are inherently single-threaded. If multiple threads try to access the data the COM calls automatically block all but one thread at a time. It is impossible to multi-thread this. – Enigmativity Sep 18 '16 at 09:28
  • 1
    Also, you're doing IO. It's unlikely that making your code multi-threaded would produce any significant improvement in any case. Multi-threading only really helps CPU intensive operations. – Enigmativity Sep 18 '16 at 09:29
  • can't we optimize the time? –  Sep 18 '16 at 09:34
  • what do you think if I read 4 sheets from different files, then we have a 4 ExcelApplication = new Excel.Application(); to read each sheet. –  Sep 18 '16 at 09:37
  • This couldn't help ? –  Sep 18 '16 at 09:37
  • 1
    If you have Excel running in four different processes then you still will run against an IO limit. If all four take 7 minutes then running four processes you might find that the first one takes 6.9 minutes and the last three about 0.2 minutes each. Only one way to find out though. – Enigmativity Sep 18 '16 at 09:44
  • 1
    What you should do is read the excel file using an approach which reads the file directly rather than via COM. You might find that they will load in seconds. – Enigmativity Sep 18 '16 at 09:46
  • 1
    Have a read of this: http://stackoverflow.com/questions/12996234/optimal-way-to-read-an-excel-file-xls-xlsx/12996544#12996544 – Enigmativity Sep 18 '16 at 09:46
  • Thank you, I will check this way :) –  Sep 18 '16 at 11:06
  • the fastest way to write excel files - use OpenXml SDK. It allows to stream writing file as xml https://msdn.microsoft.com/en-us/library/office/bb448854.aspx – Backs Sep 19 '16 at 07:31
  • @Backs, I need to read excel –  Sep 19 '16 at 07:38
  • @Hanaa read also: http://stackoverflow.com/questions/23102010/open-xml-reading-from-excel-file – Backs Sep 19 '16 at 07:48

0 Answers0