/// <summary>
/// Create FootNote Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, this.ColumnHeader[DSRColumnsHeader.FOOTNOTES]);
excelFile.GetCellFont(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get FootNotes Data TAble.
Table = this.GetFootNoteTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Footnotes.FootNotesDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesHeaderColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesNameColIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Footnotes.FootNotesDetailsRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesNameColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesNameColIndex, true);
excelFile.WrapText(sheetNo, Constants.Sheet.Footnotes.FootNotesHeaderRowIndex, Constants.Sheet.Footnotes.FootNotesLastColIndex, LastRow, Constants.Sheet.Footnotes.FootNotesLastColIndex, true);
// -- Set Cell Borders
}
/// <summary>
/// Create TimePeriod without DataValue Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, Constants.SheetsNames.TimeperiodsWithoutData);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.SheetsNames.TimeperiodsWithoutData);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get TimePeriod Data TAble.
Table = this.GetTimeperiodWithoutDataTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, true);
}
/// <summary>
/// Create Unit Sheet of Summary Report
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Unit Sheet
int SheetNo = base.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.UNIT]);
DataTable UnitTable = null;
// -- sheet content
excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.UNIT]);
excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Unit Table Filled
UnitTable = this.GetUnitTable();
// -- Set SNo into TAble
this.SetSNoIntoTableColumn(ref UnitTable);
// -- Set Value To Excel Sheet
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, UnitTable, SheetNo, false);
int LastRow = Constants.Sheet.Unit.DetailsRowIndex + UnitTable.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Unit.LastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(SheetNo, Constants.Sheet.Unit.DetailsRowIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, LastRow, Constants.Sheet.SummaryReport.UnitColValueIndex, true);
}
/// <summary>
/// Create IUS Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Timeperiod Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.IUS]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.IUS]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.IUS]);
excelFile.SetCellValue(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]);
Table = this.GetIUSTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUS.IUSDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUS.IUSLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex);
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSCountColINdex, LastRow, Constants.Sheet.IUS.IUSLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.Sheet.IUS.IUSNameColIndex, LastRow, Constants.Sheet.IUS.IUSNameColIndex, true);
// -- Set Cell Borders
excelFile.SetCellBorder(Constants.Sheet.IUS.IUSDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Medium, Color.Black);
}
/// <summary>
/// Create IUS Linked To Multiple Classifications Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = base.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSLinkedTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
}
/// <summary>
/// Create IUS NOT Linked TO Classification Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSMissingICTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
// -- Set Cell Borders
excelFile.SetCellBorder(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black);
}
/// <summary>
/// Generate Indicator Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create Indicator Sheet
int SheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.INDICATOR]);
DataTable IndicatorTable = null;
// -- sheet content
excelFile.SetCellValue(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.INDICATOR]);
excelFile.GetCellFont(SheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Indicator DataTable with Values
IndicatorTable = this.GetIndicatorDataTable();
this.SetSNoIntoTableColumn(ref IndicatorTable);
// -- Fill Indicator Excel Sheet with Data
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, IndicatorTable, SheetNo, false);
// -- Apply Font Settings
int RowCount = IndicatorTable.Rows.Count;
this.ApplyFontSettings(ref excelFile, SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, Constants.Sheet.Indicator.DetailsRowIndex + RowCount, Constants.Sheet.Indicator.DetailsRowIndex, true);
// -- Get Last Row
RowCount = Constants.Sheet.Indicator.DetailsRowIndex + RowCount;
// -- Set Column Width
excelFile.SetColumnWidth(SheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(SheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, RowCount, Constants.Sheet.SummaryReport.IndicatorColValueIndex, true);
//// -- Set Cell Borders
//excelFile.SetCellBorder(Constants.Sheet.Indicator.IndicatorDetailsRowIndex, Constants.Sheet.SummaryReport.IndicatorColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black);
RowCount += 2;
// -- Show Missing Info
excelFile.SetCellValue(SheetNo, RowCount, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]);
excelFile.GetCellFont(SheetNo, RowCount, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
DataTable MissingTable = null;
MissingTable = GetMissingIndicators(IndicatorTable);
this.SetSNoIntoTableColumn(ref MissingTable);
RowCount += 1;
// -- Fill Indicator Excel Sheet with Data
excelFile.LoadDataTableIntoSheet(RowCount, Constants.HeaderColIndex, MissingTable, SheetNo, false);
// -- Apply Font Settings
int LastRow = MissingTable.Rows.Count + RowCount;
this.ApplyFontSettings(ref excelFile, SheetNo, RowCount, Constants.HeaderColIndex, LastRow, Constants.Sheet.Indicator.NameColIndex, true);
}
/// <summary>
/// Create TimePeriod Sheet
/// </summary>
/// <param name="excelFile"></param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.AREA]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]);
// -- Get Area Data TAble.
Table = this.GetAreaTable();
excelFile.SetColumnFormatType("H:H",sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.SetColumnFormatType("I:I", sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Area.AreaDetailsRowIndex + Table.Rows.Count;
LastRow += 2;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex, true);
// -- Set Missing Area Header
excelFile.SetCellValue(sheetNo, LastRow, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]);
//excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Bold = true;
excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
LastRow += 1;
DataTable MissingTable = null;
MissingTable = this.GetMissingArea();
excelFile.LoadDataTableIntoSheet(LastRow, Constants.Sheet.SummaryReport.AreaColIndex, MissingTable, sheetNo, false);
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, LastRow, Constants.HeaderColIndex, LastRow + MissingTable.Rows.Count + 1, Constants.Sheet.Area.AreaIDColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.OthersColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaIDColIndex, LastRow, Constants.Sheet.Area.AreaIDColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.AreaNameColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaStartDateColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex, true);
}
/// <summary>
/// Create DataBase Log Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
// -- Create Timeperiod Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.DATABASE_LOG]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.DATABASE_LOG]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
Table = this.GetDataBaseLogTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.DBLog.DataBaseLogDetailsRowIndex, Constants.Sheet.DBLog.DataBaseLogNameColIndex, LastRow, Constants.Sheet.DBLog.DataBaseLogNameColIndex, true);
}
/// <summary>
/// Create Comments Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]);
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.NOTES_NOTES]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get Comments Data TAble.
Table = this.GetCommentsTables();
int Counter = 0;
foreach (DataRow row in Table.Rows)
{
//-- Exit IF current Record is For Comments.
if (!string.IsNullOrEmpty(row[this.ColumnHeader[DSRColumnsHeader.INDICATOR]].ToString()))
{
Counter += 1;
row[DILanguage.GetLanguageString("SERIAL_NUMBER").ToString()] = Counter;
}
}
Table.AcceptChanges();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Comments.CommentsDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsNameColIndex, LastRow, Constants.Sheet.Comments.CommentsNameColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Comments.CommentsDetailsRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Comments.CommentsLastColIndex, LastRow, Constants.Sheet.Comments.CommentsLastColIndex, true);
}
/// <summary>
/// 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>
/// Set Font Style in Reports
/// </summary>
/// <param name="excelFile">Excel File</param>
private void ApplyFontSetting(ref DIExcel excelFile, int sheetNo, int missingRecordsCount)
{
int CurrentRowIndex = 0;
// -- Set Font Of Missing Records
excelFile.GetRangeFont(sheetNo, Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, Constants.Sheet.Indicator.DetailsRowIndex, this.LastColIndex).Bold = true;
CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + missingRecordsCount;
// -- Set Font Of Addtional Records
excelFile.GetRangeFont(sheetNo, CurrentRowIndex - 1, Constants.HeaderColIndex, CurrentRowIndex + 1, this.LastColIndex).Bold = true;
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.DetailsRowIndex, Constants.Sheet.Indicator.NameColIndex, CurrentRowIndex, this.NameColIndex);
// -- Wrap Text of Name Column
excelFile.WrapText(sheetNo, Constants.DetailsRowIndex, this.NameColIndex, CurrentRowIndex, this.NameColIndex, true);
}
