Sign in to follow this  
Followers 0
ptrex

Excel Grid in AutoIT

32 posts in this topic

#1 ·  Posted (edited)

Excel Grid in AutoIT

For those who are interested in Excel.

Helps you using Excel function and use the results of these back in AutoIT variables.

Also this can be used as a replacement for the ListView, and load the data direct in the Excel Grid.

Exporting to an Excel file is standard included (look at the toolbar icons).

;
; Script generated by AutoBuilder 0.5 Prototype
; Embedded Excel Object in AutoIt
; Helps you using Excelfunction and use the results of these back in AutoIT vars.
; Also this can be used as a replacement for the ListView, and load the data direct in the Excel Grid.
; PTREX 09/11/05
;

#include <GUIConstants.au3>
#NoTrayIcon

;Vars
Dim $oMyError
Dim $i
Dim $j

;Declare objects
$oExcel = ObjCreate("OWC10.spreadsheet"); Default to Office XP

If not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003   
EndIf
IF not IsObj($oExcel) Then
    $oExcel = ObjCreate("OWC00.spreadsheet"); Office 2000
EndIf
    If IsObj($oExcel) Then
        with $oExcel
        ;.Worksheets ("Sheet1").Activate
        ;.activesheet.range ("A1:B10").value = "TEST INFO"
            for $i = 1 to 15
                for $j = 1 to 15
                .cells($i,$j).value = $i
                next
            next
        EndWith
    Else
     MsgBox(0,"Reply","Not an Object",4)
EndIf

