Embed Excel Documents in VB.Net Application
I have stopped updating this blog. This link can also be found in my Website.
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.
Click on OK. Your toolbox now looks like this.
Create a new form and place the EOVC on the form. Add couple of buttons so that your form now looks like this
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.
The above creates a blank new workbook for you
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.
The above code simply saves the file that you opened. To do a SaveAs use the code below.
If you have a predefined path then you can also use
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
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
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.
You can also do a Print Preview using the below code
To close the file without saving, simply use
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”
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.
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.
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.