[DBP] - [STYX, Matrix1, Blue GUI] Excel interface via COM/ OLE

Author

Message

Manson_NS

20

Years of Service

User Offline

Joined: 4th May 2004

Location: Denmark

Posted: 21st Mar 2014 23:23 Edited at: 22nd Mar 2014 12:15

Link

I have been looking back and forth for a reasonable interface with Excel, with little to no luck. So while it may be freely accessible on the forum elsewhere, apparently I felt the need to do this.
I do in no way claim this is the most efficient, but it works, and I wanted to share it in case others can't find such functionality.

It unfortunately* requires STYX for the COM-interface - since Styx is clashing with Matrix1, I had preferred to avoid STYX - so if anybody has an alternative interface for COM, it'll be highly appreciated! Also, the call for functions with multiple parameters does not return data with STYX.

There's an abundance of functions available in the COM interface, but I'm evidently not bright enough to get all of them to work, so again - if anybody feels like tweaking this, there's some obvious optimizations to be made

Edited, 22/03-2014:

global kernel32 as dword : kernel32=find free dll() : load dll "kernel32.dll",kernel32

file$="c:\test.xlsx"
if file exist(file$) : doc=open_Excel_doc(file$,1) : else : doc=new_Excel_doc(1) : endif
sheet=sheet_com_instance(doc,2)

for x=1 to 5
  for y=1 to 5
    inc v : excel_set_text$(sheet,x,y,str$(v))
  next y
next x
for x=1 to 5
  for y=6 to 10
    inc v : excel_set_value#(sheet,x,y,v+0.132456)
  next y
next x
for x=6 to 10
  for y=1 to 5
    inc v : excel_set_comment$(sheet,x,y,str$(v))
  next y
next x
for x=6 to 10
  for y=6 to 10
    inc v : excel_set_formula$(sheet,x,y,"=10*"+str$(v))
  next y
next x

