how to get data from an excel file?

[gabriel]

Hello:

what's the easiest way to get or read data (some specific cells) from an excel worksheet?

thanks in advance

[srod]

If the target system has Excel installed then you might use COMatePLUS (there are a few Excel demos in the package). Alternatively, use ODBC via PB's database library. You will need to set up a DSN connection to the Excel workbook in question (either through the control panel or through code which you can find in these forums) and then you just use PB's database functions as per normal.

[gabriel]

Yes , Excel is installed. I looked at examples coming with COMATEplus, it seems that all are for writing/creating excel files. I need example for reading it, is there such one?

Thank you

[srod]

The basic demo also reads a couple of cells, albeit from a newly created worksheet. Altering it to load an existing workbook is simple enough.

I will add that the ODBC path is likely to lead to a solution which runs faster, much faster!

[gabriel]

I didn't succeed to find that demo, could you just indicate it's name.
Otherwise, I just need to open an existing excel file, and read some cells, if it's small and easy to you to do it with COMAte, I'll be very thankfull.
On the other hand, momentarily I'm saving the excel file as .csv file, then accessing the cells by reading it as text file, but this is not quite straightforward

Thanks anyway, your COMATE is fantastic

[PureLeo]

Hey srod, I'm trying your COMatePLUS and I'm getting the error when running the excel demo...

I don't have Excel installed, only OpenOffice... is that ok, or I need Excel to run it?

[KIKI]

Hey srod, I'm trying your COMatePLUS and I'm getting the error when running the excel demo...

I don't have Excel installed, only OpenOffice... is that ok, or I need Excel to run it?

Hi Leo,
There's an example on this forum on how to use Open Office spreadsheets with comate Plus.

[Kiffi]

or I need Excel to run it?

yes, you need an installed Excel to access Excel-Objects.

Greetings ... Kiffi

[Rook Zimbabwe]

Flype had PURExls
http://www.purebasic.fr/english/viewtop ... it=purexls

And you CAN read and write to xls sheets if you have them already made... ABBKlaus had something too as I recall but I think he abandoned it... Use the SEARCH on the forum or GOOGLE the forum for WRITE TO EXCEL and see what pops!

[gabriel]

the old link for flype' PureXLS is dead, is there a new one?

[Kiffi]

the old link for flype' PureXLS is dead, is there a new one?

"Use the search, Luke"

http://www.purebasic.fr/english/viewtop ... LS#p330302



Greetings ... Kiffi

[gabriel]

examples show always how to create an excel file, no one to get data from (just reading some cells)

[RASHAD]

Removed

[Paul]

You can download some sample code and a sample excel sheet here, which uses PureBasic's built in database commands...
http://www.reelmedia.org/pureproject/FILES/readxls.zip

This is the code in the ZIP file:

Code: Select all

#ODBC_ADD_DSN =1 
#Database     =1
#Library      =1


Procedure MakeConnection(Driver.s,strAttributes.s)
  If OpenLibrary(#Library,"ODBCCP32.DLL")
    Buffer=AllocateMemory(Len(strAttributes))
    CopyMemory(@strAttributes,Buffer,Len(strAttributes))
    For x=0 To Len(strAttributes)-1
      If PeekB(Buffer+x)=Asc(";"):PokeB(Buffer+x,0):  EndIf
    Next
    result=CallFunction(#Library,"SQLConfigDataSource",0,#ODBC_ADD_DSN,@Driver,Buffer)
    FreeMemory(Buffer)
    CloseLibrary(#Library)
  EndIf
  ProcedureReturn result
EndProcedure



file$="my stuff.xls"

If MakeConnection("Microsoft Excel Driver (*.xls)","DSN=ConnectXLS;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";")
  If UseODBCDatabase()
    If OpenDatabase(#Database,"ConnectXLS","","",#PB_Database_ODBC)
      If DatabaseQuery(#Database,"select * from [my data$]")
        While NextDatabaseRow(#Database)
          id=0
          col1 =GetDatabaseLong(#Database,id):id+1
          col2$=GetDatabaseString(#Database,id):id+1
          Debug Str(col1)+"..."+col2$
        Wend
      EndIf    
      CloseDatabase(#Database)
    EndIf
  EndIf
EndIf

[gabriel]

Thank you very much Paul:D
that's what I was searching, something really simple, compact and fast.

everything OK, except the first line of "my stuff.xls" is not read, any trick?