Пример #1
0
        /// <summary>
        /// Add pictures of chemical strucure indicatied by text in each cell in range.
        /// </summary>
        /// <param name="range">The range to sweep.</param>
        /// <param name="callback">Callback function before visiting cell.</param>
        public static void AddChemicalStructures(dynamic range, IPictureGegerator pictureGenerator, Action callback = null)
        {
            switch (range)
            {
            case Excel.Range cells:
                dynamic      sheet  = Globals.ThisAddIn.Application.ActiveSheet;
                Excel.Shapes shapes = sheet.Shapes;

                var saveScreenUpdating = Globals.ThisAddIn.Application.ScreenUpdating;
                var saveCalculation    = Globals.ThisAddIn.Application.Calculation;
                try
                {
                    Globals.ThisAddIn.Application.ScreenUpdating = false;
                    Globals.ThisAddIn.Application.Calculation    = Excel.XlCalculation.xlCalculationManual;

                    var shapeNames = shapes.Cast <Excel.Shape>().Select(n => n.Name).ToList();
                    ExcelTool.EnumerateCells(cells, cell => GenerateChemicalStructurePictureOnCell(cell, pictureGenerator, shapeNames), callback);
                }
                finally
                {
                    Globals.ThisAddIn.Application.Calculation    = saveCalculation;
                    Globals.ThisAddIn.Application.ScreenUpdating = saveScreenUpdating;
                }
                break;

            default:
                break;
            }
        }
Пример #2
0
        private void PrintTextEffectsMenuItem_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            System.Drawing.Size size = TextRenderer.MeasureText("Text Effect  ", new System.Drawing.Font("Arial", 20));

            Excel.Worksheet sheet  = _app.ActiveWorkbook.ActiveSheet;
            Excel.Shapes    shapes = sheet.Shapes;

            int top = 0;

            for (int i = 0; i < 50; i++)
            {
                Excel.Shape shape = shapes.AddTextEffect((Office.MsoPresetTextEffect)i,
                                                         string.Format("Text Effect {0}", i + 1),
                                                         "Arial",
                                                         20,
                                                         Office.MsoTriState.msoFalse,
                                                         Office.MsoTriState.msoFalse,
                                                         Convert.ToSingle(_app.ActiveWindow.VisibleRange.Width / 2) - size.Width / 2,
                                                         top
                                                         );

                top += 30;

                _textEffects.Add(shape);
            }

            return;
        }
Пример #3
0
        private void convertButton_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Worksheet currentSheet = Globals.ThisAddIn.GetActiveWorksheet();
            //  Excel.Workbook workbook = Globals.ThisAddIn.GetActiveWorkbook();
            Excel.Shapes shapes = currentSheet.Shapes;
            string       a      = null;

            foreach (Excel.Shape item in shapes)
            {
                a = item.Name;
            }
            Excel.Shape   shape = shapes.Item(a);
            SmartArt      smart = shape.SmartArt;
            SmartArtNodes nodes = smart.AllNodes;

            List <TextFrame2> textFrame2s = new List <TextFrame2>();

            foreach (SmartArtNode node in nodes)
            {
                textFrame2s.Add(node.TextFrame2);
            }
            TextRange2    range;
            List <string> strings = new List <string>();

            foreach (TextFrame2 textframe2 in textFrame2s)
            {
                range = textframe2.TextRange;
                strings.Add(range.Text);
            }
        }
Пример #4
0
        /// <summary>
        /// Explicitly release all com objects
        /// </summary>

        static void ReleaseAllObjects()
        {
            if (LogCalls)
            {
                DebugLog.Message("ReleaseAllObjects");
            }

            ReleaseObject(XlRange);
            ReleaseObject(XlShapeRange);
            ReleaseObject(XlShapes);
            //			ReleaseObject(XlSelection);
            //			ReleaseObject(XlTemp);

            ReleaseObject(XlQueryTables);
            ReleaseObject(XlPictures);
            ReleaseObject(XlSheet);
            ReleaseObject(XlBook);
            ReleaseObject(XlSheet2);
            ReleaseObject(XlBook2);
            ReleaseObject(XlBooks);

            XlRange      = null;
            XlShapeRange = null;
            XlShapes     = null;
            //		XlSelection = null;
            //		XlTemp = null;
            XlQueryTables = null;
            XlPictures    = null;
            XlSheet       = null;
            XlBook        = null;
            XlSheet2      = null;
            XlBook2       = null;
            XlBooks       = null;
        }
