Wrapper for LibXl or XlsxWriter ...

[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

[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 view

   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.

 

[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

[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=7610
look for Attachment in 1st Message of Thread ( use v1.9 Version )

Xbase++ CLASS Code is 99 % compatible to harbour

Code: Select all  Expand view

#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

Code: Select all  Expand view

METHOD _HBLibXL:init(cName,cKey) CLASS _HBLibXL

but Code might have Function with "other Name".
if you have more Question ask in German Xbase++ Forum

[Enrrique Vertiz]

Thanks Jimmy, I'll check to see what I can do...