How to create a dialog web component that will Export Records to Excel
Through the growing popularity of web pages based on Database Applications, regular maintenance of the database must still be performed. Although you can log on to the server and do maintenance operations, wouldn't it be nice just to go to a web page and have the operations run with a push of a few buttons?
First of all let me start by saying “I thoroughly enjoy Dialog Components” There is so much power in using Dialog Components.
Second, the basic rule for utilizing desktop operations on the web is, “If you can copy and paste, you can run an operation on the web”.
Another bit of information. The web does not understand table.current(). So you will need to replace this code with table.open(“[PathAlias.ADB_Path]\table_name”).
If you can copy and paste you can pretty much come close to creating an export of records for excel. First create your export operation from the control panel.
Fig. 1. An Export Operation for Excel
Then view the xbasic of the export operation. This will show the code needed to create the dialog component and perform our export operation via the web.
Fig. 2. Xbasic for Export
From here press the Copy to Clipboard button. Then we will create a Dialog Component to make our button to export our records.
Next we want to create a Dialog button that will perform the export operation for us. Select new Component, then Dialog. Press the OK button.
Fig. 3 Selecting Dialog Component
Next we want to place just one control on the web component.
Fig. 4. One Control for web component.
Then we want to hide the row under Row Properties.
Then we move to the properties section of the form. This is where we get creative. Naturally we want to choose our style.
To make the dialog component resemble a button, Rename the submit button with something appropriate (I used Export People) and remove (uncheck) the Reset button feature.
Fig. 5 Export Button
Next we move to the Server Events section. Here we click on the After Validate button and paste our code in from the Export operation. However, we will need to make a few small changes.
Fig. 6 Original Code for Export Operation.
For the first line, the Web version of our application does not know the current table. Remember our little tid bit of information mentioned earlier on? So we need to change
Line 1 a_tbl = table.current() To a_tbl = table.open("[PathAlias.ADB_Path]\people")
On Line 8 You may want to specify a special path or folder so you could recode line 8 to:
Line 8 export.file = "C:\Out_Folder\peopleout.xls"
That is basically all that you need to do for the component to work. Next we need to place the component on an Alpha Five web page.
How to create a dialog web component that will Export “Filtered” Records to Excel
This method is useful if you have a certain requirement to only export filtered records. For example you may only want to export records based on a Date being filled in and omit records without a specific date.
For our example we will export records whose title is President.
We can duplicate the export people Dialog Component and then edit it making our change to the export filter on line 3
Line 3 now becomes query.filter = "Title=\"President\""
Once again you may want to (need to) change line 8 to specify the path where the exported file may need to go.
Then just place the component on an Alpha Five web page. You can use the same page or create a new page depending on your requirements. Publish and you are good to go.
How to make your Excel Exports show up in a web browser window
Well now that you know how to export records to Excel, how will you use that file? Will it be emailed, will you allow your user or guest to download the file? Why not code a little further and let the file open up in a browser window? Then the user can save the file or just review the file and close the window.
To do this we need to wrap our Xbasic code with some additional code and make a few changes to the existing code.
We will insert at Lines 1 - 7 the following code:
Line 1 dim a_tbl as P ‘ Pointer to our table
Line 2 dim exportfile as C ‘making the Excel file name a character variable
Line 3 dim filename as C ‘making the export file name a character variable
Line 4 this is just a blank line for clarity
Line 5 exportfile = “peopleout.xls”
Line 6 filename = session.session_folder + exportfile ‘this is where the export file will be located and the browser will be directed here so it can display the Excel information.
Line 7 this is just a blank line for clarity
Line 8 The start of our original code, previously Line 1.
Next Line 15 get changed from
Line 15 Export.file=filename ‘here we let Alpha take care of the output path and file name
Then at the end of our original code we need to add a few lines.
Line 28 if file.exists(filename)
Line 29 response.redirect(session.session_url + exportfile+"?"+time("hms03"))
Line 30 end if
See Figure 7 below.
Fig. 7. Code Changes
Then we save our work, publish the component, and then insert the component on an Alpha Five Web Page and Viola! We have our exported records displayed in an Excel spreadsheet in the web browser. See Figure 10.
Figure 8 Excel records displayed in a Web Browser.
From here the user can do a file “Save As” and save the spreadsheet to their machine.
Back To Tips, Guidelines, Articles