Jump to content
pyzonet

Export Listview to Excel & Print!

Recommended Posts

pyzonet

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
erikkn

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
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
antonioj84
;The export to excel is not happening I added the appropriate nam change, the scripts run but it not exporting to excel, any assistance willbe great


;###############################
;#      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 !",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 = _Excel_BookNew($lv)
For $colexcel = 1 To $col
_Excel_RangeWrite($excel,"Col " & $colexcel,1,$colexcel)
    $i = 0
    do
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,0),2 + $i,1)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,1),2 + $i,2)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,2),2 + $i,3)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,3),2 + $i,4)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,4),2 + $i,5)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,5),2 + $i,6)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,6),2 + $i,7)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,7),2 + $i,8)
        _Excel_RangeWrite($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

 

Share this post


Link to post
Share on other sites
water

You need to add some error checking to your script. What is the value of @error after calling a function?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
antonioj84
Func exporttoexcel();EXPORT TO EXCEL

$col = 9
$count = _GUICtrlListView_GetItemCount($lv)
GUICtrlSetState($export,$gui_disable)
$excel = _Excel_BookNew($lv)


if @error Then MsgBox(0,'error', @error)  ; this retun 1

For $colexcel = 1 To $col
_Excel_RangeWrite($excel,"Col " & $colexcel,1,$colexcel)
    $i = 0
    do
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,0),2 + $i,1)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,1),2 + $i,2)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,2),2 + $i,3)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,3),2 + $i,4)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,4),2 + $i,5)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,5),2 + $i,6)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,6),2 + $i,7)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,7),2 + $i,8)
        _Excel_RangeWrite($excel, _GUICtrlListView_GetItemText($lv,$i,8),2 + $i,9)
    $i = $i + 1
    Until $i = $count
Next

 

Share this post


Link to post
Share on other sites
water

@error = 1 is described in the help file ;-)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×