private Excel.Worksheet ExportExcel(ClassItem classItem, Excel.Worksheet worksheet)
{
var cursor = 1;
// Create BACKEND Header with 6 columns.
// Merger 6 columns.
var header = (Excel.Range)worksheet.Range[worksheet.Cells[cursor, 1], worksheet.Cells[cursor, 6]];
header.Merge();
header.Value2 = "BackEnd";
// Create header cloumns.
cursor++;
worksheet.Range[worksheet.Cells[cursor, 1], worksheet.Cells[cursor, 1]].Value = "No.";
worksheet.Range[worksheet.Cells[cursor, 2], worksheet.Cells[cursor, 2]].Value = "Type";
worksheet.Range[worksheet.Cells[cursor, 3], worksheet.Cells[cursor, 3]].Value = "Line";
worksheet.Range[worksheet.Cells[cursor, 4], worksheet.Cells[cursor, 4]].Value = "Name";
worksheet.Range[worksheet.Cells[cursor, 5], worksheet.Cells[cursor, 5]].Value = "Related Name";
worksheet.Range[worksheet.Cells[cursor, 6], worksheet.Cells[cursor, 6]].Value = "Note";
// Get SP and table list
var refSPList = new List<string>();
var refTableList = new List<string>();
// Create data.
foreach (var programItem in classItem.ProgramItems)
{
// Add the initial item
if (!refSPList.Contains(programItem.Name, StringComparer.Create(CultureInfo.InvariantCulture, true)))
{
refSPList.Add(programItem.Name);
GetReferenceData(programItem, ref refSPList, ref refTableList);
}
break;
}
foreach (var programName in refSPList)
{
var programItem = SqlItemManager.GetRegisteredItem(programName) as ProgramItem;
if (programItem != null)
{
cursor++;
worksheet.Range[worksheet.Cells[cursor, 4], worksheet.Cells[cursor, 4]].Value = programItem.Name;
string refString = string.Empty;
foreach (var sqlItem in programItem.SqlItems)
{
refString += sqlItem.Name + Environment.NewLine;
}
worksheet.Range[worksheet.Cells[cursor, 6], worksheet.Cells[cursor, 6]].Value = refString;
}
}
// Create header cloumns.
cursor = cursor + 2;
worksheet.Range[worksheet.Cells[cursor, 1], worksheet.Cells[cursor, 1]].Value = "No.";
worksheet.Range[worksheet.Cells[cursor, 2], worksheet.Cells[cursor, 2]].Value = "Type";
worksheet.Range[worksheet.Cells[cursor, 3], worksheet.Cells[cursor, 3]].Value = "Line";
worksheet.Range[worksheet.Cells[cursor, 4], worksheet.Cells[cursor, 4]].Value = "Name";
worksheet.Range[worksheet.Cells[cursor, 5], worksheet.Cells[cursor, 5]].Value = "Related Name";
worksheet.Range[worksheet.Cells[cursor, 6], worksheet.Cells[cursor, 6]].Value = "Note";
foreach (var tableName in refTableList)
{
var tableItem = SqlItemManager.GetRegisteredItem(tableName) as TableItem;
if (tableItem != null)
{
cursor++;
worksheet.Range[worksheet.Cells[cursor, 4], worksheet.Cells[cursor, 4]].Value = tableItem.Name;
foreach (var columnItem in tableItem.Columns)
{
if (columnItem.DataType == DataTypes.NVarchar)
{
cursor++;
worksheet.Range[worksheet.Cells[cursor, 5], worksheet.Cells[cursor, 5]].Value = columnItem.Name;
}
}
}
}
return worksheet;
}
public List<ClassItem> GetClassDataFromExcel(string filePath)
{
var result = new List<ClassItem>();
ClassItem classItem = null;
// var spCollection = new Dictionary<string, List<string>>();
// Create localized tables and columns 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[2] 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 className = ((Excel.Range)excelRange.Cells[r, 1]).Value2;
var spName = ((Excel.Range)excelRange.Cells[r, 5]).Value2 + string.Empty;
if (className != null || classItem == null)
{
if (_nameClassHeaderColumn.Equals(className + string.Empty)) { continue; }
classItem = new ClassItem { Name = className + string.Empty };
result.Add(classItem);
}
spName = spName.ToLowerInvariant().Trim().Trim('[', ']', '.');
// Remove 'dbo'
if (spName.StartsWith("dbo"))
{
spName = spName.Substring(3);
spName = spName.Trim('[', ']', '.');
}
if (!string.IsNullOrEmpty(spName.Trim()))
{ classItem.AddProgramItem(spName); }
}
}
}
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;
}
