/// <summary>
/// Create IUS Linked To Multiple Classifications Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
DataTable Table = null;
int sheetNo = base.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_LINKED_TO_CLASSIFICATIONS"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSLinkedTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
}
/// <summary>
/// Create TimePeriod without DataValue Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, Constants.SheetsNames.TimeperiodsWithoutData);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, Constants.SheetsNames.TimeperiodsWithoutData);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get TimePeriod Data TAble.
Table = this.GetTimeperiodWithoutDataTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Timeperiod.TimePeriodDetailsRowIndex, Constants.Sheet.Timeperiod.TimePeriodLastColIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, LastRow, Constants.Sheet.Timeperiod.TimePeriodColValueIndex, true);
}
/// <summary>
/// Create 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 NOT Linked TO Classification Sheet
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString("IUS_MISSING_CLASSIFICATION"));
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
// -- Get IUSLinked Data TAble.
Table = this.GetIUSMissingICTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.HeaderNameColWidth, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.HeaderRowIndex, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, LastRow, Constants.Sheet.IUSLinkedTOIC.IUSLinkedNameColIndex, true);
// -- Set Cell Borders
excelFile.SetCellBorder(Constants.Sheet.IUSLinkedTOIC.IUSLinkedDetailsRowIndex, Constants.HeaderColIndex, SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, Color.Black);
}
/// <summary>
/// Create TimePeriod Sheet
/// </summary>
/// <param name="excelFile"></param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.AREA]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.AREA]);
excelFile.SetCellValue(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, ColumnHeader[DSRColumnsHeader.GLOBAL]);
// -- Get Area Data TAble.
Table = this.GetAreaTable();
excelFile.SetColumnFormatType("H:H",sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.SetColumnFormatType("I:I", sheetNo, SpreadsheetGear.NumberFormatType.Text);
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.Area.AreaDetailsRowIndex + Table.Rows.Count;
LastRow += 2;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex, true);
// -- Set Missing Area Header
excelFile.SetCellValue(sheetNo, LastRow, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.MISSINGINFORMATION]);
//excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Bold = true;
excelFile.GetCellFont(sheetNo, LastRow, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
LastRow += 1;
DataTable MissingTable = null;
MissingTable = this.GetMissingArea();
excelFile.LoadDataTableIntoSheet(LastRow, Constants.Sheet.SummaryReport.AreaColIndex, MissingTable, sheetNo, false);
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, LastRow, Constants.HeaderColIndex, LastRow + MissingTable.Rows.Count + 1, Constants.Sheet.Area.AreaIDColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.OthersColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaIDColIndex, LastRow, Constants.Sheet.Area.AreaIDColIndex);
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.AreaNameColumnWidth, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex);
// -- autofit Map
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaStartDateColIndex, LastRow, Constants.Sheet.Area.AreaLastColIndex);
// -- Wrap Text of Indicator Column
excelFile.WrapText(sheetNo, Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.Area.AreaNameColIndex, LastRow, Constants.Sheet.Area.AreaNameColIndex, true);
}
/// <summary>
/// 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 Summary Report Record Header and Its Value.
/// </summary>
/// <param name="ExcelSheet">Excel File</param>
/// <param name="SheetNo">Sheet No</param>
private void FeedSummaryReportValues(ref DIExcel excelFile,int sheetNo, int startRowPosition)
{
// -- count - total Records in the Database
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.CountRowIndex, Constants.Sheet.SummaryReport.CountColIndex, Constants.Sheet.SummaryReport.CountColValueIndex, DSRColumnsHeader.COUNT, DatabaseSummaryReportGenerator.DBQueries.TablesName.Data, startRowPosition);
// -- Total indicators
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.IndicatorRowIndex, Constants.Sheet.SummaryReport.IndicatorColIndex, Constants.Sheet.SummaryReport.IndicatorColValueIndex, DSRColumnsHeader.INDICATOR, DatabaseSummaryReportGenerator.DBQueries.TablesName.Indicator, startRowPosition);
// -- Total units
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.UnitRowIndex, Constants.Sheet.SummaryReport.UnitColIndex, Constants.Sheet.SummaryReport.UnitColValueIndex, DSRColumnsHeader.UNIT, DatabaseSummaryReportGenerator.DBQueries.TablesName.Unit, startRowPosition);
// -- Total subgroups
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUP, DatabaseSummaryReportGenerator.DBQueries.TablesName.SubgroupVals, startRowPosition);
// -- Add Subgroup Type and Subgroup Dimension Info For DI6 Database.
if (DICommon.ISDevInfo6Database(DatabaseSummaryReportGenerator.SourceDatabaseNameWPath))
{
// -- Total subgroups Type
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupTypeRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUPTYPE, DatabaseSummaryReportGenerator.DBQueries.TablesName.SubgroupType, startRowPosition);
// -- Total subgroups Dimension
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.SubgroupDimensionRowIndex, Constants.Sheet.SummaryReport.TemplateColIndex, Constants.Sheet.SummaryReport.SubgroupColValueIndex, DSRColumnsHeader.SUBGROUPDIMENSION, DatabaseSummaryReportGenerator.DBQueries.TablesName.Subgroup, startRowPosition);
}
// -- Total IUS
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.IUSRowIndex, Constants.Sheet.SummaryReport.IUSColIndex, Constants.Sheet.SummaryReport.IUSColValueIndex, DSRColumnsHeader.IUS, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorUnitSubgroup, startRowPosition);
// -- Area
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaColIndex, Constants.Sheet.SummaryReport.AreaColValueIndex, DSRColumnsHeader.AREA, DatabaseSummaryReportGenerator.DBQueries.TablesName.Area, startRowPosition);
// -- Area total levels
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, DSRColumnsHeader.LEVEL, DatabaseSummaryReportGenerator.DBQueries.TablesName.Area, startRowPosition);
// --Set TimePeriod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.TimeRowIndex, Constants.Sheet.SummaryReport.TimeColIndex, Constants.Sheet.SummaryReport.TimeColValueIndex, DSRColumnsHeader.TIMEPERIOD, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Set Minimum Timeperiod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.TimeRowIndex, Constants.Sheet.SummaryReport.TimeMinMaxColIndex, Constants.Sheet.SummaryReport.TimeMinMaxColValueIndex, DSRColumnsHeader.MIN, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Maximum Timeperiod
this.SetReportsHeaderValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.MaxTimeRowIndex, Constants.Sheet.SummaryReport.TimeMinMaxColIndex, Constants.Sheet.SummaryReport.TimeMinMaxColValueIndex, DSRColumnsHeader.MAX, DatabaseSummaryReportGenerator.DBQueries.TablesName.TimePeriod, startRowPosition);
// -- Total sources
int SourceRowindex = startRowPosition + Constants.Sheet.SummaryReport.SourceRowIndex;
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref SourceRowindex, Constants.Sheet.SummaryReport.SourceColIndex, Constants.Sheet.SummaryReport.SourceColValueIndex, DSRColumnsHeader.SOURCE, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Source);
// -- Set language
this.SetLanguageValues(ref excelFile, sheetNo, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.LanguageColIndex, Constants.Sheet.SummaryReport.LanguageNameColValueIndex, DSRColumnsHeader.LANGUAGE, DatabaseSummaryReportGenerator.DBQueries.TablesName.Language);
// -- Set Indicator Classification
this.SetICTotalValues(ref excelFile, sheetNo, startRowPosition);
try
{
// -- Set Column Font Bold
excelFile.GetRangeFont(sheetNo, startRowPosition + Constants.Sheet.SummaryReport.AreaRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex).Bold = true;
excelFile.AutoFitColumns(sheetNo, startRowPosition + Constants.Sheet.Area.AreaDetailsRowIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex, startRowPosition + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex, Constants.Sheet.SummaryReport.AreaLevelColIndex);
}
catch
{}
}
/// <summary>
/// Set Indicator Classification SummaryReport Total Values.
/// </summary>
/// <param name="excelFile">Object of Excel Worksheet</param>
/// <param name="sheetNo">Sheet Number of Current Worksheet</param>
private void SetICTotalValues(ref DIExcel excelFile, int sheetNo,int startRowIndex)
{
int ICRowIndex = 0;
// -- Count Total Language.
int LangCount = DatabaseSummaryReportGenerator.DBConnection.ExecuteDataTable(DIQueries.GetLangauges(DatabaseSummaryReportGenerator.DBQueries.DataPrefix)).Rows.Count;
//-- Set Indicator Classification Row Index
ICRowIndex = startRowIndex + Constants.Sheet.SummaryReport.ICLanguageDefaultIndex + LangCount + 2;
// -- sector
this.SetReportsHeaderValues(ref excelFile, sheetNo,ref ICRowIndex, Constants.Sheet.SummaryReport.LanguageColIndex, Constants.Sheet.SummaryReport.LanguageColCountValueIndex, DSRColumnsHeader.SECTOR, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Sector);
// -- goal
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref ICRowIndex, Constants.Sheet.SummaryReport.ICTypeColIndex, Constants.Sheet.SummaryReport.ICTypeColCountValueIndex, DSRColumnsHeader.GOAL, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Goal);
// -- cf
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref ICRowIndex, Constants.Sheet.SummaryReport.ICTypeColIndex, Constants.Sheet.SummaryReport.ICTypeColCountValueIndex, DSRColumnsHeader.CF, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.CF);
// -- institution
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref ICRowIndex, Constants.Sheet.SummaryReport.ICTypeColIndex, Constants.Sheet.SummaryReport.ICTypeColCountValueIndex, DSRColumnsHeader.INSTITUTION, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Institution);
// -- theme
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref ICRowIndex, Constants.Sheet.SummaryReport.ICTypeColIndex, Constants.Sheet.SummaryReport.ICTypeColCountValueIndex, DSRColumnsHeader.THEME, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Theme);
// -- convention
this.SetReportsHeaderValues(ref excelFile, sheetNo, ref ICRowIndex, Constants.Sheet.SummaryReport.ICTypeColIndex, Constants.Sheet.SummaryReport.ICTypeColCountValueIndex, DSRColumnsHeader.CONVENTION, DatabaseSummaryReportGenerator.DBQueries.TablesName.IndicatorClassifications, ICType.Convention);
// -- Set Last Row Index of Summary Sheet
this.SheetLastRowIndex = ICRowIndex;
// -- Apply Font Settings
this.SetSummaryReportFontSettings(ref excelFile, sheetNo, ICRowIndex, startRowIndex);
// -- autofit columns
excelFile.AutoFitColumns(sheetNo, Constants.Sheet.SummaryReport.ICHeaderCellIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex, ICRowIndex, Constants.Sheet.SummaryReport.ICHeaderCellIndex);
}
/// <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();
}
}
