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