def schedule( request ):
spreadsheet = SpreadSheet( settings.GOOGLE['email'],
settings.GOOGLE['password'],
'results2013'
)
if request.method == 'POST':
town = request.POST['town']
level = request.POST['level']
else:
town = 'all'
level = 'A1'
worksheet = spreadsheet.get_worksheet_byname('results2013',level)
results = worksheet.get_rows()
if town != 'all':
games = []
for game in results:
if town in game.values():
games.append(game)
else:
games = results
return render_to_response('schedule.html', {'games':games},
context_instance=RequestContext(request))
def __init__(self, args):
# Setup Logger
self.logger = logging.Logger('BuddyRanker')
console = logging.StreamHandler()
console.setLevel('DEBUG' if args.debug else 'INFO')
formatter = logging.Formatter(
fmt='%(asctime)s %(levelname)-5s %(message)s',
datefmt='%Y-%m-%d %H:%M:%S')
console.setFormatter(formatter)
self.logger.addHandler(console)
# Setup Goggle Sheets Credentials and options
self.SpreadSheet = SpreadSheet(gsecrets=args.g_secrets, url=args.g_url)
self.g_scores_sheet = "Scores"
self.g_ranking_sheet = "Rankings"
self.g_rank_header = ["Player", "Relative Rank (out of 1000)"]
self.ignore_upload = args.g_ignore_upload
# Setup localdata parametes
self.localdata = args.localdata
self.headers = [
'Player 1', 'Player 2', 'Score Player 1', 'Score Player 2'
]
# Bradley Terry related parameters
self.init_rank = args.init_rank
self.tol = args.tol
self.max_itt = args.max_itt
def spreadSheet_insert(t0,t1,t2,h,l):
KEY_FILENAME ='/home/pi/python_apps/hydroponics/auth.json'
SHEET_ID ='1nZhut2Sp8ZlijdCSqxCs4__dDsPXPXUYnu891RYcHqE'
APPEND_RANGE = 'sheet1!A1:D1'
APPEND_LENGTH = 5
sheet = SpreadSheet(KEY_FILENAME,SHEET_ID,APPEND_RANGE,APPEND_LENGTH)
sheet.append(["{0:%Y-%m-%d %H:%M:%S}".format(t0), t1, t2,h,l])
def __init__(self):
# Options to be displayed in a .txt file
file_text = open("menu_options.txt", "r")
self.text_menu = file_text.read()
# Init spreadsheet by default size [20, 20]
self.spreadsheet = SpreadSheet([20, 20])
self.choices = {
"1": self.new_spreadsheet,
"2": self.save_spreadsheet,
"3": self.load_spreadsheet,
"4": self.set_cell_value,
"5": self.get_cell_value,
"6": self.quit
}
def dummy(rows, cols, dtcols=None, DfltVals=None, phcol=None, emailcol=None):
if DfltVals != None:
name = change_to_array(DfltVals)
if dtcols != None:
dtcols = change_to_array(dtcols)
if phcol != None:
phcol = change_to_array(phcol)
if emailcol != None:
emailcol = change_to_array(emailcol)
try:
rows = int(rows)
except:
rows = 0
try:
cols = int(cols)
except:
cols = 0
app = QApplication(sys.argv)
# print(phcol)
# print(emailcol)
sheet = SpreadSheet(rows,
cols,
datecol=dtcols,
name=name,
titlerow=None,
phcol=phcol,
emailcol=emailcol)
sheet.setWindowIcon(QIcon(QPixmap(".\\images\\image.png")))
sheet.resize(1280, 720)
sheet.show()
sys.exit(app.exec_())
def __init__(self):
wx.Frame.__init__(self, None,
title='SpreadSheet Grid Editor', size=(640, 480))
spreadsheet = SpreadSheet()
spreadsheet.setCellFormula('a1', '2+3')
spreadsheet.setCellFormula('a2', 'a1')
spreadsheet.setCellFormula('a3', '"button"')
spreadsheet._calculate()
def cell_selection_callback(cell_name, cell):
print cell_name, cell
grid = SpreadSheetGrid(self, spreadsheet, [cell_selection_callback])
def on_octopart_lookup_component(self, event):
ct = self._current_type
if not ct:
return
if not ct.has_valid_key_fields:
raise Exception("Missing key fields (value / footprint)!")
category = self.part_category(ct.refs)
ol = octo.octopart_lookup()
pn = ct.value
te = wx.TextEntryDialog(self,
'Update search string',
caption='Search part',
value=pn)
rs = te.ShowModal()
pn = te.Value
while True:
cat_uid = ol.find_categories(pn)
up = ol.parts_search(pn, cat_uid)
#up = ol.parts_search(pn)
hits = ol.get_hits()
if hits < 1:
te = wx.TextEntryDialog(self,
'Component does not exist in Octopart',
caption='Component not found',
value=ct.value)
rs = te.ShowModal()
if rs == wx.ID_CANCEL:
return
pn = te.Value
else:
break
# self.fields = list(up[0].keys())
self.fields = ol.get_fields()
ss = SpreadSheet(self, up)
ss.Show()
class Main():
""" Clase para crear objectos de página.
"""
# Lista de enlaces seleccionados de un value_list espeficico de una SpreadSheet
link_list = SpreadSheet(sheet='Creación de cursos 2018-2',
worksheet='Cursos 2018-2', values_list='BM180:BM181').get_link_list()
def __init__(self):
"""Constructor de la clase Main inicializamos el driver en la pagina de inicio
"""
self.driver = webdriver.Firefox()
self.driver.get("http://tic.uis.edu.co/")
def log_in_experticava(self):
"""
login experticava25. Ingresa a la plataforma con las credenciales especificas
"""
# Log in
login_page = page.LoginPage(self.driver)
login_page.click_posgrado_button()
login_page.username_element = 'exper-tic'
login_page.password_element = 'exper-tic'
login_page.click_login_button()
def get_history_score(self, url):
self.driver.get(url)
history_score_page = page.HistoryScorePage(self.driver)
history_score_page.click_enviar_button()
time.sleep(5)
history_score_page.click_download_button()
time.sleep(5)
def select_report(self, url):
""" Selecciona reporte por cursos en este caso tipo de reporte estudiantes y
período a mostrar de 6 meses
Arguments:
url {string} -- [url para cada página reporte]
"""
# Select report
self.driver.get(url)
statistics_page = page.StatisticsPage(self.driver)
# Menu report 53 Profesor, 55 Estudiante
statistics_page.menu_report_element = '55'
# Page menu 16: 6 months
statistics_page.menu_time_element = '16'
statistics_page.click_vista_button()
def tearDown(self):
self.driver.close()
class TestCase(unittest.TestCase):
""" Clase para probar objectos de página.
Arguments:
unittest {library} -- Python unittesting framework
"""
link_list = SpreadSheet(sheet='Creación de cursos 2018-2',
worksheet='Cursos Sedes',
values_list='AG3:AG188').get_link_list()
def setUp(self):
#chrome_options = webdriver.ChromeOptions()
#prefs = {'download.default_directory' : 'G:\\Mi unidad\\1_ExperTIC\\03_Ejecucion\\2019\\02_ProcesosPrimarios\\07_TrabajoconAuxiliares\\Descarga de Notas\\2018-2'}
#chrome_options.add_experimental_option('prefs', prefs)
#self.driver = webdriver.Chrome(executable_path="chromedriver.exe", chrome_options=chrome_options)
self.driver = webdriver.Firefox()
self.driver.get("http://tic.uis.edu.co/ava25")
# Log in
login_page = page.LoginPage(self.driver)
#login_page.click_posgrado_button()
login_page.username_element = 'exper-tic'
#login_page.password_element = 'exper-tic'
login_page.password_element = '123581mapa'
login_page.click_login_button_ava25()
def test_export_excel(self):
# Select report
for link in self.link_list:
print(link)
if (link == ''):
pass
else:
self.driver.get(link)
export_xls_page = page.ExportXlsPage(self.driver)
export_xls_page.click_enviar_button()
export_xls_page.click_descargar_button()
time.sleep(10)
pass
def tearDown(self):
self.driver.close()
#!/usr/bin/python3
#coding: utf-8
import time
import datetime
from spreadsheet import SpreadSheet
import bme280
# シート固有のid
sheet = SpreadSheet('xxx')
# データ取得のインターバル[sec]
delay = 600
while (1):
start = time.time()
d_time = datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
sensor = bme280.readData()
sheet.append([d_time, sensor[0], sensor[1], sensor[2]])
lap = time.time() - start
#print("lap_time:{0}".format(end) + "[sec]")
neri = delay - lap
time.sleep(neri)
class BuddyRanker():
def __init__(self, args):
# Setup Logger
self.logger = logging.Logger('BuddyRanker')
console = logging.StreamHandler()
console.setLevel('DEBUG' if args.debug else 'INFO')
formatter = logging.Formatter(
fmt='%(asctime)s %(levelname)-5s %(message)s',
datefmt='%Y-%m-%d %H:%M:%S')
console.setFormatter(formatter)
self.logger.addHandler(console)
# Setup Goggle Sheets Credentials and options
self.SpreadSheet = SpreadSheet(gsecrets=args.g_secrets, url=args.g_url)
self.g_scores_sheet = "Scores"
self.g_ranking_sheet = "Rankings"
self.g_rank_header = ["Player", "Relative Rank (out of 1000)"]
self.ignore_upload = args.g_ignore_upload
# Setup localdata parametes
self.localdata = args.localdata
self.headers = [
'Player 1', 'Player 2', 'Score Player 1', 'Score Player 2'
]
# Bradley Terry related parameters
self.init_rank = args.init_rank
self.tol = args.tol
self.max_itt = args.max_itt
def read_local_file(self):
lst = []
with open(self.file, 'rb') as csvfile:
reader = csv.reader(csvfile, delimiter=',')
for row in reader:
if len(row) != 4:
self.logger.critical(
'FATAL: CSV should contain rows with 4 columns only')
exit(1)
# Keep same format a google sheet
lst.append(dict(zip(self.headers, row)))
return lst
def get_player_list(self, game_data, headers):
players = set()
for game in game_data:
players.add(game[headers[0]])
players.add(game[headers[1]])
return list(players)
def init_fake_winners(self, game_data, headers):
players = self.get_player_list(game_data=game_data, headers=headers)
wins = {}
for player in players:
for opp in players:
if opp == player: continue
if player not in wins.keys():
wins[player] = {opp: 1}
else:
wins[player][opp] = 1
return wins
def get_game_data(self):
if self.localdata != None:
game_data = self.read_local_file()
headers = self.headers
else:
game_data = self.SpreadSheet.open_sheet(
spreadsheet=self.g_scores_sheet)
headers = list(reversed(game_data[0].keys()))
return game_data, headers
def setup_wins(self, game_data, headers):
wins = self.init_fake_winners(game_data=game_data, headers=headers)
for game in game_data:
player1 = game[headers[0]]
player2 = game[headers[1]]
player1_score = int(game[headers[2]])
player2_score = int(game[headers[3]])
self.logger.debug(
"Player1: %s, Score: %s Player2: %s, Score: %s", player1,
player1_score, player2, player2_score)
# Determine who won game
if player1_score > player2_score:
self.logger.debug("Player 1 Wins: %s", player1)
wins[player1][player2] += 1
elif player2_score > player1_score:
self.logger.debug("Player 2 Wins: %s", player2)
wins[player2][player1] += 1
else:
self.logger.critical("No Ties allowed, continuing")
self.logger.info("Wins calculated: %s", wins)
return wins
def get_games_played(self, wins, player1, player2):
tot = 0
if player1 in wins.keys() and player2 in wins[player1].keys():
tot += wins[player1][player2]
if player2 in wins.keys() and player1 in wins[player2].keys():
tot += wins[player2][player1]
return tot
def get_vector_diff(self, list1, list2):
# Lists must be of same length
diff = 0
for i in range(0, len(list1)):
diff += abs(float(list1[i]) - float(list2[i]))
return diff
def norm_dict(self, ranks):
factor = 1.0 / sum(ranks.itervalues())
return {k: v * factor for k, v in ranks.iteritems()}
def train_ranking(self):
game_data, headers = self.get_game_data()
wins = self.setup_wins(game_data=game_data, headers=headers)
players = self.get_player_list(game_data=game_data, headers=headers)
total_wins = {player: sum(wins[player].values()) for player in wins}
# Generate initial rank vector
rank = self.norm_dict({player: self.init_rank for player in players})
itt = 0
while itt < self.max_itt:
itt += 1
last_rank = rank.copy()
for player in players:
if player not in wins.keys():
rank[player] = 0
else:
tot = 0
for opp in wins[player]:
tot_games = self.get_games_played(wins=wins,
player1=player,
player2=opp)
tot = tot_games / (last_rank[player] + last_rank[opp])
rank[player] = total_wins[player] / tot
# normalize
rank = self.norm_dict(rank)
if float(self.get_vector_diff(last_rank.values(),
rank.values())) < float(self.tol):
self.logger.info("Converged after %s itterations", itt)
break
elif itt % 10 == 0:
self.logger.info("Working on itteration %s ", itt)
self.logger.info("Completed ranks: %s", rank)
return rank
def upload_sheet(self, ranks):
if self.ignore_upload:
self.logger.info(
"Did not upload to Google Sheets due to ignore_upload flag")
return
# SpreadSheet.upload_sheet uploads 2D array to GSheets
data = []
for key in ranks:
data.append([key, int(ranks[key] * 1000)])
data = sorted(data, key=lambda x: (x[1]), reverse=True)
data.insert(0, self.g_rank_header)
self.logger.info("Uploading to Google Sheets")
self.SpreadSheet.upload_sheet(data=data,
spreadsheet=self.g_ranking_sheet)
self.logger.info("Completed upload to Google Sheets")
args = parser.parse_args()
period = min(args.period, 12)
ym_list = [(args.year if args.month - i > 0 else args.year - 1,
args.month - i if args.month - i > 0 else args.month + 12 - i)
for i in range(period)]
if args.selenium:
mf = MoneyForwardSelenium(os.environ['MONEYFORWARD_KEYFILE'])
else:
mf = MoneyForwardRequests(os.environ['MONEYFORWARD_KEYFILE'])
if (not (mf.login())):
del mf
sys.exit()
if args.update:
mf.fetch()
ss = SpreadSheet(os.environ['SPREADSHEET_KEYFILE'],
os.environ['SPREADSHEET_ID'])
mfdata_dict = {}
ssdata_dict = {}
lock1 = threading.Lock()
lock2 = threading.Lock()
ts1 = [
threading.Thread(target=run, args=(year, month, ssdata_dict, ss))
for (year, month) in ym_list
]
ts2 = [
threading.Thread(target=run,
args=(year, month, mfdata_dict, mf, args.selenium, lock1))
for (year, month) in ym_list
]
ts3 = [
threading.Thread(target=run2,
from spreadsheet import SpreadSheet
if __name__ == '__main__':
# create object to access "Validacao_VersaoTestes" spreadsheet
spreadSheet = SpreadSheet()
# open worksheet with index no. 1: "Validacao"
worksheet = spreadSheet.get_worksheet('Validacao_VersaoTestes', 1)
# check a specific cell of column no. 24
spreadSheet.check_cell(20)
# uncheck a specific cell of column no. 24
spreadSheet.uncheck_cell(21)
# write an error on a specific cell of column no. 25
spreadSheet.write_error("error: another dumb error", 20)
# write a json object error on cell (20, 26)
spreadSheet.write_json_error({"error": "this is a json error"}, 20, 26)
class Menu:
"""
Display a menu and respond to choices when run.
"""
def __init__(self):
# Options to be displayed in a .txt file
file_text = open("menu_options.txt", "r")
self.text_menu = file_text.read()
# Init spreadsheet by default size [20, 20]
self.spreadsheet = SpreadSheet([20, 20])
self.choices = {
"1": self.new_spreadsheet,
"2": self.save_spreadsheet,
"3": self.load_spreadsheet,
"4": self.set_cell_value,
"5": self.get_cell_value,
"6": self.quit
}
def display_menu(self):
print(self.text_menu)
def run(self):
'''Display the menu and respond to choices.'''
while True:
self.display_menu()
choice = input("Enter an option: ")
action = self.choices.get(choice)
if action:
action()
else:
print("{0} is not a valid choice".format(choice))
def new_spreadsheet(self):
print('New Spreadsheet Selected')
n_rows = input("Number of rows for spreadsheet: ")
n_cols = input("Number of columns for spreadsheet: ")
self.spreadsheet = SpreadSheet([int(n_rows), int(n_cols)])
def save_spreadsheet(self):
print('Save Spreadsheet Selected')
def load_spreadsheet(self):
print('Load Spreadsheet Selected')
def set_cell_value(self):
print('Set a cell value')
posx = input("Index of column: ")
posy = input("Index of row: ")
value = input("Enter a value or expression: ")
# TODO: Com diferenciem el tipus de valor entre string i numeric? -> En un input el valor sempre es string
# Mirar si es numero??
self.spreadsheet.set(posx=int(posx), posy=int(posy), value=value)
def get_cell_value(self):
print('Get a cell value')
posx = input("Index of column: ")
posy = input("Index of row: ")
cell = self.spreadsheet.get_by_pos(posx=int(posx), posy=int(posy))
print(cell.value)
def quit(self):
print("Bye Nerd!")
sys.exit(0)
def new_spreadsheet(self):
print('New Spreadsheet Selected')
n_rows = input("Number of rows for spreadsheet: ")
n_cols = input("Number of columns for spreadsheet: ")
self.spreadsheet = SpreadSheet([int(n_rows), int(n_cols)])
def spreadSheet_insert(keyFileName, sheetId, appendRange, appendLength, t0, t1,
t2, h, l):
sheet = SpreadSheet(keyFileName, sheetId, appendRange, appendLength)
sheet.append(["{0:%Y-%m-%d %H:%M:%S}".format(t0), t1, t2, h, l])
token = "Zaj0BRu04W1aofVIm2AIxGdhuwI5fUayF9ji8sCBpru"
headers = {
"Authorization": "Bearer " + token,
"Content-Type": "application/x-www-form-urlencoded"
}
#message = 'message送信！'
payload = {"message": text, "stickerPackageId": 2, "stickerId": 513}
r = requests.post(url, headers=headers, params=payload)
from spreadsheet import SpreadSheet
spreadsheet_name = '1a_PQovhySYPV5D-rGhs1Soh7pvhGWmVltSPsSX_VmuA'
key_name = json_dir + 'raspberryai-62aca965a8af.json'
sheet_name = 'raspi-jump-drive' #Sheet1' # シート名
sheet = SpreadSheet(
spreadsheet_name) #'1a_PQovhySYPV5D-rGhs1Soh7pvhGWmVltSPsSX_VmuA')
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def get_spreadsheet(searchKey):
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
key_name, scope)
gc = gspread.authorize(credentials) # JSONキーファイルで認証
wks = gc.open(sheet_name).sheet1 # sheetをオープン
records = wks.get_all_values() # 中身を取り出して配列に保存
