The task was to create an Excel spreadsheet for users to download on my React-based earning platform, BitcoinOptimists.com. It was quite a challenge so I thought it might be helpful to others if I shared the experience and code related to it. So here goes nuthin’.
Within the spreadsheet itself I needed two worksheets containing a user’s income and expenditures, along with the pertinent information related to each. They needed to be properly sorted by date, while looking well-groomed (styled) to boot. I wanted it to be the type of document a big earner could turn into their accountant and leave that accountant “knocked-their-socks-off” impressed.
While researching how to go about this, I learned that xlsx/SheetJS was the default or most common Excel download NPM package. The catch there, I quickly discovered, was styling the Excel file did not come baked into the cake. Lacking this essential ingredient ruled this package out.
Thereafter, I reviewed Medium articles, Stackoverflow Q&A’s, and YouTube videos to get a feel for better alternatives. What I decided upon was ExcelJS. The styling capability was there, and besides SheetJS, it had the highest weekly download rate amongst the bunch. Naturally, this meant there were quite a few resources out there for it (not YouTube videos, however).
So, after building and testing the two MongoDB query options users could choose, and properly formatting the data on the backend so it would be uniform on the frontend, I was ready to get my hands dirty with ExcelJS. (I feel dirty brushing over this task since it was very complicated and important, but alas, it is not the topic of conversation here.)
To start, you need to *shocker* import the package. You shouldn’t need to see code for that, so let’s start with the creation of the workbook.
In the screenshot below, you’ll see that createExcelWorkbook is an async function that takes an incomeArray and spendsArray as arguments. After the “guard” clause and the string variable used for date displays with moment.js, we get to the creation of the ExcelJS Workbook, and the basic additions to it. Nothing nonstandard here, it’s the template used within the documentation.
After the basic workbook setup, you need to create your worksheets. I named mine Income (+) and Spends (-), and set a few properties. I updated the tabColor and froze the first row in the Excel worksheet, so when you scroll downwards, you can still see which column the data is from.
After creating the worksheets, the next step is to create the columns. Each column requires a header, key, and width. This is also where you can set the style. Do note that you can grab a row (with the sheet.getRow() function) and style it manually, much like how I set the row’s alignment on line 1541 and 1542. You can find other style possibilities in the documentation by clicking here.
After styling and aligning the initial column headers (the first row), it was time to loop through the data and create a row for each.
The problem I ran into here was the spreadsheet automatically applies the styling from the previous row unless you specify otherwise. In other words, the column header styles were being applied to every row and field thereafter. Not the effect I was looking for.
Thus, I tried finding documentation where you could style a row automatically with the addRow function, but I learned through some stackoverflow research that this wasn’t possible. Brainstorming possible solutions, I came up with a goodie. Instead of “for-looping” the entirety of my dataset, I would create one row at the [0] index and then style it after its creation. Then, I would iterate over the rest of my dataset and use the package’s “style based upon the previous row” standard to my advantage. A little extra code, but it did the trick.
You’ll see starting on line 1548, I add a row to my incomeSheet, followed with the values of the first [0] element in the incomeArray. Then starting on line 1562, I grab the newly-created second row with the .getRow(2) function, and update the styles and alignment to my liking. I also update specific cells from the row in the 5 lines of code that follow, with the very handy .getCell() function. For example, I updated the number format of cell B2 on line 1564 (to standard currency based upon their documentation), and its font styles — including green coloring for the currency figures — on line 1565.
I would note that ‘numFmt’ (number format) did NOT work when I entered it into the columns, so that is why I added it here to the specific cell itself. And thankfully since the styles can transfer onto the next row, I only have to do it once for the first data-based row.
In the screenshot below, you can see I start my for loop at the [1] index, since I already added a row at the [0] index. The code is essentially the same, but you may notice there is an ‘i’ after the closing parentheses in the incomeSheet.addRow function. All that does is say “Yes I want to follow the styles from the previous row.” (Otherwise, it’ll follow the style from your column row.)
The code for the second, spendsArray is the same, so I’ll refrain from sharing that. What’s next is taking that customized Workbook, creating it, and having it download in the browser. AKA: The part that I fretted most about, but ended up being one of the easiest things to implement.
Here’s where the async function kicks in. To create the workbook, you need to write a buffer and do stuff with Blobs. I really don’t know exactly what the heck is going on, but luckily I found a few resources that provided similiar examples and I just went with it — and luckily it worked.
On line 1637 I do the buffering, which basically creates the file. Then, you have to create a Blob and put that buffer stuff into it for a BufferBlob HodgePodge.
On line 1642 I use an NPM package called file-saver that plops the blob into a user’s browser. It’s pretty nifty. But after getting this to work and writing this blob — err blog — post, I learned from my friend how to do the same thing without the file-saver package. It’s a few more lines of code, but far less than what the NPM package adds. So I’ve decided to go that route, as the code works the same — and that can be found in the screenshot below.
Here’s what it looks like after the magic happens.
And finally, the end result! (Let’s hope the accountants appreciate my efforts)
Hope you find this useful. Shoot me a comment if you have any questions. Would be more than happy to help.
