7.8 Putting it all together | Microsoft Office Automation with Visual FoxPro

Putting it all together

Listing 2 shows a program (XLSample1.PRG in the Developer Download files available at www.hentzenwerke.com) that creates a workbook with worksheets for the order history of the first three clients (the first three is just a number chosen from the air; you can play around with the loop counter, if you desire). This example covers entering data, adding a totaling formula, formatting cells, setting up the sheets to print, viewing the PrintPreview, and finally saving the spreadsheet (also see Figure 11).

Listing 2. Tasmanian Traders customer history workbook. This example covers many of the topics in this chapter, including adding data and formulas, formatting cells, saving the spreadsheet, previewing, and others.
* Put order information for several customers into an Excel worksheet

* Change the LoopCounter constant to change the number of customers

* entered into Excel.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

#DEFINE xlEdgeBottom 9

#DEFINE xlEdgeTop 8

#DEFINE xlContinuous 1

#DEFINE xlMedium -4138

#DEFINE xlHAlignCenter -4108

#DEFINE xlHAlignCenterAcrossSelection 7

#DEFINE xlVAlignBottom -4107

#DEFINE xlAutomatic -4105

#DEFINE xlPortrait 1

#DEFINE autoIn2Pts 72

#DEFINE swp_nosize 1

#DEFINE swp_nomove 2

#DEFINE hwnd_topmost -1

#DEFINE hwnd_notopmost -2

#DEFINE LoopCounter 3

LOCAL oBook, oRange, oSheet

* Open the Order History view, which contains

* a summary of orders for one customer.

CLOSE DATA

OPEN DATABASE _SAMPLES + "\TASTRADE\DATA\Tastrade"

USE CUSTOMER IN 0

SELECT 0

USE "Order History" ALIAS OrderHistory

* Add a workbook, using default settings

oExcel = CREATEOBJECT("Excel.Application")

oExcel.Visible = .T.

oBook = oExcel.Workbooks.Add()

oSheet = oBook.ActiveSheet

* Add styles to the workbook

WITH oBook

.Styles.Add("Bold")

WITH .Styles["Bold"]

.Font.Bold = .T.

.Font.Size = 12

.HorizontalAlignment = xlHAlignCenter

.VerticalAlignment = xlVAlignBottom

.WrapText = .T.

ENDWITH

ENDWITH

FOR I = 1 TO LoopCounter

REQUERY()

WITH oSheet

* Name the sheet with the Customer ID

.Name = Customer.Customer_ID

.Select()

* Put customer name at top

.Range("A2").Value = Customer.Company_Name

* Put column headings in Row 5

.Range("A5").Value = "Order Number"

.Range("B5").Value = "Date"

.Range("C5").Value = "Amount"

oRange = .Range("A6:C6")

ENDWITH

* Loop through orders and send data

SCAN

WITH oRange

.Columns[1].Value = Order_Id

.Columns[2].Value = Order_Date

.Columns[3].Value = Ord_Total

ENDWITH

* Move range down one row

oRange = oRange.Offset(1,0)

ENDSCAN

* Now add total row

nLastRow = oRange.Row && Row property always give first row of range

&& This range has only one row

cLastRow = ALLTRIM(STR(nLastRow))

WITH oSheet

.Cells[ nLastRow , 1 ] = "Total"

* Need to convert nLastRow to char to use in formula for sum

.Cells[ nLastRow , 3 ].Formula = ;

"=SUM( C6:C" + LTRIM(STR(nLastRow - 1)) + " )"

ENDWITH

* Start Formatting the sheet

WITH oSheet

* Apply the Bold Style to column headers

WITH .Range("A5:C5")

.Style = "Bold"

.Borders[xlEdgeBottom].LineStyle = xlContinuous

ENDWITH

* Apply Bold Style to Total label

.Range("A" + cLastRow).Style = "Bold"

.Range("A" + cLastRow + ":C" + cLastRow).Borders[xlEdgeTop].LineStyle = ;

xlContinuous

* Apply Bold Style to client name, then override

* the horizontal alignment to spread it across columns

.Range("A2").Style = "Bold"

.Range("A2:C2").HorizontalAlignment = xlHAlignCenterAcrossSelection

* Change the format of the date column

.Range("B6:B" + cLastRow).NumberFormat = "Dd-Mmm-YYY"

* Fix the column width

.Range("A5:C" + cLastRow).Columns.AutoFit()

