#### start playing with the spreadsheet

import uno

# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()

# create the UnoUrlResolver
resolver = localContext.ServiceManager.createInstanceWithContext(
                                "com.sun.star.bridge.UnoUrlResolver", localContext )

# connect to the running office
ctx = resolver.resolve( "uno:pipe,name=tmh;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager

# get the central desktop object
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)

# access the current calc application
model = desktop.getCurrentComponent()


# constants to be provided 

yearX = '2016'
# mac: workDir = 'file:///Users/tmh/Documents/xyz/shared/nancy/workspace113/sfs/cashResource/'
workDir = 'file:///E:/nancy/workspace113/sfs/cashResource/'

# access the active sheet
# active_sheet = model.CurrentController.ActiveSheet

templateFile = workDir  + 'system/template.ods'
workDir      = workDir  + yearX 
dataFile     = workDir  + '/data.csv'
outputFile   = workDir  + '/1-prepared/'

# open template and data documents and loop through all rows
templateDocument = desktop.loadComponentFromURL(templateFile, "_blank", 1, ())
templateSheet    = templateDocument.Sheets.getByIndex(1)
dataDocument     = desktop.loadComponentFromURL(dataFile    , "_blank", 1, ())
dataSheet        = dataDocument.Sheets.getByIndex(0)

property1  = ("Overwrite", True)
property2  = ("FilterName", "MS Excel 97") #pyoo.FILTER_EXCEL_2007)
properties = ( property1
             , property2
             )

sourceRow  = 1
sponsor    = dataSheet.getCellByPosition(0,sourceRow)
sponsorId  = sponsor.getString()

loopX = 1

# loop through all rows in the data sheet and create an xls for each

# while sponsorId <> "" : 
while loopX < 8 : 

  loopX = loopX + 1

# assign the assignment number (i.e. sponsor's id)

  sourceCol = 0
  target = templateSheet.getCellByPosition(1,17)
  target.setString (sponsorId)
  targetRow = 19


# get two values per data-row for the reimbursement data - state and fed

  for x in [0,1,2,3]:

    sourceCol = sourceCol + 1
    source = dataSheet.getCellByPosition(sourceCol,sourceRow)
    target = templateSheet.getCellByPosition(1,targetRow)
    target.setValue (source.getValue())
    sourceCol = sourceCol + 1
    source = dataSheet.getCellByPosition(sourceCol,sourceRow)
    target = templateSheet.getCellByPosition(2,targetRow)
    target.setValue (source.getValue())

    print(x)
    print(source.getValue())

    targetRow = targetRow + 1

    continue     # for x in range(4)


  targetRow = targetRow + 1     # skip blank line

# get two values per data-row for the reimbursement data - state and fed

  for x in range(7):

    sourceCol = sourceCol + 1
    source = dataSheet.getCellByPosition(sourceCol,sourceRow)
    target = templateSheet.getCellByPosition(1,targetRow)
    target.setValue (source.getValue())
    sourceCol = sourceCol + 1
    source = dataSheet.getCellByPosition(sourceCol,sourceRow)
    target = templateSheet.getCellByPosition(2,targetRow)
    target.setValue (source.getValue())

    targetRow = targetRow + 1

    print(x)
    print(source.getValue())

    continue     # for x in range(7)

# recalculate the source spreadhseet and save it as xls file

  templateDocument.calculateAll()
  templateDocument.storeAsURL( outputFile + sponsorId + ".xls", ())
#                            , Array ( makePropertyValue ("Overwrite" , True)
#                                    , makePropertyValue ("FilterName" , "MS Excel 97")
#                                    ) #pyoo.FILTER_EXCEL_2007
#                            , ( ("Overwrite", True), ("FilterName", "MS Excel 97") )
#                            , properties
#                            )

  sourceRow = sourceRow + 1
  sponsor   = dataSheet.getCellByPosition(0,sourceRow)
  sponsorId = sponsor.getString()

  print(sponsor.getString())

  continue     # while sponsor <> ""

# row = row + 1

# make sure all cells have current values, and save the file
# document.dispose()
# doc.save('example.xlsx', pyoo.FILTER_EXCEL_2007)

templateDocument.dispose()