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
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.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
so I need some help with LibXL or failing that with xlsxwriter
#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)METHOD _HBLibXL:init(cName,cKey) CLASS _HBLibXLThanks Jimmy, I'll check to see what I can do...