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. """ url = convert_path_to_url(fname) component = Calc(context, url) return component
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 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 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 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)