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