working with offline sql server data in excel

7/27/2019 Working With Offline SQL Server Data in Excel

1/11

Walkthrough: Working with OfflineSQL Server Data in ExcelBrian A. RandellMCW Technologies, LLC

Applies to:

Microsoft Visual Studio Tools for the Microsoft Office System

Summary: Excel makes it easy to work rectangular data such as data from an externaldatabase. In this walkthrough, youll load data from SQL Server into an ADO.NET

Dataset. Youll then use Excels object model to support synchronizing changes made tothe data in Excel with SQL Server. (11 printed pages)

Contents Getting Started Hook Up the Event Handlers Add Code to Import Data Synchronize Changes with the Server Run Code Before Closing the Workbook

Introduction

Youll download data from the SQL Server Northwind sample database into a local

DataSet object, releasing your connection to the server. Youll then load the data from thedataset into a sheet within an Excel workbook as shown in Figure 1. Once the data is

loaded, youll make some changes and send the results back to SQL Server. Once youredone, the example will display a message box informing you of the number of records

updated.


2/11

Figure 1. Excel with data loaded from SQL Server

Prerequisites

To follow this walkthrough, the following software and components must be installed on

the development computer:

Microsoft Visual Studio .NET 2003 or Microsoft Visual Basic .NET Standard 2003 Microsoft Visual Studio Tools for the Microsoft Office System Microsoft Office Professional 2003 SQL Server or MSDE (7.0 or 2000), with the Northwind sample database installed.

This demonstration assumes that youve set up SQL Server/MSDE allowing access

using integrated security.

TIP: Although its not required, this demonstration assumes that youve set the Option Strict setting

in your project to On (or have added the Option Strict statement to each module in your

project.) Setting the Option Strict setting to On requires a bit more code, as youll see, but italso ensures that you dont perform any unsafe type conversions. You can get by without it, but

in the long run, the discipline required by taking advantage of this option will far outweigh the

difficulties it adds as you write code.


3/11

Getting Started

In order to get started, youll need to begin by creating a new Visual Studio .NET project

that works with Excel.

Create the Project

1. Start Visual Studio .NET, and select File, New, Project.2. In the Project Types pane, expandMicrosoftOffice 2003 Projects, and then

select Visual Basic Projects.

3. In the Templates pane, select Excel Workbook.4. Name the project ExcelSQLServerData, and store it in a convenient local path.5. Accept the defaults on the Office Project Wizard dialog box, and clickFinish to

create the project. Visual Studio .NET opens the ThisWorkbook.vb file in the codeeditor for you.

Lay Out the Spreadsheet

In order to load the data and synchronize changes, youll need to add some way to start the

code running. For this demonstration, youll create two hyperlinks within the workbook,and react to the SheetFollowHyperlink event of the Workbook object to run your code.

1. Press F5 to run the project, loading Excel and your new workbook.2.

Rename Sheet1 to Products. If youd like, remove Sheet2 and Sheet3.

3. Within Excel, put the cursor in cell G1, and select Insert, Hyperlink.4. In the Insert Hyperlink dialog box, in the Link to: pane, on the left side of the

dialog box, select Place in This Document.

5. Set the Text to display value to Load Data.6. Make sure the cell reference in the dialog box matches the location of your

hyperlink. When youre done, the dialog box should look like Figure 2. Click OK

to dismiss the dialog boxyou should see the new hyperlink within the workbook.


4/11

Figure 2. The finished Insert HyperLink dialog box

7. Repeat the process, putting the cursor in cell G2, and put Update Data in the Textto display field.

8. Select File, Save to save your changes. The workbook should look something likeFigure 3.

Figure 3. Your Excel workbook ready to go.

Hook Up the Event Handlers

In order to start your code running, youll need to react to the

Workbook.SheetFollowHyperlink event. In this section, youll add support for reacting tothis event. Follow these steps to hook up the event handler:


5/11

1. From the Class Name dropdown list in the upper-left corner of the code editorwindow, select ThisWorkbook.

2. From the Method Name dropdown list in the upper-right corner of the code editorwindow, select SheetFollowHyperlink. Visual Studio .NET creates the eventhandler stub for you.

3. Add the following procedure stubs to the current class. Youll fill in the detailslater:

Pr i vat e Sub LoadData( )

End Sub

Pr i vate Sub UpdateDat a( )

End Sub

4. Modify the ThisWorkbook_SheetFollowHyperlink procedure, adding the followingcode:

Tr y

Sel ect Case Tar get . Name

Case " Load Dat a"

LoadDat a( )

Case " Updat e Dat a"

Updat eDat a( )

End Sel ect

