node.js Node exceljs 读取文件

Node exceljs reading file

提问by Marc Rasmussen

So according to the offical documentation i should be able to read an excel document using:

因此，根据官方文档，我应该能够使用以下方法读取 excel 文档：

    // read from a file 
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
    .then(function() {
        // use workbook 
    });

// pipe from stream 
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());

I have the following document:

我有以下文件：

What i need to do is basicly to load each row into an object:

我需要做的基本上是将每一行加载到一个对象中：

var excelObject = {competence1: '', competence2: ''}

And then save it into an array.

然后将其保存到数组中。

However the documentation doesnt give me more on how i might read from this file. It uses a variable called streamhowever this variable is not explained anywhere.

然而，文档并没有给我更多关于我如何从这个文件中读取的信息。它使用一个名为的变量，stream但是在任何地方都没有解释这个变量。

Does anyone know the plugin and know how i might achieve my goal?

有谁知道这个插件并知道我如何实现我的目标？

回答by Diogo Cardoso

var workbook = new Excel.Workbook(); 
workbook.xlsx.readFile(filename)
    .then(function() {
        var worksheet = workbook.getWorksheet(sheet);
        worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
          console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
        });
    });

回答by Statyan

And in case you are using file as ArrayBuffer (for example file was read on client with FileReader.readAsArrayBuffer()), then to make it load with this lib you have to do the next:

如果您将文件用作 ArrayBuffer（例如，文件是在客户端上使用 FileReader.readAsArrayBuffer() 读取的），则要使用此库加载它，您必须执行以下操作：

    let workbook = new Excel.Workbook();
    let stream = new Stream.Readable();
    stream.push(file); // file is ArrayBuffer variable
    stream.push(null);
    workbook.xlsx.read(stream).then((workbook)=> {
         // get worksheet, read rows, etc
    });

回答by Qin Luo

//Read a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile("data/Sample.xlsx").then(function () {

    //Get sheet by Name
    var worksheet=workbook.getWorksheet('Sheet1');

    //Get Lastrow
    var row = worksheet.lastRow

    //Update a cell
    row.getCell(1).value = 5;

    row.commit();

    //Save the workbook
    return workbook.xlsx.writeFile("data/Sample.xlsx");

});

回答by Naveen

The below code snippet will help you to read the file sample.xlsx, which contains two columns 1. username 2. password. and I'm trying to assert with Mocha(chai), to check the data type match.

下面的代码片段将帮助您阅读文件 sample.xlsx，其中包含两列 1. 用户名 2. 密码。我正在尝试使用 Mocha(chai) 进行断言，以检查数据类型匹配。

var Excel = require('exceljs');
const assert = require("chai").assert;

var workbook = new Excel.Workbook();
workbook.creator ="Naveen"; 
workbook.modified ="Kumar";


workbook.xlsx.readFile("sample.xlsx").then(function(){
    var workSheet =  workbook.getWorksheet("one"); 

    workSheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {

        currRow = workSheet.getRow(rowNumber); 
         console.log("User Name :" + currRow.getCell(1).value +", Password :" +currRow.getCell(2).value);
         console.log("User Name :" + row.values[1] +", Password :" +  row.values[2] ); 

         assert.equal(currRow.getCell(2).type, Excel.ValueType.Number); 
       //  console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
      });
})

Hope this helps somebody

希望这可以帮助某人

回答by Mayukh

Step1: Download exceljs module using npm

Step1：使用npm下载exceljs模块

Step2: Use the following piece of code to read data from excel

Step2：使用下面这段代码从excel中读取数据

import { Workbook, Worksheet } from 'exceljs';

public static async getExcelD(filename: string, column1Item: string) {
        let wb:Workbook = new Workbook();
        let datafile = path.join(__dirname, "../testData", filename);
        await wb.xlsx.readFile(datafile).then(async ()=>{
            let sh:Worksheet = wb.getWorksheet("Sheet1");
            for(let i=1;i<=sh.actualRowCount;i++){
                if(sh.getRow(i).getCell(1).value==column1Item){
                    data1 = sh.getRow(i).getCell(2).value;
                }
            }
        })
        return await data1;
}

回答by Sumit Bopche

I was getting an error for .xls file. After trying all the permutation and combination, I converted the .xls file into .xlsx and it worked.

我收到 .xls 文件的错误。在尝试了所有的排列组合之后，我将 .xls 文件转换为 .xlsx 并且它起作用了。

const Excel = require('exceljs/dist/es5');


const readExcel = async () => {

  //You can use the absolute path also
  let filepath = './genovaFile.xlsx';

  let workbook = new Excel.Workbook();

  await workbook.xlsx.readFile(filepath);

//You can use the index number also eg. 0 for selecting the sheet
  let worksheet = workbook.getWorksheet("sheet1");

   worksheet.eachRow({ includeEmpty: true }, async (row, rowNumber) => {

    console.log("row " + row.values);

   })

}