/// <summary> /// Set Indicator Classification Value For Summary Report /// </summary> /// <param name="excelFile"></param> /// <param name="sheetNo"></param> /// <param name="rowIndex"></param> /// <param name="colHeaderIndex"></param> /// <param name="colValueIndex"></param> /// <param name="headerType">Enum for Summary Report Column Header</param> /// <param name="tableName"></param> /// <param name="icType">Enum Classification Type(ICType)</param> private void SetReportsHeaderValues(ref DIExcel excelFile, int sheetNo,ref int rowIndex, int colHeaderIndex, int colValueIndex, DSRColumnsHeader headerType, string tableName, ICType icType) { string SqlQuery; //-- Set Record Header Value excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]); try { switch (headerType) { case DSRColumnsHeader.SECTOR: //-- Set value for ICs. case DSRColumnsHeader.THEME: case DSRColumnsHeader.SOURCE: case DSRColumnsHeader.GOAL: case DSRColumnsHeader.INSTITUTION: case DSRColumnsHeader.CF: case DSRColumnsHeader.CONVENTION: //--Query is like SELECT count(*) from " + msDBPrefix + "Indicator_Classifications" + msDBLngCode + " WHERE IC_Type='SC'" SqlQuery = DIQueries.GetTableRecordsCount(tableName, IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[icType]); excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery))); colValueIndex += 1; this.SetICLevel(ref excelFile, sheetNo, icType, rowIndex, colValueIndex); break; } rowIndex += 2; } catch { }//(Exception ex) throw ex } }
/// <summary> /// Set Summary Report Count Value Into "Summary Report" WorkSheet /// </summary> /// <param name="ExcelSheet"></param> /// <param name="SheetNo"></param> /// <param name="RowIndex"></param> /// <param name="ColHeaderIndex"></param> /// <param name="ColValueIndex"></param> /// <param name="HeaderType"></param> /// <param name="TableName"></param> private void SetReportsHeaderValues(ref DIExcel excelFile,int sheetNo,int rowIndex, int colHeaderIndex, int colValueIndex,DSRColumnsHeader headerType,string tableName,int startRowPosition) { string SqlQuery; //-- Set Record Header Value excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]); try { switch (headerType ) { case DSRColumnsHeader.IUS : SqlQuery = DatabaseSummaryReportGenerator.DBQueries.IUS.GetIUS(FilterFieldType.None,string.Empty,FieldSelection.NId); excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(SqlQuery).Rows.Count)); break; case DSRColumnsHeader.LEVEL: SqlQuery = DatabaseSummaryReportGenerator.DBQueries.Area.GetMaxAreaLevelFrmAreaTable(); int MaxVal = (int)DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery); int Counter; if (this.CurrentSheetType == SummarySheetType.Detailed) { //-- Set "Count OF DATAVALUE" Cell Value excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex, base.ColumnHeader[DSRColumnsHeader.COUNT] + " " + base.ColumnHeader[DSRColumnsHeader.OF] + " " + base.ColumnHeader[DSRColumnsHeader.DATAVALUE]); //-- Set "SUM OF DATAVALUE" Cell Value excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex, base.ColumnHeader[DSRColumnsHeader.SUM] + " " + base.ColumnHeader[DSRColumnsHeader.OF] + " " + base.ColumnHeader[DSRColumnsHeader.DATAVALUE]); try { excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex).Bold = true; excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex).Bold = true; } catch {} } for (Counter = 1; Counter <= MaxVal; Counter++) { excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex + Counter, Counter); SqlQuery = DIQueries.GetTableRecordsCount(tableName, Convert.ToString(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Area.AreaLevel + "=" + Counter)); excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelRowValueIndex, colValueIndex + Counter, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery))); if (this.CurrentSheetType == SummarySheetType.Detailed) { //-- Set Count OF Data VAlue excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colValueIndex + Counter, this.GetCountOFDataBYAreaLevel(Counter)); excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colValueIndex + Counter, this.GetSumOFDataBYAreaLevel(Counter)); } } break; case DSRColumnsHeader.MIN : //-- Set Value for Minimum Timeperiod. SqlQuery = DIQueries.GetMinValue(tableName,Convert.ToString( DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Timeperiods.TimePeriod),string.Empty); excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery))); break; case DSRColumnsHeader.MAX : //-- Set Value for Minimum Timeperiod. SqlQuery = DIQueries.GetMaxValue(tableName, DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Timeperiods.TimePeriod,string.Empty); excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery))); break; default ://-- Default Process SqlQuery = DIQueries.GetTableRecordsCount(tableName,string.Empty); excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery))); break; } } catch //(Exception ex) { // throw ex; } }
/// <summary> /// Set Language Value into Sheet /// </summary> /// <param name="ExcelSheet">worksheet in use</param> /// <param name="SheetNo">Sheet number</param> /// <param name="RowIndex">Row Index</param> /// <param name="ColHeaderIndex">Header Column Index</param> /// <param name="ColValueIndex">Column Value Index</param> /// <param name="HeaderType">Enum for ColumnHeader</param> /// <param name="TableName">Table Name</param> private void SetLanguageValues(ref DIExcel excelFile, int sheetNo,int rowIndex, int colHeaderIndex, int colValueIndex,DSRColumnsHeader headerType,string tableName) { int Counter=0; //-- Set Record First Value excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]); DataView ReportTable = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DIQueries.GetLangauges(DatabaseSummaryReportGenerator.DBQueries.DataPrefix)).DefaultView; for ( Counter = 0; Counter <= ReportTable.Count - 1; Counter++) { // -- Make default language font Bold if (Convert.ToBoolean(ReportTable[Counter][Language.LanguageDefault])) { excelFile.GetCellFont(sheetNo, rowIndex, colValueIndex).Bold = true; } excelFile.SetCellValue(sheetNo, rowIndex + Counter,colValueIndex,ReportTable[Counter][Language.LanguageName]); } // -- language count excelFile.SetCellValue(sheetNo, rowIndex, Constants.Sheet.SummaryReport.LanguageColCountValueIndex, ReportTable.Count); }