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.

NTK - Main Forum » MS-Excel library?

1 to 16 of 16

- 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
- libxlhrb.zip
- 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
- LIBXLHRB.ZIP
- 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.

1 to 16 of 16