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> /// Read excel workbook and update Database with Keywords /// This will Get UserKeywords from Excel and Call overloaded GetKeywordAndUpdateDatabase function with /// two parameter presFile path and UKeywords /// </summary> /// <param name="presentationFile">Excel work book to read</param> private Int32 GetKeywordAndUpdateDatabase(string presentationFile) { Int32 RetVal = 0; System.Data.DataTable UKeywordsDataTable; String Pres_FileName = string.Empty; String UKeyword = String.Empty; String Keywords = String.Empty; String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename try { // Using Excel Control Library DIExcel DIExl = new DIExcel(presentationFile); // --- Get UserKeywords --- // Get Keywords Sheet into DataTable UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS); // Get UserKeyword UKeyword = this.GetUserKeyWords(UKeywordsDataTable); //close excel DIExl.Close(); // Call Other overload RetVal = this.GetKeywordAndUpdateDatabase(presentationFile, UKeyword); } catch (Exception ex) { } return RetVal; }
/// <summary> /// Generates the .XLS presentation file in specified folder on the basis of current Map object settings. /// </summary> /// <param name="presentationOutputFolderPath">Folder Path where .xls presentation is created.</param> /// <param name="maxHeightInPixelToFitAll">(optional) maximun width (in pixel) for presentation composite content to fit in a single page. (-1 default)</param> /// <param name="maxWidthInPixelToFitAll">(optional) maximun height (in pixel) for presentation composite content to fit in a single page. (-1 default)</param> /// <returns>Returns the full file path of .xls presentation generated.</returns> public string GeneratePresentation(string presentationOutputFolderPath, int maxWidthInPixelToFitAll, int maxHeightInPixelToFitAll) { string RetVal = string.Empty; if (!(string.IsNullOrEmpty(presentationOutputFolderPath))) { float MapOriginalWidth = this.m_Width; float MapOriginalHeight = this.m_Height; // Getting the xls sheet names in desired language. string MapSheetName = DILanguage.GetLanguageString("MAP"); string MapDataSheetName = DILanguage.GetLanguageString("DATA"); string SourceSheetName = DILanguage.GetLanguageString("SOURCECOMMON"); // Set path for presentation and png images string FileSuffix = DateTime.Now.Ticks.ToString(); // Set Image file extension(.png or .emf) on he basis of UserPreference property. string ImageExtention = string.Empty; if (this.UserPreference.General.ShowExcel) { //If ShowExcel is True, then User can see presentation in MS Excel object ImageExtention = "emf"; } else { // Png format is supported in SpreadsheetGear (.Emf NOT supported) //If ShowExcel is False, then presentation will be opened in Spreadsheet Gear, so Use Png format. ImageExtention = "png"; } string PresentationPath = presentationOutputFolderPath + @"\Map" + FileSuffix + ".xls"; string TitleImgPath = Path.Combine(presentationOutputFolderPath, "Title" + FileSuffix + "." + ImageExtention); //"Title" string MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + "." + ImageExtention); string DisclaimerImgPath = Path.Combine(presentationOutputFolderPath, "Disclaimer" + FileSuffix + "." + ImageExtention); string InsetFilePrefix = string.Empty; DIExcel ExcelApp = new DIExcel(); Image img; //- Calculate pixelToPoint factor as SpreadsheetGear uses point unit instead of Pixel unit for measuring Length and width Graphics graphics = Graphics.FromImage(new System.Drawing.Bitmap(1, 1)); double PixcelToPointFactor = 72 / graphics.DpiX; //1 inch = 72 points Size TitleSize = new Size(); Size LegendSize = new Size(); int MaxLegendHeight = 20; int MaxLegendWidth = 30; int RowIndex = 0; int SheetIndex; double CellHeight = ExcelApp.GetCellHeight(0, 0, 0); // default cell height from default sheet. double CellWidth = ExcelApp.GetColumnWidth(0, 0, 0, 0, 1); // Default cell width from default sheet. int SingleCellWidthInPixel = (int)(ExcelApp.GetWorksheet(0).Cells[0, 0].Width / PixcelToPointFactor); if (maxWidthInPixelToFitAll > 0 && maxHeightInPixelToFitAll > 0) { //- Offset page Margins (default left margin -> 0.7" & right margin -> 1.4") and (default Top & bottom margin -> 0.75") maxWidthInPixelToFitAll = maxWidthInPixelToFitAll - (int)(2.1 * graphics.DpiX); maxHeightInPixelToFitAll = maxHeightInPixelToFitAll - (int)(1.5 * graphics.DpiY); //- Calculating maximum Height & width of presentation's composite contents in Points //maxWidthInPixelToFitAll = (int)(maxWidthInPixelToFitAll * PixcelToPointFactor); //maxHeightInPixelToFitAll = (int)(maxHeightInPixelToFitAll * PixcelToPointFactor); // Offset margin of first column width. maxWidthInPixelToFitAll -= SingleCellWidthInPixel; //- Set final Map width so that Map can be accomodated within maximum width specified. this.m_Width = (float)maxWidthInPixelToFitAll - 2; //- offset width of Inset images (if present)/ //- generally inset image is 15% of total Map and starts after a gap of 1/2 column width. if (this.Insets.Count > 0) { float InsetImageWidth = MapOriginalWidth; //- Default if (this.Insets[0].InsetImage != null) { InsetImageWidth = (float)(this.Insets[0].InsetImage.Width); } this.m_Width -= (InsetImageWidth * 0.1F) + (float)(SingleCellWidthInPixel); } //- Adjusting Map height in proportion with CurrentExtent's Width to height ratio. this.m_Height = this.m_Width * Math.Min(4 / 3, (this.m_CurrentExtent.Height / this.m_CurrentExtent.Width)) + 3; } try { if (File.Exists(PresentationPath)) { File.Delete(PresentationPath); } ExcelApp.SaveAs(PresentationPath); ExcelApp.RenameWorkSheet(0, MapSheetName); //language handling Rename first(default) sheet ExcelApp.Save(); //********************************* WorkSheet 1 - Map Images************************************************** this.ClearSelection(); //-- Clear any Layer selection (if any) //Generate the first sheet which is the Map Image for MRD data. //*** generate title image in temp folder TitleSize = this.GetTitleImage(Path.GetDirectoryName(TitleImgPath), Path.GetFileNameWithoutExtension(TitleImgPath), ImageExtention); //*** Generate Themes Legend Image in temp folder foreach (Theme _ThemeTemp in this.Themes) { if (_ThemeTemp.Visible == true) { //Generate the Legend image, and calculating image maximum height & width. LegendSize = _ThemeTemp.GetLegendImage(presentationOutputFolderPath, _ThemeTemp.ID + FileSuffix, ImageExtention, this.PointShapeIncluded, this.TemplateStyle.Legends.ShowCaption, this.TemplateStyle.Legends.ShowRange, this.TemplateStyle.Legends.ShowCount, this.TemplateStyle.Legends.ShowMissingLegend); MaxLegendWidth = Math.Max(MaxLegendWidth, (int)(LegendSize.Width * PixcelToPointFactor)); MaxLegendHeight = Math.Max(MaxLegendHeight, (int)(LegendSize.Height * PixcelToPointFactor)); } } //*** generate discalimer (common) image in temp folder this.GetDisclaimerImage(Path.GetDirectoryName(DisclaimerImgPath), Path.GetFileNameWithoutExtension(DisclaimerImgPath), ImageExtention, maxWidthInPixelToFitAll); int ImageRowPosition = 0; //Holds the row position for Image to be pasted. //---Insert Sheet for Map of most recent data. SheetIndex = ExcelApp.GetSheetIndex(MapSheetName); ExcelApp.ActivateSheet(SheetIndex); ExcelApp.ShowWorkSheetGridLine(SheetIndex, false); if (this._showTimePeriods || this._showAreaLevels) { //*** Set map rendering info for most recent time period for all level for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++) { this.UpdateRenderingInfo(-1, -1, iThemeIndex); } this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true); } else { this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true); } ExcelApp.Save(); // Set Title Image ImageRowPosition = 0; if (this.Title != "") { img = Image.FromFile(TitleImgPath); // Converting Image size from Pixel to Points while pasting. ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height. } // Set Map Image ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor); // Set Insets Image int InsetCtr; CellWidth = 70; //TODO: Remove hardcoding calculate column width right. int RowOffset = ImageRowPosition; int ColOffset = (int)(this.Width / CellWidth) + 2; InsetFilePrefix = "Inset" + FileSuffix; for (InsetCtr = 0; InsetCtr <= this.Insets.Count - 1; InsetCtr++) { Inset TempInset = this.Insets[InsetCtr]; string InsetImgFilePath = Path.Combine(presentationOutputFolderPath, InsetFilePrefix + InsetCtr + "." + ImageExtention); if (TempInset.InsetImage != null) { TempInset.InsetImage.Save(InsetImgFilePath); } else { //Extract new "png" image of inset in temp folder. this.GetInsetImage(presentationOutputFolderPath, ImageExtention, (int)this.Width, (int)this.Height, InsetCtr, InsetFilePrefix); } if (TempInset.Visible) { //*** Extract InsetName png. TempInset.GetInsetName(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix, ImageExtention); img = Image.FromFile(Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention)); ExcelApp.PasteImage(SheetIndex, Path.Combine(presentationOutputFolderPath, "InsetName" + TempInset.Name + FileSuffix + "." + ImageExtention), ColOffset, RowOffset, img.Width, img.Height); //*** use previously generated inset images in temp folder RowOffset += img.Height / (int)CellHeight; img = Image.FromFile(InsetImgFilePath); ExcelApp.PasteImage(SheetIndex, InsetImgFilePath, ColOffset, RowOffset, (img.Width * 0.09), (img.Height * 0.09)); RowOffset += (int)(img.Width * 0.09) / (int)CellHeight + 2; img.Dispose(); } } //Set Theme Legend Images ImageRowPosition += (int)((this.m_Height * PixcelToPointFactor) / (int)CellHeight) + 2; //Increment row position by rows covered by Image height. int p = 1; //p is the column position of the Legend Images (x - axis). foreach (Theme _ThemeTemp in this.Themes) { if (_ThemeTemp.Visible == true) { img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention); ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); //(double)LegendSize.Width, (double)LegendSize.Height); p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2 } } // Set Sources ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1; RowIndex = ImageRowPosition + 1; ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources"); DataView _DVSources = this.GetUniqueSourceList(); //getting unique sources in DataView for (int ctr = 0; ctr < _DVSources.Count; ctr++) { ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]); //- Wrap Cell for source int EndingColumnToMerge = maxWidthInPixelToFitAll / SingleCellWidthInPixel; ExcelApp.MergeCells(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge); ExcelApp.WrapText(SheetIndex, RowIndex + ctr + 1, 1, RowIndex + ctr + 1, EndingColumnToMerge, true); } // Set Disclaimer Image ImageRowPosition += _DVSources.Count + 3; img = Image.FromFile(DisclaimerImgPath); ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor)); ExcelApp.Save(); //--Now, generate sheets for each timePeriod selected. if (this._showTimePeriods) { DataView DVTimePeriods = this.GetTimePeriodsForThemes(); for (byte i = 0; i <= DVTimePeriods.Count - 1; i++) { //Restrict Excel Worksheet name to 31 characters string sheetName = DVTimePeriods[i][Timeperiods.TimePeriod].ToString(); if (sheetName.Length > 31) { sheetName.Substring(0, 31); } //---Insert Sheet for Map of specified TimePeriod. ExcelApp.InsertWorkSheet(sheetName); SheetIndex = ExcelApp.GetSheetIndex(sheetName); ExcelApp.ActivateSheet(SheetIndex); ExcelApp.ShowWorkSheetGridLine(SheetIndex, false); // Draw Map for specifoed TimePeriod. MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention); for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++) { this.UpdateRenderingInfo((int)(DVTimePeriods[i][Timeperiods.TimePeriodNId]), -1, iThemeIndex); } this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true); // Insert Title Image ImageRowPosition = 0; if (this.Title != "") { img = Image.FromFile(TitleImgPath); ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height. } // Insert Map Image ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor); //Set Theme Legend Images ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height. p = 1; //p is the column position of the Legend Image (x - axis). foreach (Theme _ThemeTemp in this.Themes) { if (_ThemeTemp.Visible == true) { img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention); ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2 } } // Set Sources. ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1; RowIndex = ImageRowPosition + 1; ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, "Sources"); for (int ctr = 0; ctr < _DVSources.Count; ctr++) { //*** Add Sources ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]); } // Insert Disclaimer Image. ImageRowPosition += _DVSources.Count + 3; img = Image.FromFile(DisclaimerImgPath); ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor)); } } else if (this.ShowAreaLevels) //If only AreaLevel is selected. { DataView DVAreaLevels = this.GetAreaLevels(); for (byte i = 0; i <= DVAreaLevels.Count - 1; i++) { //---Insert Sheet for Map of specified TimePeriod. ExcelApp.InsertWorkSheet(DVAreaLevels[i][Area_Level.AreaLevelName].ToString()); SheetIndex = ExcelApp.GetSheetIndex(DVAreaLevels[i][Area_Level.AreaLevelName].ToString()); ExcelApp.ActivateSheet(SheetIndex); ExcelApp.ShowWorkSheetGridLine(SheetIndex, false); // Draw Map for specifoed TimePeriod. MapImgPath = Path.Combine(presentationOutputFolderPath, "Map" + FileSuffix + i.ToString() + "." + ImageExtention); for (int iThemeIndex = 0; iThemeIndex <= this.m_Themes.Count - 1; iThemeIndex++) { this.UpdateRenderingInfo(-1, (int)(DVAreaLevels[i][Area_Level.AreaLevel]), iThemeIndex); } this.GetMapImage(Path.GetDirectoryName(MapImgPath), Path.GetFileNameWithoutExtension(MapImgPath), ImageExtention, true); // Insert Title Image ImageRowPosition = 0; if (this.Title != "") { img = Image.FromFile(TitleImgPath); ExcelApp.PasteImage(SheetIndex, TitleImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); ImageRowPosition += (int)(img.Height * PixcelToPointFactor) / (int)CellHeight + 1; //Increment row position by rows covered by Image height. } // Insert Map Image ExcelApp.PasteImage(SheetIndex, MapImgPath, 1, ImageRowPosition, this.m_Width * PixcelToPointFactor, this.m_Height * PixcelToPointFactor); //Set Theme Legend Images ImageRowPosition += (int)(this.m_Height * PixcelToPointFactor) / (int)CellHeight + 2; //Increment row position by rows covered by Image height. p = 1; //p is the column position of the Legend Image (x - axis). foreach (Theme _ThemeTemp in this.Themes) { if (_ThemeTemp.Visible == true) { img = Image.FromFile(presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention); ExcelApp.PasteImage(SheetIndex, presentationOutputFolderPath + @"\" + _ThemeTemp.ID.ToString() + FileSuffix + "." + ImageExtention, p, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)img.Height * PixcelToPointFactor); p += (int)(img.Width / CellWidth) + 2; // Increment column pos. by Legend's width + 2 } } // Set Sources. ImageRowPosition += MaxLegendHeight / (int)CellHeight + 1; RowIndex = ImageRowPosition + 1; ExcelApp.SetCellValue(SheetIndex, RowIndex, 1, SourceSheetName); for (int ctr = 0; ctr < _DVSources.Count; ctr++) { //Adding sources from Dv into cell. ExcelApp.SetCellValue(SheetIndex, RowIndex + ctr + 1, 1, _DVSources[ctr]["IC_Name"]); } // Insert Disclaimer Image. ImageRowPosition += _DVSources.Count + 3; img = Image.FromFile(DisclaimerImgPath); ExcelApp.PasteImage(SheetIndex, DisclaimerImgPath, 1, ImageRowPosition, (double)img.Width * PixcelToPointFactor, (double)(img.Height * PixcelToPointFactor)); } ExcelApp.Save(); } //********************************* WorkSheet - Map Data********************************************* //TODO Use TablePresentation object logic ExcelApp.InsertWorkSheet(MapDataSheetName); //insert DataSheet. SheetIndex = ExcelApp.GetSheetIndex(MapDataSheetName); ExcelApp.ActivateSheet(SheetIndex); DataTable _DTDataSheet = GetDataSheetView(this.PresentationData); //Getting Map Data or DataSheet. string[,] arrDataView = new string[_DTDataSheet.Rows.Count, _DTDataSheet.Columns.Count - 1]; //Array Columns will be 1 less than dv, to ignore extra footNote column // --Adding Column names in desired language , only one time, at row 1. ExcelApp.SetCellValue(SheetIndex, 1, 0, DILanguage.GetLanguageString("TIMEPERIOD")); //Timeperiods.TimePeriod ExcelApp.SetCellValue(SheetIndex, 1, 1, DILanguage.GetLanguageString("AREAID")); //Area.AreaID ExcelApp.SetCellValue(SheetIndex, 1, 2, DILanguage.GetLanguageString("AREANAME")); //Area.AreaName ExcelApp.SetCellValue(SheetIndex, 1, 3, DILanguage.GetLanguageString("INDICATOR")); //Indicator.IndicatorName ExcelApp.SetCellValue(SheetIndex, 1, 4, DILanguage.GetLanguageString("DATA")); //Data.DataValue ExcelApp.SetCellValue(SheetIndex, 1, 5, DILanguage.GetLanguageString("UNIT")); //Unit.UnitName ExcelApp.SetCellValue(SheetIndex, 1, 6, DILanguage.GetLanguageString("SUBGROUP")); //SubgroupVals.SubgroupVal ExcelApp.SetCellValue(SheetIndex, 1, 7, DILanguage.GetLanguageString("SOURCECOMMON")); //IndicatorClassifications.ICName int col = 8; //Columnn number 8th foreach (string MDColumn in this._MDIndicatorFields.Split(',')) { if (MDColumn.Length > 0) { //Adding column name at "col" position. ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataIndicator.Columns[MDColumn].Caption); col++; } } foreach (string MDColumn in this._MDAreaFields.Split(',')) { if (MDColumn.Length > 0) { //Adding column name at "col" position. ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataArea.Columns[MDColumn].Caption); col++; } } foreach (string MDColumn in this._MDSourceFields.Split(',')) { if (MDColumn.Length > 0) { //Adding column name at "col" position. ExcelApp.SetCellValue(SheetIndex, 1, col, this._DIDataView.MetadataSource.Columns[MDColumn].Caption); col++; } } // ---Columns added... RowIndex = 2; //Start displaying Map Data from Row - 2nd foreach (DataRow DRowDTDataSheet in _DTDataSheet.Rows) { for (int Columns = 0; Columns < _DTDataSheet.Columns.Count; Columns++) { //Assign cellvalue with data in DataTable. ExcelApp.SetCellValue(SheetIndex, RowIndex, Columns, DRowDTDataSheet[Columns].ToString()); if (_DTDataSheet.Columns[Columns].ColumnName == Data.DataValue) { //If Column id = "DataValue" then add footNotes As-Comment if present. if (DRowDTDataSheet[FootNotes.FootNote].ToString() != "") { ExcelApp.AddComment(SheetIndex, RowIndex, Columns, DRowDTDataSheet[FootNotes.FootNote].ToString(), true); } } } RowIndex++; } ExcelApp.AutoFitColumns(SheetIndex, 1, 0, _DTDataSheet.Rows.Count - 1, _DTDataSheet.Columns.Count - 1); //Auto fitting columns width. ExcelApp.Save(); //********************************* WorkSheet - Sources************************************************** ExcelApp.InsertWorkSheet(SourceSheetName); SheetIndex = ExcelApp.GetSheetIndex(SourceSheetName); ExcelApp.ActivateSheet(SheetIndex); ExcelApp.SetCellValue(SheetIndex, 0, 0, SourceSheetName); //Cell value = "Source" in desired language. ExcelApp.SetCellValue(SheetIndex, 0, 2, DIConnection.ConnectionStringParameters.DbName); for (byte i = 0; i <= _DVSources.Count - 1; i++) { //Adding sources from Dv into cell. ExcelApp.SetCellValue(SheetIndex, i + 3, 0, _DVSources[i]["IC_Name"]); ExcelApp.AutoFitColumn(SheetIndex, i); } ExcelApp.Save(); //********************************* WorkSheet - Keyword************************************************** ExcelApp.InsertWorkSheet(Presentation.KEYWORD_WORKSHEET_NAME); ExcelApp.ActivateSheet(ExcelApp.GetSheetIndex(MapSheetName)); //Set first sheet as ACTIVE sheet.. ExcelApp.Save(); RetVal = PresentationPath; } catch (Exception ex) { Console.Write(ex.Message); } finally { ExcelApp.Close(); if (graphics != null) { graphics.Dispose(); } this.m_Width = MapOriginalWidth; this.m_Height = MapOriginalHeight; } } return RetVal; }
/// <summary> /// Insert Xml serialized presentation text in a worksheet and append it to presenatation workbook /// </summary> /// <param name="Presentation">Preesnataion Class Instance TablePresentation / Map</param> /// <param name="PresentationPath">Full path of presentation file</param> /// <param name="presentationType">PresentationType enum value. Table / Graph / Map</param> public static void InsertSelectionSheet(object Presentation, string PresentationPath, PresentationType presentationType) { string SerializedText = string.Empty; DIExcel DIExcel = new DIExcel(PresentationPath); DIExcel.InsertWorkSheet(SELECTION_WORKSHEET_NAME); int SelectionWorksheetIndex = DIExcel.GetSheetIndex(SELECTION_WORKSHEET_NAME); //-- Upadte the user selection GIds before inserting them in selections sheet //-- Retrieve serialized text switch (presentationType) { case PresentationType.Table: SerializedText = ((TablePresentation)Presentation).GetSerializedText(true); break; case PresentationType.Graph: if (((GraphPresentation)Presentation).TablePresentation.UserPreference.General.ShowExcel) { SelectionWorksheetIndex -= 1; } SerializedText = ((GraphPresentation)Presentation).GetSerializedText(); break; case PresentationType.Map: SerializedText = ((Map.Map)Presentation).GetSerializedText(true); break; } //Insert Serialized Text into worksheet // Single excel cell can hold at the most 32000 characters // If length of serialized text is greater than 32000 char then break them and place them in multiple cells //TODO Use this logic for Table and graph also and update GetSerializedPresentationText() function accordingly if (SerializedText.Length > 32000) { int i = 0; //Calculating and Iterating number of 32000 Characters slots in Text //-- Set First column format type as "TEXT" DIExcel.SetColumnFormatType("A:A", SelectionWorksheetIndex, SpreadsheetGear.NumberFormatType.Text); while (i < Math.Floor((double)(SerializedText.Length / 32000))) { //'Adding next 32000 Charcters each time at i row. if (i == 160) { } DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i, 32000)); i += 1; } DIExcel.SetCellValue(SelectionWorksheetIndex, i, 0, SerializedText.Substring(32000 * i)); } else { DIExcel.SetCellValue(SelectionWorksheetIndex, 0, 0, SerializedText); } //-- Hide the selection sheet. DIExcel.HideWorksheet(SELECTION_WORKSHEET_NAME); DIExcel.ActiveSheetIndex = 0; DIExcel.Save(); DIExcel.Close(); }
/// <summary> /// Extarct Serialized Xml text from Selection worksheet of presentation /// </summary> /// <param name="PresentationPath">Presentation file path</param> /// <param name="presentationType">Presentation Type</param> /// <param name="showExcel">True, if hosting application use Excel, otherwise false</param> /// <returns></returns>Excel public static string GetSerializedPresentationText(string PresentationPath, PresentationType presentationType, bool showExcel) { string RetVal = string.Empty; object SerializedText = null; // Open presentation using excel wrapper class (Spreadsheet gear) DIExcel DIExcel = new DIExcel(PresentationPath); // Identify Selection worksheet int SelectionSheetIndex = DIExcel.GetSheetIndex(SELECTION_WORKSHEET_NAME); // Check for existence of selection tab if (SelectionSheetIndex != -1) { // In case of graph chart sheet is not conidered as sheet so reduce index by 1 if (showExcel && presentationType == Presentation.PresentationType.Graph) { SelectionSheetIndex -= 1; } //Assunmption - Xml serialized text will occupy at the most 20 cells. //May convert this logic based on max range used for (int i = 0; i < 500; i++) { SerializedText = DIExcel.GetCellValue(SelectionSheetIndex, i, 0, i, 0); if (!string.IsNullOrEmpty(SerializedText.ToString())) { RetVal += SerializedText.ToString(); } else { break; } } } DIExcel.Close(); 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; }
/// <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; }