For Advanced

Lesson 10/10

Excel Without Excel

Sometimes you need to process an Excel file, but you don’t have MS Office to open it. Instead of resorting to Microsoft’s suite alternatives such as LibreOffice or Google Docs, or online converters, you can use our Xlsx Addon, which allows performing some actions on spreadsheet data without having Excel installed. So, enable the addon in the Addons panel and try it!

The following script is a simple XLSX to CSV file converter to show the idea of the addon: the robot will download a sample Excel spreadsheet file excel.xlsx to a user Desktop, then will open it with the command and start processing. Row by row (the for loop with the ♥rowNo variable) and cell by cell in each row (the for loop with the ♥columnNo variable) the robot reads the values with the xlsx.getvalue command and adds them to the already read ones, putting a comma at the end of the new data (♥value = ♥value♥result,).

When a full row is read (the ♥columnNo variable in the for loop reaches 7), the ♥newline special variable is added to the comma-separated values of the row (♥value = ♥value‴♥newline‴), making a new line for the values that will be read from the next row.

After all rows were read, the content of the ♥value variable is written to an output.csv file on the Desktop.

♥value = ‴‴
♥file = ♥environment⟦USERPROFILE⟧\Desktop\excel.xlsx filename ♥file ♥file
for ♥rowNo from 1 to 11
  for ♥columnNo from 1 to 7
    xlsx.getvalue ♥rowNo colindex ♥columnNo
    ♥value = ♥value♥result,
  ♥value = ♥value‴♥newline‴
text.write ♥value filename output.csv

The code above has one limitation: the data range (number of columns and rows filled with data) must be known in order to set the appropriate counters in both for loops.