Page 1 of 1
Wrapper for LibXl or XlsxWriter ...
Posted: Thu Mar 17, 2022 12:14 pm
by Enrrique Vertiz
Greetings
I want to review both options for generating large XLSX without excel installed and fast, will someone have an example of how to use commercial LibXL??
The same query but with XlsxWriter if it exists??
Thanks
Re: Wrapper for LibXl or XlsxWriter ...
Posted: Thu Mar 17, 2022 3:46 pm
by Jimmy
hi,
i have test-drive LibXl and XlsxWriter but now use ADO to create *.XLSx without Excel.
Why
LibXl and XlsxWriter are not from Microsoft and ADO work with many Microsoft Product
Code: Select all | Expand
oConnect := CreateObject( "ADODB.Connection" )
bError := ERRORBLOCK( { | oErr | BREAK( oErr ) } )
BEGIN SEQUENCE
oConnect:Execute( "DROP TABLE " + myXlsFile )
END SEQUENCE
ERRORBLOCK( bError )
// ---------------------- Catalog -------------------------- *
oCatalog := CreateObject( "ADOX.Catalog" )
oCatalog:ActiveConnection := 'Provider=Microsoft.ACE.OLEDB.12.0;' + ;
'Data Source=' + myXlsFile + ';' + ;
'Extended Properties="Excel 12.0 Xml";'
// ---------------------- Create Table --------------------- *
oTable := CreateObject( "ADOX.Table" )
oTable:Name := "Sheet1"
ii := 1
FOR ii := 1 TO iMax
cField := aStructure[ ii ] [ DBS_NAME ]
cType := aStructure[ ii ] [ DBS_TYPE ]
nLen := aStructure[ ii ] [ DBS_LEN ]
nDec := aStructure[ ii ] [ DBS_DEC ]
oColumn := CreateObject( "ADOX.Column" )
oColumn:Name := cField
DO CASE
CASE cType = "C"
oColumn:Type := adVarWChar
oColumn:DefinedSize := nLen
oColumn:Attributes := 2 // adColNullable
CASE cType = "M"
oColumn:Type := adLongVarWChar
oColumn:Attributes := 2 // adColNullable
CASE cType = "N"
oColumn:Type := adDouble
oColumn:DefinedSize := nLen
oColumn:NumericScale := nDec
CASE cType = "D"
oColumn:Type := adDate
CASE cType = "L"
oColumn:Type := adBoolean
ENDCASE
oTable:Columns:Append( oColumn )
NEXT
// add Table to Catalog
oCatalog:Tables:Append( oTable )
oConnect:ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;' + ;
'Data Source=' + myXlsFile + ';' + ;
'Extended Properties="Excel 12.0 Xml";'
oConnect:open()
SetProperty( "ExportDbf", "ProgressBar_1", "Value", 0 )
DO EVENTS
// #define Use_INSERT .T.
#ifdef Use_INSERT
// ---------------------- INSERT INTO ---------------------- *
// prepare String for Fields
cSelect := "( "
ii := 1
FOR ii := 1 TO iMax
cField := aStructure[ ii ] [ DBS_NAME ]
cSelect += cField
IF ii < iMax
cSelect += ", "
ENDIF
NEXT
cSelect += " ) "
// now start
nStart := SECONDS()
GO TOP
DO WHILE !EOF()
ii := 1
cSql := "INSERT INTO [Sheet1] " + cSelect + "VALUES ( "
FOR ii := 1 TO iMax
cField := aStructure[ ii ] [ DBS_NAME ]
cType := aStructure[ ii ] [ DBS_TYPE ]
nPosi := FIELDPOS( cField )
xValue := FIELDGET( nPosi )
DO CASE
CASE cType = "C"
xValue := "'" + STRTRAN( xValue, "'", " " ) + "'"
CASE cType = "M"
IF LEN( xValue ) > 64
xValue := "'Memo'"
ELSE
xValue := "'" + STRTRAN( xValue, "'", " " ) + "'"
ENDIF
CASE cType = "D"
IF EMPTY( xValue )
xValue := "0"
ELSE
// xValue := DTOC( xValue )
// xValue := DTOS( xValue ) + "000001"
// xValue := HB_STOT( DTOS( xValue ) + "000000" )
// xValue := serial2dt(xValue )
xValue := STR( dt2serial( xValue ) )
ENDIF
CASE cType = "L"
xValue := IF( xValue = .T., "TRUE", "FALSE" )
CASE cType = "N"
xValue := STR( xValue )
ENDCASE
cSql += xValue
IF ii < iMax
cSql += ","
ENDIF
NEXT
cSql += ")"
oConnect:Execute( cSql )
onDummy( TIME(), cSql )
nRowLine ++
IF ( nRowLine % nEvery ) = 0
nProz := CalcPos( nRowLine, nMax )
IF nProz > 100
nProz := 100
ENDIF
SetProperty( "ExportDbf", "ProgressBar_1", "Value", nProz )
DO EVENTS
ENDIF
SKIP
ENDDO
#ELSE
// ---------------------- ADO Recordset -------------------- *
objRS := CreateObject( "ADODB.Recordset" )
objRS:Open( "Select * from [Sheet1]", oConnect, adOpenKeyset, adLockOptimistic )
// now start
nStart := SECONDS()
GO TOP
DO WHILE !EOF()
aField := {}
aValue := {}
ii := 1
FOR ii := 1 TO iMax
cField := aStructure[ ii ] [ DBS_NAME ]
cType := aStructure[ ii ] [ DBS_TYPE ]
nPosi := FIELDPOS( cField )
xValue := FIELDGET( nPosi )
IF EMPTY( xValue )
DO CASE
CASE cType = "C"
xValue := " "
CASE cType = "M"
xValue := " "
CASE cType = "N"
xValue := 0.00
CASE cType = "D"
xValue := CTOD( " . . " )
CASE cType = "L"
xValue := .F.
ENDCASE
ENDIF
AADD( aField, cField )
AADD( aValue, xValue )
NEXT
objRS:AddNew( aField, aValue )
objRS:Update() // objRS:UpdateBatch()
nRowLine ++
IF ( nRowLine % nEvery ) = 0
nProz := CalcPos( nRowLine, nMax )
IF nProz > 100
nProz := 100
ENDIF
SetProperty( "ExportDbf", "ProgressBar_1", "Value", nProz )
DO EVENTS
ENDIF
SKIP
ENDDO
objRS:Close()
objRS := NIL
#ENDIF
oConnect:Close()
oConnect := NIL
nStop := SECONDS() - nStart
onDummy( TIME(), "finish after " + LTRIM( STR( nStop ) ) )
oCatalog := NIL
oTable := NIL
oColumn := NIL
hb_cdpSelect( cOldLang )
SetCursorWait( "WinLeft", .F. )
SetCursorWait( "WinRight", .F. )
DO EVENTS
// Msginfo( "finish after " + LTRIM( STR( nStop ) ) )
RETURN .T.
Re: Wrapper for LibXl or XlsxWriter ...
Posted: Thu Mar 17, 2022 4:00 pm
by Enrrique Vertiz
Thanks Jimmy but ADO is good for exporting but it doesn't allow me to define cells with colors, widths, borders, make breaks and embed formulas, I need to generate large reports in Excel, so I need some help with LibXL or failing that with xlsxwriter
Thanks again
Re: Wrapper for LibXl or XlsxWriter ...
Posted: Thu Mar 17, 2022 7:51 pm
by Jimmy
hi,
so I need some help with LibXL or failing that with xlsxwriter
here you will find some Xbase++ CLASS Code for LibXL from Hubert Brandel
https://www.xbaseforum.de/viewtopic.php?f=16&t=7610look for Attachment in 1st Message of Thread ( use v1.9 Version )
Xbase++ CLASS Code is 99 % compatible to harbour
Code: Select all | Expand
#IDFEF __XPP__
#include 'ot4xb.ch' // 3-PP LIB not need for harbour
#xcommand METHOD <!ClassName!>:<MethodName> => METHOD <MethodName> CLASS <ClassName>
#ENDIF
CLASS _HBLibXL
...
METHOD _HBLibXL:init(cName,cKey)
harbour
but Code might have Function with "other Name".
if you have more Question ask in German Xbase++ Forum
Re: Wrapper for LibXl or XlsxWriter ...
Posted: Thu Mar 17, 2022 9:37 pm
by Enrrique Vertiz
Thanks Jimmy, I'll check to see what I can do...