DC_WorkArea2Excel() size limit

[Tom]

but can open DBF file direct in Excel and work with it

Never. Nevernevernever. Don't tell your customers to do this. Tell them to make a damned copy. If someone "works" with your DBFs in Excel, he will kill your app.

[Victorio]

Tom: You're right...
but it is client, with special requirements , and his database is extremely large in compare with other clients.
my app use about 50 other clients and they do not need this, and I do not want work several days, weeks on changes for only his special requirements ,

[Victorio]

I want use this :

#Pragma Library("ASCOM10.LIB")
*#Pragma Library("DCLIPX.LIB") // I have it in xpj
*#Pragma Library("DCLIP1.LIB") // I have it in xpj

* one type command for generating XLS file

#COMMAND COPY TO <(file)> TYPE XLS ;
[FIELDS <flds,...>] ;
[ FOR <for>] ;
[ WHILE <whl>] ;
[ NEXT <nxt>] ;
[RECORD <rcd>] ;
[ <rst: REST>] ;
[ VIA <dbe>] ;
[ ALL ] ;
=> _dbExport( DC_Path(AppName(.t.)) + '_TempData', { <(flds)> }, __EBCB(<for>), __EBCB(<whl>), <nxt>, <rcd>, <.rst.>, <dbe> ) ;
;M->_oExcel := CreateObject("Excel.Application");
;M->_oExcel:DisplayAlerts:=.f.;
;M->_oBook:= M->_oExcel:workbooks:Open(DC_Path(AppName(.t.))+'_TempData.dbf');
;M->_oBook:SaveAs(<(file)>+".xls", -4143 );
;M->_oExcel:Quit();
;M->_oExcel:Destroy();
;Ferase(DC_Path(AppName(.t.))+'_TempData.Dbf')
*

* two type command for generating ? maybe XLSX file with code 51 in SaveAs

#COMMAND COPY_XLSX TO <(file)> TYPE XLSX ;
[FIELDS <flds,...>] ;
[ FOR <for>] ;
[ WHILE <whl>] ;
[ NEXT <nxt>] ;
[RECORD <rcd>] ;
[ <rst: REST>] ;
[ VIA <dbe>] ;
[ ALL ] ;
=> _dbExport( DC_Path(AppName(.t.)) + '_TempData', { <(flds)> }, __EBCB(<for>), __EBCB(<whl>), <nxt>, <rcd>, <.rst.>, <dbe> ) ;
;M->_oExcel := CreateObject("Excel.Application");
;M->_oExcel:DisplayAlerts:=.f.;
;M->_oBook:= M->_oExcel:workbooks:Open(DC_Path(AppName(.t.))+'_TempData.dbf');
;M->_oBook:SaveAs(<(file)>+".xlsx",51 );
;M->_oExcel:Quit();
;M->_oExcel:Destroy();
;Ferase(DC_Path(AppName(.t.))+'_TempData.Dbf')
*


* and in function use :

* this is ok, but generating XLS file
DC_WorkArea2Excel(suborexcel,,,,afields)

* this generating wrong unread format
COPY TO (suborexcel) TYPE XLS

* this generating also wrong unread format
COPY_XLSX TO (suborexcel) TYPE XLSX


In post "Using a command to copy DBF to EXCEL" I found one change from Roger :
oExcel:saveAs(<filename>,51)

but this do not work.

Have somebody this way of generate xlsx in program ?

[rdonnay]

Try this:

This was written for compatibility with Visual FoxPro.

Add this code at the top of your source to create the custom command, then you can use the command just like COPY TO.

Code: Select all

#COMMAND COPY TO <(file)> TYPE XLS ;
         [FIELDS <flds,...>] ;
         [   FOR <for>] ;
         [ WHILE <whl>] ;
         [  NEXT <nxt>] ;
         [RECORD <rcd>] ;
         [ <rst: REST>] ;
         [   VIA <dbe>] ;
         [ ALL ] ;
=> _dbExport( DC_Path(AppName(.t.)) + '_TempData', { <(flds)> }, __EBCB(<for>), __EBCB(<whl>), <nxt>, <rcd>, <.rst.>, <dbe> ) ;
   ;M->_oExcel := CreateObject("Excel.Application");
   ;M->_oExcel:DisplayAlerts:=.f.;
   ;M->_oBook:= M->_oExcel:workbooks:Open(DC_Path(AppName(.t.))+'_TempData.dbf');
   ;M->_oBook:SaveAs(<(file)>+".xls", -4143 );
   ;M->_oExcel:Quit();
   ;M->_oExcel:Destroy();
   ;Ferase(DC_Path(AppName(.t.))+'_TempData.Dbf')

[Victorio]

This code look same as I have in source. ??? I found it here in forum in older post.
Or is some diferencies ?
For me not generating correct file.

May be problem with this ?
****************************
PROCEDURE DbeSys()
****************************
_LoadDbes()

RETURN

****************************************
STATIC FUNCTION _LoadDbes()
****************************************
LOCAL i, aDbeList := DbeList(), cDbeList := ''


IF Valtype(aDbeList) = 'A'
FOR i := 1 TO Len(aDbeList)
cDbeList += aDbeList[i,1] + ','
NEXT
ENDIF

