/// <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 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 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 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 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 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 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> /// 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); }
/// <summary> /// Initialize WorkSheet with Heading and Columns names and set font properties. /// </summary> /// <param name="RangeCheckSheet"></param> /// <param name="SheetNo"></param> private void SetWorkbookInitialValue(ref DIExcel RangeCheckSheet, int SheetNo) { //-- Set Report Heading RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SourceHeaderColIndex, this.ColumnsHeader [DRCColumnsHeader.DataRangeCheckReport]); //-- Set Font and Size RangeCheckSheet.GetCellFont(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex).Bold = true; RangeCheckSheet.GetCellFont(SheetNo, RangeCheckFileRowsInfo.SheetHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex).Size = RangeCheckCustomizationInfo.HeaderFontSize; //-- Set Font Bold RangeCheckSheet.GetRangeFont(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SheetHeaderColIndex, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.SourceDataColIndex).Bold = true; //-- Display the database file name RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SourceHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Source ]); RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.SourceHeaderRowIndex, RangeCheckFileRowsInfo.SourceDataColIndex, this._SourceDatabaseNameWPath ); //-- Set Date and Time Value RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.DateTimeHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.DateandTime]); string CurDateTime; CurDateTime = DateTime.Now.ToString(); RangeCheckSheet.SetCellValue(SheetNo, RangeCheckFileRowsInfo.DateTimeHeaderRowIndex, RangeCheckFileRowsInfo.DateTimeDataColIndex, CurDateTime); }
/// <summary> /// 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(); } }
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; }
private void WriteHeader(DIExcel excelFile, int rowIndex, string headerText) { excelFile.SetCellValue(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex, headerText); excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Bold = true; excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Size = 12; }
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 Summary Sheet For Summary Report /// </summary> /// <param name="excelFile"></param> /// <param name="sheetNo"></param> private void GenerateForSummaryReport(ref DIExcel excelFile,int sheetNo) { try { // -- database name excelFile.SetCellValue(sheetNo, Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseNameColIndex, base.ColumnHeader[DSRColumnsHeader.DATABASENAME]); excelFile.SetCellValue(sheetNo, Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseValueColIndex, DatabaseSummaryReportGenerator.DBConnection.ConnectionStringParameters.DbName ); // -- Feed Data into Summary Report sheet this.FeedSummaryReportValues(ref excelFile, sheetNo, 0); //-- Set Header Name excelFile.SetCellValue(sheetNo, Constants.Sheet.SummaryReport.ICHeaderCellIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex, base.ColumnHeader[DSRColumnsHeader.SUMMARY_REPORT] + ":" + Convert.ToString(this.CurrentSheetType==SummarySheetType.Basic? DILanguage.GetLanguageString("BASIC") : DILanguage.GetLanguageString("DETAILED"))); // -- Heaser Set Font Settings excelFile.GetCellFont(sheetNo, Constants.Sheet.SummaryReport.ICHeaderCellIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex).Size = Constants.SheetsLayout.HeaderFontSize; } catch //(Exception ex) { //-- Throw ex; } }
/// <summary> /// Set Language Value into Sheet /// </summary> /// <param name="ExcelSheet">worksheet in use</param> /// <param name="SheetNo">Sheet number</param> /// <param name="RowIndex">Row Index</param> /// <param name="ColHeaderIndex">Header Column Index</param> /// <param name="ColValueIndex">Column Value Index</param> /// <param name="HeaderType">Enum for ColumnHeader</param> /// <param name="TableName">Table Name</param> private void SetLanguageValues(ref DIExcel excelFile, int sheetNo,int rowIndex, int colHeaderIndex, int colValueIndex,DSRColumnsHeader headerType,string tableName) { int Counter=0; //-- Set Record First Value excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]); DataView ReportTable = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DIQueries.GetLangauges(DatabaseSummaryReportGenerator.DBQueries.DataPrefix)).DefaultView; for ( Counter = 0; Counter <= ReportTable.Count - 1; Counter++) { // -- Make default language font Bold if (Convert.ToBoolean(ReportTable[Counter][Language.LanguageDefault])) { excelFile.GetCellFont(sheetNo, rowIndex, colValueIndex).Bold = true; } excelFile.SetCellValue(sheetNo, rowIndex + Counter,colValueIndex,ReportTable[Counter][Language.LanguageName]); } // -- language count excelFile.SetCellValue(sheetNo, rowIndex, Constants.Sheet.SummaryReport.LanguageColCountValueIndex, ReportTable.Count); }