Catch ex As Except i on

MessageBox. Show( ex. Message, ex. Source, _

MessageBoxBut t ons. OK, MessageBoxI con. Err or )

End Try

Add Code to Import Data

Next, youll need to add the code that creates a DataSet, loading the data from SQL Server,and add code to copy that data into the Products sheet within the Excel workbook.

1. Scroll to the top of the code module, and add the following statement, which willreduce the amount of typing required to refer to the objects and members youll

reference:

I mpor t s System. Dat a. Sql Cl i ent

2. Add the following declarations, immediately beneath the existing declarations forthe ThisApplication and ThisWorkbook variables:

Pr i vate Di sabl eWorkSheet Changes As Bool ean = Fal se

Pr i vat e mda As Sql Dat aAdapter


6/11

Pr i vat e mds As Dat aSet

Pr i vate mdt As Syst em. Dat a. Dat aTabl e

Pr i vat e xl Sheet As Excel . Worksheet

Pr i vat e r ngUC As Excel . Range

Pr i vat e r ngData As Excel . Range

WARNING! Excel provides a DataTable object, as does ADO.NET. Its

important to distinguish between the two, and the code uses an

explicit namespace reference to avoid the ambiguity.

3. Add the following procedure to the OfficeCodeBehind class. This procedureconnects to SQL Server on the local computer, using integrated security, and fills a

DataSet:

Pr i vat e Sub GetDataSet( )

Di m cnn As Sql Connect i on

Tr y

I f mds I s Nothi ng Then

mds = New Dat aSet

El se

mds. Tabl es. Remove( mdt )

End I f

cnn = New Sql Connect i on( _

"Ser ver =' . ' ; " & _"Dat abase=Nor t hwi nd; " & _

"I nt egr at ed Secur i t y=t r ue")

Di m st r SQL As St r i ng = "SELECT " & _

"Product I d AS [ I d] , ProductName AS [Name] , " & _

"Uni t sI nSt ock AS [ On Hand] , " & _

"Uni t sOnOr der AS [ On Or der ] , " & _

"Reor derLevel AS [ Reor der Level ] " & _

"FROM Pr oduct s WHERE Di scont i nued = 0 " & _

"ORDER BY Uni t sI nSt ock"

Di m cmd As New Sql Command(st r SQL, cnn)

mda = New Sql Dat aAdapt er ( cmd)

mda. Fi l l ( mds)

mdt = mds. Tabl es( 0)


7/11

Di m cb As New Sql CommandBui l der ( mda)

mda. Updat eCommand = cb. Get Updat eCommand( )

Cat ch ex As Except i on


MessageBoxButt ons. OK, MessageBoxI con. Err or )End Try

End Sub

4. Add the following procedure to the class. This procedure creates the columnheadings in the worksheet using the field names from the Columns collection of the

DataTable contained within the new DataSet, and provides basic formatting:

Pr i vate Sub Set upWor ksheet ( )

Tr y

Di sabl eWor kSheet Changes = Tr ue

xl Sheet = Di r ect Cast ( _Thi sWor kbook. Wor ksheet s( "Pr oduct s" ) , Excel . Wor ksheet )

r ngUC = Di r ect Cast ( xl Sheet . Cel l s( 2, 1) , Excel . Range)

r ngUC. Cur r ent Regi on. Cl ear ( )

Di m i As I nt eger = 0

Di m col As DataCol umn

Di m r ng As Excel . Range

For Each col I n mdt . Col umns

I += 1

r ng = Di r ect Cast ( xl Sheet . Cel l s( 1, i ) , Excel . Range)r ng. Val ue = col . Col umnName

r ng. Font . Bol d = True

Next

Fi nal l y

Di sabl eWor kSheet Changes = Fal se

End Try

End Sub

5. Add the following procedure, which takes the data from the DataTable in thepreviously loaded DataSet and puts it into the Products worksheet:

Pri vat e Sub Put Dat aI nXL( )


Di m i As I nt eger = 0

Di m j As I nt eger = 0

Di m dr As Dat aRow


8/11

Tr y

Thi sAppl i cat i on. Scr eenUpdat i ng = Fal se

I f Not mdt I s Not hi ng Then

For i = 0 To mdt . Rows. Count - 1

dr = mdt . Rows( i )For j = 0 To mdt . Col umns. Count - 1

r ngUC. Of f set ( i , j ) . Val ue = dr ( j ) . ToSt r i ng( )

Next j

Next

r ngData = r ngUC. Cur r ent Regi on

End I f

Fi nal l y

Thi sAppl i cat i on. Scr eenUpdat i ng = Tr ue


End TryEnd Sub

