/// <summary>
/// Apply Font Setting for Summary Report Sheet.
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
/// <param name="icRow">Last Record RowIndex Of sheet</param>
private void SetSummaryReportFontSettings(ref DIExcel excelFile, int sheetNo, int icRow, int startRowIndex)
{
// -- Apply font settings
excelFile.GetRangeFont(sheetNo,startRowIndex+ Constants.Sheet.SummaryReport.ICHeaderCellIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex,icRow,Constants.Sheet.SummaryReport.ICHeaderCellIndex ).Bold = true;
if (DatabaseSummaryReportGenerator.FontName != null)
{
excelFile.GetRangeFont(sheetNo, Constants.Sheet.SummaryReport.ICHeaderColValueIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex, icRow, Constants.Sheet.SummaryReport.ICHeaderLastColIndex).Name = DatabaseSummaryReportGenerator.FontName;
excelFile.GetRangeFont(sheetNo, Constants.Sheet.SummaryReport.ICHeaderColValueIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex, icRow, Constants.Sheet.SummaryReport.ICHeaderLastColIndex).Size = (double)DatabaseSummaryReportGenerator.FontSize;
}
}
///<summary>
/// Set Summary Report Record Header and Its Value.
/// </summary>
/// <param name="ExcelSheet">Excel File</param>
/// <param name="SheetNo">Sheet No</param>
private void FeedSummaryReportValues(ref DIExcel excelFile,int sheetNo, int startRowPosition)
{
// -- count - total Records in the Database
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.CountRowIndex, Constants.Sheet.SummaryReport.CountColIndex, Constants.Sheet.SummaryReport.CountColValueIndex, DSRColumnsHeader.COUNT, DatabaseSummaryReportGenerator.DBQueries.TablesName.Data, startRowPosition);
// -- Total indicators
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.IndicatorRowIndex, Constants.Sheet.SummaryReport.IndicatorColIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, DSRColumnsHeader.INDICATOR, DatabaseSummaryReportGenerator.DBQueries.TablesName.Indicator, startRowPosition);
// -- Total units
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.UnitRowIndex, Constants.Sheet.SummaryReport.UnitColIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, DSRColumnsHeader.UNIT, DatabaseSummaryReportGenerator.DBQueries.TablesName.Unit, startRowPosition);
// -- Total subgroups
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUP, DatabaseSummaryReportGenerator.DBQueries.TablesName.SubgroupVals, startRowPosition);
// -- Add Subgroup Type and Subgroup Dimension Info For DI6 Database.
if (DICommon.ISDevInfo6Database(DatabaseSummaryReportGenerator.SourceDatabaseNameWPath))
{
// -- Total subgroups Type
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupTypeRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUPTYPE, DatabaseSummaryReportGenerator.DBQueries.TablesName.SubgroupType, startRowPosition);
// -- Total subgroups Dimension
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupDimensionRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUPDIMENSION, DatabaseSummaryReportGenerator.DBQueries.TablesName.Subgroup, startRowPosition);
}
// -- Total IUS
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.IUSRowIndex, Constants.Sheet.SummaryReport.IUSColIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, DSRColumnsHeader.IUS, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorUnitSubgroup, startRowPosition);
// -- Area
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaColIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, DSRColumnsHeader.AREA, DatabaseSummaryReportGenerator.DBQueries.TablesName.Area, startRowPosition);
// -- Area total levels
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, DSRColumnsHeader.LEVEL, DatabaseSummaryReportGenerator.DBQueries.TablesName.Area, startRowPosition);
// --Set TimePeriod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.TimeRowIndex, Constants.Sheet.SummaryReport.TimeColIndex, Constants.Sheet.SummaryReport.TimeColValueIndex, DSRColumnsHeader.TIMEPERIOD, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Set Minimum Timeperiod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.TimeRowIndex, Constants.Sheet.SummaryReport.TimeMinMaxColIndex, Constants.Sheet.SummaryReport.TimeMinMaxColValueIndex, DSRColumnsHeader.MIN, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Maximum Timeperiod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.MaxTimeRowIndex, Constants.Sheet.SummaryReport.TimeMinMaxColIndex, Constants.Sheet.SummaryReport.TimeMinMaxColValueIndex, DSRColumnsHeader.MAX, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Total sources
int SourceRowindex = startRowPosition + Constants.Sheet.SummaryReport.SourceRowIndex;
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref SourceRowindex, Constants.Sheet.SummaryReport.SourceColIndex, Constants.Sheet.SummaryReport.SourceColValueIndex, DSRColumnsHeader.SOURCE, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Source);
// -- Set language
this.SetLanguageValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.LanguageColIndex, Constants.Sheet.SummaryReport.LanguageNameColValueIndex, DSRColumnsHeader.LANGUAGE, DatabaseSummaryReportGenerator.DBQueries.TablesName.Language);
// -- Set Indicator Classification
this.SetICTotalValues(ref excelFile, sheetNo, startRowPosition);
try
{
// -- Set Column Font Bold
excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex).Bold = true;
excelFile.AutoFitColumns(sheetNo, startRowPosition + Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex);
}
catch
{}
}
/// <summary>
/// Set IC Level
/// </summary>
/// <param name="eicType">Enum ICTYpe</param>
private void SetICLevel(ref DIExcel excelSheet, int sheetNo, ICType eicType, int rowIndex, int colValueIndex)
{
DataRow[] objRows = null;
DataRow NewRow = null;
string[] Temp = null;
Int32 MaxLevel = 0;
DataTable Table = base.GetTempTable();
// -- Get Indicator Classification Value
DataView TempTable = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DatabaseSummaryReportGenerator.DBQueries.IndicatorClassification.GetIC(FilterFieldType.None, string.Empty, eicType, FieldSelection.Light)).DefaultView;
TempTable.Sort = IndicatorClassifications.ICParent_NId + " ASC, " + IndicatorClassifications.ICName;
DataTable dt = new DataTable();
foreach (DataRowView ROW in TempTable)
{
NewRow = Table.NewRow();
// -- Get Records for IC ParentNID
objRows = Table.Select(Constants.ICTempTableColumns.ID + "=" + ROW[IndicatorClassifications.ICParent_NId].ToString());
NewRow[Constants.ICTempTableColumns.ID] = ROW[IndicatorClassifications.ICNId];
NewRow[Constants.ICTempTableColumns.PARENT_ID] = ROW[IndicatorClassifications.ICParent_NId];
NewRow[Constants.ICTempTableColumns.ACT_LABEL] = ROW[IndicatorClassifications.ICName].ToString();
// -- Get Level If Any NidID have Parent_NID
if (objRows.Length > 0)
{
string[] tempVal = new string[] { Constants.ICTempTableColumns.ICLEVEL_SEPERATOR };
Temp = objRows[0][Constants.ICTempTableColumns.LABEL].ToString().Split(tempVal, StringSplitOptions.RemoveEmptyEntries);
// -- Set Maximum level if array lengh is greater than MaxLength.
if (MaxLevel < Temp.Length)
{ MaxLevel = Temp.Length; }
NewRow[Constants.ICTempTableColumns.LABEL] = objRows[0][Constants.ICTempTableColumns.LABEL].ToString() + Constants.ICTempTableColumns.ICLEVEL_SEPERATOR + ROW[IndicatorClassifications.ICName].ToString();
}
else
{ NewRow[Constants.ICTempTableColumns.LABEL] = ROW[IndicatorClassifications.ICName]; }
Table.Rows.Add(NewRow);
}
Table.AcceptChanges();
int CountICByLevel = 0;
// -- Set Level IF Table has records
if (Table.Rows.Count > 0)
{
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, base.ColumnHeader[DSRColumnsHeader.LEVEL]);
excelSheet.GetRangeFont(sheetNo, rowIndex, colValueIndex, rowIndex, colValueIndex).Bold = true;
for (int i = 0; i <= MaxLevel; i++)
{
// -- Increase Column Index
colValueIndex += 1;
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, (i + 1));
// -- Increase Row Index for Value
rowIndex += 1;
CountICByLevel = this.GetCountICLavel(Table, i);
// -- Set Lavel Value
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, CountICByLevel);
// -- Decrease rowindex for next Lavel.
rowIndex -= 1;
}
}
}
/// <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 Heading Name of Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="sheetNo">Sheet Index</param>
private void SetSheetHeading(ref DIExcel excelFile, int sheetNo, string sheetName)
{
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, sheetName);
excelFile.GetRangeFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.MissingTextRowIndex, Constants.HeaderColIndex).Bold = true;
}
/// <summary>
/// Set Font Style in Reports
/// </summary>
/// <param name="excelFile">Excel File</param>
private void ApplyFontSetting(ref DIExcel excelFile, int sheetNo, int missingRecordsCount)
{
int CurrentRowIndex = 0;
// -- Set Font Of Missing Records
excelFile.GetRangeFont(sheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, Constants.Sheet.Indicator.DetailsRowIndex, this.LastColIndex).Bold = true;
CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + missingRecordsCount;
// -- Set Font Of Addtional Records
excelFile.GetRangeFont(sheetNo, CurrentRowIndex - 1, Constants.HeaderColIndex, CurrentRowIndex + 1, this.LastColIndex).Bold = true;
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.DetailsRowIndex, Constants.Sheet.Indicator.NameColIndex, CurrentRowIndex, this.NameColIndex);
// -- Wrap Text of Name Column
excelFile.WrapText(sheetNo, Constants.DetailsRowIndex, this.NameColIndex, CurrentRowIndex, this.NameColIndex, true);
}
/// <summary>
/// Set Missing Text and Fonts
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
private void SetMissingText(ref DIExcel excelFile, int sheetNo)
{
excelFile.SetCellValue(sheetNo, Constants.MissingTextRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.MISSING) + " : " + DBNameForMissingRecords);
// -- Set Headers Font Bold.
excelFile.GetRangeFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.MissingTextRowIndex, Constants.HeaderColIndex).Bold = true;
}
/// <summary>
/// Apply Font Settings
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="sheetNo">Excel Sheet Index</param>
/// <param name="firstRowIndex">Start Row Index</param>
/// <param name="lastRowIndex">End Row Index</param>
/// <param name="firstColIndex">Start Column Index</param>
/// <param name="lastColIndex">End Column Index</param>
/// <param name="boldHeader">Set Heading Font Bold</param>
protected void ApplyFontSettings(ref DIExcel excelFile, int sheetNo, int firstRowIndex, int firstColIndex, int lastRowIndex, int lastColIndex, bool boldHeader)
{
excelFile.GetRangeFont(sheetNo, firstRowIndex, firstColIndex, lastRowIndex, lastColIndex).Name = DatabaseSummaryReportGenerator.FontName ;
excelFile.GetRangeFont(sheetNo, firstRowIndex, firstColIndex, lastRowIndex, lastColIndex).Size = DatabaseSummaryReportGenerator.FontSize ;
// -- Set First Row Header Bold
if (boldHeader == true)
{
excelFile.GetRangeFont(sheetNo, firstRowIndex, firstColIndex, firstRowIndex, lastColIndex).Bold = true;
this.SetCellsBorder(ref excelFile, sheetNo, firstRowIndex, firstColIndex, lastColIndex);
}
}
/// <summary>
/// Initialize WorkSheet with Heading and Columns names and set font properties.
/// </summary>
/// <param name="RangeCheckSheet"></param>
/// <param name="SheetNo"></param>
private void SetWorkbookInitialValue(ref DIExcel RangeCheckSheet, int SheetNo)
{
//-- Set Report Heading
RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SourceHeaderColIndex, this.ColumnsHeader [DRCColumnsHeader.DataRangeCheckReport]);
//-- Set Font and Size
RangeCheckSheet.GetCellFont(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex).Bold = true;
RangeCheckSheet.GetCellFont(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex).Size = RangeCheckCustomizationInfo.HeaderFontSize;
//-- Set Font Bold
RangeCheckSheet.GetRangeFont(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.SourceDataColIndex).Bold = true;
//-- Display the database file name
RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SourceHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Source ]);
RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SourceDataColIndex, this._SourceDatabaseNameWPath );
//-- Set Date and Time Value
RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.DateTimeHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.DateandTime]);
string CurDateTime;
CurDateTime = DateTime.Now.ToString();
RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.DateTimeDataColIndex, CurDateTime);
}
/// <summary>
/// Generate Range Check Report
/// </summary>
/// <returns></returns>
public bool GenerateExcelReport()
{
bool RetVal = false;
DataTable ReportTable;
int SheetNo = 0;
int ProgressBarValue=0;
//-- Initialize Collection
this.InitColumnHeading();
//-- Check Selected File Selected
if (this.ReportDestinationFilePath.Length > 0)
{
DIExcel RangeCheckSheet = new DIExcel();
string NumDecSeparator = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
//-- Problem with Different Locale of Office and Regional(Setting)
System.Threading.Thread ThisThread = System.Threading.Thread.CurrentThread ;
System.Globalization.CultureInfo OriginalCulture = ThisThread.CurrentCulture ;
//-- Create Worksheet named "Range Check"
RangeCheckSheet.CreateWorksheet(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
SheetNo = RangeCheckSheet.GetSheetIndex(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
this.SetWorkbookInitialValue( ref RangeCheckSheet, SheetNo);
ThisThread.CurrentCulture = OriginalCulture;
//-- Fill DataTable
ReportTable = this.DBConnection.ExecuteDataTable(DBQueries.Data.GetValuesRangeCheck());
// TempTable.Merge(ReportTable,false);
int maxValue = ReportTable.Rows.Count;
try
{
ThisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//-- Proceed If datatable has Records
if (ReportTable.Rows.Count > 0)
{
// Initialize progress bar
this.RaiseProgressBarInitialize(maxValue + 1);
int RowNum = RangeCheckFileRowsInfo.DataStartingRowIndex;
int LastIUSNID = 0;
//-- Fill Record Indicator wise
foreach (DataRow rowval in ReportTable.Rows)
{
// -- Avoid Process if IUSNID is Last IUSNID
if (Convert.ToInt32(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]) != LastIUSNID)
{
LastIUSNID = Convert.ToInt32(rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]);
//-- Set Data Column Header Bold
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum + RangeCheckFileRowsInfo.HeaderRowCount, RangeCheckFileRowsInfo.SheetHeaderColIndex).Bold = true;
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Indicator]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator.IndicatorName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Unit]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Subgroup]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal]));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Min]);
// -- Get Minimum Value
double MinValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MinValue])? Convert.ToDouble("0.0") :Convert.ToDouble( rowval[Indicator_Unit_Subgroup.MinValue])) ;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MinValue);
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Max]);
double MaxValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MaxValue]) ? Convert.ToDouble("0.0") : Convert.ToDouble(rowval[Indicator_Unit_Subgroup.MaxValue]));
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MaxValue);
RowNum += 1;
DataTable Table=null;
DataTable OtherTable=null;
// -- insert timeperiod, areaid,area name,datavalue,source
#region "-- Change No: c1 --"
if (MaxValue > MinValue)
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID,RangeType.DataBelow_MinVal ).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MaxVal).Select());
}
else if (MaxValue == 0.0 && MinValue == 0.0) { }
else
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MinVal).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataBelow_MaxVal).Select());
}
#endregion
////if (MaxValue > MinValue)
////{
//// Table = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID )); //+ " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MinValue ));
//// OtherTable = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID ));//+ " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MaxValue));
////}
////else if (MaxValue == 0.0 && MinValue == 0.0) { }
////else
////{
//// Table = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MinValue));
//// OtherTable = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MaxValue));
////}
if (Table != null)
{
// -- Merge Both Table
Table.Merge(OtherTable, true);
//-- Check if Records Exist in DataTable Then Export to Excel Workbook
if (Table.Rows.Count > 0)
{
//-- Set Data Column Header Font
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex).Italic = true;
//-- Set Column Backgroud Color TO Grey
RangeCheckSheet.SetRangeColor(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex, Color.Black, System.Drawing.Color.FromArgb(Color.Gray.R, Color.Gray.G, Color.Gray.B));
//-- Raname DataTable Column Name As Per Excel Sheet
this.RenameColumn(ref Table);
//-- Load DataTAble Into Excel
RangeCheckSheet.LoadDataTableIntoSheet(RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, Table, SheetNo, false);
}
RowNum += Table.Rows.Count + 1;
}
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
} //End Of Next
//-- Increase ProgressBar Value
ProgressBarValue++;
//-- IF numbers of record in dataview is morethan the max rows i.e 50,000 available in excel
if (RowNum > RangeCheckCustomizationInfo.MAX_EXCEL_ROWS )
{
break;
}
}
//-- Set WorkSheet Border Line width
this.SetColumnWidth(ref RangeCheckSheet, SheetNo);
this.SetSheetBorder(ref RangeCheckSheet, SheetNo);
} //End OF IF
//-- Make Progressbar Value to Maximum
this.RaiseProgressBarIncrement(maxValue );
try
{
// -- Save the Workbook If file exist then delete and then Save
if (System.IO.File.Exists(this.ReportDestinationFilePath))
{
System.IO.File.SetAttributes(this.ReportDestinationFilePath, FileAttributes.Normal);
System.IO.File.Delete(this.ReportDestinationFilePath);
}
RangeCheckSheet.SaveAs(this.ReportDestinationFilePath);
this.RaiseProgressBarClose();
RetVal = true;
}
catch
{
RetVal = false;
}
}
catch (Exception ex)
{
this.RaiseProgressBarClose();
throw new ApplicationException(ex.Message);
}
}//End OF IF
return RetVal;
}
private void FormatCell(DIExcel excelObj, int sheetIndex)
{
try
{
// Set Title Font
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex).Bold = true;
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex).Size = 12;
// Set Name Font
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex).Size = 10;
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex).Bold = true;
excelObj.GetRange(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1);
// Set Table Header backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TableStartRowIndex, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, Color.Black, Color.LightGray);
// Set GUID Column backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.GuidColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.GuidColIndex, Color.Black, Color.LightGray);
// Set Subgroup Dimension Column backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, Color.Black, Color.LightGray);
// Set Bottom Border color for Subgroup Dimensions
excelObj.SetRangeBorder(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex,Constants.IUSSheet.DimensionHeaderRowIndex,excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, LineStyle.Continuous, BorderWeight.Thin,Color.Black,BordersIndex.EdgeBottom);
// Set Font Size
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Size = 8;
// Set Font Bold
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TableStartRowIndex, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Bold = true;
// Set Font Name
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Name = Constants.IUSSheet.SheetFontName;
// Center Align
excelObj.SetHorizontalAlignment(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, HAlign.Center);
// Set S.No Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.SnoColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.FirstColumnIndex);
// Set GUID Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.GUIDColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.GuidColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.GuidColIndex);
// Set Indicator Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.IndicatorColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.IndicatorColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.IndicatorColIndex);
// Set Unit Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.UnitColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.UnitColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.UnitColIndex);
// Set Subgroup Dimensions Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.SubgroupColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.SgValColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.SgValColIndex);
// Set Subgroup Dimensions Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.DimensionsColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.DimensionStartColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
