<-My Blog->

In this article, we gonna learn how to import and export excel and csv in node.js. 

1. Let's create a new express project using express generator.

npm i -g express-generator
express node-excel-csv --view=hbs

2. Create a folder config and inside this create a file database.js.

config/database.js

module.exports = { 
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD
}

3. Install dotenv npm package.

npm i dotenv

After Installation import dotenv in app.js

require('dotenv').config();

4. Create a .env file in the root and add these environment variables.

DB_HOST=localhost            // your database host
DB_DATABASE=node-excel-csv  // your database name
DB_USERNAME=root           // your database username 
DB_PASSWORD=              // your database password

5. Install sequelize and mysql2 npm packages.

npm i sequelize mysql2

6. Create a models folder and inside this create a connection.js and movie.model.js

models/connection.js

const { Sequelize } = require('sequelize');
const config = require('../config/database'); 

const sequelize = new Sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    operatorsAliases: 'false',
    logging: false
});  

module.exports = sequelize

models/movie.model.js

const { DataTypes } = require('sequelize');
const sequelize = require('./connection');

const Movie = sequelize.define('Movie', {
    movie: {
        type: DataTypes.STRING 
    },
    category: {
        type: DataTypes.STRING 
    },
    director: {
        type: DataTypes.STRING 
    },
    rating: {
        type: DataTypes.FLOAT(8, 1) 
    }
}, {
    underscored: true
}); 
module.exports = Movie;

Note:- Don't forget to create a movies table. https://github.com/ultimateakash/node-excel-csv/blob/master/node-excel-csv.sql

Above Schema Based on the import(excel or csv) file.

7. Install multer and uniqid npm packages.

npm i multer, uniqid

8. create a folder helpers and inside this folder create a file uploader.js

const multer = require('multer');
const uniqid = require('uniqid'); 
const path = require('path');

const storage = multer.diskStorage({
    destination: function(req, file, cb) {
        cb(null, 'storage/uploads/')
    },
    filename: function(req, file, cb) {
        cb(null, Date.now() + uniqid() + path.extname(file.originalname))
    }
})
const upload = multer({ storage: storage });

exports.upload = (field) => upload.single(field); 

9. Create a storage folder and inside this create uploads and outputs folder.

10. Install xlsx npm package.

npm i xlsx

xlsx package provides a bunch of functions for reading and writing CSV/Excel files.

Parsing functions:-

      XLSX.read(data, read_opts) attempts to parse data

      XLSX.readFile(filename, read_opts) attempts to read filename and parse.

Note:- you can pass raw option to false  if you want formatted data.(example - formatted date)

XLSX.read(data, { raw: false })
XLSX.readFile(filename, { raw: false })

Writing functions:-

      XLSX.write(wb, write_opts) attempts to write the workbook

      XLSX.writeFile(wb, filename, write_opts) attempts to write workbook

Utility Functions:-

      Constructing:-

            book_new creates an empty workbook

            book_append_sheet adds a worksheet to a workbook

       Importing:

           aoa_to_sheet converts an array of arrays of JS data to a worksheet

           json_to_sheet converts an array of JS objects to a worksheet

          sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.

          sheet_add_json adds an array of JS objects to an existing worksheet.

    Exporting:

          sheet_to_json converts a worksheet object to an array of JSON objects.

          sheet_to_csv generates delimiter-separated-values output.

Ref:- https://www.npmjs.com/package/xlsx

11. Create a folder controllers and inside this folder create movie.controller.js

controllers/movie.controller.js

const XLSX = require("xlsx"); 
const Movie = require("../models/movie.model")
const outputPath = 'storage/outputs' 

exports.index = async (req, res) => { 
    const movies = await Movie.findAll();
    return res.render('index', { movies });
}

exports.import = async (req, res) => { 
    const wb = XLSX.readFile(req.file.path); 
    const sheets = wb.SheetNames;
    
    if(sheets.length > 0) {
        const data = XLSX.utils.sheet_to_json(wb.Sheets[sheets[0]]);
        const movies = data.map(row => ({
            movie: row['Movie'],
            category: row['Category'],
            director: row['Director'],
            rating: row['Rating']
        }))
        await Movie.bulkCreate(movies); 
    }
    return res.redirect('/');
}

