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()