/// <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>
/// 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>
/// 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 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 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 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 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 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 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 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>
/// Set Summary Report Count Value Into "Summary Report" WorkSheet
/// </summary>
/// <param name="ExcelSheet"></param>
/// <param name="SheetNo"></param>
/// <param name="RowIndex"></param>
/// <param name="ColHeaderIndex"></param>
/// <param name="ColValueIndex"></param>
/// <param name="HeaderType"></param>
/// <param name="TableName"></param>
private void SetReportsHeaderValues(ref DIExcel excelFile,int sheetNo,int rowIndex, int colHeaderIndex, int colValueIndex,DSRColumnsHeader headerType,string tableName,int startRowPosition)
{
string SqlQuery;
//-- Set Record Header Value
excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]);
try
{
switch (headerType )
{
case DSRColumnsHeader.IUS :
SqlQuery = DatabaseSummaryReportGenerator.DBQueries.IUS.GetIUS(FilterFieldType.None,string.Empty,FieldSelection.NId);
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(SqlQuery).Rows.Count));
break;
case DSRColumnsHeader.LEVEL:
SqlQuery = DatabaseSummaryReportGenerator.DBQueries.Area.GetMaxAreaLevelFrmAreaTable();
int MaxVal = (int)DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery);
int Counter;
if (this.CurrentSheetType == SummarySheetType.Detailed)
{
//-- Set "Count OF DATAVALUE" Cell Value
excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex, base.ColumnHeader[DSRColumnsHeader.COUNT] + " " + base.ColumnHeader[DSRColumnsHeader.OF] + " " + base.ColumnHeader[DSRColumnsHeader.DATAVALUE]);
//-- Set "SUM OF DATAVALUE" Cell Value
excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex, base.ColumnHeader[DSRColumnsHeader.SUM] + " " + base.ColumnHeader[DSRColumnsHeader.OF] + " " + base.ColumnHeader[DSRColumnsHeader.DATAVALUE]);
try
{
excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colHeaderIndex).Bold = true;
excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colHeaderIndex).Bold = true;
}
catch {}
}
for (Counter = 1; Counter <= MaxVal; Counter++)
{
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex + Counter, Counter);
SqlQuery = DIQueries.GetTableRecordsCount(tableName, Convert.ToString(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Area.AreaLevel + "=" + Counter));
excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelRowValueIndex, colValueIndex + Counter, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery)));
if (this.CurrentSheetType == SummarySheetType.Detailed)
{
//-- Set Count OF Data VAlue
excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelCountDataRowIndex, colValueIndex + Counter, this.GetCountOFDataBYAreaLevel(Counter));
excelFile.SetCellValue(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaLevelSumDataRowIndex, colValueIndex + Counter, this.GetSumOFDataBYAreaLevel(Counter));
}
}
break;
case DSRColumnsHeader.MIN : //-- Set Value for Minimum Timeperiod.
SqlQuery = DIQueries.GetMinValue(tableName,Convert.ToString( DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Timeperiods.TimePeriod),string.Empty);
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery)));
break;
case DSRColumnsHeader.MAX : //-- Set Value for Minimum Timeperiod.
SqlQuery = DIQueries.GetMaxValue(tableName, DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Timeperiods.TimePeriod,string.Empty);
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery)));
break;
default ://-- Default Process
SqlQuery = DIQueries.GetTableRecordsCount(tableName,string.Empty);
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery)));
break;
}
}
catch //(Exception ex)
{
// throw ex;
}
}
/// <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;
}
}
}
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;
}
/// <summary>
/// Generate Summary Sheet For Comparison Report
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
private void GenerateForComparisonReport(ref DIExcel excelFile, int sheetNo)
{
try
{
//-- Set Header Name
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("COMPARISONREPORT"));
// --Set Reference Database name
DatabaseSummaryReportGenerator.SourceDatabaseNameWPath = this.RefDBConnection.ConnectionStringParameters.DbName;
// -- Set Header Name and Value
excelFile.SetCellValue(sheetNo, Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseNameColIndex, (base.ColumnHeader[DSRColumnsHeader.REFERENCE] + " " + base.ColumnHeader[DSRColumnsHeader.DATABASENAME]));
excelFile.SetCellValue(sheetNo, Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseValueColIndex, this.ReferenceDataBaseFile);
// -- Feed Data into Summary Report sheet
this.FeedSummaryReportValues(ref excelFile, sheetNo, 0);
// -- Set TArget Database Summary
DatabaseSummaryReportGenerator.SourceDatabaseNameWPath = this.TargetDBConnection.ConnectionStringParameters.DbName;
// -- Set Header Name and Value
excelFile.SetCellValue(sheetNo,this.SheetLastRowIndex + Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseNameColIndex,( base.ColumnHeader[DSRColumnsHeader.TARGET] + " " + base.ColumnHeader[DSRColumnsHeader.DATABASENAME]));
excelFile.SetCellValue(sheetNo, this.SheetLastRowIndex + Constants.Sheet.SummaryReport.DataBaseNameRowIndex, Constants.Sheet.SummaryReport.DataBaseValueColIndex, DatabaseSummaryReportGenerator.SourceDatabaseNameWPath );
// -- Feed Data into Summary Report sheet
this.FeedSummaryReportValues(ref excelFile, sheetNo, this.SheetLastRowIndex);
}
catch //(Exception ex)
{
//-- Throw ex;
}
}
/// <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 Language and Its Value
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
private void SetSheetLanguageValue(ref DIExcel excelFile, int sheetNo)
{
excelFile.SetCellValue(sheetNo, Constants.LanguageRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.LANGUAGE));
excelFile.SetCellValue(sheetNo, Constants.LanguageRowIndex, Constants.HeaderColValueIndex, this.LanguageName);
}
/// <summary>
/// Set Heading Name of Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="sheetNo">Sheet Index</param>
private void SetSheetHeading(ref DIExcel excelFile, int sheetNo, string sheetName)
{
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, sheetName);
excelFile.GetRangeFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.MissingTextRowIndex, Constants.HeaderColIndex).Bold = true;
}
/// <summary>
/// Set Missing Text and Fonts
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
private void SetMissingText(ref DIExcel excelFile, int sheetNo)
{
excelFile.SetCellValue(sheetNo, Constants.MissingTextRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.MISSING) + " : " + DBNameForMissingRecords);
// -- Set Headers Font Bold.
excelFile.GetRangeFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.MissingTextRowIndex, Constants.HeaderColIndex).Bold = true;
}
/// <summary>
/// Generates the .XLS presentation file in specified folder on the basis of current Map object settings.
/// </summary>
/// <param name="presentationOutputFolderPath">Folder Path where .xls presentation is created.</param>
/// <param name="maxHeightInPixelToFitAll">(optional) maximun width (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <param name="maxWidthInPixelToFitAll">(optional) maximun height (in pixel) for presentation composite content to fit in a single page. (-1 default)</param>
/// <returns>Returns the full file path of .xls presentation generated.</returns>
public string GeneratePresentation(string presentationOutputFolderPath, int maxWidthInPixelToFitAll, int maxHeightInPixelToFitAll)
{
string RetVal = string.Empty;
if (!(string.IsNullOrEmpty(presentationOutputFolderPath)))
{
float MapOriginalWidth = this.m_Width;
float MapOriginalHeight = this.m_Height;
// Getting the xls sheet names in desired language.
string MapSheetName = DILanguage.GetLanguageString("MAP");
string MapDataSheetName = DILanguage.GetLanguageString("DATA");
string SourceSheetName = DILanguage.GetLanguageString("SOURCECOMMON");
// Set path for presentation and png images
string FileSuffix = DateTime.Now.Ticks.ToString();
// Set Image file extension(.png or .emf) on he basis of UserPreference property.
string ImageExtention = string.Empty;
if (this.UserPreference.General.ShowExcel)
{
//If ShowExcel is True, then User can see presentation in MS Excel object
ImageExtention = "emf";
}
else
{
// Png format is supported in SpreadsheetGear (.Emf NOT supported)
//If ShowExcel is False, then presentation will be opened in Spreadsheet Gear, so Use Png format.
ImageExtention = "png";
}
string PresentationPath = presentationOutputFolderPath + @"\Map" + FileSuffix + ".xls";
string TitleImgPath = Path.Combine(presentationOutputFolderPath, "Title" + FileSuffix + "." + ImageExtention); //"Title"
string MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + "." + ImageExtention);
string DisclaimerImgPath = Path.Combine(presentationOutputFolderPath, "Disclaimer" + FileSuffix + "." + ImageExtention);
string InsetFilePrefix = string.Empty;
DIExcel ExcelApp = new DIExcel();
Image img;
//- Calculate pixelToPoint factor as SpreadsheetGear uses point unit instead of Pixel unit for measuring Length and width
Graphics graphics = Graphics.FromImage(new System.Drawing.Bitmap(1, 1));
double PixcelToPointFactor = 72 / graphics.DpiX; //1 inch = 72 points
Size TitleSize = new Size();
Size LegendSize = new Size();
int MaxLegendHeight = 20;
int MaxLegendWidth = 30;
int RowIndex = 0;
int SheetIndex;
double CellHeight = ExcelApp.GetCellHeight(0, 0, 0); // default cell height from default sheet.
double CellWidth = ExcelApp.GetColumnWidth(0, 0, 0, 0, 1); // Default cell width from default sheet.
int SingleCellWidthInPixel = (int)(ExcelApp.GetWorksheet(0).Cells[0, 0].Width / PixcelToPointFactor);
if (maxWidthInPixelToFitAll > 0 && maxHeightInPixelToFitAll > 0)
{
//- Offset page Margins (default left margin -> 0.7" & right margin -> 1.4") and (default Top & bottom margin -> 0.75")
maxWidthInPixelToFitAll = maxWidthInPixelToFitAll - (int)(2.1 * graphics.DpiX);
maxHeightInPixelToFitAll = maxHeightInPixelToFitAll - (int)(1.5 * graphics.DpiY);
//- Calculating maximum Height & width of presentation's composite contents in Points
//maxWidthInPixelToFitAll = (int)(maxWidthInPixelToFitAll * PixcelToPointFactor);
//maxHeightInPixelToFitAll = (int)(maxHeightInPixelToFitAll * PixcelToPointFactor);
// Offset margin of first column width.
maxWidthInPixelToFitAll -= SingleCellWidthInPixel;
//- Set final Map width so that Map can be accomodated within maximum width specified.
this.m_Width = (float)maxWidthInPixelToFitAll - 2;
//- offset width of Inset images (if present)/
//- generally inset image is 15% of total Map and starts after a gap of 1/2 column width.
if (this.Insets.Count > 0)
{
float InsetImageWidth = MapOriginalWidth; //- Default
if (this.Insets[0].InsetImage != null)
{
InsetImageWidth = (float)(this.Insets[0].InsetImage.Width);
}
this.m_Width -= (InsetImageWidth * 0.1F) + (float)(SingleCellWidthInPixel);
}
//- Adjusting Map height in proportion with CurrentExtent's Width to height ratio.
this.m_Height = this.m_Width * Math.Min(4 / 3, (this.m_CurrentExtent.Height / this.m_CurrentExtent.Width)) + 3;
}
try
{
if (File.Exists(PresentationPath))
{
File.Delete(PresentationPath);
}
ExcelApp.SaveAs(PresentationPath);
ExcelApp.RenameWorkSheet(0, MapSheetName); //language handling Rename first(default) sheet
ExcelApp.Save();
//********************************* WorkSheet 1 - Map Images**************************************************
this.ClearSelection(); //-- Clear any Layer selection (if any)
//Generate the first sheet which is the Map Image for MRD data.
//*** generate title image in temp folder
TitleSize = this.GetTitleImage(Path.GetDirectoryName(TitleImgPath), Path.GetFileNameWithoutExtension(TitleImgPath), ImageExtention);
//*** Generate Themes Legend Image in temp folder
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
//Generate the Legend image, and calculating image maximum height & width.
LegendSize = _ThemeTemp.GetLegendImage(presentationOutputFolderPath, _ThemeTemp.ID + FileSuffix, ImageExtention, this.PointShapeIncluded, this.TemplateStyle.Legends.ShowCaption, this.TemplateStyle.Legends.ShowRange, this.TemplateStyle.Legends.ShowCount, this.TemplateStyle.Legends.ShowMissingLegend);
MaxLegendWidth = Math.Max(MaxLegendWidth, (int)(LegendSize.Width * PixcelToPointFactor));
MaxLegendHeight = Math.Max(MaxLegendHeight, (int)(LegendSize.Height * PixcelToPointFactor));
}
}
//*** generate discalimer (common) image in temp folder
this.GetDisclaimerImage(Path.GetDirectoryName(DisclaimerImgPath), Path.GetFileNameWithoutExtension(DisclaimerImgPath), ImageExtention, maxWidthInPixelToFitAll);
int ImageRowPosition = 0; //Holds the row position for Image to be pasted.
//---Insert Sheet for Map of most recent data.
SheetIndex = ExcelApp.GetSheetIndex(MapSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
if (this._showTimePeriods || this._showAreaLevels)
{
//*** Set map rendering info for most recent time period for all level
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
else
{
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
}
ExcelApp.Save();
// Set Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
// Converting Image size from Pixel to Points while pasting.
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Set Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
// Set Insets Image
int InsetCtr;
CellWidth = 70; //TODO: Remove hardcoding calculate column width right.
int RowOffset = ImageRowPosition;
int ColOffset = (int)(this.Width / CellWidth) + 2;
InsetFilePrefix = "Inset" + FileSuffix;
for (InsetCtr = 0; InsetCtr <= this.Insets.Count - 1; InsetCtr++)
{
Inset TempInset = this.Insets[InsetCtr];
string InsetImgFilePath = Path.Combine(presentationOutputFolderPath, InsetFilePrefix + InsetCtr + "." + ImageExtention);
if (TempInset.InsetImage != null)
{
TempInset.InsetImage.Save(InsetImgFilePath);
}
else
{
//Extract new "png" image of inset in temp folder.
this.GetInsetImage(presentationOutputFolderPath, ImageExtention, (int)this.Width, (int)this.Height, InsetCtr, InsetFilePrefix);
}
if (TempInset.Visible)
{
//*** Extract InsetName png.
TempInset.GetInsetName(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix, ImageExtention);
img = Image.FromFile(Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention));
ExcelApp.PasteImage(SheetIndex, Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention), ColOffset, RowOffset, img.Width, img.Height);
//*** use previously generated inset images in temp folder
RowOffset += img.Height / (int)CellHeight;
img = Image.FromFile(InsetImgFilePath);
ExcelApp.PasteImage(SheetIndex, InsetImgFilePath, ColOffset, RowOffset, (img.Width * 0.09), (img.Height * 0.09));
RowOffset += (int)(img.Width * 0.09) / (int)CellHeight + 2;
img.Dispose();
}
}
//Set Theme Legend Images
ImageRowPosition += (int)((this.m_Height * PixcelToPointFactor) / (int)CellHeight) + 2; //Increment row position by rows covered by Image height.
int p = 1; //p is the column position of the Legend Images (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); //(double)LegendSize.Width, (double)LegendSize.Height);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
DataView _DVSources = this.GetUniqueSourceList(); //getting unique sources in DataView
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
//- Wrap Cell for source
int EndingColumnToMerge = maxWidthInPixelToFitAll / SingleCellWidthInPixel;
ExcelApp.MergeCells(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge);
ExcelApp.WrapText(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge, true);
}
// Set Disclaimer Image
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
ExcelApp.Save();
//--Now, generate sheets for each timePeriod selected.
if (this._showTimePeriods)
{
DataView DVTimePeriods = this.GetTimePeriodsForThemes();
for (byte i = 0; i <= DVTimePeriods.Count - 1; i++)
{
//Restrict Excel Worksheet name to 31 characters
string sheetName = DVTimePeriods[i][Timeperiods.TimePeriod].ToString();
if (sheetName.Length > 31)
{
sheetName.Substring(0, 31);
}
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(sheetName);
SheetIndex = ExcelApp.GetSheetIndex(sheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo((int)(DVTimePeriods[i][Timeperiods.TimePeriodNId]), -1, iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources");
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//*** Add Sources
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
}
else if (this.ShowAreaLevels) //If only AreaLevel is selected.
{
DataView DVAreaLevels = this.GetAreaLevels();
for (byte i = 0; i <= DVAreaLevels.Count - 1; i++)
{
//---Insert Sheet for Map of specified TimePeriod.
ExcelApp.InsertWorkSheet(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
SheetIndex = ExcelApp.GetSheetIndex(DVAreaLevels[i][Area_Level.AreaLevelName].ToString());
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.ShowWorkSheetGridLine(SheetIndex, false);
// Draw Map for specifoed TimePeriod.
MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention);
for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++)
{
this.UpdateRenderingInfo(-1, (int)(DVAreaLevels[i][Area_Level.AreaLevel]), iThemeIndex);
}
this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true);
// Insert Title Image
ImageRowPosition = 0;
if (this.Title != "")
{
img = Image.FromFile(TitleImgPath);
ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height.
}
// Insert Map Image
ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor);
//Set Theme Legend Images
ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height.
p = 1; //p is the column position of the Legend Image (x - axis).
foreach (Theme _ThemeTemp in this.Themes)
{
if (_ThemeTemp.Visible == true)
{
img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention);
ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor);
p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2
}
}
// Set Sources.
ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1;
RowIndex = ImageRowPosition + 1;
ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, SourceSheetName);
for (int ctr = 0; ctr < _DVSources.Count; ctr++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]);
}
// Insert Disclaimer Image.
ImageRowPosition += _DVSources.Count + 3;
img = Image.FromFile(DisclaimerImgPath);
ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor));
}
ExcelApp.Save();
}
//********************************* WorkSheet - Map Data*********************************************
//TODO Use TablePresentation object logic
ExcelApp.InsertWorkSheet(MapDataSheetName); //insert DataSheet.
SheetIndex = ExcelApp.GetSheetIndex(MapDataSheetName);
ExcelApp.ActivateSheet(SheetIndex);
DataTable _DTDataSheet = GetDataSheetView(this.PresentationData); //Getting Map Data or DataSheet.
string[,] arrDataView = new string[_DTDataSheet.Rows.Count, _DTDataSheet.Columns.Count - 1]; //Array Columns will be 1 less than dv, to ignore extra footNote column
// --Adding Column names in desired language , only one time, at row 1.
ExcelApp.SetCellValue(SheetIndex, 1, 0, DILanguage.GetLanguageString("TIMEPERIOD")); //Timeperiods.TimePeriod
ExcelApp.SetCellValue(SheetIndex, 1, 1, DILanguage.GetLanguageString("AREAID")); //Area.AreaID
ExcelApp.SetCellValue(SheetIndex, 1, 2, DILanguage.GetLanguageString("AREANAME")); //Area.AreaName
ExcelApp.SetCellValue(SheetIndex, 1, 3, DILanguage.GetLanguageString("INDICATOR")); //Indicator.IndicatorName
ExcelApp.SetCellValue(SheetIndex, 1, 4, DILanguage.GetLanguageString("DATA")); //Data.DataValue
ExcelApp.SetCellValue(SheetIndex, 1, 5, DILanguage.GetLanguageString("UNIT")); //Unit.UnitName
ExcelApp.SetCellValue(SheetIndex, 1, 6, DILanguage.GetLanguageString("SUBGROUP")); //SubgroupVals.SubgroupVal
ExcelApp.SetCellValue(SheetIndex, 1, 7, DILanguage.GetLanguageString("SOURCECOMMON")); //IndicatorClassifications.ICName
int col = 8; //Columnn number 8th
foreach (string MDColumn in this._MDIndicatorFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataIndicator.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDAreaFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataArea.Columns[MDColumn].Caption);
col++;
}
}
foreach (string MDColumn in this._MDSourceFields.Split(','))
{
if (MDColumn.Length > 0)
{
//Adding column name at "col" position.
ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataSource.Columns[MDColumn].Caption);
col++;
}
}
// ---Columns added...
RowIndex = 2; //Start displaying Map Data from Row - 2nd
foreach (DataRow DRowDTDataSheet in _DTDataSheet.Rows)
{
for (int Columns = 0; Columns < _DTDataSheet.Columns.Count; Columns++)
{
//Assign cellvalue with data in DataTable.
ExcelApp.SetCellValue(SheetIndex, RowIndex, Columns, DRowDTDataSheet[Columns].ToString());
if (_DTDataSheet.Columns[Columns].ColumnName == Data.DataValue)
{
//If Column id = "DataValue" then add footNotes As-Comment if present.
if (DRowDTDataSheet[FootNotes.FootNote].ToString() != "")
{
ExcelApp.AddComment(SheetIndex, RowIndex, Columns, DRowDTDataSheet[FootNotes.FootNote].ToString(), true);
}
}
}
RowIndex++;
}
ExcelApp.AutoFitColumns(SheetIndex, 1, 0, _DTDataSheet.Rows.Count - 1, _DTDataSheet.Columns.Count - 1); //Auto fitting columns width.
ExcelApp.Save();
//********************************* WorkSheet - Sources**************************************************
ExcelApp.InsertWorkSheet(SourceSheetName);
SheetIndex = ExcelApp.GetSheetIndex(SourceSheetName);
ExcelApp.ActivateSheet(SheetIndex);
ExcelApp.SetCellValue(SheetIndex, 0, 0, SourceSheetName); //Cell value = "Source" in desired language.
ExcelApp.SetCellValue(SheetIndex, 0, 2, DIConnection.ConnectionStringParameters.DbName);
for (byte i = 0; i <= _DVSources.Count - 1; i++)
{
//Adding sources from Dv into cell.
ExcelApp.SetCellValue(SheetIndex, i + 3, 0, _DVSources[i]["IC_Name"]);
ExcelApp.AutoFitColumn(SheetIndex, i);
}
ExcelApp.Save();
//********************************* WorkSheet - Keyword**************************************************
ExcelApp.InsertWorkSheet(Presentation.KEYWORD_WORKSHEET_NAME);
ExcelApp.ActivateSheet(ExcelApp.GetSheetIndex(MapSheetName)); //Set first sheet as ACTIVE sheet..
ExcelApp.Save();
RetVal = PresentationPath;
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
ExcelApp.Close();
if (graphics != null)
{
graphics.Dispose();
}
this.m_Width = MapOriginalWidth;
this.m_Height = MapOriginalHeight;
}
}
return RetVal;
}
/// <summary>
/// Generate Range Check Report
/// </summary>
/// <returns></returns>
public bool GenerateExcelReport()
{
bool RetVal = false;
DataTable ReportTable;
int SheetNo = 0;
int ProgressBarValue=0;
//-- Initialize Collection
this.InitColumnHeading();
//-- Check Selected File Selected
if (this.ReportDestinationFilePath.Length > 0)
{
DIExcel RangeCheckSheet = new DIExcel();
string NumDecSeparator = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
//-- Problem with Different Locale of Office and Regional(Setting)
System.Threading.Thread ThisThread = System.Threading.Thread.CurrentThread ;
System.Globalization.CultureInfo OriginalCulture = ThisThread.CurrentCulture ;
//-- Create Worksheet named "Range Check"
RangeCheckSheet.CreateWorksheet(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
SheetNo = RangeCheckSheet.GetSheetIndex(this.ColumnsHeader[DRCColumnsHeader.RangeCheck]);
this.SetWorkbookInitialValue( ref RangeCheckSheet, SheetNo);
ThisThread.CurrentCulture = OriginalCulture;
//-- Fill DataTable
ReportTable = this.DBConnection.ExecuteDataTable(DBQueries.Data.GetValuesRangeCheck());
// TempTable.Merge(ReportTable,false);
int maxValue = ReportTable.Rows.Count;
try
{
ThisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//-- Proceed If datatable has Records
if (ReportTable.Rows.Count > 0)
{
// Initialize progress bar
this.RaiseProgressBarInitialize(maxValue + 1);
int RowNum = RangeCheckFileRowsInfo.DataStartingRowIndex;
int LastIUSNID = 0;
//-- Fill Record Indicator wise
foreach (DataRow rowval in ReportTable.Rows)
{
// -- Avoid Process if IUSNID is Last IUSNID
if (Convert.ToInt32(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]) != LastIUSNID)
{
LastIUSNID = Convert.ToInt32(rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId]);
//-- Set Data Column Header Bold
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum + RangeCheckFileRowsInfo.HeaderRowCount, RangeCheckFileRowsInfo.SheetHeaderColIndex).Bold = true;
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Indicator]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[ DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator.IndicatorName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Unit]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString( rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitName] ));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Subgroup]);
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, Convert.ToString(rowval[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal]));
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Min]);
// -- Get Minimum Value
double MinValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MinValue])? Convert.ToDouble("0.0") :Convert.ToDouble( rowval[Indicator_Unit_Subgroup.MinValue])) ;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MinValue);
RowNum += 1;
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, this.ColumnsHeader[DRCColumnsHeader.Max]);
double MaxValue = (Information.IsDBNull(rowval[Indicator_Unit_Subgroup.MaxValue]) ? Convert.ToDouble("0.0") : Convert.ToDouble(rowval[Indicator_Unit_Subgroup.MaxValue]));
RangeCheckSheet.SetCellValue(SheetNo, RowNum, RangeCheckFileRowsInfo.DetailDataColIndex, MaxValue);
RowNum += 1;
DataTable Table=null;
DataTable OtherTable=null;
// -- insert timeperiod, areaid,area name,datavalue,source
#region "-- Change No: c1 --"
if (MaxValue > MinValue)
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID,RangeType.DataBelow_MinVal ).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MaxVal).Select());
}
else if (MaxValue == 0.0 && MinValue == 0.0) { }
else
{
Table = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataAbove_MinVal).Select());
OtherTable = this.RangeCheckDetails(this.GetValidatedRangeDetails(ReportTable, LastIUSNID, RangeType.DataBelow_MaxVal).Select());
}
#endregion
////if (MaxValue > MinValue)
////{
//// Table = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID )); //+ " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MinValue ));
//// OtherTable = RangeCheckDetails(ReportTable.Select(Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID ));//+ " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MaxValue));
////}
////else if (MaxValue == 0.0 && MinValue == 0.0) { }
////else
////{
//// Table = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " > " + Indicator_Unit_Subgroup.MinValue));
//// OtherTable = RangeCheckDetails(ReportTable.Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Indicator_Unit_Subgroup.IUSNId + " = " + LastIUSNID + " AND " + Data.DataValue + " < " + Indicator_Unit_Subgroup.MaxValue));
////}
if (Table != null)
{
// -- Merge Both Table
Table.Merge(OtherTable, true);
//-- Check if Records Exist in DataTable Then Export to Excel Workbook
if (Table.Rows.Count > 0)
{
//-- Set Data Column Header Font
RangeCheckSheet.GetRangeFont(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex).Italic = true;
//-- Set Column Backgroud Color TO Grey
RangeCheckSheet.SetRangeColor(SheetNo, RowNum, RangeCheckFileRowsInfo.SheetHeaderColIndex, RowNum, RangeCheckFileRowsInfo.ColWidthLastIndex, Color.Black, System.Drawing.Color.FromArgb(Color.Gray.R, Color.Gray.G, Color.Gray.B));
//-- Raname DataTable Column Name As Per Excel Sheet
this.RenameColumn(ref Table);
//-- Load DataTAble Into Excel
RangeCheckSheet.LoadDataTableIntoSheet(RowNum, RangeCheckFileRowsInfo.DetailHeaderColIndex, Table, SheetNo, false);
}
RowNum += Table.Rows.Count + 1;
}
this.RaiseProgressBarIncrement(ProgressBarValue); //raise Progressbar_Increment event
} //End Of Next
//-- Increase ProgressBar Value
ProgressBarValue++;
//-- IF numbers of record in dataview is morethan the max rows i.e 50,000 available in excel
if (RowNum > RangeCheckCustomizationInfo.MAX_EXCEL_ROWS )
{
break;
}
}
//-- Set WorkSheet Border Line width
this.SetColumnWidth(ref RangeCheckSheet, SheetNo);
this.SetSheetBorder(ref RangeCheckSheet, SheetNo);
} //End OF IF
//-- Make Progressbar Value to Maximum
this.RaiseProgressBarIncrement(maxValue );
try
{
// -- Save the Workbook If file exist then delete and then Save
if (System.IO.File.Exists(this.ReportDestinationFilePath))
{
System.IO.File.SetAttributes(this.ReportDestinationFilePath, FileAttributes.Normal);
System.IO.File.Delete(this.ReportDestinationFilePath);
}
RangeCheckSheet.SaveAs(this.ReportDestinationFilePath);
this.RaiseProgressBarClose();
RetVal = true;
}
catch
{
RetVal = false;
}
}
catch (Exception ex)
{
this.RaiseProgressBarClose();
throw new ApplicationException(ex.Message);
}
}//End OF IF
return RetVal;
}
/// <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>
/// Set IC Level
/// </summary>
/// <param name="eicType">Enum ICTYpe</param>
private void SetICLevel(ref DIExcel excelSheet, int sheetNo, ICType eicType, int rowIndex, int colValueIndex)
{
DataRow[] objRows = null;
DataRow NewRow = null;
string[] Temp = null;
Int32 MaxLevel = 0;
DataTable Table = base.GetTempTable();
// -- Get Indicator Classification Value
DataView TempTable = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DatabaseSummaryReportGenerator.DBQueries.IndicatorClassification.GetIC(FilterFieldType.None, string.Empty, eicType, FieldSelection.Light)).DefaultView;
TempTable.Sort = IndicatorClassifications.ICParent_NId + " ASC, " + IndicatorClassifications.ICName;
DataTable dt = new DataTable();
foreach (DataRowView ROW in TempTable)
{
NewRow = Table.NewRow();
// -- Get Records for IC ParentNID
objRows = Table.Select(Constants.ICTempTableColumns.ID + "=" + ROW[IndicatorClassifications.ICParent_NId].ToString());
NewRow[Constants.ICTempTableColumns.ID] = ROW[IndicatorClassifications.ICNId];
NewRow[Constants.ICTempTableColumns.PARENT_ID] = ROW[IndicatorClassifications.ICParent_NId];
NewRow[Constants.ICTempTableColumns.ACT_LABEL] = ROW[IndicatorClassifications.ICName].ToString();
// -- Get Level If Any NidID have Parent_NID
if (objRows.Length > 0)
{
string[] tempVal = new string[] { Constants.ICTempTableColumns.ICLEVEL_SEPERATOR };
Temp = objRows[0][Constants.ICTempTableColumns.LABEL].ToString().Split(tempVal, StringSplitOptions.RemoveEmptyEntries);
// -- Set Maximum level if array lengh is greater than MaxLength.
if (MaxLevel < Temp.Length)
{ MaxLevel = Temp.Length; }
NewRow[Constants.ICTempTableColumns.LABEL] = objRows[0][Constants.ICTempTableColumns.LABEL].ToString() + Constants.ICTempTableColumns.ICLEVEL_SEPERATOR + ROW[IndicatorClassifications.ICName].ToString();
}
else
{ NewRow[Constants.ICTempTableColumns.LABEL] = ROW[IndicatorClassifications.ICName]; }
Table.Rows.Add(NewRow);
}
Table.AcceptChanges();
int CountICByLevel = 0;
// -- Set Level IF Table has records
if (Table.Rows.Count > 0)
{
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, base.ColumnHeader[DSRColumnsHeader.LEVEL]);
excelSheet.GetRangeFont(sheetNo, rowIndex, colValueIndex, rowIndex, colValueIndex).Bold = true;
for (int i = 0; i <= MaxLevel; i++)
{
// -- Increase Column Index
colValueIndex += 1;
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, (i + 1));
// -- Increase Row Index for Value
rowIndex += 1;
CountICByLevel = this.GetCountICLavel(Table, i);
// -- Set Lavel Value
excelSheet.SetCellValue(sheetNo, rowIndex, colValueIndex, CountICByLevel);
// -- Decrease rowindex for next Lavel.
rowIndex -= 1;
}
}
}
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>
/// 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);
}
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>
/// Set Indicator Classification Value For Summary Report
/// </summary>
/// <param name="excelFile"></param>
/// <param name="sheetNo"></param>
/// <param name="rowIndex"></param>
/// <param name="colHeaderIndex"></param>
/// <param name="colValueIndex"></param>
/// <param name="headerType">Enum for Summary Report Column Header</param>
/// <param name="tableName"></param>
/// <param name="icType">Enum Classification Type(ICType)</param>
private void SetReportsHeaderValues(ref DIExcel excelFile, int sheetNo,ref int rowIndex, int colHeaderIndex, int colValueIndex, DSRColumnsHeader headerType, string tableName, ICType icType)
{
string SqlQuery;
//-- Set Record Header Value
excelFile.SetCellValue(sheetNo, rowIndex, colHeaderIndex, base.ColumnHeader[headerType]);
try
{
switch (headerType)
{
case DSRColumnsHeader.SECTOR: //-- Set value for ICs.
case DSRColumnsHeader.THEME:
case DSRColumnsHeader.SOURCE:
case DSRColumnsHeader.GOAL:
case DSRColumnsHeader.INSTITUTION:
case DSRColumnsHeader.CF:
case DSRColumnsHeader.CONVENTION:
//--Query is like SELECT count(*) from " + msDBPrefix + "Indicator_Classifications" + msDBLngCode + " WHERE IC_Type='SC'"
SqlQuery = DIQueries.GetTableRecordsCount(tableName, IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[icType]);
excelFile.SetCellValue(sheetNo, rowIndex, colValueIndex, Convert.ToString(DatabaseSummaryReportGenerator.DBConnection.ExecuteScalarSqlQuery(SqlQuery)));
colValueIndex += 1;
this.SetICLevel(ref excelFile, sheetNo, icType, rowIndex, colValueIndex);
break;
}
rowIndex += 2;
}
catch { }//(Exception ex) throw ex }
}
/// <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>
/// Insert Xml serialized presentation text in a worksheet and append it to presenatation workbook
/// </summary>
/// <param name="Presentation">Preesnataion Class Instance TablePresentation / Map</param>
/// <param name="PresentationPath">Full path of presentation file</param>
/// <param name="presentationType">PresentationType enum value. Table / Graph / Map</param>
public static void InsertSelectionSheet(object Presentation, string PresentationPath, PresentationType presentationType)
{
string SerializedText = string.Empty;
DIExcel DIExcel = new DIExcel(PresentationPath);
DIExcel.InsertWorkSheet(SELECTION_WORKSHEET_NAME);
int SelectionWorksheetIndex = DIExcel.GetSheetIndex(SELECTION_WORKSHEET_NAME);
//-- Upadte the user selection GIds before inserting them in selections sheet
//-- Retrieve serialized text
switch (presentationType)
{
case PresentationType.Table:
SerializedText = ((TablePresentation)Presentation).GetSerializedText(true);
break;
case PresentationType.Graph:
if (((GraphPresentation)Presentation).TablePresentation.UserPreference.General.ShowExcel)
{
SelectionWorksheetIndex -= 1;
}
SerializedText = ((GraphPresentation)Presentation).GetSerializedText();
break;
case PresentationType.Map:
SerializedText = ((Map.Map)Presentation).GetSerializedText(true);
break;
}
//Insert Serialized Text into worksheet
// Single excel cell can hold at the most 32000 characters
// If length of serialized text is greater than 32000 char then break them and place them in multiple cells
//TODO Use this logic for Table and graph also and update GetSerializedPresentationText() function accordingly
if (SerializedText.Length > 32000)
{
int i = 0;
//Calculating and Iterating number of 32000 Characters slots in Text
//-- Set First column format type as "TEXT"
DIExcel.SetColumnFormatType("A:A", SelectionWorksheetIndex, SpreadsheetGear.NumberFormatType.Text);
while (i < Math.Floor((double)(SerializedText.Length / 32000)))
{
//'Adding next 32000 Charcters each time at i row.
if (i == 160)
{
}
DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i, 32000));
i += 1;
}
DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i));
}
else
{
DIExcel.SetCellValue(SelectionWorksheetIndex, 0, 0, SerializedText);
}
//-- Hide the selection sheet.
DIExcel.HideWorksheet(SELECTION_WORKSHEET_NAME);
DIExcel.ActiveSheetIndex = 0;
DIExcel.Save();
DIExcel.Close();
}
