AutoIT – Exporting QlikView data to Excel

As I showed before QV has a COM interface.

So we can use it in our AutoIT scripting event to extract data from the QV client (or any other object !) and save it to Excel.

It basically means you can manipulate the data in the QV Document and export to Excel or upload to SharePoint is you like.

Even the graphs are exportable.

Here we go for a simple example.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$Qview = ObjCreate("QlikTech.QlikView")

if IsObj($Qview) Then
	MsgBox(0,"",$Qview.OSName & " " &$Qview.OSVersion)
Else
	MsgBox(0,"","NOK")

EndIf


$Qview.MsgBox("QlikView Messagebox Qv_Version " & $Qview.QvVersion )

$Qview.OpenDoc("C:\_\QlikView\SALES.qvw")

WinSetState("QlikView", "", @SW_MAXIMIZE)

$ActiveDoc = $Qview.ActiveDocument

; INSERT HERE and start every call with "QvDoc."
Local $Sheet, $obj, $w, $h

$Sheet = $ActiveDoc.ActivateSheet("DataSheet")

; Right CLick on the object to see the Object ID, don't use the Sheet ID !
$obj = $ActiveDoc.GetSheetObject("TB02")

$w = $obj.GetColumnCount
$h= $obj.GetRowCount

If $h >=  1 Then
	; Export the full object
	ConsoleWrite( $w  & " " & $h & @CRLF)
	$obj.ExportBiff(@ScriptDir & "\QV_Export_Test.xls") 
EndIf

; Get the distinct ItemGroup Field Values
$Values = $ActiveDoc.Fields("ItemGroup").GetPossibleValues 

for $i=0 to $Values.Count-1
  $curVal = $Values.Item($i).Text
  ;Select is like a click selection in the QV Client
  $ActiveDoc.Fields("ItemGroup").Select ($curVal) 
  ; Exports a file for each ItemGroup Field Distinct Selection, see above
  $obj.ExportBiff(@ScriptDir & "\QV_Export_Test" & $curVal &".xls") 
  ConsoleWrite($curVal & @CRLF)
next


ConsoleWrite(@ScriptDir & "\QV_Export_Test.xls" & @CRLF)

$Sheet.FitZoomToWindow

ConsoleWrite($ActiveDoc.GetVariable("MyVarTest") & @CRLF)
ConsoleWrite($ActiveDoc.GetPathName & @CRLF)

;$Qview.Quit


Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM 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)
Endfunc

Nice to see such a seamless cooperation. A rather new BI technology supporting an old COM interface.

This make me happy Smile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: