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; }