/// <summary>
/// GENERATE Summary Report Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int SheetNo = 0;
// -- Count Available Sheets in Excel WorkBook
int Count = excelFile.AvailableWorksheetsCount;
// -- Rename or Create First existing sheet to "Summary Report"
if (Count > 0)
{
excelFile.RenameWorkSheet(Constants.Sheet.SummaryReport.SummaryReportSheetIndex, base.ColumnHeader[DSRColumnsHeader.SUMMARY]);
}
else
{ excelFile.CreateWorksheet(base.ColumnHeader[DSRColumnsHeader.SUMMARY]); }
//--Get Sheet No of SummaryReport Sheet
SheetNo = excelFile.GetSheetIndex(base.ColumnHeader[DSRColumnsHeader.SUMMARY]);
// -- Generate Summary Report For Both Comparison and Summary Report as per selection
if (ForComparisonReport)
this.GenerateForComparisonReport(ref excelFile, SheetNo);
else
this.GenerateForSummaryReport(ref excelFile, SheetNo);
}
/// <summary>
/// Create Sheet for Supplied Name
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="sheetName">WorkSheet Name</param>
/// <returns>int</returns>
protected int CreateSheet(ref DIExcel excelFile, string sheetName)
{
int RetVal = 0;
string TempName = string.Empty;
TempName = sheetName;
// -- IF Length of Sheet Name is greater than defined sheet length then Get SheetName for defined length.
if (TempName.Length > Constants.SheetsLayout.SheetNameMaxLength)
{
TempName = TempName.Substring(0, Constants.SheetsLayout.SheetNameMaxLength);
}
TempName = TempName.Replace(@"/","_");
// -- Create Indicator Sheet.
excelFile.CreateWorksheet(TempName);
// -- Get Sheet Index
RetVal = excelFile.GetSheetIndex(TempName);
return RetVal;
}
/// <summary>
/// Create Sheets
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="sheetName">WorkSheet Name</param>
/// <returns>int</returns>
private int CreateSheet(ref DIExcel excelFile, string sheetName, int count)
{
int RetVal = 0;
string TempName = string.Empty;
TempName = this.GetValidSheetName(sheetName, count);
//-- Replace "/" in Sheet Name with "_"
TempName = TempName.Replace(@"/", "_");
// -- Create Indicator Sheet.
excelFile.CreateWorksheet(TempName);
// -- Get Sheet Index
RetVal = excelFile.GetSheetIndex(TempName);
return RetVal;
}
/// <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;
}
