Please can anyone try to tell me what shall I change in my code to get faster reading of Excel files?
I have 9 000 rows in my Excel sheet. I will have to work with more Excel files which have even more rows and I think my solution is very slow. Now I read Excel (9000 rows) read second and one (800 rows) then I do compare values (trying to find that 800 records in 9000 rows. It take me over 100sec on two separate threads.
class ExcelParser
{
private string path;
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
private int Sheet;
public ExcelParser(string path, int Sheet)
{
this.Sheet = Sheet;
this.path = path;
this.wb = this.excel.Workbooks.Open(path);
this.ws = wb.Worksheets[Sheet];
}
//column code and available are index of excel column from where read data
public Dictionary<string, string> ReadCell(int column_code = 0, int column_available = 0)
{
int i = 2; // row index in excel
Dictionary<string, string> data = new Dictionary<string, string>();
try {
while (this.ws.Cells[i, column_code].Value2 != null)
{
try
{
data.Add(this.ws.Cells[i, column_code].Value2, (this.ws.Cells[i, column_available].Value2).ToString());
i++;
}
catch (Exception e)
{
i++;
//form_obj.UpdateText(i);
}
}
} finally {
wb.Close();
excel.Quit();
Marshal.FinalReleaseComObject(excel);
Marshal.FinalReleaseComObject(this.ws);
Marshal.FinalReleaseComObject(this.wb);
this.ws = null;
this.wb = null;
}
return data;
}
}
Also my GUI keeps open 3 Excel instances, after I release all objects or try to this.wb.Close(). After I quit the GUI, the instances are exited. Why are instances of Excel not closed after this function ends via wb.Close(); and excel.Quit();?
