How-to Create an Excel File in Node.js with Axios and Excel4Node
Most of the time you most-probably want to use the data of an API on your website or in your application. Though, sometimes it might be that you are willing to download the data you got in an easy-to-read Excel (.xlsx) file. Luckily for us, Node is providing this option with the Excel4Node package. Let’s see how this works!
For this tutorial we will download old weather data, which is as a sample and available without an API key, though please feel free to use your own data if you want to. The data is encoded in JSON and can be found here. You might want to create a free account at OpenWeatherMap, so you can get real time data, rather than the older sample data I am using in this tutorial.
Downloading the Data via Axios
In order to download the data first we will use the Axios library, which is a great promise based HTTP client of which you can find more information here. To do so, create a new folder, a JavaScript file and install the discussed two packages via your terminal with the code below.
npm install axios
npm install excel4nodeAfter you installed the two libraries, we can start the actual coding. The following code should download the necessary data, so preferably type (in order to learn the syntax), otherwise copy this code in and I will explain you per line what is happening.
const axios = require("axios");
const xl = require('excel4node');const API_SAMPLE_URL = ‘https://samples.openweathermap.org/data/2.5/forecast/hourly?id=524901&appid=b6907d289e10d714a6e88b30761fae22'const getWeatherData = async () => {
try {
return await axios.get(API_SAMPLE_URL)
} catch (error) {
console.error(error)
}
}const processWeatherData = async () => {
const weatherData = await getWeatherData() if (weatherData.data) {
let API_RESPONSE = weatherData.data.list
console.log(API_RESPONSE)
}
}processWeatherData()
Running this code will give you an array with the weather data of Moscow on the 31st of March 2019 during different moment of the day. Let me explain you how this works.
Line 1–2
Here we are simply including the two packages we downloaded into the project via npm before. Although we will not be using the Excel4Node right away, you will get the point that we will need this later on.
Line 4
This line is declaring a constant containing the URL of where to get the actual data. This is mainly done to prevent any typos in case the project would get larger and we would have to copy the URL in multiple places. Now we can just use this constant and prevent any mistakes made there.
Line 5–11
A regular arrow function with the in ES8 introduced async/await methods included. Within the function there is a try/catch statement, which is ideal for this kind of situations, because in case of any errors, your programme does not break completely, but prints the error in your terminal.
In case Axios is able to get the data from the URL provided as parameter in the axios.get method on line seven, the response will be returned, so we can use it later on. Otherwise, the function will run the catch statement and log the error.
Line 13–20
Another arrow function with async/await methods has been placed here. Since the await keyword has been placed here, the code will wait for the JSON data to be downloaded before running any other code.
If completed, we check whether the data we want is included. The original response comes with a lot of metadata, which we will not use for this tutorial. Therefore I limit the API_RESPONSE constant to a limited version and print this to the console. This constant is an array with the weather data (all the dictionaries), of which one example (first instance of the array) is shown below.
{
dt: 1553709600,
main: {
temp: 272.09,
temp_min: 271.358,
temp_max: 272.09,
pressure: 1018.01,
sea_level: 1018.01,
grnd_level: 997.153,
humidity: 100,
temp_kf: 0.73
},
weather: [{
id: 803,
main: ‘Clouds’,
description: ‘broken clouds’,
icon: ‘04n’
}],
clouds: { all: 60 },
wind: { speed: 3.25, deg: 287.822 },
sys: { pod: ’n’ },
dt_txt: ‘2019–03–27 18:00:00’
}Creating the Excel file with Excel4Node
Now we have collected this data and stored it in an array, we can easily create an Excel (.xlsx) file out of this for you to be distributed.
In order to so I have only added the bold code below, which helps me to create the Excel file containing the temp value. Despite the fact that I have only included one value for now, feel free to add as many variables in the excel file as you wish.
const API_SAMPLE_URL = ‘https://samples.openweathermap.org/data/2.5/forecast/hourly?id=524901&appid=b6907d289e10d714a6e88b30761fae22'const getWeatherData = async () => {
try {
return await axios.get(API_SAMPLE_URL)
} catch (error) {
console.error(error)
}
}const processWeatherData = async () => {
const weatherData = await getWeatherData() if (weatherData.data) {
let API_RESPONSE = weatherData.data.list var wb = new xl.Workbook();
var ws = wb.addWorksheet(‘Weather Data’); var style = wb.createStyle({
font: {
color: ‘#000000’,
size: 10,
}
}); ws.cell(1, 1)
.string(Object.keys(API_RESPONSE[0].main)[0])
.style(style); for (a = 0; a < API_RESPONSE.length; a++) {
ws.cell(a + 2, 1)
.number(API_RESPONSE[a].main.temp)
.style(style);
} wb.write(‘WeatherData.xlsx’);
}
}processWeatherData()
Let’s discuss the code above per line to see what is happening.
Line 20–21
Just like you would open Excel and open a new workbook, the same is happening here in code. Line 20 is creating a new workbook, extended by line 21, which creates a new worksheet named Weather Data. Note that everything else added to the workbook will be added to either the wb or ws value (depending on what it is), so in the end the wb (short for workbook) can be created.
Line 23–28
The workbook needs to now what the styling of the cells will be. For now, I have only set the font to black and its size to ten, though you could add here whatever you like. Check out all the possibilities over here.
Line 30–32
Here we are starting to fill the cells with the actual data. Above the temperatures, which we will enter right after, I want a title showing the user that these are temperatures.
For this I get the API_RESPONSE constant, get the first element, go into the main (which is a key in the dictionary) part, get all the key values and take the first instance (0). Excel4Node has the cell method, which can be used to place the data. In this case I am placing the title temp as a string in cell 1, 1 and use the style we have set before.
Line 34–38
With the help of the for in-range loop we will enter the temperatures below the just entered title. The loop will go over all the temperature data and place the values in the cells, with the cell method and styling used before.
Line 39
This final function is combining all the information we have entered and creating the actual file for you. Without this function nothing would be created or done. With the parameter of this function you can name your file to whatever you like.
If you run the code, you should find the Excel file in the same folder as your code.