6. Add the following procedure, which applies some formatting to the newly loadeddata in Excel:

Pr i vate Sub FormatCol umns( )

Tr y


r ngDat a. Col umns. NumberFor mat = "0"

r ngDat a. Col umns. Aut oFi t ( )

Fi nal l y


End Try

End Sub

7. Add the following code to the LoadData procedure created earlier:GetDat aSet ( )

Set upWor ksheet( )

Put DataI nXL( )

For mat Col umns( )

8. Select File, Save All to save the entire solution.9. Press F5 to run the project, loading Excel and your workbook.10.Within Excel, click the Load Data link you added previously, and verify that your

code has imported and formatted the data, as shown in Figure 1.


9/11

11.Close the Excel and the workbook, saving changes if you desire, and return toVisual Studio .NET.

Synchronize Changes with the Server

You need to add some code to trap the Workbooks SheetChange event and put anychanges made to the loaded data into the correct columns in the DataTable objects Rows

collection. Then, youll add code to actually send the changes to SQL Server when youclick the Update Data hyperlink.

1. From the Class Name dropdown list in the upper-left corner of the code editorwindow, select ThisWorkbook.

2. From the Method Name dropdown list in the upper-right corner of the code editorwindow, select ThisWorkbook_SheetChange. Visual Studio .NET creates the

event handler stub for you.

3. Modify the ThisWorkbook_SheetChange procedure, so that it looks like thefollowing:

Pr i vate Sub Thi sWorkbook_Sheet Change( _

ByVal Sh As Obj ect , ByVal Tar get As Excel . Range) _

Handl es Thi sWor kbook. Sheet Change

I f Not Di sabl eWorkSheet Changes Then

I f Not Thi sAppl i cat i on. _

I nt er sect ( r ngDat a, Tar get ) I s Not hi ng Then

' Subt r act 1 because Excel i s 1- based,

' t hen subt r act one row f or t he header .

' Don' t handl e changes t o t he

' header r ow, however .

I f Tar get . Row > 1 Then

Di m i nt Row As I nt eger = Tar get . Row - 2

Di m i nt Col As I nt eger = Tar get . Col umn - 1

mdt . Rows( i nt Row) ( i nt Col ) = Tar get . Val ue

End I f

End I f

End I f

End Sub

4. Add the following code to the existing UpdateData procedure stub, sending theDataTables modified rows to SQL Server:

Tr y

I f mds. HasChanges Then

Di m i ntRV As I nteger = mda. Update(mdt)

I f i nt RV > 0 Then


10/11

MessageBox. Show( Str i ng. Format ( _

"{0} r ecords wer e updated successf ul l y. " , _

i nt RV. ToSt r i ng( ) ) , _

"Success" , MessageBoxButt ons. OK, _

MessageBoxI con. I nf ormat i on)

End I fEnd I f

Catch ex As Except i on


MessageBoxBut t ons. OK, MessageBoxI con. Err or )

End Try

5. Save your project, then press F5 to run it.6. Within Excel, click the Load Data link, and verify that youve successfully loaded

the data.

7. Modify any of the loaded data and click the Update Data hyperlink to send yourchanges back to SQL Server.

8. Close Excel, but this time dont save the changes made to the workbook. Onceback in Visual Studio .NET, press F5 again to start debugging again.

9. Click the Load Data link again, and verify that that your submitted changes showup.

10.Close Excel (saving the workbook, if you like), returning to Visual Studio .NET.Run Code Before Closing the Workbook

To complete the walkthrough, you need to add code to the Workbooks BeforeClose event.This event will check to see if there are any changes that have not yet been submitted to the

server. If so, the code displays an alert asking if changes should be submitted.

1. Add the following code to the ThisWorkbook_BeforeClose procedure:I f mds. HasChanges Then

I f MessageBox. Show( _

"There ar e changes t hat need t o submi t t ed. " & _

"Do you want t o send t he changes i n now?" , _

"Quest i on" , MessageBoxBut t ons. YesNo, _

MessageBoxI con. Quest i on) = Di al ogResul t . Yes Then

Updat eDat a( )

End I f

End I f

2. Save your project, then press F5 to run it.


11/11

3. Within Excel, click the Load Data link.4. Modify any of the loaded data and then attempt to close the workbook. You will

see an alert indicating that you have changes to be saved. Click Yes to save your

changes.

5. When Excel prompts you to save the workbook, select No.6. Once back in Visual Studio .NET, press F5 again to start debugging again.7. Click the Load Data link again, and verify that that your changes show up.8. Close Excel (saving the workbook, if you like), returning to Visual Studio .NET.

working with offline sql server data in excel

Documents