Excel File Operations

Setup

npm install xlsx

Reading Excel

const XLSX = require('xlsx');

const workbook = XLSX.readFile('data.xlsx');

const sheetNames = workbook.SheetNames;

const worksheet = workbook.Sheets[sheetNames[0]];

const data = XLSX.utils.sheet_to_json(worksheet);
console.log(data);

Writing Excel

const data = [
  { Name: 'John', Age: 30, City: 'New York' },
  { Name: 'Jane', Age: 25, City: 'London' }
];

const workbook = XLSX.utils.book_new();

const worksheet = XLSX.utils.json_to_sheet(data);

XLSX.utils.book_append_sheet(workbook, worksheet, 'Users');

XLSX.writeFile(workbook, 'users.xlsx');

Export to Excel

app.get('/export/users', async (req, res) => {
  const users = await User.find().lean();
  
  const workbook = XLSX.utils.book_new();
  const worksheet = XLSX.utils.json_to_sheet(users);
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Users');
  
  const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
  
  res.setHeader('Content-Disposition', 'attachment; filename=users.xlsx');
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.send(buffer);
});

Key Takeaway

Use xlsx for Excel operations. Export database data. Import Excel data to database.