/// <summary> /// Generate Indicator Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { // -- Create Indicator Sheet int SheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.INDICATOR]); DataTable IndicatorTable = null; // -- sheet content excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.INDICATOR]); excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get Indicator DataTable with Values IndicatorTable = this.GetIndicatorDataTable(); this.SetSNoIntoTableColumn(ref IndicatorTable); // -- Fill Indicator Excel Sheet with Data excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, IndicatorTable, SheetNo, false); // -- Apply Font Settings int RowCount = IndicatorTable.Rows.Count; this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, Constants.Sheet.Indicator.DetailsRowIndex + RowCount, Constants.Sheet.Indicator.DetailsRowIndex, true); // -- Get Last Row RowCount = Constants.Sheet.Indicator.DetailsRowIndex + RowCount; // -- Set Column Width excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex, true); //// -- Set Cell Borders //excelFile.SetCellBorder(Constants.Sheet.Indicator.IndicatorDetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black); RowCount += 2; // -- Show Missing Info excelFile.SetCellValue(SheetNo, RowCount, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]); excelFile.GetCellFont(SheetNo, RowCount, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; DataTable MissingTable = null; MissingTable = GetMissingIndicators(IndicatorTable); this.SetSNoIntoTableColumn(ref MissingTable); RowCount += 1; // -- Fill Indicator Excel Sheet with Data excelFile.LoadDataTableIntoSheet(RowCount, Constants.HeaderColIndex, MissingTable, SheetNo, false); // -- Apply Font Settings int LastRow = MissingTable.Rows.Count + RowCount; this.ApplyFontSettings(ref excelFile, SheetNo, RowCount, Constants.HeaderColIndex, LastRow, Constants.Sheet.Indicator.NameColIndex, true); }
/// <summary> /// Create TimePeriod without DataValue Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { int sheetNo = this.CreateSheet(ref excelFile, Constants.SheetsNames.TimeperiodsWithoutData); DataTable Table = null; // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.SheetsNames.TimeperiodsWithoutData); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get TimePeriod Data TAble. Table = this.GetTimeperiodWithoutDataTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex); // -- autofit Map excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, true); }
/// <summary> /// Create FootNote Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { DataTable Table = null; int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]); // -- sheet content excelFile.SetCellValue(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]); excelFile.GetCellFont(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get FootNotes Data TAble. Table = this.GetFootNoteTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.Footnotes.FootNotesDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesNameColIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex); excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex, true); excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true); // -- Set Cell Borders }
/// <summary> /// Create IUS Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { // -- Create Timeperiod Sheet int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.IUS]); DataTable Table = null; // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.IUS]); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.IUS]); excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]); Table = this.GetIUSTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.IUS.IUSDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUS.IUSLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex); excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSCountColINdex, LastRow, Constants.Sheet.IUS.IUSLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex, true); // -- Set Cell Borders excelFile.SetCellBorder(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Medium, Color.Black); }
/// <summary> /// Create IUS Linked To Multiple Classifications Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { DataTable Table = null; int sheetNo = base.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS")); // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS")); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get IUSLinked Data TAble. Table = this.GetIUSLinkedTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex); // -- autofit Map excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true); }
/// <summary> /// Create Unit Sheet of Summary Report /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { // -- Create Unit Sheet int SheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.UNIT]); DataTable UnitTable = null; // -- sheet content excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.UNIT]); excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get Unit Table Filled UnitTable = this.GetUnitTable(); // -- Set SNo into TAble this.SetSNoIntoTableColumn(ref UnitTable); // -- Set Value To Excel Sheet excelFile.LoadDataTableIntoSheet(Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, UnitTable, SheetNo, false); int LastRow = Constants.Sheet.Unit.DetailsRowIndex + UnitTable.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Unit.LastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex, true); }
/// <summary> /// Create IUS NOT Linked TO Classification Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { int sheetNo = this.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION")); DataTable Table = null; // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION")); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get IUSLinked Data TAble. Table = this.GetIUSMissingICTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex); // -- autofit Map excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true); // -- Set Cell Borders excelFile.SetCellBorder(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black); }
/// <summary> /// Generate Sheet for Comparison Report /// </summary> /// <param name="excelFile">Excel File</param> /// <param name="dbConnection">Database Connection </param> /// <param name="dbQueries">DIQueries</param> /// <param name="sheetType">Sheet Type for Comparison Reports</param> internal void GenerateSheet(ref DIExcel excelFile, DIConnection dbConnection, DIQueries dbQueries, SheetType sheetType) { int SheetNo = 0; int CurrentRowIndex = 0; DataTable MissingRecords = null; DataTable AdditionalRecords = null; SheetSource SheetGenerator = null; // -- Get Sheet Class Instance SheetGenerator = SheetSourceFactory.CreateInstance(sheetType, dbConnection, dbQueries); this.NameColIndex = SheetGenerator.NameColumnIndex; this.LastColIndex = SheetGenerator.LastColumnIndex; this.LanguageName = SheetGenerator.GetLanguageName(); // -- Get Missing Records MissingRecords = SheetGenerator.GetMissingRecordsTable(); //-- Get Additional Records AdditionalRecords = SheetGenerator.GetAdditionalRecordsTable(); // if records is morethan 50000 then create multiple sheets if (MissingRecords.Rows.Count > Constants.SheetsLayout.MAXEXCELROWS || (MissingRecords.Rows.Count + AdditionalRecords.Rows.Count) > Constants.SheetsLayout.MAXEXCELROWS) { // -- Create Multiple Sheet this.GenerateSheets(ref excelFile, SheetGenerator.SheetName, MissingRecords, AdditionalRecords); } else { // -- Create Worksheet SheetNo = this.CreateSheet(ref excelFile, SheetGenerator.SheetName, 0); // -- Set Initial Sheet Value this.SetSheetHeading(ref excelFile, SheetNo, SheetGenerator.SheetName); this.SetSheetLanguageValue(ref excelFile, SheetNo); this.SetMissingText(ref excelFile, SheetNo); // -- Load Missing Records Into Sheet excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, MissingRecords, SheetNo, false); CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + MissingRecords.Rows.Count; excelFile.SetCellValue(SheetNo, CurrentRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.ADDITIONAL) + " : " + DBNameForAdditionalRecords); CurrentRowIndex += 1; // -- Load Additional Records Into Sheet excelFile.LoadDataTableIntoSheet(CurrentRowIndex, Constants.HeaderColIndex, AdditionalRecords, SheetNo, false); // -- Apply Font Settings this.ApplyFontSetting(ref excelFile, SheetNo, MissingRecords.Rows.Count); } }
/// <summary> /// Create TimePeriod Sheet /// </summary> /// <param name="excelFile"></param> internal override void GenerateSheet(ref DIExcel excelFile) { int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.AREA]); DataTable Table = null; // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.AREA]); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.AREA]); excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]); // -- Get Area Data TAble. Table = this.GetAreaTable(); excelFile.SetColumnFormatType("H:H",sheetNo, SpreadsheetGear.NumberFormatType.Text); excelFile.SetColumnFormatType("I:I", sheetNo, SpreadsheetGear.NumberFormatType.Text); excelFile.LoadDataTableIntoSheet(Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.Area.AreaDetailsRowIndex + Table.Rows.Count; LastRow += 2; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex, true); // -- Set Missing Area Header excelFile.SetCellValue(sheetNo, LastRow, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]); //excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Bold = true; excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; LastRow += 1; DataTable MissingTable = null; MissingTable = this.GetMissingArea(); excelFile.LoadDataTableIntoSheet(LastRow, Constants.Sheet.SummaryReport.AreaColIndex, MissingTable, sheetNo, false); // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, LastRow, Constants.HeaderColIndex, LastRow + MissingTable.Rows.Count + 1, Constants.Sheet.Area.AreaIDColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.OthersColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaIDColIndex, LastRow, Constants.Sheet.Area.AreaIDColIndex); excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.AreaNameColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex); // -- autofit Map excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaStartDateColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex, true); }
/// <summary> /// Export IUS into IUS Spreadsheet /// </summary> /// <param name="xlsOutputFileName"></param> /// <param name="languageFileNameWPath"></param> /// <returns></returns> public bool ExportIUS(string xlsOutputFileName, string languageFileNameWPath) { bool RetVal = false; int SheetIndex = 0; DataTable IUSSheetTable = null; IWorksheet WorkSheet = null; DIExcel ExcelObj = new DIExcel(); try { // Get IUS Table IUSSheetTable = this.GetIUSTable(); // Rename First Sheet ExcelObj.RenameWorkSheet(0, Constants.IUSSheet.SheetName); WorkSheet = ExcelObj.GetWorkSheet(Constants.IUSSheet.SheetName); SheetIndex = ExcelObj.GetSheetIndex(Constants.IUSSheet.SheetName); // Set Sheet Title and Header Values ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetTitle); ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetName); // Load DataTable Into Worksheet ExcelObj.LoadDataTableIntoSheet(Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, IUSSheetTable, SheetIndex, false); // Set Subgroup Dimension Column Header ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.SubgroupDimensionsColumn); // Merger Subgroup Dimension Column Text ExcelObj.MergeCells(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.DimensionHeaderRowIndex, ExcelObj.GetUsedRange(SheetIndex).ColumnCount - 1); // Format Sheet Contents this.FormatCell(ExcelObj, SheetIndex); // Freeze Pane this.FreezePane(ExcelObj, SheetIndex); // Save Excel File ExcelObj.SaveAs(xlsOutputFileName); RetVal = true; } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (ExcelObj != null) { ExcelObj.Close(); } } return RetVal; }
/// <summary> /// Create DataBase Log Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { DataTable Table = null; // -- Create Timeperiod Sheet int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.DATABASE_LOG]); // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.DATABASE_LOG]); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; Table = this.GetDataBaseLogTable(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex, true); }
/// <summary> /// Create Comments Sheet /// </summary> /// <param name="excelFile">Excel File</param> internal override void GenerateSheet(ref DIExcel excelFile) { DataTable Table = null; int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]); // -- sheet content excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]); excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Get Comments Data TAble. Table = this.GetCommentsTables(); int Counter = 0; foreach (DataRow row in Table.Rows) { //-- Exit IF current Record is For Comments. if (!string.IsNullOrEmpty(row[this.ColumnHeader[DSRColumnsHeader.INDICATOR]].ToString())) { Counter += 1; row[DILanguage.GetLanguageString("SERIAL_NUMBER").ToString()] = Counter; } } Table.AcceptChanges(); excelFile.LoadDataTableIntoSheet(Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false); int LastRow = Constants.Sheet.Comments.CommentsDetailsRowIndex + Table.Rows.Count; // -- Apply Font Settings this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true); // -- Set Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsNameColIndex, LastRow, Constants.Sheet.Comments.CommentsNameColIndex); excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex); // -- Wrap Text of Indicator Column excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true); }
private void WriteDataValue(DIExcel excelFile, Question xmlQuestion,ref int dataValueRowIndex) { string[] SelectedValues =null; int Index=0; switch (xmlQuestion.AnswerType) { case AnswerType.TB: case AnswerType.TBN: case AnswerType.RB: case AnswerType.CB: case AnswerType.DateType: excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.DataValue); break; case AnswerType.SCB: case AnswerType.SRB: break; case AnswerType.CH: SelectedValues = DICommon.SplitStringNIncludeEmpyValue(xmlQuestion.DataValue, ","); foreach (AnswerTypeOption Option in xmlQuestion.Options) { excelFile.GetCellFont(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex).Name = "Wingdings"; if (SelectedValues[Index] == "1") { excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(252) ); } else { excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(251)); } excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex + 1, Option.Text); dataValueRowIndex += 1; Index += 1; } break; case AnswerType.GridType: int StartRowIndex=dataValueRowIndex; int EndRowIndex; int EndColIndex; excelFile.LoadDataTableIntoSheet(dataValueRowIndex,Constants.DataValueColumnIndex, xmlQuestion.GridTable,Constants.SheetIndex, true); dataValueRowIndex += xmlQuestion.GridTable.Rows.Count; EndRowIndex=dataValueRowIndex-1; EndColIndex =Constants.DataValueColumnIndex+ xmlQuestion.GridTable.Columns.Count-1; excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideHorizontal); excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideVertical); excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeBottom); excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeTop); excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeLeft); excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeRight); break; case AnswerType.Calculate: break; case AnswerType.Aggregate: break; default: break; } }
/// <summary> /// Generate Multiple Sheets for having records more than 50,000 /// </summary> /// <param name="excelFile">Excel File</param> /// <param name="sheetName">Sheet Name </param> /// <param name="missingRecords">Missing Records DataTable</param> /// <param name="additionalRecords">Additional Records DataTable</param> private void GenerateSheets(ref DIExcel excelFile, string sheetName, DataTable missingRecordsTable, DataTable additionalRecordsTable) { int CurrentRowIndex = 0; int MissingRecordCount = 0; int AdditionalRecordCount = 0; int SheetNo = 0; int FirstRowNo = 0; DataTable MissingRecords = null; DataTable AdditionalRecords = null; DataTable TempTable = null; MissingRecords = missingRecordsTable; AdditionalRecords = additionalRecordsTable; MissingRecordCount = (MissingRecords != null) ? MissingRecords.Rows.Count : 0; AdditionalRecordCount = (AdditionalRecords != null) ? AdditionalRecords.Rows.Count : 0; if ((MissingRecordCount + AdditionalRecordCount) > 0) { SheetNo = this.CreateSheet(ref excelFile, sheetName, this.SheetCounter); // -- Set Sheet Headers this.SetSheetHeading(ref excelFile, SheetNo, sheetName); this.SetSheetLanguageValue(ref excelFile, SheetNo); this.SetMissingText(ref excelFile, SheetNo); this.SheetCounter += 1; try { // -- Write Missing Records If Its Not Null if (MissingRecords != null) { if (MissingRecords.Rows.Count <= Constants.SheetsLayout.MAXEXCELROWS) { excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, MissingRecords, SheetNo, false); MissingRecordCount = MissingRecords.Rows.Count; MissingRecords = null; } else { FirstRowNo = Convert.ToInt32(MissingRecords.Rows[0][DILanguage.GetLanguageString("SERIAL_NUMBER")]); // -- Get Records Less than Maximum Excel sheet Rows DataRow[] Rows = MissingRecords.Select("[" + DILanguage.GetLanguageString("SERIAL_NUMBER") + "]" + " < " + (FirstRowNo + Constants.SheetsLayout.MAXEXCELROWS)); TempTable = MissingRecords.Clone(); foreach (DataRow Row in Rows) { TempTable.ImportRow(Row); } excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, TempTable, SheetNo, false); MissingRecordCount = Constants.SheetsLayout.MAXEXCELROWS; for (int i = 0; i < MissingRecordCount; i++) { MissingRecords.Rows[i].Delete(); } MissingRecords.AcceptChanges(); } } CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + MissingRecordCount; excelFile.SetCellValue(SheetNo, CurrentRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.ADDITIONAL) + " : " + DBNameForAdditionalRecords); CurrentRowIndex += 1; // -- Write Additional Records If Its Not Null if (AdditionalRecords != null) { if ((MissingRecordCount + AdditionalRecords.Rows.Count) <= Constants.SheetsLayout.MAXEXCELROWS) { // -- Load Additional Records Into Sheet excelFile.LoadDataTableIntoSheet(CurrentRowIndex, Constants.HeaderColIndex, AdditionalRecords, SheetNo, false); AdditionalRecordCount = 0; //MissingRecordCount = 0; AdditionalRecords = null; } else { int Counter = 0; FirstRowNo = Convert.ToInt32(AdditionalRecords.Rows[0][DILanguage.GetLanguageString("SERIAL_NUMBER")]); // -- Get Records Less than Maximum Excel sheet Rows DataRow[] Rows = AdditionalRecords.Select("[" + DILanguage.GetLanguageString("SERIAL_NUMBER") + "]" + " < " + ((FirstRowNo + Constants.SheetsLayout.MAXEXCELROWS) - MissingRecordCount)); TempTable = AdditionalRecords.Clone(); foreach (DataRow Row in Rows) { TempTable.ImportRow(Row); Counter += 1; } // -- Load Additional Records Into Sheet excelFile.LoadDataTableIntoSheet(CurrentRowIndex, Constants.HeaderColIndex, TempTable, SheetNo, false); // -- Get Extra Records for (int i = 0; i < Counter; i++) { AdditionalRecords.Rows[i].Delete(); } AdditionalRecords.AcceptChanges(); AdditionalRecordCount = AdditionalRecords.Rows.Count; } } this.ApplyFontSetting(ref excelFile, SheetNo, MissingRecordCount); MissingRecordCount = (MissingRecords != null) ? MissingRecordCount : 0; AdditionalRecordCount = (AdditionalRecords != null) ? AdditionalRecordCount : 0; if ((MissingRecordCount + AdditionalRecordCount) > 0) { this.GenerateSheets(ref excelFile, sheetName, MissingRecords, AdditionalRecords); } } catch (Exception) { throw; } } }
/// <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; }