Jul 26, 2013 at 9:05 AM
Edited Jul 26, 2013 at 9:05 AM
I am sorry I didn't put the timers on the piece of code posted above. I have already done that. These are more exact results:
FileStream fs = File.Open(file, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
excelReader.IsFirstRowAsColumnNames = true;
m_ds = excelReader.AsDataSet();
This takes about 2 s. If I understand correctly this is the where the IO takes place. If so, I think this is excellent.
foreach (DataRow r in dt.Rows)
string str = Convert.ToString(r["Date"]);
d = DateTime.Parse(ControlExcel.ConvertToDateTime(str), provider);
d = DateTime.Parse(ControlExcel.ConvertToDateTime(str));
v = Convert.ToDouble(r[p.Key]);
catch (Exception theException)
This takes about 13 s.
My first thought was that access to the DataTable container is slow. So I tried some alternatives, by copying the contents of the columns I am interested in into List or into System.Array objects. Then I looped over the List/System.Array containers, but the
time is exactly the same.
As a reference, I am comparing to what I observe when using the Microsoft.Office.Interop functionality. In this case, the file access time is comparable. But I only read the columns I am interested in and not the whole workbook (like when using the ExcelDataReader
--> this might a big advantage!). I get the colmun data into System.Array objects. Then looping over these containers is very fast (abt 2 seconds), with the same code as in the foreach loop above.