Building a NodeJs, TypeScript Project For Reading Excel Files

Recap

The first part of this tutorial was just about getting the config set up in a way that felt a little bit more professional than just npm init it can be found here https://medium.com/@andrewallison/a-simple-project-with-typescript-nodejs-85e48616b1

The source code can be grabbed here. I’ve tagged it as P1 so you can jump straight to where this article kicks off if you need to.

https://github.com/AndrewAllison/read-excel/releases/tag/P1

Part 2

Ok so lets kick off the second part of this by installing the required packages for reading the excel file and writing it out to JSON

 npm i exceljs csvtojson

ExcelJs — This is by far one of the better libraries if found to help manipulate Excel files. Find out more about it here https://www.npmjs.com/package/exceljs

CsvToJson — This one makes turinging the excel into JSON via the medium of csv really simple https://www.npmjs.com/package/csvtojson

NOTE! This is based around a relatively small and simple Excel file. If the file is big you may want to use a stream instead of reading it all into memory in one go. There is a great SO article about this and I may do some future posts on it. https://stackoverflow.com/questions/69866039/parse-excel-file-and-create-json-format-in-exceljs-on-nodejs

OK, Lets get the meaty part of the project

Create a new filesrc/file-utils.ts This is just a nearter place to put some of the methods incase at some point they need to be reused. File it with the following code

import * as csvToJson from 'csvtojson';
import * as ExcelJS from 'exceljs';
import * as fs from 'fs';
import * as path from 'path';

const tempFolderPath = path.join(__dirname, '../', 'temp');

const readFromFile = async (filePath: string) => {
  return new Promise((resolve) => {
    csvToJson()
      .fromFile(filePath)
      .then((jsonObj) => {
        return resolve(jsonObj);
      });
  });
};

const writeToTempFile = async (
  originalExcelFile: string,
  tempCsvFilePath: string,
): Promise<void> => {
  if (!fs.existsSync(tempFolderPath)) {
    fs.mkdirSync(tempFolderPath);
  }

  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(originalExcelFile);

  const writeStream = fs.createWriteStream(tempCsvFilePath);
  await workbook.csv.write(writeStream, { sheetName: 'books' });
};

export { tempFolderPath, readFromFile, writeToTempFile };

The two util methods in this file are `writeToTempFile` which is for reading a Excel file and writing it to a temp csv and `readFromFile` which will read the temp file and produce a json object from it.

Next update the src/index.ts file with the following.

import * as fs from 'fs';
import * as path from 'path';
import { readFromFile, tempFolderPath, writeToTempFile } from './file-utils';

const run = async () => {
  return new Promise(async (resolve) => {
    const originalExcelFile = path.join(__dirname, '../', 'data', 'books.xlsx');
    const tempCsvFilePath = path.join(tempFolderPath, 'temp_books.csv');
    const jsonWriteFile = path.join(tempFolderPath, 'books.json');

    console.log(`Reading file from: ${originalExcelFile}`);
    await writeToTempFile(originalExcelFile, tempCsvFilePath);
    const jsonObj = (await readFromFile(tempCsvFilePath)) as any[];

    console.log(`Writing file ${jsonWriteFile}`);
    const jsonWrite = fs.createWriteStream(jsonWriteFile);
    jsonWrite.write(JSON.stringify(jsonObj, null, 2));

    return resolve({
      success: true,
      message: `File wrote complete: Items Stored: ${jsonObj.length}`,
    });
  });
};

run()
  .catch((e) => console.error(e))
  .then((c) => console.log('Complete', c));

You will also need to create a folder called data in the root directory of the project and add the example xlsx file. It’s a books file I grabbed from a gist. Download it from the GitHub repo https://github.com/AndrewAllison/read-excel/blob/main/data/books.xlsx click the “Download” button and make sure it goes in the data folder at the root of your project.

The file structure should look something like:

LETS GO!!!!!

Run the npm run start command again and watch it magically produce it’s files. Theres should be a books.json and a books.csv in the temp folder like above. These won’t be part of the committed files as they are in a temp location. This is because we want to keep re-generating them as things change but you could add them if you so wished.

Lastly we will do the git add . command followed by git-cz fill in the details and do a git push to get our code pushed on to github

Last step is if you have been working in a git flow style and are hanging out on the develop branch let’s create a pull request and merge it all back into the main branch. There will be a green banner in Github telling you that you pushed to develop and asking you to merge back into main.

Pull Request to merge back to main

Bonus

Just throwing this in as a little bit of a bonus

npm i -D dotenv-cli standard-version conventional-github-releaser

So you can keep track of the version of the package and changes in a neat little way it’s pretty easy to use the following packages and set up some script commands. I’ll not fully automate this as it’s beyond the scope of this article but it wouldn’t take much.

standard-version

conventional-github-releaser

"release": "standard-version --no-verify",
"post:release": "npm run release:github && npm run release:tags",
"release:github": "dotenv -e .env -- conventional-github-releaser -p conventionalcommits",

create a .env file in the root of your project. Grab a githib token by following these instructions https://github.blog/2013-05-16-personal-api-tokens/

CONVENTIONAL_GITHUB_RELEASER_TOKEN="<TOKEN_GOES_HERE>"
DEBUG=conventional-github-releaser

From there you can run npm run release you should get some output similar to

output from git release

You can also do this on a release branch if you want to really step it up a notch. There is a whole articles worth of automating the release process. I might try and get around to one of those soon.

Carry out the following command

git push --follow-tags origin develop

The next step I’m not sure if I did correctly as there release notes did get generated so Iwill look to fix this up properly in a future article. To get them working I did a pull request and got everything merged into main. The ran npm run release:github You will get some pretty out put

The release notes did then appear

Release notes in github

You can have a better look at https://github.com/AndrewAllison/read-excel/releases

Thoughts

As someone who has worked a lot of years in tech I’ve never put a lot of content out there as I always wanted it to be perfect before I released it this time I’m trying to just put stuff out there even if it’s not exactly how I’d like it. So feel free to give feedback and make requests but be gentle and kind. I’m simply trying to help others with the stuff I know.

References

We grabbed some google based content to use as a demo spreadsheet. We got it from here. https://gist.githubusercontent.com/jaidevd/23aef12e9bf56c618c41/raw/c05e98672b8d52fa0cb94aad80f75eb78342e5d4/books.csv