Пример #5
0
 // Initialize Shapes List
 private void InitializeListOfShapes(Excel.Shapes shapes)
 {
     myShapes = new List <MyShape>();
     for (int i = 1; i <= shapes.Count; i++)
     {
         Excel.Shape shape   = shapes.Item(i);
         MyShape     myShape = new MyShape(shape);
         myShapes.Add(myShape);
     }
 }
Пример #6
0
        public static void UpdatePictures(IPictureGegerator pictureGenerator)
        {
            dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;

            Excel.Shapes shapes    = sheet.Shapes;
            var          shapeList = new List <Tuple <Excel.Shape, int, int> >();

            foreach (var shape in shapes.Cast <Excel.Shape>().Where(n => IsChemicalStructure(n)))
            {
                var cell = shape.TopLeftCell;
                shapeList.Add(new Tuple <Excel.Shape, int, int>(shape, cell.Row, cell.Column));
            }

            var saveScreenUpdating = Globals.ThisAddIn.Application.ScreenUpdating;
            var saveCalculation    = Globals.ThisAddIn.Application.Calculation;

            try
            {
                Globals.ThisAddIn.Application.ScreenUpdating = false;
                Globals.ThisAddIn.Application.Calculation    = Excel.XlCalculation.xlCalculationManual;

                foreach (var shapeInfo in shapeList)
                {
                    var         shape       = shapeInfo.Item1;
                    Excel.Range cell        = sheet.Cells[shapeInfo.Item2, shapeInfo.Item3];
                    var         pictureName = shape.Name;
                    shape.Delete();
                    var sg = pictureGenerator.GenerateTemporary((string)cell.Text, (double)cell.Width, (double)cell.Height);
                    if (sg != null)
                    {
                        try
                        {
                            try
                            {
                                var filename = sg.FileName;
                                AddPicture(cell, filename, pictureName);
                            }
                            catch (Exception ex)
                            {
                                Trace.TraceInformation(ex.Message);
                            }
                        }
                        finally
                        {
                            sg.Dispose();
                        }
                    }
                }
            }
            finally
            {
                Globals.ThisAddIn.Application.Calculation    = saveCalculation;
                Globals.ThisAddIn.Application.ScreenUpdating = saveScreenUpdating;
            }
        }
Пример #7
0
        public static void SetChemicalStructureVisible(bool isVisible)
        {
            dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;

            Excel.Shapes shapes = sheet.Shapes;
            var          flag   = isVisible ? Office.MsoTriState.msoTrue : Office.MsoTriState.msoFalse;

            foreach (var shape in shapes.Cast <Excel.Shape>().Where(n => IsChemicalStructure(n)))
            {
                shape.Visible = flag;
            }
        }
        private void GenerateChart()
        {
            Excel.Worksheet  activeSheet   = null;
            Excel.Range      selectedRange = null;
            Excel.Shapes     shapes        = null;
            Excel.Chart      chart         = null;
            Excel.ChartTitle chartTitle    = null;

            try
            {
                activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
                Excel.Range rangePivot;
                rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA2000");
                // selectedRange = (Excel.Range)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Selection;
                selectedRange = IdentifyPivotRanges(rangePivot);
                shapes        = activeSheet.Shapes;
                shapes.AddChart2(Style: 201, XlChartType: Excel.XlChartType.xlColumnClustered,
                                 Left: 480, Top: 190, Width: 450,
                                 Height: Type.Missing, NewLayout: true).Select();

                chart          = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
                chart.HasTitle = false;
                // chart.ChartTitle.Text = "Threats History";
                chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244);                                             // Change chart to light gray
                // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0);
                chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels
                chart.HasLegend = true;
                chart.SetSourceData(selectedRange);

                (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
            }
            finally
            {
                if (chartTitle != null)
                {
                    Marshal.ReleaseComObject(chartTitle);
                }
                if (chart != null)
                {
                    Marshal.ReleaseComObject(chart);
                }
                if (shapes != null)
                {
                    Marshal.ReleaseComObject(shapes);
                }
                if (selectedRange != null)
                {
                    Marshal.ReleaseComObject(selectedRange);
                }
            }
        }
