Skip to content
Advertisement

xlwings alternative on Linux

I have a small Python script with the purpose of updating certain cells of an Excel sheet stored on Onedrive. In particular, the Excel workbook contains a price list in one sheet, a list of variables (e.g., currency exchange rate) that are to be updated automatically via Python in the second sheet, and a history of currency exchange rates in the 3rd (also to be edited via Python). I want to let a Raspberry Pi run and execute the script at regular hours and thereby update the field that contains the currency exchange rate from Rand to Euro and to insert the current value to a history of currency exchange rates. The script should work without causing errors in case the Excel sheet is open on other devices etc (multiple users should be able to read and edit the file). On Windows, I made it work like this:

from forex_python.converter import CurrencyRates
import xlwings as xw
from datetime import datetime

# get date and time
now = datetime.now()
current_date = now.strftime("%Y-%m-%d")
current_time = now.strftime("%H:%M:%S")
# Get current Rand to Euro conversion rate
rates = CurrencyRates()
RtoEUR = rates.get_rate("ZAR", "EUR")
# edit workbook
exl_app = xw.App(visible = False)
wb = xw.Book("/mnt/usb/LELE_expenses.xlsx")
## update currency exch. rate
sht0 = wb.sheets["Auto_upd_vals"]
sht0.range("C2").value = RtoEUR
sht1 = wb.sheets["Hist"]
last = sht1.range("B" + str(wb.sheets[0].cells.last_cell.row)) 
    .end("up").row
## save current date, time and currency exch. rate
sht1.range("A" + str(last + 1)).value = current_date
sht1.range("B" + str(last + 1)).value = current_time
sht1.range("C" + str(last + 1)).value = RtoEUR
wb.save()
wb.close()
exl_app.quit()

Unfortunately, it is not possible to install xlwings on Raspbian. Are there any alternatives to make this happen?

Errors so far:

pip3 install xlwings results in “xlwings requires an installation of Excel and therefore only works on Windows and macOS…” Running export INSTALL_ON_LINUX=1 enables installation, but the module cannot be imported. Presumably because it still doesn’t have Excel.

Advertisement

Answer

Hm, I guess this was easier than I expected:

from forex_python.converter import CurrencyRates
from openpyxl import Workbook, load_workbook
from datetime import datetime

# get date and time
now = datetime.now()
current_date = now.strftime("%Y-%m-%d")
current_time = now.strftime("%H:%M:%S")
# Get current Rand to Euro conversion rate
rates = CurrencyRates()
RtoEUR = rates.get_rate("ZAR", "EUR")
# edit workbook
wb = load_workbook("/mnt/usb/LELE_expenses.xlsx")
## update currency exchange rate
sht0 = wb["Auto_upd_vals"]
sht0["C2"].value = RtoEUR
# append currency exchange rate history
sht1 = wb["Hist"]
sht1.append([current_date, current_time, RtoEUR])
wb.save("/mnt/usb/LELE_expenses.xlsx")
wb.close()

doesn’t require Excel. I am not sure if it will cause any problems, though.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement