private static bool ISValidDESSheet(DIExcel desFile, int sheetIndex) { bool RetVal = false; DataTable SheetTable = null; try { SheetTable = desFile.GetDataTableFromSheet(desFile.GetSheetName(sheetIndex)); // check rows count ( it should be greater than 9) & columns count should be grtthan 7 if (SheetTable != null && SheetTable.Rows.Count > 9 && SheetTable.Columns.Count > 7) { // check 4th row [ indicator =<indicator>] if (!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[3][0])) && !string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[3][1]))) { // check 6th row [ unit =<unit>] if (!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[5][0])) && !string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[5][1]))) { RetVal = true; } } } } catch (Exception) { RetVal = false; } return RetVal; }
/// <summary> /// DevInfo_5_0 Area Spreadsheet (should have 5 columns only staring the value from 6th Row - AreaID, AreaName, AreaLevel, AreaGID, ParentGID) /// </summary> /// <param name="templateFileName"></param> /// <param name="xlsFilenames"></param> /// <param name="tempFolderPath"></param> /// <param name="trgDBQueries">Instance of target queries object</param> /// <param name="languageName">languageName</param> /// <returns></returns> public bool CreateTemplateFrmAreaSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgDBQueries, string languageName) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries; AreaBuilder AreaBuilderObj; DIDatabase TempTemplateFile; string DataPrefix = string.Empty; string LanguageCode = string.Empty; LanguageBuilder DILanguageBuilder; try { //create temp template file TempTemplateFile = new DIDatabase(templateFileName); TempTemplateFile.Dispose(); //create DIConnection, queries and area objects DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); if (trgDBQueries != null) { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgDBQueries.LanguageCode); DataPrefix = DBQueries.DataPrefix; LanguageCode = trgDBQueries.LanguageCode.Replace("_", ""); // create langauge dependent tables if not exists in the new template this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode); } else { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); } AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); //get areas from excel files foreach (string XlsFileName in xlsFilenames) { try { // insert area into template DIExcel AreaXlsFile = new DIExcel(XlsFileName); AreaInfo NewArea; DataTable TempTable = AreaXlsFile.GetDataTableFromSheet(AreaXlsFile.GetSheetName(0)); //check it is a valid area excel file or not if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1) { if (TempTable.Rows[2][0].ToString() == DITemplate.AreaIdString & TempTable.Rows[2][1].ToString() == DITemplate.AreaNameString & TempTable.Rows[2][2].ToString() == DITemplate.AreaLevelString & TempTable.Rows[2][3].ToString() == DITemplate.AreaGIdString & TempTable.Rows[2][4].ToString() == DITemplate.AreaParentGIdString) { // delete empty rows for (int i = 0; i < 4; i++) { TempTable.Rows[0].Delete(); } TempTable.AcceptChanges(); // sort the table by level TempTable.DefaultView.Sort = TempTable.Columns[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex].ColumnName; foreach (DataRowView Row in TempTable.DefaultView) { try { // get area info from temp table NewArea = new AreaInfo(); NewArea.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaID].ColumnIndex].ToString()); NewArea.Name = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaName].ColumnIndex].ToString()); NewArea.Level = Convert.ToInt32(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex]); NewArea.GID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaGID].ColumnIndex].ToString()); NewArea.Parent = new AreaInfo(); NewArea.Parent.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.PareaGID].ColumnIndex].ToString()); //insert area into template // Step 1: If Area_Parent_ID is blank then set Area_Parent_NID to -1 and Area_Level=1 if (string.IsNullOrEmpty(NewArea.Parent.ID)) { NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2: If Area_Parent_ID is not blank then get Area_Parent_NID NewArea.Parent.Nid = AreaBuilderObj.GetAreaNidByAreaID(NewArea.Parent.ID); if (NewArea.Parent.Nid <= 0) { // Step 2.1: If Area_Parent_NID <=0 then set Area_Parent_NID to -1 and Area_Level=1 NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2.1: If Area_Parent_NID >0 then Area_Level=Area_Parent_Level+1 //get parent area level NewArea.Parent.Level = AreaBuilderObj.GetAreaLevelByAreaID(NewArea.Parent.ID); NewArea.Level = NewArea.Parent.Level + 1; } } // insert area into template AreaBuilderObj.InsertIntoDatabase(NewArea.Name, NewArea.ID, NewArea.GID, NewArea.Level, NewArea.Parent.Nid); } catch (Exception ex) { // do nothing } } } } } catch (Exception) { // } } RetVal = true; } catch (Exception ex) { RetVal = false; throw new ApplicationException(ex.ToString()); } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <summary> /// DevInfo_5_0 Indicator Spreadsheet (should have 2 columns only staring the value from 6th Row - Indicator,Indicator_GId) /// </summary> /// <param name="templateFileName"></param> /// <param name="xlsFilenames"></param> /// <param name="tempFolderPath"></param> /// <returns></returns> public bool CreateTemplateFrmIndicatorSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgQueries, string languageName) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries; IndicatorBuilder IndicatorBuilderObj; DIDatabase TempTemplateFile; string DataPrefix = string.Empty; string LanguageCode = string.Empty; try { //create temp template file TempTemplateFile = new DIDatabase(templateFileName); TempTemplateFile.Dispose(); //create DIConnection, queries and indicator objects DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); if (trgQueries == null) { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); } else { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgQueries.LanguageCode); DataPrefix = DBQueries.DataPrefix; LanguageCode = trgQueries.LanguageCode.Replace("_", ""); this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode); } IndicatorBuilderObj = new IndicatorBuilder(DBConnection, DBQueries); //get indicators from excel files foreach (string XlsFileName in xlsFilenames) { try { // insert indicators into template DIExcel IndicatorXlsFile = new DIExcel(XlsFileName); IndicatorInfo NewIndicator; DataTable TempTable = IndicatorXlsFile.GetDataTableFromSheet(IndicatorXlsFile.GetSheetName(0)); //check it is a valid indicator excel file or not if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1) { if (TempTable.Rows[2][0].ToString() == DITemplate.IndicatorString) { //starting index should be 5 for (int i = 5; i < TempTable.Rows.Count; i++) { NewIndicator = new IndicatorInfo(); //indicator Name NewIndicator.Name = DICommon.RemoveQuotes(TempTable.Rows[i][0].ToString()); //indicator GId NewIndicator.GID = DICommon.RemoveQuotes(TempTable.Rows[i][1].ToString()); //insert indicator into template IndicatorBuilderObj.CheckNCreateIndicator(NewIndicator); } } } } catch (Exception) { // } } RetVal = true; } catch (Exception ex) { RetVal = false; throw new ApplicationException(ex.ToString()); } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
public bool ImportMetataFromExcel(MetadataElementType categoryType, MetaDataType metadataType, int elementNId, string xlsFileNameWPath, string xsltFldrPath) { bool RetVal = true; string MetadataText = string.Empty; string FirstColumnValue = string.Empty; string SecondColumnValue = string.Empty; string ThirdColumnValue = string.Empty; string ElementName = string.Empty; string ElementGID = string.Empty; DataTable ExcelDataTable = null; DataRow Row; DI7MetaDataBuilder MDBuilder; DI7MetadataCategoryBuilder MDCategoryBuilder; DI7MetadataCategoryInfo MetadataCategory; DIExcel ExcelFile = null; try { // -- Get data table from excel file ExcelFile = new DIExcel(xlsFileNameWPath); ExcelDataTable = ExcelFile.GetDataTableFromSheet(ExcelFile.GetSheetName(0)); // -- create database builder objects MDCategoryBuilder = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries); MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries); // -- import metadata reports with category for (int RowIndex = 1; RowIndex < ExcelDataTable.Rows.Count; RowIndex++) { Row = ExcelDataTable.Rows[RowIndex]; FirstColumnValue = Convert.ToString(Row[0]); SecondColumnValue = Convert.ToString(Row[1]); ThirdColumnValue = Convert.ToString(Row[2]); // get element name if (string.IsNullOrEmpty(ElementName)) { if (string.IsNullOrEmpty(FirstColumnValue)) { break; } else { ElementName = FirstColumnValue; continue; } } // get element gid/id if (string.IsNullOrEmpty(ElementGID)) { if (string.IsNullOrEmpty(FirstColumnValue)) { break; } else { ElementGID = FirstColumnValue; // get element nid by element gid if (metadataType == MetaDataType.Source) { elementNId = this.GetElementNidByGID(ElementName, metadataType); } else { elementNId = this.GetElementNidByGID(ElementGID, metadataType); } // Skip title row by incrementing row index RowIndex++; continue; } } // continue if row is blank if (string.IsNullOrEmpty(FirstColumnValue) && string.IsNullOrEmpty(SecondColumnValue) && string.IsNullOrEmpty(ThirdColumnValue)) { // reset element value elementNId = 0; ElementName = string.Empty; ElementGID = string.Empty; continue; } else if (elementNId > 0) { // import metadata report with metadata category // get metadata category and metedata report MetadataCategory = new DI7MetadataCategoryInfo(); MetadataCategory.CategoryName = SecondColumnValue; MetadataCategory.CategoryGID = FirstColumnValue; MetadataCategory.CategoryType = DIQueries.MetadataElementTypeText[categoryType]; // import metadata category MetadataCategory.CategoryNId = MDCategoryBuilder.CheckNInsertCategory(MetadataCategory); // import metadata report if (MetadataCategory.CategoryNId > 0) { MDBuilder.InsertORUpdateMetadataInfo(metadataType, MetadataCategory.CategoryNId.ToString(), elementNId, this.ReplaceNewLineInMetadataReport(ThirdColumnValue)); } } } } catch (Exception) { RetVal = false; } finally { if (ExcelFile != null) { ExcelFile.Close(); } } return RetVal; }