Excel Automation - Importing Excel File into System.Data.DataTable

Hi

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?

Help!

Thanks
Simon
LVL 2
sbennettsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
koolnurdConnect With a Mentor Commented:
ok...

use columns and rows it will give you the range...

And for selecting cell value...use like this...

((Excel.Range)worksheet.Cells[row, column]).Value2
0
 
koolnurdCommented:
try this way

// Open XLSDocument
XLSDocument document = new XLSDocument("AdvancedReport.xls");
DataSet dataSet = new DataSet("AdvancedReport");
for (int i = 0; i < document.WorkBook.Worksheets.Count; i++)
{
Worksheet worksheet = document.WorkBook.Worksheets[i];
DataTable table = dataSet.Tables.Add(worksheet.Name);
for (int column = 0; column <= worksheet.LastColumn; column++)
{
table.Columns.Add(string.Format("Column{0}", column));
}
for (int row = 0; row <= worksheet.LastRow; row++)
{
object[] data = new object[worksheet.LastColumn + 1];
for (int column = 0; column <= worksheet.LastColumn; column++)
{
data[column] = worksheet.Cell(row, column).Value;
}
table.Rows.Add(data);
}
}
// Close XLSDocument
document.Close();
0
 
sbennettsAuthor Commented:
Hi, thanks for your response.

No joy I'm afraid.  Falls over in several places, for instance there not being a lastcolumn or lastrow available.  Also, the worksheet.Cell seems to fall over as well.

I'm using MS Office v9.0 Object Library

Regards
Simon
0
 
sbennettsAuthor Commented:
Hi, thanks for your help, managed to do it more or less as you suggest.  Some subtle tweaks but very helpful.
Cheers
Simon
0
All Courses

From novice to tech pro — start learning today.