aDbeList := cDbeList

IF !('DBFDBE'$aDbeList) .AND. !DbeLoad( "DBFDBE",.T.)
DC_WinAlert( "Database-Engine DBFDBE not loaded" )
ENDIF
IF !('NTXDBE'$aDbeList) .AND. !DbeLoad( "NTXDBE",.T.)
DC_WinAlert( "Database-Engine NTXDBE not loaded" )
ENDIF
IF !('DBFNTX'$aDbeList) .AND. !DbeBuild( "DBFNTX", "DBFDBE", "NTXDBE" )
DC_WinAlert( "DBFNTX Database-Engine, Could not build engine" )
ENDIF
IF !('CDXDBE'$aDbeList) .AND. !DbeLoad( "CDXDBE",.T.)
DC_WinAlert( "Database-Engine CDXDBE not loaded" )
ENDIF
IF !('DBFCDX'$aDbeList) .AND. !DbeBuild( "DBFCDX", "DBFDBE", "CDXDBE" )
DC_WinAlert( "DBFCDX Database-Engine, Could not build engine" )
ENDIF
IF !('FOXDBE'$aDbeList) .AND. !DbeLoad( "FOXDBE",.T.)
DC_WinAlert( "Database-Engine FOXDBE not loaded" )
ENDIF
IF !('FOXCDX'$aDbeList) .AND. !DbeBuild( "FOXCDX", "FOXDBE", "CDXDBE" )
DC_WinAlert( "FOXCDX Database-Engine, Could not build engine" )
ENDIF
IF !('DELDBE'$aDbeList) .AND. !DbeLoad( "DELDBE",.T.)
DC_WinAlert( "Database-Engine DELDBE not loaded" )
ENDIF

DbeSetDefault( "FOXCDX" )

RETURN .t.

[rdonnay]

That is only the code that loads the FOXCDX dbe and sets the default to FOXCDX.

There's nothing wrong with that code.

Send me the DBF you are trying to copy to XLS. Maybe I can help.

[Victorio]

OK, I sended You part of Main app , where is defined COPY and COPY_XLSX commands.

In ExportExcel is using this command.
There are 3 ways to create ,
one with DC_WorkArea2Excel(suborexcel,,,,afields) - this works fine ! view file a.xls
here I have selected fields in array

second with COPY , not ok view file b.xls

third with COPY_XLSX , not ok view file c.xls

I sended You also database, from this I generate xls, database have not memo fields, only numbers, characters and Logical
Hmmm logical, can be here in export ? Excel do not know logical, od know ?

[Victorio]

sorry, i forget attach file...

[Victorio]

Hi all.

Now I have not very clear solution, but runs ok.

Some interesting things :
- I want export dbf to excel, but XLS file generated from Xbase know only ...65000 rows , I am not sure, but everything show it
- XLSX I do not know generate, system with #COMMAND and code 50 not run on my app

- I tryed copy part of data to DBF file (some temporary database only for reporting), and want automatically open in Excel - problem, because I use FOXCDX , and Excel can not open this type file (Visual Foxpro and others)
- then I try convert VFP DBF file to FOX2X file , ok super Excel open it,

But next problem with national characters Latin 2 CP852 od 1250
I must convert temporary DBF field with character from ANSI to OEM
replace KN_VLA with ConvToOEMCP(KN_VLA)

Now everything is ok, but it is only quick solution for my customer.

Here is part from source >

* copy from application database to temporary file DBF
COPY TO (suborexcel+"1") FIELDS CPACI,CPAEI,PVEI,VYMC,DRPC,CLVC,UMPC,VYME,DRPE,CLVE,UMPE,;
KN_PCS,KN_CIT,KN_MEN,KN_ICO,KN_RCI,KN_VLA,KN_TVL,VYMPOD,OSC,VYMPODPV,KN_TVL ;
VIA "FOXCDX"

* switch to OEM
SET CHARSET TO OEM

* this is little utility to converting VisualFoxPro database to FoxPro 2X
RunShell(suborexcel+"1.dbf"+" "+suborexcel+"2.dbf",cestahlprg+"fox2x.exe",,.t.,.t.)

* again swith to ANSI
SET CHARSET TO ANSI

* open temporary database and change field KN_VLA
SELECT 50
use (suborexcel+"2") ALIAS DBF50 EXCLUSIVE
go top
do while eof()!=.T.
replace KN_VLA with ConvToOEMCP(KN_VLA)
skip
enddo
close DBF50

* again open main application database to work
SELECT 9

SET CHARSET TO OEM

* next opening DBF file in excel
DC_SpawnUrl(suborexcel+"2.dbf")

I know it is some "chaos" in my source, sorry for it

[rdonnay]

You may be able to use Xbase++ to convert the DBF to FOX2X.

Give this a try:

Code: Select all

#INCLUDE "foxdbe.CH"

FUNCTION Main()

DC_LoadRdds()

dbeSetDefault('FOXCDX')

use myOldFile 

DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .T. )
DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDBE_LOCKMODE_2X )

copy to myNewFile via 'foxcdx'

RETURN nil