In order to get us started, answer the following question:  in Word 2003, how many commands do you need to use to insert a watermark (in case you don’t know it, a watermark is a transparent background text which appears in all pages – for instance, “confidential”, “draft” etc.)? The answer is seven commands, or menu options. In word 2007 only two menus will be enough.

Another classical question: When you have a text you’d like to format (changing font style, text color and size, for instance), in Word 2003 you usually select the text and the desired format using the format bar. This is undoubtedly the best and the fastest way to do it, and surely you must have had the same idea. There is a “but”, though: in order to see the final result you need to actually select the new format in the format bar. If you don’t like it, simply use a new format, and in case you don’t like it for the second time, just click once more on the font style, for instance, until you get a result you’re satisfied with. However, Word 2007 is much simpler, because you select the text and the format. The huge difference is that as you go through the font styles with the mouse or the keyboard, you can already have a glimpse, in real time, of what your text will look like with that particular format. This spares us a huge amount of time and allows us to test many kinds of fonts with a simple click. Finally, when you find a font which satisfies your needs, you just have to click on that font’s name to accept the formatting. The good news is that this process of real-time selection and visualization is part of every application in the Office suite 2007 (Word, Excel, PowerPoint etc). Check an example on Figure 1.

Figure 1 – Text formatting in Word 2007

Still on formatting: what about selecting a text and being able to format it quickly through a Smart Tag, without having to resource to the toolbar? This is exactly what you can see in Figure 2.

Figure 2 - Formatting Smart Tag

As for menus, now we have in every Office 2007 application what we call a context-sensitive menu – that is, a menu which adapts to the user’s situation and enables the execution of context-related tasks. If you have a table, for instance, the menu shown will be related to operations involving tables; if you select a figure or any other object, the menu will adapt to the new context.

And since we’re talking about menus, check out in Figure 3 the all-new menu style: everything is new, from design to context. The objective here is to have all resources in the palm of your hand, just a mouse-click away. This new menu style is called a Ribbon. In case you want to show/hide the Ribbon, just double-click on the menu option.

Figure 3 – Redesigned menus

Do you see the shape and style applied to the image in Figure 4? If you think I’ve copied the image to an image-treating software and applied a style, you’re totally mistaken. All I’ve done is select the image, open the style list and select a style, as you can see in Figure 4. And all that with a single click. As I regularly write articles, books and presentations, this was one of the resources I liked the most, because you have a library with many styles just a click away from you.

Figure 4 – Image style

Have you ever needed to generate a PDF file from, let’s say, a DOC, PPT or XLS file? Certainly you must have used a PDF-generating application. However, the PDF format is native to Office 2007. In other words, you can just save the file as PDF. Check out this menu option in Figure 5.

Figure 5 – Save as PDF

This English version has been translated from Portuguese into English by Guilherme Braga (guizomail@gmail.com)

Technologies used
- .NET
- MS-Excel 2003
- VSTO

MS-Excel has been, without a doubt, the best spreadsheet in the market for years. All the available resources within Excel are key in areas such as controlling, finance, accounting, logistics, etc. The integration of Excel with a database has become a functionality which is crucial to manipulate data in a worksheet (Front-End) where the data source can be SQL Server, Access or any other data source available. 

With .NET, the developers’ market has entered a new world of developing languages and developing tools (VS.NET 2003). For those who use Excel, programming in VBA (Visual Basic for Application) will continue to be a valid choice.

If we put the development power of .NET together with Excel what we get is VSTO (Visual Studio Tools for the Microsoft Office System) which is a tool that you need to install separately in VS.NET 2003 (currently on version 1.0)

Nevertheless, in this article I will show you a bit of what can be done with Excel and VSTO 2.0. Version 2.0 can only be installed using VS.NET 2005 and it is worth noting that these are beta versions, at least up to when this article was last updated (Sept/2005). If you have worked with VSTO 1.0, believe me, version 2.0 is way better and easier to develop with.

To get started, open VS.NET 2005 and create a new project with the following characteristics:

