Excellion - Read and Write Excel Files

FREEMIUM

By Lorenzo Sinisi | Updated a month ago | Data

Health Check

N/A

Back to All Tutorials (1)

How to turn your excel files into business logic accessible via REST APIs

Thinking of turning an excel file into code? Use this instead!

Excellion service that lets you manipulate Excel files via REST APIs.

It is a tool designed for developers where you can upload, write and read excel files on the fly.

There is only one API endpoint which is /api/changeset and it accepts the format form-data (as you can see in the example below).

If you get a 401 double-check the existence of all the cell numbers and sheet names.

On a very high level this is what happens when you make a call:

HTTP call is initiated
Payload: upload the file (under the name “file”) and changes + selects are specified
The server will store a temporary copy of the file, write it, recalculate all formulas, read it again and return the result
The file gets deleted from the server

Download the example file ?

Download this Excel file (does nothing but the sum of some cells, check it out): https://docs.google.com/spreadsheets/d/1WuVi7F1PDpH69JWRa9bDxTHT1JaA5w43/edit?usp=sharing&ouid=113909502023297765536&rtpof=true&sd=true

Test the APIs ?

Get the path of your downloaded file and replace both the TOKEN and the PATH of your file, then try to run this code:

curl --location --request POST 'RAPID_API_ENDPOINT' \
--form 'changes="[{\"cell\": \"A1\", \"sheet\": \"Sheet 1\", \"value\": 4}]"' \
--form 'select="[{\"cell\": \"A7\", \"sheet\": \"Sheet 1\"}]"' \
--form 'file=@"/Yourmachine/yourusername/change/this/excel-example.xlsx"' // download this from the instructions above

Change the Excel file columns ?

In order to change any column in the Excel file, you must know which Sheet you want to operate on, then you can describe the changes to your file in the following format:

[{"cell": CELL_NAME (i.e. A11), "sheet": SHEET_NAME (i.e. "Sheet 1"), "value": anything as long as it works in excel}]

Read the Excel file columns ?

The same exact format goes for the field “select” of the form, you need to specify cell name and sheet (see the curl example above).

[{"cell": CELL_NAME (i.e. A11), "sheet": SHEET_NAME (i.e. "Sheet 1")}]

Example:

In the following example we are changing the cell A1 of the sheet “Sheet 1” and setting the value to the number 4:

var axios = require('axios');
var FormData = require('form-data');
var fs = require('fs');
var data = new FormData();
data.append('changes', '[{"cell": "A1", "sheet": "Sheet 1", "value": 4}]'); // we want to change only this cell
data.append('select', '[{"cell": "A7", "sheet": "Sheet 1"}]'); // we want to read this value then
data.append('file', fs.createReadStream('/Users/yourusername/Desktop/excel-example.xlsx')); // <-- change this

var config = {
  method: 'post',
  url: 'RAPID_API_ENDPOINT',
  headers: { 
    ...data.getHeaders()
  },
  data : data
};

axios(config)
.then(function (response) {
  console.log(JSON.stringify(response.data));
})
.catch(function (error) {
  console.log(error);
});

As you can also see, the select field contains an array of cells that we want to return as result of our changes.

The result of such call will be something like the following:

{
    "response": {
        "changes": {
            "changes": [
                {
                    "cell": "A1",
                    "sheet": "Sheet 1",
                    "value": 4
                }
            ],
            "file": "/tmp/plug-1631/multipart-1631468662-760532740890383-2",
            "select": [
                {
                    "cell": "A7",
                    "sheet": "Sheet 1"
                }
            ]
        },
        "file": "/tmp/plug-1631/multipart-1631468662-760532740890383-2",
        "results": {
            "Sheet 1!A7": 10
        }
    }
}

There is no limit to the amount of cells that you read and write.

NOTE: if you need any support don’t wait a second and request it, we will be more than happy to help you!