OLE with .NET - Part 3 - Inserting OLE Object in Excel

OLE with .NET - Part 3 - Inserting OLE Object in Excel

After having an overview of OLE, you will probably want to practice using OLE in practice. So what are the actual use cases of OLE? This article will bring you a primary use: Inserting OLE Object in Excel with .NET platform.

If you want to add to your application the ability to Insert any OLE Object into an Excel document, then this is precisely the place for you. Please stick with us to learn how to do it.*

Inserting OLE Object in Excel

  • Note: This example applies to C# platforms. This example will demonstrate how to extract an OLE Object from an existing Excel document that stored on your computer. We have fully installed all of the methods used in this article in the Excel Office Component library, so make sure you have our library installed.
  • First of all, import the libraries needed for this task.
  • C# Version:
    using OfficeComponent.Excel;
    using OfficeComponent.Excel.Charts;
  • VB Version:
    Imports OfficeComponent.Excel;
    Imports OfficeComponent.Excel.Charts;
  • Next, we will proceed to the implementation of the executable function. To achieve this, first, create yourself an object of the WorkbookManager class. We have also installed this class in the OfficeComponent.Excel library
  • C# Version: WorkbookManager manager = new WorkbookManager();
  • VB Version: Dim manager As New WorkbookManager()
  • You will need to use this manager variable to manage all of the Workbooks that we will need to use in our program.
  • Next, we will need to use the manager to manage all the workbooks. Therefore, we will start by creating a new Workbook for ourselves. Unfortunately, we will not have a separate method to create, but we will pass the Add method of the manager. Understandably, managers are considered as editors, while the workbook is the Excel document set.
  • C# Version: Workbook workbook = manager.Workbooks.Add();
  • VB Version: Dim workbook As Workbook = manager.Workbooks.Add()
  • As we defined from the beginning, each workbook will consist of many different sheets, and each sheet will probably include many different OLE objects. Therefore, it is not appropriate if we only insert OLE objects only on the first Sheet of Workbook. You might think of using loops to browse through all the sheets of a document, but here, we allow you to quickly create an IWorksheet collection and assign it with Workbook.Worksheets
  • C# Version: IWorksheet sheet = workbook.Worksheets[0];
  • VB Version: Dim sheet As IWorksheet = workbook.Worksheets(0)
  • In this example, we will use a saved image file on the computer as an OLE object. Therefore, access to the OLE object store is required.
  • C# Version: Image img = Image.FromFile(CommonDataPath + "\\PdfLogo.png");
  • VB Version: Dim img As Image = Image.FromFile(CommonDataPath & "\PdfLogo.png")
  • Create a new object of class OleObject
  • C# Version: OleObject obj = (OleObject)sheet.OleObjectCollection.Add(CommonDataPath + "\\Images.pdf", img, OleLinkType.Embed);
  • VB Version: Dim obj As OleObject = CType(sheet.OleObjectCollection.Add(CommonDataPath & "\Images.pdf", img, OleLinkType.Embed), OleObject)
  • Select the location to insert OLE object
  • C# Version: obj.Location = sheet["A2:B4"];
  • VB Version: obj.Location = sheet("A2:B4")
  • With the output, you will probably want to store it right on your computer. So, please create your output path.
  • ** C# Version:** string fileName = OutputDir + "\\" + this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat);
  • VB Version: Dim fileName As String = OutputDir & "\" & Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat)
  • You can save the file to the newly created path.
  • C# Version: workbook.SaveAs(fileName);
  • VB Version: workbook.SaveAs(fileName)
  • Close the document.
  • C# Version: workbook.Close();
  • VB Version: workbook.Close()
  • By returning the path to the output file location, you will end your executive function.
  • C# Version: return fileName;
  • VB Version: Return fileName
  • And finally, the full source code for the above demo.
  • C# Version:
        WorkbookManager manager = new WorkbookManager();

        // Add a workbook.
        Workbook workbook = manager.Workbooks.Add();

        IWorksheet sheet = workbook.Worksheets[0];

        Image img = Image.FromFile(CommonDataPath + "\\PdfLogo.png");
        OleObject obj = (OleObject)sheet.OleObjectCollection.Add(CommonDataPath + "\\Images.pdf", img, OleLinkType.Embed);
        obj.Location = sheet["A2:B4"];

        string fileName = OutputDir + "\\" + this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat);
        // Save the document.
        workbook.SaveAs(fileName);

        // Close the document.
        workbook.Close();

        return fileName;
  • VB Version:
      Dim manager As New WorkbookManager()

        ' Add a workbook.
        Dim workbook As Workbook = manager.Workbooks.Add()

        Dim sheet As IWorksheet = workbook.Worksheets(0)

        Dim img As Image = Image.FromFile(CommonDataPath & "\PdfLogo.png")
        Dim obj As OleObject = CType(sheet.OleObjectCollection.Add(CommonDataPath & "\Images.pdf", img, OleLinkType.Embed), OleObject)
        obj.Location = sheet("A2:B4")

        Dim fileName As String = OutputDir & "\" & Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat)
        ' Save the document.
        workbook.SaveAs(fileName)

        ' Close the document.
        workbook.Close()

        Return fileName