* For some reason, the first column doesn't expand enough

* without the column header wrapping one character of the

* last word. Add another character to the column width

.Range("A5").Columns.ColumnWidth = .Range("A5").Columns.ColumnWidth + 1

* AutoFit ensures that the largest value just barely fits.

* We prefer a little more space between the last two columns, so

* add another character to each of these columns, too

.Range("B5").Columns.ColumnWidth = .Range("B5").Columns.ColumnWidth + 1

.Range("C5").Columns.ColumnWidth = .Range("C5").Columns.ColumnWidth + 1

* Now set up the page to print

WITH .PageSetup

* Don't assume that these are the defaults;

* these are things users can change!

.Orientation = xlPortrait

.FitToPagesTall = 1

.FitToPagesWide = 1

.CenterHorizontally = .T.

.CenterVertically = .T.

.TopMargin = 1.0 * autoIn2Pts

.BottomMargin = 1.0 * autoIn2Pts

.LeftMargin = 1.5 * autoIn2Pts

.RightMargin = 1.5 * autoIn2Pts

.HeaderMargin = 0.5 * autoIn2Pts

.FooterMargin = 0.5 * autoIn2Pts

.LeftHeader = "TasTrade Client ID &A"

.RightHeader = "Page &P of &N"

.RightFooter = "Printed On &D"

ENDWITH

ENDWITH

* Add another sheet for the next client

IF I <> LoopCounter

oSheet = oBook.Worksheets.Add()

ENDIF

* Get next client

SKIP IN Customer

ENDFOR

* PrintPreview the workbook.

* Declare the APIs

* FindWindowA returns the window handle from the window's caption

DECLARE LONG FindWindowA IN WIN32API STRING class, STRING title

* SetWindowPos moves the window to the top, using the window handle

DECLARE SetWindowPos IN WIN32API LONG HWND, LONG hwndafter, ;

LONG x, LONG Y, LONG cx, LONG cy, LONG flags

* Capture the window handles for both Excel and VFP.

hWndXL = FindWindowA("XLMAIN", oExcel.Caption)

hWndVFP = FindWindowA("VFP66400000", _VFP.Caption)

* Bring VFP to the top, and instruct the user to

* close PrintPreview

= SetWindowPos(hWndVFP, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

= MessageBox("Close this MessageBox, and then Excel will come forward." + ;

CHR(13) + "Close Print Preview when ready to return to FoxPro.")

* Now bring the Excel window to the top

= SetWindowPos(hWndVFP, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

= SetWindowPos(hWndXL, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

* Issue the PrintPreview method to show the first client.

* Make sure your code can tolerate a wait state, and that the

* user knows to close the PrintPreview window to continue

oSheet.PrintPreview()

* When the user closes PrintPreview, it returns to here.

* Bring VFP to the top, then notify user.

= SetWindowPos(hWndXL, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

= SetWindowPos(hWndVFP, hwnd_topmost, 0,0,0,0, swp_nosize + swp_nomove)

= MessageBox("Building the Excel file is complete." + CHR(13) + ;

"Ready to save the Excel file.")

* Reset Excel and VFP to NoTopMost

= SetWindowPos(hWndXL, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

= SetWindowPos(hWndVFP, hwnd_notopmost, 0,0,0,0, swp_nosize + swp_nomove)

* Save the file.

* Set up the file name

XLFile = FULLPATH(CURDIR()) + "XLSample1.XLS"

XLFileJustName = JUSTSTEM(XLFile)

* Determine whether to use SaveAs or Save

IF oBook.Name <> XLFileJustName

* If the file already exists, delete it

IF FILE(XLFile)

ERASE (XLFile)

ENDIF

* Save it without fear of the user dialog box

oBook.SaveAs(XLFile)

ELSE

* Save it, since it's already been saved with SaveAs

oBook.Save()

ENDIF

=MessageBox("Excel file saved as:" + CHR(13) + XLFile)

oExcel.Quit()

RELEASE oExcel

USE IN OrderHistory

USE IN Customer

Figure 11. The finished Tasmanian Traders customer history workbook This example covers entering data, adding a total formula, formatting cells, setting up the sheets to print, viewing the PrintPreview, and finally saving the spreadsheet.

We ve covered a lot in this chapter, yet Excel has even more to offer. We ve worked on the mechanics of building a workbook with standard spreadsheet features; in the next chapter, we ll cover the more advanced Excel features that help your users analyze their data.