sikharma13
Starting Member
44 Posts |
Posted - 2013-10-31 : 08:22:44
|
its about excel automation via sqli want to insert my query here.. im new to sql and vfpand i just dont know what to do..sorry for being noob. :)here is my query.."select province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant,no_ncom_educ_m1,no_ncom_educ_m2,educ_grant_m1,educ_grant_m2,t_educ_grant,no_ncom_dew,no_eli_for_health_grant,no_ncom_hcv_m1,no_ncom_hcv_m2,no_ncom_bhcv,fncom_m1,fncom_m2,hg_m1,hg_m2,h_grant,total from py_abcdeeh "and here is the code i where i want to insert my query..local oExcel, oSheetoExcel = CreateObject([Excel.Application])if vartype(oExcel) != "O" * could not instantiate Excel object MESSAGEBOX("Invoice Auto was unable to open your version of Excel or Excel is not Installed",0+16, ; "Invoice Report to Excel") return .F.endifoExcel.Visible = .T.oExcel.Workbooks.Add()oExcel.Activesheet.Name = "Invoice Report Total"oSheet = oExcel.ActiveSheet*Assign Directory/Folder name to a varaibleLOCAL myDirmyDir = "C:\XLFolder"*If Folder does not exist create folderIF ! DIRECTORY(myDir) MD &myDirENDIFlnRow = 0lcInvoiceid = invoicehdr.invoiceidlcCustomerid = customers.customeridSELECT hedr.invoiceid, hedr.date, ;cust.name, cust.address, cust.city, cust.phone, cust.email, ; hedr.licenseno, hedr.vin, hedr.makemodel, hdtl.description, hdtl.quantity, hdtl.price, ;hdtl.labour_rate, hdtl.labour, hdtl.total, hedr.subtotal, hedr.disclaimeramount, hedr.hst, ;hedr.total as grandtotal FROM invoicehdr hedr ;INNER JOIN customers cust ON cust.customerid = hedr.customerid ;JOIN invoicedtl hdtl ON hdtl.invoiceid = hedr.invoiceid ;where hedr.invoiceid = lcinvoiceid AND cust.customerid = lcCustomerid ; into cursor curInvoiceSET STEP ON IF _tally = 0 MESSAGEBOX("There was no Data Found in your query",0+16, "Invoice Report Total") oExcel.DisplayAlerts = .F. oExcel.workbooks.close() oExcel.quit() oExcel = NULLRELEASE oExcel RETURN .f.ELSE DODEFAULT()ENDIFGO TOPoSheet.Range("A1").value = [Invoiceid]oSheet.Range("A2").value = curInvoice.invoiceidoSheet.Range("A1").Interior.ColorIndex = 6oSheet.Range("A1").Borders.Linestyle = 1oSheet.Range("A2").Borders.Linestyle = 1oSheet.Range("A1").Font.Bold = .T.oSheet.Range("B1").value = [Date]oSheet.Range("B2").value = curInvoice.dateoSheet.Range("B1").Interior.ColorIndex = 6oSheet.Range("B1").Borders.Linestyle = 1oSheet.Range("B2").Borders.Linestyle = 1oSheet.Range("B1").Font.Bold = .T.oSheet.Range("F3").value = curInvoice.name*!* oSheet.Range("F3").Interior.ColorIndex = 6oSheet.Range("F3").Borders.Linestyle = 1oSheet.Range("F3").Font.Bold = .F.oSheet.Range("F4").value = curInvoice.address*!* oSheet.Range("F3").Interior.ColorIndex = 6oSheet.Range("F4").Borders.Linestyle = 1oSheet.Range("F4").Font.Bold = .F.oSheet.Range("F5").value = curInvoice.city*!* oSheet.Range("F3").Interior.ColorIndex = 6oSheet.Range("F5").Borders.Linestyle = 1oSheet.Range("F5").Font.Bold = .F.oSheet.Range("F6").value = curInvoice.phone*!* oSheet.Range("F3").Interior.ColorIndex = 6oSheet.Range("F6").Borders.Linestyle = 1oSheet.Range("F6").Font.Bold = .F.oSheet.Range("F7").value = curInvoice.email*!* oSheet.Range("F3").Interior.ColorIndex = 6oSheet.Range("F7").Borders.Linestyle = 1oSheet.Range("F7").Font.Bold = .f.oSheet.Range("A6").value = [License Plate]oSheet.Range("A7").value = curInvoice.licensenooSheet.Range("A6").Interior.ColorIndex = 6oSheet.Range("A6").Borders.Linestyle = 1oSheet.Range("A7").Borders.Linestyle = 1oSheet.Range("A6").Font.Bold = .T.oSheet.Range("B6").value = [Vin]oSheet.Range("B7").value = curInvoice.vinoSheet.Range("B6").Interior.ColorIndex = 6oSheet.Range("B6").Borders.Linestyle = 1oSheet.Range("B7").Borders.Linestyle = 1oSheet.Range("B6").Font.Bold = .T.oSheet.Range("C6").value = [Make & Model]oSheet.Range("C7").value = curInvoice.makemodeloSheet.Range("C6").Interior.ColorIndex = 6oSheet.Range("C6").Borders.Linestyle = 1oSheet.Range("C7").Borders.Linestyle = 1oSheet.Range("C6").Font.Bold = .T.oSheet.Range("A9").value = [Description]oSheet.Range("A10").value = curInvoice.descriptionoSheet.Range("A9").Interior.ColorIndex = 6oSheet.Range("A9").Borders.Linestyle = 1oSheet.Range("A10").Borders.Linestyle = 1oSheet.Range("A9").Font.Bold = .T.oSheet.Range("B9").value = [Quantity]oSheet.Range("B10").value = curInvoice.quantityoSheet.Range("B9").Interior.ColorIndex = 6oSheet.Range("B9").Borders.Linestyle = 1oSheet.Range("B10").Borders.Linestyle = 1oSheet.Range("B9").Font.Bold = .T.oSheet.Range("C9").value = [Price]oSheet.Range("C10").value = curInvoice.priceoSheet.Range("C9").Interior.ColorIndex = 6oSheet.Range("C9").Borders.Linestyle = 1oSheet.Range("C10").Borders.Linestyle = 1oSheet.Range("C9").Font.Bold = .T.oSheet.Range("D9").value = [Labour Rate]oSheet.Range("D10").value = curInvoice.labour_rateoSheet.Range("D9").Interior.ColorIndex = 6oSheet.Range("D9").Borders.Linestyle = 1oSheet.Range("D10").Borders.Linestyle = 1oSheet.Range("D9").Font.Bold = .T.oSheet.Range("E9").value = [Labour]oSheet.Range("E10").value = curInvoice.labouroSheet.Range("E9").Interior.ColorIndex = 6oSheet.Range("E9").Borders.Linestyle = 1oSheet.Range("E10").Borders.Linestyle = 1oSheet.Range("E9").Font.Bold = .T.oSheet.Range("F9").value = [Total]oSheet.Range("F10").value = curInvoice.totaloSheet.Range("F9").Interior.ColorIndex = 6oSheet.Range("F9").Borders.Linestyle = 1oSheet.Range("F10").Borders.Linestyle = 1oSheet.Range("F9").Font.Bold = .T.oSheet.Range("A1:A20").Borders.Linestyle = 1 && Example: Creates borders for cells in rangeoSheet.Range("B1:B20").Borders.Linestyle = 1 oSheet.Range("C1:C20").Borders.Linestyle = 1 oSheet.Range("D1:D20").Borders.Linestyle = 1 oSheet.Range("E1:E20").Borders.Linestyle = 1 oSheet.Range("F1:F20").Borders.Linestyle = 1osheet.Columns("A").ColumnWidth = 15osheet.Columns("B").ColumnWidth = 15 oSheet.columns("C").ColumnWidth = 15 oSheet.columns("D").ColumnWidth = 15 oSheet.columns("E").ColumnWidth = 15oSheet.columns("F").ColumnWidth = 15oSheet.Columns("C").NumberFormatLocal = "$0.00_ " &&add decimal place 0.00oSheet.Columns("D").NumberFormatLocal = "$0.00_ " oSheet.Columns("F").NumberFormatLocal = "$0.00_ " oSheet.columns("A:F").HorizontalAlignment = 2 &&Align Left nAnswer = MESSAGEBOX("Would You Like To Save XLWorkBook C:\XLFolder", 4+32, "Save Active XLWorkBook")IF nAnswer = 6 STORE "C:\XLFolder\xlworkbook" + PADL(invoicehdr.invoiceid,8,[0]) + ".xlsx" TO mcExcelFileIF FILE(mcExcelFile) DELETE FILE (mcExcelFile) ENDIF oSheet.SaveAs(mcExcelFile) ENDIFnAnswer = MESSAGEBOX("Would You Like To Print XLWorkBook", 4+32, "Print Active XLWorkBook")IF nAnswer = 6 oSheet.pagesetup.Orientation = 2 oSheet.pagesetup.LeftMargin = 0.8 oSheet.pagesetup.RightMargin = 0 oSheet.pagesetup.TopMargin = 0.75 oSheet.pagesetup.BottomMargin = 0.75 oSheet.pagesetup.HeaderMargin = 0.3 oSheet.pagesetup.FooterMargin = 0.3 oSheet.PrintOut(1,2,1) ENDIF please i really need it.. thank you so much!VFP9.0 via MySQL 5.0 |
|
|