Ask Your Question
0

[SOLVED] To Run Rapidly LibreOffice Macro

asked 2018-12-12 13:54:03 +0100

libremacrouser gravatar image

updated 2018-12-15 21:09:15 +0100

Hi!

I work in a LibreOffice Macro. My code work so slowly because of my data. I have around 10,000 data in a sheet and when I write for loop and if statement, my code work is so slowly(It works about 5hours). Is there any methods to run it rapidly?

I added my code that works slowly.

Sub Main

rA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
cA = CreateUnoStruct("com.sun.star.table.CellAddress")
aaA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
abA = CreateUnoStruct("com.sun.star.table.CellAddress")
acA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
adA = CreateUnoStruct("com.sun.star.table.CellAddress")
aeA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
afA = CreateUnoStruct("com.sun.star.table.CellAddress")


'Sheet 2 'Ürün Ağaçları(Sheet Name)
Doc = ThisComponent
Sheeti2 = Doc.Sheets.getByIndex(2)
Curs = Sheeti2.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows2 = Curs.Rows.Count


'Sheet 5    '3 Aylık Pivot(Sheet Name)
Doc = ThisComponent
Sheeti5 = Doc.Sheets.getByIndex(5)
Curs = Sheeti5.createCursor
Curs.gotoEndOfUsedArea(True)
NumRows5 = Curs.Rows.Count



dim document as object
    document = ThisComponent
    sheet1 = document.Sheets(5)'3 Aylık Pivot(Sheet Name)
    sheet2 = document.Sheets(2)'Ürün Ağaçları(Sheet Name)
    sheet3 = document.Sheets(6)'3 Aylık Tahmin(Sheet Name)
    sheet4 = document.Sheets(7)'Ağaçla Tahminler(Sheet Name)
    sheet5 = document.Sheets(3)'Takım Ağaçları (Sheet Name)


For iCountTree = 0 to NumRows5 '3 Aylık pivottaki satır sayısı 
For iCountTree1 = 1 to NumRows2'ürün ağacı satır sayısı 

if sheet1.getCellByPosition(0, iCountTree+5).String = sheet2.getCellByPosition(1, iCountTree1).String Then
        'Parça Kodunu copy start
            With aaA
            .Sheet       = 2
            .StartColumn = 2
            .StartRow    = iCountTree1
            .EndColumn   = 2
            .EndRow      = iCountTree1       
            End With
        'Parça Kodunu copy finish
        'Ürün Kodu copy start
            With acA
            .Sheet       = 2
            .StartColumn = 1
            .StartRow    = iCountTree1
            .EndColumn   = 1
            .EndRow      = iCountTree1       
            End With
        'Ürün Kodu copy finish
        'Parça Miktarını copy start
            With aeA
            .Sheet       = 2
            .StartColumn = 4
            .StartRow    = iCountTree1
            .EndColumn   = 4
            .EndRow      = iCountTree1       
            End With
        'Parça Miktarını copy finish

        for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim
        if sheet4.getCellByPosition(1, iCountForEmpty).String = empty then 
        'Parça Kodunu paste start
            With abA
            .Sheet       = 7
            .Column      = 2
            .Row         = iCountForEmpty
            End With
            ThisComponent.Sheets(7).CopyRange(abA, aaA)
        'Parça Kodunu paste finish
        'Ürün Kodu paste start
            With adA
            .Sheet       = 7
            .Column      = 1
            .Row         = iCountForEmpty
            End With
            ThisComponent.Sheets(7).CopyRange(adA, acA)
        'Ürün Kodu paste finish
        'Parça Miktarını paste start
            With afA
            .Sheet       = 7
            .Column      = 3
            .Row         = iCountForEmpty
            End With
            ThisComponent.Sheets(7).CopyRange(afA, aeA) 
        'Parça Miktarını paste finish

        'For to be empty to value of cells start
        'Parça Kodunu copy start
            With aaA
            .Sheet       = 2
            .StartColumn = 15
            .StartRow    = 1
            .EndColumn   = 15
            .EndRow      = 1     
            End With
        'Parça Kodunu copy finish
        'Ürün Kodu copy start
            With acA
            .Sheet       = 2
            .StartColumn = 15
            .StartRow    = 1
            .EndColumn   = 15
            .EndRow      = 1     
            End With
        'Ürün Kodu copy finish
        'Parça Miktarını copy start
            With aeA
            .Sheet       = 2
            .StartColumn = 15
            .StartRow    = 1
            .EndColumn   = 15
            .EndRow      = 1     
            End With
        'Parça Miktarını copy finish
        'For to be empty to value of cells finish

        Exit For     
        else
        end if
        next iCountForEmpty

    Else

    end if

    next iCountTree1 ...
(more)
edit retag flag offensive close merge delete

Comments

You do not need to use ThisComponent.Sheets(7) when copying. You have already defined it as "sheet4", use this variable. Do you copy only data? Or can there be formulas in the cells?

JohnSUN gravatar imageJohnSUN ( 2018-12-12 15:03:55 +0100 )edit

Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?

libremacrouser gravatar imagelibremacrouser ( 2018-12-12 15:20:40 +0100 )edit

@libremacrouser: Can you explain in general words (not arbitrary and probably badly chosen names) what this code is supposed to achieve?
Quoting @libremacrouser: "Yes @JohnSUN, I copy only data. What can I do to copy paste rapidly?"
You are obviously testing conditions. For simply copying ranges there is the very fast copyRangemethod that every sheet can call. See https://api.libreoffice.org/docs/idl/...

Lupp gravatar imageLupp ( 2018-12-12 18:28:15 +0100 )edit

