#AutoIt3Wrapper_Au3Check_Parameters=-q -d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w- 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include "OOoCalc.au3"

Opt("WinTitleMatchMode", 2)

_OOoCalc_ComErrorHandler_UserFunction(_ErrFunc)

Global $sTitle = "Calc Demo"

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Create a new Book")
Global $oCalc = _OOoCalc_BookNew() ;Create test file
If @error Then Exit MsgBox($MB_TOPMOST, "Calc Test", "Unable to create new Book.")

WinWait("Calc")
WinSetState("Calc", "", @SW_MAXIMIZE)
WinActivate("Calc", "")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Get sheet list...")
Global $aSheets = _OOoCalc_SheetList($oCalc)
_ArrayDisplay($aSheets, "Sheet List")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set the sheet name ...")
_OOoCalc_SheetNameSet($oCalc, "CALC")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Write some text and data into the sheet...")
_OOoCalc_WriteCell($oCalc, "Table Demo", 0, 0) ;We can write to the cells by row or column
_OOoCalc_CellSetColors($oCalc, 0x0000FF, 0x00FF00, 0, 0) ;Foreground and Background color in 0xRRGGBB
_OOoCalc_WriteCell($oCalc, "'===================", "A2") ;...or with direct address
_OOoCalc_CellSetColors($oCalc, 0x000000, 0xFF0000, "A2")
For $iCol = 1 To 5 ;Create test data for Sheet1
	_OOoCalc_WriteCell($oCalc, "Number " & $iCol, 2, $iCol)
	_OOoCalc_HorizontalAlignSet($oCalc, $RIGHT, 2, $iCol)
	For $iRow = 3 To 5
		_OOoCalc_WriteCell($oCalc, Int(Random(10, 1000)), $iRow, $iCol) ;Write values
	Next
Next

For $iCounter = 2 To 6
	_OOoCalc_RangeSort($oCalc, "CALC", "B3:F6", $iCounter, True, True)
	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "B3:F6 Sorted by column " & Chr($iCounter + 64) & " (ascending)...")
Next

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, 'Replace "Demo" with "Test"...')
_OOoCalc_ReplaceInRange($oCalc, "Demo", "Test")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Transfer some data from an AutoIT array to the sheet")
Global $asArray[3][2] = [[11, 12],[13, 14],["TEST", 15]] ;Create a 2-d array
_ArrayDisplay($asArray, "Array to write in Calc")
_OOoCalc_WriteFromArray($oCalc, $asArray, "B8", -1, -1) ;Writes the array at the cell

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Insert hyperlink")
_OOoCalc_HyperlinkInsert($oCalc, "AutoIT", "http://www.autoitscript.com/", "F10") ;Insert hyperlink

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set alignment...")
_OOoCalc_HorizontalAlignSet($oCalc, $RIGHT, "B10")
_OOoCalc_HorizontalAlignSet($oCalc, $RIGHT, "F10")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Create a border around B8:C10...")
_OOoCalc_CreateBorders($oCalc, "B8:C10")

If MsgBox($MB_YESNO + $MB_ICONQUESTION + $MB_TOPMOST, $sTitle, "Would you like to print the active worksheet?") = $IDYES Then _OOoCalc_SheetPrint($oCalc)

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Transfer some data to an AutoIT array...")
Global $asRange = _OOoCalc_ReadSheetToArray($oCalc, "C4:H6")
_ArrayDisplay($asRange, "C4:H6")
Global $asSheet = _OOoCalc_ReadSheetToArray($oCalc)
_ArrayDisplay($asSheet, "Entire Sheet")

_OOoCalc_SheetAddNew($oCalc, "DEMO") ;Create a new sheet
MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Added a new sheet but it is not the active sheet...")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Copy some content from the actual sheet to the new sheet...")
_OOoCalc_RangeMoveOrCopy($oCalc, "B4:C7", "C2", 1, -1, "DEMO") ;Areas are either copied or moved

; This won't do anything if the default new workbook is set to only one sheet
MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Move sheet...")
_OOoCalc_SheetMove($oCalc, 1, "DEMO")

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Activate the new sheet...")
_OOoCalc_SheetActivate($oCalc, "DEMO") ;Change to this sheet
_OOoCalc_WriteCell($oCalc, "Total Data From " & _OOoCalc_SheetNameGet($oCalc), 0, 0)

; This won't do anything if the default new workbook is set to only one sheet
If $aSheets[0] > 1 Then
	For $iSheet = 2 To $aSheets[0]
		MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Remove " & $aSheets[$iSheet] & "...")
		_OOoCalc_SheetDelete($oCalc, $aSheets[$iSheet])
	Next
