Excel helper wrapper

User projects written in or related to FreeBASIC.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Mar 11, 2010 16:11

Added:

xlsplit

Modified:
xlsort - fixed bug when using with Excel 2000.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Mar 11, 2010 20:07

The xlhelper wrapper is now on a Windows Live Skydrive (see link in first post). Updates to the wrapper and examples will now be posted at that link. I am currently working on creating example code listings for each command and will eventually create documentation for the wrapper (if I can find time).
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Mar 12, 2010 22:26

Last edited by phishguy on Mar 13, 2010 17:46, edited 3 times in total.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Mar 13, 2010 16:33

phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Mar 22, 2010 18:07

Added:

xlgetworkbook 'gets the workbook object
xladdmodule 'adds a VBcomponent module
xladdmacro(strcode) 'adds the macro defines in the string
xlrunmacro("MyMacro") 'runs the macro "MyMacro"
xladdbutton(100,100,100,40) 'add a button
xlshapetext("My Macro") 'set the button text
xlonaction("MyMacro") 'set the button action
xlshapetextcolor(rgb(0,0,255)) 'set the button color
xlshapetextfont("Lucida Handwriting") 'set the button font
xlshapetextfontsize(16) 'set the font size


Note: Macro security must be diabled for the xladdmacro function to work.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Apr 06, 2010 21:53

Added:
xlprint

This will print the current sheet to the active printer with an optional number of copies.

Note: If you happen to have Excel, this can be a quick and convenient way to print out your graphics screen.

Here is an example:

Code: Select all

#include "xlhelper.bas"
screenres 400,480,8
color 0,15
cls
circle (200,239),100,0
draw string (120,239),"Freebasic is Great!"
bsave "temp.bmp",0 'save screen as bitmap
xlstart(1,0) 'open excel hidden
xlputimagefromfile("temp.bmp",0,0) 'put image in spreadsheet
xlprint 'print spreadsheet
xlsaved 'supress file save dialog
xlquit 'close excel workbook
xlrelease 'release excel objects
kill ("temp.bmp") 'delete temporary bitmap file
sleep
end


You also would have the flexibility of rotating and/or resizing the image before printing.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Postby phishguy » Jul 29, 2010 15:06

It's been awhile since I added anything to this wrapper. I started playing around with graphing and I found that I hate the chart wizard. So, I've started adding commands to add and change chart parameters without the wizard. I've just started. So, all I have so far is:

XLChartAddV2
XLChartType

This works on Excel 2007. But it doesn't work on Excel 2000. I'm not sure about other versions of Excel.

You will need the latest version of xlhelper. The link is in the first post.

Here is an example that shows pretty fast real time graph updates.

Code: Select all

#include "xlhelper.bas"

xlstart(1)

Dim sval(255) As String

For x As Integer = 0 To 255
    sval(x) = "0"
    Print sval(x)
Next x

xlpastearray(sval(),,1)
Dim xlwind As hwnd
xlwind = xlhandle
setforegroundwindow(xlwind)

xlgetrange("A1:A256" )
xlchartaddV2
xlcharttype(4,false)

For z As Integer = 1 To 5   
   
    For y As Integer = 1 To 50
       
        For x As Integer = 0 To 255
            sval(x) = Str(Sin(x/y) * 20)
        Next x   
       
        xlpastearray(sval(),,1)
        Sleep 50
    Next y
   
    For y As Integer = 50 To 1 Step -1
       
        For x As Integer = 0 To 255
            sval(x) = Str(Sin(x/y) * 20)
        Next x   
       
        xlpastearray(sval(),,1)
        Sleep 50
    Next y
   
Next z

xlsaved
xlquit
xlrelease




<edit>
Here is another one with 2 data sets.

Code: Select all

#include "xlhelper.bas"

xlstart(1)

Dim sval(1 to 255,1 to 2) As String
 
For x As Integer = 1 To 255
    sval(x,1) = "0"
    sval(x,2) = "0"
Next x

xlpastearray(sval(),"A1",true)
Dim xlwind As hwnd
xlwind = xlhandle
setforegroundwindow(xlwind)

xlgetrange("A1:B255" )
xlchartaddV2
xlcharttype(4,false)

For z As Integer = 1 To 5   
   
    For y As Integer = 1 To 50
       
        For x As Integer = 1 To 255
            sval(x,1) = Str(Sin(x/y) * 20 - 20)
            sval(x,2) = str(sin((255-x)/y) * 20 + 20)
        Next x   
       
        xlpastearray(sval(),"A1",true)
        Sleep 50
    Next y
   
    For y As Integer = 50 To 1 Step -1
       
        For x As Integer = 1 To 255
            sval(x,1) = Str(Sin(x/y) * 20 - 20)
            sval(x,2) = str(sin((255-x)/y) * 20 + 20)
        Next x   
       
        xlpastearray(sval(),"A1",true)
        Sleep 50
    Next y
   
Next z

xlsaved
xlquit
xlrelease

phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Re: Excel helper wrapper

Postby phishguy » Nov 10, 2013 18:34

I have added a few more functions recently.

xltableadd
xltablestyle
xltableshowtotals
xltableaddcalc
xltablerename
xlfiledialog
xlsetkey
xlsort2
xlcellsselect
xlrangeselect2

Here is an example for sorting on a key and creating a table.

