Use Foxpro to work with Excel

** Opening ******************************************

*

_xls.CreateObject ('Excel.Application')

_xls.Workbooks.Add

_xls.Workbooks.Open (sys(5) + curdir() + 'book.xls')

_xls.Visible = .t.

** Sheets *******************************************

*

_xls.sheets(1).Select && Select Sheet

_xls.sheets(2).Delete && Delete Sheet

_xls.sheets(3).Name = 'Hello' && Rename Sheet

_xls.sheets.add && Add new sheet

** Columns / Row ************************************

*

_xls.Rows("1:5").RowHeight = 10

_xls.Rows("1:5").EntireRow.AutoFit

_xls.Rows("1:5").Insert

_xls.Columns("A:Z").ColumnWidth = 10

_xls.Columns("A:Z").EntireColumn.AutoFit

_xls.Columns("A:B").Insert

** Selection ****************************************

*

_xls.Range("A1:A5").Select && Select Range

_xls.Cells(1, 1).Select && Select Cell

_xls.Selection.End(1).Select && Next Left

_xls.Selection.End(2).Select && Next Right

_xls.Selection.End(3).Select && Next Top

_xls.Selection.End(4).Select && Next Bottom

_xls.Columns("A:Z").Select && Select Columns

_xls.Rows("1:5").Select && Select Rows

_xls.Cells.Select && Select Entire Workbook

** Data Extract / Assignment ************************

*

_xls.Selection = 'Text'

_xls.Cells(1, 1).Value = 'Text'

_value = _xls.Cells(1, 1).Value

** Font Style Setting *******************************

*

_xls.Selection.Font.Name = 'Arial'

_xls.Selection.Font.Size = 12

_xls.Selection.Font.Bold = .T.

_xls.Selection.Font.Italic = .T.

_xls.Selection.Font.Underline = .T.

_xls.Selection.Font.ColorIndex = 0

_xls.Selection.ShrinkToFit = .T.

_xls.Selection.Orientation = 45

_xls.Selection.MergeCells = .T.

_xls.Selection.WrapText = .T.

_xls.Selection.NumberFormat = '$#,##0.00'

_xls.Selection.Interior.ColorIndex = 0

** Alignment ****************************************

*

_xls.Selection.HorizontalAlignment = 1 && Auto Justify

_xls.Selection.HorizontalAlignment = 2 && Left Justify

_xls.Selection.HorizontalAlignment = 3 && Center Justify

_xls.Selection.HorizontalAlignment = 4 && Right Justify

_xls.Selection.VerticalAlignment = -4160 && Top Justify

_xls.Selection.VerticalAlignment = 2 && Center Justify

_xls.Selection.VerticalAlignment = 3 && Bottom Justify

** Border Lines *************************************

*

_xls.Selection.Borders(1).LineStyle = 1 && Cell Left

_xls.Selection.Borders(2).LineStyle = 1 && Cell Right

_xls.Selection.Borders(3).LineStyle = 1 && Cell Top

_xls.Selection.Borders(4).LineStyle = 1 && Cell Bottom

_xls.Selection.Borders(5).LineStyle = 1 && \ Diagonal

_xls.Selection.Borders(6).LineStyle = 1 && / Diagonal

_xls.Selection.Borders(7).LineStyle = 1 && Outer Left

_xls.Selection.Borders(8).LineStyle = 1 && Outer Top

_xls.Selection.Borders(9).LineStyle = 1 && Outer Bottom

_xls.Selection.Borders(10).LineStyle = 1 && Outer Right

_xls.Selection.Borders(10).Weight = 1 && See line weight below

** Picture ******************************************

*

_xls.ActiveSheet.Pictures.Insert ( ;

sys(5) + curdir() + 'TEST.JPG').Select

_xls.Selection.ShapeRange.ScaleHeight(0.5, 1, 0) && 0.5=50%

_xls.Selection.ShapeRange.ScaleWidth (0.5, 1, 0)

