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.