Read a simple LibreOffice Calc file

[Red2]

My program needs to directly read a (very simple) XLSX file via LibreOffice’s Calc (in Windows). That is, MS Excel is not installed so VBA is not an option.

I found a single FB forum post regarding using LibreOffice:
viewtopic.php?f=14&t=10768&p=133284&hil ... RL#p133284

The sample code (by phishguy » Mar 27, 2010 7:37), when I compiled it, gave the following error:
Command executed:
"C:\Program Files (x86)\FreeBASIC\FBC.exe" "-g" "P:\HMG_Code\AccessExcel\Read_Calc_File.bas"
Compiler output:
C:\PROGRA~2\FREEBA~1\bin\win32\ld.exe: cannot find -ldisphelper
Results:
Compilation failed
System:
FBIde: 0.4.6
fbc: FreeBASIC Compiler - Version 1.07.0 (08-25-2019), built for win32 (32bit)
OS: Windows NT 6.2 (build 9200)

I have no experience in Open Office Basic automation and I find it quite complicated as opposed to using VBA. At least it is so for me.

Question:
Is someone more experienced willing to explain/guide how this can be done ("hidden") in my FB program?
Note: I understand that parameter #4 to loadComponentFromURL() is an important issue.

I would be very, very appreciative your kind help.

Thank you, Red2

[dodicat]

Firstly you need the disphelper library file.
https://sourceforge.net/projects/fbc/fi ... Libraries/

(15 up from the bottom of the page).
You can put the libdisphelper.a in the same folder as your code while you experiment.
(libdisphelper.a is 32 bits for the 32 bit fb compiler)

[Red2]

Thank you very much Dodicat!

The libdisphelper.a file you suggested permitted a successful compilation.

I get an error when I run the .EXE but that at least gets me started. This will take some work.

Thanks again!

Red2

[D.J.Peters]

@Red2 why you don't use File I/O commands from the BASIC included in LibreOffice to read and write documents ?

https://help.libreoffice.org/Basic/Run-Time_Functions

Joshy

[Red2]

Hi Joshy,

Thank you for taking the time to assist. If the XLSX spreadsheet file format was just unencumbered simple text I would do so. Actually, this is what my application already with CSV exports of the XLSX file's contents

My application needs to directly access and read individual XLSX files (where Microsoft's VBA will not be available) at runtime.

I would like to think that, over the years, others have managed this given the XLSX format's wide use.

Is there some other way you might suggest here?

Thanks again for your very kind help.
Red2

[Red2]

A bit more: My issue to date has been opening the XLSX file "hidden" and "readonly". The code I found on the forum opens a brand new blank, not an existing XLSX file.

The 4th parameter (aNoArgs) in loadComponentFromURL() needs to specify both "hidden" and "readonly" and I do not know how this should be done.

Code: Select all

dim As SAFEARRAYBOUND sab(1)
dim aNoArgs as VARIANT
aNoArgs.vt = VT_ARRAY or VT_VARIANT
sab(0).lLbound = 0 : sab(0).cElements = 1
aNoArgs.parray=SafeArrayCreate(VT_VARIANT,1,cast(SAFEARRAYBOUND Ptr,@sab(0)))

aNoArgs needs "hidden" and "readonly" included before it is passed as 4th parameter to loadComponentFromURL()

I apologize but I am rather new to FB this is beyond what I understand.

Thank you again for your patience and help.
Red2

[Knatterton]

In my experience, if you have a program idea, first realize it "quick and dirty", with .cvs, only to get the data somehow. If the program works, you can still improve it. This would be such a quick solution:

Code: Select all

Function LoadCVS(sFilename as String) as long
   Dim as short filehandle 
   Dim as long row = 0
   Dim as Long nTotal     
   Dim as String txt       
   Dim As Short iPos

   ' Open and read the file
   filehandle = Freefile
   Open sFileName For Input as #filehandle

   If LOF(filehandle) = 0 Then Return 1
   
   Do Until EOF(filehandle)
      Line Input #filehandle, txt   
      
      ' get the wanted data out of the line somehow
   
   Loop

   
   Close #filehandle
   
   Return 0
End Function

If you get stuck in the beginning with xlsx chances are high the project will be never finished, like so many fb projects. :-)

[Red2]

Hi Knatterton,

Thank you, I appreciate your reply. My application currently accesses the CSV format file.

The users, however, find this manual step irritating. If I can programmatically read the XLSX file itself then this extra step can be done away with. That is my hope here.

In a "previous life" I accomplished this sort of thing using Excel's VBA. Here I hope I can utilize LibreOfficec to accomplish this but am finding it rather complicated. At least have a start.

Thanks again for your kind input.

Red2

[dodicat]

Using barebones FB and shell
These xlsx files are in compressed format.
I have had a look at them via power shell by saving the contents into a .zip file
I have commented out
shell "rd /S/Q tmpfolder"
in the function load so they can be viewed.

I have used
xl\sharedstrings.xml
This file seems to show the string used OK but deletes any repeats.
I cannot see one complete file in the tmpfolder with all the strings used.

Code: Select all

#include "file.bi"

Function loadfile(file as string) as String
	If FileExists(file)=0 Then Print file;" not found":Sleep:end
   var  f=freefile
    Open file For Binary Access Read As #f
    Dim As String text
    If Lof(f) > 0 Then
      text = String(Lof(f), 0)
      Get #f, , text
    End If
    Close #f
    return text
end Function

Sub savefile(filename As String,p As String)
    Dim As Integer n
    n=Freefile
    If Open (filename For Binary Access Write As #n)=0 Then
        Put #n,,p
        Close
    Else
        Print "Unable to save " + filename
    End If
End Sub


Sub string_split(byval s As String,chars As String,result() As String)
    redim result(0)
    Dim As String var1,var2
Dim As long pst,LC=len(chars)
      #macro split(stri)
    pst=Instr(stri,chars)
    var1="":var2=""
    If pst<>0 Then
    var1=Mid(stri,1,pst-1)
    var2=Mid(stri,pst+LC)
    Else
    var1=stri
End if
    if len(var1) then 
    redim preserve result(1 to ubound(result)+1)
    result(ubound(result))=var1
    end if
    #endmacro
   Do
   split(s):s=var2
Loop Until var2=""
End Sub

function load(XLSX_File as string,a() as string) as string
    var L=loadfile(XLSX_File)
savefile("sample.zip",L)   ' save to .zip 
shell "powershell Expand-Archive -Path "+"sample.zip -DestinationPath "+"tmpfolder"
var ans= loadfile("tmpfolder\xl\sharedstrings.xml")
'shell "rd /S/Q tmpfolder"  'remove the tmpfolder +files
string_split(ans,"<t>",a())
for n as long=lbound(a) to ubound(a)
    a(n)=rtrim(a(n),"</t></si><si>")
next
a(ubound(a))=rtrim(a(ubound(a)),"</t></si></sst>")
kill "sample.zip"  'delete the .zip
return ans
end function
'================================

redim as string res()
print "Please wait . . ."
print load("example.xlsx",res())   'some .xlsx file of your choice
print
for n as long=lbound(res) to ubound(res)
    print res(n)
next
print
print "Done"
sleep


  

[Knatterton]

Thank you, I appreciate your reply.

These were my first thoughts. I mysself used open office calc very often on Windows. Today Mint uses libre office, but until now i am not satisfied neither with calc nor with writer. Thinking for installing open office again. :-)

[Red2]

Hi dodicat,

Thank you so much for your very kind and prompt assistance. I love your creative solution approach. Your code comes so close to what I could go with. As you clearly point out the one confounding issue is the "repeats".

I can parse and extract the header row (no repeats here). But, the data rows definitely contain repeats. Unfortunately ZIP file internals are way beyond my capabilities.

If you or anyone else has more creative ideas you would be willing to share I would greatly appreciate hearing them.

Thanks again, Red2

[Knatterton]

Now i have installed open office, i found calc not even has XLSX Format. For me personally this is not a problem, it is quick and still works with .xls, that's all i need.

But development has stopped.

https://itsfoss.com/install-openoffice-ubuntu-linux/

So that is no option here.

[Red2]

Hi Knatterton,

Thank you for your information. You mentioned, "i found calc not even has XLSX Format" and that is true in its default spreadsheet format.
LibreOffice's default spreadsheet format can easily be changed from ODS to XLSX (or several others).

I am still experimenting using LibreOffice to programatically read (not write) XLSX files. Seems plausible enough so perhaps it will yet happen!

Thanks again, Red2

[jj2007]

Red,

You should explore Joshy's advice. LibreOffice has a BASIC similar to VBA, and I am pretty sure that it can open various formats including *.xlsx. That would be by far the easiest way. With Microsoft Excel, you could even steer the whole process from outside using DDE, but as far as I remember LibreOffice has not implemented DDE.

@Red2 why you don't use File I/O commands from the BASIC included in LibreOffice to read and write documents ?

https://help.libreoffice.org/Basic/Run-Time_Functions

Joshy

[Knatterton]

I don't know why you need the .xlsx format. If you want to show the data in a table in your program, this could happen no matter if they come from a .xlsx or .cvs file. Export can happen the same with .cvs. . :-)