Пример #9
0
        /// <summary>
        /// Find shape on <paramref name="cell"/>.
        /// </summary>
        /// <param name="cell">The cell to find shape.</param>
        /// <returns>The shape on <paramref name="cell"/>.
        /// <see langword="null"/> if no shape on <paramref name="cell"/>.</returns>
        public static Excel.Shape FindChemShape(Excel.Range cell)
        {
            dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;

            Excel.Shapes shapes = sheet.Shapes;
            foreach (var shape in shapes.Cast <Excel.Shape>().Where(n => IsChemicalStructure(n)))
            {
                var cellShape = shape.TopLeftCell;
                if (cell.Row.Equals(cellShape.Row) &&
                    cell.Column.Equals(cellShape.Column))
                {
                    return(shape);
                }
            }
            return(null);
        }
Пример #10
0
        /// <summary>
        /// Excelのオブジェクトを削除
        /// </summary>
        /// <param name="excelWorkbooks">対象のExcel</param>
        /// <param name="shapeName">削除するオブジェクト名</param>
        private void DeleteExcelShapes(ref Excel.Workbook excelWorkbooks, string shapeName)
        {
            // すべてのExcelシートからスタンプを削除
            foreach (Excel.Worksheet sheet in excelWorkbooks.Sheets)
            {
                Excel.Shapes excelShapes = (Excel.Shapes)sheet.Shapes;

                // スタンプ画像かオブジェクト名で判定して削除
                foreach (Excel.Shape shape in excelShapes)
                {
                    if (shape.Name == shapeName)
                    {
                        shape.Delete();
                    }
                }
            }
        }
Пример #11
0
        /// <summary>
        /// Excelの全てのシートにスタンプを貼付け
        /// </summary>
        /// <param name="ExcelWorkbooks">対象のExcel</param>
        /// <param name="imageFilePath">貼付けるスタンプの画像ファイルパス</param>
        /// <param name="stampWidth">補正する画像の横幅</param>
        /// <param name="stampHeight">補正する画像の縦幅</param>
        private void AddStampPicture(ref Excel.Workbook ExcelWorkbooks, string imageFilePath, float stampWidth, float stampHeight, string stampName)
        {
            foreach (var sheet in ExcelWorkbooks.Sheets)
            {
                Excel.Worksheet workSheet = (Excel.Worksheet)sheet;

                // 画像貼付処理
                Excel.Shapes excelShapes = (Excel.Shapes)workSheet.Shapes;
                Excel.Shape  stampShape  = excelShapes.AddPicture(imageFilePath,
                                                                  Microsoft.Office.Core.MsoTriState.msoFalse,
                                                                  Microsoft.Office.Core.MsoTriState.msoTrue,
                                                                  0,
                                                                  0,
                                                                  (float)stampWidth,
                                                                  (float)stampHeight);

                // 貼付けた画像のオブジェクト名を設定
                stampShape.Name = stampName;
            }
        }
        public static void StartLoading()
        {
            Excel.Application app          = Globals.ThisAddIn.Application;
            Excel.Window      activeWindow = app.ActiveWindow;
            Excel.Worksheet   currentSheet = app.ActiveSheet;
            Excel.Range       visibleRange = activeWindow.VisibleRange;

            System.Drawing.Size textSize = System.Windows.Forms.TextRenderer.MeasureText("Loading please wait ...", new System.Drawing.Font("Arial", 22));

            Excel.Shapes shapes = currentSheet.Shapes;

            float left = Convert.ToSingle(visibleRange.Width / 2) - textSize.Width / 2;
            float top  = Convert.ToSingle(visibleRange.Height / 2) - (textSize.Height / 2 + 40);

            if (visibleRange.Row != 1)
            {
                // Add any top margin if there is a scroll top
                Excel.Range invisibleRangeOnTop = currentSheet.Range[currentSheet.Cells[1, 1], currentSheet.Cells[visibleRange.Row - 1, 1]];
                top += invisibleRangeOnTop.Height;
            }

            if (visibleRange.Column != 1)
            {
                // Add any left margin if there is a scroll to right
                Excel.Range invisibleRangeAside = currentSheet.Range[currentSheet.Cells[1, 1], currentSheet.Cells[1, visibleRange.Column - 1]];
                left += invisibleRangeAside.Width;
            }

            _loadingText = shapes.AddTextEffect(Office.MsoPresetTextEffect.msoTextEffect16,
                                                "Loading please wait ...",
                                                "Arial",
                                                22,
                                                Office.MsoTriState.msoFalse,
                                                Office.MsoTriState.msoFalse,
                                                left,
                                                top
                                                );

            app.Interactive = false;
        }
