DC_WorkArea2Excel() size limit

This forum is for eXpress++ general support.
Message
Author
Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: DC_WorkArea2Excel() size limit

#21 Post by Victorio »

Thank you, this is better solution, than fox2x utility.

User avatar
pedroah
Posts: 28
Joined: Wed Nov 05, 2014 7:15 pm
Location: Dominican Republic
Contact:

Re: DC_WorkArea2Excel() size limit

#22 Post by pedroah »

Try this, I placed notes, as best I could, I hope this help


// aHeader = Array of tittles
// aFields = Array of Fields
// Note : Need excel to work ( Tested from Excel 2003 to 2016)
PROCEDURE ExportTableToExcel( aHeader,aFields )
LOCAL oExcel
LOCAL i := 1
LOCAL j := 1
LOCAL xValorColumna
LOCAL nRegistro := ( alias() )->( Recno() )

oExcel := CreateObject( "Excel.Application" ) // Create a Excel Object

IF NIL == oExcel
RETURN
ENDIF

oExcel:Workbooks:Add // Add New Workbooks
oExcel:Application:Visible = .T. // Make Excel Vosible


// Headers
FOR i:= 1 TO len( aHeader )
oExcel:Cells(j,i):Select
oExcel:Selection:Font:Name = "Courier"
oExcel:Selection:Font:Size = 10
oExcel:Selection:Font:Bold = .T.
oExcel:Cells(j,i):Value = aHeader
NEXT

// Details
i := 1
j++

WHILE ( alias() )->( !eof() )

FOR i := 1 TO len( aHeader )
oExcel:Cells(j,1):Select
oExcel:Selection:Font:Name = "Courier"
oExcel:Selection:Font:Size = 10
xValorColumna := aFields // Field Value
oExcel:Cells(j,i):Value = &xValorColumna // Replace value in Excel
NEXT

j++
( alias() )->( dbskip() )
ENDDO


oExcel:destroy()
goto nRegistro
RETURN

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: DC_WorkArea2Excel() size limit

#23 Post by Victorio »

pedroah: thank you, I will try it.
now I modify source to generate directly DBF file, then rename dbf extension to xls and open with excel.
it is quick.
If I put change to Fox2x I do not need convert CP from ANSI to OEM to correct national characters.
It looks like it will be ok, but your function want examine too.

Wolfgang Ciriack
Posts: 478
Joined: Wed Jan 27, 2010 10:25 pm
Location: Berlin Germany

Re: DC_WorkArea2Excel() size limit

#24 Post by Wolfgang Ciriack »

The procedure from pedroah may be a little bit slow.
It is better to set the values in excel with a range and an array.

Code: Select all

aOutput:={}
aadd(aOutput, {"No.","Date","Value"})
do while !dc_eof()
    aadd(aOutput, { kb->nr, kb->kasdat,kb->summe})
    dc_dbskip()
enddo
oExcel:= CreateObject("Excel.Application")
oExcel:Visible:=.F.
oExcel:Application:DisplayAlerts:=.F.

oBook := oExcel:Workbooks:Add()
oWorkSheet := oBook:ActiveSheet
oWorkSheet:Select()
oWorkSheet:Columns( 3 ):NumberFormat := "#.#0,00"

SetExcelValue("A1",oWorkSheet,"Title")
SetExcelValue("A3",oWorkSheet,"Title 2")
SetExcelValue("B3",oWorkSheet,ntocmonth(monat)+" "+str(jahr,4))
SetExcelValue("A6:C"+ntrim(6+len(aOutput)-1),oWorkSheet,aOutput)

oWorkSheet:Columns( 2 ):AutoFit()
 
oBook:SaveAs(d)
oExcel:Quit()
oExcel:Destroy()
_______________________
Best Regards
Wolfgang

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: DC_WorkArea2Excel() size limit

#25 Post by Victorio »

slow function can be problem because database has about 700000 records.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: DC_WorkArea2Excel() size limit

#26 Post by rdonnay »

I made a small change to DC_Array2Excel() and DC_WorkArea2Excel().

If you use a .XLSX extension on the file name it will now automatically save as type xlOpenXMLWorkbook thus allowing more than 65536 rows to be saved.

Copy _DCFUNCT.PRG to your \exp20\source\dclipx folder.
Run BUILD19_SL1.BAT or BUILD20.BAT to rebuild DCLIPX.DLL.
Attachments
_dcfunct.zip
(46.72 KiB) Downloaded 642 times
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: DC_WorkArea2Excel() size limit

#27 Post by Victorio »

Roger : Thank you very much !
I will try.

Post Reply