_xls.Selection.ShapeRange.IncrementLeft (1)      && Minus to move right

_xls.Selection.ShapeRange.IncrementTop (1)       && Minus to move up

_xls.ActiveSheet.Shapes.Addline (X1, Y1, X2, Y2).Select

** Page Break ***************************************

*

_xls.ActiveSheet.Rows(10).PageBreak = 1 && 0 = Remove Break

_xls.ActiveSheet.Columns(10).PageBreak = 1

** Page Setup ***************************************

*

_xls.ActiveSheet.PageSetup.CenterHeader = "Center Header"

_xls.ActiveSheet.PageSetup.LeftHeader = "Left Header"

_xls.ActiveSheet.PageSetup.RightHeader = "Right Header"

_xls.ActiveSheet.PageSetup.CenterFooter = "Center Footer"

_xls.ActiveSheet.PageSetup.LeftFooter = "Left Footer"

_xls.ActiveSheet.PageSetup.RightFooter = "Right Footer"

_xls.ActiveSheet.PageSetup.HeaderMargin = 36 && 1" = 72

_xls.ActiveSheet.PageSetup.FooterMargin = 36

_xls.ActiveSheet.PageSetup.BottomMargin = 36

_xls.ActiveSheet.PageSetup.TopMargin = 36

_xls.ActiveSheet.PageSetup.LeftMargin = 36

_xls.ActiveSheet.PageSetup.RightMargin = 36

_xls.ActiveSheet.PageSetup.PrintArea = "A1:Z30"

_xls.ActiveSheet.PageSetup.PrintGridLines = .T.

_xls.ActiveSheet.PageSetup.PrintHeading = .T.

_xls.ActiveSheet.PageSetup.PrintTitleColumns = "$A:$B"

_xls.ActiveSheet.PageSetup.PrintTitleRows = "$1:$5"

_xls.ActiveSheet.PageSetup.FitToPagesWide = 1

_xls.ActiveSheet.PageSetup.FitToPagesTall = 1

_xls.ActiveSheet.PageSetup.Zoom = .F.

_xls.ActiveSheet.PageSetup.CenterHorizontally = .T.

_xls.ActiveSheet.PageSetup.CenterVertically = .T.

_xls.ActiveSheet.PageSetup.PaperSize = 1 && 1=Letter, 2=Legal

_xls.ActiveSheet.PageSetup.Orientation = 1 && 1=Portrait

&& 2=Landscape

** Closing ******************************************

*

_xls.DisplayAlerts = .F.

_xls.AlertBeforeOverwriting = .F.

_xls.ActiveWorkBook.Save

_xls.ActiveWorkBook.SaveAs (sys(5) + curdir() + 'book.xls')

_xls.Quit

Release _xls

***** Color Index *****

*

* 00=No Fill 01=Black 02=White 03=Red

* 04=Bright Green 05=Blue 06=Yellow 07=Pink

* 08=Turquoise 09=Dark Red 10=Green 11=Dark Blue

* 12=Dark Yellow 13=Violet 14=Teal 15=Gray 25%

* 16=Gray 50% 33 Sky Blue 34=Light Turqupise 35 Light Green

* 36=Light Yellow 37=Pale Blue 38=Rose 39=Lavender

* 40=Tan 41=Light Blue 42=Aqua 43=Lime

* 44=Gold 45=Light Orange 46=Orange 47=Blue-Gray

* 48=Gray 40% 49=Dark Teal 50=Sea Green 51=Dark Green

* 52=Oliver Green 53=Brown 54=Plum 55=Indigo

* 56=Gray 80%

*

***********************

***** Line Style ******

*

* 1=Continuous 4=Dash Dot 5=Dash Dot Dot 13=Slant Dash Dot

* -4115=Dash -4118=Dot -4119=Double

*

***********************

***** Line Weight *****

*

* 0=None 1=Hairline 2=Thin -4138=Medium 4=Thick

*

***********************

Split PDF into single page

