Excel File Operations

The complete documentation of API usage can be viewed in NPM-XLSX。

Load Excel datasheet

Data contents is often stored in Excel files (*.xlsx). To read data from it in your script, leanpro.xlsx package can be used to read and write Excel files. This package is a thin wrapper of the NPM package XLSX. For more details, refers to XLSX NPM package.

Follow the below steps to add Excel loading code:

* Drag and drop the `Load Excel Data` tool into the code
* Then select the Excel file from the tool dialog
* Set the variable name of the Excel data table, the default value is "workbook"
* Select the worksheet name or index to read (default is 0, which means the first worksheet)
* Finish reading Excel data

example code:

const xlsx = require('leanpro.xlsx');
let workbook = xlsx.readFile("C:\\temp\\data.xlsx");
let worksheetData = xlsx.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
console.log(worksheetData); // Output workbook contents, default to comment state

the results as follows, which should be the same as the worksheet:

[
  { "first_name": "James",
    "last_name": "Butt",
    "company_name": "Benton, John B Jr",
    "state": "LA",
    "zip": "70116" },
  { "first_name": "Josephine",
    "last_name": "Darakjy",
    "company_name": "Chanay, Jeffrey A Esq",
    "state": "MI",
    "zip": "48116" },
  { "first_name": "Art",
    "last_name": "Venere",
    "company_name": "Chemel, James L Cpa",
    "state": "NJ",
    "zip": "8014" } 
]

If you want to access the worksheet by its name, such as "sheet1", the code should looks like the following:

const xlsx = require('leanpro.xlsx');
let workbook = xlsx.readFile("C:\\temp\\data.xlsx");
let worksheetData = xlsx.utils.sheet_to_json(workbook.Sheets["sheet1"]); // use the workbook name to index directly

Use Excel data

Excel files also contain a lot of style and format information, and the data is stored in different sheets (Sheets), so users need to know how to get the data in the sheets and use them in automated operation scripts.

If the data read from the table in the application looks like this:

data = [
    [ '100', 'EmilyIna', 'F', '77', 'NahumBing', 'ChaucerBeck' ],
    [ '101', 'NahumBing', 'F', '92', 'DanBeryl', 'BartonZora' ],
    [ '102', 'MarionQuintina', 'M', '90', 'TrollpoeCaesar', 'EmilyIna' ],
    ......
]
header = [ 'student ID', 'name', 'gender', 'score', 'father', 'mother' ]

The data type for reading and writing csv and xlsx files is in the form of an object array, so it needs to be modified to the following form:

[
  {
    "student ID": "100",
    "name": "EmilyIna",
    "gender": "F",
    "gender": "77",
    "father": "NahumBing",
    "mother": "ChaucerBeck"
  },
  {
    "student ID": "101",
    "name": "NahumBing",
    "gender": "F",
    "gender": "92",
    "father": "DanBeryl",
    "mother": "BartonZora"
  },
  {
    "student ID": "102",
    "name": "MarionQuintina",
    "gender": "M",
    "gender": "90",
    "father": "TrollpoeCaesar",
    "mother": "EmilyIna"
  },
  ...
]

As for why the data format of the object array is needed, you can check the instructions of Excel Read and Write Library and CSV Read and Write.

Therefore, an auxiliary function is needed to convert the data form of the table header and table data into an object array. The code is as follows:

function arrayToJson(data, header) {
    // Method for converting a two-dimensional array into an array of objects
    // Used to build data written to xlsx and csv
    let rows = data.map((datum) => {
        let keyvalueSet = {};
        datum.forEach((cell, index) => {
            keyvalueSet[header[index]] = cell;
        })
        return keyvalueSet;
    })
    return rows;
}

Write Excel data

Create a new workbook

let workbook = xlsx.utils.book_new();

The book_new() method creates an empty workbook with no worksheets. Spreadsheet software usually expects at least one worksheet and will throw an error if an empty workbook is passed to the write function.

let filename = "C:\\temp\\data.xlsx";
let header = ["first_name", "last_name", "company_name", "state", "zip"];
let sheet = xlsx.utils.json_to_sheet(worksheetData, { header: header });

if (existsSync(filename)) {
	workbook = xlsx.readFile(filename);
  workbook.Sheets[workbook.SheetNames[0]] = sheet;
} else {
    xlsx.utils.book_append_sheet(workbook, sheet, 'sheet1');
}
xlsx.writeFile(workbook, fullname);

xlsx.utils.json_to_sheet is used to traverse the JS object array in order to generate worksheet objects.

workbook.Sheets is an object whose keys are sheet names and whose values are sheet objects. book_append_sheet is used to add a worksheet to a workbook

Conversion to and from CSV format

CukeTest not only provides the reading and writing library for Excel, but also provides reading and writing methods for CSV files. Relatively speaking, .csv files have higher versatility in the development process, but in actual work scenarios, Excel .xlsx files are often more commonly used, so here is an introduction to the interaction between CSV files and Excel files convert.

Excel file to CSV file:

Read Excel file to get json data:

const xlsx = require('leanpro.xlsx');
let header = ["first_name", "last_name", "company_name", "state", "zip"];
let workbook = xlsx.readFile("C:\\temp\\data.xlsx");
let worksheetData = xlsx.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);

After getting the data in json format, you can use the saveToCsvFile(rows, filePath) function to save the data as a csv file:

Util.saveToCsvFile(data, "./data_bak.csv");

CSV file to Excel file:

Read CSV file to get json data:

let csvData = await Util.loadCsvFile('C:\\temp\\data.csv');

After getting the data in json format, you can use xlsx.utils.json_to_sheet to convert the json data into a worksheet, and then write it into the workbook:

let sheet = xlsx.utils.json_to_sheet(csvData, { header: header });
workbook.Sheets[workbook.SheetNames[0]] = sheet;
xlsx.writeFile(workbook, "./data_bak.xlsx");