Create and Download Excel files from JSON data

In DronahQ Studio we can create an Excel file of xlsx format and download the same from the JSON data. This JSON data can be obtained through different means such as connecting Databases, APIs, sheets, and more.

To understand this, we will perform three use cases, where we will get the data in JSON format and create an Excel file from it, and then download it with a single click.

  1. Download multiple rows from the table.
  2. Download directly from the database using the data store.
  3. Download directly from the database.

Prerequisite

Knowledge of JavaScript is a plus. This will help to better understand creating objects and working with JavaScript libraries.

Library used

Here I have used the SheetJs library, which is an Excel JavaScript library that enables us to create a workbook of Excel and convert JSON to a downloadable xlsx file.
To use this library first we need to import it to our studio.

  1. Create an app from the studio, Settings > Custom Script.

  1. Select Library Import and paste the file URL: https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js then Import. Once imported, save the script and it will be displayed on the same window.

Now, this library has been imported. All the modules and utilities related to our library can be accessed on DronaHQ Studio while writing JavaScript code.

Download multiple selected rows from the table

In this use case, we will get the data and then display it on our app in a table grid view. We will download multiple selected rows from the table in xlsx format.

  1. Get a Table grid and go to its properties and under display check Yes(with multi-select) of Checkbox.

checkbox

  1. Go to Bind data of table grid and select Connector and connect to the database. Here I am using MongoDB, one can use any other methods to connect data, like APIs, sheets, and more.
  2. Connect the binding keys to the table grid and then the data will be displayed.

  1. Place a Button from Controls under Featured section to your app. This button will be used to down the selected data from the table grid in xlsx format.

  1. Go to Actions of the button and click on button_click. This will open action flow. Add an on-screen action of JS Code.
  2. Add input parameters. These input parameters will be used to get the data from the selected row of the table and provide it with a variable name that can be accessed in our JavaScript code editor.

  1. In the JavaScript code editor write a code using our imported SheetJs – xlsx min.js to convert JSON into xlsx worksheet and download it.

Code:

let data = []; 
  
for(let i = 0; i < ids.length; i++) { 
data.push({ 
id: ids[i], 
name: names[i], 
location: locations[i], 
injuryType: injurytypes[i], 
action: actions[i] 
}) 
} 
filename='reports.xlsx'; 
       
        var ws = XLSX.utils.json_to_sheet(data); 
        var wb = XLSX.utils.book_new(); 
        XLSX.utils.book_append_sheet(wb, ws, "People"); 
        XLSX.writeFile(wb,filename); 

Code explanation:

  • We created an empty object of data to store the details of variables passed in the function which are ids, names, locations, injurytypes and actions.
  • Since there are multiple rows selected, the variables will get multiple data too, so we will iterate each set of arrays which has details rows wise by adding a loop from 0 to no. Of ids (length).
  • Next, we have a filename variable that decides the name of the file and the format of the file. report.xlsx signals XLSX export.
  • Next, we created a variable ws with json_to_sheet(data). The json_to_sheet utility function walks an array of JS objects in order, to generate a worksheet object. By default, it will generate a header row and one row per object in the array. The data is the JS objected created with our table details.
  • Next, we created a variable wb with book_new(). The book_new utility function creates an empty workbook with no worksheets. Spreadsheet software generally requires at least one worksheet and enforces the requirement in the user interface. This library enforces the requirement at write time, throwing errors if an empty workbook is passed to write functions.
  • Now that we have created a worksheet and workbook, we now need to append this to a sheet. The book_append_sheet utility function appends a worksheet to the workbook. We used book_append_sheet(wb, ws, “People”), here the third argument specifies the desired worksheet name. Multiple worksheets can be added to a workbook by calling the function multiple times.
  • Finally, we used writeFilea(wb, filename). Now that we have appended our worksheet to our work book the writeFile method packages the data and attempts to save the new file. It takes care of packaging to write wb to filename and attempting a local download.

  1. Test the script, a download pop will occur on the screen. It means it is working. Click on continue and finish.

Now we are done with downloading multiple selected rows from the table in xlsx format.

Download directly from the database using the data store

In this use case, we will use Data Store to store the data from the connector and then a button that will download the whole data directly after converting it to xlsx file.

  1. Drag and drop Data Store from Controls under Advance section.

  1. Go to Bind data of data store and select Connector and connect to the database. Here I am using Supabase, one can use any other methods to connect data, like APIs, sheet and more.
  2. Connect the binding keys.

  1. Place a Button from Controls under Featured section to your app. This button will be used to down the selected data from the table grid in xlsx format.

  1. Go to Actions of the button and click on button_click. This will open action flow. Add an on-screen action of JS Code.
  2. Add input parameters. These input parameters will be used to get the data from the Data Store and provide it a variable name that can be accessed in our JavaScript code editor.

  1. In the JavaScript code editor write a code using our imported SheetJs – xlsx min.js to convert JSON into xlsx worksheet and download it.

