Convert DFB to Excel

Lama ga nulis..
ok silahkan di copas aja


CREATE CURSOR curCompany (Company C(20), Qtr1 N(10,2), qtr2 N(10,2), qtr3 N(10,2), qtr4 N(10,2))
 FOR lni = 1 TO 10
 APPEND BLANK
 REPLACE curCompany.company WITH SYS(2015)
 REPLACE curCompany.qtr1 WITH 1 + 1000 * RAND( )
 REPLACE curCompany.qtr2 WITH 1 + 1000 * RAND( )
 REPLACE curCompany.qtr3 WITH 1 + 1000 * RAND( )
 REPLACE curCompany.qtr4 WITH 1 + 1000 * RAND( )
 ENDFOR

* Excel: HorizontalAlignment
 * 2 = Left
 * 3 = Center
 * 4 = Right

local oExcel, oSheet
 oExcel = CreateObject([Excel.Application])
 oExcel.Visible = .T.
 oExcel.Workbooks.Add()

oSheet = oExcel.ActiveSheet

lnRow = 0
 SELECT curCompany
 GO TOP
 DO WHILE NOT EOF()
 lnRow = lnRow + 1
 IF lnRow = 1
 oSheet.Cells(lnRow,1).Value = [FoxPro Rocks!]
 oSheet.Cells(2,1).Value = [FoxPro Rocks!]
 oSheet.Cells(3,1).Value = [FoxPro Rocks!]
 oSheet.Cells(4,1).Value = [FoxPro Rocks!]
 oSheet.Cells(5,1).Value = [FoxPro Rocks!]

lnRow = 6
 lnCol = 3
 oSheet.Range([C6]).Select
 oSheet.Cells(lnRow,lnCol).Value = [Qtr 1]
 oSheet.Cells(lnRow,lnCol).Font.Bold = .T.

*oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
 oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3

lnCol = lnCol + 1
 oSheet.Range([D6]).Select
 oSheet.Cells(lnRow,lnCol).Value = [Qtr 2]
 oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
 *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
 oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3

lnCol = lnCol + 1
 oSheet.Range([E6]).Select
 oSheet.Cells(lnRow,lnCol).Value = [Qtr 3]
 oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
 *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
 oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3

lnCol = lnCol + 1
 oSheet.Range([F6]).Select
 oSheet.Cells(lnRow,lnCol).Value = [Qtr 4]
 oSheet.Cells(lnRow,lnCol).Font.Bold = .T.
 *oSheet.Cells(lnRow,lnCol).HorizontalAlignment = xlCenter
 oSheet.Cells(lnRow,lnCol).HorizontalAlignment = 3

lnRow = 7
 lnBeginRange = lnRow
 ENDIF

oSheet.Cells(lnRow,1).Value = curCompany.Company
 oSheet.Cells(lnRow,3).Value = curCompany.qtr1
 oSheet.Cells(lnRow,4).Value = curCompany.qtr2
 oSheet.Cells(lnRow,5).Value = curCompany.qtr3
 oSheet.Cells(lnRow,6).Value = curCompany.qtr4

SKIP
 ENDDO

* Create the formula rather than hardcoding total so the user can
 * change the spreadsheet and it will reflect new totals.
 * Example:  =SUM(D5:D10)
 FOR lni = 1 TO 4
 lcFormula = [=SUM(] + CHR(64 + lni) + ALLTRIM(STR(m.lnBeginRange)) + [:] +;
 CHR(64 + 3 + lni) + ALLTRIM(STR(m.lnRow)) + [)]

oSheet.Cells(lnRow+1,2+lni).Formula = [&lcFormula]
 ENDFOR

hasil lihat gambar

gimana langsung ada SUM() loh hehe😀 silahkan kreatif sendiri..

lihat dibawah sample berikutnya🙂
How to copy a .jpg from a general field to an Excel sheet.

 oExcel =CREATEOBJECT("excel.application")
 oWorkBook = oExcel.workbooks.add()
 oSheet = oWorkbook.activesheet
 USE e:\trans\pics AGAIN IN 0 && The table with the general field that holds the jpg.
 LOCATE && Go op
 KEYBOARD "{CTRL+C}{CTRL+W}" && Copy the jpg
 MODIFY GENERAL pics.pic
 oSheet.paste() && Paste the clipboard content in the the sheet
 oExcel.visible = .t.</pre>

<span style="font-family: monospace;">

 

How to create a chart via Excel automation


<span style="font-family: monospace;"> #DEFINE xlColumnClustered    51
 LOCAL oExcel as Excel.application
 LOCAL oWorkbook,oSheet
 oExcel = CREATEOBJECT("Excel.application")
 oWorkbook= oExcel.Workbooks.Add()
 oSheet = oWorkbook.activesheet
 WITH oSheet
 .Range("A1").Select
 .Range("A1").FormulaR1C1 = "1"
 .Range("A2").Select
 .Range("A2").FormulaR1C1 = "2"
 .Range("A3").Select
 .Range("A3").FormulaR1C1 = "3"
 .Range("A4").Select
 .Range("A4").FormulaR1C1 = "4"
 .Range("A5").Select
 .Range("A5").FormulaR1C1 = "5"
 .Range("A6").Select
 .Range("A6").FormulaR1C1 = "6"
 .Range("B1").Select
 .Range("B1").FormulaR1C1 = "10"
 .Range("B2").Select
 .Range("B2").FormulaR1C1 = "11"
 .Range("B3").Select
 .Range("B3").FormulaR1C1 = "50"
 .Range("B4").Select
 .Range("B4").FormulaR1C1 = "60"
 .Range("B5").Select
 .Range("B5").FormulaR1C1 = "70"
 .Range("B6").Select
 .Range("B6").FormulaR1C1 = "90"
 .Range("A1:B6").Select
 ENDWITH
 WITH oWorkbook
 .Charts.Add
 .ActiveChart.ChartType = xlColumnClustered
 .ActiveChart.SetSourceData(oSheet.Range("A1:B6"))
 .ActiveChart.HasTitle = .f.
 ENDWITH
 oExcel.Visible =.t.</span>

How to delete a sheet from a workbook


<span style="font-family: monospace;">Local oSheet,oWorkBook,oExcel
 oExcel = CREATEOBJECT("Excel.application")
 oWorkBook = oExcel.Workbooks.Add()
 oSheet = oWorkBook.activeSheet
 oSheet.Delete()
 oExcel.Visible = .t.</span>

How to add a sheet to a workbook

 <span style="font-family: monospace;">Local oSheet,oWorkBook,oExcel
 oExcel = CREATEOBJECT("Excel.application")
 oWorkBook = oExcel.Workbooks.Add()
 oWorkbook.Sheets.Add
 oExcel.Visible = .t.</span>

How to move a sheet within a Workbook.

 <span style="font-family: monospace;">oExcel = CREATEOBJECT("excel.application")
 oWorkbook = oExcel.Workbooks.Add()
 oWorkbook.Sheets.Add
 oSheet = oWorkbook.ActiveSheet
 oSheet.Move(,oWorkbook.Sheets(4)) && Move after sheet3
 oSheet.Move(oWorkbook.Sheets(4),) && Move before sheet3
 oExcel.Visible =.t.</span>

link : http://www.tek-tips.com/faqs.cfm?fid=4428

http://www.tek-tips.com/faqs.cfm?fid=4266

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: