from gi.repository import GOffice as Go
Gnm.init()

if Gnm.in_tree:
    print("Using in-tree gi.overrides.Gnm")
else:
    print("Using installed gi.overrides.Gnm at {}".format(
        gi.overrides.Gnm.__file__))

# -----------------------------------------------------------------------------

print("\nTesting GnmValue overrides:")
# __str__
print(Gnm.Value.new_empty())
print(Gnm.Value.new_bool(0))
print(Gnm.Value.new_bool(1))
print(Gnm.Value.new_int(12))
print(Gnm.Value.new_float(12.5))
print(Gnm.Value.new_string("howdy"))
v = Gnm.Value.new_float(12.5)
v.set_fmt(Go.Format.new_from_XL("0.00"))
print(v)

# -----------------------------------------------------------------------------

print("\nTesting GnmRange overrides:")
# __new__
r = Gnm.Range(1, 2, 3, 4)
# __str__
print(r)
print("Name: {}".format(sheet.props.name))
print("Number of columns: {}".format (sheet.props.columns))
print("Number of rows: {}".format (sheet.props.rows))

# Store values and expressions is some cells.  Coordinates are (col,row)
# both starting at 0.  (So what the gui sees as row 1 is 0 here.)
sheet.cell_set_value(0,0,Gnm.Value.new_int(10))
sheet.cell_set_value(0,1,Gnm.Value.new_float(101.25))
sheet.cell_set_text(0,2,"=A1+A2")
sheet.cell_set_text(0,3,"'01")
sheet.cell_set_text(0,4,"zzz")
sheet.cell_set_value(0,5,Gnm.Value.new_string("abc"))
sheet.cell_set_value(0,6,Gnm.Value.new_bool(1))

# Copy A1:A7, paste to C1:C7
src = Gnm.Range()
src.init(0,0,0,6)
cr = Gnm.clipboard_copy_range(sheet,src)
dst = Gnm.Range()
dst.init(2,0,2,6)
pt = Gnm.PasteTarget.new (sheet,dst,Gnm.PasteFlags.DEFAULT)
Gnm.clipboard_paste_region(cr,pt,None)


# Make A1:A2 bold
st = Gnm.Style.new()
st.set_font_bold(1)
r = Gnm.Range()
r.init(0,0,0,1)
sheet.apply_style(r,st)
def main():

    gnm_init()

    # Open (or create if it doesn't exist) our price tracking workbook.
    wb = wb_open("btcprices.gnumeric")

    # Select the first sheet in the workbook, check the name, and update name.
    sheet = wb.sheet_by_index(0)
    if sheet.props.name != "Bitcoin Prices":
        sheet.props.name = "Bitcoin Prices"

    # Check to see if there is a title row by checking if there's any value set at B2.
    # If none is set, populate the title row and style it. This is effectively a
    # run-once operation.

    if sheet.cell_get_value(1, 1) is None:
        # Labels
        sheet.cell_set_text(1, 1, "Timestamp (UTC)")
        sheet.cell_set_text(2, 1, "BTC Price (USD)")

        # Bold
        st = Gnm.Style.new()
        st.set_font_bold(1)
        r = Gnm.Range()
        # col row col row range, so 1:1 (B2)
        r.init(1, 1, 2, 1)
        sheet.apply_style(r, st)

        # Adjust Column Width
        # TBD - no blessed direct sizing interface, using autofit
        #r = Gnm.Range()
        #r.init(0,0,4,4)
        #Gnm.colrow_autofit_col(sheet, r)

    # Find first unused row in sheet to set up the addition of data
    for row in range(2, sheet.props.rows):
        if sheet.cell_get_value(1, row) is None:
            break

    # At this point, row points to the next open row

    # We will use the json interface to coinbase at https://api.coindesk.com/v1/bpi/currentprice.json
    # to extract the updated time and the current USD value, and then populate the row with that data.
    # Note that the user-agent needs to be set to a sane value, as urllib's default is banned due to abuse.
    # (per coinbase's request in their json feed, please don't abuse the interface with excessive requests!)
    #

    import urllib2, json

    url = "https://api.coindesk.com/v1/bpi/currentprice.json"
    agent = "Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.8.1.11) Gecko/20071127 Firefox/2.0.0.11"

    opener = urllib2.build_opener()
    opener.addheaders = [('User-Agent', agent)]
    response = opener.open(url)
    data = json.loads(response.read())

    # Update the row with the time the price was updated
    # we're getting the time in ISO 8601 format, which gnumeric won't directly
    # interpret, so we're going to convert it to python time, and the re-write it
    # in yyyy-mm-dd hh:mm:ss format that gnumeric is fine with.

    import dateutil.parser
    import time

    tdate = dateutil.parser.parse(data['time']['updated'])

    # Note we use set_text and not set_value(Gnm.Value.new_string()) because we
    # want gnumeric to interpret our input for us into a time value. If we just
    # wanted the raw text, we'd use set value and new_string.

    sheet.cell_set_text(1, row, tdate.strftime("%Y-%m-%d %H:%M:%S"))

    # ... then we update with the current USD exchange rate.
    # We get a little fancy using "locale" to deal with comma separators in the
    # ascii to floating point conversion using atof. (We could just set text in
    # gnumeric as well, but  then we wouldn't be showing off setting
    # floating point values ;-)

    import locale
    locale.setlocale(locale.LC_NUMERIC, '')
    sheet.cell_set_value(
        2, row, Gnm.Value.new_float(locale.atof(data['bpi']['USD']['rate'])))

    # Let's format the price to just dollars with a thousands separator
    st = Gnm.Style.new()
    st.set_format_text("$0,000")
    r = Gnm.Range()
    r.init(2, row, 2, row)
    sheet.apply_style(r, st)

    # Whew! All done. Save it out and quit.

    wb_save(wb)

    # Cleanup
    wb = None
    ioc = None

    quit()