Пример #13
0
        /// <summary>
        /// Add a picture file named <paramref name="pictureFileName"/> on <paramref name="cell"/> and name <paramref name="name"/>.
        /// </summary>
        /// <param name="cell">The cell to add a picture.</param>
        /// <param name="pictureFileName">File name of the picture.</param>
        /// <param name="name">Excel name of the picture.</param>
        public static void AddPicture(Excel.Range cell, string pictureFileName, string name)
        {
            cell.Activate();
            dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;

            Excel.Shapes shapes = sheet.Shapes;
            Excel.Shape  shape  = shapes.AddPicture(
                pictureFileName,
                Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue,
                (float)cell.Left, (float)cell.Top, (float)cell.Width, (float)cell.Height);
            shape.Placement = Excel.XlPlacement.xlMove;
            shape.Name      = name;

            // if you want to fit pictures with each cell, delte the following lines.
            shape.ScaleHeight(1, Office.MsoTriState.msoTrue);
            shape.ScaleWidth(1, Office.MsoTriState.msoTrue);

            var   xyRatioCell  = (float)cell.Width / (float)cell.Height;
            var   xyRatioShape = shape.Width / shape.Height;
            float scale        = 1;

            if (xyRatioCell > xyRatioShape)
            {
                // fit to cell.Height
                scale = (float)cell.Height / shape.Height;
            }
            else
            {
                // fit to cell.Width;
                scale = (float)cell.Width / shape.Width;
            }

            shape.ScaleHeight(scale, Office.MsoTriState.msoTrue);
            shape.ScaleWidth(scale, Office.MsoTriState.msoTrue);
            shape.LockAspectRatio = Office.MsoTriState.msoTrue;
        }
        PopulateAreaWithSubgraphImages
        (
            Range oNameColumnArea,
            Range oSubgraphImageColumnArea,
            SizeF oSubgraphImageSizePt,

            Dictionary <String, Microsoft.Office.Interop.Excel.Shape>
            oOldSubgraphImages,

            TemporaryImages oTemporarySubgraphImages
        )
        {
            Debug.Assert(oNameColumnArea != null);
            Debug.Assert(oSubgraphImageColumnArea != null);
            Debug.Assert(oOldSubgraphImages != null);
            Debug.Assert(oTemporarySubgraphImages != null);

            // Gather some required information.

            Int32 iRows = oNameColumnArea.Rows.Count;

            Debug.Assert(iRows == oSubgraphImageColumnArea.Rows.Count);

            Debug.Assert(oNameColumnArea.Parent is Worksheet);

            Worksheet oWorksheet = (Worksheet)oNameColumnArea.Parent;

            Microsoft.Office.Interop.Excel.Shapes oShapes = oWorksheet.Shapes;

            Object [,] aoNameValues = ExcelUtil.GetRangeValues(oNameColumnArea);

            Dictionary <String, String> oFileNames =
                oTemporarySubgraphImages.FileNames;

            // Set the row heights to fit the images.

            oNameColumnArea.RowHeight =
                oSubgraphImageSizePt.Height + 2 * SubgraphImageMarginPt;

            // Get the first cell in the subgraph image column.

            Range oSubgraphImageCell = (Range)oSubgraphImageColumnArea.Cells[1, 1];

            // Loop through the area's rows.

            for (Int32 iRow = 1; iRow <= iRows; iRow++)
            {
                String sName, sFileName;

                // Check whether the row's name cell has a corresponding file name
                // in the dictionary.

                if (
                    ExcelUtil.TryGetNonEmptyStringFromCell(aoNameValues, iRow, 1,
                                                           out sName)
                    &&
                    oFileNames.TryGetValue(sName, out sFileName)
                    )
                {
                    // Give the picture a name that can be recognized by
                    // GetSubgraphImageDictionary().

                    String sPictureName =
                        VertexTableColumnNames.SubgraphImage + "-" + sName;

                    Microsoft.Office.Interop.Excel.Shape oPicture;

                    // If an old version of the picture remains from a previous
                    // call to this method, delete it.

                    if (oOldSubgraphImages.TryGetValue(sPictureName,
                                                       out oPicture))
                    {
                        oPicture.Delete();
                    }

                    String sFileNameWithPath = Path.Combine(
                        oTemporarySubgraphImages.Folder, sFileName);

                    oPicture = oShapes.AddPicture(sFileNameWithPath,
                                                  MsoTriState.msoFalse, MsoTriState.msoCTrue,

                                                  (Single)(Double)oSubgraphImageCell.Left
                                                  + SubgraphImageMarginPt,

                                                  (Single)(Double)oSubgraphImageCell.Top
                                                  + SubgraphImageMarginPt,

                                                  oSubgraphImageSizePt.Width,
                                                  oSubgraphImageSizePt.Height
                                                  );

                    oPicture.Name = sPictureName;
                }

                // Move down one cell in the image column.

                oSubgraphImageCell = oSubgraphImageCell.get_Offset(1, 0);
            }
        }
