public List<TableItem> GetTableDataFromExcel(string filePath, int defaultSheet = 1)
{
var result = new List<TableItem>();
TableItem classItem = null;
// Create table list
if (File.Exists(filePath)
&& (filePath.Trim().EndsWith(_xlsExtension)
|| filePath.Trim().EndsWith(_xlsxExtension)))
{
Excel.Workbook wkb = null;
Excel.Application excel = null;
try
{
excel = new Excel.Application();
wkb = excel.Workbooks.Open(filePath);
var sheet = wkb.Sheets[defaultSheet] as Excel.Worksheet;
//
// Take the used range of the sheet. Finally, get an object array of all
// of the cells in the sheet (their values). You can do things with those
// values. See notes about compatibility.
//
if (sheet != null)
{
Excel.Range excelRange = sheet.UsedRange;
for (var r = 1; r <= excelRange.Rows.Count; r++)
{
var tableName = ((Excel.Range)excelRange.Cells[r, 1]).Value2 + string.Empty;
tableName = tableName.Trim();
var tableItem = SqlItemManager.GetRegisteredItem(tableName) as TableItem;
if (tableItem == null)
{
tableItem = new TableItem(tableName);
}
tableItem.IsBeingLocalized = true;
for (var c = 2; c <= excelRange.Columns.Count; c++)
{
var columnName = ((Excel.Range)excelRange.Cells[r, c]).Value2 + string.Empty;
columnName = columnName.Trim();
if (!string.IsNullOrEmpty(columnName))
{
if (tableItem.ContainColumn(columnName))
{
var columnItem = tableItem.GetColumn(columnName);
columnItem.IsLocalized = true;
}
else
{
var columnItem = new ColumnItem(columnName);
columnItem.IsLocalized = true;
tableItem.Columns.Add(columnItem);
}
}
}
if (tableItem.Columns.Count != 0)
{
result.Add(tableItem);
}
else
{
SqlItemManager.UnregisterItem(tableItem.Name);
}
}
}
}
catch (Exception ex)
{
//if you need to handle stuff
Console.WriteLine(ex.Message);
}
finally
{
//
// Clean up.
//
if (wkb != null)
{
wkb.Close();
Marshal.FinalReleaseComObject(wkb);
Marshal.FinalReleaseComObject(excel);
}
}
}
return result;
}
public override void UpdateReferences()
{
var connection = ConnectionManager.GetConnection();
if (connection != null)
{
// Update SP
try
{
var command = new SqlCommand
{
Connection = connection,
CommandText =
string.Format(
@"SELECT DISTINCT REFERENCED_ENTITY_NAME FROM SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.{0}', 'OBJECT') AS RE INNER JOIN
(SELECT NAME FROM SYS.OBJECTS WHERE TYPE IN ('FN', 'IF', 'TF')
UNION
SELECT NAME FROM SYS.PROCEDURES
UNION
SELECT NAME FROM SYS.VIEWS) PR ON PR.NAME = RE.REFERENCED_ENTITY_NAME",
this.Name)
};
var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
var programName = row[0].ToString();
var proramItem = SqlItemManager.GetRegisteredItem(programName);
if (proramItem != null)
{
_sqlItems.Add(proramItem);
}
else
{
proramItem = new ProgramItem(programName);
_sqlItems.Add(proramItem);
proramItem.UpdateReferences();
}
}
}
catch { };
// Update Tables
try
{
var command = new SqlCommand
{
Connection = connection,
CommandText =
string.Format(
@" SELECT DISTINCT REFERENCED_ENTITY_NAME FROM SYS.DM_SQL_REFERENCED_ENTITIES ('DBO.{0}', 'OBJECT') AS RE
INNER JOIN SYS.TABLES AS TB ON TB.NAME = RE.REFERENCED_ENTITY_NAME
WHERE (TB.NAME LIKE 'AH_MASTER%' OR TB.NAME = 'AH_MEMBER_WELLNESS_TIPS') AND TB.NAME NOT LIKE '%_LOCALIZED' ",
this.Name)
};
var dataTable = new DataTable();
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
foreach (DataRow row in dataTable.Rows)
{
var tableName = row[0].ToString();
var tableItem = SqlItemManager.GetRegisteredItem(tableName) as TableItem;
if (tableItem != null)
{
if (!_sqlItems.Contains(tableItem))
{
tableItem.IsReferenced = true;
_sqlItems.Add(tableItem);
}
}
else
{
tableItem = new TableItem(tableName);
tableItem.IsReferenced = true;
_sqlItems.Add(tableItem);
tableItem.UpdateReferences();
}
}
}
catch { }
}
}