Re: Read/Write XLSX files without Excel

[KennyOoi]

hi,

have you try the sample from minigui.

minigui\SAMPLES\Advanced\HbXlsxWriter

On Saturday, February 19, 2022 at 12:26:10 AM UTC+8 rhoyc...@gmail.com wrote:

Hola a todos.

Necesito abrir/leer archivos .xlsx sin tener el Excel/Office instalado. Dentro de lo que he investigado encontré una librería llamada OpenXLSX de TROLDAL.

La descargué de https://github.com/troldal/OpenXLSX y pude crear la librería compilada con MSVC.

Tengo un problema de conocimiento: los ejemplos están escritos para C++ y no entendí cómo aplicarlos en programación HARBOUR/XCODE.

Será que alguien puede escribir un pequeño ejemplo que abra un archivo xlsx y que pueda recorrer las filas y columnas y obtener sus valores? Es todo lo que necesito en este momento.

Para los que están pensando en otras soluciones:

1) He intentado con la clase XLSXClass.prg de Srdan Dragojlovic, pero tiene un error que no lee bien las columnas.

2) He intendando con XLSXIO de Brecht Sanders (https://github.com/brechtsanders/xlsxio), ni siquiera pude compilarla y tiene los ejemplos para C++

3) Y así con otras opciones.

Se agradece si alguien puede escribir un pequeño ejemplo que abra un archivo xlsx y que pueda recorrer las filas y columnas y obtener sus valores? Es todo lo que necesito en este momento.

Google Traslator: -------------------------------------------------------------------------

Hello everyone.

I need to open/read .xlsx files without having Excel/Office installed. Within what I have investigated I found a library called OpenXLSX from TROLDAL.

I downloaded it from https://github.com/troldal/OpenXLSX and was able to create the compiled library with MSVC.

I have a knowledge problem: the examples are written for C++ and I didn't understand how to apply them in HARBOR/XCODE programming.

Can someone write a little example that opens an xlsx file and can iterate through the rows and columns and get their values? It's all I need right now.

For those who are thinking of other solutions:
1) I've tried Srdan Dragojlovic's XLSXClass.prg class, but it has an error that it doesn't read columns correctly.
2) I've been trying Brecht Sanders XLSXIO (https://github.com/brechtsanders/xlsxio), I couldn't even compile it and it has the examples for C++
3) And so with other options.

It is appreciated if someone can write a small example that opens an xlsx file and can loop through the rows and columns and get their values? It's all I need right now.

[HBQuerier]

Generates a spreadsheet with the contents of the current directory:

#INCLUDE "COMMON.CH"
#INCLUDE "i_pseudofunc.CH"
#INCLUDE "hbcompat.CH"


#DEFINE F_NAME 1
#DEFINE F_SIZE 2
#DEFINE F_DATE 3
#DEFINE F_TIME 4
#DEFINE F_ATTR 5


ANNOUNCE RDDSYS
REQUEST DBFCDX




Function GetDirectory( CurrentDirectory_s )

Local Directory_a := {}
Local DirTable_s := "DIRTABLE"
// Local CurrentDirectory_s := CURDRIVE() + ":\" + CURDIR()
Local CurrentDirectory_s := ""
Local CurrentDate_d := DATE()
Local CurrentTime_s := TIME()

Local Fields_a := {  {  "FNAME",     "C",  50, 0 }, ;
                     {  "FSIZE",            "N",         15, 0 }, ;
                     {  "FDATE",            "D",  8, 0 }, ;
                     {  "FTIME",     "C",  10, 0 }, ;
                     {  "FATTR",     "C",   8, 0 }, ;
                                                        {  "CURDIR", "C", 150, 0 }, ;
                                                        {  "CURDATE", "D", 8, 0 }, ;
                                                        {  "CURTIME", "C", 12, 0 }  }



If CurrentDirectory_s == NIL
        CurrentDirectory_s := CURDRIVE() + ":\" + CURDIR()
EndIf


SET EXCLUSIVE ON

RDDSetDefault("DBFCDX")

If File( DirTable_s + ".DBF" )
        FErase( DirTable_s + ".DBF" )
EndIf

TRY
   DBCREATE( DirTable_s, Fields_a, "DBFCDX", .T., "" )
CATCH
        ? "Error trying to create " + DirTable_s
        wait
        DbCloseAll()
END


Directory_a := Directory( "*.*" )

USE (DirTable_s) ALIAS DIRTABLE

AEVAL(  Directory_a, { |File_a| FileAppendDirectory( File_a[F_NAME], File_a[F_SIZE], File_a[F_DATE], File_a[F_TIME], File_a[F_ATTR], CurrentDirectory_s, CurrentDate_d, CurrentTime_s   ) } )

DbCloseAll()


USE (DirTable_s) ALIAS DIRTABLE

CreateDirectorySpreadsheet( CurrentDirectory_s, CurrentDate_d, CurrentTime_s )



DbCloseAll()



Return Nil



Function FileAppendDirectory( FileName_s, FileSize_n, FileDate_d, FileTime_s, FileAttribute_s, CurrentDirectory_s, Date_s, Time_s )

IF DIRTABLE->( DbAppend() )

        REPLACE FNAME WITH FileName_s, FSIZE WITH FileSize_n, FDATE WITH FileDate_d, FTIME WITH FileTime_s, FATTR WITH FileAttribute_s, CURDIR WITH CurrentDirectory_s, CURDATE WITH Date_s, CURTIME WITH Time_s

ENDIF




Return Nil