** This process requires you to have acrobat pro
** The acrobat reader does not compatible with it


** Create an object to hold the Acrobat
Pd = CreateObject('acroexch.pddoc')
Pn = CreateObject('acroexch.pddoc')Pd.open('input.pdf')


** Get the actual page count from pdf
PC = pd.getnumpages


** First page is 0, so the last page is the PC - 1
For cp = 0 to PC - 1


  ** Create a new and empty PDF file
  Pn.create


  ** Insert a page from the original file
  Pn.insertpages(-1, pd, cp, 1, .f.)


  ** Save the new pdf with page number
  Pn.save(1, 'output_' + padl(cp + 1, 3, '0'))


  ** Close the new pdf file
  Pn.close


Next


** Close and release all PDF process
Pd.Close
Release pd
Release Pn
Close all

Print to IP printer at DOS Command

In the DOS command prompt, create a batch file and type the following code:




For /R %CD% %%I in (*.PRN) do (
  @echo printing %%I
  LPR -S 192.168.1.100 -P raw %%I
)




The 192.168.1.100 is your network printer's IP address. It should be changed accordingly.


This program reads all *.PRN and print them to the specified IP printer.

Ontario Public Holiday Program

Lparameters pDate
Private xY, xC, xN, xK, xI
Private xJ, xM, xD, xHoliday


xHoliday = ''
Do Case
  Case Month(pDate) =  1 And ;
       Day(pDate)   =  1
    xHoliday = "New year's day"

  Case Month(pDate) =  1 And ;

       Day(pDate)   =  2 And ;

       Dow(pDate)   =  2

    xHoliday = "Monday after new year"

  Case Month(pDate) =  2 And ;

       Dow(pDate)   =  2 And ;

       Day(pDate)   > 14 And ;

       Day(pDate)   < 22

    xHoliday = "Family Day"

  Case Month(pDate) =  5 And ;
       DOW(pDate)   =  2 And ;
       DAY(pDate)   > 21 And ;
       DAY(pDate)   < 29
    xHoliday = 'Victoria Day'

  Case Month(pDate) =  7 And ;
       Day(pDate)   =  1
    xHoliday = 'Canada Day'

  Case Month(pDate) =  7 And ;
       Day(pDate)   =  2 And ;
       Dow(pDate)   =  2
    xHoliday = 'Monday after Canada Day'

  Case Month(pDate) =  8 And ;
       Dow(pDate)   =  2 And ;
       Day(pDate)   <  8
    xHoliday = 'Civil Holiday'

  Case Month(pDate) =  9 And ;
       Dow(pDate)   =  2 And ;
       Day(pDate)   <  8
    xHoliday = 'Labour Day'

  Case Month(pDate) = 10 And ;
       Dow(pDate)   =  2 And ;
       Day(pDate)   >  7 And ;
       Day(pDate)   < 15
    xHoliday = 'Thanksgiving'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 25
    xHoliday = 'Christmas Day'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 26
    xHoliday = 'Boxing Day'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 27 And ;
       Dow(pDate)   = 2
    xHoliday = 'Monday after Christmas'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 28 And ;
       Dow(pDate)   = 2
    xHoliday = 'Monday after Christmas'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 27 And ;
       Dow(pDate)   = 3
    xHoliday = 'Tuesday after Christmas'

  Case Month(pDate) = 12 And ;
       Day(pDate)   = 28 And ;
       Dow(pDate)   = 3
    xHoliday = 'Tuesday after Christmas'

  Otherwise
    xY = Year(pDate)
    xC = Int(xY / 100)
    xN = xY - 19 * Int(xY / 19)
    xK = Int((xC - 17) / 25)
    xI = xC - Int(xC / 4) - ;
         Int((xC - xK) / 3) + 19 * xN + 15
    xI = xI - 30 * Int(xI / 30)
    xI = xI - Int(xI / 28) * (1 - Int(xI / 28) * ;
         Int(28 / (xI + 1)) * Int((21 - xN) / 11))
    xJ = xY + Int(xY / 4) + xI + 2 - xC + Int(xC / 4)
    xJ = xJ - 7 * Int(xJ / 7)
    xL = xI - xJ
    xM = 3 + Int((xL + 40) / 44)
    xD = xL + 28 - 31 * Int(xM / 4)
 
    Do Case
      Case pDate = Date(xY, xM, xD) - 2
        xHoliday = 'Good Friday'

      Case pDate = Date(xY, xM, xD) + 1
        xHoliday = 'Easter Monday'

      Case Dow(pDate) = 1
        xHoliday = 'Sunday'

      Case Dow(pDate) = 7
        xHoliday = 'Saturday'

    Endcase
