VFP: A small experience set for interaction with Excel

Get worksheet name

In the following example, a Microsoft Excel instance is created and a new Workbook is added. Use the foreach statement to display the name of each worksheet in the workbook. In this example, Microsoft Excel must be correctly installed on the machine where the sample runs.
Oexcel = create ("Excel. application ")
Oexcel. workbooks. Add

For each omyvar in oexcel. Sheets
? Omyvar. Name
Next omyvar

Excel report Creation

Allows FoxPro data to generate an Excel report
Visual FoxPro is one of the widely used front-end database development platforms. Therefore, we often encounter DBF data files in our daily work. However, with the popularization of office automation, more and more DBF Files need to be converted into excel electronic documents. The common conversion method is to use the copy command in FoxPro to convert the format or directly open the DBF File in Excel. Both of these methods can achieve the goal, but cannot obtain the format specification, can directly print the output report, and the output Excel file, cannot effectively convert the data type, this vulnerability is prone to data statistics errors. Is there a better way? Here is a more common method. when Excel is called in VFP, DBF Files can directly generate an Excel report in the same format as shown in figure (1 ). Excel report
The specific process is as follows:
Edit recommended articles
● Fresh access to Excel 2000 XP
● Excel 2000 experience in formula application
● Excel 2000 function application information function
Suppose we want to output the table agcallop. DBF file as an Excel file, as shown in Report 1. In order to make it more universal, the page uses A4 paper horizontal output. The default font is 10 10, and the page number is added to the footer.
Some program code is as follows:
M. outfilename = putfile ('output result', 'agcallop', 'xls ')
& Export File Name
EF = Createobject ('excel. application ')
& Call Excel programs
Ef. workbooks. Add
& Add a workbook
Ef. worksheets ("sheet1"). Activate
& Activate the first Worksheet
Ef. Visible =. T.
& Show Excel page
Ef. cells. Select
& Select the entire table
Ef. selection. Font. size = 10
& Set the default font size of the entire table to 10
Select 0
Use agcallop
& Select the exported table
Num = reccount ()
& Calculate the total number of exported records
Go top
I = 5
Ef. Range ("F1: K1"). Select
& Select the cell where the title bar is located
Ef. selection. Merge
& Merge Cells
With ef. Range ("f1 ")
& Set title and font attributes
. Value = 'workload of customer service representative statistical table'
. Font. Name = ""
. Font. size = 18
Endwith
Ef. Rows (2). rowheight = 1/0. 035
& Set the second row height to 1 cm
Ef. Range ("H2: O2"). Select
& Select the cell in the statistical condition column
Ef. selection. Merge
& Merge Cells
Ef. Range ("h2"). Font. size = 10
Ef. Range ("h2"). horizontalalignment = 4
& Set content alignment to right, 3 to center, and 2 to right
Ef. Range ("h2"). value = 'statistical time: '+ DTOC (date () + 'print Date:' + DTOC (date ())
Ef. Rows ("3:4"). Select
With ef. Selection
. Horizontalalignment = 3
& Set horizontal alignment of behaviors 3 and 4
. Verticalalignment = 2
& Vertical center
. Numberformatlocal = "@"
& Set behavior-dependent content 3 and 4
Endwith
Ef. Range ("A3: A4"). Select
Ef. selection. Merge
& Vertically merge rows 3 and 4 in the first column
Ef. Range ("A3"). value = 'employee ID'
& Set the title content of the first column
Ef. Columns ("A"). Select
& Select the entire column
Ef. selection. horizontalalignment = 3
& Horizontal center
Ef. Columns ("A: B"). Select
Ef. selection. numberformatlocal = "@"
& Set column A and column B as plain content
Ef. Range ("B3: B4"). Select
Ef. selection. Merge
& Vertically merge 3 and 4 rows in the second column
Ef. Range ("B3"). value = 'name'
& Set the title of the second column
Ef. Columns ("B"). Select
& Select the entire column
Ef. selection. horizontalalignment = 3
& Horizontal center
Ef. Range ("C3: E3"). Select
& Merge the C-E column of the third row horizontally
Ef. selection. Merge
Ef. Range ("C3"). value = 'total traffic'
& The third line mark is the column entitled "total traffic volume"
Ef. Range ("C4"). value = 'Incoming call volume'
& 1st sub-titles "Incoming call volume" under "total traffic"
Ef. Range ("D4"). value = 'call output'
& 2nd subtitles under "total traffic": "call volume"
Ef. Range ("E4"). value = 'computation'
& 3rd subtitles under "total traffic": "Total"
Ef. Range ("F3: h3"). Select
Ef. selection. Merge
Ef. Range ("F3"). value = 'total traffic time'
Ef. Range ("F4"). value = 'Incoming call time'
Ef. Range ("G4"). value = 'outbound call time'
Ef. Range ("H4"). value = 'computation'
Ef. Range ("I3: K3"). Select
Ef. selection. Merge
Ef. Range ("I3"). value = 'average time of a single traffic'
Ef. Range ("I4"). value = 'Incoming call time'
Ef. Range ("J4"). value = 'outbound call time'
Ef. Range ("K4"). value = 'computation'
Ef. Range ("L3: L4"). Select
Ef. selection. Merge
Ef. Range ("L3"). value = 'accumulative working time'
Ef. Range ("M3: M4"). Select
Ef. selection. Merge
Ef. Range ("m3"). value = 'invalid time'
Ef. Range ("N3: N4"). Select
Ef. selection. Merge
Ef. Range ("N3"). value = 'entry qty'
Ef. Range ("O3: O4"). Select
Ef. selection. Merge
Ef. Range ("O3"). value = 'validity period ratio'
Next, assign values to cells in each column of each row using the scan statement. For better printing effects, you also need to set pages, such as the title line, page center, and horizontal layout. You can also add border lines.
The above program is successfully debugged in VFP 6 + Excel 2000 + Windows 2000. You can adjust the parameter settings as needed.

The fox controls the excl file to be saved as DBF (the excl background runs)

FIL = thisform. list1.value & its value is a file name containing a path
EOLE = Createobject ('excel. application ')
EOLE. workbooks. Open (FIL) & open a file
EOLE. selection. autofilter & disable (enable if none exist) automatic filtering
EOLE. Range ("A1: f1000"). Select & select the first six columns of data from row 1st to row 1000 (only the data is stored in another table)
Asfil = strtran (upper (strtran (FIL, substr (FIL, 1, rat ('/', Fil), 'c:/Windows/desktop/cphz /')), '. XLS ','. dbf') & name of the file to be saved
If file (asfil)
Delete file & asfil
Endif
EOLE. activeworkbook. saveas (asfil, 8) & Save As DBF
EOLE. activeworkbook. Saved =. T. & do not save the current excl table
EOLE. workbooks. Close & close table
EOLE. Quit & Exit excl
Release EOLE & release Variables

Although append from tx.xls type xl8 can be used to append data more easily, it sometimes causes the program to exit illegally and incorrectly (I wonder if it is because of invalid characters), and there are some restrictions.

Practical Interactive Programming of VFP and Excel
Min Yimin

Abstract: This article introduces several practical interactive programming methods and examples of VFP and Excel. It combines the powerful data processing functions of VFP with the excellent tabulation functions of Excel, the application effect of the software is greatly enhanced.
Keywords: VFP, Excel, DDE, Ole, interactive programming.
Some cross_programming methods between VFP and Excel
Zou yiming (Gansu Lanzhou Section of instruments Lanzhou Petrochemical Staff College)
Abstract: The author introduces us in this paper some practical methods and instance of cross_programming between VFP and excel. it extremely improves their performances by combining VFP's data processing functions with Excel's excellent tabulation functions.
Key words: VFP, Excel, Ole, DDE, cross-Programming

VFP (Visual FoxPro) is a relational database management system. Thanks to its powerful data processing capabilities and good compatibility, it has become a powerful tool for database application developers, execl is an excellent spreadsheet processing software with unique advantages in terms of compatibility, operation interface, formula calculation, and charts. These two types of software have been widely used in their respective application fields. At the same time, these two types of software also have good interactive programming capabilities, laying a good foundation for the two to complement each other and learn from each other.
This article will introduce several VFP and Excel interactive programming methods based on specific examples. Although each method has different implementation methods, its functions are similar. Its functions are described as follows: VFP data table "Student renewal table. DBF contains "Student name", "Chinese", and "Mathematics "...... and other fields, the sample program will interact with each other from the Excel Workbook "VFP. in the worksheet "query" of xls ", you can use the unit specified by the bookmarked" Course name "to obtain the name of the course to be queried, and then obtain the" Student renewal table "in the data table. DBF automatically searches to find the list of students who failed the course, and finally transfers it to the original Excel worksheet to form a new work form. At the same time, the new worksheet is also named after this course.
The following programs are successfully debugged in vfp5.0 and excel97. Due to space limitations, the error handling section in the program is omitted.

1. execl-driven VFP
Execl's built-in VBA language (Visual Basic for Application) provides a convenient way to expand execl functions. You can use this language to directly drive VFP to complete data retrieval and other functions.
The program first generates a VFP object, and then runs the VFP fumbling command string using the docmd method of VFP. Then, the result is copied to the clipboard using the datetoclip method of VFP, finally, VBA paste it to the correct position of the worksheet.
Sub foxtest ()

Dim ofox as object
Dim slesson as string
Dim scommand as string

Set ofox = Createobject ("visualfoxpro. application") 'Start VFP and generate VFP objects
Sheets ("query"). Select
Slesson = range ("Course name") 'get the name of the course to be queried in the cell named "Course name"
Sheets. add' to generate a new work form
Activesheet. Name = slesson 'specifies that the name of the work form is the same as the course name

Scommand = "select student ID, Chinese, mathematics from D:/VFP/student orders table where" + slesson + "<60 into cursor Temp" 'to form a VFP query command string
Ofox. docmd scommand run the VFP command string
Ofox. datatoclip "Temp", 3' copy the search result to the clipboard in text format
Range ("A1: A1"). Select 'points to the Unit in the upper-left corner of the target region to be copied.
Activesheet. paste the search result

End sub

For ease of use, the author made a toolbar named "Search" and a button named "Start search" in execl, and associated the above macro program segment with the self-made button, click this button to run the program and obtain the required data in Excel.
You can create a toolbar and a button as follows:
1. Select "Tools"/"Custom" to display the custom dialog box;
2. Select the "toolbar" Page and press the "new" button;
3. In the Toolbar Dialog Box, enter "Search" as the name of the new toolbar;
4. select the "command" Page, select "macro" in the "category" list, select "Custom button" in the "command" list, and drag it to the new "Search" toolbar;
5. press the "update selected content" button, first fill in the button name "Start search" in the "name" column, and then select "macro ", select the macro program foxtest () in the subsequent "" list to specify a toolbar button for the macro.

Ii. Use the OLE function to drive execl in VFP
Ole (Object Linking and Embedding) is an effective method for transferring and sharing data between Windows applications. With the help of OLE, VFP can not only share the data of other applications, but also directly control the running of other applications in object mode, thus further expanding the VFP function. VFP allows you to directly create, use, and control OLE objects in a program to automate Ole. As an ole client VFP and excel as an OLE server, it has a good programming interface. The following sections use OLE to implement the required functions.
The program first generates an Excel OLE object oleapp to operate on it, then uses the OLE function to obtain the course name to be queried from the Excel form, and controls EXCEL to generate a new worksheet, the query results of VFP are still transmitted to the Excel worksheet using the clipboard.

Oleapp = Createobject ("Excel. application") & Open EXCEL to generate an OLE object
Oleapp. application. Caption = "VFP interactive programming" & specify the title bar name
Oleapp. application. Visible =. T. & set to excel visible
Oleapp. application. workbooks. Open ("D:/VFP/vfps.xls") & open an Excel Workbook

Do while. T.
With oleapp. Application
Nanswer = MessageBox ("start searching? ", 32 + 4," Search for specified data ") & generate information box
If (. Not. (nanswer = 6) & if you press the "yes" button, start searching. Otherwise, exit.
Exit
Endif

. Sheets ("query"). Select & select the "query" work form
Slesson = oleapp. application. Range ("Course name"). Value & get the name of the course to be queried
. Sheets. Add & create a work form
. Activesheet. Name = slesson & specify the name of the work form
Scommand = "select student ID, Chinese, mathematics from D:/VFP/ where" + alltrim (slesson) + "<60 into cursor Temp" & create a VFP query command string
& Scommand & execute VFP command string
_ VFP. datatoclip ("Temp", 3) & copy the search result to the clipboard in text format
. Range ("A1: A1"). Select & point to the Unit in the upper left corner of the target region
. Activesheet. paste & Paste search results
Endwith
Enddo

Oleapp. Quit & close Excel and save the updated workbook File

****************************************
Objexcel. activeworkbook. Close (. F .)
Close excel ~~ Not a storage disk ~ If it is disabled, the disk cannot be saved.

Set print parameters:

Q: How can I select a printer when I use Excel for report printing? Which of the following are the parameters of. activewindow. selectedsheets. printout?
 
A:
Expression. printout (from, to, copies, preview, activeprinter, printtofile, collate, prtofilename)

Expression is required. This expression returns an object "applied" to the list.

From variant type, optional. The start page number. If this parameter is omitted, it is printed from the starting position.

To variant type. Optional. The ending page number. If this parameter is omitted, it is printed to the last page.

Optional, copies variant type. The number of copies to print. If this parameter is omitted, only one copy is printed.

Preview variant type. Optional. If it is true, Microsoft Excel prints and previews the specified object before printing. If this parameter is set to false or omitted, the object is printed immediately.

Optional, activeprinter variant type. Set the name of the active printer.

Printtofile variant type. Optional. If it is true, the output is printed to the file. If prtofilename is not specified, Microsoft Excel prompts you to enter the file name to be output.

Collate variant type. Optional. If it is true, each copy is printed one by one.

Prtofilename (optional) is of the variant type. If you set printtofile to true, this parameter specifies the file name to be printed.

Description

The "page" described by the from parameter and to parameter refers to the page to be printed, not all pages in the worksheet or workbook.

How can I open an Excel file with a password by programming?

Problem:
The password is added to the Excel file. how to program the password in VFP to open the Excel file
For example:
EOLE = Createobject ('excel. application ')
EOLE. workbooks. Open ("D:/22.xls ")
In this case, a Password dialog box is displayed. You need to enter the password to open the file.
How can I open an Excel file with a password by programming?

A:
 
Oele. workbooks. Open ("D:/22.xls",. F.,. F.," 123 "," 456 ")
"123" indicates the password to open the permission,
"456" indicates the password for permission modification.

----------------------------------------------

Question 1: how to insert an image with a known path to an Excel file.

A:
Oexcel1.activesheet. Pictures. insert ("Image File Name ")

----------------------------------------------

Question 2: Can I specify to place an image in a certain position or area and control the image display size?
 
A:
Oleapp. Sheets (1). Select
Oleapp. Range ("location"). Select
Oleapp. activesheet. Pictures. insert ("Image File Name"). Select
Oleapp. selection. shaperange. lockaspectratio =. T.
Oleapp. selection. shaperange. Height = 57

----------------------------------------------

Q: If a field of type "" is output to excel, the previous zero is automatically removed. For example, if "009877" is changed to "9877", how can this problem be solved?

A:
1. Add a single quotation mark (') to the front of the string.

2.
EOLE. Range ("A1: Y1"). Select
EOLE. selection. numberformatlocal = "@"

----------------------------------------------

Insert rows and columns in Excel:
 
Objexcel. Rows (1). insert () & Insert a row before the first row
Objexcel. columns (2). insert () & Insert a column before the second column

----------------------------------------------

Obtain the number of Excel records

Loxls = Createobject ("Excel. application ")
Bookexcel = loxls. application. workbooks. Open ("F:/pz.xls ")
Nrows = bookexcel. worksheets ('pz ')
Usedrange = nrows. usedrange
R = usedrange. Rows. Count & number of rows
C = usedrange. Columns. Count & Columns

----------------------------------------------

Q: How can I automatically execute a macro command when opening a file in Excel.

A:
You only need to name the macro auto_open.
If you want to run the command when the file is closed, it is named auto_close.

----------------------------------------------

Q: Can I paste Excel files in VFP?

. Range ("A2"). pastespecial ("xlpastevalues") & error

. Range ("A2"). pastespecial (3) & error

. Range ("A2"). pastespecial & I have to wait.

How can I only paste values?
 
========================================================== ==================

A:

Xlpastevalues =-4163
Oexcel. Range ("A1"). Select
Oexcel. selection. Copy
Oexcel. Range ("B1"). _ pastespecial (xlpastevalues)

Note: In Excel, "xlpastevalues" is a constant with a numeric value:-4163

1. Create an Excel Object

EOLE = Createobject ('excel. application ′)

2. Add a new workbook

EOLE. workbooks. Add

3. Set 3rd worksheets to activate worksheets.

EOLE. worksheets ("sheet3"). Activate

4. Open a specified workbook

EOLE. workbooks. Open ("C:/temp/ll.xls ″)

5. display the Excel window

EOLE. Visible =. T.

6. Change the Excel title bar

EOLE. Caption = "VFP Application calls Microsoft Excel ″

7. assign values to cells

EOLE. cells (). value = xm (XM is the database field name)

8. Set the width of the specified column (unit: number of characters)

EOLE. activesheet. Columns (1). columnwidth = 5

9. Set the height of the specified row (unit: lbs)

EOLE. activesheet. Rows (1). rowheight = 1/0. 035

(Set the Row Height to 1 cm, 1 lb = 0.035 cm)

10. Insert a paging character before the first row

EOLE. worksheets ("sheet1"). Rows (18). pagebreak = 1

11. Delete the paging character before the 4th column

EOLE. activesheet. Columns (4). pagebreak = 0

12. Specify the border line width (borders parameters are as follows)

Ole. activesheet. Range ("B3: D3"). Borders (2). Weight = 3

13. Set the four border line types

EOLE. activesheet. Range ("B3: D3"). Borders (2). linestyle = 1

(Borders parameters: 1-left, 2-right, 3-top, 4-bottom, 5-oblique, 6-oblique/; linestyle values: 1 and 7-thin, 2-thin, 4-dotted, 9-double thin)

14. Set the header

EOLE. activesheet. pagesetup. centerheader = "Report 1 ″

15. Set footer

EOLE. activesheet. pagesetup. centerfooter = "Page & P ″

16. Set the header to the top margin to 2 cm

EOLE. activesheet. pagesetup. headermargin = 2/0. 035

17. Set the footer margin to 3 cm

EOLE. activesheet. pagesetup. footermargin = 3/0. 035

18. Set the top margin to 2 cm.

EOLE. activesheet. pagesetup. topmargin = 2/0. 035

19. Set the bottom margin to 4 cm.

EOLE. activesheet. pagesetup. bottommargin = 4/0. 035

20. Set the left margin to 2 cm

Veole. activesheet. pagesetup. leftmargin = 2/0. 035

21. Set the right margin to 2 cm

EOLE. activesheet. pagesetup. rightmargin = 2/0. 035

22. Set page horizontal center

EOLE. activesheet. pagesetup. centerhorizontally =. T.

23. Set the vertical center of the page

EOLE. activesheet. pagesetup. centervertically =. T.

24. Set the page size (1-narrow Row 8511 39-Wide Row 1411)

EOLE. activesheet. pagesetup. papersize = 1

25. Print the cell network cable

EOLE. activesheet. pagesetup. printgridlines =. T.

26. Copy the entire Worksheet

EOLE. activesheet. usedrange. Copy

27. Copy a specified region

EOLE. activesheet. Range ("A1: E2"). Copy

28. Paste

EOLE. Worksheet ("sheet2"). Range ("A1"). pastespecial

29. Insert a row before row 2nd

EOLE. activesheet. Rows (2). insert

30. Insert a column before Column 2nd

EOLE. activesheet. columns (2). insert

31. Set Font

EOLE. activesheet. cells (2, 1). Font. Name = "″

32. Set the font size

EOLE. activesheet. cells (1, 1). Font. size = 25

33. Set the font to italic

EOLE. activesheet. cells (1, 1). Font. italic =. T.

34. Set the font of the entire column to bold.

EOLE. activesheet. Columns (1). Font. Bold =. T.

35. Clear cell Formula

EOLE. activesheet. cells (1, 4). clearcontents

36. Print the preview Worksheet

EOLE. activesheet. printpreview

37. Print the output Worksheet

EOLE. activesheet. Printout

38. The worksheet is

EOLE. activeworkbook. saveas ("C:/temp/22.xls ″)

39. Discard the disk

EOLE. activeworkbook. Saved =. T.

40. Close the workbook

EOLE. workbooks. Close

41. Exit Excel

EOLE. Quit