It's All Writing.

Writing makes you happy.

How to make a specification which is executable and inspectable.

I explain how to make a specification which is executable and inspectable through some entries continued. The following technologies is used.

  • Node.js
  • JSON
  • WebdriverIO
  • Microsoft Excel ( Please don’t hate! Excel is one of the most suitable tools for the person who isn’t a programmer to write the specification! And they like that very much. )

Now, I’ll explain by entries of the following series.

  • How to generate data structure from Microsoft Excel.
  • Basis of WebdriverIO
  • How to make a End-to-End Testing by WebdriverIO and Microsoft Excel.
  • Can I execute multiple scenarios which make by multiple sheets of Excel? Yes we can!
  • Let’s try BDD!

So, in this entry, I’ll explain “How to generate data structure from Microsoft Excel”.

xlxs package

xlxs package is a very useful and strong for the purpose.

Parser and writer for various spreadsheet formats. Pure-JS cleanroom implementation from official specifications, related documents, and test files. Emphasis on parsing and writing robustness, cross-format feature compatibility with a unified JS representation, and ES3/ES5 browser compatibility back to IE6.

A one of the reason which I choose Node.js is that it can use xlsx.

install xlsx package

$ npm install xlsx

read xlsx file and convert to JSON

It’s very simple.

const xlsx = require('xlsx');
const utils = xlsx.utils;

const specification_xlsx_path = './specification.xlsx';

let workbook = xlsx.readFile(specification_xlsx_path);
let worksheet = workbook.Sheets['data'];
let json = utils.sheet_to_json(worksheet);

if you make specification.xlsx like below.

sample of xlsx
sample of xlsx

Above code returns…

         Name: 'TAKAHIRO SHINCHI',
         Birthday: '1979/01/03',
         Sex: 'male',
         Hobby: 'Walking'
         Name: 'KAZUYO SHINCHI',
         Birthday: '1975/04/13',
         Sex: 'female'

you can see that the first row is the header row and not considered data and that Birthday’s format is kept. You’d also notice Hobby in KAZUYO being not output.

You can change these behavior by options.

if you change code like below.

let json = utils.sheet_to_json(worksheet, {header: 1, defval: ''});

it returns…

    [ 'Name', 'Birthday', 'Sex', 'Hobby' ],
    [ 'TAKAHIRO SHINCHI', '1979/01/03', 'male', 'Walking' ],
    [ 'KAZUYO SHINCHI', '1975/04/13', 'female', '' ]

Please check the reference for details.