Calc Macro to Fix and Transfer to new sheet

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
SeekTruth
Posts: 4
Joined: Mon Nov 17, 2014 9:14 pm

Calc Macro to Fix and Transfer to new sheet

Post by SeekTruth »

Python 2.7.4
Ubuntu 14.04 LTS

Hello. I have been meddling with Python for a little bit, fixing what I can in other scripts, but i'm a complete beginner still. I've been looking through instructions on this and have been :crazy: ... I was hoping someone could help make a macro/python script out of the following instructions... I would then execute it on a csv or ods file which I already have to tools to do.

- Delete Header Row

- Insert 3 blank Columns Between B and C

- Columns A and B - Change Case "Capitalize Every Word"

- Column C -

Code: Select all

=TRIM(A1)
Populate down until end of column A content

- Column D -

Code: Select all

=TRIM(B1)
Populate down until end of column B content

- Column E -

Code: Select all

=C1&" "&D1
Populate down until end of column C content

- Insert 1 Blank Column Between F and G

- Column F - Change Case "lower case"
- Column F - Find and Replace @gmail.con with @gmail.com & @yahoo.con with @yahoo.com

- Column G -

Code: Select all

=TRIM(F1)
Populate down until end of column F content

- Copy Contents of Column E

- Create New Sheet
- Column A - Paste Special "Link" Option. (Paste of First Sheet Column E)
- Copy Contents of Column G on First Sheet
- Column B - Paste Special "Link" Option. (Paste of First Sheet Column G)

Save this active sheet to csv with new name.

I know it's a lot, but any assistance would be greatly appreciated.
Thank you!
SeekTruth
Posts: 4
Joined: Mon Nov 17, 2014 9:14 pm

Re: Calc Macro to Fix and Transfer to new sheet

Post by SeekTruth »

I know it's ugly. I used the macro generator, and did everything needed to fix the code afterwards.... So it works, but has the following issues:

1 - I'm forced to define a range, but it will change every month. I would like to substitute row count, or some kind of better auto fill to make the range dynamic based on content.
2 - Everything opporates in the GUI, would love to make it non-GUI.
3 - Save automatically and just append _new to the end of the file.

For anyone looking to fix their recorded macro... here's my ugly fix :D

Code: Select all

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

Sub MainFixed

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ---------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args2())

rem ----------------------------------------------------------------------

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1:$A$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToTitleCase", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$B$1:$B$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToTitleCase", "", 0, args6())


rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "StringName"
args8(0).Value = "=TRIM(A1)"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args8())

dim args8a(0) as new com.sun.star.beans.PropertyValue
args8a(0).Name = "ToPoint"
args8a(0).Value = "$C$1:$C$35032"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8a())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())

rem --------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$D$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem --------------------------------------------------------------------

dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "StringName"
args10(0).Value = "=TRIM(B1)"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args10())

rem ----------------------------------------------------------------------

dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$D$1:$D$35032"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())

rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "ToPoint"
args22(0).Value = "$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args22())

rem ----------------------------------------------------------------------
dim args23(0) as new com.sun.star.beans.PropertyValue
args23(0).Name = "StringName"
args23(0).Value = "=C1&"+CHR$(34)+" "+CHR$(34)+"&D1"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args23())

rem ----------------------------------------------------------------------
dim args25(0) as new com.sun.star.beans.PropertyValue
args25(0).Name = "ToPoint"
args25(0).Value = "$E$1:$E$35032"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args25())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())

rem ----------------------------------------------------------------------
dim args30(0) as new com.sun.star.beans.PropertyValue
args30(0).Name = "ToPoint"
args30(0).Value = "$G$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args30())
dispatcher.executeDispatch(document, ".uno:InsertColumns", "", 0, args30())

rem ----------------------------------------------------------------------
dim args31(0) as new com.sun.star.beans.PropertyValue
args31(0).Name = "ToPoint"
args31(0).Value = "$F$1:$F$35032"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args31())
dispatcher.executeDispatch(document, ".uno:ChangeCaseToLower", "", 0, Array())

rem ----------------------------------------------------------------------
dim args33(17) as new com.sun.star.beans.PropertyValue
args33(0).Name = "SearchItem.StyleFamily"
args33(0).Value = 2
args33(1).Name = "SearchItem.CellType"
args33(1).Value = 0
args33(2).Name = "SearchItem.RowDirection"
args33(2).Value = true
args33(3).Name = "SearchItem.AllTables"
args33(3).Value = false
args33(4).Name = "SearchItem.Backward"
args33(4).Value = false
args33(5).Name = "SearchItem.Pattern"
args33(5).Value = false
args33(6).Name = "SearchItem.Content"
args33(6).Value = false
args33(7).Name = "SearchItem.AsianOptions"
args33(7).Value = false
args33(8).Name = "SearchItem.AlgorithmType"
args33(8).Value = 0
args33(9).Name = "SearchItem.SearchFlags"
args33(9).Value = 71680
args33(10).Name = "SearchItem.SearchString"
args33(10).Value = "@gmail.con"
args33(11).Name = "SearchItem.ReplaceString"
args33(11).Value = "@gmail.com"
args33(12).Name = "SearchItem.Locale"
args33(12).Value = 255
args33(13).Name = "SearchItem.ChangedChars"
args33(13).Value = 2
args33(14).Name = "SearchItem.DeletedChars"
args33(14).Value = 2
args33(15).Name = "SearchItem.InsertedChars"
args33(15).Value = 2
args33(16).Name = "SearchItem.TransliterateFlags"
args33(16).Value = 1280
args33(17).Name = "SearchItem.Command"
args33(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args33())

