Excel File Operations

Setup

```bash npm install xlsx ```

Reading Excel

```javascript 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

```javascript 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

```javascript 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.