Code: Select all

#include "xlhelper.bas"

'start Excel with 1 worksheets
xlstart(1)

dim gpa as single
dim rowvalue as integer = 3
dim offset as integer = 3
dim as string names(5) = {"Chris","Ryan","Betty","Fred","Ann"}
dim as string classname(5) = {"English","History","Physics","Geometry","Health"}
dim as string header(4) = {"Student","Class","Semester","GPA"}

'put in the header string values
for x as integer = 0 to 3
    xlputvalue(rowvalue,x + 1 + offset,header(x))
next x

'fill the worksheet with data
for semester as integer = 1 to 2
    for hclass as integer = 0 to 4
        for hname as integer = 0 to 4
            gpa = int(rnd * 30) / 10 + 1
            rowvalue += 1
            xlputvalue(rowvalue,offset + 1,names(hname))
            xlputvalue(rowvalue,offset + 2,classname(hclass))
            xlputvalue(rowvalue,offset + 3,str(semester))
            xlputvalue(rowvalue,offset + 4,str(gpa))
        next hname
    next hclass
next semester

'set the number format for the GPA column
xlcellsselect(4,offset + 4,rowvalue,offset + 4)
xlnumberformat("0.0")

'set the sort key for the name column data
xlsetkey(4,offset + 1,rowvalue,offset + 1)

'sort the entire range of data using the sort key
xlsort2(4,offset + 1,rowvalue,offset + 4)

'select a range for the table
xlrangeselect2(3,offset + 1,rowvalue,offset + 4)

'add the table
xltableadd

'set the table style
xltablestyle

'select cell A1
xlcellselect(1,1)

'release the objects and uninitialize Excel
xlrelease
MaverickM
Posts: 2
Joined: Feb 26, 2014 11:40

Re: Excel helper wrapper

Postby MaverickM » Feb 26, 2014 11:49

This looks like a really useful wrapper! However, i think the download link is broken? It just sends me to a one-drive log-in page.

Can you provide an alternative link? Thanks!
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Re: Excel helper wrapper

Postby phishguy » Feb 26, 2014 14:58

MaverickM
Posts: 2
Joined: Feb 26, 2014 11:40

Re: Excel helper wrapper

Postby MaverickM » Feb 26, 2014 15:29

That did the trick! Thanks.
JohnK
Posts: 279
Joined: Sep 01, 2005 5:20
Location: Earth, usually
Contact:

Re: Excel helper wrapper

Postby JohnK » Jan 28, 2015 19:14

This gives me an error trying to download off OneDrive (I refer to it as NoneDrive).
Since it is a .bas file could you please put it in a 'code box' in this thread?
Thanks
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Re: Excel helper wrapper

Postby phishguy » Jan 30, 2015 2:57

Sure thing. See first post.
BasicScience
Posts: 489
Joined: Apr 18, 2008 4:09
Location: Los Angeles, CA
Contact:

Re: Excel helper wrapper

Postby BasicScience » Jan 17, 2016 5:52

Excel wrapper is a great tool, but even after closing it down with xlquit and xlrease there still is an excel.exe process running (as shown by the Processes tab in the Task Manager. Moreover, if you make several calls to xlstart and xlopen, then there are multiple processes that are left running.
Zippy
Posts: 1295
Joined: Feb 10, 2006 18:05

Re: Excel helper wrapper

Postby Zippy » Jan 17, 2016 16:33

BasicScience wrote:Excel wrapper is a great tool, but even after closing it down with xlquit and xlrease there still is an excel.exe process running (as shown by the Processes tab in the Task Manager. Moreover, if you make several calls to xlstart and xlopen, then there are multiple processes that are left running.

I can't replicate your experience that excel.exe remains after using xlquit() and xlrelease() to close and release a single instance. Would be helpful to know what you are doing, exactly - are you opening an existing book/sheet or creating one, and what exactly are you doing with it after it is open? Can you post compilable and runnable test code that demos your fail?

You can effect/control multiple instances (multiple open books) by copying the global internal "xlapp" variable that is used when an instance of excel is created. Hopefully this code will be comprehensible:

Code: Select all

'xlhelper.bas test, multiple instances
'' Does excel.exe remain after xlquit() & xlrelease()
''  Compile as console prog.
''  Tested Win10 64bit, Excel 2013, FB 1.04.0 (10-01-2015), built for win32 (32bit)
'
#include "xlhelper.bas"
'
'create 2 placeholder structures, 1 for each
'' book we are opening or creating
dim as IDispatch ptr book1,book2
'
'create 1st book
xlstart()
print "start book1 xlapp = ";xlapp
book1=xlapp 'store book1 pointer
'
sleep 2000
'
'create 2nd book
xlstart()
print "start book2 xlapp = ";xlapp
book2=xlapp 'store book2 pointer
'
sleep 2000
'
xlapp=book1 'set internal pointer to book1
xlquit()
xlrelease()
print "End book1 xlapp = ";xlapp
'
xlapp=book2 'set interal pointer to book2
xlquit()
xlrelease()
print "End book2 xlapp = ";xlapp
'
print
print "Exit in 5 seconds....."
sleep 5000

Note: What you see here is all of the testing I've performed of this premise. Caveat emptor.
.

Return to “Projects”

Who is online

Users browsing this forum: Majestic-12 [Bot] and 5 guests