Monthly Archives: July 2012

Biggest multipurpose FREE Excel Add-in


EDIT

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

http://www.siddharthrout.com/free-add-in/

 

I am planning to create a FREE VSTO Excel Add-In which can be used by every one. I would request you to leave your requests here.

I intend to make this the biggest multipurpose FREE Add-In for everyone so be sure you leave your request Winking smile

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.

Microsoft MVP : Re-Awarded


 

EDIT

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

http://www.siddharthrout.com/2012/07/01/microsoft-mvp-re-awarded/

 

image

Today when I woke up, I was greeted with an unexpected email. I say unexpected because I was expecting the email on the 2nd of July (1st being a Sunday).

Dear Siddharth Rout,
Congratulations! We are pleased to present you with the 2012 Microsoft® MVP Award! ……

I am sure you can imagine how happy I was! This is the second time in succession I’ve been given this award.

I would like to convey my heartiest thanks to the following

1. Microsoft for recognizing and appreciating my contributions

2. Tanmay Kapoor (my MVP Lead)

3. The Visual Basic Team

4. Anyone else who has contributed in getting me this MVP awarded again.

I hope my contribution to the community not only continues but also exceeds last years benchmarks Smile