Author: John Au-Yeung

Sometimes, we want to create an Excel File with Node.js.

In this article, we’ll look at how to create an Excel File with Node.js.

How to create an Excel File with Node.js?

To create an Excel File with Node.js, we can use the excel4node package.

To install it, we run

npm i excel4node

Then we use it by writing

const excel = require('excel4node');

const workbook = new excel.Workbook();
const worksheet = workbook.addWorksheet('Sheet 1');
const worksheet2 = workbook.addWorksheet('Sheet 2');

const style = workbook.createStyle({
  font: {
    color: '#FF0800',
    size: 12
  },
  numberFormat: '$#,##0.00; ($#,##0.00); -'
});

worksheet.cell(1, 1).number(100).style(style);
worksheet.cell(1, 2).number(200).style(style);
worksheet.cell(1, 3).formula('A1 + B1').style(style);
worksheet.cell(2, 1).string('string').style(style);
worksheet.cell(3, 1).bool(true).style(style).style({
  font: {
    size: 14
  }
});
workbook.write('Excel.xlsx');

to create a new workbook with

const workbook = new excel.Workbook();

Then we create 2 worksheets with

const worksheet = workbook.addWorksheet('Sheet 1');
const worksheet2 = workbook.addWorksheet('Sheet 2');

Then we set the font styles of the cells with

const style = workbook.createStyle({
  font: {
    color: '#FF0800',
    size: 12
  },
  numberFormat: '$#,##0.00; ($#,##0.00); -'
});

Next, we add some content to the cells with

worksheet.cell(1, 1).number(100).style(style);
worksheet.cell(1, 2).number(200).style(style);
worksheet.cell(1, 3).formula('A1 + B1').style(style);
worksheet.cell(2, 1).string('string').style(style);
worksheet.cell(3, 1).bool(true).style(style).style({
  font: {
    size: 14
  }
});

Cell A1 is 1, 1.

And then we save the spreadsheet with

workbook.write('Excel.xlsx');

Conclusion

To create an Excel File with Node.js, we can use the excel4node package.

Sometimes, we want to select where in array of _id with Node.js MongoDB.

In this article, we’ll look at how to select where in array of _id with Node.js MongoDB.

How to select where in array of _id with Node.js MongoDB?

To select where in array of _id with Node.js MongoDB, we can use the collection find method with the $in operator.

For instance, we write

db.collection.find({
  _id: {
    $in: [1, 2, 3, 4]
  }
});

to call find with the _id property set to an object with $in set to [1, 2, 3, 4] to query for entries with _id 1, 2, 3, or 4.

Conclusion

To select where in array of _id with Node.js MongoDB, we can use the collection find method with the $in operator.

Sometimes, we want to make a HTTPS POST in Node.js without any third party module.

In this article, we’ll look at how to make a HTTPS POST in Node.js without any third party module.

How to make a HTTPS POST in Node.js without any third party module?

To make a HTTPS POST in Node.js without any third party module, we can use the https.request method.

For instance, we write

const https = require('https');

const postData = JSON.stringify({
  'msg': 'Hello World!'
});

const options = {
  hostname: 'jsonplaceholder.com',
  port: 443,
  path: '/post',
  method: 'POST',
  headers: {
    'Content-Type': 'application/x-www-form-urlencoded',
    'Content-Length': postData.length
  }
};

const req = https.request(options, (res) => {
  console.log('statusCode:', res.statusCode);
  console.log('headers:', res.headers);

  res.on('data', (d) => {
    process.stdout.write(d);
  });
});

req.on('error', (e) => {
  console.error(e);
});

req.write(postData);
req.end();

to call https.request with the options for the request, which includes the request URL, method, and headers.

We get the response from the callback we pass into https.request as the 2nd argument.

And we pass in the request body by calling the req.write.

res.on lets us listen to the response by listening to the 'data' event.

And the 'error' event is emitted when there’s an error.

Conclusion

To make a HTTPS POST in Node.js without any third party module, we can use the https.request method.

Sometimes, we want to fix Node.js dotenv isn’t loading environment variables.

In this article, we’ll look at how to fix Node.js dotenv isn’t loading environment variables.

How to fix Node.js dotenv isn’t loading environment variables?

To fix Node.js dotenv isn’t loading environment variables, we can call config with an object with path set to the path of the config file with the environment variables.

For instance, we write

const path = require('path')
require('dotenv').config({
  path: path.resolve(__dirname, '../.env')
})

to call config with an object with path set to the path of the .env file.

As a result, the .env file’s environment variables will be loaded into process.env.

Conclusion

To fix Node.js dotenv isn’t loading environment variables, we can call config with an object with path set to the path of the config file with the environment variables.

Sometimes, we want to make a query with OR condition with Node.js Sequelize.

In this article, we’ll look at how to make a query with OR condition with Node.js Sequelize.

How to make a query with OR condition with Node.js Sequelize?

To make a query with OR condition with Node.js Sequelize, we can use Op.or as the key.

For instance, w write

const {
  Op
} = require('Sequelize');

const r = await to(Student.findAll({
  where: {
    LastName: "Doe",
    FirstName: {
      [Op.or]: ["John", "Jane"]
    },
    Age: {
      [Op.between]: [18, 24]
    }
  }
}));

to call findAll with the where property set to an object with the fields we’re filtering by.

And we use the Op.or property set to an array of values to search for entries with FirstName set to 'John' or 'Jane'.

We use Op.between to search for entries with Age between 18 and 24.

Each property in where will be joined together with OR.

Conclusion

To make a query with OR condition with Node.js Sequelize, we can use Op.or as the key.