useage libreoffice calc [debian,windows,ubuntu]

(this tutorial might be useless for others expect me)

based on:
http://www.ooowiki.de/RundenK%28c3bc%29rzen.html
http://www.ooowiki.de/SummeWenn.html
https://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative
http://www.debugpoint.com/2014/09/writing-a-macro-in-libreoffice-calc-getting-started/

go to end of a file:
ctrl + End

save the csv file as an ods document:
download your orders csv file
rightclick on the .csv file and import it with libreoffice calc
choose in the popup window ok
save it as an ods document.

get unique item names:
click “D”-column so it is marked light blue
Data > More filters > Standart filter >
Condition “”
options > [x] no duplicates
“ok”
ctrl + C to copy them
open a new txt file and
then save them in a txt file
close the odt document without saving

ctrl +z to undo the filter
then click on U4 and paste it there with ctrl + v
save it

run the macro:
open the ods document again
“tools” -> “markros” -> “organize macros” -> “libreoffice basic”
bottom right click on “new”
top left name it to “add_freds_code”
copy and paste:

REM  *****  BASIC  *****
sub add_freds_code
   
    dim document   as object
    dim dispatcher as object
 
    oSheet = thisComponent.sheets(0) '1.Blatt
    ocursor = oSheet.createCursor()
    ocursor.gotoStart() 
    ocursor.gotoEndofUsedArea(false)            'letzte Zelle des Bereiches
    letzteSpalte = ocursor.getRangeAddress.endColumn   'index letzte Spalte des Bereichs
    letzteZeile = ocursor.getRangeAddress.endRow   'index letzte Zeile des Bereichs
   
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
    dim args1(0) as new com.sun.star.beans.PropertyValue
    dim args2(0) as new com.sun.star.beans.PropertyValue
  
    args1(0).Name = "ToPoint"
    args1(0).Value = "$N$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Name = "StringName"
    args2(0).Value = "Item [name]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$N$2"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "all"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$N$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
 '   args2(0).Value = "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(U4; "("; "\("); ")"; "\)"); "["; "\["); "]"; "\]"); "+"; "\+")"
      args2(0).Value = "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(U4; ""(""; ""\(""); "")""; ""\)""); ""[""; ""\[""); ""]""; ""\]""); ""+""; ""\+"")"
      
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
      
    args1(0).Value = "$O$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Total [L]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$O$2"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=SUM(L2:L"+letzteZeile+")"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$O$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=SUMIF($D$2:$D$"+letzteZeile+";N4;$L$2:$L$"+letzteZeile+")"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$P$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Total [€]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$P$2"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=ROUND(O2/T2;2)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$P$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=ROUND(O4/$T$2;2)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$Q$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Total [Items]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$Q$2"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=SUM(Q4:Q100)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$Q$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=COUNTIFS($D$2:$D$"+letzteZeile+";N4)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$R$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Total [Items %]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$R$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=ROUND(100*Q4/$Q$2;1)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$S$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Total [€ %]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$S$4"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "=ROUND(100*P4/$P$2;1)"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    args1(0).Value = "$T$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Exchangerate"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
    args1(0).Value = "$T$2"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "360"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
    
    args1(0).Value = "$U$1"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    args2(0).Value = "Item [orgname]"
    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
  
'    args1(0).Value = "$U$4"
'    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
'    args2(0).Value = ".*your_search_string_for_an_item.*"
'    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

'    Cell = oSheet.getCellByPosition(20,3)
'    Cell.CellBackColor = RGB(0, 255, 0)

    Cell = oSheet.getCellByPosition(19,1)
    Cell.CellBackColor = RGB(0, 255, 0)

   msgbox "Completed!"
end sub

to run the macro press F5 (or the button “basic run the marcro”)
Use odf format

after running the macro:
change your exchangerate in T2

add your keywords in the row of U starting at U4 so you have an unique keyword for every item.
simply copy the unique item names from the txt file (mark them all ctrl +a, then copy them ctrl + c)
click on U4 then paste them ( ctrl +V )

mark N4 > S4 and enlarge them on the bottom right down square until you reach the last keyword on Ux
mark N4 > U4 to Nx > Ux
Data > sort > column P > descending > ok

mark the area for diagram (U first then P)
click on U4 hold down shift and click on Ux
hold down ctrl and click on P4 hold down shift and click on Px
now click on the diagram button and follow the instructions

if you are done:
zoom in the diagram (Ctrl + Mousewheel-Up)
click a bar in the diagram until it shows green dots left right and middle
rightclick a green dot and choose “insert data lettering”
copy diagram (click it and press Ctrl + C)
open a new libreoffice calc document (Ctrl + N) and paste it inside (Ctrl + V)
top right click on “file” -> “preview in webbrowser”
in webbrowser click “file” -> “printpreview” -> “scale 60%”

if you are interested in libreoffice extensions or templates:
https://extensions.libreoffice.org/extensions
https://extensions.libreoffice.org/templates
http://templates.openoffice.org/de

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s