message "text: "+excel_get_text$(sheet,7,7)
message "value: "+str$(excel_get_value#(sheet,2,2))
message "formula: "+excel_get_formula$(sheet,7,7)
message "value (calculated): "+str$(excel_get_value#(sheet,7,7))

close_excel_doc(doc)
end

// Returns a COM interface for a new instance of Excel, with a 3-sheet document - visual determins whether Excel is visible or not
function new_Excel_doc(visual)
      app=FIND_FREE_COM()
      LOAD COM CLASS "Excel.Application",app // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application_members(v=office.15).aspx
//  hwnd=get com integer(app,"Hwnd") // Window Handle!!! 
      Set Com Integer app,"Visible",visual
      Workbooks=FIND_FREE_COM() : make com instance get com pointer(app,"Workbooks"),Workbooks
      WORKBOOK=FIND_FREE_COM() : instance=Call Com Func(Workbooks,"Add") : make com instance instance,WORKBOOK
      delete com Workbooks : delete com app
endfunction WORKBOOK

// Attempts to open the document 'txt$' in Excel, and returns a COM interface of Excel - visual determins whether Excel is visible or not
function open_Excel_doc(txt$,visual)
  if file exist(txt$) 
      app=FIND_FREE_COM()
      LOAD COM CLASS "Excel.Application",app // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application_members(v=office.15).aspx
//  hwnd=get com integer(app,"Hwnd") // Window Handle!!! 
      Set Com Integer app,"Visible",visual
      Workbooks=FIND_FREE_COM() : make com instance get com pointer(app,"Workbooks"),Workbooks
      WORKBOOK=FIND_FREE_COM() : instance=Call Com Func(Workbooks,"Open","S",txt$) : make com instance instance,WORKBOOK
      delete com Workbooks : delete com app
    else
      errormessage "The file "+quote$(txt$)+" does not exist"
  endif
endfunction WORKBOOK

// Close the Excel document
function close_excel_doc(WORKBOOK) : 
 app=FIND_FREE_COM() : make com instance get com pointer(WORKBOOK,"Application"),app
 Call Com Func app,"Quit" : delete com app
endfunction
function excel_save_doc(WORKBOOK)
 call com func WORKBOOK,"Save"
endfunction

// Returns the name of the currently opened document
function doc_name$(WORKBOOK) : fullname$=get com string(WORKBOOK,"FullName")
endfunction fullname$

// Returns a COM pointer for 'ID' sheet in the 'WORKBOOK' document
function sheet_com_instance(WORKBOOK,ID)// Since I cannot figure how to use the Item(Index) function on the sheet-list, we have to go back/forth to find the proper sheet number - DUMB!
  sheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),sheet
  do
    index=get com integer(sheet,"Index")
    if index=ID then exitfunction sheet
    if index<ID then n=get com pointer(sheet,"Next") : if n<>0 : delete com sheet : make COM instance n,sheet : else : errormessage "Fault, looping through the sheets somehow failed" : exitfunction 0 : endif
    if index>ID then p=get com pointer(sheet,"Previous") : if p<>0 : delete com sheet : make COM instance p,sheet : else : errormessage "Fault, looping through the sheets somehow failed" : exitfunction 0 : endif
  loop
endfunction 0

// Returns a COM pointer for cell 'x','y' in 'sheet'
function excel_cell_com(sheet,x,y) // More nonsence, the Range command should be able to do this without flickering through the sheets and cells
  c=call com func(sheet,"Activate")
  app=FIND_FREE_COM() : make com instance get com pointer(sheet,"Application"),app
  call com func app,"Goto","S","R"+str$(y)+"C"+str$(x)
  cell=find_free_com() : make com instance get com pointer(app,"ActiveCell"),cell
  delete com app
endfunction cell

// These are pretty self-explaining, but some checking for whether a cell contains a formula could be added
function excel_set_text$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : set com string cell,"Value",value$ : delete com cell
endfunction
function excel_set_formula$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : set com string cell,"Formula",value$ : delete com cell
endfunction
function excel_set_value#(sheet,x,y,value#) : cell=excel_cell_com(sheet,x,y) : set com float cell,"Value",value# : delete com cell
endfunction
function excel_set_comment$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : call com func cell,"AddComment","S",value$ : delete com cell
endfunction
function excel_get_text$(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value$=get com string(cell,"Text") : delete com cell
endfunction value$
function excel_get_formula$(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value$=get com string(cell,"Formula") : delete com cell
endfunction value$
function excel_get_value#(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value#=get com float(cell,"Value2") : delete com cell
endfunction value#

function FIND_FREE_COM()
  for com=1 to 256 : if com class exist(com)=0 : exitfunction com : endif : next : 
  errormessage "All COM-classes are in use!"
endfunction 0
function COMS_IN_USE() : for com=1 to 256 : inc counter,(com class exist(com)=0) : next com
endfunction counter

// Get a COM instance for the active sheet:
function ActiveSheet(WORKBOOK) : activeSheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),activeSheet
endfunction activeSheet
// Get a COM instance for the 'next' sheet
function NextSheet(Sheet) : p=get com pointer(Sheet,"Next") : if p<>0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif
endfunction next_sheet
function Direct2NextSheet(Sheet) : p=get com pointer(Sheet,"Next") : if p<>0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif : delete com sheet
endfunction next_sheet
// Get a COM instance for the 'previous' sheet
function PreviousSheet(Sheet) : p=get com pointer(Sheet,"Previous") : if p<> 0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif
endfunction next_sheet
// Get the name of a sheet
function sheet_name$(sheet) : name$=get com string(sheet,"Name")
endfunction name$
// Get the name of the active sheet
function ActiveSheetName(WORKBOOK)
 active=ActiveSheet(WORKBOOK)
 t$=get com string(active,"Name")
 delete com active
endfunction t$
function first_sheet(WORKBOOK)
  sheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),sheet
  repeat
    p=get com pointer(sheet,"Previous")
    if p<>0 then delete com sheet : make COM instance p,sheet
  until p=0
endfunction sheet

function find_sheet(WORKBOOK,name$)
 sheet=first_sheet(WORKBOOK)
 do
   if sheet_name$(sheet)=name$ : exitfunction sheet : endif
   sheet=Direct2NextSheet(Sheet)
   if sheet=0 then message "Sheet "+quote$(name$)+" does not exist" : exitfunction 0
 loop
endfunction 0
// Returns the number of sheets in the excel document
function sheet_count(WORKBOOK)
  SHEETS=FIND_FREE_COM() : make com instance get com pointer(WORKBOOK,"Sheets"),SHEETS // http://msdn.microsoft.com/en-us/library/office/ff193217(v=office.15).aspx
  SheetCount=get com integer(SHEETS,"Count") : delete com SHEETS
endfunction SheetCount

+ Code Snippet

global kernel32 as dword : kernel32=find free dll() : load dll "kernel32.dll",kernel32

file$="c:\test.xlsx"
if file exist(file$) : doc=open_Excel_doc(file$,1) : else : doc=new_Excel_doc(1) : endif
sheet=sheet_com_instance(doc,2)

for x=1 to 5
  for y=1 to 5
    inc v : excel_set_text$(sheet,x,y,str$(v))
  next y
next x
for x=1 to 5
  for y=6 to 10
    inc v : excel_set_value#(sheet,x,y,v+0.132456)
  next y
next x
for x=6 to 10
  for y=1 to 5
    inc v : excel_set_comment$(sheet,x,y,str$(v))
  next y
next x
for x=6 to 10
  for y=6 to 10
    inc v : excel_set_formula$(sheet,x,y,"=10*"+str$(v))
  next y
next x

message "text: "+excel_get_text$(sheet,7,7)
message "value: "+str$(excel_get_value#(sheet,2,2))
message "formula: "+excel_get_formula$(sheet,7,7)
message "value (calculated): "+str$(excel_get_value#(sheet,7,7))

close_excel_doc(doc)
end

// Returns a COM interface for a new instance of Excel, with a 3-sheet document - visual determins whether Excel is visible or not
function new_Excel_doc(visual)
      app=FIND_FREE_COM()
      LOAD COM CLASS "Excel.Application",app // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application_members(v=office.15).aspx
//  hwnd=get com integer(app,"Hwnd") // Window Handle!!! 
      Set Com Integer app,"Visible",visual
      Workbooks=FIND_FREE_COM() : make com instance get com pointer(app,"Workbooks"),Workbooks
      WORKBOOK=FIND_FREE_COM() : instance=Call Com Func(Workbooks,"Add") : make com instance instance,WORKBOOK
      delete com Workbooks : delete com app
endfunction WORKBOOK

// Attempts to open the document 'txt$' in Excel, and returns a COM interface of Excel - visual determins whether Excel is visible or not
function open_Excel_doc(txt$,visual)
  if file exist(txt$) 
      app=FIND_FREE_COM()
      LOAD COM CLASS "Excel.Application",app // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application_members(v=office.15).aspx
//  hwnd=get com integer(app,"Hwnd") // Window Handle!!! 
      Set Com Integer app,"Visible",visual
      Workbooks=FIND_FREE_COM() : make com instance get com pointer(app,"Workbooks"),Workbooks
      WORKBOOK=FIND_FREE_COM() : instance=Call Com Func(Workbooks,"Open","S",txt$) : make com instance instance,WORKBOOK
      delete com Workbooks : delete com app
    else
      errormessage "The file "+quote$(txt$)+" does not exist"
  endif
endfunction WORKBOOK

// Close the Excel document
function close_excel_doc(WORKBOOK) : 
 app=FIND_FREE_COM() : make com instance get com pointer(WORKBOOK,"Application"),app
 Call Com Func app,"Quit" : delete com app
endfunction
function excel_save_doc(WORKBOOK)
 call com func WORKBOOK,"Save"
endfunction

// Returns the name of the currently opened document
function doc_name$(WORKBOOK) : fullname$=get com string(WORKBOOK,"FullName")
endfunction fullname$



// Returns a COM pointer for 'ID' sheet in the 'WORKBOOK' document
function sheet_com_instance(WORKBOOK,ID)// Since I cannot figure how to use the Item(Index) function on the sheet-list, we have to go back/forth to find the proper sheet number - DUMB!
  sheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),sheet
  do
    index=get com integer(sheet,"Index")
    if index=ID then exitfunction sheet
    if index<ID then n=get com pointer(sheet,"Next") : if n<>0 : delete com sheet : make COM instance n,sheet : else : errormessage "Fault, looping through the sheets somehow failed" : exitfunction 0 : endif
    if index>ID then p=get com pointer(sheet,"Previous") : if p<>0 : delete com sheet : make COM instance p,sheet : else : errormessage "Fault, looping through the sheets somehow failed" : exitfunction 0 : endif
  loop
endfunction 0

// Returns a COM pointer for cell 'x','y' in 'sheet'
function excel_cell_com(sheet,x,y) // More nonsence, the Range command should be able to do this without flickering through the sheets and cells
  c=call com func(sheet,"Activate")
  app=FIND_FREE_COM() : make com instance get com pointer(sheet,"Application"),app
  call com func app,"Goto","S","R"+str$(y)+"C"+str$(x)
  cell=find_free_com() : make com instance get com pointer(app,"ActiveCell"),cell
  delete com app
endfunction cell


// These are pretty self-explaining, but some checking for whether a cell contains a formula could be added
function excel_set_text$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : set com string cell,"Value",value$ : delete com cell
endfunction
function excel_set_formula$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : set com string cell,"Formula",value$ : delete com cell
endfunction
function excel_set_value#(sheet,x,y,value#) : cell=excel_cell_com(sheet,x,y) : set com float cell,"Value",value# : delete com cell
endfunction
function excel_set_comment$(sheet,x,y,value$) : cell=excel_cell_com(sheet,x,y) : call com func cell,"AddComment","S",value$ : delete com cell
endfunction
function excel_get_text$(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value$=get com string(cell,"Text") : delete com cell
endfunction value$
function excel_get_formula$(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value$=get com string(cell,"Formula") : delete com cell
endfunction value$
function excel_get_value#(sheet,x,y) : cell=excel_cell_com(sheet,x,y) : value#=get com float(cell,"Value2") : delete com cell
endfunction value#

function FIND_FREE_COM()
  for com=1 to 256 : if com class exist(com)=0 : exitfunction com : endif : next : 
  errormessage "All COM-classes are in use!"
endfunction 0
function COMS_IN_USE() : for com=1 to 256 : inc counter,(com class exist(com)=0) : next com
endfunction counter

// Get a COM instance for the active sheet:
function ActiveSheet(WORKBOOK) : activeSheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),activeSheet
endfunction activeSheet
// Get a COM instance for the 'next' sheet
function NextSheet(Sheet) : p=get com pointer(Sheet,"Next") : if p<>0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif
endfunction next_sheet
function Direct2NextSheet(Sheet) : p=get com pointer(Sheet,"Next") : if p<>0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif : delete com sheet
endfunction next_sheet
// Get a COM instance for the 'previous' sheet
function PreviousSheet(Sheet) : p=get com pointer(Sheet,"Previous") : if p<> 0 : next_sheet=FIND_FREE_COM() : make COM instance p,next_sheet : endif
endfunction next_sheet
// Get the name of a sheet
function sheet_name$(sheet) : name$=get com string(sheet,"Name")
endfunction name$
// Get the name of the active sheet
function ActiveSheetName(WORKBOOK)
 active=ActiveSheet(WORKBOOK)
 t$=get com string(active,"Name")
 delete com active
endfunction t$
function first_sheet(WORKBOOK)
  sheet=FIND_FREE_COM() : make COM instance get com pointer(WORKBOOK,"ActiveSheet"),sheet
  repeat
    p=get com pointer(sheet,"Previous")
    if p<>0 then delete com sheet : make COM instance p,sheet
  until p=0
endfunction sheet

function find_sheet(WORKBOOK,name$)
 sheet=first_sheet(WORKBOOK)
 do
   if sheet_name$(sheet)=name$ : exitfunction sheet : endif
   sheet=Direct2NextSheet(Sheet)
   if sheet=0 then message "Sheet "+quote$(name$)+" does not exist" : exitfunction 0
 loop
endfunction 0
// Returns the number of sheets in the excel document
function sheet_count(WORKBOOK)
  SHEETS=FIND_FREE_COM() : make com instance get com pointer(WORKBOOK,"Sheets"),SHEETS // http://msdn.microsoft.com/en-us/library/office/ff193217(v=office.15).aspx
  SheetCount=get com integer(SHEETS,"Count") : delete com SHEETS
endfunction SheetCount

Back to top

Profile PM

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 22nd Mar 2014 00:19 Edited at: 22nd Mar 2014 00:20

Link

Crashes for me during open_Excel_doc, on the line make com instance instance,WORKBOOK, because instance has the value 0.

Been looking for a good way to read/write excel files so I will watch this.

Klan Kamakazee.com

Back to top

Profile PM Email Website

Manson_NS

20

Years of Service

User Offline

Joined: 4th May 2004

Location: Denmark

Posted: 22nd Mar 2014 10:30

Link

Well, this code is completely undocumented, which is simply because there's some rather big improvements still to be made, so I've not added the fault checks and whatnot to make it work in every scenario.

I have rectified the code above to check for whether reference is made to an existing file - otherwise it'll produce a new sheet.

The element to toy with is the visibility flag - it can run completely in the back, feeding the data directly to DB

Back to top

Profile PM

GIDustin

16

Years of Service

User Offline

Joined: 30th May 2008

Location:

Posted: 22nd Mar 2014 17:10

Link

Got it to work with your new updates.

How much more do you have to do on it before you think you got everything covered? Have you planned for things like cell formatting, sizes, etc?

Pretty neat stuff though!

Klan Kamakazee.com

Back to top

Profile PM Email Website

Manson_NS

20

Years of Service

User Offline

Joined: 4th May 2004

Location: Denmark

Posted: 22nd Mar 2014 19:38

Link

Well through the COM interface, all of Excel is accessible, so there's really too much functionality to cover it all.
For my initial purpose, all I need from this is to read and write Excel documents, so I don't think I'll add tons more for the time being.
My focus now would be to speed it up, since this is now an obvious tool over VBA based macros.

Back to top

Profile PM

Sorry your browser is not supported!

Code Snippets / [DBP] - [STYX, Matrix1, Blue GUI] Excel interface via COM/ OLE