#!/usr/bin/python
#
# Save .xls file into .csv while saving sheets into different files
#
# Taken from http://www.logilab.org/blogentry/6130
# Modified by Arie Skliarouk <skliarie@gmail.com>
#
# For single-sheet files, use simpler conversion:
# localc -convert-to csv a.xls
#
# Notes:
#  * upon completion the script kills all soffice.bin oosplash.bin processes
#    (if started from script). This might kill other soffice sessions on
#    the machine

import logging
import sys
import os.path as osp
import os
import time

import uno

loffice_started=None

def convert_spreadsheet(filename):
    """load a spreadsheet document, and convert all sheets to
    individual CSV files"""
    logging.info('processing %s', filename)
    url = "file://%s" % osp.abspath(filename)
    export_mask = make_export_mask(url)
    # initialize Uno, get a Desktop object
    service_manager, desktop = _uno_init()
    try:
        # load the Document
        document = desktop.loadComponentFromURL(url, "_blank", 0, ())
        controller = document.getCurrentController()
        sheets = document.getSheets()
        logging.info('found %d sheets', sheets.getCount())

        # iterate on all the spreadsheets in the document
        enumeration = sheets.createEnumeration()
        while enumeration.hasMoreElements():
            sheet = enumeration.nextElement()
            name = sheet.getName()
            logging.info('current sheet name is %s', name)
            controller.setActiveSheet(sheet)
            outfilename = export_mask % name.replace(' ', '_')
            document.storeToURL(outfilename,
                                make_property_array(FilterName="Text - txt - csv (StarCalc)",
                                                    FilterOptions="59,34,76,1" ))
    finally:
        document.close(True)

def make_export_mask(url):
    """convert the url of the input document to a mask for the written
    CSV file, with a substitution for the sheet name

    >>> make_export_mask('file:///home/foobar/somedoc.xls')
    'file:///home/foobar/somedoc$%s.csv'
    """

    components = url.split('.')
    components[-2] += '$%s'
    components[-1] = 'csv'
    return '.'.join(components)

def make_property_array(**kwargs):
    """convert the keyword arguments to a tuple of PropertyValue uno
    structures"""
    array = []
    for name, value in kwargs.iteritems():
        prop = uno.createUnoStruct("com.sun.star.beans.PropertyValue")
        prop.Name = name
        prop.Value = value
        array.append(prop)
    return tuple(array)

def connect_to_libre_office():
    # Get the uno component context from the PyUNO runtime
    local_context = uno.getComponentContext()
    # Get the local Service Manager
    local_service_manager = local_context.ServiceManager
    # Create the UnoUrlResolver on the Python side.
    local_resolver = local_service_manager.createInstanceWithContext(
    "com.sun.star.bridge.UnoUrlResolver", local_context)
    # Connect to the running OpenOffice.org and get its context.
    # XXX make host/port configurable
    context = local_resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
    # Get the ServiceManager object
    service_manager = context.ServiceManager
    # Create the Desktop instance
    desktop = service_manager.createInstance("com.sun.star.frame.Desktop")
    return service_manager, desktop
    
def _uno_init(_try_start=True):
    """init python-uno bridge infrastructure"""    
    global loffice_started
    try:
        service_manager,desktop=connect_to_libre_office()
        return service_manager, desktop
    except Exception, exc:
        if exc.__class__.__name__.endswith('NoConnectException') and _try_start:
            logging.info('Trying to start UNO server')
            loffice_started=True
            child_pid = os.fork()
            if child_pid == 0:
                logging.info("Child Process: PID# [%s]" , os.getpid())
                status = os.system('localc --invisible --headless --nofirststartwizard --accept="socket,host=localhost,port=2002;urp;"')
                # The localc is get killed using killall
                sys.exit(0)
                
            time.sleep(10)
            logging.info('localc started, parent_id is [%d]', os.getpid())

    try:
        service_manager,desktop=connect_to_libre_office()
        return service_manager, desktop
    except:
        logging.exception("Unresolved problems connecting to UNO server")
        raise

def kill_loffice():
    global loffice_started
    if loffice_started==True:
      logging.info("Killing loffice processes...")
      os.system("killall soffice.bin oosplash.bin")
        
def run():
    for filename in sys.argv[1:]:
        convert_spreadsheet(filename)

def configure_log():
    logger = logging.getLogger('')
    logger.setLevel(logging.DEBUG)
    handler = logging.StreamHandler(sys.stdout)
    logger.addHandler(handler)
    format = "%(asctime)s %(levelname)-7s [%(name)s] %(message)s"
    handler.setFormatter(logging.Formatter(format))

if __name__ == '__main__':
    configure_log()
    run()
    kill_loffice()
    sys.exit(0)