Xlsxlibhb_ver2

[arturo tamayo]

Dear All,

Here I leave the sources of our contribution for harbor and xharbour of the complete functions of John McNamara for the libxlsxwriter,

enjoy it

To download the complete library from https://www.mediafire.com/file/8bn1bbcx ... 2.zip/file

Greetings
Arturo Tamayo Daza

[richard-service]

Dear All,

Here I leave the sources of our contribution for harbor and xharbour of the complete functions of John McNamara for the libxlsxwriter,

enjoy it

To download the complete library from https://www.mediafire.com/file/8bn1bbcx ... 2.zip/file

Greetings
Arturo Tamayo Daza

Thank you so much.

[Antonio Linares]

Here you have it also:

https://github.com/FiveTechSoft/FWH_tools/blob/master/Xlsxlibhb_ver2.zip

[nageswaragunupudi]

Many thanks to the authors for making this Great Library available to our Harbour community.

Thank you Mr. John McNamara and Mr. Arturo Tamayo Daza

[RAMESHBABU]

Hi,

When I am trying to use the XlsxlibHb with xHarbour,
I am getting the following error.

ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ FiveWin for xHarbour 21.04 - Apr. 2021 Harbour development power ³Ü
³ (c) FiveTech 1993-2021 for Microsoft Windows 9X/NT/200X/ME/XP/Vista/7/8/10 ³Û
ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙÛ
ÿ ßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßß
Compiling...
xHarbour 1.2.3 Intl. (SimpLex) (Build 20201212)
Copyright 1999-2020, http://www.xharbour.org http://www.harbour-project.org/
Compiling 'chart_data_table.prg' and generating preprocessed output to 'chart_data_table.ppo'...

Lines 120, Functions/Procedures 2, pCodes 335
Embarcadero C++ 7.00 for Win32 Copyright (c) 1993-2015 Embarcadero Technologies, Inc.
chart_data_table.c:
Turbo Incremental Link 6.70 Copyright (c) 1997-2014 Embarcadero Technologies, Inc.
Error: Unresolved external '_hb_parvptr' referenced from C:\FWH\LIB\XLSXLIBHB.LIB|XlxsFunc
Error: Unable to perform link
* Linking errors *

Will this library work with only Harbour or xHarbour too ?

-Ramesh Babu

[nageswaragunupudi]

While working with this library I am learning a lot while enjoying it.
Till now I have used only a very few features of the library and there is a lot more to explore.

Exporting CUSTOMER.DBF to CUSTOMER.XLSX


Exporting WWONDERS.DBF to WWONDERS.XLSX with IMAGES in the Memo Fields:

[nageswaragunupudi]

Main program:

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

function wwonders()

   local cXlsx

   USE C:\FWH\SAMPLES\WWONDERS VIA "DBFCDX"
   cXlsx    := DBF2XLSX()
   ShellExecute( 0, "Open", cXlsx )

return nil
 

Exporting to xlsx is easy using this new function:

Code: Select all  Expand view

function DBF2XLSX( cFileXlsx )

   local oBook, oSheet
   local aStruct, bRow, nRow

   if Empty( Alias() )
      return nil
   endif

   DEFAULT cFileXlsx := cFileSetExt( DbInfo( DBI_FULLPATH ), "xlsx" )

   oBook    := TWorkBook():New( cFileXlsx )
   if oBook == nil
      ? "Can not create " + cFileXlsx
      return nil
   endif

   GO TOP
   aStruct  := DBSTRUCT()
   AEval( aStruct, { |a,i| If( a[ 2 ] == "M" .and. IsBinaryData( FieldGet( i ) ), a[ 2 ] := "m", nil ) } )
   bRow     := "{||{" + FW_ArrayAsList( ArrTranspose( aStruct )[ 1 ] ) + "}}"
   bRow     := &( bRow )

   oSheet   := oBook:AddSheet()
   nRow  := oSheet:SetStruct( aStruct )
   DBEVAL( { || nRow := oSheet:SayRow( nRow, Eval( bRow ) ) } )

   oBook:Close()

   ? "Exported to " + cFileXlsx

return cFileXlsx
 

In turn this function uses a small class I started making now. Right now, this class uses minimal features of this library

[Antonio Linares]

Dear Ramesh,

Please add this code to the bottom of your main PRG and please let me know if it solves that unresolved external for you using xHarbour

many thanks for your feedback

Code: Select all  Expand view

#pragma BEGINDUMP

#include <hbapi.h>

#ifdef __XHARBOUR__

#include <stdarg.h>

void * hb_parvptr( int iParam, ... )
{
    void * result;
    va_list args;
    va_start( args, iParam );
    result = hb_parptr( iParam, args );
    va_end( args );

    return result;
}

#endif

#pragma ENDDUMP

[RAMESHBABU]

Dear Antonio,

Wonderful. It worked !!.

That is Antonio and THE MASTER.

As told by Mr.Nageswara Rao

While working with this library I am learning a lot while enjoying it.
Till now I have used only a very few features of the library and there is a lot more to explore.

