read excel .xlsx file data ...

[marc_256]

Hello,

For my little robots (5 DOF robot arm), I like to read excel data information and convert it to graphic drawings.
Is it possible to read excel .xlsx file data in purebasic LST 5.73 x64 ?
And how to do this ...

thanks,
marc

[infratec]

It would be much easier to export ist or save it as csv file.
The other way is via COM, but than you need Excel installed.
Here are some examples to read a xls file via COM.
It is also possible to install an ODBC driver for Excel.

https://www.purebasic.fr/english/viewto ... 70#p553170
https://www.purebasic.fr/english/viewtopic.php?p=548955
...

[ricardo_sdl]

You might try to convert to csv format? Or try a library for that:
https://brechtsanders.github.io/xlsxio/

Good luck!

[Marc56us]

xlsx is a compressed archive with XML files inside.
There is many folder inside, but tabs sheets are in root.
ie: sheet1.xml is first tab
etc
(open xlsx with 7zip and you will see what is inside)
With PB, Use Packer lib then XML lib (or string or regex functions) to extract data
(xlsx is defaut format of xl files since office 2007. Previous versions use xls format who is binary file)

[HeX0R]

Try this

[Marc56us]

For my little robots (5 DOF robot arm), I like to read excel data information and convert it to graphic drawings.
Is it possible to read excel .xlsx file data in purebasic LST 5.73 x64 ?
And how to do this

Hi,
Because I like to find out how it works and create a simple tool for a specific use.
A little sample (no pbi, no dcom, all OS, excel not needed). Barely 50 lines...
Only first column of (text data) but easy to adapt.

Code: Select all

; XL_Lite_Reader
; Read the First column of text of an Excel file
; (C)Marc56us - 2022/04/17
; In reply to: https://www.purebasic.fr/english/viewtopic.php?t=78990
; Only Excel >= 2007 (or compatible) 
; https://en.wikipedia.org/wiki/Microsoft_Excel#XML_Spreadsheet
; Adapt an use as you want. Enjoy :-)

EnableExplicit

Define XL_File$ = "c:\Tmp\Test.xlsx"

SetCurrentDirectory(GetPathPart(XL_File$))

If FileSize(XL_File$) < 1
  Debug "File " + XL_File$ + " Not Found"
  End
EndIf

If GetExtensionPart(XL_File$) <> "xlsx"
  Debug "Not an xlsx file. Use Office >= 2007"
  End
EndIf

Procedure Un_Zip(Zip$, File$)
  UseZipPacker()
  Global Tmp_File$
  If OpenPack(0, Zip$)
    While NextPackEntry(0)  
      Tmp_File$ = PackEntryName(0)
      If  Tmp_File$ = File$
        Tmp_File$ = GetFilePart(Tmp_File$)
        UncompressPackFile(0, Tmp_File$)
        ClosePack(0)
        ProcedureReturn
      EndIf
    Wend  
    ClosePack(0)
  Else
    Debug "Error in Zip"
  EndIf
EndProcedure

Procedure Get_Datas()
  Protected Txt$
  If Not ReadFile(0, Tmp_File$) : ProcedureReturn 2 : EndIf
  While Not Eof(0)
    Txt$ + ReadString(0, #PB_File_IgnoreEOL)
  Wend  
  CloseFile(0)
  Protected RegEx$ = ~"<t xml:space=\"preserve\">(.+?)</t>"
  If Not CreateRegularExpression(0, RegEx$)
    Debug "Regex Error" : ProcedureReturn 1
  EndIf 
  If ExamineRegularExpression(0, Txt$)
    While NextRegularExpressionMatch(0)
      Debug RegularExpressionGroup(0, 1)
    Wend 
  EndIf
EndProcedure

Un_Zip(XL_File$, "xl/sharedStrings.xml")
If Tmp_File$ : Get_Datas() : EndIf

End

Yes, I use RegEx because I like it, but you can use FindString instead.

As I think that your robot must always read or write in the same way, it is easy to adapt this program if the file contains several columns: just look at the sheet1.xml file and the sharedStrings.xml file then adapt the expression.
Use 7zip to see what inside xlsx and a browser or editor to see xml files.

[ZX80]

Hello, all.

I liked HeX0R's version and it works for me. Thank you!
But I don't know how to adapt it to get empty cells as well. For example, we have the following table:

A1 = "0"...B1 = "1"...C1 = "2"
A2 = "a"...B2 = " "....C2 = "c"
A3 = " "....B3 = "e"...C3 = "f"

As a result, when reading the second line, we will see this:

a|c|

Okay. But how do you know that the value "c" belongs to the third column and not the second? I expected to see something like this:

a||c|

In other words, how to completely recreate the original data? I suspect this is somewhere between lines 144 and 160. But I couldn't fix it. HeX0R, can you tell me how it is possible?
I thought to find the fullest or longest line as a guideline. And then re-read other lines.

Thanks in advance.

[HeX0R]

That was pretty interesting, excel just left out unused (or better: empty) cells in the xml.
I wasn't aware of that phenomenon, please try the latest version.
Although I'm pretty sure, that might not be the final solution, it should make it work for now.

[ZX80]

Great job! Thank you master HeX0R

Thanks for the quick fix. I wanted to ask this question yesterday, but I was too shy. Now it works as expected. Thanks again for sharing your nice code. It will be very useful to have it in my collection.

[HeX0R]

No need to be shy
Most of the codes I offer have been made for my personal use (and none of the xlsx files I need has any empty cell!).
But excel files are pretty variable, although that small include is not meant to be able to handle all possible variations, the root functionality should work flawlessly.

Just go on sending bug reports while I'm still around here.

[marc_256]

Hello everyone,

thanks for your help,
tomorrow is a day of, so I can do some testing ...

What I try to do, is create a excel file with 5 columns and 1000st of lines, time, position X,Y,(Z) and the rotation of my robot.
(Z is the height, not in use for now)
So, in Purebasic, I like to make a 2D plane and read the columns and convert this data to graphic data.

greetings,
marc

[al2791]

Hello,
I wanted to report a small bug in the ExcelReader.pbi program, indeed if an excel cell contains an http link (sample : https://xxxx.com) the program seems to loop.
Thank you again for your excellent work
Best regards

[HeX0R]

I can't recreate that, can you upload such a xlsx somewhere?

[al2791]

here is an example of an excel file :
https://wetransfer.com/downloads/f09b04 ... e=sendgrid

[HeX0R]

Should be fixed now.
It hadn't anything to do with the links, but there were a row missing in the xml, which led to an infinite loop.