Jump to content

Excel Grid in AutoIT


ptrex
 Share

Recommended Posts

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
Link to comment
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

Link to comment
Share on other sites

@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
Link to comment
Share on other sites

  • 11 months later...

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]
Link to comment
Share on other sites

Link to comment
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]
Link to comment
Share on other sites

  • 3 months later...

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
Link to comment
Share on other sites

  • 6 months later...

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

Link to comment
Share on other sites

@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 = "http://example.microsoft.com/Test.xml"

regards,

ptrex

Link to comment
Share on other sites

  • 7 months later...

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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...