There is a lot to learn from this Library.

-Ramesh Babu P

[nageswaragunupudi]

FWH will include an interface class to this library
If this library is downloaded and linked with FWH we can use the class to use the lib with much ease.

Fonts and Images

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST FWXLSXLIB

function Main()

   local cFile, oXlsx, oFormat

   cFile    := "testfont.xlsx"
   oXlsx    := XlsxLibObj( cFile )

   WITH OBJECT oXlsx

      WITH OBJECT( oFormat := :Format() )
         :SetFont( "IMPACT", 50, NARGB(0,CLR_HRED), "BOLD" )
         :SetColor( nil, NARGB( CLR_YELLOW ) )
         :FontRotate( 90 )
         :SetAlign( 2, 10 )
      END

      :MergeRange( 0,0,15,0, "MERGED", oFormat )
      :SetColumnSize( 1, 1, 40 )

      WITH OBJECT( oFormat := :Format() )
         :SetFont( "Times New Roman", 20, 0, "BOLD" )
      END

      :Say( "B1", "Times New Roman", oFormat )
      :Say( 1, 1, "äöüÄÖÜ",  :Format():SetFont( "tahoma", 16, 0xff0000 ) )
      :B3    := "THIRD ROW"
      WITH OBJECT ( oFormat := :Format() )
         :NumFormat( 4 )
         :SetFont( "Lucida Console", 14, 0x0000ff, "bold" )
      END
      :Say( 4, 1, 3456789.2, oFormat )

      // Image
      :aImageSize( 1000, 700 )
      :B17 := MEMOREAD( "\fwh\bitmaps\sea.bmp" )

      :Close()

   END

   ShellExecute( 0, "Open", cFile )

return nil
 

[nageswaragunupudi]

This example demonstrates:

1. Image Brush
2. Sheet name customized
3. Group Headers
4. Freeze Header Rows
5. Hide Zero values
6. Footer row with aggregage formulae



Exporting tables is very simple

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oXlsx, aStruct, aGroup, aFooter, bRow, nRow

   // Prepare DBF
   USE CUSTOMER
   aStruct  := DbStruct()
   aGroup   := { { 2, 3, "NAME" }, { 4,7,"ADDRESS" }, { 9, 10, "PERSONAL" } }
   aFooter  := { ,"TOTALS",,,,,,,,"=AVERAGE(range)","=SUM(range)", }
   bRow     := "{||{" + FW_ArrayAsList( ArrTranspose( aStruct )[ 1 ] ) + "}}"
   bRow     := &( bRow )

   // Write to xlsx
   if ( oXlsx := XlsxLibObj( "customer.xlsx", "customer" ) ) != nil
      WITH OBJECT oXlsx
         nRow  := :SetStruct( aStruct,,aGroup )
         DBEVAL( { || nRow := :SayRow( nRow, Eval( bRow ) ) } )
         :SetFooter( aFooter )
         :SetBackGround( "\fwh\bitmaps\backgrnd\paper2.bmp" )
         :HideZero()
         //
         :Close()
         ? "Created customer.xlsx"
         ShellExecute( 0, "Open", "customer.xlsx" )
      END
   else
      ? "Can not create xlsx"
   endif

return nil
 

[nageswaragunupudi]

One line code for no frills export of DBF or other tables

Code: Select all  Expand view

oXlsx:CreateFrom() // uses current alias

for other tables:

Code: Select all  Expand view

oXlsx:CreateFrom( oRs )

[nageswaragunupudi]

Exporting multiple tables to different Sheets/Tabs of one single ExcelBook:

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oXlsx

   USE STATES SHARED NEW
   USE WWONDERS SHARED NEW VIA "DBFCDX"
   USE CUSTOMER SHARED NEW

   if ( oXlsx := XlsxLibObj( "mydbfs.xlsx", Alias() ) ) != nil
      WITH OBJECT oXlsx
         CUSTOMER->( :CreateFrom() )
         STATES->(   :AddSheet( Alias() ):CreateFrom() )
         WWONDERS->( :AddSheet( Alias() ):CreateFrom() )
         :Close()
      END
      ? "Created mydbfs.xlsx"
      ShellExecute( 0, "Open", "mydbfs.xlsx" )
   else
      ? "can not create xlsx"
   endif

   CLOSE DATA

return nil
 

[Horizon]

Hi Mr. Rao,

To use this library in fwh 23.10.

I have add this line to my main application.

Code: Select all  Expand view

REQUEST XLXLIB

I have add my this line to my makefile.

Code: Select all  Expand view

 echo $(FWDIR)\lib\xlsxlibhbmsvc.lib >> msvc.tmp  
 

I have compiled my application. There is an error like this.

Code: Select all  Expand view

Master.obj : error LNK2001: unresolved external symbol _HB_FUN_XLXLIB

What else do I need to do?

[nageswaragunupudi]

Please use

REQUEST XLSXLIB

Sorry for the spelling mistake in the whatsnew.txt