Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Bondjames

Technical User
Mar 25, 2021
24
IN
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()



 
[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
 
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

Code:
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
 
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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top