Method 1

Method 1 involves simply grabbing the report’s streamsource1.rowset and turning it into a spreadsheet via OLE automation.

set proc to whatever.rep addi
r = new whateverreport()

At this point, r.streamsource1.rowset is available for you to pass as a parameter to the following function.  This code is largely «borrowed» from Gary White’s paper ‘Talking to Excel’ from his web site. For a complete description of this code (as well as his explanatory comments which are deleted here for the sake of brevity), the reader is referred to the original source.

excelreports(r.streamsource1.rowset)

function ExcelReports(RepNameRowset)
   local oExcel, oRange, oCell,  r, i, ;
      nRow, cFormula, cRange, nTop, nLeft
   store 0 to oExcel, oRange, oCell, r, i, nRow, ;
      cFormula, cRange, nTop, nLeft
   r := RepNameRowset
   r.first()
   oExcel := new oleAutoclient("excel.application")
   oExcel.workbooks.add()
   nRow := 1
   for i = 1 to r.fields.size
      oExcel.ActiveSheet.cells( nRow, i ).select()
      with ( oExcel.ActiveCell )
         ColumnWidth := r.fields[i].length
         formula := r.fields[i].fieldName
      endwith
   endfor
   if r.first()
      do
         nRow ++
         for i = 1 to r.fields.size
            oCell := oExcel.ActiveSheet.cells( nRow, i )
            do case
               case r.fields[i].type = "CHARACTER"
                  oCell.formula := ;
                     "'"+trim(r.fields[i].value)
               case r.fields[i].type = "DOUBLE"
                  oCell.formula := r.fields[i].value
               otherwise
                  oCell.formula := r.fields[i].value
            endcase
         endfor
      until not r.next()
   endif
   oExcel.ActiveSheet.cells( 1, 1 ).select()
   oExcel.Cells.Select()
   oExcel.Selection.Columns.AutoFit()
   oExcel.Range("A1").Select()
   oExcel.visible := true
   oExcel = null
   return

This works fine for many reports, and actually gives the user a more useful dataset to work with than a literal translation of the report into Excel. But it doesn’t work well with multiple table datamodule-based reports which use extensive master rowset linking and calculated fields, nor does it generate quite what the user wants for summary reports. For these, we recommend method 2.

Method 2

To illustrate this, we need to create one of those troublesome reports. So that we’re all on the same sheet of music, change to the c:\program files\borland\visual dbase\samples\fleet\tables subdirectory, and run the report wizard to generate a summary report on the schedule.dbf. In report wizard step 2, select ‘Summary’, in step 3 select ‘Flight ID’ (ascending), in step 4 select ‘Booked’, in step 5 select ‘Next’ (i.e., just leave the defaults), and finally, ‘Run the Report’, saving it to an appropriate name (we’ll use ‘junk.rep’). You should end up with a short 2 column report with summaries for each of the Flight IDs.

Next, create a table to hold the report results – junk.dbf sounds appropriate – with 2 fields: Flight ID, and Booked (or whatever titles you want to appear on the spreadsheet), both numeric, and wide enough to hold the report results. Open junk.rep in the designer, and drag junk.dbf onto it. Change the junk1 query object’s requestlive property to true. Now we’re ready for phase 2.

Click on the ‘Flight Id: 3’ text object (the value ‘3’, not the words ‘Flight Id:’ — see the image below), and inspect it. Examine the object’s text property ({||"<H3>  " + this.parent.parent.parent.rowset.fields["Flight ID"].value + "</H3>}), and copy it to the Windows clipboard.

Now go to the events pane of the inspector, and select ‘onRender’(not 'canRender' — see the image below), changing it to a codeblock command (not expression). In the codeblock editor, type:

   this.form.junk1.rowset.beginAppend()   // which opens up your new table
   this.form.junk1.rowset.fields['Flight ID'].value=  // and then paste in your copied text from the
         clipboard at the end of this line. Edit it to delete the <H3> html tags, so you end up with
   ...=this.parent.parent.parent.rowset.fields["Flight ID"].value.

Now select the ‘Sum of Booked’ field (form.streamsource1.group1.footerband.text1), and create an onRender codeblock for it. All you need to put in here is this.form.junk1.rowset.fields["BOOKED"].value = this.text() since we’re continuing within the same beginAppend() event initiated in the previous field. Also, since we don’t have to worry about the HTML tags, we can just reference the object’s text value directly.

Since the beginAppend() on the next onRender event generates an implicit save(), our rows are committed as the next set of data renders. Until we get to the bottom of the page, that is. Fortunately, somebody must have known we were going to try something like this, because the report form has an onPage() event, which fires when the page is done rendering. Just add a codeblock here which issues the this.form.junk1.rowset.save() statement, and we’re done!

Before rendering the report, make sure to delete the contents of 'junk.dbf'.  For example, in your pushbutton (or whatever) onClick event:

_app.databases[1].emptytable('junk') // or use junk exclu; zap; use

Next, fill the junk.dbf with data by rendering the report:

set proc to junk.rep addi
r = new junkreport()
r.render()

Note that at this point, if your report has more than one page (which it shouldn't in this example), the junk table will only accrue as many records as pages are rendered. This can be a “feature” if users of an application only want part of a report converted to Excel.  Otherwise, you'll have to employ some means to render() the entire report, such as changing  r.output  to 4 (HTML) instead of the default.

Finally, convert the contents of the junk.dbf rowset by using the code described at the beginning of this paper:

set proc to (file containing the Excelreports function) addi
(class containing the Excelreports function)::excelreports(r.junk1.rowset)

There are, of course numerous permutations and variations of when, where, and how much of which rowset to convert to Excel, but basically, this is it!

Good luck!