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()
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()