How do we read one time excel file instead of every time open and close in C#

[Gani tpt]

I have the requirement which is i want to read particular range from excel.

For example below is my code:

int endno = 500;

for (int i=0; i <=endno; i++)

{

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test.xlsx");
Excel.Worksheet sheet = xlWorkbook(sheet1);

char[] startIndex = start.ToArray();
char[] endIndex = end.ToArray();

int startX = int.Parse(startIndex[0].ToString());
string startY = startIndex[1].ToString();
int endX = int.Parse(endIndex[0].ToString());
string endY = endIndex[1].ToString();

object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;

// have to do some process and calculation here

}

the above case, i want to open the file every loop iterating and process some calculation.

the pain is, when i read the excel file every time (open excel and read), it's taking too much time and and unnecessarily more excel process occupied.

Instead of every time open and close excel file, Is it possible to read one time open the file and convert into data table..?

So that, the first time will be reading the file and converting in to data table. The next iteration we can read it from the datatable and we can process it.

Is it possible to do this..?

Continue reading...