@Lupp As far as I understood the above code, this is the usual filtering of data from the table on the third sheet for the coincidence of the values ​​in the column B with the values ​​of column A on the sixth sheet. The result of filtering - the second, third and fifth cells are recorded in the first row on the eighth page, where there is no data in column B.

JohnSUN gravatar imageJohnSUN ( 2018-12-12 19:37:23 +0100 )edit

@JohnSUN I editted my question. I added a picture on it. I try to your code in a simple example and it worked great. After that I tried it my real LibreOffice and I got this error which in the picture. I again try to understand what is your code. It is really great and I could see it how is work in my simple example. If I could complete my code, it would be really great. Can you tell me how can I solve my problem?

libremacrouser gravatar imagelibremacrouser ( 2018-12-13 07:09:11 +0100 )edit

@JohnSUN and I couldn't understand what is their mean --> i7 = (UBound(oData2)+1) * (UBound(oData5)+1) oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)

libremacrouser gravatar imagelibremacrouser ( 2018-12-13 07:21:47 +0100 )edit

It's almost the same as what you wrote here: for iCountForEmpty = 1 to NumRows2*2'Bu Sayıyı Ağaçların listesinden fazla olacak şekilde seçtim You took just double the number of rows. I thought about the case when all the codes in 'Ürün Ağaçları'.B:B and in '3 Aylık Pivot'.A:A would be the same and asked for a size that was enough for all matches, each with each.

When you substituted real data, it turned out that "each with each one" is more than a million values, but sheet of Calc has only 1048576 lines.

So, just replace

i7 = (UBound(oData2)+1) * (UBound(oData5)+1)

with

i7 = 1048576

JohnSUN gravatar imageJohnSUN ( 2018-12-13 07:50:01 +0100 )edit

Thanks for your reply @JohnSUN. I got it what is mean of i7 = (UBound(oData2)+1) * (UBound(oData5)+1). Sorry I think I ask you much questions. I try to understand its basic to improve my question and myself. Can you tell me about the error that I editted its picture in my question? In addition I couldn't understand --> oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7) I am not sure about (1.1.3.i7). And I don't know why it is work in an my simple example and doesn't work in my real file that I write it in a comment it is up two comments.

libremacrouser gravatar imagelibremacrouser ( 2018-12-13 08:12:56 +0100 )edit

Error - the number i7 turned out to be very large, we demanded from the macro to select on the sheet 'Ağaçla Tahminler' a range of cells larger than there is.

(1,1,3,i7) - mean "columns 1,2 and 3 (B, C and D) from second row (you skip header in your macros) to last needed row (to last row on sheet?)"

JohnSUN gravatar imageJohnSUN ( 2018-12-13 08:31:20 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-12 16:06:51 +0100

JohnSUN gravatar image

updated 2018-12-12 16:07:49 +0100

I hope that this variant do same and work something rapidly:

Sub JohnSUNsCopy
Dim oSheets As Variant  ' All sheets of ThisComponent '
Dim oSheet As Variant   ' Any single sheet '
Dim oData2 As Variant   ' All data from Ürün Ağaçları Sheet '
Dim oData5 As Variant   ' All data from 3 Aylık Pivot Sheet '
Dim oData7 As Variant   ' Data to paste to Ağaçla Tahminler Sheet '
Dim oCursor As Variant  ' You know what it is, you used it in your code '
Dim i2 As Long, i5 As Long  ' Variables for cycles  '
Dim i7 As Long          ' Count trees '
Dim nEndRow7 As Long    ' The number of the last non-empty row on sheet 7 column 1 '
Dim oRange7 As Variant
    oSheets = ThisComponent.getSheets()
    oSheet = oSheets.getByIndex(2)
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
    oData2 = oCursor.getDataArray() ' All cells of Ürün Ağaçları Sheet in the array '

    oSheet = oSheets.getByIndex(5)
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
    oData5 = oCursor.getDataArray() ' All cells of 3 Aylık Pivot Sheet in the array '

    oSheet = oSheets.getByIndex(7)
    i7 = (UBound(oData2)+1) * (UBound(oData5)+1)
    oRange7 = oSheet.getCellRangeByPosition(1, 1, 3, i7)
    oData7 = oRange7.getDataArray()
    nEndRow7 = LBound(oData7)

    For i2 = LBound(oData2) To UBound(oData2)
        For i5 = LBound(oData5) To UBound(oData5)
            If oData5(i5)(0)  = oData2(i2)(1) Then
                For i7 = nEndRow7 To UBound(oData7)
                    If (IsEmpty(oData7(i7)(0)) Or Trim(oData7(i7)(0))="") Then 
                        oData7(i7)(0) = oData2(i2)(1)
                        oData7(i7)(1) = oData2(i2)(2)
                        oData7(i7)(2) = oData2(i2)(4)
                        nEndRow7 = i7+1
                        Exit For
                    EndIf 
                Next i7
            EndIf 
        Next i5
    Next i2
    oRange7.setDataArray(oData7)
End Sub
edit flag offensive delete link more

Comments

Thanks @JohnSUN. Thanks to you I solved my problem. Anymore my macro run great and it works for only 15minutes. It is really great for me because before your solution, it was almost 5 hours.

libremacrouser gravatar imagelibremacrouser ( 2018-12-15 21:08:30 +0100 )edit

Glad I could help. The algorithm could be accelerated even more (perhaps even several times faster). But to do this, you need to optimize the search for matching values ​​that should be copied, and determine in advance all the blank lines on which to write the result. This would make the code so complex that I could not explain all its details.

JohnSUN gravatar imageJohnSUN ( 2018-12-15 21:22:10 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2018-12-12 13:54:03 +0100

Seen: 73 times

Last updated: Dec 15 '18