Project Types: Visual C# / Office
Templates: Excel Application
Name: ExcelMSDN
Location: C:\MSDN
Solution Name: ExcelMSDN

Refer to pictures below:

 
Picture 1: New Project window

Choose the solution as shown in the picture above. Next, click on OK to define the document name. We can either define a new document or copy an existing document. For this article, we will create a new document:

 
Picture 2: Select a document window

Save your application as follows:

 
Picture 3: Save Project window

Click on the Save button to save your new solution. You should note that the UI (User Interface) is actually Excel’s UI (which includes toolbars, sheets, etc) within VS.NET 2005:

 
Picture 4: VSTO 2.0 with Excel's UI

Now, under the Solution Explorer you should add a UserControl to the project as shown in the picture below:

 
Picture 5: Add New Item window

You now also add the following controls to the project-level control you’ve just added:

Picture 6: Design view of UserControl1

In the Toolbox, drag a bindingSource control to the document and configure the DataSource Property accordingly. At this stage there is no data source; thus, it is necessary that we add one to the project. Click on the Add Project Data Source link and configure the data source. I use the Northwind database as the data source for the project; therefore, you should use the same database and choose the tables Categories and Products.

See picture below:

Picture 7: Adding a data source to the Project

In same instances it is possible to define the Master Details. Go back to the worksheet and drag the controls from the Toolbox onto the active sheet (Sheet1) as shown in the next picture. Note that the controls are embedded in the worksheet (look at the formula bar). Now, you should configure the properties of the controls according to the information in the bindingSource control.

Remember that you do not need to write any code to bind the data to the controls. All you need to do is to configure the controls’ properties.
 

 
Picture 8: Adding controls to Sheet1

We are now ready to add a few lines of code to our project. Change ThisWorkbook view to code mode. By adding a few lines of code we can show the UserControl1 in Excel’s Task Pane (referred to within VS.NET as ActionsPane):

Finally, save your project and press (F5) to execute it. Note that the UserControl1 is shown in the Task Pane (ActionsPane) with the data from the Northwind database. On the worksheet there is also a GridView control that shows the same data shown in the Task Pane. The same goes for the listboxes on the worksheet and Task Pane.

Another new feature is the WebControl which allows you to browse the web within and Excel worksheet:

1. Development tools – normally an ASP developer will use such tools as Visual InterDev, Dreamweaver, Ultradev, Homesite, Adobe Go Live and, why not, the good-old Notepad (that has never crashed!!!), EditPad, in sum, the tool you know best. However, you will need to know at least HTML e VBScript to get by. In some cases, JavaScript is used, Perl, DHTML, among other tools. Now, with ASP.NET you can use VS.NET (which you can find in three versions Professional, Developer and Architect) or for those who’d rather not put their hands in their pockets, they’d use Web Matrix. Notice that in a single tool you have at your disposal all the tools you need to develop all the topics I discuss in this article

2. Script x Compiled – with VBScript, the HTML and JavaScript codes are called scripts. When a page is called on the server, the ASP library reads each line step by step, interprets the code, build the virtual HTML and send the information to the browser which requested the information. On ASP.NET the pages are compiled onto DLLs and thus, no matter how simple the page is, it will be around 25% faster than normal ASP. Now, imagine you have a large application where the processing of simultaneous scripts requires performance? You can kiss goodbye to ASP. As far as language is concerned, you can choose any of the native languages supported by the DotNet Framework. On VS.NET you can choose between VB.NET, C# and C++ with the default installation option.

3. Variables – In VBScript the explicit declaration of variables is not mandatory; in ASP.NET all variables must be declared. In VBScript if you declare a variable, say, Dim Test, there is not data type for this variable and it is, thus, treated as a Variant, in other words, it takes any value. In ASP.NET all variable must be declared according to its data type (Dim name As String). This is critical for code execution, allowing us to know exactly what data type it is and, when necessary, we can convert the data type by using one of the data type conversion functions.

