Ruby - Generate an Excel Spreadsheet
In the financial world, there are always Excel spreadsheets; spreadsheets and Excel dominate most Fortune 500 Companies. Many times a business person will ask that a bunch of data is put in an Excel spreadsheet for them to manipulate. There are many ways to do this in the C# and .NET world, but how do we skin this cat in Ruby?
In Ruby we can use the Axlsx library, this is a cool library. With Axlsx you can create excel worksheets with charts, images (with links), automated and fixed column widths, customized styles, functions, tables, conditional formatting, print options, comments, merged cells, auto filters, file and stream serialization as well as full schema validation.
Enough talk, let’s dive into the code:
Axlsx has a concept of Package, which holds the Workbook that in turns hold the different Worksheets. In the code above we create a package and from the package variable we retrieve the Workbook. To add a Worksheet you call the ‘add_worksheet’ method on the workbook, passing in a has to for the name. The ‘add_worksheet’ method takes a code block and from the code example you see you can call ‘add_row’ to start adding items into the rows of the worksheet. To save the spreadsheet to disk you call the ‘serialize’ method on the ‘package’ variable passing in the filename. Opening this Excel spreadsheet, you will see a column for income and a column for expense with values below these columns. How easy was that?
There are a bunch of examples available on the GitHub repository for Axlsx; I can see myself using this library quite often going forward.