rem ----------------------------------------------------------------------
dim args35(17) as new com.sun.star.beans.PropertyValue
args35(0).Name = "SearchItem.StyleFamily"
args35(0).Value = 2
args35(1).Name = "SearchItem.CellType"
args35(1).Value = 0
args35(2).Name = "SearchItem.RowDirection"
args35(2).Value = true
args35(3).Name = "SearchItem.AllTables"
args35(3).Value = false
args35(4).Name = "SearchItem.Backward"
args35(4).Value = false
args35(5).Name = "SearchItem.Pattern"
args35(5).Value = false
args35(6).Name = "SearchItem.Content"
args35(6).Value = false
args35(7).Name = "SearchItem.AsianOptions"
args35(7).Value = false
args35(8).Name = "SearchItem.AlgorithmType"
args35(8).Value = 0
args35(9).Name = "SearchItem.SearchFlags"
args35(9).Value = 65536
args35(10).Name = "SearchItem.SearchString"
args35(10).Value = "yahoo.con"
args35(11).Name = "SearchItem.ReplaceString"
args35(11).Value = "yahoo.com"
args35(12).Name = "SearchItem.Locale"
args35(12).Value = 255
args35(13).Name = "SearchItem.ChangedChars"
args35(13).Value = 2
args35(14).Name = "SearchItem.DeletedChars"
args35(14).Value = 2
args35(15).Name = "SearchItem.InsertedChars"
args35(15).Value = 2
args35(16).Name = "SearchItem.TransliterateFlags"
args35(16).Value = 1280
args35(17).Name = "SearchItem.Command"
args35(17).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args35())

rem ----------------------------------------------------------------------
dim args36(0) as new com.sun.star.beans.PropertyValue
args36(0).Name = "ToPoint"
args36(0).Value = "$G$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args36())

rem ----------------------------------------------------------------------
dim args37(0) as new com.sun.star.beans.PropertyValue
args37(0).Name = "StringName"
args37(0).Value = "=TRIM(F1)"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args37())

rem ----------------------------------------------------------------------
dim args38(0) as new com.sun.star.beans.PropertyValue
args38(0).Name = "ToPoint"
args38(0).Value = "$G$1:$G$35032"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args38())
dispatcher.executeDispatch(document, ".uno:FillDown", "", 0, Array())

rem ----------------------------------------------------------------------

dim args39(0) as new com.sun.star.beans.PropertyValue
args39(0).Name = "ToPoint"
args39(0).Value = "$E$1:$E$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args39())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------

dim args47a(0) as new com.sun.star.beans.PropertyValue
args47a(0).Name = "Nr"
args47a(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args47a())

rem ----------------------------------------------------------------------

rem dim args44(1) as new com.sun.star.beans.PropertyValue
rem args44(0).Name = "Name"
rem args44(0).Value = "Sheet2"
rem args44(1).Name = "Index"
rem args44(1).Value = 2

rem dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args44())
rem dispatcher.executeDispatch(document, ".uno:FocusCellAddress", "", 0, args44())

rem ----------------------------------------------------------------------

dim args45(0) as new com.sun.star.beans.PropertyValue
args45(0).Name = "ToPoint"
args45(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args45())

rem ----------------------------------------------------------------------

dim args46(5) as new com.sun.star.beans.PropertyValue
args46(0).Name = "Flags"
args46(0).Value = "A"
args46(1).Name = "FormulaCommand"
args46(1).Value = 0
args46(2).Name = "SkipEmptyCells"
args46(2).Value = false
args46(3).Name = "Transpose"
args46(3).Value = false
args46(4).Name = "AsLink"
args46(4).Value = true
args46(5).Name = "MoveMode"
args46(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args46())

rem ----------------------------------------------------------------------
dim args47(0) as new com.sun.star.beans.PropertyValue
args47(0).Name = "Nr"
args47(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args47())

rem ----------------------------------------------------------------------

dim args48(0) as new com.sun.star.beans.PropertyValue
args48(0).Name = "ToPoint"
args48(0).Value = "$G$1:$G$35032"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args48())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args49(0) as new com.sun.star.beans.PropertyValue
args49(0).Name = "Nr"
args49(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args49())

rem ----------------------------------------------------------------------

