How can I use ODBC to populate an xls file with data? 1

[MrDong]

Hello All
I would like to put data out of a .dbf table into an Excel sheet over ODBC. I am able to send values with the following code... so far so good. But actually I would like to send the result of an array or an sql query. My feeling says it should be possible but I can't figure out how. I would be very glad for hints.

STORE 0 TO lnConHand, lnSQLRes
strforconn="Driver={Microsoft Excel Driver (*.xls)};" ;
+"DriverId=790;" ;
+"Dbq=C:\Documents and Settings\Desktop\testio.xls;" ;
+"ReadOnly=0;" ;
+"DefaultDir=C:\Documents and Settings\Desktop;"

lnConHand = SQLSTRINGCONNECT(strforconn)

IF lnConHand>0
ret=SQLEXEC(lnConHand, "INSERT INTO [sheet1$] (Date, Number) VALUES ('01.01.2002', 100.2)")
IF ret<0
MESSAGEBOX(areeor(ret)
endif
ELSE
MESSAGEBOX("Could not establish ODBC connection for .xls handling")
ENDIF

lnSQLRes=SQLDISCONNECT(lnConHand)

 

[Mike Lewis]

Mr Dong,

There's a much easier way. Instead of using ODBC and SQL pass-through, just export the table (or query result) to Excel:

USE MyTable
COPY TO TestIO.xls TYPE XL5 FIELDS Date, Number

To do the same with a query result or an array, just copy the data to a temporary table first.

Of course, you might have a special reason for using SQL pass-through, but generally the above technique is simpler and faster.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[MrDong]

Hi Mike
Thanks for this. I am aware of this way to handle the "problem" however I don't think it is the most elegant solution in my case. I realy would like to have a passthrough to Excel. The point is that one of our departments wants to have excel as output and the data is quite diverse. So that is the main reason why I don't want to have a temporary table.
Of course if I can't avoid it I will come back to your solution.
Thanks a lot
Dong

 

[baltman]

In my opinion, this is the most elegant solution. It is certainly the fastest.

You could easily change the logic to format cell by cell depending on data in each cell. Prepare the spacing and columns in the VFP table ahead of time.

Some minor formating, such a bolding headers, is easiest to apply via automation.

Export a Formatted Table to Excel using HTML faq184-4704

Some other faqs dealw with multiple tables -> workbooks via html as well.

Don't forget to set printarea etc using fcount() and reccount().

e.g.
nCol = fcount()
cRange = "A1:"+IIF(nCol<=26,CHR(nCol+64),CHR(INT((nCol-1)/26)+64)+CHR(INT(MOD((nCol-1),26))+65))+TRANSFORM(RECCOUNT())

Brian

Brian

 

[MrDong]

Well
I think to bottom of this question is how to get a Foxpro array into an Excel Range.
This is what I tried up to now:
- Loop through the arrays and paste value by value to Excel.
--> very slow
- Put everything into strings, put them to _cliptext and paste to excel.
--> fast enough but no more datatypes
- I know also about the macro stuff but there seem to be problems with finding the VBA macro and I do not want to leave the vfp enviroment if I don't have to.

So I thought I might try to select directly into excel.
This would be a solution that would solve most of my problems in that area.

I mean if it is not possible to do that way I give your sugestions a try.. especially the html one sound appealing although there is quite an overhead for achieving the result.

Please understand me right I am simply looking for the perfect solution to my problem because I am on that quite some time now.

Thanks

Dong

 

[baltman]

You'll find the HTML has less overhead than any Excel based solution. The slowest portion is when Excel is interpreting the HTML formatting, but it is still faster than cell/range formatting directly into Excel.

VFP can write large text files very quickly using textmerge.

The solution was table based, but would work equally well on an array.

I banged my head on this foy months/years and this is the fastest export to Excel with formatting solution I could come up with.

Good luck,
Brian

 

[MrDong]

Thanks a lot
Then I go with that.
Best
Dong

 

[ramani]

I generally provide a button to my report forms to export the results to excel format.

The reports are pulled into a cursor and stays as default alias. Or open a table and leave it as default alias.

Call the following code saved as a prg

Example
* lcExcelFile = name of Excel file to create
* The created file is kept in myDocuments

lcExcelFile = [myFile.xls]
USE myTable ALIAS myTable
=VfpExcel()


VfpExcel.prg
*!****************************************************************************!*
*!* Beginning of program VFPExcel.prg *!*
*!****************************************************************************!*

PARAMETER lnPaperOrientation

*!* 1 = letter size paper, portrait orientation (1,1) *!*
*!* 2 = letter size paper, landscape orientation (1,2) *!*
*!* 3 = legal size paper, portrait orientation (5,1) *!*
*!* 4 = legal size paper, landscape orientation (5,2) *!*

*!* The following line of code sets a default lnPaperOrientation value of 1 *!*
*!* where no parameter is passed *!*
lnPaperOrientation = ;
IIF(TYPE("lnPaperOrientation") = "L", 1, lnPaperOrientation)

*!* The following code sets the paper size and orientation variables based *!*
*!* on the lnPaperOrientation value *!*
DO CASE

CASE lnPaperOrientation = 2
lnPaperSize = 1
lnPrintOrientation = 2

CASE lnPaperOrientation = 3
lnPaperSize = 5
lnPrintOrientation = 1

CASE lnPaperOrientation = 4
lnPaperSize = 5
lnPrintOrientation = 2

OTHERWISE

lnPaperSize = 1
lnPrintOrientation = 1

ENDCASE

*!* The following code determines whether or not there is a table open in *!*
*!* the currently selected work area. *!*
lcTableAlias = ALIAS()
IF EMPTY(lcTableAlias)
=MESSAGEBOX("A table must be open in the currently selected work area" + ;
CHR(13) + "in order for this program to work.")
RETURN && If no table is open, then return *!*
ENDIF

*!* The following code determines the derived Excel file name and location. *!*

The next two lines are reserved and the lcExcelFile is passed as a variable.


* lcTablePath = LEFT(DBF(), RAT("\", DBF()))
* lcExcelFile = lcTablePath + lcTableAlias + ".xls"

IF FILE(lcExcelFile) && If a file by the derived name already *!*
*!* exists in the derived location *!*
** lcMessageText = "An Excel file by the name of " + lcTableAlias + ;
** ".xls" + CHR(13) + "already exists at location:" + CHR(13) + ;
** "Do you want to delete it now and replace it?"
** lnDialogType = 4 + 32 + 256
** lnFirstWarning = MESSAGEBOX(lcMessageText, lnDialogType)
** IF lnFirstWarning = 6 && User responds with a "Yes" *!*
ERASE (lcExcelFile) && Erase the existing file *!*
** ELSE
** RETURN
** ENDIF
ENDIF

*!* The following code determines the selected range of the print area for *!*
*!* the derived Excel file. This is based on the number of fields in the *!*
*!* source table (columns) and the number of records in the source table *!*
*!* (rows). A range of three rows is added to the number of records. This *!*
*!* allows for the following: *!*
*!* One row is added by the COPY TO process to hold the names of the fields. *!*
*!* One row is inserted as a spacer between the field names and the first *!*
*!* row of data. *!*
*!* One row is added to the bottom to contain a SUM function for numeric, *!*
*!* integer, and/or currency data. *!*
lcTotalRangeExpr = ;
["A1:] + ColumnLetter(FCOUNT()) + ALLTRIM(STR(RECCOUNT() + 3)) + ["]
lcTotalPrintArea = ;
["$A$1:$] + ColumnLetter(FCOUNT()) + [$]+ALLTRIM(STR(RECCOUNT() + 3)) + ["]

*!* The following code will erase any previously created temporary excel *!*
*!* file created by this program *!*
ERASE HOME() + "VFP_to_Excel.xls"

*!* The following code creates the temporary Excel file that will be used *!*
*!* for the derived Excel file *!*
COPY TO HOME() + "VFP_to_Excel" TYPE XL5

*!* The following code commences the OLE Automation process. *!*
oExcelObject = CREATEOBJECT('Excel.Application')

*!* The following code opens the "VFP_to_Excel" file that was created by the *!*
*!* "COPY TO" command *!*
oExcelWorkbook = ;
oExcelObject.Application.Workbooks.Open(HOME() + "VFP_to_Excel")

*!* The following code activates the Worksheet which contains the "COPY TO" *!*
*!* data *!*
oActiveExcelSheet = oExcelWorkbook.Worksheets("VFP_to_Excel").Activate

*!* The following code establishes an Object Reference to the "VFP_to_Excel" *!*
*!* worksheet *!*
oExcelSheet = oExcelWorkbook.Worksheets("VFP_to_Excel")

WAIT WINDOW "Developing Microsoft Excel File..." + CHR(13) + "" + CHR(13) + ;
"Passing formatting information to Excel." + CHR(13) + "" NOWAIT

*!* The following code selects row 2 and then inserts a row that will serve *!*
*!* as a spacer between the field names and the first row of data. *!*
oExcelSheet.Rows("2:2").Select
oExcelSheet.Rows("2:2").Insert

*!* The following code sets font attributes of row 1 (the field names). *!*
oExcelSheet.Rows("1:1").Font.Name = "Arial"
oExcelSheet.Rows("1:1").Font.FontStyle = "Bold"
oExcelSheet.Rows("1:1").Font.Size = 8

*!* The following code creates an array using the AFIELDS() Function. This *!*
*!* array will provide information pertaining to the data type, width, and *!*
*!* number of decimal places for each field of the source table. *!*
lnFields = AFIELDS(laFields)

*!* The following code in the FOR loop will be processed for each field in *!*
*!* the source table. *!*
FOR iField1 = 1 TO lnFields

*!* The following line of code uses a Procedure (ColumnLetter) that is *!*
*!* contained in this program. This procedure will return a *!*
*!* corresponding Excel Column (letter) reference that must be used in *!*
*!* passing any cell or column specific formatting or information to *!*
*!* Excel. *!*
lcColumn = ColumnLetter(iField1)

*!* The following code creates strings of information in a format *!*
*!* required by Excel for the processing of commands that are specific to *!*
*!* rows, columns, and/or cells. For example, in order to SELECT a range *!*
*!* of cells from the third field of a 62 record table, you must bear the *!*
*!* following in mind: *!*
*!* 1. The top 2 rows consist of the field names and then a spacer row *!*
*!* between that and the top data. *!*
*!* 2. On account of the above, the data will start at row 3 and end at *!*
*!* row 62 + 2. *!*
*!* 3. Also on account of the above, any added numeric calculation must *!*
*!* be contained at row 62 + 3. *!*
*!* So, in order to pass the cell to contain a calculation for column 3, *!*
*!* you must pass (with the quotes) "C65" The range of cells for the *!*
*!* calculation must be passed (with the quotes) as "C3:C64" Lastly, the *!*
*!* string to pass to Select column 3 (with the quotes) as "C:C" *!*
*!* Therefore, this program builds these strings out and stores them to *!*
*!* variables for Macro Substitution so that the literal string contains *!*
*!* quotes for passing the information to Excel. *!*
lcCellForCalcuation = ;
["] + lcColumn + ALLTRIM(STR(RECCOUNT() + 3)) + ["]
lcCalculationRange = ;
lcColumn + [3:] + lcColumn + ALLTRIM(STR(RECCOUNT() + 2))
lcColumnExpression = ;
["] + lcColumn + [:] + lcColumn + ["]
oExcelSheet.Columns(&lcColumnExpression.).Select

*!* The following code checks for the data type of the source Visual *!*
*!* FoxPro table by referencing the array created earlier in the program. *!*
*!* Depending upon the data type, a literal format expression is built to *!*
*!* contain quotes and is later passed to Excel by Macro Substituted *!*
*!* reference (i.e. an ampersand [&] followed by a period [.] terminator).*!*
DO CASE

CASE (laFields(iField1,2)$"C.L") && Is the field data type Character *!*
*!* or Logical *!*
lcFmtExp = ["@"] && Pass Character formatting *!*

CASE (laFields(iField1,2)$"N.I.Y")&& Is the field data type Numeric, *!*
*!* Integer, or Currency *!*
IF (laFields(iField1,2)$"Y") && If it is Currency *!*
*ramani Modified to take care of $ not appearing in exports
** lcFmtExp = ["$#,##0.00"] && Pass Currency Formatting *!*
lcFmtExp = ["#,##0.00"] && Pass Currency Formatting *!*
*!* with a comma separator *!*
ELSE && If it is other than Currency *!*
IF laFields(iField1,4) = 0 && If the Decimal Width is *!*
*!* zero *!*
lcFmtExp = ["0"] && Pass Numeric formatting *!*
*!* with no decimals *!*
ELSE && Otherwise *!*
*!* Build a format string containing the appropriate number *!*
*!* of decimals *!*
lcFmtExp = ["0.] + REPLICATE("0", laFields(iField1,4)) + ["]
ENDIF
ENDIF

CASE (laFields(iField1,2)$"D.T") && Is the field data type Date or *!*
*!* DateTime *!*
lcFmtExp = ["mm/dd/yy"] && Pass Date formatting *!*

ENDCASE

*!* The following code passes the derived format expression to Excel *!*
oExcelSheet.Columns(&lcColumnExpression.).NumberFormat = &lcFmtExp.

*!* If the field data type is Numeric, Integer, or Currency, will add a *!*
*!* calculation to the cell immediately below the last row containing *!*
*!* data. *!*
IF (laFields(iField1,2)$"N.I.Y") && Is the field data type Numeric, *!*
*!* Integer, or Currency *!*
oExcelSheet.Range(&lcCellForCalcuation.).Value = ;
[=SUM(&lcCalculationRange.)]
IF (laFields(iField1,2)$"N.I") && Is the field data type *!*
*!* Numeric or Integer *!*
oExcelSheet.Range(&lcCellForCalcuation.).Select

*!* The following code will format the cell containing the *!*
*!* calculation to have a comma separator. This process was *!*
*!* already done for any event where the field data type was *!*
*!* currency. *!*
lcCalculationFormat = ["#,##0] + IIF(laFields(iField1,4) > 0, [.] +;
REPLICATE("0", laFields(iField1,4)), []) + ["]
oExcelSheet.Range(&lcCellForCalcuation.).NumberFormat = ;
&lcCalculationFormat.
ENDIF
ENDIF

ENDFOR

*!* Once the data has been formatted and any calculation have been added, *!*
*!* the file is ready for the application of final formatting, autofitting *!*
*!* of cells, and the setting of print attributes. *!*
WAIT WINDOW "Developing Excel File Report" + CHR(13) + "" + CHR(13) +;
"setting print area and final formatting" NOWAIT
oExcelSheet.Cells.Select
oExcelSheet.Cells.EntireColumn.AutoFit
oExcelSheet.Range(&lcTotalRangeExpr.).Select

*!* IMPORTANT NOTE - POSSIBILITY OF PAGE SETUP OBJECT UNAVAILABLE ERRORS IF *!*
*!* THIS PROGRAM IS RUN ON A MACHINE WITH NO REGISTERED *!*
*!* PRINTER DEVICE. *!*
*!* *!*
*!* The following code section performs operations that are offered in the *!*
*!* "Page Setup" user interface of Microsoft Excel. If this program is run *!*
*!* from a computer where no printer driver is installed (it can be off line *!*
*!* or online or disconnected, but the printer driver software must be *!*
*!* installed and a registered printer device must be available as a *!*
*!* printer), then this section may produce errors than can be ignored. *!*
WITH oExcelSheet.PageSetup

*!* This area sets to Title Rows of the spreadsheet that will be printed *!*
*!* on each page. Since this example contains the table field names on *!*
*!* the top row, and then an empty row of cells that was inserted by this *!*
*!* program, then we will set row 1 through row 2 as the title rows. *!*
.PrintTitleRows = "$1:$2"
*!* Setting Title Columns would work in similar fashion to Setting Title *!*
*!* Rows. Here, however, the column letter would be used in syntax *!*
*!* similar to the above example. Here, however, a null string is *!*
*!* passed. This example simply shows that the option is available. *!*
.PrintTitleColumns = ""
.PrintArea = &lcTotalPrintArea. && The print area is set *!*
.LeftHeader = lcExcelFile && The left header is populated *!*
*!* with the file name *!*
.CenterHeader = "" && The Center Header and the ... *!*
.RightHeader = "" && Right Header are left blank *!*
*!* The below referenced "cStamp" is a procedure contained in this *!*
*!* program. It builds out a string which contains the computer system *!*
*!* date and time on which the resulting Excel file was created. *!*
.LeftFooter = cStamp() && Left Footer is populated with *!*
*!* cStamp returned string *!*
.RightFooter = "Page &P of &N" && Right Footer is populated with *!*
*!* Page _ of _ *!*
.CenterHorizontally = .T. && Print area centered horizontally *!*
.CenterVertically = .F. && Print area not centered *!*
*!* vertically *!*
.Orientation = lnPrintOrientation && The parameter derived print *!*
*!* orientation is set *!*
.Papersize = lnPaperSize && The parameter derived paper size *!*
*!* is set *!*
.Zoom = .F. && The "Adjust to" scaling is *!*
*!* suppressed *!*
.FitToPagesWide = 1 && The scaling of "Fit To" and 1 *!*
*!* page wide is selected *!*
.FitToPagesTall = 99 && The scaling of "Fit To" and 99 *!*
*!* pages tall is selected *!*
*!* NOTE: This will not cause a *!*
*!* small file to span 99 pages, but *!*
*!* it would cause a smaller file to *!*
*!* be compressed. *!*

ENDWITH

*!* The following code selects the upper left cell of the derived Excel *!*
*!* file *!*
oExcelSheet.Range("A1").Select

*!* The following code saves the derived Excel file to its assigned name and *!*
*!* location *!*
oExcelWorkbook.SaveAs(lcExcelFile)

** =MESSAGEBOX("Your Excel File is Ready!",64)

*!* The following code turns the OLE instance of Excel visible *!*
oExcelObject.Visible = .T.

*!****************************************************************************!*
*!* End of program VFPExcel.prg *!*
*!****************************************************************************!*


*!****************************************************************************!*
*!* Beginning of PROCEDURE ColumnLetter *!*
*!* This procedure derives a letter reference based on a numeric value. It *!*
*!* uses the basis of the ASCII Value of the upper case letters A to Z (65 *!*
*!* through 90) to return the proper letter (or letter combination) for a *!*
*!* provided numeric value. *!*
*!****************************************************************************!*

* PROCEDURE ColumnLetter
*
* PARAMETER lnColumnNumber
*
* lnFirstValue = INT(lnColumnNumber/27)
* lcFirstLetter = IIF(lnFirstValue=0,"",CHR(64+lnFirstValue))
* lcSecondLetter = CHR(64+MOD(lnColumnNumber,26))
*
* RETURN lcFirstLetter + lcSecondLetter

** Modified by ramani

PROCEDURE ColumnLetter

PARAMETER lnColumnNumber

lnFirstValue = INT((lnColumnNumber-1)/26)
lcFirstLetter = IIF(lnFirstValue=0,"",CHR(64+lnFirstValue))
lcSecondLetter = CHR(65+MOD(lnColumnNumber-1,26))

RETURN lcFirstLetter + lcSecondLetter




*!****************************************************************************!*
*!* End of procedure ColumnLetter *!*
*!****************************************************************************!*


*!****************************************************************************!*
*!* Beginning of PROCEDURE cStamp *!*
*!* This procedure derives a text representation of the system date and time *!*
*!* in the form of: *!*
*!* 01/01/2000 11:59:00 would be rendered as: *!*
*!* Saturday, January 1, 2000 @ 11:59 am *!*
*!****************************************************************************!*

PROCEDURE cStamp

cDTString1 = CDOW(DATE()) + ", "
cDTString2 = CMONTH(DATE()) + " "
cDTString3 = ALLTRIM(STR(DAY(DATE()))) + ", "
cDTString4 = ALLTRIM(STR(YEAR(DATE()))) + " @ "
cDTString5 = IIF(VAL(LEFT(TIME(), 2)) > 12, ;
ALLTRIM(STR(VAL(LEFT(TIME(), 2)) - 12)) +;
SUBSTR(TIME(), 3, 3), LEFT(TIME(), 5))
cDTString6 = IIF(VAL(LEFT(TIME(),2))=>12,"pm","am")
cDTString = "Created on " + cDTString1 + ;
cDTString2 + cDTString3 + cDTString4 + cDTString5 + cDTString6

RETURN cDTString

*!****************************************************************************!*
*!* End of procedure cStamp *!*
*!****************************************************************************!*



Note the code is downloaded and majority is not mine. I did tinger with it to suit my convenience and also removed some errors in the original code. Thus the reference is lost in the following code. However the credit goes to the orginal author. It is unfortunate that I could not immediately provide the authors name. Sorry about that. Probably, if my memory is correct it was lifted from a Microsoft Knowledge base location.


And Brian, thanks for sharing your code under FAQ. My star to you. (Somehow, I had not seen that earlier).

ramani


____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[ramani]

Brian, thanks for sharing your code under FAQ. Somehow, I did not see that earlier. My star to you.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[baltman]

Thank you. Besides being fast, added bonuses of this approach is that it overcomes Excel row limits (with 2007 it'll export 1,048,576 rows), and it exports memo fields.

It can also be combined easily with the strategy outlined in Export (groups of) DBFs to Excel Workbook QUICKLY faq184-3005

Brian