0

I have very limited experience with Raspbian and LibreOffice, but I know and use Excel and xlwings all the time. I need to find a way to create a programme that does the same as one that I have created in xlwings. I would appreciate any help.

The code that I have in xlwings: Opens an Excel window; takes and validates 2 inputs and takes the time the first is entered; checks the excel file for how many rows there are; checks the last serial number; checks if the inputs pass or fail a criterion. It then adds 1 to the serial number and puts all the info into the file, which shows up in real time (This is essential). Code Below:

import xlwings as xw

file = 'test.xlsx'

wb = xw.Book(file)
sht = wb.sheets['Sheet1']
active_sheet = wb.sheets.active

sht.range('A1:A5').columns.autofit()

sht.range('A1').value = ['serial no.', 'date/time', 'gross', 'net', 'pass/fail']

while True:
    try:
        gross = float(input('Input Gross: '))
        gross = '%.2f' % gross
    except ValueError:
        print('You need to enter a number with a decimal point\nExample 7.05')
        continue

    try:
        net = float(input('Input Net: '))
        net = '%.2f' % net
    except ValueError:
        print('You need to enter a number with a decimal point\nExample 6.58')
        continue

    date_time = datetime.now().strftime("%d/%m/%Y %H:%M:%S")

    num_rows = sht.api.UsedRange.Rows.Count
    if num_rows == 1:
        last_serial = 0
    else:
        cell = 'A' + str(num_rows)
        last_serial = sht.range(cell).value
        print(last_serial)
        print(type(last_serial))

    new_serial = int(last_serial) + 1

    if float(gross) < 6.25 or float(gross) > 7.80:
        print('Fail')
        pass_fail = 'Fail'
    else:
        pass_fail = 'Pass'

    next_row = 'A' + str(num_rows + 1)

    sht.range(next_row).value = [new_serial, date_time, gross, net, pass_fail]

wb.save()

The code isn't perfect but it gives you and idea of what I'm looking for. Is it possible to do this in LibreOffice on a Raspberry Pi?

MrRBM97
  • 169
  • 10
  • You don't need either Excel or Libre to read and write `xlsx` files. `xlwings` doesn't work with Excel files though, it uses COM Automation, a Windows technology, to remotely control Excel. That's *slooooow* and only used on desktop applications because it requires an open Excel instance. *Every* property or method call is a cross-process call. It's a *lot* faster to work with `xlsx` programmatically, using eg `xlsxwriter` or `openpyxl` – Panagiotis Kanavos Apr 27 '21 at 10:37
  • @PanagiotisKanavos But being slow is probably not a major concern for the OP. I think he is much more concerned with the value poping up in an already opened excel(like) document. – JohanL Apr 27 '21 at 10:40
  • @JohanL the code doesn't pop up anything. It generates and saves an Excel document. There's no reason to use Excel or Libre for this. It's actually *easier* to use `xlsxwriter` or `openpyxl`. – Panagiotis Kanavos Apr 27 '21 at 10:44
  • In any case, `xlwings` can't be used. COM Automation/OLE Automation/Microsoft Automation isn't available on any OS except Windows. And even on Windows, LibreOffice and OpenOffice use different APIs – Panagiotis Kanavos Apr 27 '21 at 10:45
  • The introductory [xlsxwriter tutorial](https://xlsxwriter.readthedocs.io/tutorial01.html) shows how easy it is to create an Excel file using just the library. There's no need to construct cell ranges with `next_row = 'A' + str(num_rows + 1)`, one can just use the index, eg `worksheet.write(row, 0, 'Total')`. Instead of storing dates as strings, one can [store the actual date and change the display style](https://xlsxwriter.readthedocs.io/working_with_dates_and_time.html) and use eg `worksheet.write_datetime('A1', date_time, date_format)` – Panagiotis Kanavos Apr 27 '21 at 10:50
  • It's also possible to save an entire Pandas dataframes directly to `xlsx` with [to_excel](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html). Pandas uses `openpyxl` or `xlsxwriter` to actually write the file – Panagiotis Kanavos Apr 27 '21 at 10:54
  • @PanagiotisKanavos JohanL is correct, the speed isn't a problem. I know I have to compromise on the speed to get the real-time "value popping up on" the document. On my PC this code pops up in an Excel window. I have and do use openpyxl but it doesn't allow the real-time element. I also know that xlwings doesn't work in anything but windows, I did state that in my question! – MrRBM97 Apr 27 '21 at 11:00
  • Libre and OpenOffice have a completely different automation API called UNO (Unified Network Objects). You can use it through the [PyUno package](https://wiki.openoffice.org/wiki/PyUNO_bridge). You have to start Calc in advance, and try to connect to it once it's started. Check the SO question [Using Python to access LibreOffice Calc using Uno](https://stackoverflow.com/questions/60663664/using-python-to-access-libreoffice-calc-using-uno) too. The [PyUno samples](https://wiki.openoffice.org/wiki/PyUNO_samples) can be confusing. – Panagiotis Kanavos Apr 27 '21 at 11:49
  • For the record: xlwings works on macOS, too, not just Windows. – Felix Zumstein Apr 27 '21 at 14:32

0 Answers0