REM  *****  BASIC  *****

REM Remove duplicates
Dim oDialog1 As Object, oDoc As Object, oController As Object, oSheet As Object

Sub RemoveDuplicates
    oDoc = ThisComponent
    oController = oDoc.getCurrentController
    oSheet = oController.activeSheet

    Dim oSel As Object
    oSel = oDoc.getCurrentSelection()

    InitLanguage

    If not oSel.supportsService("com.sun.star.sheet.SheetCellRange") then
        MsgBox getText(0)
        Exit sub
    End If

    Dim sourceAddress
    sourceAddress = oSel.getRangeAddress

    If oSel.supportsService("com.sun.star.sheet.SheetCell") then
        ExtendSingleSelection(oSel)
        oSel = oDoc.getCurrentSelection()
    End If

    If oSel.supportsService("com.sun.star.sheet.SheetCell") then
        MsgBox getText(1)
        Exit Sub
    End If

    ' Do not allow selecting the whole sheet: it would simply be too long ("hang")
    ' In the future, the max column coult may be increased - use ">=" here
    If ((oSel.Columns.Count >= 1024) And (oSel.Rows.Count = 1024*1024)) Then
        MsgBox "The whole sheet selected; this will hang the operation - aborting"
        Exit Sub
    End If

    DialogLibraries.LoadLibrary("RemoveDuplicates")
    oDialog1 = CreateUnoDialog( DialogLibraries.RemoveDuplicates.Options )
    UpdateDialogUI
    cbIncludeTitle
    If oDialog1.Execute() = 0 Then
        oRange = oSheet.getCellRangeByPosition(sourceAddress.StartColumn, sourceAddress.StartRow, sourceAddress.EndColumn, sourceAddress.EndRow)
        oController.select(oRange)
    End If
End Sub

Sub UpdateDialogUI
    oDialog1.GetControl("OptionHint").Text = getText(5)
    oDialog1.GetControl("btnSelectAll").Label = getText(3)
    oDialog1.GetControl("cbTitle").Label = getText(4)
    oDialog1.GetControl("btnOK").Label = getText(6)
    oDialog1.GetControl("btnCancel").Label = getText(7)
End Sub

Sub UpdateListBoxItems (hasTitle As Boolean)
    Dim oSel, lbList
    oSel = oDoc.getCurrentSelection()
    lbList = oDialog1.GetControl("lbList")
    lbList.removeItems(0, lbList.getItemCount())
    If hasTitle Then
        Dim aDataArray(), lColumns As Long, lColumn As Long
        aDataArray = oSel.getDataArray
        lColumns = ubound(aDataArray(0))
        For lColumn = 0 to lColumns
            lbList.addItem(aDataArray(0)(lColumn), lColumn)
        Next lColumn
    Else
        Dim oAddress, columns, i
        oAddress = oSel.getRangeAddress
        columns = oSheet.Columns
        For i = oAddress.StartColumn to oAddress.EndColumn
            lbList.addItem(getText(2) + columns.getByIndex(i).Name, i - oAddress.StartColumn)
        Next i
    End If
    btnSelectAllClick
End Sub

Sub ExtendSingleSelection(oSel)
Dim oCursor
    oCursor = oSel.getSpreadSheet.createCursorByRange(oSel)
    oCursor.collapseToCurrentRegion
    oController.select(oCursor)
End Sub

Function CheckInArray(checkRow As String, byRef UnionArray)
    CheckInArray = True
    On Error GoTo duplicateRow
    UnionArray.Add(1, checkRow) ' Fails if exists
    CheckInArray = False
duplicateRow:
End Function

Function GetCellRangeAddress(Sheet, StartColumn, StartRow, EndColumn, EndRow)
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    CellRangeAddress.Sheet = Sheet
    CellRangeAddress.StartColumn = StartColumn
    CellRangeAddress.EndColumn = EndColumn
    CellRangeAddress.StartRow = StartRow
    CellRangeAddress.EndRow = EndRow
    GetCellRangeAddress = CellRangeAddress
End Function

Function GetCellAddress(Sheet, Column, Row)
    Dim CellAddress As New com.sun.star.table.CellAddress
    CellAddress.Sheet = Sheet
    CellAddress.Column = Column
    CellAddress.Row = Row
    GetCellAddress = CellAddress
End Function

Sub MoveCells(Sheet, StartColumn, StartRow, EndColumn, EndRow, NewRow)
    oSheet.moveRange(GetCellAddress(Sheet, StartColumn, NewRow), _
                     GetCellRangeAddress(Sheet, StartColumn, StartRow, EndColumn, EndRow), _
                     com.sun.star.sheet.CellDeleteMode.UP)
End Sub

Sub RemoveCells(Sheet, StartColumn, StartRow, EndColumn, EndRow)
    oSheet.removeRange(GetCellRangeAddress(Sheet, StartColumn, StartRow, EndColumn, EndRow), _
                       com.sun.star.sheet.CellDeleteMode.UP)
End Sub

Function GetRowString(aDataArray(), Row As Long, selectedLst()) As String
    Dim RowStr As String, i As Long
    For i = lBound(selectedLst) to uBound(selectedLst)
        RowStr = RowStr & aDataArray(Row)(selectedLst(i)) & Chr(1)
    Next i
    GetRowString = RowStr
