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)