Import and export XLSX files in web application

Import and export XLSX files in web application

Support for XLSX files is very common nowadays in many applications. In this lab, we will learn how to import and export XLSX files in Vue and Express. We will use the xlsx package to import XLSX file. The xlsx package is a Node.js module that provides a simple interface to read and write XLSX files.

Importing XLSX files in Vue

We will begin with the front-end application first. Let’s install xlsx as development dependency in our Vue app.

Setup

npm install xlsx --save-dev

Create new file input component

Let’s create a new component by duplicating FileInput.vue and name it as BufferFileInput.vue`.

Changing the following line from:

reader.readAsDataURL(f);

to

reader.readAsArrayBuffer(f);

This will make the FileReader return a Buffer instead of file content encoded in base64 string.

Adding component to HomeView.vue

Please add the followings in <script setup>:

// ...
// add under import statements
import BufferFileInput from '../components/BufferFileInput.vue';
import { utils, read } from 'xlsx';

//... some existing functions

const excelData = ref([]);
const importExcel = (files) => {
  if (files.length > 0) {
    const workbook = read(files[0]);
    console.log(workbook.SheetNames[0])
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    excelData.value = Array.from(utils.sheet_to_json(worksheet, {raw:false, header: 1}));
    console.log(excelData.value);
  }
}

const deleteRow = (rowIndex) => {
  excelData.value.splice(rowIndex, 1);
}

<BufferFileInput @change="importExcel" accept=".xlsx" />
<table class="table table-striped">
  <tr>
    <th v-for="item in excelData[0]"></th>
  </tr>
  <tr v-for="(item, rowIndex) in excelData.slice(1)">
    <td v-for="(val, colIndex) in item">
      <input type="text" v-model="excelData[rowIndex+1][colIndex]" />
    </td>
    <td><button type="button" @click="deleteRow(rowIndex+1)">X</button></td>
  </tr>
</table>

After selecting an Excel file in the file input, we will see a console message which output all data in the xlsx file you selected.

Upload to server with fetch

After that, we may try to upload those data to the Express backend server. Please add the followings to HellowView.vue.

const uploadTable = async () => {
  let response = await fetch('/api/import',{
    method: 'post',
    headers: {
      'Content-Type':'application/json'
    },
    body: JSON.stringify(excelData.value)
  })

  if (response.ok) {
    excelData.value = []
    alert('data import success.')
  }
}

Wrapping the table using a form with submit button.

<form @submit.prevent="uploadTable">
	<!-- ... -->
	<input type="submit" value="Save" />
</form

Construct a route handler function in routes/index.js of the Express application

const { MongoClient } = require("mongodb");
// Replace the uri string with your connection string.
const uri = "mongodb+srv://<username>:<password>@<endpoint>.mongodb.net/?retryWrites=true&w=majority";
const client = new MongoClient(uri);
const db = client.db('bookingDB');

router.post('/api/import', async function(req, res, next) {
  let rawData = req.body || [];
  let data = rawData.slice(1).map(d => {
    let i = 0;
    return (rawData[0] || []).reduce((a, b) => {
      a[b.replace(/\n/g, '').replace(/\r/g, '')] = (d[i++] || '')
      return a
    }, {})
  })

  let result = await db.collection('volunteers').insertMany(data);

  return res.json(data);
});

You may need to add mongodb to your Express project

npm install --save mongodb

Export XLSX file from databas

const XLSX = require('xlsx')
router.get('/api/export.xlsx', async function(req, res, next) {
  const workbook = XLSX.utils.book_new();
  let volunteers = await db.collection('volunteers').find({}).toArray();

  XLSX.utils.book_append_sheet(workbook, XLSX.utils.json_to_sheet(volunteers))
  res.setHeader('Content-Type', 'application/vnd.ms-excelapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  const buf = XLSX.write(workbook, {type: "buffer", bookType: "xlsx"});
  return res.send(buf)
});

You will need to install xlsx package on the express server.

npm isntall --save xlsx