Subject Re: Calculated field and Query
From Mervyn Bick <invalid@invalid.invald>
Date Tue, 11 Dec 2018 08:53:34 +0200
Newsgroups dbase.getting-started

On 2018-12-10 8:30 PM, Tim Ward wrote:
> On 10/12/2018 12:26, Dirk wrote:
>> so i have to find a way to loop the info
> Hi Dirk,
>      If I wanted to mix table fields and calculated values into excel
> I'd use something like the loop below. Doing this way you don't need to
> have the table fields in the same order as the excel output and you can
> have calculated fields where you require them. Sorry if my Dutch is not
> good.

Twelve programmers, a dozen solutions. :-)

I would prefer to make the calculations in the SQL statement with the
fields, including the calculated fields, in the required order.  This
simplifies the code for placing the data in Excel.

Assuming we have a table test.dbf with the fields aantal and
eenheidsprijs and we also want to show the cost.

cWorkbook = 'd:\examples\test.xls' //  As required
q = new query()
q.sql = 'select aantal,eenheidprijs,aantal*eenheidsprijs as kost from test' = true
qrf = q.rowset.fields //to save some typing later
oExcel  = new oleAutoclient("Excel.Application")
//oExcel.visible = true
oSheet = oExcel.ActiveSheet

// The following will place the fieldnames, including the calculated
// field's name, in the Excel worksheet as headings
// Alternatively replace with code to write headings to each column

nRow = 10
nCol = 8 //Column H
for n = 1 to qrf.size
    oCell = oSheet.cells(nRow,nCol)
    oCell.formula = qrf[n].fieldname

// The following will loop through the rowset and place the data for
// each record on a new line in Excel

do while not q.rowset.endofset
    nRow++ // Move down a row for each record
    nCol = 8 // Start in column H for each record
    for n = 1 to qrf.size
       oCell = oSheet.cells(nRow,nCol)
       oCell.formula = qrf[n].value
     next //Go to next record
enddo = false