NTK and The NTK Project
are properties of Jn Dechereux
Home | Documentation | FAQ.

Vanilla 1.1.8 is a product of Lussumo. More Information: Documentation, Community Support.

Welcome Guest!
Want to take part in these discussions? If you have an account, sign in now.
If you don't have an account, apply for one now.
    •  
      CommentAuthorjnd
    • CommentTimeSep 22nd 2010
     
    Hi there,

    Can someone indicate/advice me a good library capable to access, create and manipulate Excel sheets?
    BTW, I would prefer a .DLL library, because it's easier/faster to wrap its C functions using NTK's '_DLL32...' or 'DECLARE FUNC...' command. Anyway, a good ActiveX control would be ok too!

    TIA,
    JN
    •  
      CommentAuthorbxed
    • CommentTimeSep 30th 2010
     
    www.libxl.com

    Direct reading and writing Excel files
    LibXL is a library that can read and write Excel files. It doesn't require Microsoft Excel and combines an easy to use and powerful features. Library can be used to

    * Generate a new spreadsheet from scratch
    * Extract data from an existing spreadsheet
    * Edit an existing spreadsheet

    LibXL can help your applications in exporting and extracting data to/from Excel files with minimum effort. Also it can be used ever as report engine. Library can be used in C, C++, C#, Delphi, Fortran and other languages.

    Supports Excel 97-2003 binary formats (xls) and Excel 2007/2010 xml formats (xlsx). Supports Unicode and 64-bit platforms. There are a wrapper for .NET developers and separate Mac and Linux edition.
    Simple interoperate, no more Excel dependency
    LibXL has C/C++ headers, Delphi unit and .NET assembly for including in your project. No OLE automation.

    Customizing the look and feel
    LibXL supports numerous formatting options: alignments, borders, colors, fill patterns, fonts, merging cells and so on.

    High performance
    Writing speed is about 2 100 000 cells per second for numbers and 240 000 cells per second for 8-character random strings in binary format (CPU 3.2 GHz).

    Royalty-free distribution with your application
    Our customers can use this library in theirs commercial applications without any fees.
    •  
      CommentAuthorsperling
    • CommentTimeOct 5th 2010
     
    Nice lib, wrote some HB_FUNC's to test it, and works fine for me...

    Jan
    •  
      CommentAuthorjnd
    • CommentTimeOct 5th 2010
     
    Thank you Ed and Jan,
    for both precious advices. I will take look to LibXL, then keep you informed...

    Bye for now,
    JN
    •  
      CommentAuthorbxed
    • CommentTimeOct 15th 2010
     
    Hi Jan and Jnd,

    The functions xlSheetWriteNum and xlSheetSetCol does not
    work, what is wrong ?

    Thanks for your help and your advice.
    Bxed

    *-------------------------------------------------------

    public hDllxl := LoadLibrary( "libxl32" )

    if empty(hDllxl)
    return
    endif

    Test_Libxl( "Test.xls" )

    FreeLibrary( hDllxl )
    return
    *****
    *****
    *****
    FUNCTION TEST_LIBXL ( xFile )
    local xBook,xSheet,xVal

    xBook=xlCreateBook()
    xSheet=xlBookAddSheet( xBook , "Sheet= 01" , 0 )

    * bug *
    xlSheetSetCol( xSheet , 0 , 3 , IEEREAL( 30 ) , 0 , 1 )

    xlSheetWriteStr( xSheet , 1 , 0 , "row 2 col 1" , 0 )
    xlSheetWriteStr( xSheet , 1 , 1 , "row 2 col 2" , 0 )
    xlSheetWriteStr( xSheet , 1 , 2 , DTOC(DATE()) , 0 )
    xlSheetWriteStr( xSheet , 1 , 3 , TIME() , 0 )

    xVal=IEEREAL( 1234.56 )

    * bug *
    xlSheetWriteNum( xSheet , 2 , 1 , xVal , 0 )

    if xlBookSave( xBook , xFile )
    ShellExecute( 0 , "open", xFile , 0 , 0 , 1 )
    endif

    xlBookRelease( xBook )
    return


    /*
    * clipwks
    * purpose: to convert a Clipper numeric to spreadsheet format
    * syntax: IEEREAL( nValue )
    * returns: char string of length 8. each char in string represents
    * the appropriate byte of nIEEVar.
    */

    #pragma BEGINDUMP

    #include <hbapi.h>

    union {
    double n;
    char s[8];
    } v;

    HB_FUNC( IEEREAL )
    {
    double n;

    n = hb_parnd(1);

    hb_retclen( (char *) &n, 8);
    }

    /*
    * clipwks
    * purpose: to convert a spreadsheet string into its numeric value
    * syntax: realiee( cIEEStr )
    * returns: nValue
    */

    HB_FUNC( REALIEE )
    {
    char i;
    char *t;

    t = hb_parc(1);

    for (i = 0; i < 8; i++)
    v.s[i] = *t++;

    hb_retnd( v.n );
    }

    #pragma ENDDUMP


    #define CTYPE_VOID 0
    #define CTYPE_CHAR 1
    #define CTYPE_UNSIGNED_CHAR -1
    #define CTYPE_CHAR_PTR 10
    #define CTYPE_UNSIGNED_CHAR_PTR -10
    #define CTYPE_SHORT 2
    #define CTYPE_UNSIGNED_SHORT -2
    #define CTYPE_SHORT_PTR 20
    #define CTYPE_UNSIGNED_SHORT_PTR -20
    #define CTYPE_INT 3
    #define CTYPE_UNSIGNED_INT -3
    #define CTYPE_INT_PTR 30
    #define CTYPE_UNSIGNED_INT_PTR -30
    #define CTYPE_LONG 4
    #define CTYPE_UNSIGNED_LONG -4
    #define CTYPE_LONG_PTR 40
    #define CTYPE_UNSIGNED_LONG_PTR -40
    #define CTYPE_FLOAT 5
    #define CTYPE_FLOAT_PTR 50
    #define CTYPE_DOUBLE 6
    #define CTYPE_DOUBLE_PTR 60
    #define CTYPE_VOID_PTR 7
    #define CTYPE_BOOL 8
    #define CTYPE_STRUCTURE 1000
    #define CTYPE_STRUCTURE_PTR 10000
    #define PASCAL_TYPE "Pascal"
    *
    #Include "LIBXL.CH"
    *
    *
    #define tagBookHandle CTYPE_INT
    #define tagSheetHandle CTYPE_INT
    #define tagFormatHandle CTYPE_INT
    #define tagFontHandle CTYPE_INT


    FUNCTION xlCreateBook ()
    local xProc:=GetProcAddress( hDllxl , "xlCreateBookCA", PASCAL_TYPE , tagBookHandle )
    return CallDll( xProc )

    FUNCTION xlBookRelease ( HBook )
    local xProc:=GetProcAddress( hDllxl , "xlBookReleaseA", PASCAL_TYPE , CTYPE_VOID , tagBookHandle )
    return CallDll( xProc , HBook )

    FUNCTION xlBookSave ( HBook , FileName )
    local xProc:=GetProcAddress( hDllxl , "xlBookSaveA", PASCAL_TYPE , CTYPE_INT , tagBookHandle , CTYPE_CHAR_PTR )
    return IIF( empty(CallDll( xProc , HBook , FileName )) , .F. , .T. )

    FUNCTION xlBookAddSheet ( HBook , NameSheet , HSheet )
    local xProc:=GetProcAddress( hDllxl , "xlBookAddSheetA", PASCAL_TYPE , tagSheetHandle , tagBookHandle , CTYPE_CHAR_PTR , tagSheetHandle )
    return CallDll( xProc , HBook , NameSheet , HSheet )

    FUNCTION xlSheetSetCol ( HSheet , xColFirst , xColLast , xWidth , HFormat , NotHidden )
    local xProc:=GetProcAddress( hDllxl , "xlSheetSetColA", PASCAL_TYPE , CTYPE_INT , tagSheetHandle , CTYPE_INT , CTYPE_INT , CTYPE_DOUBLE , tagFormatHandle , CTYPE_BOOL )
    return CallDll( xProc , HSheet , xColFirst , xColLast , xWidth , HFormat , NotHidden )

    FUNCTION xlSheetWriteStr ( HSheet , xRowl , xCol , xTexte , HFormat )
    local xProc:=GetProcAddress( hDllxl , "xlSheetWriteStrA" , PASCAL_TYPE , CTYPE_INT , tagSheetHandle , CTYPE_INT , CTYPE_INT , CTYPE_CHAR_PTR , tagFormatHandle )
    return CallDll( xProc , HSheet , xRowl , xCol , xTexte , HFormat )

    FUNCTION xlSheetWriteNum ( HSheet , xRow , xCol , xValue , HFormat )
    local xProc:=GetProcAddress( hDllxl , "xlSheetWriteNumA", PASCAL_TYPE , CTYPE_INT , tagSheetHandle , CTYPE_INT , CTYPE_INT , CTYPE_DOUBLE , tagFormatHandle )
    return CallDll( xProc , HSheet , xRow , xCol , xValue , HFormat )
    •  
      CommentAuthorsperling
    • CommentTimeOct 15th 2010
     
    Hmmm, you don't say what's your problem... :-(

    I suspect your are seeing only int values?
    I´m not really sure if this CTYPE_DOUBLE (parameter 4) is right...
    I had to change it to a floating type.

    I´m not really sure why do you use the IEEREAL function... Should work without it, if setting the 4' parameter to floating.
    Also, I recommend to use the following line to write dates to the XLS:
    xlSheetWriteNum(::sheets[::ActSheet],y,x,xlBookDatePack(::book,xyear,xmonth,xday,0,0,0,0),xFormat)

    Hope this helps...

    Jan
    •  
      CommentAuthorbxed
    • CommentTimeOct 16th 2010
     
    Hello Jan,
    Thank for your answer.
    The function xlSheetWriteNum does not work.

    xlSheetWriteNum( xSheet , 2 , 1 , 123 , 0 ) - problem -> GPF
    xlSheetWriteNum( xSheet , 2 , 1 , F2BIN(123) , 0 ) - problem -> empty cell

    FUNCTION xlSheetWriteNum ( HSheet , xRow , xCol , xValue , HFormat )
    local xProc:=GetProcAddress( hDllxl , "xlSheetWriteNumA",;
    PASCAL_TYPE , CTYPE_INT , CTYPE_INT ,;
    CTYPE_INT , CTYPE_INT ,;
    CTYPE_FLOAT,; // CTYPE_DOUBLE ????
    CTYPE_INT )
    return CallDll( xProc , HSheet , xRow , xCol , xValue , HFormat )

    How wrapped you the function xlSheetWriteNum ?
    How send you the value to the function ?

    Thank for your help.
    Bxed
    •  
      CommentAuthorsperling
    • CommentTimeOct 17th 2010
     
    Hi bxed:

    Well, I did a different approach interfacing the lib...
    I wrote the HB_FUNC's

    Anyway, I will post my source below, but:

    GetProcAddress( hDllxl , "xlSheetWriteNumA",;
    PASCAL_TYPE , CTYPE_INT , CTYPE_INT ,;
    CTYPE_INT , CTYPE_INT ,;
    CTYPE_FLOAT,; // CTYPE_DOUBLE ????
    CTYPE_INT )

    You declared the Sheet handle as an INT type, but I believe a handle cannot be an integer, it must be a pointer...

    Also, with my source, I simply write the value to the cell:
    xlSheetWriteNum(::sheet,2,1,123.456,::format)

    Here is what I do:

    *------------------------------------------------------------------------------
    * Low Level C Routines
    *------------------------------------------------------------------------------
    #pragma BEGINDUMP



    #include <windows.h>
    #include <shlobj.h>

    #include "hbapi.h"
    #include "hbvm.h"
    #include "hbstack.h"
    #include "hbapiitm.h"
    #include "libxl.h"

    HB_FUNC(XLSHEETWRITENUM)
    {
    SheetHandle nSheet = (SheetHandle) hb_parptr(1);
    int x = (int) hb_parni(2);
    int y = (int) hb_parni(3);
    float n = (float) hb_parnd(4);
    FormatHandle n5 = (FormatHandle) hb_parptr(5);

    xlSheetWriteNum(nSheet,x,y,n,n5);
    hb_retnl(1);
    }

    #pragma ENDDUMP

    I linked the LIB into my link script, and the DLL must be in the exe path.

    Hope this helps,

    BR, Jan
    •  
      CommentAuthorbxed
    • CommentTimeOct 19th 2010
     
    Hello Jan,
    Thank for your help.

    The function xlSheetWriteNum is ok.
    Value is double (not float) in function xlSheetWriteNumA
    of ..\include_c\SheetA.h

    ..\include_c\SheetA.h
    XLAPI int XLAPIENTRY xlSheetWriteNumA(SheetHandle handle, int row, int col, double value, FormatHandle format);

    Best regard.
    Bxed


    HB_FUNC( XLSHEETWRITENUM )
    {
    SheetHandle handle = (SheetHandle) hb_parptr(1);
    int row = (int) hb_parni(2);
    int col = (int) hb_parni(3);
    double value = (double) hb_parnd(4); /* float value = (float) hb_parnd(4); */
    FormatHandle format = (FormatHandle) hb_parptr(5);

    hb_retni( (int) xlSheetWriteNumA( handle , row , col , value , format ) );
    }
    •  
      CommentAuthorbxed
    • CommentTimeNov 9th 2010
     
    Hi,

    I wrote the wrapper for xharbour and libXL for Windows 3.0.2 - 2010-09-15.

    LibXL is a library that can read and write Excel files without Excel.

    download the demo :
    http://www.libxl.com/download.html

    The file Libxlhrb.zip contains:

    - Readme.txt
    - Libxl.ch
    - LibxlFnc.prg - Libxl functions
    - LibxlTst.prg - Tests

    Instructions for use:
    ---------------------
    for bcc55
    implib -a libxl32.lib libxl32.dll

    Compile LibxlFnc
    Link LibxlFnc.obj and the libxl32.lib into your link script
    The libxl32.dll must be in the exe path

    Regards
    Bxed
    •  
      CommentAuthorjnd
    • CommentTimeNov 10th 2010 edited
     
    Congratulations Bxed!
    You've done a great job. Many thanks for sharing your knowlege with other NTK community members.
    I'm sure many of them will be interested in your work.

    Cheers,
    JN


    P.S Do you see any drawback if I publish a copy of your contribution in the dedicated 'NTK - Knowledge Sharing' section of this newsgroup?
    •  
      CommentAuthorjnd
    • CommentTimeNov 10th 2010 edited
     
    Hi again,

    Also, a special thanks to JAN, who's lent you a hand and somehow point you the right direction...

    Thx guys, feel free to continue contributing - IMO, that's really important for the NTK community's evolution.

    JN
    •  
      CommentAuthorbxed
    • CommentTimeNov 10th 2010
     
    Hi Jnd,
    Yes you can publish a copy of my contribution in the dedicated 'NTK - Knowledge Sharing'
    Bxed
    •  
      CommentAuthorbxed
    • CommentTimeNov 14th 2010
     
    Hi,
    This is the new version of libxlHrb.zip

    modifs:
    - libxlfnc.prg : HB_FUNC( XLBOOKSETKEY )
    - libxltst.prg : xlBookSetKey( xBook )

    libxlfnc.prg, libxltst.prg, readme.txt :

    Note for licensing information
    ------------------------------
    example :
    Name: john smith
    Key: windows-1234567abcdefwxyz

    in HB_FUNC( XLBOOKSETKEY ) :
    xlBookSetKeyA( handle , L"john smith" , L"windows-1234567abcdefwxyz" );

    in LibxlTst.prg
    xBook=xlCreateBookC()
    xlBookSetKey( xBook )


    Bxed
    •  
      CommentAuthorTom Black
    • CommentTimeAug 28th 2020
     
    I think you should try ZetExcel. It is good library capable to access, create and manipulate Excel sheets.
    •  
      CommentAuthorAbbougaga
    • CommentTimeSep 1st 2020
     
    Hi Tom:
    Thx for this information, sounds interesting. But afaik ZetExcel is mainly for .NET and VB.NET. However, I've been told it could be also driven via COM/ACTIVEX - so just wondering if, alike BXED for LIBXL, you could share with us your NTK-wrapper source code, as well as a basic Test.PRG sample that shows how to proceed?

    Cheers
    Ab.