Excel Files

[George]

Hi forum,

Can we read and save as DBF an excel (XLS) file, using only xHarbour + FWH, with not microsoft excel installed in the computer?

Regards,

George

[Enrico Maria Giordano]

Try something like this:

Code: Select all  Expand view

FUNCTION MAIN()

    LOCAL oCn := CREATEOBJECT( "ADODB.Connection" )

    oCn:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\xharbour\mytest.xls;Extended Properties='Excel 8.0'" )

    oCn:Execute( "SELECT * INTO [DBASE III; Database=c:\xharbour].mytest FROM [MySheet$]" )

    oCn:Close()

    RETURN NIL

Please note that the $ sign after the sheet name is required.

EMG

[R.F.]

You can also try FileXLS class, it writes direct XLS files, no need to have anything installed.

[Enrico Maria Giordano]

Ok, but George needs to read from an XLS and write to a DBF, if I understood correctly.

EMG

[George]

Enrico, Rene,

That is correct. I have to read an XLS file, created by another program, and save it as DBF in order to do some process with my xHarbour + FWH software.
By now I am using TEXCEL.PRG class. It is working fine, but as you know wee need to have Microsoft Excel installed.

BTW a do you know how to, when saving the XLS file as DBF, to have a field (in the generated DBF) as logical? Now I can have numerical and chars fields automatically created from the XLS, but not logical field.

Regards,


George

[James Bott]

George,

>BTW a do you know how to, when saving the XLS file as DBF, to have a field (in the generated DBF) as logical? Now I can have numerical and chars fields automatically created from the XLS, but not logical field.

This is a round about way, but you could process the first DBF as a temp file, and change all the data in the field you want to be logical as you copy the data to a new file.

Or, another idea just came to me, you can subclass TDatabase and modify the load method to convert the data each time a record is read. This way there is another (mock) field that is logical. Let me know if you would like an example.

James

[George]

James,

I am trying to avoid modify the DBF. I would prefer using TEXCEL class to create a logical type column in the XLS file, before saving as DBF; I don't know if that is possible.

I am glad to see you example.

Regards,


George

[James Bott]

George,

>I am trying to avoid modify the DBF. I would prefer using TEXCEL class to create a logical type column in the XLS file, before saving as DBF; I don't know if that is possible.

>I am glad to see you example.

First create a subclass of TDatabase. Lets assume your fieldname is "ACTIVE" (the one you want to be a logical). We create a new fake field ISACTIVE. I don't know what your field contains, but here lets assume it contains Y or N.

class TMyData from TDatabase
data isactive
method load
endclass

method load class TDatabase
super:load()
// Convert the real field to a logical
::isactive:= if(::ACTIVE="Y",.t.,.f.)
return self

Now to use it all you do is this:

use mydata
oMyData:= TMyData():new()

msgInfo( oMyData:isactive ) // returns .t. or .f.

Simple.

James

[James Bott]

Anyone,

Where can I get a copy of the TExcel class?

James

[Baxajaun]

James,

following the link:

http://fivetechsoft.com/forums/viewtopic.php?t=2765

Best regards,

Felix

[James Bott]

Felix,

Thanks but the link to TXLS is now dead.

James

[James Bott]

OK, I found the TXLS.ZIP file on my own disk. I was looking for TEXCEL.ZIP before.

Now I am confused. George, didn't you say that you were using TEXCEL to read an Excel file? The version I have only writes to Excel files.

James

[digicad]

I have code in VB for creating XLS file direct with FCREATE() and FWRITE()
If you interesting I can send this file for conversion to xHarbour

[George]

James

> Where can I get a copy of the TExcel class?
Sent to your email


Digicad,

Please send the VB code to georgeabinader@msn.com

Regards,

George

[jose_murugosa]

Here is it again.

http://misoft.blogspot.com/2005/05/txls.html