Skip to content

Instantly share code, notes, and snippets.

@chuckwagoncomputing
Last active February 8, 2018 19:24
Show Gist options
  • Save chuckwagoncomputing/62fb46a79e5005afc5cb307a10540d9d to your computer and use it in GitHub Desktop.
Save chuckwagoncomputing/62fb46a79e5005afc5cb307a10540d9d to your computer and use it in GitHub Desktop.
LibreOffice macro to parse vcard data
importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.table.XCell);
importClass(Packages.com.sun.star.text.XText);
function parseAddress(addr) {
var raddr = addr.split(" ").reverse();
var ret = {zip: raddr[0], state: raddr[1], city: raddr[2], street: ""};
for (var i = 3; i < raddr.length; i++) {
ret.street = raddr[i] + " " + ret.street;
}
return ret;
}
function hex2a(hexx) {
var hex = hexx.toString();
var str = '';
for (var i = 0; i < hex.length; i += 2) {
str += String.fromCharCode(parseInt(hex.substr(i, 2), 16));
}
return str;
}
function vcardParse(input) {
var Re1 = /^(version|fn|title|org|note):(.+)$/i;
var Re2 = /^([^:;]+);([^:]+):(.+)$/;
var ReKey = /item\d{1,2}\./;
var fields = {};
var inputSplit = input.split(/\r\n|\r|\n|Ú/);
for (var is = 0; is < inputSplit.length; is++) {
var line = inputSplit[is];
var results, key;
if (Re1.test(line)) {
results = line.match(Re1);
key = results[1].toLowerCase();
fields[key] = results[2];
} else if (Re2.test(line)) {
results = line.match(Re2);
key = results[1].replace(ReKey, '').toLowerCase();
var meta = {};
var resultsSplit = results[2].split(';');
var resultsMatch = [];
for (var i = 0; i < resultsSplit.length; i++) {
var p = resultsSplit[i];
var match = p.match(/([a-z]+)=(.*)/i);
if (match) {
meta[match[1]] = match[2];
} else {
meta["TYPE" + (i === 0 ? "" : i)] = p;
}
}
if (!fields[key]) fields[key] = [];
fields[key].push({
meta: meta,
value: results[3].split(';')
})
}
}
return fields;
}
//get the document object from the scripting context
var oDoc = XSCRIPTCONTEXT.getDocument();
//get the XSpreadsheetDocument interface from the document
var xSDoc = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
//get the XIndexAccess interface used to access each sheet
var xSheetsIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSDoc.getSheets());
var cs = xSheetsIndexAccess.getByIndex(2);
var cps = xSheetsIndexAccess.getByIndex(1);
//get the XCellRange interface used to access a cell
var csCells = UnoRuntime.queryInterface(XCellRange,cs);
var cpsCells = UnoRuntime.queryInterface(XCellRange, cps);
var upperBound = 500
for (var i = 1; i < upperBound; i++) {
var rc = csCells.getCellByPosition(1, i);
var rcText = UnoRuntime.queryInterface(XText, rc);
var rcString = rcText.getString();
if (rcString.length() == 0) {
break;
}
var p = i;
var vcard = vcardParse(hex2a(rcString.slice(2, rcString.length())));
var pcNote = cpsCells.getCellByPosition(0, p);
var pcNoteText = UnoRuntime.queryInterface(XText, pcNote);
pcNoteText.setString(vcard["note"])
var pcName = cpsCells.getCellByPosition(1, p);
var pcNameText = UnoRuntime.queryInterface(XText, pcName);
pcNameText.setString(vcard["fn"])
if (vcard["adr"]) {
var street = vcard["adr"][0]["value"][2] || ""
var city = vcard["adr"][0]["value"][3] || ""
var state = vcard["adr"][0]["value"][4] || ""
var zip = vcard["adr"][0]["value"][5] || ""
if (city.length == 0 && state.length == 0 && zip.length == 0 && street.length > 0) {
var paddr = parseAddress(street);
street = paddr.street;
city = paddr.city;
state = paddr.state;
zip = paddr.zip;
}
var pcAddress = cpsCells.getCellByPosition(2, p);
var pcAddressText = UnoRuntime.queryInterface(XText, pcAddress);
pcAddressText.setString(street);
var pcCity = cpsCells.getCellByPosition(3, p);
var pcCityText = UnoRuntime.queryInterface(XText, pcCity);
pcCityText.setString(city);
var pcState = cpsCells.getCellByPosition(4, p);
var pcStateText = UnoRuntime.queryInterface(XText, pcState);
pcStateText.setString(state);
var pcZip = cpsCells.getCellByPosition(5, p);
var pcZipText = UnoRuntime.queryInterface(XText, pcZip);
pcZipText.setString(zip);
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment