def convert_pdf(args, context):
component = get_component(args, context)
filename = basename(args.file_).split('.')[0] + '.pdf'
url = convert_path_to_url(pathjoin(args.outputdir, filename))
property_ = '{}_pdf_Export'.format(component.__class__.__name__.lower())
component.store_to_url(url, 'FilterName', property_)
component.close(True)
def saveCalc(calcObject, filepath=None):
"""Save xlsx file.
Note:
If `filepath` have no suffix, it adds '.ods' at the end of filepath.
Args:
calcObject (Calc object): Object created by :py:func:`calcmanip.connect2Calc`.
filepath (string or pathlib.Path, optional): filepath to save file.
"""
if filepath is None:
if calcObject.Location == '':
filepath = Path('./Untitled.ods')
warnings.warn('Saving at ./Untitled.ods')
else:
filepath = Path(calcObject.Location)
else:
filepath = Path(filepath)
# fix extension
if filepath.suffix == '':
filepath = filepath.parent / (str(filepath.name) + '.ods')
# save
url = convert_path_to_url(str(filepath))
calcObject.store_as_url(url, 'FilterName')
def get_component(fname, context):
"""
Load the object for the Excel spreadsheet.
"""
url = convert_path_to_url(fname)
component = Calc(context, url)
return component
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 get_component(args, context):
_, ext = splitext(args.file_)
url = convert_path_to_url(args.file_)
if ext == '.odt':
component = Writer(context, url)
elif ext == '.ods':
component = Calc(context, url)
else:
raise ValueError('Supported file type are [odt|ods]: {}'.format(ext))
return component
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 get_component(fname, context):
"""Load the object for the Excel spreadsheet.
@param fname (str) The name of the Excel file.
@param context (??) The UNO object connected to the local LibreOffice server.
@return (??) UNO LibreOffice Calc object representing the loaded
Excel file.
"""
url = convert_path_to_url(fname)
component = Calc(context, url)
return component
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)
def get_document(file, connection):
"""
Load the component containing the word document.
@param connection (ScriptContext) - connection to the headless LibreOffice process
@param file (str) - path to the Word doc
@return document (Writer)
"""
url = convert_path_to_url(file)
document = Writer(connection, url)
return document
def get_document(fname, connection):
"""Load the component containing the word document.
@param connection (ScriptContext) Connection to the headless LibreOffice process
@param fname (str) Path to the Word doc
@return document (Writer) UNO object representing the loaded Word
document.
"""
url = convert_path_to_url(fname)
document = Writer(connection, url)
return document
def calc_sample(args, context):
calc = Calc(context)
sheet = calc.get_sheet_by_index(0)
sheet.set_columns_str(0, 0, ['Month', 'Sales', 'End Date'])
path = next(search_file(args.datadirs[0], 'data1.csv'))
csv_file = CsvFile(path, has_header=True)
for i, data in enumerate(csv_file.read(), 1):
sheet.set_rows(0, i, data, csv_file.header)
format_date = constant('com.sun.star.util.NumberFormat.DATE')
formats = calc.get_number_formats()
locale = context.create_struct('com.sun.star.lang.Locale')
cell_range = sheet.get_cell_range_by_name('C2:C13')
cell_range.NumberFormat = formats.getStandardFormat(format_date, locale)
chart_cell_range = sheet.get_cell_range_by_name('A1:B13')
sheet.add_charts_new_by_name(
'Sales', context.make_rectangle(8000, 1000, 16000, 10000),
chart_cell_range.get_range_address(), True, True)
chart = sheet.get_chart_by_name('Sales')
chart_doc = chart.get_embedded_object()
title_text_shape = chart_doc.get_title()
title_text_shape.String = 'Sales Chart'
diagram = chart_doc.create_instance('com.sun.star.chart.BarDiagram')
diagram.Vertical = True
diagram.DataCaption = constant('com.sun.star.chart.ChartDataCaption.VALUE')
chart_doc.set_diagram(diagram)
sheets_count = calc.get_sheets_count()
new_sheets_data = ['sales', 'benefit', 'budget']
calc.insert_multisheets_new_by_name(new_sheets_data, sheets_count)
calc.get_sheet_by_name('budget').set_name('cost')
base_path = convert_path_to_url(pathjoin(args.outputdir, 'sample'))
calc.store_to_url(base_path + '.ods', 'FilterName', 'calc8')
calc.store_to_url(base_path + '.xls', 'FilterName', 'MS Excel 97')
calc.store_to_url(base_path + '.csv', 'FilterName',
'Text - txt - csv (StarCalc)')
calc.store_to_url(base_path + '-calc.pdf', 'FilterName', 'calc_pdf_Export')
calc.store_to_url(base_path + '-calc.html', 'FilterName',
'HTML (StarCalc)')
calc.close(True)
def write(self, path):
self.writer.store_to_url(convert_path_to_url(path), 'FilterName',
'writer_pdf_Export')
def write(self, path):
self.writer.store_to_url(
convert_path_to_url(path),
'FilterName',
'writer_pdf_Export'
)
def __enter__(self):
context = connect(Socket(self.host, self.port))
self.writer = Writer(context, convert_path_to_url(self.input_path))
return self
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
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
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 __enter__(self):
context = connect(Socket(self.host, self.port))
self.writer = Writer(context, convert_path_to_url(self.input_path))
return self
