LIBX reading XLSx sheets

Moderator: Moderatoren

Antworten
simofranz
Rookie
Rookie
Beiträge: 9
Registriert: Mo, 26. Sep 2016 17:11

LIBX reading XLSx sheets

Beitrag von simofranz » Mo, 18. Jun 2018 14:47

I'm evaluating the LIBX software in order to import data from external Excel files in my application.
I can't undarstand the right syntax to read correct kind of values stored in a cell.
Example routine:

oBook := xlCreateBook()
oSheet:=oBook:BookGetSheet(0)
if ! empty(oSheet)
for nRow:=oSheet:SheetFirstRow() TO oSheet:SheetLastRow()
for nCol:=oSheet:SheetFirstCol() TO oSheet:SheetLastCol()

oType:=oSheet:SheetCellType(nRow,nCol)

DO CASE
CASE oType==CELLTYPE_EMPTY
??SPACE(10)

CASE oType==CELLTYPE_NUMBER
// how convert number & date ??
oFormat:=oSheet:SheetCellFormat(nRow,nCol) // <------------------------- ????

DO CASE
CASE oFormat==NUMFORMAT_GENERAL
??STR(oSheet:SheetReadNum(nRow,nCol) ,10)
*CASE oFormat==NUMFORMAT_NUMBER_D2
??STR(oSheet:SheetReadNum(nRow,nCol) ,10,2)
*CASE oFormat==NUMFORMAT_DATE
* ??DTOC( oSheet:SheetReadDate(nRow,nCol,"DD-MM-AA") ,8)
*
ENDCASE


CASE oType==CELLTYPE_STRING
??oSheet:SheetReadStr(nRow,nCol)

CASE oType==CELLTYPE_BLANK
??SPACE(10)

ENDCASE
??SPACE(4)
next
?
next
else
?"sheet empty"
endif

georg
Foren-Administrator
Foren-Administrator
Beiträge: 2205
Registriert: Fr, 08. Feb 2008 21:29

Re: LIBX reading XLSx sheets

Beitrag von georg » Mo, 18. Jun 2018 15:34

Hello,


maybe you take a look here: https://www.xbaseforum.de/viewtopic.php ... 717#p79170

Xbase++ comes with a class for Office Automation that could help you with your problem. And I assume that the way of e.g. addressing a cell/row/column is the same as in LIBX (which is a product I do not know about), as both needs to interoperate with the MS APIs.
Liebe Grüsse aus der Eifel,

Georg S. Lorrig
Vorsitzender des Deutschsprachige Xbase-Entwickler e. V.
Redakteur der Wiki des Deutschprachigen Xbase-Entwickler e.V.

Benutzeravatar
brandelh
Foren-Moderator
Foren-Moderator
Beiträge: 14549
Registriert: Mo, 23. Jan 2006 20:54
Wohnort: Germersheim
Kontaktdaten:

Re: LIBX reading XLSx sheets

Beitrag von brandelh » Mo, 18. Jun 2018 17:02

In Excel a date is stored as a number format. The Help page is here :arrow: http://www.libxl.com/spreadsheet.html

read the original doku for getting help on LibXL, and for the HBLibXL translate the Syntax to xbase classes ...
my class does handle the parameter nSheetHandle internal, if you use a oSheet object.

xlSheetCellType(nSheetHandle, nrow, ncol) gets the cell Type, if it is CELLTYPE_NUMBER it could be a number OR a date/time.
Syntax inside my class:
oSheet:SheetCellType( nrow, ncol) => nCellType ...

xlSheetCellFormat(nSheetHandle, nrow, ncol) Returns cell's format. It can be changed by user => oFormat
oSheet:SheetCellFormat(nrow, ncol) => oFormat ...

If a cell is a date or a number you can check with this

xlSheetIsDate(SheetHandle handle, int row, int col) => Checks that cell contains a date or time value.
oSheet:SheetIsDate(nrow, ncol)

to read a date OR a number there is

double xlSheetReadNum(SheetHandle handle, int row, int col, FormatHandle* format)
* Reads a number or date/time and its format from cell.
* Use xlBookDateUnpack() for extract date/time parts from double. *
If *format == 0 then error occurs. Get error info with xlBookErrorMessage().

xlBookDateUnpack() should give you a date like you want it. But I never used it with this function, because I do not use date/time format in Excel, but strings with german date format.

BOOK !!!

double xlBookDatePack(BookHandle handle,int year, int month, int day, int hour, int min, int sec, int msec) == Packs date and time information into double type.
int xlBookDateUnpack(BookHandle handle, double value, int* year, int* month, int* day, int* hour, int* min, int* sec, int* msec) // Unpacks date and time information from double type. Returns 0 if error occurs.
nYear etc. have to be parameter per reference @nYear, etc.
Gruß
Hubert

Benutzeravatar
brandelh
Foren-Moderator
Foren-Moderator
Beiträge: 14549
Registriert: Mo, 23. Jan 2006 20:54
Wohnort: Germersheim
Kontaktdaten:

Re: LIBX reading XLSx sheets

Beitrag von brandelh » Mo, 18. Jun 2018 17:17

you wrote above

Code: Alles auswählen

oSheet:SheetReadDate(nRow,nCol,"DD-MM-AA")
this is the wrong syntax, the * after the FormatHandle* format means that it have to be a handle to a variable (object)

FormatHandle* format)

in Xbase++ you just can use a variable per Referenz to get back a format-object or use a format object wich is a handle to the object.
But you can't use a string even if it would be per referenz, strings are handled different.
Have a look at the page of format help, wich format types are use and how to create a format object
As I understand, this format is just for the way the date is shown inside excel, the true date/time value is stored inside the double ... to get it use the shown funktions from book.
Gruß
Hubert

Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 11426
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg

Re: LIBX reading XLSx sheets

Beitrag von AUGE_OHR » Mo, 18. Jun 2018 21:27

hi,

i do not use LibX but with activeX i use Date with

Code: Alles auswählen

   o:treatDateAsString( .T. )
which give me right String YYYYMMDDHHMMSS
gruss by OHR
Jimmy

Antworten