Пример #15
0
        public ExcelSortAndFilterButton(ExcelTemplateView templateView)
        {
            this.View = templateView;
            ExcelInterop.Worksheet worksheet = null;
            ExcelInterop.Shapes    shapes    = null;
            ExcelInterop.Shape     shape     = null;
            try
            {
                worksheet  = View.ViewSheet;
                OwnerRange = View.FirstOutputCell;
                Name       = $"ExcelBtn{Interlocked.Increment(ref cpt)}";
                shapes     = worksheet.Shapes;

                shape = (ExcelInterop.Shape)shapes.AddOLEObject("Forms.CommandButton.1",
                                                                Type.Missing,
                                                                false,
                                                                false,
                                                                Type.Missing,
                                                                Type.Missing,
                                                                Type.Missing,
                                                                OwnerRange.Left,
                                                                OwnerRange.Top,
                                                                20,
                                                                20);

                shape.Name = Name;
                object s = worksheet.GetType().InvokeMember(Name, BindingFlags.GetProperty, null, worksheet, null);
                commandButton = s as ExcelForms.CommandButton;


                commandButton.FontName  = "Arial";
                commandButton.Font.Size = 8;
                commandButton.Caption   = "S/F";
                commandButton.ForeColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

                commandButton.Click += () =>
                {
                    using (ExcelMainWindow excelWindow = new ExcelMainWindow(ETKExcel.ExcelApplication.Application.Hwnd))
                    {
                        //@@SortAndFilterManagement.DisplaySortAndFilterWindow(excelWindow, View);
                    }
                };
            }
            finally
            {
                if (shape != null)
                {
                    ExcelApplication.ReleaseComObject(shape);
                }
                if (shapes != null)
                {
                    ExcelApplication.ReleaseComObject(shapes);
                }
                if (worksheet != null)
                {
                    ExcelApplication.ReleaseComObject(worksheet);
                }

                shape     = null;
                shapes    = null;
                worksheet = null;
            }
        }
