Using exceljs for Excel Import and Export in Node.js and the Browser

Excel is a common tool for data handling, and many backend management systems need to import data from or export data to Excel files.

In Node.js we use the exceljs package, which has over 300,000 weekly downloads, to read an Excel file, iterate through workbooks, worksheets, rows and cells, and extract the data.

Example code to set up a project:

mkdir exceljs-test
cd exceljs-test
npm init -y
npm install --save exceljs

Reading a file:

const { Workbook } = require('exceljs');
(async function main(){
  const workbook = new Workbook();
  const wb = await workbook.xlsx.readFile('./data.xlsx');
  wb.eachSheet((sheet, i)=>{
    console.log('工作表' + i);
    sheet.eachRow((row, j)=>{
      const rowData = [];
      row.eachCell((cell, k)=>{ rowData.push(cell.value); });
      console.log('行' + j, rowData);
    });
  });
})();

The hierarchy is workbook → worksheet → row → cell ; traversing each level yields all data.

exceljs also provides worksheet.getSheetValues() for quick data extraction without manual loops.

To generate an Excel file we create a workbook, add a worksheet, define columns, add rows, apply styles, and write the file:

const { Workbook } = require('exceljs');
(async function main(){
  const workbook = new Workbook();
  const ws = workbook.addWorksheet('guang111');
  ws.columns = [
    {header:'ID', key:'id', width:20},
    {header:'姓名', key:'name', width:30},
    {header:'出生日期', key:'birthday', width:30},
    {header:'手机号', key:'phone', width:50}
  ];
  const data = [
    {id:1, name:'光光', birthday:new Date('1994-07-07'), phone:'13255555555'},
    {id:2, name:'东东', birthday:new Date('1994-04-14'), phone:'13222222222'},
    {id:3, name:'小刚', birthday:new Date('1995-08-08'), phone:'13211111111'}
  ];
  ws.addRows(data);
  ws.eachRow((row, rowIndex)=>{
    row.eachCell(cell=>{
      if(rowIndex===1){
        cell.style = {font:{size:10,bold:true,color:{argb:'ffffff'}},alignment:{vertical:'middle',horizontal:'center'},fill:{type:'pattern',pattern:'solid',fgColor:{argb:'000000'}},border:{top:{style:'dashed',color:{argb:'0000ff'}},left:{style:'dashed',color:{argb:'0000ff'}},bottom:{style:'dashed',color:{argb:'0000ff'}},right:{style:'dashed',color:{argb:'0000ff'}}}};
      } else {
        cell.style = {font:{size:10,bold:true},alignment:{vertical:'middle',horizontal:'left'},border:{top:{style:'dashed',color:{argb:'0000ff'}},left:{style:'dashed',color:{argb:'0000ff'}},bottom:{style:'dashed',color:{argb:'0000ff'}},right:{style:'dashed',color:{argb:'0000ff'}}}};
      }
    });
  });
  await workbook.xlsx.writeFile('./data2.xlsx');
})();

In the browser we load exceljs from a CDN, use an <input type="file"> to select a file, call workbook.xlsx.load(file) to read it, and workbook.xlsx.writeBuffer() to generate a Blob that can be downloaded via an <a> element with the download attribute.

function download(arrayBuffer){
  const link = document.createElement('a');
  const blob = new Blob([arrayBuffer]);
  const url = URL.createObjectURL(blob);
  link.href = url;
  link.download = 'guang.xlsx';
  document.body.appendChild(link);
  link.click();
  link.remove();
}

Running the browser example shows that the same data can be parsed and exported directly in the client, completing a full Excel import/export workflow for both Node.js and browser environments.