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