/// <summary>
/// Extarct Serialized Xml text from Selection worksheet of presentation
/// </summary>
/// <param name="PresentationPath">Presentation file path</param>
/// <param name="presentationType">Presentation Type</param>
/// <param name="showExcel">True, if hosting application use Excel, otherwise false</param>
/// <returns></returns>Excel
public static string GetSerializedPresentationText(string PresentationPath, PresentationType presentationType, bool showExcel)
{
string RetVal = string.Empty;
object SerializedText = null;
// Open presentation using excel wrapper class (Spreadsheet gear)
DIExcel DIExcel = new DIExcel(PresentationPath);
// Identify Selection worksheet
int SelectionSheetIndex = DIExcel.GetSheetIndex(SELECTION_WORKSHEET_NAME);
// Check for existence of selection tab
if (SelectionSheetIndex != -1)
{
// In case of graph chart sheet is not conidered as sheet so reduce index by 1
if (showExcel && presentationType == Presentation.PresentationType.Graph)
{
SelectionSheetIndex -= 1;
}
//Assunmption - Xml serialized text will occupy at the most 20 cells.
//May convert this logic based on max range used
for (int i = 0; i < 500; i++)
{
SerializedText = DIExcel.GetCellValue(SelectionSheetIndex, i, 0, i, 0);
if (!string.IsNullOrEmpty(SerializedText.ToString()))
{
RetVal += SerializedText.ToString();
}
else
{
break;
}
}
}
DIExcel.Close();
return RetVal;
}
/// <summary>
/// Get Title from Presentaion
/// </summary>
/// <param name="presentaionType"></param>
/// <param name="presFileName"></param>
/// <returns></returns>
private String GetPresentationTitle(PresentationType presentaionType,String presFileName, DIExcel dIExl)
{
string RetVal = string.Empty;
int Map_Suffix_Lenght = 10; //" - Map.xls"
int Graph_Suffix_Lenght = 12; //" - Graph.xls"
try
{
switch (presentaionType)
{
//Map . // In case of Map RetVal Column will be name of the file minus " - Map.xls"
case PresentationType.M:
RetVal = presFileName.Substring(0, presFileName.Length - Map_Suffix_Lenght);
break;
case PresentationType.G:
//Get FileName for RetVal Column. In case of table or Graph this will be name of the file minus " - Table.xls" or - Graph.xls so lenght is 1;
RetVal = presFileName.Substring(0, presFileName.Length - Graph_Suffix_Lenght);
break;
case PresentationType.T:
//Get RetVal and Subtitle Column Value when Presentation is not Map
//Get FileName for RetVal Column. In case of table or Graph this will be name of the file minus " - Table.xls" or - Graph.xls so lenght is 1;
RetVal = presFileName.Substring(0, presFileName.Length - Graph_Suffix_Lenght);
// Value in First sheet A1 and A2 Column respectively will added in RetVal
// Getting Value of Main RetVal from first cell and adding this to RetVal
if (dIExl.GetCellValue(0, CELL_A1).Length > 0)
{
//Check RetVal already contain this word or not, If Not then add this to RetVal
if (RetVal.Contains(dIExl.GetCellValue(0, CELL_A1)) == false)
{
//Check column value contain RetVal filename or not
if ((dIExl.GetCellValue(0, CELL_A1)).Contains(RetVal) == false) // Add column value to RetVal
{
RetVal += " " + dIExl.GetCellValue(0, CELL_A1);
}
else // Replace Existing Value of RetVal with this column value
{
RetVal = dIExl.GetCellValue(0, CELL_A1);
}
}
}
// Getting SubTitle (If Available)
if (dIExl.GetCellValue(0, CELL_A2).Length > 0)
{
// Adding SubTitle in RetVal string with a blank space
RetVal += " " + dIExl.GetCellValue(0, CELL_A2);
}
break;
default:
break;
}
}
catch (Exception ex)
{
}
return RetVal;
}
private int GetElementNId(string excelFilePath, MetaDataType elementType, int elementNameRowIndex)
{
int RetVal = 0;
string ElementName = string.Empty;
string ElementGId = string.Empty;
SpreadsheetGear.IWorksheet MetadataSheet;
IndicatorBuilder IndBuilder = null;
IndicatorInfo IndInfo = new IndicatorInfo();
DataTable Table = null;
int GIdRowIndex = elementNameRowIndex + 1;
//-- Open excel File and get first worksheet
DIExcel DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
//-- Get Element GId
ElementGId = DiExcel.GetCellValue(ElementSheetIndex, GIdRowIndex, ElementGIdColumnIndex, GIdRowIndex, ElementGIdColumnIndex);
//-- Get Element Name
ElementName = DiExcel.GetCellValue(ElementSheetIndex, elementNameRowIndex, ElementNameColumnIndex, elementNameRowIndex, ElementNameColumnIndex);
//-- Get GId By Name if GID is blank in Excel File
if (!string.IsNullOrEmpty(ElementGId) || !string.IsNullOrEmpty(ElementName))
{
switch (elementType)
{
case MetaDataType.Indicator:
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
RetVal = IndBuilder.GetIndicatorNid(ElementGId, ElementName);
break;
case MetaDataType.Map:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.Area.GetAreaMapMetadataByName(string.IsNullOrEmpty(ElementGId) ? ElementName : ElementGId));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][Area_Map_Metadata.LayerNId]);
}
break;
case MetaDataType.Source:
case MetaDataType.Sector:
case MetaDataType.Goal:
case MetaDataType.CF:
case MetaDataType.Theme:
case MetaDataType.Institution:
case MetaDataType.Convention:
case MetaDataType.IndicatorClassification:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.GId, "'" + ElementGId + "'", FieldSelection.Heavy));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][IndicatorClassifications.ICNId]);
}
break;
default:
break;
}
}
return RetVal;
}
