|
Is it possible to create a system.data.datatable or dataset from an excel worksheet that is obtained using interop.excel in C#?
The code is as following. As you can see, I can read/modify certain cell, but is it possible to convert the table to a System.data.datatable? Or at least print out the select range in the excelworksheet?
protected void OpenExcelSpreadSheet(string input)
{
Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
Excel.Workbook newWorkbook =
excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
// The following code opens an existing workbook
object Missing = System.Reflection.Missing.Value;
string workbookPath= "myFile.xls";
Excel.Workbook excelWorkbook =
excelApp.Workbooks.Open(workbookPath,Missing,Missing,Missing,
Missing,Missing,Missing,Missing,Missing,Missing,Missing,Missing,Missing,Missing,Missing);
// The following gets the Worksheets collection
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
// The following gets Sheet1 for editing
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
// The following gets cell A1 for editing
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A1", "A1");
excelCell.Value2 = input;
Excel.Range excelTable = (Excel.Range)excelWorksheet.get_Range("A1", "A27");
int noOfRow = excelTable.Rows.Count;
int noOfColumn = excelTable.Columns.Count;
Response.Write("Table has " + noOfRow + " rows and " + noOfColumn + " columns. ");
}
|
|
|
|