EndIf

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Searching for some numbers, color these cells, and add them with a formula...")
Global $iSum = 0
For $iCol = 2 To 3 ;Search sheet for numbers
	For $iRow = 1 To 3
		If _OOoCalc_ReadCell($oCalc, $iRow, $iCol) <> "" Then ;..and if found
			$iSum += _OOoCalc_ReadCell($oCalc, $iRow, $iCol) ;..sum total
			_OOoCalc_CellSetColors($oCalc, 0x0000FF, 0x00FF00, $iRow, $iCol) ;Colors in 0xRRGGBB
		EndIf
	Next
Next
_OOoCalc_WriteCell($oCalc, "Grand Total:", 6, 2) ;Write string in sheet
_OOoCalc_WriteCell($oCalc, $iSum, 6, 3) ;Write value in sheet
_OOoCalc_WriteCell($oCalc, "SUM(D2:D4)----->", 8, 2) ;Write string in sheet
_OOoCalc_WriteFormula($oCalc, "=SUM(D2:D4)", 8, 3) ;Write formula in sheet

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, 'Find "Grand"')
Global $aFind = _OOoCalc_FindInRange($oCalc, "Grand")
_ArrayDisplay($aFind, 'Find "Grand"')

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Format text...")
_OOoCalc_FontSetProperties($oCalc, "A1", -1, -1, -1, -1, False, False, True) ;Underline
_OOoCalc_FontSetProperties($oCalc, "C7", -1, -1, -1, -1, True, False, False) ;Bold
_OOoCalc_FontSetProperties($oCalc, "C9", -1, -1, -1, -1, False, True, False) ;Italicize

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Number format cells...")
_OOoCalc_NumberFormat($oCalc, $NUMBER_1000INT, 6, 3) ;Format number with thousands separator
_OOoCalc_NumberFormat($oCalc, $NUMBER_1000INT, 8, 3) ;Format number with thousands separator

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set height of row 9...")
_OOoCalc_RowSetProperties($oCalc, 8, 1000, False, True, False) ;Set column height

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set width of first column to optimum and second column to 10mm...")
_OOoCalc_ColumnSetProperties($oCalc, 0, 5000, True, True, True) ;Set optimal column width
_OOoCalc_ColumnSetProperties($oCalc, 1, 1000, False, True, False) ;Set second optimal column width
_OOoCalc_ColumnSetProperties($oCalc, 2, 5000, True, True, False) ;Set third optimal column width

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set row colors...")
_OOoCalc_RowSetColors($oCalc, 0xFF00F0, 0xF0FFF0, 0)

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Delete row 8...")
_OOoCalc_RowDelete($oCalc, 7)
MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Insert 3 rows at row 5...")
_OOoCalc_RowInsert($oCalc, 4, 3)

MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Insert a column ...")
_OOoCalc_ColumnInsert($oCalc, 0)
MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Set column colors ...")
_OOoCalc_ColumnSetColors($oCalc, 0xFFFFFF, 0x000000, 0)
MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Delete the column ...")
_OOoCalc_ColumnDelete($oCalc, 0)

If MsgBox($MB_YESNO + $MB_ICONQUESTION + $MB_TOPMOST, $sTitle, "Would you like to save the workbook?") = $IDYES Then
	Global $sFile = @ScriptDir & "\calcdemo"
	Global $aFiles[5][2] = [["ods", ""],["xls", "MS Excel 97"],["pdf", "calc_pdf_Export"],["txt", "Text - txt - csv (StarCalc)"],["html", "HTML (StarCalc)"]]

	Global $sMessage = "Save the book in:" & @CRLF
	For $iType = 0 To UBound($aFiles) - 1
		$sMessage &= $aFiles[$iType][0] & @TAB & $aFiles[$iType][1] & @CRLF
	Next
	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, $sMessage)

	;Save in different formats
	For $iType = 0 To UBound($aFiles) - 1
		_OOoCalc_BookSaveAs($oCalc, StringFormat("%s.%s", $sFile, $aFiles[$iType][0]), $aFiles[$iType][1])
	Next
	_OOoCalc_BookClose($oCalc)

	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Show the stored files...")
	For $iType = 0 To UBound($aFiles) - 1
		ShellExecute(StringFormat("%s.%s", $sFile, $aFiles[$iType][0]))
		MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, "Calc " & $aFiles[$iType][0], "Press OK")
	Next
Else
	_OOoCalc_BookClose($oCalc)
EndIf

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc