/// <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 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 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 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 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> /// 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> /// 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 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); }
/// <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); }
private void WriteQuestionIntoExcel(DIExcel excelFile, Question xmlQuestion,ref int RowIndex) { int DataValueRowIndex = RowIndex + 1; int BoldStartIndex = -1; int BoldEndIndex = -1; int ItalicStartIndex = -1; int ItalicEndIndex = -1; string BoldStartTag="<b>"; string ItalicStartTag="<i>"; string BoldEndTag = "</b>"; string ItalicEndTag = "</i>"; string QuestionText=xmlQuestion.Text; //set question no and question text excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex, xmlQuestion.No); excelFile.SetColumnWidth(Constants.SheetIndex, 6, RowIndex, Constants.QuestionNoColumnIndex, RowIndex, Constants.QuestionNoColumnIndex); //replace <br> with new line character and remove enter key QuestionText= QuestionText.Replace(Char.ConvertFromUtf32(13), string.Empty); QuestionText = QuestionText.Replace("\n", string.Empty); QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.CrLf, string.Empty); QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.Lf.ToString(), string.Empty); QuestionText=QuestionText.Replace("<br>", Microsoft.VisualBasic.ControlChars.NewLine); //apply bold tag BoldStartIndex = QuestionText.IndexOf(BoldStartTag); if (BoldStartIndex >= 0) { QuestionText = QuestionText.Replace(BoldStartTag, string.Empty); BoldEndIndex = QuestionText.IndexOf(BoldEndTag); QuestionText = QuestionText.Replace(BoldEndTag, string.Empty); } //apply italic tag ItalicStartIndex = QuestionText.IndexOf(ItalicStartTag); if (ItalicStartIndex >= 0) { QuestionText = QuestionText.Replace(ItalicStartTag, string.Empty); ItalicEndIndex = QuestionText.IndexOf(ItalicEndTag); QuestionText = QuestionText.Replace(ItalicEndTag, string.Empty); } //set question text QuestionText.Replace(ItalicStartTag, string.Empty); excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, QuestionText); //set bold if (BoldStartIndex >= 0 & BoldEndIndex >= 0) { excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, BoldStartIndex, BoldEndIndex-BoldStartIndex).Font.Bold = true; } //set italic if (ItalicStartIndex >= 0 & ItalicEndIndex >= 0) { excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, ItalicStartIndex, ItalicEndIndex-ItalicStartIndex).Font.Italic= true; } //set layout of question text cell and question no cell //excelFile.MergeCells(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, RowIndex, Constants.QuestionTextColumnIndex + 6); //excelFile.SetHorizontalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.HAlign.Justify); //excelFile.SetVerticalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.VAlign.Justify); //excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex).Bold = true; excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex).Bold = true; excelFile.AutoFitColumn(Constants.SheetIndex, Constants.QuestionNoColumnIndex); this.WriteDataValue(excelFile, xmlQuestion,ref DataValueRowIndex); RowIndex =DataValueRowIndex+ 2; }
/// <summary> /// Feed data into indicator Classification Sheet /// </summary> /// <param name="eICType">Enum of ICType</param> /// <param name="excelFile">Excel File</param> /// <param name="sheetNo">Sheet No</param> internal void GenerateICSheet(ref DIExcel excelFile, int sheetNo, ICType eicType) { // -- Get Temp Table For ICs. DataRow[] objRows = null; DataRow NewRow = null; Int32 MaxLevel = 0; string[] Temp = null; int Counter = 0; int TotCols = 0; DataView TempTableView = null; DataView TempDV = null; // -- Get Temporary Table for Classification DataTable Table = this.GetTempTable(); try { // -- Get Indicator Classification Value TempTableView = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DatabaseSummaryReportGenerator.DBQueries.IndicatorClassification.GetIC(FilterFieldType.None,string.Empty,eicType,FieldSelection.Heavy)).DefaultView; TempTableView.Sort = IndicatorClassifications.ICParent_NId + " ASC, " + IndicatorClassifications.ICName + " ASC"; foreach (DataRowView RowView in TempTableView) { NewRow = Table.NewRow(); // -- Get Records for IC ParentNID objRows = Table.Select(Constants.ICTempTableColumns.ID + "=" + RowView[IndicatorClassifications.ICParent_NId].ToString()); NewRow[Constants.ICTempTableColumns.ID] = RowView[IndicatorClassifications.ICNId]; NewRow[Constants.ICTempTableColumns.PARENT_ID] = RowView[IndicatorClassifications.ICParent_NId]; NewRow[Constants.ICTempTableColumns.ACT_LABEL] = RowView[IndicatorClassifications.ICName].ToString(); //-- Set Label with Parent Level IF Parent Level Exist if (objRows.Length > 0) { string[] tempVal = new string[] { Constants.ICTempTableColumns.ICLEVEL_SEPERATOR }; Temp = objRows[0][Constants.ICTempTableColumns.LABEL].ToString().Split(tempVal, StringSplitOptions.RemoveEmptyEntries); if (MaxLevel < Temp.Length) { MaxLevel = Temp.Length; } NewRow[Constants.ICTempTableColumns.LABEL] = objRows[0][Constants.ICTempTableColumns.LABEL].ToString() + Constants.ICTempTableColumns.ICLEVEL_SEPERATOR + RowView[IndicatorClassifications.ICName].ToString(); } else { NewRow[Constants.ICTempTableColumns.LABEL] = RowView[IndicatorClassifications.ICName]; } Table.Rows.Add(NewRow); } Table.AcceptChanges(); //-- update table DataTable tempTbl = Table.Copy(); foreach (DataRow dr in Table.Rows) { foreach (DataRow drTmp in tempTbl.Rows) { // -- Set PArentID to "-99" IF HAve No Child if (drTmp[Constants.ICTempTableColumns.LABEL].ToString().StartsWith(dr[Constants.ICTempTableColumns.LABEL].ToString() + Constants.ICTempTableColumns.ICLEVEL_SEPERATOR)) { dr[Constants.ICTempTableColumns.PARENT_ID] = Constants.ICTempTableColumns.PARENTID_VALUE; // "-99"; Table.AcceptChanges(); break; } } } Table.DefaultView.Sort = Constants.ICTempTableColumns.LABEL; Temp = null; //-- Set Total Column Length. TotCols = MaxLevel + 1 + 3; int IndCol = MaxLevel + 1; int UnitCol = IndCol + 1; int SubCol = UnitCol + 1; // -- Fill IUS int jj; DataTable TempTable =this.GetCollectionTable(TotCols); int LastRow; for (Counter = 0; Counter <= Table.Rows.Count - 1; Counter++) { // -- if the Number of Elements in the DataRow is same as MaxLevel then get the IUS combination for this level string[] tempVal = new string[] { Constants.ICTempTableColumns.ICLEVEL_SEPERATOR }; Temp = Table.Rows[Counter][Constants.ICTempTableColumns.LABEL].ToString().Split(tempVal, StringSplitOptions.RemoveEmptyEntries); // If Parent_NId is not -99 then Get IUS Record for Current ICNId. if (Table.Rows[Counter][Constants.ICTempTableColumns.PARENT_ID].ToString() != Constants.ICTempTableColumns.PARENTID_VALUE) { // -- get the IUS for this DataRow try { TempDV = null; TempDV = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DatabaseSummaryReportGenerator.DBQueries.IUS.GetDistinctIUSByIC(eicType, Table.Rows[Counter][Constants.ICTempTableColumns.ID].ToString(), FieldSelection.Light)).DefaultView; // -- Sort By Indicator, Unit, Subgroup TempDV.Sort = Indicator.IndicatorName + " Asc," + Unit.UnitName + " Asc," + SubgroupVals.SubgroupVal + " Asc"; } catch (Exception) { // Interaction.MsgBox(ex.Message); } // -- Set IUS for Each Level if ICNID have related IUS. if (TempDV.Count > 0) { foreach (DataRowView DVRow in TempDV) { DataRow Drow = TempTable.NewRow(); for (jj = 0; jj <= Temp.GetUpperBound(0); jj++) { Drow[jj] = Temp[jj]; } Drow[IndCol] = DVRow[Indicator.IndicatorName].ToString(); Drow[UnitCol] = DVRow[Unit.UnitName].ToString(); Drow[SubCol] = DVRow[SubgroupVals.SubgroupVal].ToString(); TempTable.Rows.Add(Drow); } } else { DataRow Row = TempTable.NewRow(); for (jj = 0; jj <= Temp.GetUpperBound(0); jj++) { Row[jj] = Temp[jj]; } Row[jj] = ""; Row[jj + 1] = ""; Row[jj + 2] = ""; TempTable.Rows.Add(Row); } TempTable.AcceptChanges(); } } // -- Create DataArray object[,] DataArray = new object[TempTable.Rows.Count + 1, TotCols + 1]; try { // Startng Column for Serial Number // -- header DataArray[0, 0] = DILanguage.GetLanguageString("SERIAL_NUMBER"); for (jj = 0; jj <= MaxLevel; jj++) { DataArray[0, jj + 1] = base.ColumnHeader[DSRColumnsHeader.LEVEL] + " " + (jj + 1); } DataArray[0, jj + 1] = base.ColumnHeader[DSRColumnsHeader.INDICATOR]; DataArray[0, jj + 2] = base.ColumnHeader[DSRColumnsHeader.UNIT]; DataArray[0, jj + 3] = base.ColumnHeader[DSRColumnsHeader.SUBGROUP]; // -- body for (Counter = 1; Counter <= TempTable.Rows.Count; Counter++) { // -- Exit to write data if Records exceed from MAximum defined Rows. if (Counter > RangeCheckReport.RangeCheckCustomizationInfo.MAX_EXCEL_ROWS) { break; } DataArray[Counter, 0] = Counter; for (jj = 0; jj <= TotCols - 1; jj++) { DataArray[Counter, jj + 1] = TempTable.Rows[Counter - 1][jj]; //[jj]; } } } catch (Exception) { } // -- print the DataRaay into the Excel Sheet excelFile.SetArrayValuesIntoSheet(sheetNo, Constants.Sheet.IC.ICDetailsRowIndex, Constants.Sheet.IC.ICHeaderColIndex, Constants.Sheet.IC.ICDetailsRowIndex + Counter + 1, TotCols + 1, DataArray); // excelFile.Range(excelFile.Cells(4, 1), excelFile.Cells(4 + Counter - 1, TotCols + 1)).Value = DataArray; LastRow = Constants.Sheet.IC.ICDetailsRowIndex + Counter; // -- Apply Font Settings base.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IC.ICDetailsRowIndex, Constants.Sheet.IC.ICHeaderColIndex, LastRow, TotCols, true); string sHeadVal = string.Empty; // -- header switch (eicType) { case ICType.Sector: sHeadVal = base.ColumnHeader[DSRColumnsHeader.SECTOR]; break; case ICType.Goal: sHeadVal = base.ColumnHeader[DSRColumnsHeader.GOAL]; break; case ICType.CF: sHeadVal = base.ColumnHeader[DSRColumnsHeader.CF]; break; case ICType.Institution: sHeadVal = base.ColumnHeader[DSRColumnsHeader.INSTITUTION]; break; case ICType.Theme: sHeadVal = base.ColumnHeader[DSRColumnsHeader.THEME]; break; case ICType.Convention: sHeadVal = base.ColumnHeader[DSRColumnsHeader.CONVENTION]; break; case ICType.Source: sHeadVal = base.ColumnHeader[DSRColumnsHeader.SOURCE]; break; } // -- Set Header Value excelFile.SetCellValue(sheetNo, Constants.Sheet.IC.ICHeaderRowIndex, Constants.Sheet.IC.ICHeaderColIndex, sHeadVal); excelFile.GetCellFont(sheetNo, Constants.Sheet.IC.ICHeaderRowIndex, Constants.Sheet.IC.ICHeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; // -- Show Missing Information Detail also TempTable = this.GetCollectionTable(1); // new Collection(); // -- Current IC Type is Source then Add Missing Information if (eicType == ICType.Source) { // -- Show Missing Information Detail also TempDV = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DatabaseSummaryReportGenerator.DBQueries.Source.GetMissingInfoSources(DatabaseSummaryReportGenerator.DBConnection.ConnectionStringParameters.ServerType)).DefaultView; // -- Sort by Name TempDV.Sort = IndicatorClassifications.ICName + " Asc"; TotCols = 1; if (TempDV.Count > 0) { foreach (DataRowView DVRow in TempDV) { // -- Data Array DataRow row = TempTable.NewRow(); row[0] = DVRow[IndicatorClassifications.ICName].ToString(); TempTable.Rows.Add(row); } DataArray = new object[TempTable.Rows.Count + 2, TotCols + 1]; // Startng Column for Serial Number DataArray[0, 0] = base.ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]; DataArray[1, 0] = DILanguage.GetLanguageString("SERIAL_NUMBER"); DataArray[1, 1] = base.ColumnHeader[DSRColumnsHeader.SOURCE]; // -- body for (Counter = 1; Counter <= TempTable.Rows.Count; Counter++) { DataArray[Counter + 1, 0] = Counter; for (jj = 0; jj <= TotCols - 1; jj++) { DataArray[Counter + 1, 1] = TempTable.Rows[Counter - 1][0]; } } LastRow += 1; // -- print the DataRaay into the Excel Sheet excelFile.SetArrayValuesIntoSheet(sheetNo, LastRow, Constants.Sheet.IC.ICHeaderColIndex, LastRow + Counter + 1, TotCols + 1, DataArray); // -- Apply Font Settings base.ApplyFontSettings(ref excelFile, sheetNo, LastRow + 1, Constants.Sheet.IC.ICHeaderColIndex, LastRow + Counter + 1, TotCols, true); base.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IC.ICDetailsRowIndex, Constants.Sheet.IC.ICHeaderColIndex, LastRow + Counter + 1, TotCols, true); // -- Set Missing Info Font and size excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize; } } // -- Set Column Widths for (jj = 0; jj <= MaxLevel; jj++) { excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.ICLavelColWidth, Constants.Sheet.IC.ICDetailsRowIndex, jj+1, LastRow, jj+1); } // -- Set indicator Column Width 40. excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.ICNameColWidth, Constants.Sheet.IC.ICDetailsRowIndex, jj +1 , LastRow, jj +1); // -- Set Auto col width for Unit and Subpop excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IC.ICDetailsRowIndex, jj + 2, Constants.Sheet.IC.ICDetailsRowIndex + Counter, jj + 3); // -- Set S No Column Width excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.SNoColumnWidth, Constants.Sheet.IC.ICDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.HeaderColIndex ); } catch (Exception) {} finally { //-- Close Data REader if ((TempTableView != null)) TempTableView.Dispose(); // -- Dispose DataView if ((TempDV != null)) TempDV.Dispose(); } }
/// <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 Column Width and Alignment /// </summary> /// <param name="RangeCheckSheet"></param> /// <param name="SheetNo"></param> private void SetColumnWidth(ref DIExcel RangeCheckSheet, int SheetNo) { //-- Set Column Width RangeCheckSheet.SetColumnWidth(SheetNo, RangeCheckCustomizationInfo.FirstColumnWidth, RangeCheckFileRowsInfo.SheetStartRowIndex, 0, RangeCheckCustomizationInfo.MAX_EXCEL_ROWS, RangeCheckFileRowsInfo.DateTimeDataColIndex); RangeCheckSheet.SetColumnWidth(SheetNo, RangeCheckCustomizationInfo.LastColumnWidth, RangeCheckFileRowsInfo.SheetStartRowIndex, RangeCheckFileRowsInfo.ColWidithStartIndex, RangeCheckCustomizationInfo.MAX_EXCEL_ROWS, RangeCheckFileRowsInfo.ColWidthLastIndex); RangeCheckSheet.SetHorizontalAlignment(SheetNo, RangeCheckFileRowsInfo.SheetStartRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex, RangeCheckCustomizationInfo.MAX_EXCEL_ROWS, RangeCheckFileRowsInfo.ColWidthLastIndex, SpreadsheetGear.HAlign.Left); }
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); } }