dim args49a(0) as new com.sun.star.beans.PropertyValue
args49a(0).Name = "ToPoint"
args49a(0).Value = "$B$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args49a())

rem ----------------------------------------------------------------------

dim args50(5) as new com.sun.star.beans.PropertyValue
args50(0).Name = "Flags"
args50(0).Value = "A"
args50(1).Name = "FormulaCommand"
args50(1).Value = 0
args50(2).Name = "SkipEmptyCells"
args50(2).Value = false
args50(3).Name = "Transpose"
args50(3).Value = false
args50(4).Name = "AsLink"
args50(4).Value = true
args50(5).Name = "MoveMode"
args50(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args50())

rem ----------------------------------------------------------------------
dim args51(2) as new com.sun.star.beans.PropertyValue
rem args51(0).Name = "URL"
rem args51(0).Value = "file:///Users/username/file.csv"
rem args51(1).Name = "FilterName"
rem args51(1).Value = "Text - txt - csv (StarCalc)"
rem args51(2).Name = "FilterOptions"
rem args51(2).Value = "9/44,34,76,1,,0,false,false"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args51())

End Sub
LibreOffice 4.2.8.2
Ubuntu 14.0.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc Macro to Fix and Transfer to new sheet

Post by Villeroy »

Record a double-click on the cell handle. This will fill down until the end of the left neigbouring column unless the neighbouring column has gaps.
No, this is one of the actions where the recorder fails.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Calc Macro to Fix and Transfer to new sheet

Post by hubert lambert »

Hello,

Here's a example. Just save the script in a .py file in your ~/.config/libreoffice/4/user/Scripts/python folder, then run it from within the spreadsheet to be processed.
The csv file is created in the current folder.
[I assume you're using Libreoffice 5.x]

Code: Select all

import csv
from unohelper import fileUrlToSystemPath

def process_sheet(event=None):
    doc = XSCRIPTCONTEXT.getDocument()

    # Get data from sheet 1
    sheets = doc.Sheets
    sheet = sheets[0]
    cursor = sheet.createCursor()
    cursor.gotoStartOfUsedArea(False)
    cursor.gotoEndOfUsedArea(True)
    data = cursor.getDataArray()

    # Delete header row
    data = data[1:]
    nb_rows = len(data)

    # Transpose data (easier to work on rows then on columns)
    data = list(zip(*data))

    # Trim formula column C
    data.insert(2, tuple("=TRIM(A{})".format(n) for n in range(1, nb_rows+1)))

    # Trim formula column D
    data.insert(3, tuple("=TRIM(B{})".format(n) for n in range(1, nb_rows+1)))

    # Formula column E
    data.insert(4, tuple('=C{0}&" "&D{0}'.format(n) for n in range(1, nb_rows+1)))

    # Column F - Change case "lower case"
    data[5] = tuple(d.lower() for d in data[5])

    # Column F - Find and replace @gmail.con with @gmail.com
    #                           & @yahoo.con with @yahoo.com
    data[5] = tuple(d.replace('@gmail.con', '@gmail.com') for d in data[5])
    data[5] = tuple(d.replace('@yahoo.con', '@yahoo.com') for d in data[5])

    # Trim formula column G
    data.append(tuple("=TRIM(F{})".format(n) for n in range(1, nb_rows+1)))

    # Copy data for later csv process
    csv_data = [data[4], data[6]]

    # Insert modified data
    data = tuple(zip(*data))
    cursor.clearContents(23)
    dest = sheet.getCellRangeByPosition(0,0,len(data[0])-1,len(data)-1)
    dest.setFormulaArray(data)
    dest.Columns.OptimalWidth = True

    # Create new sheet
    newsheetname = "Sheet2"
    if not sheets.hasByName(newsheetname):
        sheets.insertNewByName(newsheetname, sheets.Count)
    sheet2 = sheets[-1]
    sheet2.clearContents(23)

    # Column A - Paste special "Link" option. (Paste of first sheet Column E)
    # Column B - Paste special "Link" option. (Paste of first sheet column G)
    csv_data = list(zip(*csv_data))
    for n in range(len(csv_data)):
        csv_data[n] = ("={}.E{}".format(sheet.Name, n+1),
                       "={}.G{}".format(sheet.Name, n+1))
    dest = sheet2.getCellRangeByPosition(0,0,len(csv_data[0])-1,len(csv_data)-1)
    dest.setFormulaArray(csv_data)
    dest.Columns.OptimalWidth = True

    # Create csv file
    url = fileUrlToSystemPath(doc.URL)
    url = url.replace('.ods', '.csv')
    with open(url, 'w') as csvfile:
        writer = csv.writer(csvfile, delimiter=',',
                            quotechar='"', quoting=csv.QUOTE_MINIMAL)
        writer.writerows(dest.getDataArray())
I'm pretty sure this code could be simplified to achieve your goal, but I leave this up to you... ;)
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc Macro to Fix and Transfer to new sheet

Post by Villeroy »

If the input file is csv as well, it would be easier to do in Python language without any office suite.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply