I have an application that converts files from particular formats (including CSV, XLS etc) into my required output format.
I have, up until now, used the OleDB approach to open these bad boys and convert into my required format. All was working well. Basically, whatever the input format, I get it into a System.Data.DataTable and then convert away.
My problem is that recently, some of the files to be converted are coming password protected so I'm having to move toward the Excel Automation. So, I can open the files and grab the required worksheets etc, but can't seem to work out how to simply bung the data from my worksheet into a DataTable ready for converting!!
Excel.Application myXLS = new Excel.Application();
Excel.Workbook wbk = myXLS.Workbooks.Open(file.FilePath, 0, true, 5, "password", "password", _
true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);
Excel.Sheets sheets = wbk.Worksheets;
Excel.Worksheet wksht = (Excel.Worksheet)sheets.get_Item(1);
System.Data.DataTable dt = wksht.something_surely?