sum of fields of data exported from dbf to excel in VFP

[Bondjames]

have written following codes
it shows error in line [highlight #CC0000]FOR EACH FIELD IN curxl[/highlight]
also require totals to be written down in the field with headings
please can someone explain me
thanks
regards

CLOSE ALL

LOCAL oform as Form
DO Form getdaterange NAME oform noshow
oform.AutoCenter = .t.
oform.Show(1) && 1 = modal style to hold the form visible
IF oform.cancelled
** exit. do not run query
RETURN
ENDIF

** get the date values selected from the hidden form
m.dt1 = oform.from_date
m.dt2 = oform.to_date

oform.release()

SET ENGINEBEHAVIOR 70

SELECT ;
'Invoice ' as doc_type, ;
crs.date as doc_date, ;
crs.bill_no as doc_num, ;
'R' as trans_type, ;
ms.no_of_copy as place_of_supply, ;
'' as rv_chg, ;
'' as igst_state, ;
'27221F1ZF' as supp_gstin, ;
'P A' as legal_name, ;
'PA' as trade_name, ;
'J road' as supp_addr1, ;
'ridi' as supp_addr2, ;
'kolkate' as supp_city, ;
'xxxxxx' as supp_pin, ;
ms.gstin as rcp_gstin, ;
ms.bill_name as rcp_lgl_nme, ;
ms.bill_name as rcp_trade_nme, ;
ms.add1 as rcp_addr1, ;
ms.add2 as rcp_addr2, ;
'TBD' as rcp_city, ;
'TBD' as rcp_pin, ;
'TBD...' as shpto, ;
itm.hsn_code, ;
itm.itemname as prod_desc, ;
SUM(slp.qnty) AS qnty, ;
'OTH' AS item_uqc, ;
slp.rate as unit_price, ;
SUM(slp.gross_amt) as item_value, ;
SUM(slp.sgst_perc + slp.cgst_perc) as Item_gst, ;
SUM(slp.igst_perc) as IGST_AMT, ;
SUM(slp.cgst_perc) as CGST_AMT, ;
SUM(slp.sgst_perc) as SGST_AMT ;
FROM MASTER ms ;
JOIN crsale as crs ON crs.acd = ms.acd ;
JOIN slips as slp ON slp.bill_no = crs.bill_no ;
JOIN itemmst as itm ON itm.itemcode = slp.itemcode ;
WHERE crs.date between m.dt1 AND m.dt2 ;
GROUP BY crs.date, crs.bill_no, itm.itemname,slp.rate ;
INTO CURSOR curxl



SET ENGINEBEHAVIOR 90

LOCAL lnRow, lnCol, loExcel, loSheet
LOCAL lcFileName, lcSheetName, lcFieldName, lcFunction, lcFormat

lcFileName = "C:\1\MyData.xlsx"
lcSheetName = "MySheet"
loExcel = CREATEOBJECT("Excel.Application")
loExcel.Visible = .T.

* Add a new workbook and select the first sheet
loExcel.Workbooks.Add()
loSheet = loExcel.ActiveWorkbook.Worksheets(1)
loSheet.Name = "MySheet"
LOCAL lcTemplate
lcTemplate = "C:\1\123.xlsx"

IF EMPTY(ALIAS())
loexcel.Workbooks.Close()
loexecl.Quit()
MESSAGEBOX("No table or cursor found to export!", 48)
RETURN .f.
ENDIF

LOCAL ncols, j, cell_value
ncols = FCOUNT()
SCAN
FOR j = 1 TO ncols
** get data so we can check the type
cell_value = EVALUATE(FIELD(j))
DO CASE
CASE VARTYPE(m.cell_value) = 'N' OR VARTYPE(m.cell_value) = 'D'
loexcel.Cells(RECNO() + 1, j).Value = m.cell_value
OTHERWISE
loexcel.Cells(RECNO() + 1, j).Value = TRANSFORM(m.cell_value)
ENDCASE
NEXT j

ENDSCAN

lcFunction = "SUM"
lcFormat = "#,##0.00"
FOR EACH FIELD IN curxl
lcFieldName = FIELD.NAME
loSheet.Cells(lnRow, lnCol).Value = lcFunction + "(" + lcFieldName + ")"
loSheet.Cells(lnRow + 1, lnCol).Formula = "=SUM(" + lcFieldName + ")"
loSheet.Cells(lnRow + 1, lnCol).NumberFormat = lcFormat
lnCol = lnCol + 1
ENDFOR

loExcel.ActiveWorkbook.SaveAs(lcFileName)
loExcel.ActiveWorkbook.Close()
loExcel.Quit()

 

[Mike Lewis]

[tt]FOR EACH FIELD IN <cursor name>[/tt] is not valid VFP syntax. FOR EACH is used to loop through arrays or collections, not tables or cursors. You probably want to use a SCAN instead (although it's hard to know for sure because I'm having diffculty in understanding your code).

I suggest you start by studying the relevant items in the VFP Help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

This has many misunderstandings.

A loop on all fields can't be done by FOR EACH. But there is a loop as you need it just a few lines above using ncols=FCOUNT() - the field count - and using FIELD(j) for getting the field name of field number j, i.e. also FIED.NAME is wrong, it would either address a property Name of an object Field or the field called name of a workarea called Field, but there is no such object or workarea.

What you want to do is

FOR j = 1 TO ncols
lcFieldname= FIELD(j)
...
ENDFOR

The rest of your code also won't work, though. To add a SUM formula in Excel, you'll need cell names, not a column name. And when you set a formula, that will be evaluated and why do you set the value, when you set the formula, the formula will compute the value, that's not your task. If you set formula and then value, the value will override the formula result, that's just not what you want. You will also only be able to sum cells that are numeric, so this won't necessarily work for all columns, anyway.

Think harder about what you want in the "..." section of the loop.

Chriss

 

[mjcmkrsr]

Hi,

Your SQL SELECT will also throw an error. In VFP9 you have to group by all columns that are NEITHER constants (e.g. 'Invoice ' as doc_type) NOR aggregate functions (SUM(...) as)

hth

MarK

EDIT: I just noticed that you SET ENGINEBEHAVIOR TO 70 to circumvent the error throwing of your SELECT ... Although this is possible it is rather confusing since your mixing different behaviors of the VFP SQL engine.

 

[Chris Miller]

Most of the code is replaceable by the COPY TO or EXPORT command, by the way, you get all data written out to an xls file. That's an old Excel file type, but supporting all relevant VFP data types except general fields. Clearly, it supports all numeric types which are the only ones relevant for sums. And they also export a first row with field names.

Then the only aftermath to do is load the file and add the sum formulas.

Chriss

 

[Bondjames]

thank you Chris, mjcmksr, Mike for your advice just if you could tell me like i get the dbf with these headings as shown and i want to export to dbf with sum of few fields ( as rightly pointed out by mjcmksr) what code should i write please if youll can guide me only the export and sum part
regards

 

[mjcmkrsr]

Hi,

Please have a look at

https://praisachion.blogspot.com/2015/06/append-from-xlsx.html

- probably you'll find what you need

hth

MarK