Export Data to Excel Through OLE AutomationX

This article shows how to export data to excel using OLE automation. Although PowerBuilder provides a save as to Excel file format, this function has several limitations, such as including all columns even if the user does not want them and not including computed fields. This example is also much more user-friendly, because it automatically starts Excel for users, so they do not have to save the file to disk, load Excel, and then locate the file. It also makes the headers bold and auto sizes all of the columns. It can be expanded to do much more, and I will highlight a few ideas throughout the article.

The first thing to do is to create a test harness for the example, create a new application and accept PowerBuilder's invitation to create a sample application. Then create a test datawindow with some data in and place it on the generic sheet. Create a push button on the sheet ready for use later. Save the sheet and close the window painter.

Create a nonvisual object. This object will be used to store all the functions we will need and also allow you to expand the functions of the object in the future. My preference is to use autoinstantiate for this kind of object so set the autoinstantiate attribute by right clicking on the object. Save the object as nca_excel.

Before we can begin to write the main function that interfaces with Excel, we will need a few helper functions. First we will need a separate function to retrieve all of the names of the columns in the datawindow. I have moved this simple piece of code to a separate function so that it could be replaced in the future with a function that allows the user to select the columns or allows the developer to specify the columns for the datawindow. Create a function called GetColumns, which accepts two arguments, a datawindow and an unbound string array by reference, then add the following code:

// Get a list of all the columns in the DW
// Note this function could be replaced with
// a function that allows the developer to specify
// the column names to be exported.
Long ll_I, ll_Cols

ll_Cols = Long( adw_DW.Describe( 'datawindow.column.count' ) )
FOR ll_I = ll_cols TO 1 STEP -1
as_columns[ ll_I ] = adw_DW.Describe( '#' + String( ll_I ) + '.Name' )
NEXT

The next problem we need to tackle is getting the data from the datawindow. Again, I put this code in a separate function for a reason. This object could be expanded to handle datasources other than a datawindow. This function returns an any and accepts the datawindow, a long for the row and the column name:

Long ll_Col
Any la_A

IF al_Row > adw_DW.RowCount() THEN RETURN ""
ll_Col = Long( adw_DW.Describe( as_Column + ".ID" ) )
IF ll_Col > 0 THEN &
la_A = adw_DW.object.data.primary.current[ al_Row, ll_Col ]

RETURN la_A

The last helper function we need is more related to Excel. As you know, spreedsheets reference data by rows and columns, but Excel uses letters to reference the columns. Thus, a nice function to convert numbers into Excel column references will help us out when we want to start using some of the more advanced features of Excel such as column autosizing. Create a function called inttocolumn that accepts an integer and returns a string and add the following code:

// Convert a column number into a spreadsheet column reference

String ls_Col
Integer li_Min, li_Max

IF ai_col <= 0 THEN RETURN ""

// calc the major/minor letters
li_Max = ai_col / 26
li_Min = ai_col - ( li_Max * 26 )

// Convert min and max to letters
IF li_Max > 0 THEN
ls_Col = Char( 64 + li_Max )
END IF

ls_Col += String( Char( 64 + li_Min ) )

RETURN ls_Col

Now we are ready to write the main export function. In PowerBuilder we use the OLEObject Object to control other OLE applications. We will create one of these objects and then attach the object to Excel. Then we can access Excel just like we can using the VBA internal scripting language in Excel. In fact, a great way to figure out how to make calls in Excel is to use the Macro recording feature, record what you want to do in Excel, and then examine the macro produced. You can then easily convert the macro to Powerscript.

Back to our example, we want to take the data from a datawindow and load it into Excel, but we also want to include the column headings, bold the headings, and autosize all the columns to a best fit. Add the following code to the function:

// Export the data to Excel
OleObject lole_OLE, lole_Sheet
String ls_Columns[]
Long ll_Row, ll_Col, ll_Cols

lole_OLE = CREATE OleObject

SetPointer( HourGlass! )

lole_OLE.ConnectToNewObject( 'excel.application' )
lole_OLE.Workbooks.Add
lole_sheet = lole_OLE.Application.ActiveWorkbook.WorkSheets[1]

this.GetColumns( adw_DW, ls_Columns )
ll_Cols = UpperBound( ls_Columns )
FOR ll_col = 1 TO ll_cols
lole_Sheet.Cells[ 1, ll_Col ] = ls_Columns[ ll_Col ]
NEXT

FOR ll_Row = 2 TO al_rows + 1
FOR ll_Col = 1 TO ll_cols
lole_Sheet.Cells[ ll_Row, ll_Col ] = &
this.GetData( adw_DW, ll_Row - 1, ls_Columns[ ll_Col ] )
NEXT
NEXT

lole_Sheet.Range( inttocolumn( 1 ) + "1:" + inttocolumn( ll_Cols ) + "1").Select
lole_OLE.Selection.Font.Bold = True

lole_Sheet.Range("A1:A1").Select
lole_Sheet.Columns( inttocolumn( 1 ) + ":" + inttocolumn( ll_cols ) ).EntireColumn.AutoFit

lole_OLE.Application.Visible = TRUE
lole_OLE.DisconnectObject()
DESTROY lole_OLE

One thing you will notice is the use of two OLEObjects: one to hold the Excel application and another to dereference the Excel ActiveSheet. We could have just used a single OLEObject and used statements such as:

lole_OLE.Application.ActiveWorkbook.WorkSheets[1].Cells[ ll_Row, ll_Col ]

instead of

lole_Sheet.Cells[ ll_Row, ll_Col ]

However, every time you make a dot reference to another object, the operating system has to do more work. By making the dereference once to the Sheet object, you can speed up a lot of operations and make the whole thing faster.

Last, save the object and go back to the sheet we prepared earlier. Go to the script for the command button and add the following code:

nca_excel lnca_Excel

lnca_Excel.ExportToExcel( dw_1, dw_1.RowCount() )

Now run the application and click the button to export your data to Excel.