4. Development models (splitting design and code): this is one of the simplest things for those entering this new environment. In ASP you must insert the VBScript inside the existing HTML, which cut up the pages making it harder to maintain them. Who never needed to change an ASP page layout? In ASP.NET this torture has ended. Now, there are two separate files, one for the layout another one for the code, where a line links both files. This is the so-called Code Behind. Thus, design can be changed without any problems. In parallel the programmer can develop the code. It is important to point out that it is also possible to develop inserting the code directly onto the design page.

5. New Controls: ASP.NET comes with new controls that make it easier to create web pages, mainly when it is necessary to publish database contents. Some of the controls are: DataList, DataGrid and Repeater. In ASP you normally build tables by merging HTML code with VBScript, you then build a loop that reads from the Recordset. If you use the DataGrid, for example, you can do all this with only two lines of code. All you have to do is to define the DataSource property and then use the DataBind method. It is really amazing how clean ASP.NET pages are, since the amount of code needed is drastically reduced. Data validation has never been easier and with ASP.NET you can use the Validator control and configure it to suit your needs. Another control that will help web-developers is the Calendar control, which can be configured to receive one or more set of dates.

6. Debug: To begin with, you’re already familiar with the two commands: Response.Write e Response.End. Believe it or not, this is the best way to debug errors on ASP. With VS.NET we are in another world where you can set breakpoints, execute the page on a browser and when the point is located, VS.NET shows the code forcing a momentary pause in the execution. From this point onwards you can check the contents of your variables and accompany, step by step, the execution of your code. It is worth pointing out that it is often good to run through the code simply to ensure it is executing well.

7. Treating Errors: The only way to treat errors in ASP is through the instruction On Error Resume Next. This does not allow you to identify the error type. In ASP.NET this has been totally restructured with Try-Catch-Finally, where it is possible to identify any error and even customize the error messages.

8. Components: “componentization” is really an excellent tactic for developing web pages, which allows breaking up the application into layers (1, 2 or 3). The advantages are enormous when it comes to security, portability, maintenance, code reuse, etc. Normally ASP pages consume components created on Visual Basic of ActiveX. The question here is not the advantages of layered development, but the use of components. In ASP.NET using components created for the .NET framework is extremely simple, all you have to do is to create the references and consume them on the pages. It is important to point out that the components developed for the .NET framework do not require the component to be registered, or rather; there is no registry for the component. It is simply achieved by copying (XCopy) of the DLL onto the destination folder. ASP.NET pages will also consume components created on Visual Basis or any other ActiveX. However, this will be executed on an environment which is not managed by the . Framework, that is, any error generated at the component level, the operating system is responsible for that, while in a .Framework managed environment, any error is isolated at the operating system.

9. External components: Some well-known components such as Upload, e-mail component, ad-Rotator and Calendar are native to ASP.NET. Thus, you do not need to purchase any additional components. For example, you can Upload a file with a few lines of code.

10. ADO x ADO.NET: Here there have been major changes. In ASP the only concept available is the recordset. Under ASP.NET the recordset no longer exists and it has been superseded by the DataSet, which is also raised in the memory. However, you can build a DataTable, DataView, DataReader and DataAdapter. Notice that for each scenario you will use a specific method. The data access can be done through such drivers as OleDB, SqlClient, Oracle or ODBC. If you use SQL Server 7 or 2000, you should use the SqlClient which has been created specifically for this task. All the structure generated by ADO.NET is XML based, but this is very transparent to the developer.

Thus, with so many new additions, what is the best way to harness the necessary know-how at your organization? How to get your team up and running? I have always been in favor of customized training in order to take advantage of the experiences and know-how of developers already within organizations. By doing so, the learning curve is flat and not steep in such migrations. Some people would prefer to get to grips with these things through books and dedicated literatures; but no matter how good you are at self-teaching the time required will be lengthier. On the other hand, technical seminars and multimedia training kits are always welcome.

The most important thing right now is that a new era has dawned and you are right in the middle of it with no turning back. I’ve been there and I certainly do not look back. I have no doubt you will not regret when you take this next step.

No stress. Think .NET