/// <summary>
/// Export IUS into IUS Spreadsheet
/// </summary>
/// <param name="xlsOutputFileName"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
public bool ExportIUS(string xlsOutputFileName, string languageFileNameWPath)
{
bool RetVal = false;
int SheetIndex = 0;
DataTable IUSSheetTable = null;
IWorksheet WorkSheet = null;
DIExcel ExcelObj = new DIExcel();
try
{
// Get IUS Table
IUSSheetTable = this.GetIUSTable();
// Rename First Sheet
ExcelObj.RenameWorkSheet(0, Constants.IUSSheet.SheetName);
WorkSheet = ExcelObj.GetWorkSheet(Constants.IUSSheet.SheetName);
SheetIndex = ExcelObj.GetSheetIndex(Constants.IUSSheet.SheetName);
// Set Sheet Title and Header Values
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetTitle);
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.SheetName);
// Load DataTable Into Worksheet
ExcelObj.LoadDataTableIntoSheet(Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, IUSSheetTable, SheetIndex, false);
// Set Subgroup Dimension Column Header
ExcelObj.SetCellValue(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.SubgroupDimensionsColumn);
// Merger Subgroup Dimension Column Text
ExcelObj.MergeCells(SheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, Constants.IUSSheet.DimensionHeaderRowIndex, ExcelObj.GetUsedRange(SheetIndex).ColumnCount - 1);
// Format Sheet Contents
this.FormatCell(ExcelObj, SheetIndex);
// Freeze Pane
this.FreezePane(ExcelObj, SheetIndex);
// Save Excel File
ExcelObj.SaveAs(xlsOutputFileName);
RetVal = true;
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
finally
{
if (ExcelObj != null)
{
ExcelObj.Close();
}
}
return RetVal;
}
private string GetElementMetadata(MetaDataType elementType, string elementGId, int elementNId, DIExcel diExcel, int startRowIndex)
{
string RetVal = string.Empty;
MetadataCategoryBuilder MDCatBuilder;
MetadataCategoryInfo MDCatInfo;
XmlDocument MetadataXmlDoc = new XmlDocument();
XmlElement CategoryNode;
XmlElement ParaNode;
XmlText CategoryText;
string Category = string.Empty;
string CategoryValue = string.Empty;
string ElementMetadataXml = string.Empty;
int CategoryNId = 0;
//-- Get Metadata Info for Element
ElementMetadataXml = this.GetElementInfo(elementType, elementGId);
if (string.IsNullOrEmpty(ElementMetadataXml))
{
MetadataConverter.InsertRootNode(MetadataXmlDoc);
}
else
{
MetadataXmlDoc.LoadXml(ElementMetadataXml);
}
MDCatBuilder = new MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
//foreach (IRange Row in DiExcel.GetUsedRange(0).Rows )
for (int Index = startRowIndex; Index < diExcel.GetUsedRange(0).Rows.RowCount; Index++)
{
Category = Convert.ToString(diExcel.GetUsedRange(0).Rows[Index, 0].Value);
CategoryValue = Convert.ToString(diExcel.GetUsedRange(0).Rows[Index, 1].Value);
if (!string.IsNullOrEmpty(Category))
{
//-- Get Metadata CategoryInfo
MDCatInfo = MDCatBuilder.GetMetadataCategoryInfo(FilterFieldType.Name, "'" + DICommon.RemoveQuotes(Category) + "'");
//-- If Metadata not category exists
if (MDCatInfo != null && string.IsNullOrEmpty(MDCatInfo.CategoryName))
{
MDCatInfo.CategoryName = Category;
MDCatInfo.CategoryType = this.GetCategoryTypeText(elementType);
//Create new xmlelement as Category
CategoryNode = MetadataXmlDoc.CreateElement(MetaDataConstants.CategoryTagName);
//Set attribute values of category element.
CategoryNode.SetAttribute(MetaDataConstants.NameAttribute, Category);
// create para element
ParaNode = MetadataXmlDoc.CreateElement(MetaDataConstants.ParaTagName);
//Append as para node under category node
CategoryNode.AppendChild(ParaNode);
// create text node
CategoryText = MetadataXmlDoc.CreateTextNode(this.ReplaceInvalidString(CategoryValue));
//Append category text in paraNode
ParaNode.AppendChild(CategoryText);
//Append it under metadata node
MetadataXmlDoc.DocumentElement.AppendChild(CategoryNode);
}
//-- Get CategoryNId
CategoryNId = MDCatBuilder.CheckNCreateMetadataCategory(MDCatInfo);
this.UpdateMetadataCategory(MetadataXmlDoc, Category, CategoryValue);
}
else
{
//-- Exit for Next Metadata
break;
}
}
RetVal = MetadataXmlDoc.InnerXml;
return RetVal;
}
private int GetNextElementNameRowIndex(DIExcel diExcel, int startRowIndex)
{
int RetVal = 0;
for (int Index = startRowIndex; Index < diExcel.GetUsedRange(0).Rows.RowCount; Index++)
{
if (string.IsNullOrEmpty(Convert.ToString(diExcel.GetUsedRange(0).Rows[Index, 0].Value)))
{
RetVal = Index + 1;
break;
}
}
return RetVal;
}
private void FormatCell(DIExcel excelObj, int sheetIndex)
{
try
{
// Set Title Font
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex).Bold = true;
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex).Size = 12;
// Set Name Font
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex).Size = 10;
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.NameRowIndex, Constants.IUSSheet.FirstColumnIndex).Bold = true;
excelObj.GetRange(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1);
// Set Table Header backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TableStartRowIndex, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, Color.Black, Color.LightGray);
// Set GUID Column backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.GuidColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.GuidColIndex, Color.Black, Color.LightGray);
// Set Subgroup Dimension Column backgroud Color LightGray
excelObj.SetRangeColor(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, Color.Black, Color.LightGray);
// Set Bottom Border color for Subgroup Dimensions
excelObj.SetRangeBorder(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex,Constants.IUSSheet.DimensionHeaderRowIndex,excelObj.GetUsedRange(sheetIndex).ColumnCount - 1, LineStyle.Continuous, BorderWeight.Thin,Color.Black,BordersIndex.EdgeBottom);
// Set Font Size
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Size = 8;
// Set Font Bold
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.FirstColumnIndex, Constants.IUSSheet.TableStartRowIndex, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Bold = true;
// Set Font Name
excelObj.GetRangeFont(sheetIndex, Constants.IUSSheet.TitleRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1).Name = Constants.IUSSheet.SheetFontName;
// Center Align
excelObj.SetHorizontalAlignment(sheetIndex, Constants.IUSSheet.DimensionHeaderRowIndex, Constants.IUSSheet.DimensionStartColIndex, HAlign.Center);
// Set S.No Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.SnoColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.FirstColumnIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.FirstColumnIndex);
// Set GUID Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.GUIDColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.GuidColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.GuidColIndex);
// Set Indicator Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.IndicatorColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.IndicatorColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.IndicatorColIndex);
// Set Unit Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.UnitColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.UnitColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.UnitColIndex);
// Set Subgroup Dimensions Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.SubgroupColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.SgValColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, Constants.IUSSheet.SgValColIndex);
// Set Subgroup Dimensions Column Width
excelObj.SetColumnWidth(sheetIndex, Constants.IUSSheet.DimensionsColumnWidth, Constants.IUSSheet.TableStartRowIndex, Constants.IUSSheet.DimensionStartColIndex, excelObj.GetUsedRange(sheetIndex).RowCount - 1, excelObj.GetUsedRange(sheetIndex).ColumnCount - 1);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
