private void GenerateExcelFile(string xmlFileName, string excelFileName)
{
Questionnarie XMLFile = new Questionnarie();
DIExcel ExcelFile;
int RowIndex = 1;
string HeaderText = string.Empty;
try
{
ExcelFile = new DIExcel();//excelFileName);
if (XMLFile.OpenQuestionnaire(xmlFileName))
{
foreach (Question XmlQuestion in XMLFile.GetAllQuestions().Values)
{
//Write Section Name
if (HeaderText != XmlQuestion.HeaderTxt)
{
HeaderText = XmlQuestion.HeaderTxt;
this.WriteHeader(ExcelFile, RowIndex, HeaderText);
RowIndex += 1;
}
this.WriteQuestionIntoExcel(ExcelFile, XmlQuestion, ref RowIndex);
}
//delete file if already exists
if (File.Exists(excelFileName))
{
File.Delete(excelFileName);
}
ExcelFile.SaveAs(excelFileName);
ExcelFile.Close();
}
}
catch (Exception)
{
}
}
/// <summary>
/// Export metadata to excel file
/// </summary>
/// <param name="ExcelFilePath">Excel File Path to be written as export data</param>
public void ExportMetaDataToExcel(string ExcelFilePath, MetadataElementType elementType, string elementName, string elementGId, int targetElementNID)
{
try
{
// --Open excel and get first worksheet
this.DiExcel = new DIExcel(ExcelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
// --Set Matadata Type in Cell 0,0
switch (elementType)
{
case MetadataElementType.Indicator:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("INDICATOR") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
case MetadataElementType.Area:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("AREA") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
case MetadataElementType.Source:
SetMetadataCommonCellValues(DILanguage.GetLanguageString("SOURCE") + "-" + DILanguage.GetLanguageString("METADATA"), elementName, elementGId);
break;
default:
break;
}
// --Load data from xml to excel file
this.LoadData(elementType, targetElementNID);
//-- Save excel
this.DiExcel.Save();
}
catch (Exception ex)
{
}
}
/// <summary>
/// Import mapping from Excel file. Create the corresponding XML files
/// </summary>
/// <param name="mappingType">Mapping type - > Codelist | IUS | Medatata</param>
/// <param name="paramString">Param string</param>
/// <remarks>Param string should be in the format of DBNID[****]LangauageCode[****]UserNId[****]AgeCodeListGID[****]SexCodeListGId[****]LocationCodeListGID</remarks>
public string ImportMapppingFile(EnumHelper.MappingType mappingType, string paramValue, string languageCode, string dbNId, string fileName)
{
//Load Excel mapping file
string RetVal = string.Empty;
DIExcel ExportExcel = new DIExcel(fileName);
this.languageCode = languageCode;
this.dbNId = dbNId;
switch (mappingType)
{
case EnumHelper.MappingType.CodeList:
RetVal = this.ImportCodeListMappingData(ExportExcel, paramValue).ToString().ToLower();
break;
case EnumHelper.MappingType.IUS:
RetVal = this.ImportIUSMappingData(ExportExcel, paramValue).ToString().ToLower();
break;
case EnumHelper.MappingType.Metadata:
RetVal = this.ImportMetadataMappingData(ExportExcel, paramValue).ToString().ToLower();
break;
default:
break;
}
return RetVal;
}
private DIExcel GenerateDimensionAndAttributesComparison(string hlngcodedb,SDMXObjectModel.Message.StructureType DSD1, SDMXObjectModel.Message.StructureType DSD2, DIExcel ReportExcel, Dictionary<string, string> dictMappedIndicators, List<String> ListOfMissingDimensions, List<String> AdditionalDSD1DimensionList, Dictionary<string, string> dictMappedAttributes, List<String> ListOfMissingAttributes, List<String> AdditionalDSD1AttributeList)
{
int rowindex = 0;
string DimensionName = string.Empty;
string AttributeName = string.Empty;
int SNo;
SDMXObjectModel.Structure.DataStructureComponentsType DSD1DSComponents;
SDMXObjectModel.Structure.DataStructureComponentsType DSD2DSComponents;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD1;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD2;
IWorksheet WorkbookSheet = null;
try
{
DSD1DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD1.Structures.DataStructures[0].Item);
DSD2DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD2.Structures.DataStructures[0].Item);
ConceptsObjDSD1 = DSD1.Structures;
ConceptsObjDSD2 = DSD2.Structures;
ReportExcel.RenameWorkSheet(0, "Dimensions");
// Writing into Dimensions Worksheet
// Writing Matched Dimensions that exist in both DSD1 and DSD2
rowindex = rowindex + 1;
WorkbookSheet = ReportExcel.GetWorksheet(0);
this.WriteValueInCell(ReportExcel, "Matched Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex,3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MatchedDimension in dictMappedIndicators.Keys)
{
if(!(AdditionalDSD1DimensionList.Contains(MatchedDimension)))
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, MatchedDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, MatchedDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel,DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
}
// Writing Missing Dimensions that exist in DSD2 but not in DSD1
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Missing Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MissingDimension in ListOfMissingDimensions)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD2, MissingDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, MissingDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel, DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
// Writing Additional Dimensions that exist in DSD1 but not in DSD2
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Additional Dimensions", rowindex, 1, 14, true, 10, 0, 0);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 0);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 0);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 0);
rowindex = rowindex + 1;
SNo = 0;
foreach (string AdditionalDimension in AdditionalDSD1DimensionList)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 0);
DimensionName = string.Empty;
DimensionName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, AdditionalDimension, hlngcodedb);
this.WriteValueInCell(ReportExcel, AdditionalDimension, rowindex, 2, 10, false, 60, 0, 0);
if (DimensionName != string.Empty)
{
this.WriteValueInCell(ReportExcel, DimensionName, rowindex, 3, 10, false, 70, 0, 0);
}
rowindex = rowindex + 1;
}
// Writing into Attributes Worksheet
ReportExcel.InsertWorkSheet("Attributes");
WorkbookSheet = ReportExcel.GetWorksheet(1);
// Writing Matched Attributes that exist in both DSD1 and DSD2
rowindex = 1;
this.WriteValueInCell(ReportExcel, "Matched Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0,1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MatchedAttribute in dictMappedAttributes.Keys)
{
if (!(AdditionalDSD1AttributeList.Contains(MatchedAttribute)))
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false,10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, MatchedAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, MatchedAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
}
// Writing Missing Attributes that exist in DSD2 but not in DSD1
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Missing Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0,1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string MissingAttribute in ListOfMissingAttributes)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD2, MissingAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, MissingAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
// Writing Additional Attributes that exist in DSD1 but not in DSD2
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Additional Attributes", rowindex, 1, 14, true, 10, 0, 1);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, 1);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, 1);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, 1);
rowindex = rowindex + 1;
SNo = 0;
foreach (string AdditionalAttribute in AdditionalDSD1AttributeList)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, 1);
AttributeName = string.Empty;
AttributeName = GetLanguageBasedConceptNameFromConceptScheme(ConceptsObjDSD1, AdditionalAttribute, hlngcodedb);
this.WriteValueInCell(ReportExcel, AdditionalAttribute, rowindex, 2, 10, false, 60, 0, 1);
if (AttributeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, AttributeName, rowindex, 3, 10, false, 70, 0, 1);
}
rowindex = rowindex + 1;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
/// <summary>
/// Get Metadata from Excel File
/// </summary>
/// <remarks>
/// This method will Extract Metadata from excel file and make a string Containing Matadata in Xml Format .
/// So this can be stored in database
/// </remarks>
public String GetMetadataFromExcelFile(string excelFilePath, MetaDataType elementType, string fldrMetadataTemplatePath)
{
// Step 1 : Open Excel File for reading
// Step2 : Get Mask and blank Metadata xml file from metadata template Folder
// Step3 : Update Blank Metadata file from metadata found in excel file. Metadata will be inserted in xml using
// Position and path definded in Mask file.
string RetVal = String.Empty;
//Step1. Open excel File
// Open excel and get first worksheet
this.DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
// Matadata starts from 5th Row
//this._MetadataStartRowIndexInExl = 4;
// step2: Get Mask and blank xml file from Metadata Template Folder
/////GetMetadataTemplateFilesForImport(elementType, fldrMetadataTemplatePath);
// Step3: Update metadata blank document using metadata found in metadata excel.
// Import will be done using Mask File. So check for Mask file
//if (this._MetadataMaskFilePath.Length > 0 && File.Exists(this._MetadataMaskFilePath))
//{
// if (this._MetadataFilePath.Length > 0 && File.Exists(this._MetadataFilePath))
// {
// int i = 0;
// XmlNode xn = null;
// XmlNodeList xnList = null;
// try
// {
// int temp = 0;
// // Load Blank xml Structure as Metadata file
// MetadataDOM = new XmlDocument();
// MetadataDOM.Load(this._MetadataFilePath);
// // Load Mask File
// MetadataMaskDOM.Load(this._MetadataMaskFilePath);
// // Iterate all child elements of mask file.
// for (i = 0; i < MetadataMaskDOM.DocumentElement.ChildNodes.Count; i++)
// {
// try
// {
// // Get Position and path information for Metadata
// string[] nodes = MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Path")[0].InnerXml.Split('/');
// string[] position = MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Position")[0].InnerXml.Split('/');
// if (position.Length < 1)
// {
// }
// else if (position.Length == 1)
// {
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// //dom.DocumentElement.InnerXml = this.rtbDataValue[temp].Text;
// MetadataDOM.DocumentElement.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// //dom.DocumentElement.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = this.rtbDataValue[temp].Text;
// MetadataDOM.DocumentElement.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// else if (position.Length >= 2)
// {
// // Get the postion of n is postion array
// int npos = position.Length;
// //--check which position has the n
// for (int l = 1; l < position.Length; l++)
// {
// if (position[l] == "n")
// {
// npos = l;
// break;
// }
// }
// // Select Root Node Like "Indicator_Info"
// // Xnlist contain list of all node under rootNode like indicator_Info
// xnList = MetadataDOM.DocumentElement.SelectNodes(nodes[1]);
// xnList = MetadataDOM.DocumentElement.SelectNodes(nodes[1]);
// // Handling for second Postion
// // If n is not at second Postion then then start from node first child under Indicator_Info(Document Element)
// if (position[1] != "n")
// {
// xn = xnList[Convert.ToInt32(position[1]) - 1];
// }
// else
// {
// xn = MetadataDOM.DocumentElement;
// }
// // Iterate inside this node. till we reach at n postion
// //--get the value of xn till the nth position
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// for (int j = 2; j < npos; j++)
// {
// XmlNodeList xnTempList;
// // Getting List of all child nodes .
// // If our path nodes array contain Indicator_Info,Row1,FLD_VAL,ROWData,temp1
// //In First Iteration we selcted all Fld_Val node under Row1
// // In SEcond Iteration we select AllRowDAta node under FLD_Val
// // Continue until j= postion of n .So we have all nodes inside nodelist for which n is applied
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// // Insert metadata value
// if (npos == position.Length)
// {
// //xn.InnerXml = this.rtbDataValue[temp].Text;
// xn.InnerXml = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// for (int j = 2; j < npos - 1; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// if (npos == position.Length)
// {
// xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1);
// temp++;
// }
// }
// //--get the value of the nodes from the nth position
// if (npos < position.Length)
// {
// // Get all row data for which we have n in position
// xnList = xn.SelectNodes(nodes[npos]);
// //xnlist is value for total no of metadata paragraph required
// for (int o = 0; o < xnList.Count; o++)
// {
// try
// {
// xn = xnList[o];
// if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Element")
// {
// // Handling for after n node
// for (int j = npos + 1; j < nodes.Length; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// // Get Value of each metadata
// // xn.InnerXml = SetCharacterEntities(this.rtbDataValue[temp].Text);
// xn.InnerXml = SetCharacterEntities(this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1).ToString());
// temp++;
// }
// else if (MetadataMaskDOM.DocumentElement.ChildNodes[i].SelectNodes("Type")[0].InnerXml == "Attribute")
// {
// for (int j = npos + 1; j < nodes.Length - 1; j++)
// {
// XmlNodeList xnTempList;
// xnTempList = xn.SelectNodes(nodes[j]);
// xn = xnTempList[Convert.ToInt32(position[j]) - 1];
// }
// //xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = SetCharacterEntities(this.rtbDataValue[temp].Text);
// xn.Attributes.GetNamedItem(nodes[nodes.Length - 1]).Value = SetCharacterEntities(this.DiExcel.GetCellValue(0, this._MetadataStartRowIndexInExl + temp, 1, this._MetadataStartRowIndexInExl + temp, 1).ToString());
// temp++;
// }
// }
// catch (Exception ex)
// {
// }
// }
// }
// }
// }
// catch (Exception ex)
// {
// }
// }
// // Get Metadata Text in RetVal
// RetVal = MetadataDOM.InnerXml; //MetadataDOM.Save(xmlFile);
// DiExcel.Close();
// }
// catch (Exception ex)
// {
// RetVal = String.Empty;
// DiExcel.Close();
// }
// }
//}
return(RetVal);
}
/// <summary>
/// Import the Area Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportAreaData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(5, excel, paramString, "Area");
}
private DIExcel GenerateCodelistWorksheets(DIExcel ReportExcel, int SheetIndex, SDMXApi_2_0.Message.StructureType CompleteStructure)
{
int i, j;
IWorksheet CodelistWorkSheet = null;
SDMXApi_2_0.Structure.CodeListType Codelist;
SDMXApi_2_0.Structure.CodeType Code;
string CodelistName = string.Empty;
string CodeValue = string.Empty;
string CodeDescription = string.Empty;
int rowindex = 0;
string Language = string.Empty;
try
{
if (CompleteStructure.Concepts.Concept.Count > 0)
{
Language = CompleteStructure.Concepts.Concept[0].Name[0].lang;
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
Language = CompleteStructure.Concepts.ConceptScheme[0].Name[0].lang;
}
for (i = 1; i <= CompleteStructure.CodeLists.Count; i++)
{
Codelist = CompleteStructure.CodeLists[i - 1];
CodelistName = GetLangSpecificValueFor_Version_2_0(Codelist.Name, Language);
ReportExcel.InsertWorkSheet(CodelistName);
CodelistWorkSheet = ReportExcel.GetWorksheet(i);
rowindex = 1;
this.WriteValueInCell(ReportExcel, CodelistName, rowindex, 1, 12, true, 30, 0, i);
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Code", rowindex, 1, 10, true, 60, 0, i);
this.WriteValueInCell(ReportExcel, "Description", rowindex, 2, 10, true, 250, 0, i);
rowindex = rowindex + 2;
//Binding Codelist
for (j = 0; j < Codelist.Code.Count; j++)
{
Code = new SDMXApi_2_0.Structure.CodeType();
Code = Codelist.Code[j];
CodeValue = Code.value;
CodeDescription = GetLangSpecificValueFor_Version_2_0(Code.Description, Language);
if ((CodeValue.Length + 1) <= 30)
{
this.WriteValueInCell(ReportExcel, CodeValue, rowindex, 1, 10, false, 30, 0, i);
}
else
{
this.WriteValueInCell(ReportExcel, CodeValue, rowindex, 1, 10, false, CodeValue.Length + 1, 0, i);
}
this.WriteValueInCell(ReportExcel, CodeDescription, rowindex, 2, 10, false, 250, 0, i);
rowindex = rowindex + 1;
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
private void WriteQuestionIntoExcel(DIExcel excelFile, Question xmlQuestion, ref int RowIndex)
{
int DataValueRowIndex = RowIndex + 1;
int BoldStartIndex = -1;
int BoldEndIndex = -1;
int ItalicStartIndex = -1;
int ItalicEndIndex = -1;
string BoldStartTag = "<b>";
string ItalicStartTag = "<i>";
string BoldEndTag = "</b>";
string ItalicEndTag = "</i>";
string QuestionText = xmlQuestion.Text;
//set question no and question text
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex, xmlQuestion.No);
excelFile.SetColumnWidth(Constants.SheetIndex, 6, RowIndex, Constants.QuestionNoColumnIndex, RowIndex, Constants.QuestionNoColumnIndex);
//replace <br> with new line character and remove enter key
QuestionText = QuestionText.Replace(Char.ConvertFromUtf32(13), string.Empty);
QuestionText = QuestionText.Replace("\n", string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.CrLf, string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.Lf.ToString(), string.Empty);
QuestionText = QuestionText.Replace("<br>", Microsoft.VisualBasic.ControlChars.NewLine);
//apply bold tag
BoldStartIndex = QuestionText.IndexOf(BoldStartTag);
if (BoldStartIndex >= 0)
{
QuestionText = QuestionText.Replace(BoldStartTag, string.Empty);
BoldEndIndex = QuestionText.IndexOf(BoldEndTag);
QuestionText = QuestionText.Replace(BoldEndTag, string.Empty);
}
//apply italic tag
ItalicStartIndex = QuestionText.IndexOf(ItalicStartTag);
if (ItalicStartIndex >= 0)
{
QuestionText = QuestionText.Replace(ItalicStartTag, string.Empty);
ItalicEndIndex = QuestionText.IndexOf(ItalicEndTag);
QuestionText = QuestionText.Replace(ItalicEndTag, string.Empty);
}
//set question text
QuestionText.Replace(ItalicStartTag, string.Empty);
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, QuestionText);
//set bold
if (BoldStartIndex >= 0 & BoldEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, BoldStartIndex, BoldEndIndex - BoldStartIndex).Font.Bold = true;
}
//set italic
if (ItalicStartIndex >= 0 & ItalicEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, ItalicStartIndex, ItalicEndIndex - ItalicStartIndex).Font.Italic = true;
}
//set layout of question text cell and question no cell
//excelFile.MergeCells(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, RowIndex, Constants.QuestionTextColumnIndex + 6);
//excelFile.SetHorizontalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.HAlign.Justify);
//excelFile.SetVerticalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.VAlign.Justify);
//excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex).Bold = true;
excelFile.AutoFitColumn(Constants.SheetIndex, Constants.QuestionNoColumnIndex);
this.WriteDataValue(excelFile, xmlQuestion, ref DataValueRowIndex);
RowIndex = DataValueRowIndex + 2;
}
private DIExcel GenerateCodelistsComparison(string hlngcodedb, CodelistType Codelist1, CodelistType Codelist2, DIExcel ReportExcel,int SheetIndex)
{
List<CodeType> ListOfMatchedCodes;
List<CodeType> ListOfMissingCodes;
List<CodeType> ListOfAdditionalCodelist1Codes;
List<String> ListOfDSD1Codes;
List<String> ListOfDSD2Codes;
string CodeName = string.Empty;
int i, SNo, rowindex;
ListOfDSD1Codes = new List<string>();
ListOfDSD2Codes = new List<string>();
ListOfMatchedCodes = new List<CodeType>();
ListOfMissingCodes = new List<CodeType>();
ListOfAdditionalCodelist1Codes = new List<CodeType>();
i = 0;
SNo = 0;
rowindex = 0;
CodeType Code = new CodeType();
string CodeId = string.Empty;
string UnmatchedCodeName = string.Empty;
IWorksheet WorkbookSheet = null;
try
{
for (i = 0; i < Codelist2.Items.Count; i++)
{
Code = ((CodeType)(Codelist2.Items[i]));
CodeId = Code.id;
ListOfDSD2Codes.Add(CodeId);
}
for (i = 0; i < Codelist1.Items.Count; i++)
{
Code = ((CodeType)(Codelist1.Items[i]));
CodeId = Code.id;
ListOfDSD1Codes.Add(CodeId);
if (!(ListOfDSD2Codes.Contains(CodeId)))
{
ListOfAdditionalCodelist1Codes.Add(Code);
}
else
{
ListOfMatchedCodes.Add(Code);
}
}
for (i = 0; i < Codelist2.Items.Count; i++)
{
Code = ((CodeType)(Codelist2.Items[i]));
CodeId = Code.id;
if (!(ListOfDSD1Codes.Contains(CodeId)))
{
ListOfMissingCodes.Add(Code);
}
}
// Writing Matched Codes that exist in both Codelist1 and Codelist2
rowindex = rowindex + 1;
WorkbookSheet = ReportExcel.GetWorksheet(SheetIndex);
this.WriteValueInCell(ReportExcel, "Matched Codes", rowindex, 1, 14, true, 10, 0, SheetIndex);
WorkbookSheet.Cells[rowindex, 1, rowindex,2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, SheetIndex);
rowindex = rowindex + 1;
SNo = 0;
foreach (CodeType MatchedCode in ListOfMatchedCodes)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, SheetIndex);
CodeName = string.Empty;
CodeName = GetLangSpecificValue(ListOfMatchedCodes[SNo - 1].Name, hlngcodedb);
this.WriteValueInCell(ReportExcel, MatchedCode.id, rowindex, 2, 10, false,60, 0, SheetIndex);
if (CodeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, CodeName, rowindex, 3, 10, false, 70, 0, SheetIndex);
}
rowindex = rowindex + 1;
}
// Writing Missing Codes that exist in Codelist2 but not in Codelist1
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Missing Codes", rowindex, 1, 14, true,10, 0, SheetIndex);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, SheetIndex);
rowindex = rowindex + 1;
SNo = 0;
foreach (CodeType MissingCode in ListOfMissingCodes)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, SheetIndex);
CodeName = string.Empty;
CodeName = GetLangSpecificValue(ListOfMissingCodes[SNo - 1].Name, hlngcodedb);
this.WriteValueInCell(ReportExcel, MissingCode.id, rowindex, 2, 10, false,60, 0, SheetIndex);
if (CodeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, CodeName, rowindex, 3, 10, false, 70, 0, SheetIndex);
}
rowindex = rowindex + 1;
}
// Writing Additional Codes that exist in Codelist1 but not in Codelist2
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "Additional Codes", rowindex, 1, 14, true, 10, 0, SheetIndex);
WorkbookSheet.Cells[rowindex, 1, rowindex, 2].Merge();
rowindex = rowindex + 2;
this.WriteValueInCell(ReportExcel, "S.No.", rowindex, 1, 12, true, 10, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "ID", rowindex, 2, 12, true, 60, 0, SheetIndex);
this.WriteValueInCell(ReportExcel, "Name", rowindex, 3, 12, true, 70, 0, SheetIndex);
rowindex = rowindex + 1;
SNo = 0;
foreach (CodeType AdditionalCode in ListOfAdditionalCodelist1Codes)
{
SNo = SNo + 1;
this.WriteValueInCell(ReportExcel, SNo.ToString(), rowindex, 1, 10, false, 10, 0, SheetIndex);
CodeName = string.Empty;
CodeName = GetLangSpecificValue(ListOfAdditionalCodelist1Codes[SNo - 1].Name, hlngcodedb);
this.WriteValueInCell(ReportExcel, AdditionalCode.id, rowindex, 2, 10, false, 60, 0, SheetIndex);
if (CodeName != string.Empty)
{
this.WriteValueInCell(ReportExcel, CodeName, rowindex, 3, 10, false, 70, 0, SheetIndex);
}
rowindex = rowindex + 1;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
private bool ImportMetadataMappingData(DIExcel excel, string paramValue)
{
bool RetVal = false;
StringBuilder ParamString = new StringBuilder();
string ResultString = string.Empty;
try
{
ParamString.Append(paramValue);
this.objCallBack = new Callback();
int StartRowIndex = 4;
int RowCount = excel.GetUsedRange(0).RowCount;
string SourceGID = string.Empty;
string TargetGId = string.Empty;
Dictionary<string, string> DictSource = null;
Dictionary<string, string> DictTarget = null;
if (RowCount > StartRowIndex)
{
DictSource = GenerateDict_Source_Target("Metadata", true);
DictTarget = GenerateDict_Source_Target("Metadata", false);
for (int RowIndex = StartRowIndex; RowIndex <= RowCount; RowIndex++)
{
//Get the Source and Target GIDs
SourceGID = excel.GetCellValue(0, RowIndex, 1, RowIndex, 1);
TargetGId = excel.GetCellValue(0, RowIndex, 3, RowIndex, 3);
if (!(string.IsNullOrEmpty(SourceGID) || string.IsNullOrEmpty(TargetGId)))
{
//Check if the mapped GID of Excelsheet lies in the Source Files
if (this.IsValidGID(DictSource, DictTarget, SourceGID, TargetGId))
{
//Make the parameter string
ParamString.Append(SourceGID + Constants.Delimiters.ColumnDelimiter + TargetGId + Constants.Delimiters.RowDelimiter);
}
}
}
}
//Make the XML Files
ResultString = ParamString.ToString();
if (ResultString.Contains(Constants.Delimiters.RowDelimiter))
{
ResultString = ResultString.Substring(0, ResultString.Length - 9);
RetVal = true;
}
objCallBack.GenerateMetadataMappingXml(ResultString);
}
catch (Exception Ex)
{
Global.CreateExceptionString(Ex, null);
RetVal = false;
}
return RetVal;
}
/// <summary>
/// Import the Location Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportLocationData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(4, excel, paramString, "Location");
}
/// <summary>
/// Import the IUS mapping data
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramValue">Parameter value</param>
private string ImportIUSMappingData(DIExcel excel, string paramValue)
{
string RetVal = "false";
//Read the IndicatorSheet
StringBuilder ParamString = new StringBuilder();
string ResultString = string.Empty;
string OutResult = string.Empty;
bool Mapped = false;
bool IsRowMapped = false;
try
{
ParamString.Append(paramValue);
this.objCallBack = new Callback();
int StartRowIndex = 4;
int StartColIndex = 0;
int SheetIndex = 0;
int RowCount = excel.GetUsedRange(0).RowCount;
if (RowCount > StartRowIndex)
{
//Loop through each row and import the records
for (int RowIndex = StartRowIndex; RowIndex <= RowCount; RowIndex++)
{
//Import only those records whose mapping has been done.
if (excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 3, RowIndex, StartColIndex + 3) == "YES")
{
//Make sure that all the values of dropdown are selected
if (!string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 17, RowIndex, StartColIndex + 17)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 18, RowIndex, StartColIndex + 18)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 19, RowIndex, StartColIndex + 19)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 20, RowIndex, StartColIndex + 20)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 21, RowIndex, StartColIndex + 21)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 22, RowIndex, StartColIndex + 22)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 23, RowIndex, StartColIndex + 23)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 24, RowIndex, StartColIndex + 24)) && !string.IsNullOrEmpty(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 25, RowIndex, StartColIndex + 25)))
{
//Make the parameter string
ParamString.Append(excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 13, RowIndex, StartColIndex + 13) + Constants.Delimiters.IndGUIDSeoarator + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 14, RowIndex, StartColIndex + 14) + Constants.Delimiters.IndGUIDSeoarator + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 15, RowIndex, StartColIndex + 15) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 17, RowIndex, StartColIndex + 17) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 18, RowIndex, StartColIndex + 18) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 19, RowIndex, StartColIndex + 19) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 20, RowIndex, StartColIndex + 20) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 21, RowIndex, StartColIndex + 21) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 22, RowIndex, StartColIndex + 22) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 23, RowIndex, StartColIndex + 23) + Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 24, RowIndex, StartColIndex + 24) +
Constants.Delimiters.ColumnDelimiter + excel.GetCellValue(SheetIndex, RowIndex, StartColIndex + 25, RowIndex, StartColIndex + 25) +
Constants.Delimiters.RowDelimiter);
Mapped = true;
}
IsRowMapped = true;
}
}
//Make the XML Files
ResultString = ParamString.ToString();
if (ResultString.Contains(Constants.Delimiters.RowDelimiter))
{
ResultString = ResultString.Substring(0, ResultString.Length - 9);
}
OutResult = objCallBack.GenerateIUSMappingXml(ResultString);
Global.GetLanguageKeyValue("");
string[] OutResultArray = OutResult.Split(new string[] { Constants.Delimiters.ParamDelimiter }, StringSplitOptions.None);
if (OutResultArray[0].ToString() == "true")
{
RetVal = "true";
}
else
{
RetVal = "false";
if (IsRowMapped == false)
{
RetVal += "false[**]No row mapped. Please map at least one row.";
}
else if (Mapped == false)
{
RetVal += "false[**]Invalid Mapping";
}
}
}
}
catch (Exception Ex)
{
Global.CreateExceptionString(Ex, null);
RetVal = "false";
}
return RetVal;
}
/// <summary>
/// Import the Indicator Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportIndicatorData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(0, excel, paramString, "Indicator");
}
/// <summary>
/// Import CodeList Mapping Data
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramValue">Parameter value</param>
private bool ImportCodeListMappingData(DIExcel excel, string paramValue)
{
bool RetVal = false;
//Read the IndicatorSheet
StringBuilder ParamString = new StringBuilder();
string ResultString = string.Empty;
string[] paramValueArray = paramValue.Split(new string[] { "[****]" }, StringSplitOptions.None);
if (paramValueArray[3].ToString().Trim() == string.Empty)
{
if (excel.GetCellValue(0, 0, 50, 0, 50).Trim() != string.Empty)
{
paramValueArray[3] = excel.GetCellValue(0, 0, 50, 0, 50).Trim();
}
}
if (paramValueArray[4].ToString().Trim() == string.Empty)
{
if (excel.GetCellValue(0, 1, 50, 1, 50).Trim() != string.Empty)
{
paramValueArray[4] = excel.GetCellValue(0, 1, 50, 1, 50).Trim();
}
}
if (paramValueArray[5].ToString().Trim() == string.Empty)
{
if (excel.GetCellValue(0, 0, 50, 1, 50).Trim() != string.Empty)
{
paramValueArray[5] = excel.GetCellValue(0, 2, 50, 2, 50).Trim();
}
}
paramValue = string.Empty;
for (int i = 0; i < paramValueArray.Length; i++)
{
if (paramValue == string.Empty)
{
paramValue = paramValueArray[i].ToString();
}
else
{
paramValue = paramValue + "[****]" + paramValueArray[i].ToString();
}
}
try
{
ParamString.Append(paramValue);
this.objCallBack = new Callback();
this.ImportIndicatorData(excel, ParamString);
this.ImportUnitData(excel, ParamString);
if (paramValueArray[3].ToString().Trim() != string.Empty)
{
this.SelectedAgeCodeList = paramValueArray[3].ToString();
this.ImportAgeData(excel, ParamString);
}
if (paramValueArray[4].ToString().Trim() != string.Empty)
{
this.SelectedSexCodeList = paramValueArray[4].ToString();
this.ImportSexData(excel, ParamString);
}
if (paramValueArray[5].ToString().Trim() != string.Empty)
{
this.SelectedLocationCodeList = paramValueArray[5].ToString();
this.ImportLocationData(excel, ParamString);
}
this.ImportAreaData(excel, ParamString);
//Make the XML Files
ResultString = ParamString.ToString();
if (ResultString.Contains(Constants.Delimiters.RowDelimiter))
{
ResultString = ResultString.Substring(0, ResultString.Length - 9);
}
this.objCallBack.GenerateCodelistMappingXml(ResultString);
RetVal = true;
}
catch (Exception Ex)
{
RetVal = false;
Global.CreateExceptionString(Ex, null);
}
return RetVal;
}
/// <summary>
/// Import the Data and Make XML mapping files
/// </summary>
/// <param name="sheetIndex">Sheet Index</param>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Param string</param>
/// <param name="importCode">Import Code</param>
private void ImportCodeListData(int sheetIndex, DIExcel excel, StringBuilder paramString, string importCode)
{
int StartRowIndex = 4;
string SourceGID = string.Empty;
string TargetGId = string.Empty;
Dictionary<string, string> DictSource = null;
Dictionary<string, string> DictTarget = null;
int SheetCount = excel.GetWorksheetCount();
int RowCount = excel.GetUsedRange(sheetIndex).RowCount;
if (RowCount > StartRowIndex)
{
excel.ActivateSheet(sheetIndex);
DictSource = GenerateDict_Source_Target(importCode, true);
DictTarget = GenerateDict_Source_Target(importCode, false);
for (int RowIndex = StartRowIndex; RowIndex <= RowCount; RowIndex++)
{
//Get the Source and Target GIDs
SourceGID = excel.GetCellValue(sheetIndex, RowIndex, 1, RowIndex, 1);
TargetGId = excel.GetCellValue(sheetIndex, RowIndex, 3, RowIndex, 3);
if (!(string.IsNullOrEmpty(SourceGID) || string.IsNullOrEmpty(TargetGId)))
{
//Check if the mapped GID of Excelsheet lies in the Source Files
if (this.IsValidGID(DictSource, DictTarget, SourceGID, TargetGId))
{
//Make the parameter string
paramString.Append(importCode + Constants.Delimiters.ColumnDelimiter + SourceGID + Constants.Delimiters.ColumnDelimiter + TargetGId +
Constants.Delimiters.RowDelimiter);
}
}
}
}
}
private string SaveComparisonReport(string dsd1FileNameWPath, string dsd2FileNameWPath, string hlngcodedb, Dictionary<string, string> dictMappedIndicators, Dictionary<string, string> dictMappedAttributes)
{
string RetVal;
XmlDocument DSD1Xml;
XmlDocument DSD2Xml;
List<String> ListOfMissingDimensions;
List<String> ListOfMissingAttributes;
List<String> DSD2DimensionList;
List<String> DSD2AttributeList;
List<String> DSD1DimensionList;
List<String> DSD1AttributeList;
List<String> AdditionalDSD1DimensionList;
List<String> AdditionalDSD1AttributeList;
List<CodelistType> DSD1Codelists;
List<CodelistType> DSD2Codelists;
RepresentationType LocalRepresentation;
SDMXObjectModel.Structure.DimensionType Dimension;
SDMXObjectModel.Structure.TimeDimensionType TimeDimension;
SDMXObjectModel.Structure.AttributeType Attribute;
SDMXObjectModel.Common.ConceptReferenceType ConceptIdentity;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD1;
SDMXObjectModel.Structure.StructuresType ConceptsObjDSD2;
int i,j,SheetIndex;
RetVal = string.Empty;
DSD1Xml = new XmlDocument();
DSD2Xml = new XmlDocument();
DSD2DimensionList = new List<string>();
DSD2AttributeList = new List<string>();
DSD1DimensionList = new List<string>();
DSD1AttributeList = new List<string>();
ListOfMissingDimensions = new List<string>();
ListOfMissingAttributes = new List<string>();
AdditionalDSD1DimensionList = new List<string>();
AdditionalDSD1AttributeList = new List<string>();
string DSD1Dimension = string.Empty;
string DSD2Dimension = string.Empty;
string DSD1Attribute = string.Empty;
string DSD2Attribute = string.Empty;
string UnmatchedDimensionName = string.Empty;
string UnmatchedAttributeName = string.Empty;
string DSD1CodelistId = string.Empty;
string DSD2CodelistId = string.Empty;
DIExcel ReportExcel = new DIExcel();
string tempPath = string.Empty;
string FileName = string.Empty;
string FilePath = string.Empty;
try
{
DSD1Xml.Load(dsd1FileNameWPath);
DSD2Xml.Load(dsd2FileNameWPath);
SDMXObjectModel.Message.StructureType DSD1 = new SDMXObjectModel.Message.StructureType();
SDMXObjectModel.Message.StructureType DSD2 = new SDMXObjectModel.Message.StructureType();
SDMXObjectModel.Structure.DataStructureComponentsType DSD1DSComponents = new DataStructureComponentsType();
SDMXObjectModel.Structure.DataStructureComponentsType DSD2DSComponents = new DataStructureComponentsType();
CodelistType DSD1Codelist = new CodelistType();
CodelistType DSD2Codelist = new CodelistType();
DSD1 = (SDMXObjectModel.Message.StructureType)Deserializer.LoadFromXmlDocument(typeof(SDMXObjectModel.Message.StructureType), DSD1Xml);
DSD2 = (SDMXObjectModel.Message.StructureType)Deserializer.LoadFromXmlDocument(typeof(SDMXObjectModel.Message.StructureType), DSD2Xml);
DSD1DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD1.Structures.DataStructures[0].Item);
DSD2DSComponents = (SDMXObjectModel.Structure.DataStructureComponentsType)(DSD2.Structures.DataStructures[0].Item);
ConceptsObjDSD1 = DSD1.Structures;
ConceptsObjDSD2 = DSD2.Structures;
DSD1Codelists = DSD1.Structures.Codelists;
DSD2Codelists = DSD2.Structures.Codelists;
// Binding DSD2 Dimension in a list - DSD2DimensionList
for (i = 0; i < DSD2DSComponents.Items[0].Items.Count; i++)
{
if (DSD2DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.TimeDimensionType)
{
TimeDimension = (SDMXObjectModel.Structure.TimeDimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = TimeDimension.ConceptIdentity;
}
else
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
}
DSD2DimensionList.Add(((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString());
}
// Binding DSD2 Attributes in a list - DSD2AttributeList
for (i = 0; i < DSD2DSComponents.Items[1].Items.Count; i++)
{
Attribute = (SDMXObjectModel.Structure.AttributeType)(DSD2DSComponents.Items[1].Items[i]);
ConceptIdentity = Attribute.ConceptIdentity;
DSD2AttributeList.Add(((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString());
}
// Binding Matched Dimensions in a dictionary- dictMappedIndicators that exist in DSD1 as well as in DSD2
// and unmatched Dimensions in a list-AdditionalDSD1DimensionList that exist in DSD1 but not in DSD2
for (i = 0; i < DSD1DSComponents.Items[0].Items.Count; i++)
{
if (DSD1DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.TimeDimensionType)
{
TimeDimension = (SDMXObjectModel.Structure.TimeDimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = TimeDimension.ConceptIdentity;
}
else
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
}
DSD1Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
DSD1DimensionList.Add(DSD1Dimension);
if ((DSD2DimensionList.Contains(DSD1Dimension)))
{
dictMappedIndicators.Add(DSD1Dimension, DSD1Dimension);
}
else
{
AdditionalDSD1DimensionList.Add(DSD1Dimension);
}
}
// Binding Matched Attributes in a dictionary- dictMappedAttributes that exist in DSD1 as well as in DSD2
// and unmatched Attributes in a list-AdditionalDSD1AttributeList that exist in DSD1 but not in DSD2
for (i = 0; i < DSD1DSComponents.Items[1].Items.Count; i++)
{
Attribute = (SDMXObjectModel.Structure.AttributeType)(DSD1DSComponents.Items[1].Items[i]);
ConceptIdentity = Attribute.ConceptIdentity;
DSD1Attribute = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
DSD1AttributeList.Add(DSD1Attribute);
if ((DSD2AttributeList.Contains(DSD1Attribute)))
{
dictMappedAttributes.Add(DSD1Attribute, DSD1Attribute);
}
else
{
AdditionalDSD1AttributeList.Add(DSD1Attribute);
}
}
//Binding Missing Dimensions in a list-ListOfMissingDimensions that exist in DSD2 but not in DSD1
for (i = 0; i < DSD2DimensionList.Count; i++)
{
if (!(DSD1DimensionList.Contains(DSD2DimensionList[i])))
{
ListOfMissingDimensions.Add(DSD2DimensionList[i]);
}
}
//Binding Missing Attributes in a list-ListOfMissingAttributes that exist in DSD2 but not in DSD1
for (i = 0; i < DSD2AttributeList.Count; i++)
{
if (!(DSD1AttributeList.Contains(DSD2AttributeList[i])))
{
ListOfMissingAttributes.Add(DSD2AttributeList[i]);
}
}
//Binding Comparison of the two DSDs - DSD1 and DSD2
tempPath= Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath,"stock\\tempSDMXFiles");
FileName = "ComparisonReport_" + Guid.NewGuid().ToString() + ".xls";
FilePath = tempPath + "\\" + FileName;
ReportExcel = this.GenerateDimensionAndAttributesComparison(hlngcodedb,DSD1, DSD2, ReportExcel, dictMappedIndicators, ListOfMissingDimensions, AdditionalDSD1DimensionList, dictMappedAttributes, ListOfMissingAttributes, AdditionalDSD1AttributeList);
SheetIndex = 2;
foreach (string Indicator in dictMappedIndicators.Keys)
{
DSD1CodelistId = string.Empty;
for (i = 0; i < DSD1DSComponents.Items[0].Items.Count; i++)
{
if (DSD1DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.DimensionType)
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD1DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
LocalRepresentation = Dimension.LocalRepresentation;
DSD1Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
if (DSD1Dimension == Indicator)
{
DSD1CodelistId = ((CodelistRefType)((((CodelistReferenceType)(LocalRepresentation.Items[0])).Items[0]))).id;
break;
}
}
}
if (string.IsNullOrEmpty(DSD1CodelistId) == false)
{
for (j = 0; j < DSD1Codelists.Count; j++)
{
if (DSD1CodelistId == DSD1Codelists[j].id)
{
DSD1Codelist = DSD1Codelists[j];
break;
}
}
}
for (i = 0; i < DSD2DSComponents.Items[0].Items.Count; i++)
{
DSD2CodelistId = string.Empty;
if (DSD2DSComponents.Items[0].Items[i] is SDMXObjectModel.Structure.DimensionType)
{
Dimension = (SDMXObjectModel.Structure.DimensionType)(DSD2DSComponents.Items[0].Items[i]);
ConceptIdentity = Dimension.ConceptIdentity;
LocalRepresentation = Dimension.LocalRepresentation;
DSD2Dimension = ((SDMXObjectModel.Common.ConceptRefType)(ConceptIdentity.Items[0])).id.ToString();
if (DSD2Dimension == dictMappedIndicators[Indicator])
{
DSD2CodelistId = ((CodelistRefType)((((CodelistReferenceType)(LocalRepresentation.Items[0])).Items[0]))).id;
break;
}
}
}
if (string.IsNullOrEmpty(DSD2CodelistId) == false)
{
for (j = 0; j < DSD2Codelists.Count; j++)
{
if (DSD2CodelistId == DSD2Codelists[j].id)
{
DSD2Codelist = DSD2Codelists[j];
break;
}
}
}
if ((DSD1CodelistId != string.Empty) && (DSD2CodelistId != string.Empty))
{
if( (DSD1Codelist.Items.Count > 0) && (DSD2Codelist.Items.Count > 0) )
{
ReportExcel.InsertWorkSheet(DSD1Codelist.id + " Vs " + DSD2Codelist.id);
ReportExcel = this.GenerateCodelistsComparison(hlngcodedb, DSD1Codelist, DSD2Codelist, ReportExcel, SheetIndex);
SheetIndex = SheetIndex + 1;
}
}
}
ReportExcel.ActiveSheetIndex = 0;
if (ReportExcel.AvailableWorksheetsCount > 1)
{
ReportExcel.SaveAs(FilePath);
RetVal = FileName;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return RetVal;
}
private void WriteValueInCell(DIExcel Workbook, string value, int rowNum, int colNum, int fontSize, bool boldFlag, double columnWidth, double rowHeight, int Sheetindex)
{
IFont WorkbookFont = null;
IWorksheet WorkbookSheet = null;
WorkbookFont = Workbook.GetCellFont(Sheetindex, rowNum, colNum);
WorkbookSheet = Workbook.GetWorksheet(Sheetindex);
WorkbookFont.Size = fontSize;
WorkbookFont.Bold = boldFlag;
WorkbookSheet.Cells[rowNum, colNum].Value = value;
WorkbookSheet.Cells[rowNum, colNum].WrapText = true;
WorkbookSheet.Cells[rowNum, colNum].ColumnWidth = columnWidth;
if (rowHeight != 0)
{
WorkbookSheet.Cells[rowNum, colNum].RowHeight = rowHeight;
}
}
/// <summary>
/// Import the Sex Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportSexData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(3, excel, paramString, "Sex");
}
private void WriteDataValue(DIExcel excelFile, Question xmlQuestion, ref int dataValueRowIndex)
{
string[] SelectedValues = null;
int Index = 0;
switch (xmlQuestion.AnswerType)
{
case AnswerType.TB:
case AnswerType.TBN:
case AnswerType.RB:
case AnswerType.CB:
case AnswerType.DateType:
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.DataValue);
break;
case AnswerType.SCB:
case AnswerType.SRB:
break;
case AnswerType.CH:
SelectedValues = DICommon.SplitStringNIncludeEmpyValue(xmlQuestion.DataValue, ",");
foreach (AnswerTypeOption Option in xmlQuestion.Options)
{
excelFile.GetCellFont(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex).Name = "Wingdings";
if (SelectedValues[Index] == "1")
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(252));
}
else
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(251));
}
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex + 1, Option.Text);
dataValueRowIndex += 1;
Index += 1;
}
break;
case AnswerType.GridType:
int StartRowIndex = dataValueRowIndex;
int EndRowIndex;
int EndColIndex;
excelFile.LoadDataTableIntoSheet(dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.GridTable, Constants.SheetIndex, true);
dataValueRowIndex += xmlQuestion.GridTable.Rows.Count;
EndRowIndex = dataValueRowIndex - 1;
EndColIndex = Constants.DataValueColumnIndex + xmlQuestion.GridTable.Columns.Count - 1;
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideHorizontal);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideVertical);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeBottom);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeTop);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeLeft);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeRight);
break;
case AnswerType.Calculate:
break;
case AnswerType.Aggregate:
break;
default:
break;
}
}
/// <summary>
/// Import the Unit Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportUnitData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(1, excel, paramString, "Unit");
}
private void WriteHeader(DIExcel excelFile, int rowIndex, string headerText)
{
excelFile.SetCellValue(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex, headerText);
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Size = 12;
}
/// <summary>
/// Create DBVersion Sheet of Summary Report
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create DBVersion Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.DATABASE] + " " + this.ColumnHeader[DSRColumnsHeader.VERSION]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.DATABASE] + " " + this.ColumnHeader[DSRColumnsHeader.VERSION]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
Table = this.GetDBVersionTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.DBVersion.DetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBVersion.LastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.TimePeriodColWidth , Constants.Sheet.DBVersion.DetailsRowIndex, Constants.Sheet.DBVersion.ColValueIndex, LastRow, Constants.Sheet.DBVersion.ColValueIndex);
excelFile.SetHorizontalAlignment(sheetNo, Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBVersion.ColValueIndex, SpreadsheetGear.HAlign.Left);
}
private bool CreateReportForUploadedDSD(SDMXApi_2_0.Message.StructureType UploadedDSDStructure, string sdmxFolderPath, int DbNId, string UserNId)
{
bool RetVal;
RetVal = true;
string AgencyId;
AgencyId = string.Empty;
try
{
AgencyId = DevInfo.Lib.DI_LibSDMX.Constants.MaintenanceAgencyScheme.Prefix + UserNId;
DIExcel ReportExcel = new DIExcel();
ReportExcel = this.GenerateDSDWorksheet(ReportExcel, 0, UploadedDSDStructure);
ReportExcel = this.GenerateCodelistWorksheets(ReportExcel, 0, UploadedDSDStructure);
ReportExcel.ActiveSheetIndex = 0;
ReportExcel.SaveAs(sdmxFolderPath + "\\" + DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName);
this.Save_Artefacts_Details_For_Uploaded_DSD_In_Database(DbNId, DevInfo.Lib.DI_LibSDMX.Constants.Report.Id, AgencyId, DevInfo.Lib.DI_LibSDMX.Constants.Report.Version, string.Empty, ArtefactTypes.Report, sdmxFolderPath + "\\" + DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName, DevInfo.Lib.DI_LibSDMX.Constants.Report.FileName);
}
catch (Exception ex)
{
RetVal = false;
Global.CreateExceptionString(ex, null);
//Global.WriteErrorsInLog("Creating Report For Uploaded DSD From Admin");
//Global.WriteErrorsInLog(ex.StackTrace);
//Global.WriteErrorsInLog(ex.Message);
}
finally
{
}
return RetVal;
}
public bool ImportMetataFromExcel(MetadataElementType categoryType, MetaDataType metadataType, int elementNId, string xlsFileNameWPath, string xsltFldrPath)
{
bool RetVal = true;
string MetadataText = string.Empty;
string FirstColumnValue = string.Empty;
string SecondColumnValue = string.Empty;
string ThirdColumnValue = string.Empty;
string ElementName = string.Empty;
string ElementGID = string.Empty;
DataTable ExcelDataTable = null;
DataRow Row;
DI7MetaDataBuilder MDBuilder;
DI7MetadataCategoryBuilder MDCategoryBuilder;
DI7MetadataCategoryInfo MetadataCategory;
DIExcel ExcelFile = null;
try
{
// -- Get data table from excel file
ExcelFile = new DIExcel(xlsFileNameWPath);
ExcelDataTable = ExcelFile.GetDataTableFromSheet(ExcelFile.GetSheetName(0));
// -- create database builder objects
MDCategoryBuilder = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries);
// -- import metadata reports with category
for (int RowIndex = 1; RowIndex < ExcelDataTable.Rows.Count; RowIndex++)
{
Row = ExcelDataTable.Rows[RowIndex];
FirstColumnValue = Convert.ToString(Row[0]);
SecondColumnValue = Convert.ToString(Row[1]);
ThirdColumnValue = Convert.ToString(Row[2]);
// get element name
if (string.IsNullOrEmpty(ElementName))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementName = FirstColumnValue;
continue;
}
}
// get element gid/id
if (string.IsNullOrEmpty(ElementGID))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementGID = FirstColumnValue;
// get element nid by element gid
if (metadataType == MetaDataType.Source)
{
elementNId = this.GetElementNidByGID(ElementName, metadataType);
}
else
{
elementNId = this.GetElementNidByGID(ElementGID, metadataType);
}
// Skip title row by incrementing row index
RowIndex++;
continue;
}
}
// continue if row is blank
if (string.IsNullOrEmpty(FirstColumnValue) && string.IsNullOrEmpty(SecondColumnValue) && string.IsNullOrEmpty(ThirdColumnValue))
{
// reset element value
elementNId = 0;
ElementName = string.Empty;
ElementGID = string.Empty;
continue;
}
else if (elementNId > 0)
{
// import metadata report with metadata category
// get metadata category and metedata report
MetadataCategory = new DI7MetadataCategoryInfo();
MetadataCategory.CategoryName = SecondColumnValue;
MetadataCategory.CategoryGID = FirstColumnValue;
MetadataCategory.CategoryType = DIQueries.MetadataElementTypeText[categoryType];
// import metadata category
MetadataCategory.CategoryNId = MDCategoryBuilder.CheckNInsertCategory(MetadataCategory);
// import metadata report
if (MetadataCategory.CategoryNId > 0)
{
MDBuilder.InsertORUpdateMetadataInfo(metadataType, MetadataCategory.CategoryNId.ToString(), elementNId, this.ReplaceNewLineInMetadataReport(ThirdColumnValue));
}
}
}
}
catch (Exception)
{
RetVal = false;
}
finally
{
if (ExcelFile != null)
{
ExcelFile.Close();
}
}
return(RetVal);
}
private DIExcel GenerateDSDWorksheet(DIExcel ReportExcel, int SheetIndex, SDMXApi_2_0.Message.StructureType CompleteStructure)
{
int i, j;
IWorksheet DSDWorkSheet = null;
int rowindex = 0;
string Language, AttributeImportance;
Language = string.Empty;
AttributeImportance = string.Empty;
try
{
// Language = CompleteStructure.Concepts.Concept[0].Name[0].lang;
if (CompleteStructure.Concepts.Concept.Count > 0)
{
Language = CompleteStructure.Concepts.Concept[0].Name[0].lang;
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
Language = CompleteStructure.Concepts.ConceptScheme[0].Name[0].lang;
}
DSDWorkSheet = ReportExcel.GetWorksheet(0);
ReportExcel.RenameWorkSheet(0, "DSD");
rowindex = rowindex + 1;
this.WriteValueInCell(ReportExcel, "Data Structure Definition", rowindex, 1, 14, true, 30, 0, 0);
rowindex = rowindex + 2;
//Binding Dimensions
this.WriteValueInCell(ReportExcel, "Dimensions", rowindex, 1, 12, true, 30, 0, 0);
rowindex = rowindex + 2;
if (CompleteStructure.Concepts.Concept.Count > 0)
{
for (i = 0; i < CompleteStructure.KeyFamilies[0].Components.Dimension.Count; i++)
{
for (j = 0; j < CompleteStructure.Concepts.Concept.Count; j++)
{
if (CompleteStructure.Concepts.Concept[j].id == CompleteStructure.KeyFamilies[0].Components.Dimension[i].conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
for (i = 0; i < CompleteStructure.KeyFamilies[0].Components.Dimension.Count; i++)
{
for (j = 0; j < CompleteStructure.Concepts.ConceptScheme.Count; j++)
{
for (int k = 0; k < CompleteStructure.Concepts.ConceptScheme[j].Concept.Count; k++)
{
if (CompleteStructure.Concepts.ConceptScheme[j].Concept[k].id == CompleteStructure.KeyFamilies[0].Components.Dimension[i].conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
}
}
//Binding Time Dimension
if (CompleteStructure.Concepts.Concept.Count > 0)
{
for (j = 0; j < CompleteStructure.Concepts.Concept.Count; j++)
{
if (CompleteStructure.Concepts.Concept[j].id == CompleteStructure.KeyFamilies[0].Components.TimeDimension.conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
for (j = 0; j < CompleteStructure.Concepts.ConceptScheme.Count; j++)
{
for (int k = 0; k < CompleteStructure.Concepts.ConceptScheme[j].Concept.Count; k++)
{
if (CompleteStructure.Concepts.ConceptScheme[j].Concept[k].id == CompleteStructure.KeyFamilies[0].Components.TimeDimension.conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
}
rowindex = rowindex + 2;
//Binding Attributes
this.WriteValueInCell(ReportExcel, "Attributes", rowindex, 1, 12, true, 30, 0, 0);
rowindex = rowindex + 2;
if (CompleteStructure.Concepts.Concept.Count > 0)
{
for (i = 0; i < CompleteStructure.KeyFamilies[0].Components.Attribute.Count; i++)
{
for (j = 0; j < CompleteStructure.Concepts.Concept.Count; j++)
{
if (CompleteStructure.Concepts.Concept[j].id == CompleteStructure.KeyFamilies[0].Components.Attribute[i].conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
this.WriteValueInCell(ReportExcel, "Attachment Level : " + CompleteStructure.KeyFamilies[0].Components.Attribute[i].attachmentLevel, rowindex, 1, 10, false, 30, 0, 0);
if (CompleteStructure.KeyFamilies[0].Components.Attribute[i].assignmentStatus == SDMXApi_2_0.Structure.AssignmentStatusType.Mandatory)
{
AttributeImportance = "Mandatory : " + "Yes";
}
else
{
AttributeImportance = "Mandatory : " + "No";
}
this.WriteValueInCell(ReportExcel, AttributeImportance, rowindex, 2, 10, false, 30, 0, 0);
rowindex = rowindex + 2;
break;
}
}
}
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
for (i = 0; i < CompleteStructure.KeyFamilies[0].Components.Attribute.Count; i++)
{
for (j = 0; j < CompleteStructure.Concepts.ConceptScheme.Count; j++)
{
for (int k = 0; k < CompleteStructure.Concepts.ConceptScheme[j].Concept.Count; k++)
{
if (CompleteStructure.Concepts.ConceptScheme[j].Concept[k].id == CompleteStructure.KeyFamilies[0].Components.Attribute[i].conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
this.WriteValueInCell(ReportExcel, "Attachment Level : " + CompleteStructure.KeyFamilies[0].Components.Attribute[i].attachmentLevel, rowindex, 1, 10, false, 30, 0, 0);
if (CompleteStructure.KeyFamilies[0].Components.Attribute[i].assignmentStatus == SDMXApi_2_0.Structure.AssignmentStatusType.Mandatory)
{
AttributeImportance = "Mandatory : " + "Yes";
}
else
{
AttributeImportance = "Mandatory : " + "No";
}
this.WriteValueInCell(ReportExcel, AttributeImportance, rowindex, 2, 10, false, 30, 0, 0);
rowindex = rowindex + 2;
break;
}
}
}
}
}
rowindex = rowindex + 1;
//Binding Measure
this.WriteValueInCell(ReportExcel, "Measure", rowindex, 1, 12, true, 30, 0, 0);
rowindex = rowindex + 1;
if (CompleteStructure.Concepts.Concept.Count > 0)
{
for (j = 0; j < CompleteStructure.Concepts.Concept.Count; j++)
{
if (CompleteStructure.Concepts.Concept[j].id == CompleteStructure.KeyFamilies[0].Components.PrimaryMeasure.conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.Concept[j].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
else if (CompleteStructure.Concepts.ConceptScheme.Count > 0)
{
for (j = 0; j < CompleteStructure.Concepts.ConceptScheme.Count; j++)
{
if (CompleteStructure.Concepts.ConceptScheme[j].id == CompleteStructure.KeyFamilies[0].Components.PrimaryMeasure.conceptSchemeRef)
{
for (int k = 0; k < CompleteStructure.Concepts.ConceptScheme[j].Concept.Count; k++)
{
if (CompleteStructure.Concepts.ConceptScheme[j].Concept[k].id == CompleteStructure.KeyFamilies[0].Components.PrimaryMeasure.conceptRef)
{
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Name, Language), rowindex, 1, 10, false, 30, 0, 0);
this.WriteValueInCell(ReportExcel, GetLangSpecificValueFor_Version_2_0(CompleteStructure.Concepts.ConceptScheme[j].Concept[k].Description, Language), rowindex, 2, 10, false, 250, 0, 0);
rowindex = rowindex + 1;
break;
}
}
}
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ReportExcel;
}
/// <summary>
/// Import the Age Mapping data from Import Excel worksheet
/// </summary>
/// <param name="excel">Excel Object</param>
/// <param name="paramString">Parameter string</param>
private void ImportAgeData(DIExcel excel, StringBuilder paramString)
{
this.ImportCodeListData(2, excel, paramString, "Age");
}