Endcase
Return xHoliday

Change default printer

Declare Long GetLastError In WIN32API
Declare Long SetDefaultPrinter In WINSPOOL.DRV String pPrinterName


xPrinterName = "New Printer"
xDefaultPrinter = Set("printer", 2)
If SerDefaultPrinter(xPrinterName) = 0
  MessageBox("Unable to set the " + ;
             xPrinterName         + ;
             " as the default printer", 0 + 16)
Else


  && insert your code here...


EndIf


SerDefaultPrinter(xDefaultPeinter)

Treeview Recursive Program

It is a simple way to implement a Treeview in Foxpro.


Create a catalog database

Create Table Catalog (ID N(10), Parent N(10), Name C(50))
The ID field must be an unique number with start from 1 to 9999999999. Parent is the number pointing to the parent node (Previous level). In this program, 0 (zero) is the root node (first node) of the tree.

Create a form

Add a TreeView control into a form. Name it as tvwCatalog. This is what I usually do. I use txtSomething to indicate the object is a text box. tvw is stand for TreeView. Although it is not quite necessary, but with a good practice is always a good idea. In the Form1.Init, add Do Load_Catalog with this, 0

Create a recursive program (Load_Catalog)

Here is the code of the Load_Catalog.Prg

LPARAMETERS pTree, pNode

Private xQ, xParent, xID, xName, xCheck

***** Add the root node when the tree is empty

IF pTree.Nodes.Count = 0

pTree.Nodes.Add (,, 'C0', 'Root')

pTree.Nodes.Item('C0').Expanded = .T.

pTree.Nodes.Item('C0').Checked  = .T.

ENDIF

***** SYS(2015) returns a temporary file name

xQ = SYS(2015)

***** Read the nodes with specified parent node number

Select * from Catalog ;

 WHERE Parent = pNode ;

 ORDER BY Name        ;

 INTO CURSOR &xQ

***** If this parent has child node

IF RECCOUNT() > 0

  SCAN

    xParent = 'C' + ALLTRIM(STR(&xQ..Parent))

    xID     = 'C' + ALLTRIM(STR(&xQ..ID))

    xName   =       ALLTRIM(&xQ..Name)

    pTree.Nodes.Add (xParent, 4, xID, xName)

    DO Load_Catalog WITH pTree, &xQ..ID

  ENDSCAN

ENDIF

Select &xQ

USE

Test the code

Since the Catalog database has no record. You should only see the "Root" in the tree. Later I added an "ADD" button. Here is the code:


PRIVATE xName, xID, xParent, xName


xName = ALLTRIM(INPUTBOX('Please enter a name', ;
                         'Add New Catalog', ''))


IF NOT EMPTY(xName)
  SELECT MAX(ID + 1) as NewID FROM Catalog INTO CURSOR Q1
  IF RECCOUNT() = 0
    xID  = 1
  ELSE
    xID  = IIF(RECCOUNT() = 0, 1, Q1.NewID)
  ENDIF
  xParent = VAL(SUBSTR(thisform.tvwCatalog.SelectedItem.Key, 2))

  INSERT INTO Catalog (ID,  Parent,  Name) ;
               VALUES (xID, xParent, xName)
thisform.tvwCatalog.Init
ENDIF