LibXL and harbour

[Red2]

Hi Maren,

Thank you for your prompt response.
Very sorry about your computer.
In the next day or two I will take a deeper look at XLSXClass.ZIP.

Again, thank you for all your kind help!

[edk]

I'm attaching my small class for reading xlsx files directly (without using OLE, libraries, etc.) along with an example of its use.

Once I needed to read large xlsx files (over 200k rows) and all xml-parser solutions I knew were too slow. So I decided to delve into the structure of xlsx files and built a class that does not create temporary files and loads data into memory and uses tokens for xml analysis.
The class is small and consists of the following methods:

oXLSX := SmallXLSXReader():Open( cXlsxFileOpen [, bBlockUnZip, bBlockParser, lAttributes ] )

Opens xlsx file, parameters are given: file name with path, optional code block executed during xml component unpacking, optional code block executed during xml data processing, optional logical parameter specifying whether attributes*) should be included in the returned data also.
*) attributes are a hash table with keys:
"col" - cell address,
"style" - style used in the cell,
"DateTime" - whether the data is date and time,
"type" - data type,
"value" - value in the cell (entered or calculated according to a formula),
"formula" - used formula

aSheetList := oXLSX:WorkSheetList()
Returns an array of sheet names in the workbook.

aSheetData := oXLSX:WorkSheet( cSheetName [, bBlockUnZip, bBlockParser ] )
Reads data from the sheet and returns it as an array

oXLSX:Close()
Releases variables stored in the class.


The class was written for my needs and I reserve that there is no guarantee that it will work with all xlsx workbooks, but maybe it will be useful to someone.

[gfilatov]

I'm attaching my small class for reading xlsx files directly (without using OLE, libraries, etc.) along with an example of its use.

Hi Edward,

Thanks a lot for your valuable contribution

The sample code is very useful.

I've attached the updated code without Harbour and C compiler warnings for your reference.

[Rathinagiri]

Amazing! Thanks a lot!

[edk]

I've attached the updated code without Harbour and C compiler warnings for your reference.

Thanks Grigory.
You used the ColumnsAutoFit() method in your version, which does not exist in the official HMG, so I took the liberty of adopting them from your Harbour MiniGUI Extended.
I think now it should work in both HMG and MiniGUI Extended .

In addition, in the class itself, I improved the recognition of the cell value type and formatting style as Date/Time/DateTime.

XLSX_Reader_v0.2.7z

(6.82 KiB) Downloaded 540 times

[serge_girard]

Thanks !
Serge

[nekbmm]

Everything is fine in my examples.

NEX.

[Georg_BA]

Hello everyone

I have an interesting problem with an xlsx file
there are 2 files in the attachment

file_1.xlsx original file from the supplier
the file does not contain the "dimension ref" section

the process terminates with an error
Date:2023.06.07 Time: 21:03:20
Error BASE/1133 Bound error: array assign
Called from (b)SMALLXLSXREADER_PARSER_SHEET(193)
Called from AEVAL(0)
Called from SMALLXLSXREADER:PARSER_SHEET(193)
Called from (b)SMALLXLSXREADER_WORKSHEET(162)
Called from AEVAL(0)
Called from SMALLXLSXREADER:WORKSHEET(162)
Called from LOADSHEET(101)
Called from READ_XLSX(84)
Called from (b)MAIN(21)
Called from _DOCONTROLEVENTPROCEDURE(6056)
Called from EVENTS(1756)
Called from DOMESSAGELOOP(0)
Called from _ACTIVATEWINDOW(5717)
Called from MAIN(38)

the file can be opened in any XLSX editor and I will save it under a different name File_2.xlsx everything will be fine

it is possible to modify the given class to load an xlsx file without a value "dimension ref"

thank you for your help
Regards, Georg

[edk]

This is the version of the class that reads your file:

SmallXLSXReaderClass.7z

(4.73 KiB) Downloaded 463 times

As I mentioned earlier, the SmallXLSXReaderClass project is based on tokenization, not xml parsing, and does not guarantee proper operation for all variants of the created xlsx file. Due to the fact that the xml structure in your file is correct in terms of content, but it is not the same as it is generated by MS Excel, that is why the tokenization is not entirely correct.

The class was created for my needs, so this is my final version. If something doesn't work, you can modify and improve the class, the code is open = DIY

[Georg_BA]

Hello

I thought it would be a job for you

but now seriously, thank you very much for your help, your solutions are always interesting
tested, loads everything correctly

Best regards, Georg