SoftArtisans ExcelWriter Guide

Importing Data from a Database

One-step Database Import: Advantages

Note: This section includes examples that use Cells.CopyFromRecordSet and Cells.GetDataFromRecordset to import database values to an Excel spreadsheet. You can also import from a database using ExcelTemplate.

Database import with ExcelWriter is fast and flexible for the following reasons:

ADO RecordSets can be imported in a single step.
Cells can be formatted before or after import.
Formulas can be added before or after import.
The imported RecordSet can be placed in any location of the spreadsheet.
The maximum limit of columns and rows can be easily set.
Multiple RecordSets can be imported into the same spreadsheet at different locations.
Import by CopyFromRecordSet, GetDataFromRecordset, or ExcelTemplate is much faster than moving the values in script or Visual Basic.

Top

Example: Import Data with Cells.CopyFromRecordset

This example shows some of these powerful capabilities. It is based on the familiar Microsoft NorthWind Trader's database. Approximately 20,000 cells will be imported from an Access database and formatted in a few seconds on a reasonably fast PC.

tutorial/database.asp

[View Source]

The first step is quite simple. A standard ADO RecordSet is created based upon a query to the database.

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DBQ=" & filePath &_
	 ";Driver=   {MicrosoftAccessDriver(*.mdb)};DriverId=25;FIL=MSAccess;"
Set oRs = oConn.Execute ("select * from qryOrders" )

An instance of ExcelWriter is created. Database import is performed using the Cells object.

Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set cells = xlw.Worksheets(1).Cells

After setting up formats, the FirstRow and FirstCol are set. An ADO RecordSet can be considered a rectangular block of data with rows and columns. ExcelWriter can import the RecordSet into any rectangular area on the WorkSheet. In this case, the RecordSet should be placed at Cell (3,2) which is cell B3.

FirstRow = 3
FirstCol = 2

The import is performed using the CopyFromRecordSet method. CopyFromRecordSet returns the total number of rows imported into the WorkSheet and fills in the headers automatically.

NumRows = cells.CopyFromRecordset(oRs, True, FirstRow, FirstCol)

The CopyFromRecordSet method has the following parameters:

NumRows = cells.CopyFromRecordset(ADO-RecordSet, [ShowHeaders (True/False)],_
		[FirstRow], [FirstColumn], [MaxRows], [MaxColumns],_ 
		[FieldList], [FieldListInclude (saxlsExclude/saxlsInclude)])

ShowHeaders is true by default, meaning that the first row will contain the column headers retrieved from ADO. FirstRow and FirstColumn have a default value of 1. So, by default the RecordSet will be imported into cell A1. MaxRows has a default value of 65,536 for BIFF8, and 16,384 for BIFF7. MaxColumns has a default value of 256. These limits are imposed by Microsoft's BIFF8 and BIFF7 format and are not a limitation of ExcelWriter. The FieldList parameter is an empty string by default, so FieldListInclude is then saxlsExclude by default.

By default, the CopyFromRecordSet method imports all columns in the recordset. To exclude unnessecary columns from being imported, use the FieldList parameter. For example the following will include only columns 1, 2, and 3 of the recordset.

NumRows = Cells.CopyFromRecordset(oRs, True, 1, 1, 100, 100, "1,2,3", saxlsInclude)

Alternatively, the following will exclude columns 4 and 5.

NumRows = Cells.CopyFromRecordset(oRs, True, 1, 1, 100, 100, "4,5", saxlsExclude)

After import, Styles are applied to the newly imported cells.

for i = (FirstRow + 1) to (FirstRow + NumRows)
	cells(i, FirstCol    ).Style = NumStyle			' OrderID
	cells(i, FirstCol + 1).Style = TxtStyle			' CustomerID
	cells(i, FirstCol + 2).Style = NumStyle			' EmployeeID
	cells(i, FirstCol + 3).Style = DateStyle			' OrderDate
	cells(i, FirstCol + 4).Style = DateStyle			' RequiredDate
	cells(i, FirstCol + 5).Style = DateStyle			' Shipped Date
	cells(i, FirstCol + 6).Style = NumStyle			' Shipped Via
	cells(i, FirstCol + 7).Style = CurrencyStyle			' Freight
	cells(i, FirstCol + 8).Style = TxtStyle			' Ship Name
	cells(i, FirstCol + 9).Style = TxtStyle			' Ship Address
	cells(i, FirstCol +10).Style = TxtStyle			' Ship City
	cells(i, FirstCol +11).Style = TxtStyle			' Ship Region
	cells(i, FirstCol +12).Style = TxtStyle			' Ship PostalCode
	cells(i, FirstCol +13).Style = TxtStyle			' Ship Country
	cells(i, FirstCol +14).Style = TxtStyle			' Ship Company Name
	cells(i, FirstCol +15).Style = TxtStyle			' Address
	cells(i, FirstCol +16).Style = TxtStyle			' City
	cells(i, FirstCol +17).Style = TxtStyle			' Region
	cells(i, FirstCol +18).Style = TxtStyle			' Postal Code
	cells(i, FirstCol +19).Style = TxtStyle			' Country
next

Top

Example 2: Import Data with Cells.GetDataFromRecordset

The following sample demonstrates how to import data from a large recordset using Cells.GetDataFromRecordset and split the recordset rows between multiple worksheets in a workbook.

features/SplitRecSet.asp

[View Source]

Top

Section Summary

Import from a database is easy using a combination of ADO and ExcelWriter's Cells.CopyFromRecordSet method.
CopyFromRecordSet takes the ADO RecordSet as an input parameter. Optionally, columns headers, the starting row, starting column, maximum number of rows, maximum number of columns, as well as inlcuding or excluding columns can be specified.
In this example, after import the data is formatted.

Top