2
\$\begingroup\$

I'm working on some VB.Net code to generate data sheets for giving user rights on a per folder level. I came up with this code but it's very slow and I feel like it could be improved.

Public Shared Function CreateExcel(groupOwnerOf, sourceLocation, Optional groupOwner = Nothing)

    Dim ini As New IniFile(Directory.GetCurrentDirectory() & "\settings.ini")
    For Each prog As Process In Process.GetProcesses
        If prog.ProcessName = "EXCEL" Then
            prog.Kill()
        End If
    Next
    Threading.Thread.Sleep(1000)

    Dim xlApp As Excel.Application = New Excel.Application()

    If xlApp Is Nothing Then
        Logging.LogMain("Excel is not properly installed, recommending (re)isntallation")
        Return Nothing
    End If

    Dim xlWorkbook As Excel.Workbook
    xlWorkbook = xlApp.Workbooks.Add()

    Dim xlWorksheet = xlWorkbook.Sheets("sheet1")

    xlWorksheet.Name = "rights"
    xlWorksheet.Cells(1, 1) = "Paths"

    Dim Colums As Integer = 2
    Dim Sheetcounter = 2
    Dim locationCount As Integer = 3
    Dim Row As Integer = 3
    Dim ColumsGroups As Integer = 2
    Dim saveLocation = ini.GetString("settings AC", "excelMailSave") & "\" & ini.GetString("settings AC", "excelMailName") & ".xlsx"
    Dim groupCounter = 1

    If groupOwner Is Nothing Then
        groupCounter = countGroupsOfOwner(Autocomply.DisplayADsPath.Text)
    Else
        Console.WriteLine("other")
        groupCounter = countGroupsOfOwner(groupOwner)
    End If

    Console.WriteLine(groupCounter)

    If My.Computer.FileSystem.FileExists(saveLocation) Then

        My.Computer.FileSystem.DeleteFile(saveLocation)
        xlWorkbook.SaveAs(saveLocation)

    End If

    'generating excel data
    xlWorksheet.Rows(1).Orientation = 90
    xlWorksheet.Rows(1).RowHeight = 12 * xlWorksheet.StandardHeight
    xlWorksheet.Rows(1).HorizontalAlignment = Excel.Constants.xlCenter
    Do While groupOwnerOf.read()

        Autocomply.ProgressBar1.Maximum = groupCounter
        Dim membersFromGroup = GetMembersFromGroup(sourceLocation, groupOwnerOf("groupName"))
        Do While membersFromGroup.read()

            For i = ColumsGroups To groupCounter + 1 Step +1

                If membersFromGroup("memberUser") = xlWorksheet.Cells(2, i - 1).Value Then
                    Console.WriteLine("false")
                    GoTo exitMemberFor
                End If

            Next

            Console.WriteLine(membersFromGroup("memberUser"))

            xlWorksheet.Cells(2, Colums) = membersFromGroup("memberUser")
            Colums += 1
            groupCounter += 1

exitMemberFor:

        Loop

        Dim locationsOfGroup = DataFetch.locationsOfGroup(sourceLocation, groupOwnerOf("groupName"))

        Do While locationsOfGroup.read()

            For i = locationCount To Row + 1 Step +1

                If locationsOfGroup("location") = xlWorksheet.Cells(i - 1, 1).Value Then
                    Console.WriteLine("false")
                    GoTo exitFor
                End If

            Next

            xlWorksheet.Cells(Row, 1) = locationsOfGroup("location")
            Console.WriteLine(xlWorksheet.Cells(Row, 1).Value)
            Row += 1
            locationCount += 1
exitFor:

        Loop

        Autocomply.ProgressBar1.Value += 1

    Loop

    Autocomply.ProgressBar1.Value = 0
    Autocomply.ProgressBar1.Maximum = locationCount

    For Row = 3 To locationCount Step +1

        For i = 2 To groupCounter + 1 Step +1

            Dim rights = getRightsForPath(sourceLocation, xlWorksheet.Cells(1, i).Value, xlWorksheet.Cells(Row, 1).Value)
            Do While rights.Read()

                xlWorksheet.Cells(Row, i) = rights("rights")
                Console.WriteLine(rights("rights"))

            Loop

        Next

        Autocomply.ProgressBar1.Value += 1

    Next

    Autocomply.ProgressBar1.Value = 0
    xlWorkbook.SaveAs(saveLocation)
    Return xlApp

End Function
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.