End Function

' Returns 1 if successful, 0 otherwise
Function doRemove() As Long

    Dim StartTime As Date
    StartTime = Now()

    Dim bResult As Boolean, bModified As Boolean
    bResult = False
    bModified = False

    Dim oUndoManager As Object

    oDoc.lockControllers()
    oDoc.addActionLock()
    oUndoManager = ThisComponent.getUndoManager()
    oUndoManager.enterUndoContext("Remove Duplicates")
    On Error GoTo cleanup

    Dim oSel As Object, oAddress As Object, aDataArray(), selectedLst(), bHasTitle As Boolean
    oSel = oDoc.getCurrentSelection()
    oAddress = oSel.getRangeAddress()
    aDataArray = oSel.getDataArray()

    selectedLst = oDialog1.GetControl("lbList").getSelectedItemsPos()
    bHasTitle = oDialog1.GetControl("cbTitle").getState() > 0

    Dim FirstRow As Long, LastRow As Long
    FirstRow = lBound(aDataArray)
    If bHasTitle Then
        FirstRow = FirstRow + 1
    End If
    LastRow = uBound(aDataArray)

    Dim ProgressBar As Object
    ProgressBar = oDialog1.GetControl("ProgressBar1")
    ResetProgress(ProgressBar, FirstRow, LastRow)

    Dim UnionArray As New Collection, NextDestinationRow As Long, Pos As Long, checkStr As String
    NextDestinationRow = FirstRow
    For Pos = FirstRow To LastRow
        checkStr = GetRowString(aDataArray, Pos, selectedLst)
        If (Not CheckInArray(checkStr, UnionArray)) Then
            If (Pos > NextDestinationRow) Then
                MoveCells(oAddress.Sheet, _
                          oAddress.StartColumn, _
                          oAddress.StartRow + Pos, _
                          oAddress.EndColumn, _
                          oAddress.StartRow + Pos, _
                          oAddress.StartRow + NextDestinationRow)
                bModified = True
            End If
            NextDestinationRow = NextDestinationRow + 1
        End If

        If ((Pos > FirstRow) And (Pos Mod 100 = 0)) Then
            StepProgress(ProgressBar, 100)
        End If
    Next Pos

    If (NextDestinationRow <= LastRow) Then
        RemoveCells(oAddress.Sheet, _
                    oAddress.StartColumn, _
                    oAddress.StartRow + NextDestinationRow, _
                    oAddress.EndColumn, _
                    oAddress.StartRow + LastRow)
        bModified = True
    End If

    ' Make sure to set it to 100% before reporting success: it could not account yet for the last <100 elements
    SetProgress(ProgressBar, LastRow)

    bResult = True

cleanup:
    oUndoManager.leaveUndoContext()
    oDoc.unLockControllers()
    oDoc.removeActionLock()

    Dim TotalCount As Long, DuplicatesCount As Long, Message As String
    Message = "Finished in " & Format((Now()-StartTime), "[s]") & " seconds." & Chr$(13)
    If (bResult) Then
        doRemove = 1
        TotalCount = LastRow - FirstRow + 1
        DuplicatesCount = LastRow - NextDestinationRow + 1
        If (DuplicatesCount > 0) Then
            Message = Message & "We found and deleted " & DuplicatesCount & " duplicated values." & Chr$(13) & _
                                "Now we have only " & TotalCount - DuplicatesCount & " unique vaues."
        Else
            Message = Message & "No duplicates found."
        End If
    Else
        doRemove = 0
        If (bModified) Then
            Message = Message & "An error occured! The changes will now be undone."
        Else
            Message = Message & "An error occured! No changes have been made."
        End If
    End If
    MsgBox Message
    If (Not bResult And bModified) Then oUndoManager.Undo()
End Function

Sub ResetProgress(ByRef ProgressBar, nMinVal As Long, nMaxVal As Long)
    With ProgressBar
        .setRange(nMinVal, nMaxVal)
        .setValue(nMinVal)
    End With
End Sub

Sub SetProgress(ByRef ProgressBar, nVal As Long)
    ProgressBar.setValue(nVal)
End Sub

Sub StepProgress(ByRef ProgressBar, nStep As Long)
    With ProgressBar
        .setValue(.getValue() + nStep)
    End With
End Sub

Sub btnCancelClick
    oDialog1.endExecute()
End Sub

Sub btnOKClick
    oDialog1.endDialog(DoRemove())
End Sub

Sub btnSelectAllClick
    Dim lbList, count As Long, i As Long
    lbList = oDialog1.GetControl("lbList")
    count = lbList.getItemCount()
    Dim SelectItems(count) As Integer
    For i = 0 To count - 1
        SelectItems(i) = i
    Next i
    lbList.selectItemsPos(SelectItems, true)
End Sub

Sub cbIncludeTitle
    Dim cbTitle
    cbTitle = oDialog1.GetControl("cbTitle")
    If cbTitle.getState() > 0 Then
        UpdateListBoxItems(True)
    Else
        UpdateListBoxItems(False)
    End If
End Sub