#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"
#include "OOoCalc (1).au3"

Opt("WinTitleMatchMode", 2)

_OOoCalc_ComErrorHandler_UserFunction(_ErrFunc)

_Example()
Func _Example()
	Local $sTitle = "Calc Demo"

	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Create a new Book")
	Local $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...")
	Local $aSheets = _OOoCalc_SheetList($oCalc)
	_ArrayDisplay($aSheets, "Sheet List")
	Local $sSheet1 = $aSheets[1]
	Local $sSheet2 = $aSheets[2]
	Local $sSheet3 = $aSheets[3]

	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

	; Don't understand why the sort is not working
	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")
	Local $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")

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

	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Transfer some data to an AutoIT array")
	Local $asRange = _OOoCalc_ReadSheetToArray($oCalc, "C4:H6")
	_ArrayDisplay($asRange, "C4:H6")
	Local $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
	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Remove Sheet2...")
	_OOoCalc_SheetDelete($oCalc, $sSheet2)

	; This won't do anything if the default new workbook is set to only one sheet
	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Remove Sheet3...")
	_OOoCalc_SheetDelete($oCalc, $sSheet3)

	MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, "Searching for some numbers, color these cells, and add them with a formula...")
	Local $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"')
	Local $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)

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

		Local $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

		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
	EndIf

	_OOoCalc_BookClose($oCalc)

EndFunc   ;==>_Example



; 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