/// <summary> /// Extract keyword from DataTable and update Database with Keywords /// </summary> /// <param name="presentationFile">Excel work book to read</param> private int GetKeywordAndUpdateDatabase(Presentation.PresentationType presType, String presTitle, String userKeyword, DataTable presentationDataTable, string PresFile) { int RetVal = -1; // If File is an xls File then get presTitle,UKeyword from Excel File System.Data.DataTable ExcelSheetDataTable; System.Data.DataTable UKeywordsDataTable; String Pres_FileName = string.Empty; PresentationType Pres_Type = PresentationType.M; String Title = String.Empty; String UKeyword = String.Empty; String Keywords = String.Empty; String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename int Map_Suffix_Lenght = 10; //" - Map.xls" try { //If Excel File. if (PresFile.EndsWith(DICommon.FileExtension.Excel)) { // Using Excel Control Library DIExcel DIExl = new DIExcel(PresFile); // Getting Pres fileName For Database Field Pres_FileName = Path.GetFileName(PresFile); // Get Presentation Type pres type abbreviation. Which is entered in Database.M:Map ,T:Table,G:Graph Pres_Type = GetPresTypeForGalleryDB(presType); // Get title from excel Presentaion // In case of Map Title Column will be name of the file minus " - Map.xls" if (presType==Presentation.PresentationType.Map) { // In case of Map Title Column will be name of the file minus " - Map.xls" Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl); //Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght); } // Presentaion is table or Graph else { // Get Presentation Title Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl); } // --- Get UserKeywords from Excel Presentaion --- // Get Keywords Sheet into DataTable UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS); // Get UserKeyword UKeyword = this.GetUserKeyWords(UKeywordsDataTable); } //In case of Web application title and Ukeyword will be same as passed by client application else { Title = presTitle; UKeyword = userKeyword; Pres_Type = GetPresTypeForGalleryDB(presType); } //Getting Keywords from presentation Data table Keywords = this.GetKeyWordsFromPresentationDataTable(presentationDataTable); // Update Database RetVal = UpdateDBTables(PresFile, Pres_Type, Title, UKeyword, Keywords); Pres_Nid = RetVal; } catch (Exception ex) { //Successs = false; } return RetVal; }
/// <summary> /// Read excel workbook and update Database with Keywords /// </summary> /// <param name="presentationFile">Excel work book to read</param> private Int32 GetKeywordAndUpdateDatabase_Old(string presentationFile) { Int32 RetVal = 0; System.Data.DataTable ExcelSheetDataTable; System.Data.DataTable UKeywordsDataTable; String Pres_FileName = string.Empty; PresentationType Pres_Type = PresentationType.M; String Title = String.Empty; String UKeyword = String.Empty; String Keywords = String.Empty; String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename int Map_Suffix_Lenght = 10; //" - Map.xls" try { // Using Excel Control Library DIExcel DIExl = new DIExcel(presentationFile); // Getting Pres fileName For Database Field Pres_FileName = Path.GetFileName(presentationFile); //Start of getting Data from Excel Sheet // Check For Presention Type if (presentationFile.Contains(Presentation.Map_Suffix)) // Presention is Map : Get Title and Data { // Get Presentation Type for Database table when presentation file is Map //Pres_Type = Pres_Type_M; Pres_Type = PresentationType.M; // In case of Map Title Column will be name of the file minus " - Map.xls" Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght); // Using Excel Control Function to get value from Sheet ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.MAPDATA); } else // Get Title and Data for Graph and Map Presentioan { // Get Presentation Type for Database table when presentation file is table or Graph // Setting Presention Type On the basis of suffix if (presentationFile.Contains(Presentation.Graph_Suffix) || presentationFile.ToLower().Contains(Presentation.Graph_Suffix.ToLower())) { Pres_Type = PresentationType.G; } else if (presentationFile.Contains(Presentation.Table_Suffix) || presentationFile.ToLower().Contains(Presentation.Table_Suffix.ToLower())) { Pres_Type = PresentationType.T; } // Get Presentation Title Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl); // DataTable In case of Graph or Table ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.DATA); } //End of getting Data from Excel Sheet //Getting Keywords Keywords = this.GetKeyWordsFromPresentationDataTable(ExcelSheetDataTable); // --- Get UserKeywords --- // Get Keywords Sheet into DataTable UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS); // Get UserKeyword UKeyword = this.GetUserKeyWords(UKeywordsDataTable); // Update Database Pres_Nid = UpdateDBTables(presentationFile, Pres_Type, Title, UKeyword, Keywords); RetVal = Pres_Nid; } catch (Exception ex) { //Successs = false; } return RetVal; }
/// <summary> /// Read excel workbook and update Database with Keywords /// This will Get UserKeywords from Excel and Call overloaded GetKeywordAndUpdateDatabase function with /// two parameter presFile path and UKeywords /// </summary> /// <param name="presentationFile">Excel work book to read</param> private Int32 GetKeywordAndUpdateDatabase(string presentationFile) { Int32 RetVal = 0; System.Data.DataTable UKeywordsDataTable; String Pres_FileName = string.Empty; String UKeyword = String.Empty; String Keywords = String.Empty; String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename try { // Using Excel Control Library DIExcel DIExl = new DIExcel(presentationFile); // --- Get UserKeywords --- // Get Keywords Sheet into DataTable UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS); // Get UserKeyword UKeyword = this.GetUserKeyWords(UKeywordsDataTable); //close excel DIExl.Close(); // Call Other overload RetVal = this.GetKeywordAndUpdateDatabase(presentationFile, UKeyword); } catch (Exception ex) { } return RetVal; }
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 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; }
/// <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; }
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; }