Excel-vfp-sql server combined with application examples

Say in front

I'm not a dedicated visual FoxPro programmer, and it's been more than 10 months since the last time I wrote a complete application with visual FoxPro. Three days do not practicing, three days do not say oral life, I so long time not to become a fool! A few days ago my friend asked me to posting Excel->sql server data conversion program, the warm and promised. It took two nights to jot it down, it must have been a lot of bugs. But because does not involve the trade secret, the company interest, therefore wants to take out to communicate with everybody, you do not have to laugh at me this not to flow the procedure ...

If you are interested, I will analyze all the code in the following days until I use the install Shield Express for Visual FoxPro to package it.

Pre-planning

Why use Visual FoxPro

I have two options: Use Delphi or Visual FoxPro. I chose visual FoxPro to do this project in the light of the fact that the data in Excel can be very messy, and sometimes the data in a sheet corresponds to multiple SQL Server tables, so you have to decompose and combine the data through my program. With Delphi words, will be very troublesome-many conditions to judge + a lot of loops; with Visual FoxPro in this area has the advantage, the local robust cursor system, support the traditional xbase language and standard SQL statement, who is worse than it? With my understanding of these two languages, I think: Using Visual FoxPro to complete this work is certainly much easier than Delphi!

The design idea of OOP

The friend said to me, the user's final opinion is not decided, there's always the possibility of change, so I try to use OOP to make something that's not too much changed in the base class, such as SQL Server connection, transaction management, Excel file opening, closing, and so on, for the variability of the content, I try to encapsulate the same interface in subclasses, such as reading Excel data, decomposing data, and so on.

Reading Excel data in OLE mode

This is a design that will be criticized, at least my friends have the opinion: to let customers install an Excel, too annoying it! My idea is: it should not be too difficult to install an Excel in the machine; reading Excel documents directly can reduce the user's operating burden; the amount of data per time is not too large (1000), so speed is not a problem; using OLE to read Excel files directly can make your application more powerful!

A few notes

Operating Environment

SQL Sever 7 or 2000 (both Chinese and English), Visual FoxPro 7 (English version), does not support visual FoxPro 3, 5, 6;excel 2000, or XP (Chinese and English);

Specification for Excel documents

I have three Excel documents attached to them, and they are the norm. If users have similar needs, can be based on the base class, make their own subclasses to adapt to different needs.

Use it first.

Configuring ODBC connections

This program connects to SQL Server using a connection string, and all connection information is written in the ODBC.TXT document:

Driver=sql Server; Uid=sa;database=northwind; Server=boe

Please change the connection string according to your situation.

Set up a data table

All the scripts are in the Sql.sql file and run in SQL Server's Query Analyzer.

Error

The wrong information is written in the ERROR.LOG file, which you can refer to.

Run

Inventory Data correspondence: Ck.xls

Virtual Inventory data correspondence: Virstore.xls

Payment Data correspondence: Sj.xls