Sign in to follow this  
Followers 0
pyzonet

Export Listview to Excel & Print!

3 posts in this topic

Export Listview to Excel & Print!

Posted Image

;###############################
;#      Author: Pyzonet        #
;###############################

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Excel.au3>
#NoTrayIcon

Local $style = BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)
Local $msg

GUICreate("Export Listview to Excel & Print!",500,500); CREATE GUI
$lv = GUICtrlCreateListView("",0,0,500,450)
_GUICtrlListView_SetExtendedListViewStyle(-1,$style)
_GUICtrlListView_SetSelectedColumn(-1, 0)
$export = GUICtrlCreateButton("EXPORT TO EXCEL",20,460,120,30)
$exit = GUICtrlCreateButton("EXIT",360,460,120,30)

colanditems()

GUISetState(@SW_SHOW)

While 1

    Select
        Case $msg = $export
            exporttoexcel()

        Case $msg = $exit
            Exit
    EndSelect

        $msg = GUIGetMsg()

        If $msg = $GUI_EVENT_CLOSE Then ExitLoop
    WEnd

GUIDelete()

Func colanditems(); CREATE COLUMNS, ITEMS AND SUBITEMS
For $x = 1 to 9
    _GUICtrlListView_InsertColumn($lv,$x,"Col " & $x, 100)
    _GUICtrlListView_AddItem($lv, "Row " & $x & " item " & $x)
    For $x2 = 1 to 10
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 1", 1)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 2", 2)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 3", 3)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 4", 4)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 5", 5)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 6", 6)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 7", 7)
    _GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 8", 8)
    Next
Next
EndFunc

Func exporttoexcel();EXPORT TO EXCEL
$col = 9
$count = _GUICtrlListView_GetItemCount($lv)
GUICtrlSetState($export,$gui_disable)
$excel = _ExcelBookNew()
For $colexcel = 1 To $col
_ExcelWriteCell($excel,"Col " & $colexcel,1,$colexcel)
    $i = 0
    do
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,0),2 + $i,1)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,1),2 + $i,2)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,2),2 + $i,3)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,3),2 + $i,4)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,4),2 + $i,5)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,5),2 + $i,6)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,6),2 + $i,7)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,7),2 + $i,8)
        _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,8),2 + $i,9)
    $i = $i + 1
    Until $i = $count
Next

$ask = MsgBox(4,"Message","Export completed!" & @cr & @cr & "Do you want to print it now?")
if $ask = 6 Then
    Send("^p")
EndIf

EndFunc

Note: It's just an example! Do not expecting more than this!

export listview to excel.au3


Example Script(s): Export Listview to Excel & Print!....(¯`v´¯)..... ·.¸.·´...¸.·´.. (☻//▌♥♥/ \ ... Wake up the dawn and ask her why ...╔══╗ ♥ ♫ ♥║██║ ♫ ♥ ♫║(O)║♥A dreamer dreams she never dies♥╚══╝۩۞۩ஜ1984'09ஜ۩۞۩

Share this post


Link to post
Share on other sites



It's very nice, thank you! I got a question about your script: is it possible to make a nice layout, with colors and more of that?

Share this post


Link to post
Share on other sites

Depending on the size of the ListView performance might be an issue.

I suggest to try my rewrite of the Excel UDF. It's about 20 to 100 times faster when filling data into a worksheet. And it has a function to print worksheets, ranges etc. so no need to automate the Excel GUI using Send.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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