Пример #16
0
        public void CreateChart(string pivotTableName, int chartTop, string cTitle)
        {
            Excel.Worksheet  activeSheet   = null;
            Excel.Range      selectedRange = null;
            Excel.Shapes     shapes        = null;
            Excel.Chart      chart         = null;
            Excel.ChartTitle chartTitle    = null;

            try
            {
                activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
                Excel.Range rangePivot;
                rangePivot    = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000");
                selectedRange = IdentifyPivotRangesByName(pivotTableName);
                shapes        = activeSheet.Shapes;
                // Width original 255

                if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
                {
                    // 204 is a nice style with shadow
                    shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered,
                                     Left: 10, Top: chartTop, Width: 450,
                                     Height: 210, NewLayout: true).Select();
                }
                else
                {
                    shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered,
                                    Left: 10, Top: chartTop, Width: 450,
                                    Height: 210).Select();
                }

                chart                 = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
                chart.HasTitle        = true;
                chart.ChartTitle.Text = cTitle;
                chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps;

                chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray
                // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0);
                // chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels
                chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels


                chart.SetSourceData(selectedRange);
                chart.HasLegend = false;
                chart.ApplyDataLabels();

                if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
                {
                    chart.FullSeriesCollection(1).DataLabels.ShowValue = true;
                }
                else
                {
                    chart.SeriesCollection(1).DataLabels.ShowValue = true;
                }

                if (pivotTableName == "PivotTableApplicationName")
                {
                    chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward;
                }


                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle    = Excel.XlLineStyle.xlContinuous;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex   = 0;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight       = Excel.XlBorderWeight.xlThin;

                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle    = Excel.XlLineStyle.xlContinuous;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex   = 0;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
                selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight       = Excel.XlBorderWeight.xlThin;

                Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15;

                (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();

                // chart.Export(pivotTableName + ".png");
            }
            catch (Exception ex)
            {
                // ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method");
                ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message);
                throw;
            }
            finally
            {
                if (chartTitle != null)
                {
                    Marshal.ReleaseComObject(chartTitle);
                }
                if (chart != null)
                {
                    Marshal.ReleaseComObject(chart);
                }
                if (shapes != null)
                {
                    Marshal.ReleaseComObject(shapes);
                }
                if (selectedRange != null)
                {
                    Marshal.ReleaseComObject(selectedRange);
                }
            }
        }
