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