Spreadsheets are nowadays one of the most common ways of showing and sharing organised information into a file. Whether it is to keep track of the inventory of goods in a store, or the total transactions performed by a user, spreadsheets will show the organised information in such way that it is clear.
One day, one of my managers gave me the task of allowing a store to download its entire inventory in a spreadsheet, having multiple pages regarding the different categories the user may have.
After doing some research on top popular gems used in Ruby on Rails for accomplishing this task, I ended up choosing FastExcel, that is based on Libxlsxwriter and wraps some methods around it, like formatting cells.
Even though there were some other valid gems, like Caxlsx or Write_xlsx , FastExcel seemed the most lightweight gem as well as the one that offered the best performance as it is shown under its benchmarks
...
Case Scenario
In order to show how the gem can be exploited, let’s suppose a scenario where a store has 3 categories of products each one of them holding 10 to 25 products each.
Base Case
The first task to accomplish is creating a spreadsheet that has three sheets, each one of them containing the products for each category. Let’s see how that’d go:
Let’s see what have we done here:
First, we have created a workbook named inventory.xlsx , that will be the file where we’ll write our data. After that, we loop on the different product categories the store has to offer, creating a new spreadsheet for each of them by using add_worksheet(sheet_name)
Diving into the data to be written, the headers are to be added first, formatting them in bold to highlight them. To do so, we have appended the headers by using append_row(data, format , and formatted them in bold by calling the bold_format method on our FastExcel object, that is basically a wrapper method around Libxlsxwriter’s set format method in order to format cells to bold.
Finally, we have looped inside the products for each category, appending them to the worksheet. Once all categories are filled, the workbook needs to be closed in order for us to access to it, as the constant_memory: true that we used for creating the file, saves each row to disk.
Advanced functions
Even though the base case would be a good fit for an inventory report, let’s image we are also asked for a total sum of the products we have in the shelves, so that we know how many items could be expected to sell.
Formulas
Of course, we could apply a simple SUM(ROW_A:ROW_B) formula on the file once it has been generated but, what if we could append it and save time?
Let’s see it, formulas are a special value type in excel and FastExcel has wrapped their behaviour under its own class, in order to ease its inclusion.
Supposing we wanted to include our formula to the end and the units for each product are on row B, the code for including it in the worksheet would be:
After this, we can have a better understanding of the gem and how easy it is to create xlsx files from rails and even apply formulas to them.
I hope this is of help for anybody looking for a quick xlsx file generator.
I will se you around! Keep grinding !
Top comments (0)