Bonus

  • To Extract those newly inserted OLE Objects into your document, you can take advantage of the code snippet below.
  • C# Version:
        WorkbookManager manager = new WorkbookManager();

        var path = CommonDataPath + "\\Ole Objects.xlsx";
        Workbook workbook = manager.Workbooks.Open(path);

        IWorksheet sheet = workbook.Worksheets[0];

        int i = 0;
        string outdir = OutputDir + "\\Ole";
        if (Directory.Exists(outdir))
            Directory.Delete(outdir, true);
        Directory.CreateDirectory(outdir);

    #if WEB
        var outputFile = OutputDir + "\\Ole Objects" + Guid.NewGuid().ToString() + ".zip";
        using (System.IO.Compression.ZipStorer zip = System.IO.Compression.ZipStorer.Create(outputFile, ""))
        {
            foreach (OleObject obj in sheet.OleObjectCollection)
            {
                string oleFileName = "oleFile" + i;

                if (obj.IsContainer)
                {
                    byte[] buf = new byte[obj.Container.Length];
                    obj.Container.Read(buf, 0, buf.Length);
                    File.WriteAllBytes(outdir + "\\" + oleFileName, buf);
                    zip.AddFile(System.IO.Compression.ZipStorer.Compression.Store, outdir + "\\" + oleFileName, oleFileName, "");
                }
            }

            // Close the document.
            workbook.Close();
        }
        return outputFile;
    #else
        foreach (OleObject obj in sheet.OleObjectCollection)
        {
            string oleFileName = "oleFile" + i;

            if (obj.IsContainer)
            {
                byte[] buf = new byte[obj.Container.Length];
                obj.Container.Read(buf, 0, buf.Length);
                File.WriteAllBytes(outdir + "\\" + oleFileName, buf);
            }
        }

        // Close the document.
        workbook.Close();
        return outdir;
    #endif
  • VB Version:
      Dim manager As New WorkbookManager()

        Dim path = CommonDataPath & "\Ole Objects.xlsx"
        Dim workbook As Workbook = manager.Workbooks.Open(path)

        Dim sheet As IWorksheet = workbook.Worksheets(0)

        Dim i As Integer = 0
        Dim outdir As String = OutputDir & "\Ole"
        If Directory.Exists(outdir) Then
            Directory.Delete(outdir, True)
        End If
        Directory.CreateDirectory(outdir)

    #If WEB Then
        Dim outputFile = OutputDir & "\Ole Objects" & Guid.NewGuid().ToString() & ".zip"
        Using zip As System.IO.Compression.ZipStorer = System.IO.Compression.ZipStorer.Create(outputFile, "")
            For Each obj As OleObject In sheet.OleObjectCollection
                Dim oleFileName As String = "oleFile" & i

                If obj.IsContainer Then
                    Dim buf(obj.Container.Length - 1) As Byte
                    obj.Container.Read(buf, 0, buf.Length)
                    File.WriteAllBytes(outdir & "\" & oleFileName, buf)
                    zip.AddFile(System.IO.Compression.ZipStorer.Compression.Store, outdir & "\" & oleFileName, oleFileName, "")
                End If
            Next obj

            ' Close the document.
            workbook.Close()
        End Using
        Return outputFile
    #Else
        For Each obj As OleObject In sheet.OleObjectCollection
            Dim oleFileName As String = "oleFile" & i

            If obj.IsContainer Then
                Dim buf(obj.Container.Length - 1) As Byte
                obj.Container.Read(buf, 0, buf.Length)
                File.WriteAllBytes(outdir & "\" & oleFileName, buf)
            End If
        Next obj

        ' Close the document.
        workbook.Close()
        Return outdir
    #End If
  • For Web applications, you can return the results in zip format.
  • C# Version:
        var outputFile = OutputDir + "\\Ole Objects" + Guid.NewGuid().ToString() + ".zip";
        using (System.IO.Compression.ZipStorer zip = System.IO.Compression.ZipStorer.Create(outputFile, ""))
        {
            foreach (OleObject obj in sheet.OleObjectCollection)
            {
                string oleFileName = "oleFile" + i;

                if (obj.IsContainer)
                {
                    byte[] buf = new byte[obj.Container.Length];
                    obj.Container.Read(buf, 0, buf.Length);
                    File.WriteAllBytes(outdir + "\\" + oleFileName, buf);
                    zip.AddFile(System.IO.Compression.ZipStorer.Compression.Store, outdir + "\\" + oleFileName, oleFileName, "");
                }
            }

            // Close the document.
            workbook.Close();
        }
        return outputFile;
  • VB Version:
      Dim outputFile = OutputDir & "\Ole Objects" & Guid.NewGuid().ToString() & ".zip"
        Using zip As System.IO.Compression.ZipStorer = System.IO.Compression.ZipStorer.Create(outputFile, "")
            For Each obj As OleObject In sheet.OleObjectCollection
                Dim oleFileName As String = "oleFile" & i

                If obj.IsContainer Then
                    Dim buf(obj.Container.Length - 1) As Byte
                    obj.Container.Read(buf, 0, buf.Length)
                    File.WriteAllBytes(outdir & "\" & oleFileName, buf)
                    zip.AddFile(System.IO.Compression.ZipStorer.Compression.Store, outdir & "\" & oleFileName, oleFileName, "")
                End If
            Next obj

            ' Close the document.
            workbook.Close()
        End Using
        Return outputFile

The full source code of these examples is also available in our Excel package.

And that is a snapshot of how to Insert OLE Objects in Excel documents with Excel Office Component. You can choose to combine smoothly between Insert and Extract different OLE Objects on the same document based on our example. Besides, the Excel Office Component library also supports many different support methods for Word, Excel, and PDF; Please make sure you have installed and fully experience all our features. See you again on the next topics.

45-Day Money Back Guarantee

We will refund your full money in 45 days
if you are not satisfied with our products

Buy Now
You have successfully subcribed to our mailing list.
Dont miss out Get update on new articles and other opportunities Subscribe