Group_Concat for the spreadsheet
From Apache OpenOffice Wiki
This script was published by Villeroy at the Code Snippets Forum.
A most simple draft for an SQL GROUP_CONCAT function as found in many database engines (e.g. HSQLDB 2.x and MySQL). Select a range of cells so the main category is found in the first column and the subcategories to be concatenated are found in the last column of the selection. Run the macro. The 2-column output will be dumped 2 rows below the selection.
Original code
import uno def getGroupConcatDataArray(a): '''Get some data array, return a 2-column data array.''' sdelim = ',' d = dict() for r in a: lst = d.get(r[0],list()) s = r[-1] if not s in lst: lst.append(unicode(s)) d[r[0]] = lst for (k,v) in d.iteritems(): v.sort() d[k] = sdelim.join(v) return tuple(d.items()) def makeGroupConcat_draft(*args): '''Read from first and last column of selected range. Store first field as dictionary keys and concatenate the second. Dump the resulting dictionary 2 rows below current selection''' doc = XSCRIPTCONTEXT.getDocument() view = doc.getCurrentController() sel = view.getSelection() sh = sel.getSpreadsheet() a = sel.getDataArray() tgt = getGroupConcatDataArray(a) addr = sel.getRangeAddress() sc = addr.StartColumn ec = sc +1 sr = addr.EndRow +2 er = sr + len(tgt) -1 rg = sh.getCellRangeByPosition(sc, sr, ec, er) rg.setDataArray(tgt) view.select(rg) g_exportedScripts = makeGroupConcat_draft,