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
LIBX reading XLSx sheets
Moderator: Moderatoren
-
- Der Entwickler von "Deep Thought"
- Beiträge: 2832
- Registriert: Fr, 08. Feb 2008 21:29
- Hat sich bedankt: 98 Mal
- Danksagung erhalten: 13 Mal
Re: LIBX reading XLSx sheets
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.
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
Redakteur der Wiki des Deutschprachigen Xbase-Entwickler e.V.
Georg S. Lorrig
Redakteur der Wiki des Deutschprachigen Xbase-Entwickler e.V.
- brandelh
- Foren-Moderator
- Beiträge: 15707
- Registriert: Mo, 23. Jan 2006 20:54
- Wohnort: Germersheim
- Hat sich bedankt: 71 Mal
- Danksagung erhalten: 38 Mal
- Kontaktdaten:
Re: LIBX reading XLSx sheets
In Excel a date is stored as a number format. The Help page is here 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.
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
Hubert
- brandelh
- Foren-Moderator
- Beiträge: 15707
- Registriert: Mo, 23. Jan 2006 20:54
- Wohnort: Germersheim
- Hat sich bedankt: 71 Mal
- Danksagung erhalten: 38 Mal
- Kontaktdaten:
Re: LIBX reading XLSx sheets
you wrote above
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.
Code: Alles auswählen
oSheet:SheetReadDate(nRow,nCol,"DD-MM-AA")
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
Hubert
- AUGE_OHR
- Marvin
- Beiträge: 12913
- Registriert: Do, 16. Mär 2006 7:55
- Wohnort: Hamburg
- Hat sich bedankt: 19 Mal
- Danksagung erhalten: 46 Mal
Re: LIBX reading XLSx sheets
hi,
i do not use LibX but with activeX i use Date withwhich give me right String YYYYMMDDHHMMSS
i do not use LibX but with activeX i use Date with
Code: Alles auswählen
o:treatDateAsString( .T. )
gruss by OHR
Jimmy
Jimmy