/// <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); } }