#!/usr/bin/python

# -*- tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*-
#*************************************************************************
#
# Copyright 2000, 2012 LibreOffice contributors and/or their affiliates. All rights reserved.
#
# Copyright information: The source code of LibreOffice is licensed under the GNU Lesser General Public License
# <http://www.libreoffice.org/download/license/>
#
# "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners
# or are in actual use as trademarks in one or more countries.
# Their respective logos and icons are also subject to international copyright laws.
# Use thereof is explained in our <http://wiki.documentfoundation.org/TradeMark_Policy>
#
#*************************************************************************

# Use uno, which uses pyuno
import uno, unohelper
import time
import sys

import dialogbox


# ## Some global objects
ctx    = uno.getComponentContext()
res    = ctx.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver",ctx)
cxt    = res.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
desktop= cxt.ServiceManager.createInstance("com.sun.star.frame.Desktop")


###########################################################
# Import data form a text file to Calc, using the dialogbox
#
def makePropertyValue(cName, uValue, nHandle, nState):
    core = cxt.ServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
    oXIdlClass = core.forName("com.sun.star.beans.PropertyValue")
    oRV,oStruct = oXIdlClass.createObject(None)
    oStruct.Name   = cName
    oStruct.Value  = uValue
    oStruct.Handle = nHandle
    oStruct.State  = nState
    return oStruct


def getDataArrayFromFile(cFile):
    aData=[] 
    fh = None       
    try:
        fh = open(cFile)
        for lino,line in enumerate(fh, start=1):
            numbers=[]            
            for k in line.split():
                n=float(k)
                numbers.append(n)
            if len(numbers) > 0:
                aData.append(numbers)
    except (IOError, OSError) as err:
        print err
        return []
    finally:
        if fh is not None:
            fh.close()
    return aData


def importValueToCalc():
    props=tuple([makePropertyValue('ReadOnly', False, 0, 0),makePropertyValue('Hidden', False, 0, 0)])
    cTemplateFile = "private:factory/scalc"
	#cTemplateFile = "file:///home/myname/docs/testtemplate.ods"
    doc=desktop.loadComponentFromURL(cTemplateFile, "_blank", 0, props)
 
    frame = doc.getCurrentController().getFrame()
    if not frame:
        print "frame not found"
        doc.close(True)
        return

    xParent = frame.getContainerWindow()
 
    # Constructing the dialog
    box = dialogbox.DialogBox(xParent, cxt)
    dModel = box.startCreateDialog(100, 100, 146, 108)
    dModel.BackgroundColor = 0xafd2cc
    btnOK = box.addButton(dModel, "Btn1", "OK", 45, 85, 40)
    btnCancel = box.addButton(dModel, "Btn2", "Cancel", 92, 85, 40)
    btnCancel.PushButtonType = dialogbox.uno_BUTTON_CANCEL
    lbl1 = box.addLabel(dModel, "Lbl1", "Data file",    3, 10)
    lbl2 = box.addLabel(dModel, "Lbl2", "Start column", 3, 27)
    lbl3 = box.addLabel(dModel, "Lbl3", "Start row",    3, 44)
    lbl4 = box.addLabel(dModel, "Lbl4", "Chart type",   3, 61)

    editA = box.addEdit(dModel, "EditA", 45, 8)

    numStartColumn = box.addNumeric(dModel, "NumStartColumn", 2, 1, 100, 45, 26, 30, 12)
    numStartColumn.DecimalAccuracy = 0
    numStartRow = box.addNumeric(dModel, "NumStartRow", 1, 1, 100, 45, 43, 30, 12)
    numStartRow.DecimalAccuracy = 0

    arrItems = ("Area", "Bar", "Line", "Donut", "Bubble")
    comboType = box.addListBox(dModel, "ChartType", arrItems, 45, 59, 70)
    comboType.Dropdown = True

    # Finish constructing the dialog
    box.Dialog = box.finishCreateDialog(dModel, "Example import with Python", 150, 263, 170, 161)
 
    # Select the 3th item of the dropdown-box        
    control_comboT = box.Dialog.getControl("ChartType")
    control_comboT.selectItemPos(2, True)
   
    box.addListeners()
    box.Dialog.execute()
    box.removeListeners()
 
    if not box.Response:
        doc.close(True)
        return

	cDataFile = editA.Text
	aValue = getDataArrayFromFile(cDataFile)

    if len(aValue) == 0:
        doc.close(False)
        return

    iColStart = numStartColumn.Value - 1
    iRowStart = numStartRow.Value - 1
    cChartType = "com.sun.star.chart.{0}Diagram".format(control_comboT.getSelectedItem())

    # Import the data
    oSheets = doc.getSheets()
    oSheet  = oSheets.getByIndex(0)
    for i in range(len(aValue)):
        measName = "Meas_{0}".format(i+1)
        oSheet.getCellByPosition(i + iColStart, iRowStart).setString( measName )
        xValue=aValue[i]
        iCol = i + iColStart
        for x in range(len(xValue)):
            oSheet.getCellByPosition(i + iColStart, x + 1 + iRowStart).setValue( xValue[x] )
		cColName = oSheet.Columns.getByIndex(iCol).getName()
		cFormula = "=AVERAGE({0}{1}:{2}{3})".format(cColName, int(1 + iRowStart), cColName, int(len(xValue) + 1 + iRowStart))
        iRow =  len(xValue) + 2 + iRowStart
		oSheet.getCellByPosition(iCol, iRow).setFormula(cFormula)

    iRow =  len(xValue) + 2 + iRowStart
    oSheet.Rows.getByIndex(iRow).CellBackColor = long('00C7EFA7', 16)
    iCol = iColStart + len(aValue)
    oSheet.getCellByPosition(iCol, iRow).setString("Average")

    # inspired by SpreadsheetDocHelper.java
    oLine = uno.createUnoStruct("com.sun.star.table.BorderLine")
    oLine.Color = 0x99CCFF;
    oLine.InnerLineWidth = 0
    oLine.LineDistance = 0
    oLine.OuterLineWidth = 100;
    oBorder = uno.createUnoStruct("com.sun.star.table.TableBorder")
    oBorder.TopLine = oBorder.BottomLine = oBorder.LeftLine = oBorder.RightLine = oLine
    oBorder.IsTopLineValid = oBorder.IsBottomLineValid = True
    oBorder.IsLeftLineValid = oBorder.IsRightLineValid = False
    oSheet.getCellByPosition(iCol, iRow).setPropertyValue( "TableBorder", oBorder );

    # Color the start row
    oSheet.Rows.getByIndex(iRowStart).CellBackColor = long('00F7EED3', 16)

    # Create a graph
    oRect = uno.createUnoStruct("com.sun.star.awt.Rectangle")
    oRect.X      =  1000
    oRect.Y      =  3300
    oRect.Width  = 16000
    oRect.Height =  9920 
    oRange = uno.createUnoStruct("com.sun.star.table.CellRangeAddress")
    oRange.Sheet       = 0
    oRange.StartColumn = iColStart
    oRange.StartRow    = iRowStart
    oRange.EndColumn   = iColStart + len(aValue) - 1
    oRange.EndRow      = iRowStart + len(xValue)
    ra = (oRange,)
    cChartName = "CHART_tx"
    oSheet.getCharts().addNewByName(cChartName, oRect, ra, True, False)
    oChart = oSheet.getCharts().getByName(cChartName)
    
    # Chart Document
    chart = oChart.getEmbeddedObject()
    chart.lockControllers()

    # See chart2/source/controller/chartapiwrapper/ChartDocumentWrapper.cxx for available properties
    chart.setPropertyValue("HasLegend", True)
    chart.setPropertyValue("BaseDiagram", cChartType)
    
    # Diagram inside the chart document
    diag = chart.getDiagram()
    
    # See chart2/source/controller/chartapiwrapper/DiagramWrapper.cxx for available properties
    diag.setPropertyValue("HasXAxis", True)
    diag.setPropertyValue("HasXAxisGrid", True)
    
    chart.unlockControllers()

    # Save to another file
    #doc.storeToURL("file:///home/myname/docs/testevalue.ods",())
    #doc.close(True)


if __name__ == "__main__":
    importValueToCalc()