private DataTable GetExcelTable(string sheetName, string tableName)
{
DataTable dt = null;
XL.Application xlApp = new XL.Application();
XL.Workbook wb = xlApp.Workbooks.Open(Path);
XL.Worksheet ws = (XL.Worksheet)wb.Sheets[sheetName];
XL.ListObject lo = ws.ListObjects[tableName];
XL.ListColumns listColumns = lo.ListColumns;
XL.ListRows listRows = lo.ListRows;
wb.Close();
return(dt);
}
private void LoadExcelTable()
{
string workBookName = @"B:\Publish\SupportTools_Visio\TestData.xlsx";
string workSheetName = "Sheet2";
string tableName = "tbl_Data";
XL.Application xlApp = new XL.Application();
XL.Workbook wb = xlApp.Workbooks.Open(workBookName);
XL.Worksheet ws = wb.Sheets[workSheetName];
XL.ListObject lo = ws.ListObjects[tableName];
XL.ListColumns listColumns = lo.ListColumns;
XL.ListRows listRows = lo.ListRows;
VisioHlp.DisplayInWatchWindow(string.Format("{0}\n", tableName));
foreach (XL.ListColumn col in listColumns)
{
VisioHlp.DisplayInWatchWindow(col.Name);
}
tableName = "tbl_Data2";
lo = ws.ListObjects[tableName];
listColumns = lo.ListColumns;
listRows = lo.ListRows;
VisioHlp.DisplayInWatchWindow(string.Format("{0}\n", tableName));
foreach (XL.ListColumn col in listColumns)
{
VisioHlp.DisplayInWatchWindow(col.Name);
}
foreach (XL.ListRow row in listRows)
{
VisioHlp.DisplayInWatchWindow(row.ToString());
}
wb.Close();
}