Пример #17
0
        public void insertDocument(string relativePath, string nodeRef)
        {
            object missingValue = Type.Missing;
            object trueValue    = true;
            object falseValue   = false;

            try
            {
                // Suppress all app events during this process
                m_SuppressAppEvents = true;

                // Create a new document if no document currently open
                if (m_ExcelApplication.ActiveWorkbook == null)
                {
                    m_ExcelApplication.Workbooks.Add(missingValue);
                }

                // WebDAV or CIFS?
                string strFullPath = m_ServerDetails.getFullPath(relativePath, m_ExcelApplication.ActiveWorkbook.FullName, true);
                string strExtn     = Path.GetExtension(relativePath).ToLower();
                string fileName    = Path.GetFileName(relativePath);
                string nativeExtn  = ".xls" + (m_SupportsXlsx ? "x" : "");

                // If we're using WebDAV, then download file locally before inserting
                if (strFullPath.StartsWith("http"))
                {
                    // Need to unescape the the original path to get the filename
                    fileName = Path.GetFileName(Uri.UnescapeDataString(relativePath));
                    string strTempFile = Path.GetTempPath() + fileName;
                    if (File.Exists(strTempFile))
                    {
                        try
                        {
                            File.Delete(strTempFile);
                        }
                        catch (Exception)
                        {
                            strTempFile = Path.GetTempFileName();
                        }
                    }
                    WebClient fileReader = new WebClient();
                    string    url        = m_ServerDetails.WebClientURL + "download/direct/" + nodeRef.Replace(":/", "") + "/" + Path.GetFileName(relativePath);
                    fileReader.Headers.Add("Cookie: " + webBrowser.Document.Cookie);
                    fileReader.DownloadFile(url, strTempFile);
                    strFullPath = strTempFile;
                }

                Excel.Worksheet worksheet = (Excel.Worksheet)m_ExcelApplication.ActiveSheet;
                Excel.Shapes    shapes    = worksheet.Shapes;
                Excel.Range     range     = (Excel.Range)m_ExcelApplication.Selection;
                object          top       = range.Top;
                object          left      = range.Left;

                if (".bmp .gif .jpg .jpeg .png".IndexOf(strExtn) != -1)
                {
                    Excel.Shape picture = shapes.AddPicture(strFullPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,
                                                            1, 2, 3, 4);
                    picture.Top  = Convert.ToSingle(range.Top);
                    picture.Left = Convert.ToSingle(range.Left);
                    picture.ScaleWidth(1, Microsoft.Office.Core.MsoTriState.msoTrue, missingValue);
                    picture.ScaleHeight(1, Microsoft.Office.Core.MsoTriState.msoTrue, missingValue);
                }
                else if (nativeExtn.IndexOf(strExtn) != -1)
                {
                    // Workaround for KB210684 if clean, new workbook currently open
                    if (!docHasExtension() && m_ExcelApplication.ActiveWorkbook.Saved)
                    {
                        string workbookName = m_ExcelApplication.ActiveWorkbook.Name;
                        string templateName = Path.GetDirectoryName(strFullPath) + "\\" + workbookName + "." + Path.GetFileNameWithoutExtension(strFullPath);
                        m_ExcelApplication.ActiveWorkbook.Close(falseValue, missingValue, missingValue);
                        File.Move(strFullPath, templateName);
                        m_ExcelApplication.Workbooks.Add(templateName);
                    }
                    else
                    {
                        // Load the workbook
                        object         neverUpdateLinks = 2;
                        object         readOnly         = true;
                        Excel.Workbook insertXls        = m_ExcelApplication.Workbooks.Open(strFullPath, neverUpdateLinks, readOnly, missingValue, missingValue,
                                                                                            missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue,
                                                                                            missingValue, missingValue);

                        if (insertXls != null)
                        {
                            try
                            {
                                // Loop backwards through the worksheets copy-pasting them after the active sone
                                Excel.Worksheet sourceSheet;
                                for (int i = insertXls.Worksheets.Count; i > 0; i--)
                                {
                                    sourceSheet = (Excel.Worksheet)insertXls.Worksheets[i];
                                    sourceSheet.Copy(missingValue, worksheet);
                                }
                            }
                            catch (Exception e)
                            {
                                MessageBox.Show(Properties.Resources.UnableToInsert + ": " + e.Message, Properties.Resources.MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                            finally
                            {
                                insertXls.Close(falseValue, missingValue, missingValue);
                            }
                        }
                    }
                }
                else
                {
                    object iconFilename = Type.Missing;
                    object iconIndex    = Type.Missing;
                    object iconLabel    = fileName;
                    string defaultIcon  = Util.DefaultIcon(strExtn);
                    if (defaultIcon.Contains(","))
                    {
                        string[] iconData = defaultIcon.Split(new char[] { ',' });
                        iconFilename = iconData[0];
                        iconIndex    = iconData[1];
                    }
                    object      filename = strFullPath;
                    object      size     = 32;
                    Excel.Shape shape    = shapes.AddOLEObject(missingValue, filename, falseValue, trueValue,
                                                               iconFilename, iconIndex, iconLabel, left, top, size, size);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(Properties.Resources.UnableToInsert + ": " + e.Message, Properties.Resources.MessageBoxTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // Restore app event processing
                m_SuppressAppEvents = false;
            }
        }