[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

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
0
sbennetts
Asked:
sbennetts
  • 2
  • 2
1 Solution
 
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
 
koolnurdCommented:
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now