OLE with .NET - Part 2 - Extracting OLE Object in Excel

OLE with .NET - Part 2 - Extracting OLE Object in Excel

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

If you want to add to your application the ability to Extract 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.*

Extracting 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 installed our library.
  • 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 main function. Because all of our operations will work with Excel Workbooks. To achieve this, first, create yourself an object of the WorkbookManager class. We have installed this class has 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, you will want to navigate to your existing Workbook storage directory so you can access them later. Here, we can define ourselves a path variable of type string, or you can declare a variable of type var as in the example below (C# version only).
  • C# Version: var path = CommonDataPath + "\\Ole Objects.xlsx";
  • VB Version: Dim path = CommonDataPath & "\Ole Objects.xlsx"
  • We will proceed to access Workbook stored path that we have just created. What is needed now is to create a new workbook object, which will be a copy of the Excel Workbook that we want to work with. We will need to use the Open method for the workbook object with the parameter passed as the path - corresponding to the path to the stored Workbook file.
  • C# Version: Workbook workbook = manager.Workbooks.Open(path);
  • VB Version: Dim workbook As Workbook = manager.Workbooks.Open(path)
  • 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 extract OLE objects only on the first Sheet of Workbook. So 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)
  • Declare a counting variable i:
  • C# Version: int i = 0;
  • VB Version: Dim i As Integer = 0
  • You may wish to return a file stored on your computer. To do this, first declare an output path.
  • C# Version: string outdir = OutputDir + "\\Ole";
  • VB Version: Dim outdir As String = OutputDir & "\Ole"
  • Next, you will need to do something more if the path has already existed.
  • Delete the path if the path already exists
  • C# Version:
        if (Directory.Exists(outdir))
            Directory.Delete(outdir, true);
  • VB Version:
        If Directory.Exists(outdir) Then
            Directory.Delete(outdir, True)
        End If
  • Create path
  • C# Version: Directory.CreateDirectory(outdir);
  • VB Version: Directory.CreateDirectory(outdir)
  • We can now extract the OLE Objects from all our sheets with the OleObjectCollection property. We will also need to write to File with File.WriteAllBytes
  • C# Version:
        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);
            }
        }
  • VB Version:
        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
  • 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
  • Closing the document and returning the path to the output will end our execute function.
  • C# Version:
        workbook.Close();
        return outdir;
  • VB Version:
        workbook.Close()
        Return outdir
  • And finally, the full source code for the above demo.
  • 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

The full source code of this example is also available in our Excel package.

And that is a snapshot of how to Extract OLE Objects in Excel documents with Excel Office Component. In the next part, we will learn how to Insert OLE Object into an existing Excel document. Every stage will be more exciting and lighter, let's look forward to the other great features of Excel Office Component.

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