exports.export = async (req, res) => {
    const movies = await Movie.findAll({
        attributes: [
            'id', 
            'movie', 
            'category', 
            'director', 
            'rating'
        ],
        raw: true
    }); 

    const headings = [
        ['Id', 'Movie', 'Category', 'Director', 'Rating']
    ]; 

    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.json_to_sheet(movies, { 
        origin: 'A2', 
        skipHeader: true 
    });
    XLSX.utils.sheet_add_aoa(ws, headings); 
    XLSX.utils.book_append_sheet(wb, ws, 'Movies');

    const buffer = XLSX.write(wb, { bookType: 'csv', type: 'buffer' }); 
    res.attachment('movies.csv');

    return res.send(buffer);
}

Note:- In this article, I am using import file headers if your file doesn't have a headers row then you can use indexes.

You need to pass header option with sheet_to_json

const data = XLSX.utils.sheet_to_json(wb.Sheets[sheets[0]], { header: 1 });
const movies = data.map(row => ({
    movie: row[0],
    category: row[1],
    director: row[2],
    rating: row[3]
}))

In the above code you can change the extension based on the file you want to import or export. (.xlsx, .xls, .csv)

Note:- If want to store your exported file on the server you can use writeFile function.

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet(movies, { 
    origin: 'A2', 
    skipHeader: true 
});
XLSX.utils.sheet_add_aoa(ws, headings); 
XLSX.utils.book_append_sheet(wb, ws, 'Movies');

const filepath = `${outputPath}/movies.csv`;
XLSX.writeFile(wb, filepath); 

return res.download(filepath)

12. Create routes.

routes/index.js

const express = require('express');
const router = express.Router();
const movieController = require('../controllers/movie.controller');
const { upload } = require('../helpers/uploader');

router.get('/',                        movieController.index);
router.post('/import', upload('file'), movieController.import);
router.get('/export',                  movieController.export);

module.exports = router;

13. Open views/index.hbs and add the following code.

<main>
    <div class="row mb-2">
        <div class="col-sm-8 offset-2">
            <div class="row">
                <div class="col-md-6">
                    <form method="POST" action="/import" enctype="multipart/form-data"> 
                        <div class="input-group">
                            <div class="custom-file">
                                <input type="file" name="file" class="custom-file-input" id="inputGroupFile" required accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
                                <label class="custom-file-label" for="inputGroupFile">Choose file</label>
                            </div>
                            <div class="input-group-append">
                                <button type="submit" class="btn btn-primary float-right mr-2">Import <i class="fa fa-upload"></i></button>
                            </div>
                        </div>
                    </form>
                </div>
                <div class="col-md-6">
                    <a href="/export" class="btn btn-primary float-right {{#unless movies.length}} disabled {{/unless }}" role="button" aria-disabled="true">
                        Export <i class="fa fa-download"></i>
                    </a>
                </div>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-sm-8 offset-2">
            <table class="table">
                <thead>
                    <tr>
                        <th scope="col">Id</th>
                        <th scope="col">Movie</th>
                        <th scope="col">Category</th>
                        <th scope="col">Director</th>
                        <th scope="col">Rating</th>
                    </tr>
                </thead>
                <tbody>
                    {{#each movies}} 
                        <tr>
                            <th scope="row">{{ this.id }}</th>
                            <td>{{ this.movie }}</td>
                            <td>{{ this.category }}</td>
                            <td>{{ this.director }}</td>
                            <td><span class="badge bg-warning text-dark">{{ this.rating }}</span></td>
                        </tr>
                    {{else}}
                        <tr>
                            <td colspan="5" class="text-center">No Movies Found.</td>
                        </tr>
                    {{/each}}  
                </tbody>
            </table>
        </div>
    </div>
</main>

Note:- please checkout github repo - https://github.com/ultimateakash/node-excel-csv

14. Finally start the project.

npm start

Open http://localhost:3000 and try import and export.

Extras:- if you want to read a file without uploading then you can use multer.memoryStorage()

const multer = require('multer'); 
const upload = multer({ storage: multer.memoryStorage() });
exports.upload = (field) => upload.single(field);

with memoryStorage() you can access the file buffer.

const wb = XLSX.read(req.file.buffer); 
const sheets = wb.SheetNames;

 

Sample Excel CSV Files:- https://github.com/ultimateakash/node-excel-csv/tree/master/sample%20files

Checkout my full node-excel-csv example. https://github.com/ultimateakash/node-excel-csv

If you facing any issues. don't hesitate to comment below. I will be happy to help you.

Thanks.