28 replies to this topic

[ptrex]

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).

AutoIt        expandcollapse  popup

;
; 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, 14 September 2012 - 09:22 AM.

[blink314]

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

Kevin

[randallc]

OWC11.spreadsheet
Randall

[blink314]

Ahhhh... much thanks!

Kevin

[blink314]

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

[ptrex]

I will add Office 2003 to the script.

[maqleod]

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

[blink314]

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

[randallc]

You're not in luck today!
"OWC9.spreadsheet"
randall

Edited by randallc, 14 November 2005 - 03:21 AM.

[ptrex]

@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, 19 May 2008 - 03:49 PM.

[litlmike]

This is awesome! I gotta find something to use this for.

[Locodarwin]

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

[ptrex]

@litlmike

I PM you about the sorting using the ADOR.Recordset.

But of course if you use the EXCEL Grid, you have all the native sorting options available in the grid.

Up to you what you like best.

regards,

ptrex

[Radsam]

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

[Locodarwin]

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

[Radsam]

Thanks, that works great.

[Will66]

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"

AutoIt        expandcollapse  popup

#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

[SandelPerieanu]

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

AutoIt        expandcollapse  popup

#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

[ptrex]

@psando.ro

This is not intended to be used for opening an XLS file.

But if you insist on opening a file to load into the XLS object you will have to use this.

$Spreadsheet1.XMLURL = "<a href='http://example.microsoft.com/Test.xml' class='bbc_url' title='External link' rel='norewrite nofollow external'>http://example.microsoft.com/Test.xml"</a>

regards,

ptrex

[Eigensheep]

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, 17 May 2008 - 10:54 AM.