Using Ado.net Datasets in Microsoft Office System (iv)

ado| data in an Office document using data from a dataset

Once the dataset is populated with the data you need and disconnected from the database, there are a number of ways you can use that data in various Office documents. Microsoft Office system essentially does not handle most. NET-based objects, so you typically have to use COM interoperability (COM interoperability) to maintain this data, and convert the data to the type of data you want to add to the worksheet or other document. You can use its newly integrated XML capabilities in Microsoft Office System to achieve this integrated application with. NET objects.

Inserting data into a worksheet

You may have used VBA, so you can use vb.net to automatically create a new worksheet using the techniques you use in VBA. Of course, if you are familiar with the technique of automatically creating worksheets from external applications, you can use exactly the same technology to automatically create a worksheet-for example, code written with Microsoft Visual Basic 6. Then, you need to insert the contents of the dataset into the worksheet.

A Windows Form can use the custom Buildworksheet method to open Microsoft Office Excel 2003, add a workbook, and create a worksheet with the appropriate data. Figure 2 shows this result. Unfortunately, there is no property in Excel You can assign a dataset reference to it--datagrid windows and Web Forms controls have this property. Therefore, you must write some code to accomplish this task.



Figure 2 Excel 2003 worksheet that has been populated with data from the dataset

Microsoft Office System publishes individual objects through COM objects. So Microsoft has released a set of primary interop assemblies (primary interop assembly, PIAs), which is specifically targeted. NET objects are optimized for accessing COM objects. You must install PIAs on each client computer, but in fact, they are installed when you install Microsoft Office System.

When you install Microsoft Office System, select the User customization option in the Setup Wizard, as shown in Figure 3 below, where you can select or clear those features. One of the "support." NET programming option refers to the installation of PIAs. You must automatically interoperate with each of your plans. NET-compatible application installation PIAs, and you can either install PIAs into the global assembly cache (GAC) or place PIAs in your project folder. You can find more detailed information from the following article: How To:install Office Primary Interop assemblies.



Figure 3 Using the wizard to install PIAs

When on the basis of. NET application, you need to add a reference to them in the Add Reference dialog box in Visual Studio. NET, as shown in Figure 4, in 2003 PIAs. Once you have added this reference, you can be like maintenance. NET assemblies to maintain Excel COM objects.



Figure 4 Adding a reference to Excel 2003 PIAs using the Add Reference dialog box

In this Adonet.vb Windows form, Buildworksheet is defined as a private process that has and has only one dataset parameter. Because it actually deals only with a DataTable object (which stores customer data), the code declares a DataTable variable named DT at the beginning to hold a reference to the table.

Private Sub Buildworksheet (ByVal ds as DataSet)

Dim DT as DataTable

In order to maintain an Excel workbook, you need to instantiate an Excel application object and then use the object model to add a new workbook and change the name of the workbook (the example uses Northwind Customers) while performing other UI tasks.

' Create An instance of Excel 2003, add a workbook,

' And let the user know what ' s happening

Dim XL as New Excel.Application

Xl. Workbooks.Add ()

Xl. Activesheet.name = "Northwind Customers"

Xl. Visible = True

Xl. Range ("A1"). Value = "Loading the DataSet ..."

Once you load and run Excel, the code needs to refer to the corresponding table (DataTable object) in the DataSet DS. It then traverses the column collection of the DataTable object and writes each field name to the first row of the worksheet. The example uses Customers table and customers column headings.

Try

Xl. ScreenUpdating = False



' Start with the Customers table

DT = ds. Tables ("Customers")



' ADD ' column headings for the Customers

Dim DC as DataColumn

Dim icols as Int32 = 0

For each DC in DT. Columns

Xl. Range ("A1"). Offset (0, Icols). Value = DC. ColumnName

Icols + 1

Next

All that remains is to traverse all rows of the table, using the ItemArray property of the DataRow object to write the contents of each data row to a row in the worksheet. This is about the most significant indication that Excel will directly support the DataSet object.

' Add ' data

Dim Irows as Int32

For irows = 0 to dt. Rows.count-1

Xl. Range ("A2"). Offset (irows). Resize (1, icols). Value = _

Dt. Rows (irows). ItemArray ()

Next

Catch ex as Exception

The rest of the code in this process updates the UI and formats the worksheet with built-in formatting.

Finally

Xl. ScreenUpdating = True

End Try



' Make the sheet pretty

With XL. ActiveSheet.Range ("A1")

. AutoFilter ()

. AutoFormat (Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple)

End With



XL = Nothing

End Sub

Although Microsoft Office system does not support. NET objects in nature, the object model and functionality of the dataset makes it easy to implement data interaction tasks by combining them into Microsoft Office System.

Note: You can safely use this technique without worrying about where the dataset comes from, and it can even come from. NET component or Web service.