FUNCTION CreateDirectorySpreadsheet( CurrentDirectory_s, CurrentDate_d, CurrentTime_s )

        Local ExcelFile_s := "DIRTABLE.xlsx"
        LOCAL workbook
   LOCAL worksheet
   LOCAL format
        Local row_n := 0



        TRY
                IF File(ExcelFile_s)
                        FErase(ExcelFile_s)
                ENDIF
        CATCH
                ? 'Error trying to Erase DIRTABLE.xlsx'
                wait
                DbCloseAll()
                Quit
        END


        /* Create a new workbook and add a worksheet. */
        TRY
                workbook  := workbook_new( ExcelFile_s )
        CATCH
                ? 'Error trying to create ' + ExcelFile_s
                wait
                DbCloseAll()
                Quit
        END

        worksheet := workbook_add_worksheet( workbook )
        /* Add a format. */
   format := workbook_add_format( workbook )
   format_set_bold( format )



        worksheet_set_column( worksheet, 0, 0, 50)
        worksheet_write_string( worksheet, row_n, 0, "FileName", format )

        worksheet_set_column( worksheet, 1, 1, 12)
        worksheet_write_string( worksheet, row_n, 1, "FileSize", format )

        worksheet_set_column( worksheet, 2, 2, 15)
        worksheet_write_string( worksheet, row_n, 2, "FileDate", format )

        worksheet_set_column( worksheet, 3, 4, 10)
        worksheet_write_string( worksheet, row_n, 3, "FileTime", format )

        worksheet_set_column( worksheet, 4, 4, 15 )
        worksheet_write_string( worksheet, row_n, 4, "FileAttribute", format )

        worksheet_set_column( worksheet, 5, 5, 150)
        worksheet_write_string( worksheet, row_n, 5, "CurrentFolder", format )

        worksheet_set_column( worksheet, 6, 6, 15)
        worksheet_write_string( worksheet, row_n, 6, "CurrentDate", format )

        worksheet_set_column( worksheet, 7, 7, 12)
        worksheet_write_string( worksheet, row_n, 7, "CurrentTime", format )



        row_n++
        DIRTABLE->(DbGoTop())
        DO        While .NOT. DIRTABLE->( Eof() )

           worksheet_write_string( worksheet, row_n, 0, DIRTABLE->FNAME )
           worksheet_write_number( worksheet, row_n, 1, DIRTABLE->FSIZE )
           worksheet_write_string( worksheet, row_n, 2, DTOS(DIRTABLE->FDATE) )
           worksheet_write_string( worksheet, row_n, 3, DIRTABLE->FTIME )
                worksheet_write_string( worksheet, row_n, 4, DIRTABLE->FATTR )
                worksheet_write_string( worksheet, row_n, 5, CurrentDirectory_s )
           worksheet_write_string( worksheet, row_n, 6, DTOS(CurrentDate_d) )
           worksheet_write_string( worksheet, row_n, 7, CurrentTime_s )

                DIRTABLE->(DbSkip())
                row_n++


        EndDo



        TRY
           /* Save the workbook and free any allocated memory. */
                workbook_close( workbook )
        CATCH
                ? 'Error trying to close DIRTABLE.xlsx'
                wait
                DbCloseAll()
                Quit
        END

        RETURN Nil

[HBQuerier]

(I know you asked for a READing sample.  But just thought this might help a little.)

[Auge & Ohr]

hi,

most know ADO (Active Data Object) for using with SQL or Access *.MDB
but you can use it also with *.DBF or *.XLSx

this Connection String is used for *.XLSx

   strHeader := "HDR=YES;"
   oConnect := CreateObject( "ADODB.Connection" )
   oConnect:ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;' + ;
           'Data Source=' + myXlsFile + ';' + ;
           'Extended Properties="Excel 12.0 Xml;' + strHeader + 'IMEX=1' + '";'

benefit of ADO :
if you got a working CLASS than you "just" need to change Connection-String to use SQL

Jimmy

[Rhoy Chaves]

Thank you all very much for your suggestions.

I want to make it clear that the computer where the application is running does not have and will not have Office/Excel.

That is my need.

I also don't know if the server has ADO or not. It's something I can't be sure of, that's why I need to open Excel files without having Excel installed.

With Minigui's HbXlsxWriter library: it doesn't work either because that library requires Excel to be installed on the work computer.

[Auge & Ohr]

hi

ADO is a Microsoft Addon Product and can be download here

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/de-de/download/details.aspx?id=54920

Sample C:\MiniGUI\SAMPLES\Advanced\HbXlsxWriter\

------------------------
HbXlsxWriter is a Harbour library for creating Excel XLSX files.
HbXlsxWriter  is a port  of the Libxlsxwriter (please see below ). As original
library it can only create new files. It cannot read or modify existing files!

* Libxlsxwriter  is  a  C library  that  can  be  used to write text, numbers,
formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.
see  the  full documentation on http://libxlsxwriter.github.io for the getting
started guide, a tutorial, the main API documentation and examples.
------------------------

so what you need is libxlsxwriter.dll and LIB(s) to link it into your App

Jimmy

[Rhoy Chaves]

No puedo usar ninguna sentencia que contenga:  CreateObject( "Excel.Application" )

Recuerden que es un servidor que no tiene ni va a tener Excel/Office

----------------------------------------------------------------------------------------------------------------------------------------

I cannot use any statement that contains: CreateObject( "Excel.Application" )

Remember that it is a server that does not have and will not have Excel/Office

[Auge & Ohr]

hi

there is NO
 CreateObject( "Excel.Application" )
in HbXlsxWriter

ADO use

   oConnect := CreateObject( "ADODB.Connection" )

   objRS := CreateObject( "ADODB.Recordset" )

Jimmy

[Rhoy Chaves]

I can´t open a xlsx file with HbXlsxWriter.

Y don´t need to create xlsx files. I need to open xlsx files.