def check_server(address, port):
# Create a TCP socket
try:
unotools.connect(unotools.Socket(address, port))
return True
except:
return False
def convert_csv(fname):
"""
Convert all of the sheets in a given Excel spreadsheet to CSV files.
fname - The name of the file.
return - A list of the names of the CSV sheet files.
"""
# Make sure this is an Excel file.
if (not is_excel_file(fname)):
# Not Excel, so no sheets.
return []
# Run soffice in listening mode if it is not already running.
run_soffice()
# TODO: Make sure soffice is running in listening mode.
#
# Connect to the local LibreOffice server.
context = connect(Socket(HOST, PORT))
# Load the Excel sheet.
component = get_component(fname, context)
# Iterate on all the sheets in the spreadsheet.
controller = component.getCurrentController()
sheets = component.getSheets()
enumeration = sheets.createEnumeration()
r = []
if sheets.getCount() > 0:
while enumeration.hasMoreElements():
# Move to next sheet.
sheet = enumeration.nextElement()
name = sheet.getName()
controller.setActiveSheet(sheet)
# Set up the output URL.
short_name = fname
if (os.path.sep in short_name):
short_name = short_name[short_name.rindex(os.path.sep) + 1:]
outfilename = "/tmp/sheet_%s-%s.csv" % (short_name, name.replace(' ', '_SPACE_'))
r.append(outfilename)
url = convert_path_to_url(outfilename)
# Export the CSV.
component.store_to_url(url,'FilterName','Text - txt - csv (StarCalc)')
# Close the spreadsheet.
component.close(True)
# Kill soffice after done?
# /usr/lib/libreoffice/program/soffice.bin --headless --invisible --nocrashreport --nodefault --nofirststartwizard --nologo --norestore --accept=socket,host=127.0.0.1,port=2002,tcpNoDelay=1;urp;StarOffice.ComponentContext
# Done.
return r
def wait_for_uno_api():
"""
Sleeps until the libreoffice UNO api is available by the headless libreoffice process. Takes
a bit to spin up even after the OS reports the process as running. Tries several times before giving
up and throwing an Exception.
"""
tries = 0
while tries < 10:
try:
connect(Socket(HOST, PORT))
return
except ConnectionError:
time.sleep(5)
tries += 1
raise Exception("libreoffice UNO API failed to start")
def connect2Calc(file=None, port=8100, counter_max=5000):
"""Open libreoffice and enable conection with Calc.
Args:
file (str or pathlib.Path, optional): file to connect. If None, it will
open a new Calc instance.
port (int, optional): port for connection.
counter_max (int, optional): Max number of tentatives to establish a
connection.
Returns:
Calc object.
The main mathods defined for a Calc object are exemplyfied below:
>>> # adds one sheet ('Sheet2') at position 1
>>> calcObject.insert_sheets_new_by_name('Sheet2', 1)
>>>
>>> # adds multiple sheets ('Sheet3' and 'Sheet4) at position 2
>>> calcObject.insert_multisheets_new_by_name(['Sheet3', 'Sheet4'], 2)
>>>
>>> # Get number of sheets
>>> print(calcObject.get_sheets_count())
4
>>> # Remove sheets
>>> calcObject.remove_sheets_by_name('Sheet3')
>>> # get sheet data
>>> sheet1 = calcObject.get_sheet_by_name('Sheet1')
>>> sheet2 = calcObject.get_sheet_by_index(0)
Also, use :py:func:`~backpack.figmanip.setFigurePosition`
"""
# open libreoffice
libreoffice = subprocess.Popen([f"soffice --nodefault --accept='socket,host=localhost,port={port};urp;'"], shell=True, close_fds=True)
# connect to libreoffice
connected = False
counter = 0
while connected == False:
time.sleep(0.5)
try:
context = connect(Socket('localhost', 8100))
connected = True
except:
counter += 1
if counter == counter_max:
raise ConnectionError('Cannot establish connection, maybe try increasing counter_max value.')
pass
if file is None:
return Calc(context)
else:
file = Path(file)
return Calc(context, convert_path_to_url(str(file)))
def write_to_xls(self):
# connect
from unotools.unohelper import convert_path_to_url
context = unotools.connect(unotools.Socket(host=sohost, port=soport))
calc = Calc(context)
filled_issue = 0
# create tables of assignees
for assignee in assignees:
if assignee:
# get name of assignee
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
calc.insert_sheets_new_by_name(name, 0) # returns None
sheet = calc.get_sheet_by_name(name)
self.fill_header(sheet, name)
w4_end = self.get_day(datetime.datetime.now() - datetime.timedelta(days=1), 6)
w4_start = self.get_day(datetime.datetime.now()- datetime.timedelta(days=1), 0)
weeks = []
weeks_timestamp = []
this_week_num = w4_end.isocalendar()[1]
before_date = None
for week in reversed(range(1, 4)):
days_range = self.get_range_days_of_week(this_week_num - week, 0, 6)
weeks_timestamp.append(days_range)
weeks.append(days_range[0].strftime(self.date_format) + ' - ' + days_range[1].strftime(self.date_format))
if before_date is None:
before_date = self.get_range_days_of_week(this_week_num-week-1, 0, 6)[1]
weeks.append(str(w4_start.strftime(self.date_format)) + ' - ' + str(w4_end.strftime(self.date_format)))
# filling before
before = sheet.get_cell_range_by_position(4, 1, 5, 1)
before.setDataArray((('< ' + before_date.strftime(self.date_format), ''), ))
before.merge(True)
align = before.getPropertyValue('HoriJustify')
align.value = 'CENTER'
before.setPropertyValue('HoriJustify', align)
for w in weeks:
cell = sheet.get_cell_by_position(6 + weeks.index(w) * 2, 1)
cell.setString(w)
cells = sheet.get_cell_range_by_position(6 + weeks.index(w) * 2, 1, 7 + weeks.index(w) * 2, 1)
cells.merge(True)
align = cell.getPropertyValue('HoriJustify') # property of align 'VertJustify' had too
align.value = 'CENTER'
cells.setPropertyValue('HoriJustify', align)
sheet.get_cell_by_position(6 + weeks.index(w) * 2, 2).setString("План")
sheet.get_cell_by_position(7 + weeks.index(w) * 2, 2).setString("Факт")
logging.info('filling report of: ' + assignee)
# make headers:
# total ts and te of assignee
lines = 4 # start count from 1 row [in GUI 2]
ts, te = 0, 0
for issue in issues:
if issue.assignee == assignee:
report_issue = ReportIssue(issue, before_date, weeks_timestamp)
report_issue.generate_report()
iss = OLAP.select().where(OLAP.issue_id == issue.issue_id).get()
line = sheet.get_cell_range_by_position(0, lines, 3, lines)
iid = issue.url.split('/')[-1]
date_closing = self.get_date_closing(issue)
if date_closing != '':
row = sheet.get_cell_range_by_position(0, lines, 13, lines)
row.setPropertyValue('CellBackColor', 0xdedede)
row.setPropertyValues(self.keys, self.border_lines)
line.setDataArray(((iss.project_name,
"#" + iid + ' ' + iss.issue_title,
str(datetime.datetime.fromtimestamp(issue.created + 18000).strftime(
'%d-%m-%Y %H:%M')),
date_closing,),))
line = sheet.get_cell_range_by_position(4, lines, 13, lines)
t = report_issue.generate_report()
line.setDataArray(((t),))
hyperlink_issue = issue.url
title = sheet.get_cell_by_position(1, lines)
title.Text.Hyperlink = hyperlink_issue
title.Text.CharUnderline = UNDERLINE_SINGLE
title.Text.CharColor = 0x0000aa
title.Rows.Height = 600
ts += iss.time_spent
te += iss.time_estimate
filled_issue += 1
lines += 1
del issue
# sheet.get_cell_by_position(self.estimate_column, 3).setString(seconds_to_time(te) + ' h')
# sheet.get_cell_by_position(self.spend_column, 3).setString(seconds_to_time(ts) + ' h')
sheet.get_cell_range_by_position(1, 0, 1, 0).Columns.Width = 6000
sheet.get_cell_range_by_position(4, 0, 5, 2).Columns.OptimalWidth = True
calc.remove_sheets_by_name('Sheet1')
calc.insert_sheets_new_by_name('Список', 0)
assignee_sheet = calc.get_sheet_by_index(0)
assignee_list = []
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
assignee_list.append(name)
assignee_list = sorted(assignee_list)
for assignee in assignee_list:
cell = assignee_sheet.get_cell_by_position(0, assignee_list.index(assignee))
cell.setString(assignee)
cell.Text.Hyperlink = '#{0}'.format(assignee)
# here filling hyperlinks of assignee
logging.info(str(filled_issue) + ' issues from ' + str(len(issues)) + ' have assignee')
# issues_sheet[1:10, 5].border_right_width = 1
# saving
logging.info('saving report into ./Dock/reports/{0}.ods'.format(datetime.date.today()))
url = convert_path_to_url(xls_file.format(datetime.date.today()))
calc.store_to_url(url, 'FilterName', 'writer8')
calc.close(True)
def convert_csv(fname):
"""Convert all of the sheets in a given Excel spreadsheet to CSV
files. Also get the name of the currently active sheet.
@param fname (str) The name of the Excel file.
@return (list) A list where the 1st element is the name of the
currently active sheet ("NO_ACTIVE_SHEET" if no sheets are active)
and the rest of the elements are the names (str) of the CSV sheet
files.
"""
# Make sure this is an Excel file.
if (not is_excel_file(fname)):
# Not Excel, so no sheets.
return []
# Run soffice in listening mode if it is not already running.
run_soffice()
# TODO: Make sure soffice is running in listening mode.
#
# Connect to the local LibreOffice server.
context = None
attempts = 0
while (attempts < 5):
attempts += 1
try:
context = connect(Socket(HOST, PORT))
break
except ConnectionError:
time.sleep(1)
# Do we have a connection to the headless LibreOffice?
if (context is None):
# Can't connect to LibreOffice. Punt.
print("ERROR: Cannot connect to headless LibreOffice.")
return []
# Load the Excel sheet.
component = get_component(fname, context)
# Save the currently active sheet.
r = []
controller = component.getCurrentController()
active_sheet = None
if hasattr(controller, "ActiveSheet"):
active_sheet = controller.ActiveSheet
active_sheet_name = "NO_ACTIVE_SHEET"
if (active_sheet is not None):
active_sheet_name = fix_file_name(active_sheet.getName())
r.append(active_sheet_name)
# Bomb out if this is not an Excel file.
if (not hasattr(component, "getSheets")):
return r
# Iterate on all the sheets in the spreadsheet.
sheets = component.getSheets()
enumeration = sheets.createEnumeration()
pos = 0
if sheets.getCount() > 0:
while enumeration.hasMoreElements():
# Move to next sheet.
sheet = enumeration.nextElement()
name = sheet.getName()
if (name.count(" ") > 10):
name = name.replace(" ", "")
name = fix_file_name(name)
controller.setActiveSheet(sheet)
# Set up the output URL.
short_name = fname
if (os.path.sep in short_name):
short_name = short_name[short_name.rindex(os.path.sep) + 1:]
short_name = fix_file_name(short_name)
outfilename = "/tmp/sheet_%s-%s--%s.csv" % (
short_name, str(pos), name.replace(' ', '_SPACE_'))
pos += 1
r.append(outfilename)
url = convert_path_to_url(outfilename)
# Export the CSV.
component.store_to_url(url, 'FilterName',
'Text - txt - csv (StarCalc)')
# Close the spreadsheet.
component.close(True)
# clean up
os.kill(get_office_proc()["pid"], signal.SIGTERM)
# Done.
return r
# -*- coding: utf-8 -*-
import sys
from os.path import join as pathjoin
from unotools import Socket, connect
from unotools.component.writer import Writer
from unotools.unohelper import convert_path_to_url
def writer_sample(args, context):
writer = Writer(context)
writer.set_string_to_end('world\n')
writer.set_string_to_start('hello\n')
base_path = convert_path_to_url(pathjoin(args.outputdir, 'sample'))
writer.store_to_url(base_path + '.odt', 'FilterName', 'writer8')
writer.store_to_url(base_path + '.doc', 'FilterName', 'MS Word 97')
writer.store_to_url(base_path + '-writer.pdf', 'FilterName',
'writer_pdf_Export')
writer.store_to_url(base_path + '-writer.html', 'FilterName',
'HTML (StarWriter)')
writer.close(True)
if __name__ == '__main__':
from unotools import parse_argument
args = parse_argument(sys.argv[1:])
context = connect(Socket(args.host, args.port), option=args.option)
writer_sample(args, context)
def convert_csv(fname):
"""
Convert all of the sheets in a given Excel spreadsheet to CSV files.
fname - The name of the file.
return - A list of the names of the CSV sheet files.
"""
# Make sure this is an Excel file.
if (not is_excel_file(fname)):
# Not Excel, so no sheets.
if verbose:
print("NOT EXCEL", file=sys.stderr)
return []
# Run soffice in listening mode if it is not already running.
run_soffice()
# TODO: Make sure soffice is running in listening mode.
#
# Connect to the local LibreOffice server.
context = connect(Socket(HOST, PORT))
# Load the Excel sheet.
component = get_component(fname, context)
# Iterate on all the sheets in the spreadsheet.
controller = component.getCurrentController()
sheets = component.getSheets()
enumeration = sheets.createEnumeration()
r = []
pos = 0
if sheets.getCount() > 0:
while enumeration.hasMoreElements():
# Move to next sheet.
sheet = enumeration.nextElement()
name = sheet.getName()
if (name.count(" ") > 10):
name = name.replace(" ", "")
if verbose:
print("LOOKING AT SHEET " + str(name), file=sys.stderr)
controller.setActiveSheet(sheet)
# Set up the output URL.
short_name = fname
if (os.path.sep in short_name):
short_name = short_name[short_name.rindex(os.path.sep) + 1:]
outfilename = "/tmp/sheet_%s-%s--%s.csv" % (
short_name, str(pos), name.replace(' ', '_SPACE_'))
outfilename = ''.join(
filter(lambda x: x in string.printable, outfilename))
pos += 1
r.append(outfilename)
url = convert_path_to_url(outfilename)
# Export the CSV.
component.store_to_url(url, 'FilterName',
'Text - txt - csv (StarCalc)')
if verbose:
print("SAVED CSV to " + str(outfilename), file=sys.stderr)
# Close the spreadsheet.
component.close(True)
# clean up
os.kill(get_office_proc()["pid"], signal.SIGTERM)
if verbose:
print("KILLED SOFFICE", file=sys.stderr)
# Done.
if verbose:
print("DONE. RETURN " + str(r), file=sys.stderr)
return r
help="export a string containing the document text")
arg_parser.add_argument("-f",
"--file",
action="store",
required=True,
help="path to the word doc")
args = arg_parser.parse_args()
# Make sure this is a word file.
if (not is_word_file(args.file)):
# Not Word, so no text.
exit()
# Run soffice in listening mode if it is not already running.
run_soffice()
# Connect to the local LibreOffice server.
connection = connect(Socket(HOST, PORT))
# Load the document using the connection
document = get_document(args.file, connection)
if args.text:
print(get_text(document))
elif args.tables:
print(json.dumps(get_tables(document)))
# clean up
os.kill(get_office_proc()["pid"], signal.SIGTERM)
def __enter__(self):
context = connect(Socket(self.host, self.port))
self.writer = Writer(context, convert_path_to_url(self.input_path))
return self
def __enter__(self):
context = connect(Socket(self.host, self.port))
self.writer = Writer(context, convert_path_to_url(self.input_path))
return self