;Main Gui
GuiCreate("Excel Object", 802, 590,(@DesktopWidth-802)/2, (@DesktopHeight-590)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

$GUI_ActiveX = GUICtrlCreateObj ($oExcel, 10, 10 , 780 , 550)
GUICtrlSetStyle ( $GUI_ActiveX, $WS_VISIBLE )
GUICtrlSetResizing ($GUI_ActiveX,$GUI_DOCKAUTO) ; Auto Resize Object
    
GuiSetState()
While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    EndSelect
WEnd
Exit



;This is Sven P's custom error handler
Func MyErrFunc()
$HexNumber=hex($oMyError.number,8)
Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"     & @CRLF & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber             & @CRLF & _
             "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "     & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
SetError(1) ; to check for after this function returns
Endfunc

Office 2003 object added.

Enjoy

Edited by ptrex
1 person likes this

Share this post


Link to post
Share on other sites



Tried your script... any ideas what the Excel object declaration should be for Office 2003? Thanks

Kevin

Share this post


Link to post
Share on other sites

Ahhhh... much thanks!

Kevin

Share this post


Link to post
Share on other sites

Wow! That is really cool. Is there any way to customize the grid? Like get rid of the toolbar, make it non-resizable, write protect it, etc? I dont know much about objects (my only experience is with the SQL stuff). Or, is there a thread already started about the Excel object?

Kevin

Share this post


Link to post
Share on other sites

Is there any chance that this could possibly be used with OpenOffice spreadsheets? or is it limited to excel?


[u]You can download my projects at:[/u] Pulsar Software

Share this post


Link to post
Share on other sites

I just tried to use this with Office 2000 and got a Not an Object error. Any ideas? I have a liscenced version but I did not install everything (Outlook is not installed).

Kevin

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

@blink314

You need outlook to be installed in order to have this feature available.

OWC stands for Office Web Components.

PS: I have added this to my SQLite example, so that data can be exported from the LV to this Excel Object directly. This can be incorporated into you database.au3 project. I can do this for you if you want. Let me know.

@randallc

Hi I see that you trying to help out here. That's nice I appreciate this. Thanks.

Like I said above. I finalized an Listview export to this Excel object. I will post the new code in the SQLite example post.

@maqleod

Can this be used for OpenOffice spreadsheets ?

It can but it all depends what you want to do with it. If you want to read data from an OpenOffice spreadsheet or do you want to export to an OpenOffice Spreadsheet. Export to an OO spreadsheet is simple because OO can read Excel format.

To export press EXPORT 2 XLS button in the toolbar save it, and open this files later using OO Spreadsheet.

To import data from OO spreadsheet, this might be possible but I don' t know the tips and trics to do it right away.

Look here for the updated code including export to OWC Excel object.

http://www.autoitscript.com/forum/index.ph...mp;#entry123791

Enjoy.

Edited by ptrex

Share this post


Link to post
Share on other sites

For programming information regarding this embedded object, check out this file, which should be installed on any system that has the OWC set installed:

OWCDSS11.CHM

Down towards the bottom, there is a section called "Programming Information," which describes all of the properties, methods, collections, etc. supported by the OWC Spreadsheet component.

This should help you find the information you're looking for, Blink314. With this object you can do most if not all of the things you were asking about.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

Can this be done for opening an existing excel document instead of creating a new one?

Share this post


Link to post
Share on other sites

Unfortunately, no. You cannot open an existing workbook or sheet into this component, at least not directly.

You can, however, attach it to an external data source, and in doing so it would be possible to pull data from an existing workbook. It's not a simple task, however, and you'd likely be better off just C&P from the existing workbook into the new OWC.Spreadsheet.

Exporting from this component is rather straightforward, though there are limitations in how much/what kind of data can be exported.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Thanks, that works great.

Share this post


Link to post
Share on other sites

Tried your script... any ideas what the Excel object declaration should be for Office 2003? Thanks

Kevin

A machine at work has office 97 on it so will test it tommorow, but this is working for me using VersionIndependentProgID: "OWC.Spreadsheet"

#include <GUIConstants.au3>

$Form1 = GUICreate("AForm1", 600, 600, 193, 115,$WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )
GUISetState(@SW_SHOW)
$Obj1 = ObjCreate("OWC.Spreadsheet")
;$Obj1.visible=1

$Obj1_ctrl = GUICtrlCreateObj($Obj1,20, 20,500,500)

With $Obj1
.AutoFit=0
 .DisplayColHeaders=-1
  .DisplayGridlines=-1
  .DisplayHorizontalScrollBar=-1
  .DisplayRowHeaders=-1
  .DisplayTitleBar=-0
  .DisplayToolbar=0
  .DisplayVerticalScrollBar=-1
  .EnableAutoCalculate=-1
  .EnableEvents=-1
  .MoveAfterReturn=-1
  .MoveAfterReturnDirection=0
  .RightToLeft=0
  ;.ViewableRange="1:65536"

EndWith
With $Obj1
    .cells(1,1).value = "hello world"
    ;.range("A1:B2").clear
Endwith

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        
    EndSwitch
WEnd

Share this post


Link to post
Share on other sites

A machine at work has office 97 on it so will test it tommorow, but this is working for me using VersionIndependentProgID: "OWC.Spreadsheet"

#include <GUIConstants.au3>

$Form1 = GUICreate("AForm1", 600, 600, 193, 115,$WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )
GUISetState(@SW_SHOW)
$Obj1 = ObjCreate("OWC.Spreadsheet")
;$Obj1.visible=1

$Obj1_ctrl = GUICtrlCreateObj($Obj1,20, 20,500,500)

With $Obj1
.AutoFit=0
 .DisplayColHeaders=-1
  .DisplayGridlines=-1
  .DisplayHorizontalScrollBar=-1
  .DisplayRowHeaders=-1
  .DisplayTitleBar=-0
  .DisplayToolbar=0
  .DisplayVerticalScrollBar=-1
  .EnableAutoCalculate=-1
  .EnableEvents=-1
  .MoveAfterReturn=-1
  .MoveAfterReturnDirection=0
  .RightToLeft=0
  ;.ViewableRange="1:65536"

EndWith
With $Obj1
    .cells(1,1).value = "hello world"
    ;.range("A1:B2").clear
Endwith

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        
    EndSwitch
WEnd

how i can open a file *.xls with owc11 ?

thanks

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

Can you access the object as a regular COM object? I don't seem to be able to use it for anything.

Edited by NumberDaemon

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0