Embed Excel Documents in VB.Net Application


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/07/02/embed-excel-documents-in-vb-net-application/

 

As far as I know, there are no native .NET controls for embedding Office applications.

Earlier you could use the DSOFramer to achieve what you want but then it was discontinued. I believe it still works though (I am not sure). The Web Browser (COM) control is an alternative to DSOFramer, but has its own drawbacks. For example, you cannot use the inbuilt Excel “Goodies”.

Disclaimer: The below is just my personal opinion

I doubt that MS will never support embedded Office applications and the reason is very simple. MS-Office Applications are “End-User-Targeted” products. You need separate licenses for it and from a business perspective they wouldn’t want to loose on that

Having said that what alternatives do we have?

Recently I came across Edraw Office Viewer Component (EOVC)  and I was pretty much inspired by it. It not only allows us to embed the document but also gives us the experience of working in Excel directly. But here is the catch! It is not free. Considering the things which we can do with this control (in absence of a similar control in VS), I wouldn’t mind paying for it though.

Let’s test the Component. I would be testing this component in Excel 2010 and VS 2010

First download the 30 day trial version from this link.

Once you have installed it, open a new Windows Application and in the ToolBox add the Component. You can do that by Right Clicking on the ToolBox and Clicking on Choose items. Under the COM Components TAB, select the Edraw Office Viewer Component.

image

Click on OK. Your toolbox now looks like this.

image

Create a new form and place the EOVC on the form. Add couple of buttons so that your form now looks like this

image

Place this code in the form

Public Class Form1

    '~~> Create a New File
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        AxEDOffice1.CreateNew("Excel.Application")
    End Sub

    '~~> Load a File
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        AxEDOffice1.OpenFileDialog("Excel Files(*xls;*.xlsx)|*.xls;*.xlsx")
    End Sub

    '~~> Save File
    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        AxEDOffice1.Save()
    End Sub

    '~~> File Save As
    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        AxEDOffice1.SaveFileDialog()
    End Sub

    '~~> Closing the file
    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        AxEDOffice1.CloseDoc()
    End Sub

    '~~> Invoking the print dialog
    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        AxEDOffice1.PrintDialog()
    End Sub

    '~~> Quit
    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub

End Class
Here is an explanation of the commands used above.
AxEDOffice1.CreateNew("Excel.Application")
The above creates a blank new workbook for you
AxEDOffice1.OpenFileDialog()

The above presents am open file dialog so that you can choose your file. By default it shows you all Office extensions but if you want only Excel files then you can specify the respective filters as shown in the main code above. From what I tested, unfortunately it doesn’t support wildcards in the OpenFileDialog(). For example this works in VBA Excel.

Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
But the same filters in Edraw OVC will not give you error when you use it but will give you unexpected results in the DialogBox. So my suggestion is to specify the relevant extensions like I have mentioned above.
AxEDOffice1.Save()
The above code simply saves the file that you opened. To do a SaveAs use the code below.
AxEDOffice1.SaveFileDialog()
If you have a predefined path then you can also use
AxEDOffice1.SaveAs("C:\Sample.xlsx")
The best part is that the file that you load are “Read Only” i.e till the time you don’t specify a save command, the opened file is not saved. if the user tries to click on the “Save” button in Excel then Excel will inform the user that the file is in Read Only mode and to save it you need to do a “Save As”.
By default the Edraw OVC Loads the Excel toolbars as well. For example
image
If you want you can disable by simply switching it off in the Form Load Event
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    AxEDOffice1.Toolbars = False
End Sub

image

When the toolbar is visible, you have access to all the toolbar button. But if the Toolbar’s visibility is set to OFF and you want to give the user the ability to print then you can use the below command. BTW the user can still use the Excel’s Shortcuts. So pressing a CTRL P will invoke the Print button.
AxEDOffice1.PrintDialog()
You can also do a Print Preview using the below code
AxEDOffice1.PrintPreview()
To close the file without saving, simply use
AxEDOffice1.CloseDoc()
Now let’s go one step forward. Let’s try and work with Excel Ranges in the EOV Component. We will use the same sample file and create a small Report from it.
Add a new button on the form and Call it “Report”
image
In the Report button click event, paste this code. I have commented the code so you shouldn’t have any problem understanding it. What we will try and do is format our range and create a chart from it.
'~~> Create A Report
Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
    Dim oxlAp = AxEDOffice1.GetApplication()
    Dim oWbk As Excel.Workbook = AxEDOffice1.ActiveDocument()
    Dim oWs As Excel.Worksheet = oWbk.Sheets("Sheet1")

    With oWs
        '~~> Change the range into a tabular format
        .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("A1:E6"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

        '~~> Format the table
        .ListObjects("Table1").TableStyle = "TableStyleLight8"

        '~~> Format the Total and Average Expenses cells
        With .Range("A1:A6")
            .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
            With .Font
                .ColorIndex = 2 '<~~ Font Color White
                .Size = 8
                .Name = "Tahoma"
                .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                .Bold = True
            End With
        End With

        '~~> Autofitting text in columns
        .Columns("A:E").EntireColumn.AutoFit()

        '~~> Inserting a Graph
        .Shapes.AddChart.Select()
        oxlAp.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers)
        oxlAp.ActiveChart.SetSourceData(Source:=.Range("Sheet1!$A$1:$E$6"))
    End With
End Sub
Now load the sample file using the load button. You Form should look like this.
image
When you click on the Report button, you will notice that you data range has been formatted nicely and a chart has also been created.
image
To know more about Excel – VB.Net interaction, you can view this link. In fact you can try the example given there on the Edraw Office Viewer Component as well.
About these ads

12 responses to “Embed Excel Documents in VB.Net Application

  1. MRN July 22, 2012 at 11:44 pm

    Hi,

    Congrats fro MS MVP. You deserve it.

    I really need to embed an excel sheet in a VB.NET form. (All I need is an excel sheet must be editable in the form)

    There are two questions,

    a) If it is not too much trouble for you, can you give me a short example of DSOFramer?

    b) I tried web browser control, but to make it work I need to change IE settings every time the machine/PC changes is there any way to solve this issue?

    Thank you so much for your time.

    Regards,
    MRN

  2. MRN July 23, 2012 at 12:34 am

    Basically, I want the editing functionality. And then may be I can save it using vb.net. (Only thing is I do not want to change IE settings every time my machine/PC changes)

    • Siddharth Rout July 23, 2012 at 12:37 am

      If you want the editing functionality extended to the user then I am afraid you will have to go in for a 3rd Party control. Edraw is good but not the only one. :) If you find it expensive then there as lot more similar components available out there :)

      • MRN July 23, 2012 at 12:45 am

        is it possible to get excel workbook structure in data grid table? (with sheet structure of excel)
        and then export it to excel?

      • Siddharth Rout July 23, 2012 at 12:47 am

        Yes that is possible. But after importing the data into the data grid, you can only amend the values as the data will loose it’s formatting. Once you export it back to Excel, you can then format the excel range using Excel automation as mentioned in my VB.Net and Excel Tab.

  3. MRN July 23, 2012 at 12:52 am

    Just to make sure that we are on same page, My current workbook has 13 sheets so we can still preserve this sheet structure of excel in data grid table and and then export the new data as a excel file correct? If yes then do your have any reference or documentation to do this?

  4. MRN July 23, 2012 at 2:10 am

    Can’t thank you enough.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: