def __connect_to_soffice(self):
"""Make a connection to soffice and fail if it can not connect."""
MAX_ATTEMPTS = 30
attempt = 0
while 1:
if attempt > MAX_ATTEMPTS: # soffice process isin't coming up
raise RuntimeError(
"Could not connect to the soffice process. Did LibreOffice start?"
)
try:
self.soffice = pyoo.Desktop(pipe=SOFFICE_PIPE)
logging.info("Connected to soffice.")
break
except (OSError, IOError):
attempt += 1
sleep(1) # Wait for the soffice process to start
except Exception as e:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
print(exc_type, fname, exc_tb.tb_lineno)
def __init__(self, filename):
#####################
# add the following at the top of
# file:///home/alexander/programs/anaconda3/lib/python3.6/site-packages/pyoo.py
# import sys
# sys.path.append('/usr/lib/libreoffice/program')
# and the following into def open_spreadsheet(self, path, as_template=False):
# extra = ()
# pv = uno.createUnoStruct('com.sun.star.beans.PropertyValue')
# pv.Name = 'MacroExecutionMode'
# pv.Value = 4
# extra += (pv,)
######################
sys.path.append('/usr/lib/libreoffice/program')
if not self.Running_process_id() is None:
logger.info('Was ' + filename + ' already open?')
os.system(
'libreoffice --invisible --accept="socket,host=localhost,port=2002;urp;" --norestore --nologo &'
) # call subprocess
time.sleep(3)
self.desktop = pyoo.Desktop('localhost', 2002)
def get_oodesktop():
from subprocess import Popen
import atexit
from time import sleep
# Run and wait for the socket to be ready.
ooserver = Popen(
shlex.split(
'soffice --accept="pipe,name=soffice.pipe;urp;" --norestore --nologo --nodefault --headless'
))
atexit.register(ooserver.terminate)
sleep(5)
log.info("Connecting to ooserver")
desktop = pyoo.Desktop(pipe='soffice.pipe')
return desktop
def runRetrieveLibre(configFile):
print("running retrieveLibre.py")
port = configFile.LIBREPORT
myPath = configFile.CSVPATH
#if possible the script should prompt the user to save their work.
#This script also needs to deal with the port not being available.
libreCall.deactivateLibreOffice()
libreCall.activateLibreOffice(port)
desktop = pyoo.Desktop('localhost', port)
doc = desktop.open_spreadsheet(configFile.OSDPATH)
libre = readLibre(doc)
writeCSV(libre, myPath)
doc.close()
libreCall.deactivateLibreOffice()
def dbprepare(dbcon):
global engrunsheet
global exlogsheet
global doc
dbcon.execute(sqlprepare2)
dbcon.execute(sqlprepare)
if scanconfig.cte_export_ods:
oodesktop = pyoo.Desktop('localhost', 2002)
doc = oodesktop.create_spreadsheet()
engrunsheet = doc.sheets.create('EngRun', index=1)
exlogsheet = doc.sheets.create('ExLog', index=1)
del doc.sheets[0]
if scanconfig.cte_export_openpyxl:
doc = Workbook()
engrunsheet = doc.create_sheet()
engrunsheet.title = 'EngRun'
exlogsheet = doc.create_sheet()
exlogsheet.title = 'ExLog'
ws = doc.active
doc.remove_sheet(ws)
return True
def setUpModule():
global desktop
desktop = pyoo.Desktop()
logging.info('Starting the soffice process.')
command = '/usr/bin/soffice --accept="pipe,name=' + SOFFICE_PIPE + ';urp;"\
--norestore --nologo --nodefault --headless'
logfile = open(SOFFICE_LOG, "w")
subprocess.Popen(command, shell=True, stdout=logfile, stderr=logfile)
# Make a connection to soffice and fail if it can not connect
MAX_ATTEMPTS = 60
attempt = 0
while 1:
if attempt > MAX_ATTEMPTS: # soffice process isin't coming up
raise RuntimeError("Could not connect to soffice process.")
try:
soffice = pyoo.Desktop(pipe=SOFFICE_PIPE)
logging.info("Connected to soffice.")
break
except OSError:
attempt += 1
sleep(1)
# Start server initialisation
HOST, PORT = "localhost", 5555
try:
server = ThreadedTCPServer((HOST, PORT), ThreadedTCPRequestHandler)
except OSError:
print(
"Error: The port is in use. Maybe the server is already running?")
targets = data['targets']
statuses = data['statuses']
# Ahora vamos a generar los diagramas de jerarquía y de dependencia para cada una de los requisitos, y los guardaremos en la carpeta doc.
#print("len(reqs)",len(reqs))
reqdict,reqlist,my_doc_issues = tree_to_dict_list(reqs,None)
#print("ACABAMOS!!!!!!!!!!!!!!!!!!!!!!!!!!!")
# Conectaremos con nuestra instancia de PYOO
# https://github.com/seznam/pyoo
# In[ ]:
import pyoo
desktop = pyoo.Desktop('localhost', 2002)
# Copiamos el template del fichero de exportación al nombre de exportación
# In[ ]:
from shutil import copyfile
copyfile('./plugins/cosmosys_req/assets/pythons/templates/RqDownload.ods', download_filepath)
# Hemos de cargar los RqTarget
# In[ ]:
def power_sheet(path, email):
# open soffice using the bash script
soffice = subprocess.Popen('startLO')
t.sleep(7)
dt = []
# set up the libreoffice bridge
desktop = pyoo.Desktop('localhost', 2002)
doc = desktop.open_spreadsheet("../docs/templates/Power_template.ods")
# name the sheets
sum = doc.sheets[0]
power = doc.sheets[1]
delta = doc.sheets[2]
# comment this loop when sensor is plugged in
# with open("../data/sensordata/power.txt", "r") as ins:
# for line in ins:
# line = line.rstrip('\n')
# lines.append(line)
# get sensor values
with open(path, "r") as ins:
for line in ins:
line = line.rstrip('\n')
lines.append(line)
# insert sensor values into the spreadsheet
power[1:496,0].values = lines
# retrieve delta theta
lines.clear()
with open("../docs/templates/delta_theta.txt", "r") as ins:
for line in ins:
line = line.rstrip('\n')
dt.append(line)
delta[1:16,1].values = dt
# retrieve payload
max_pow = round(power[20, 43].value)
rpm_opt = round(power[20, 46].value)
rpm_max = round(power[22, 46].value)
# calculate fiber twitch
twitch = round((2.0833 * rpm_opt) - 198.458, 3)
# graph data
datax = power[1:11,32].values
datay1 = power[1:11,33].values
datay2 = power[1:11,34].values
graph_path = draw_graph(datax, datay1, datay2, email)
# user search to input profile data on the first sheet
profile = user_profile_search(email)
print(profile)
sum[1:10,7].values = profile
# name file, insert, and save
path = "../docs/power/"
date = datetime.now()
f_date = date.strftime('%Y-%m-%d %H.%M.%S.%f')
filename = email[0:5] + f_date + ".ods"
file_path = path + filename
doc.save(file_path)
power_insert(email, filename, file_path, date)
doc.close()
payload = []
# create payload list to return to the results page in GUI
payload.append(max_pow)
payload.append(rpm_max)
payload.append(rpm_opt)
payload.append(twitch)
payload.append(graph_path)
soffice.kill()
print("File Saved")
return payload
def calibrate_sheet(path):
# call the bash script "startLO" which opens the libreoffice instance
soffice = subprocess.Popen('startLO')
# sleep for 7 seconds, wait for libreoffice to start completely
t.sleep(7)
lines = []
desktop = pyoo.Desktop('localhost', 2002)
doc = desktop.open_spreadsheet("../docs/templates/Calibrate_blank.ods")
sheet = doc.sheets[0]
# open the calibration text file to proccess
with open(path, "r") as ins:
for line in ins:
line = line.rstrip('\n')
lines.append(line)
# the code below processes the raw sensor data and creates a new list
# that starts after the first double.
lines = list(map(int, lines))
# this loop locates the first double in the original list and creates
# a new list with the values after the double
for idx, val in enumerate(lines):
if (idx > 0):
double = lines[idx] / lines[idx - 1]
if double > 1.8:
newlines = lines[idx + 1:idx + 796]
break
# insert the new list into the calibrate spreadsheet
sheet[1:796, 0].values = newlines
# retieve the the delta theta values from the spreadsheet
delta_theta = sheet[1:16, 10].values
# update the delta _theta text file with the new delta theta values
with open("../docs/templates/delta_theta.txt", "w") as out:
for item in delta_theta:
out.write("%s\n" % item)
# create file, insert into db, and save
path = "../docs/calibration/"
date = datetime.now()
f_date = date.strftime('%Y-%m-%d %H.%M.%S.%f')
filename = "calibrate" + f_date + ".ods"
file_path = path + filename
doc.save(file_path)
calibrate_insert(filename, file_path, date)
doc.close()
# clean up
lines.clear()
newlines.clear()
soffice.kill()
print("File Saved")
# returns expected value to GUI
return 1
import pyoo
desktop = pyoo.Desktop(pipe='hello')
doc = desktop.create_spreadsheet()
sheet = doc.sheets[0]
str(sheet[0, 0].address)
sheet[0, 0].value = 1
str(sheet[0, 1].address)
sheet[0, 1].value = 2
sheet[0, 2].formula = '=$A$1+$B$1'
sheet[0, 2].value
doc.save('/output/example.xlsx', pyoo.FILTER_EXCEL_2007)
doc.close()
print("file output/example.xlsx created.")
"""
import os
import sys
base_path = tmp_global_obj["basepath"]
cur_path = base_path + "modules" + os.sep + "LibreOfficeCalc" + os.sep + "libs" + os.sep
sys.path.append(cur_path)
import pyoo
module = GetParams("module")
global desktop_calc, doc
if module == "connect":
result = GetParams("result")
try:
desktop_calc = pyoo.Desktop('localhost', 2002)
if desktop:
SetVar(result, True)
else:
SetVar(result, False)
except Exception as e:
print("\x1B[" + "31;40mAn error occurred\x1B[" + "0m")
PrintException()
raise e
if module == "new":
try:
doc = desktop_calc.create_spreadsheet()
except Exception as e:
print("\x1B[" + "31;40mAn error occurred\x1B[" + "0m")
oo_sheet[hdr_rowStart:hdr_rowEnd, 25:hdr_colEnd].border_width = 70
oo_sheet[hdr_rowStart:hdr_rowEnd, hdr_colStart:5].border_left_width = 65
oo_sheet[hdr_rowStart:hdr_rowEnd, 11].border_left_width = 65
oo_sheet[hdr_rowStart:hdr_rowEnd, 18].border_left_width = 65
oo_sheet[hdr_rowStart + 1:hdr_rowEnd, 5:11].border_left_width = 40
oo_sheet[hdr_rowStart + 1:hdr_rowEnd, 12:18].border_left_width = 40
oo_sheet[hdr_rowStart + 1:hdr_rowEnd, 19:25].border_left_width = 40
pass
filename = 'analysis.ods'
target = '../../output_files/' + filename
cf_csvfile = '../../input_files/constituents-financials.csv'
#setup connection to soffice
oo_desktop = pyoo.Desktop()
#create a new analysis.ods
print("-creating new ods file...")
oo_doc = oo_desktop.create_spreadsheet()
time.sleep(1)
#get sheet
oo_sheet = oo_doc.sheets[0]
#populate spreadsheet with header
oo_write_header(oo_sheet)
#extract S&P500 company symbols,names,sectors
cf_data = pandas.read_csv(cf_csvfile)
cf_symbols = cf_data.Symbol.tolist()
current_value = sheet[row, column].value
if current_value == "":
break
tickers.append({"ticker": current_value, "row": row})
row += 1
return tickers
if __name__ == "__main__":
# run soffice script and detach it
soffice = Popen(["sh", "soffice.sh"])
# wait a little bit to make sure soffice is running properly before we do anything
# bump it up if you have problems or slower hardware
sleep(1)
desktop = pyoo.Desktop("localhost", 2002)
# open file path imported from options.py
doc = desktop.open_spreadsheet(file_path)
# select desired sheet
sheet = doc.sheets[sheet_number]
# get dictionary from crawler module and tickers from file
tickers_in_file = check_tickers_in_file()
stock_data = get_prices(tickers_in_file)
for ticker in tickers_in_file:
ticker, row = ticker["ticker"], ticker["row"]
write_to_cell(row=row,
column=start_position[1] + 1,
def write_to_xls():
# connect
"""
from unotools.unohelper import convert_path_to_url
context = unotools.connect(unotools.Socket(host=sohost, port=soport))
calc = Calc(context, convert_path_to_url(xls_file))
# sheets_count = calc.get_sheets_count()
# create tables of assignees
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
try:
sheet = calc.get_sheet_by_name(name)
except:
calc.insert_sheets_new_by_name(name, 0)
sheet = calc.get_sheet_by_name(name)
name_cell = sheet.get_cell_by_position(0,0)
name_cell.setString(name)
total_cell = sheet.get_cell_by_position(0,3)
total_cell.setString('Итого: ')
sheet.get_cell_by_position(1,3).setString('Задача')
sheet.get_cell_by_position(2,3).setString('Проект')
sheet.get_cell_by_position(3, 3).setString('opened')
sheet.get_cell_by_position(4, 3).setString('closed')
sheet.get_cell_by_position(5, 3).setString('Статус, час')
bcol = sheet.get_cell_range_by_position(6,4,6,100)
issues_of_assignee = OLAP.select().distinct().where(OLAP.assignee==assignee)
ioa = tuple(ioa.issue_title for ioa in issues_of_assignee)
print(assignee, len(ioa))
last_cell = 4
issues_in_table = []
while sheet.get_cell_by_position(0,last_cell):
if sheet.get_cell_by_position(0,last_cell).getString() != '':
if sheet.get_cell_by_position(0,last_cell).getString() in ioa:
pass
# update issue info
else:
pass
# add this issue
# for ioa in issues_of_assignee:
# if sheet.get_cell_by_position(0, last_cell).getString() == ioa.issue_title:
# pass
# todo make updating info
issues_in_table.append(sheet.get_cell_by_position(0,last_cell).getString())
last_cell += 1
else:
break
# todo here creation list of issues
pass
# todo updating state and date of issue
# todo updating dates of issues
"""
desktop = pyoo.Desktop(sohost, soport)
doc = desktop.create_spreadsheet()
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
doc.sheets.create(name, index=0)
issues_sheet = doc.sheets[name]
try:
del doc.sheets['Sheet1']
except:
pass
# set cell 0.0 to name os assignee
# issues_sheet[row, column]
issues_sheet[0, 0].value = name
# fill service data
issues_sheet[1, 0].value = 'Проект'
issues_sheet[1, 1].value = 'Задача'
issues_sheet[1, 2].value = 'opened'
issues_sheet[1, 3].value = 'closed'
issues_sheet[1, 4].value = 'План'
issues_sheet[1, 5].value = 'Факт'
start_issues = 2
for issue in issues:
if issue.assignee == assignee:
iss = OLAP.select().where(
OLAP.issue_id == issue.issue_id).get()
issues_sheet[start_issues, 1].value = iss.issue_title
issues_sheet[start_issues, 0].value = iss.project_name
issues_sheet[start_issues, 2].value = str(
datetime.datetime.fromtimestamp(issue.created))
issues_sheet[start_issues, 2].inner_border_width = 50
date_closing = get_date_closing(issue)
if date_closing != '':
issues_sheet[start_issues,
0:10].background_color = 0xdedede
issues_sheet[start_issues, 3].value = str(date_closing)
issues_sheet[start_issues, 4].value = seconds_to_time(
iss.time_estimate) + ' h'
issues_sheet[start_issues,
5].value = seconds_to_time(iss.time_spent) + ' h'
# todo get color of marks
start_issues += 1
