0

My C# application takes alot of data from an excel file and process it then write it back on the file.

The problem is when loading the data from the Excel file, it takes some time (30 seconds for about 7000 field of Excel)

I want to make it faster, is there's any method to make it faster by significant amount of time?

The code I use to fetch the data is:

streamReader = new StreamReader(@"ExcelPath.txt");
string path = streamReader.ReadLine();

//An Excel Application ==(contains)==> [Many] Excel WorkBook ==(contains)==>[Many] Excel WorkSheets==(contains)==>[Many] Ranges
Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook myworkbook = excelapp.Workbooks.Open(path, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);

Microsoft.Office.Interop.Excel.Sheets myworksheets = myworkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet myworksheet =myworksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = myworksheet.UsedRange;

string[] data = new string[range.Rows.Count];
int start = 0;
for (start = 3; start < range.Rows.Count; start++)
{
    Microsoft.Office.Interop.Excel.Range myrange = myworksheet.get_Range("O" + start.ToString(), System.Reflection.Missing.Value);
    data[start] = myrange.Text;
    }
Damith
  • 62,401
  • 13
  • 102
  • 153
smohamed
  • 3,234
  • 4
  • 32
  • 57
  • Which version of Excel? looking at how you read the path from a text file, are you running just the one instance of Excel? and its not running on a server is it? Check out this KB to see other gotcha's support.microsoft.com/kb/257757 - otherwise the Range method vs Cell is WTG – Jeremy Thompson Nov 05 '11 at 02:56
  • @JeremyThompson Excel 2010, yes, one instance of Excel, No, not on a server... – smohamed Nov 05 '11 at 15:26

1 Answers1

5

I have not done anything with Excel in a loooong time...but a few years ago we were able to speed up a few of our processes that read data from Excel reading ranges of cells at a time into an array (rather than going cell by cell).

Each read operation from C# to Excel has to go through COM and that's very expensive. If you manage to reduce how many times you need to read from Excel (e.g. by reading multiple values at once) you should see a significant improvement in speed.

Hector Correa
  • 26,290
  • 8
  • 57
  • 73
  • See here for some example code covering Hector's suggestion: http://stackoverflow.com/questions/7919964/low-performance-when-reading-data-from-excel-workbook-to-arraylist-in-c-sharp – Tim Williams Nov 05 '11 at 02:12