Code:
let data = [];

for(let i = 0; i < id.length; i++) { 
data.push({ 
id: id[i], 
Position: pos[i], 
Location: loc[i], 
  
}) 
} 
filename='reports.xlsx'; 
       
        var ws = XLSX.utils.json_to_sheet(data); 
        var wb = XLSX.utils.book_new(); 
        XLSX.utils.book_append_sheet(wb, ws, "People"); 
        XLSX.writeFile(wb,filename); 

Code explanation:

  • We created an empty object of data to store the details of variables passed in the function which are id, pos, and loc.
  • Since there are multiple data present in the data store from the connector, therefore the variables will get multiple data too, we will iterate each set of arrays which has details rows wise by adding a loop from 0 to no. Of ids (length).
  • Next, we have a filename variable that decides the name of the file and the format of the file. report.xlsx signals XLSX export.
  • Next, we created a variable ws with json_to_sheet(data). The json_to_sheet utility function walks an array of JS objects in order, to generate a worksheet object. By default, it will generate a header row and one row per object in the array. The data is the JS objected created with our table details.
  • Next, we created a variable wb with book_new(). The book_new utility function creates an empty workbook with no worksheets. Spreadsheet software generally requires at least one worksheet and *nforces the requirement in the user interface. This library enforces the requirement at write time, throwing errors if an empty workbook is passed to write functions.
  • Now that we have created a worksheet and workbook, we now need to append this to a sheet. The book_append_sheet utility function appends a worksheet to the workbook. We used *ook_append_sheet(wb, ws, “People”), here the third argument specifies the desired worksheet name. Multiple worksheets can be added to a workbook by calling the function multiple times.
  • Finally, we used writeFilea(wb, filename). Now that we have appended our worksheet to our work book the writeFile method packages the data and attempts to save the new file. It takes care of packaging to write wb to filename and attempting a local download.

  1. Test the script, a download pop will occur on the screen. It means it is working. Click on continue and finish.

Now we are done with downloading data directly from the database in xlsx format.

Download directly from the database

In this use case, we will download the data from the database directly without using any UI templates such as table grid or data store. We will simply put up a button that will download all the details with a click.

  1. Place a Button from Controls under Featured section to your app. This button will be used to put action flows.

  1. Go to Actions of the button and click on button_click. This will open action flow.
  2. First select go to server-side action and select the database which you want to connect to download its data. Select the query to get the data.

  1. Save the output in a variable, so that it can be used for referencing the data later on.

  1. Next, we will add another action on the success of our previous action. This will be an on-screen action of JS Code.
  2. Add input parameter. This input parameter will be the same as the variable which we set during our server-side database connector. provide it with a variable name that can be accessed in our JavaScript code editor.

  1. In the JavaScript code editor write a code using our imported SheetJs – xlsx min.js to convert JSON into xlsx worksheet and download it.

Code:
let data = []

supadata.result.rows.forEach(value =>{ 
  data.push({ 
    id: value.id, 
    Position: value.position, 
    Location: value.location 
  }) 
}) 
filename='reports.xlsx'; 
       
        var ws = XLSX.utils.json_to_sheet(data); 
        var wb = XLSX.utils.book_new(); 
        XLSX.utils.book_append_sheet(wb, ws, "People"); 
        XLSX.writeFile(wb,filename); 

Code explanation:

  • We created an empty object of data to store the details of variable supadata passed in the function which has id, position, and location in its properties and we will save each of them in the data.
  • Since there are multiple rows of data in the variable, there will be multiple properties too, so we will iterate each set of arrays which has details rows wise by forEach() funtion.
  • Next, we have a filename variable that decides the name of the file and the format of the file. report.xlsx signals XLSX export.
  • Next, we created a variable ws with json_to_sheet(data). The json_to_sheet utility function walks an array of JS objects in order, to generate a worksheet object. By default, it will generate a header row and one row per object in the array. The data is the JS objected created with our table details.
  • Next, we created a variable wb with book_new(). The book_new utility function creates an empty workbook with no worksheets. Spreadsheet software generally requires at least one worksheet and enforces the requirement in the user interface. This library enforces the requirement at write time, throwing errors if an empty workbook is passed to write functions.
  • Now that we have created a worksheet and workbook, we now need to append this to a sheet. The book_append_sheet utility function appends a worksheet to the workbook. We used book_append_sheet(wb, ws, “People”), here the third argument specifies the desired worksheet name. Multiple worksheets can be added to a workbook by calling the function multiple times.
  • Finally, we used writeFilea(wb, filename). Now that we have appended our worksheet to our work book the writeFile method packages the data and attempts to save the new file. It takes care of packaging to write wb to filename and attempting a local download.

  1. Test the script, a download pop will occur on the screen. It means it is working. Click on continue and finish.

Now we are done with downloading JSON in xlsx format directly from the database using action flow.

NOTE: This will work on Web & Public apps and not in mobile apps - android & iOS.