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.
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
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*")
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load AxEDOffice1.Toolbars = False End Sub
'~~> 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