Building Dynamic Excel Templates with Rails and Axlsx

Remember the last time you had to create an Excel template for your team? You probably spent hours setting up data validation rules, creating dropdown lists, and making sure everything worked just right. Now imagine having to do this programmatically for hundreds of users, each with their own specific requirements. That's exactly what my co-worker Nicholas and I had to tackle recently when building a bulk upload feature, and I want to share how we solved it using Rails and the Axlsx gem.

The Challenge

We were building a bulk upload system where users needed to download Excel templates for data entry. These weren't your run-of-the-mill spreadsheets - they needed:

• Dynamic dropdowns that were fetched from the database at download time
• Complex validation rules (e.g., date ranges, number formats)
• Conditional formatting based on data relationships
• Multiple sheets with cross-references
• Support for thousands of rows of data

The Solution: Axlsx Gem

After some research, we found the Axlsx gem, which turned out to be perfect for this use case. It's a Ruby library that lets you create Excel files programmatically, and it's surprisingly powerful.

Here's a real-world example we implemented for our bulk upload system:

require 'axlsx'

class BulkUploadTemplateGenerator
  def initialize(user)
    @user = user
    @package = Axlsx::Package.new
    @workbook = @package.workbook
  end

  def generate
    create_data_sheet
    create_template_sheet
    add_named_ranges
    @package.serialize("bulk_upload_template.xlsx")
  end

  private

  def create_data_sheet
    @workbook.add_worksheet(name: 'Data') do |sheet|
      # Add categories from database
      sheet.add_row(['Category', 'Description'])
      Category.active.each do |category|
        sheet.add_row([category.name, category.description])
      end

      # Add departments
      sheet.add_row([])  # Empty row for separation
      sheet.add_row(['Departments'])
      Department.all.each do |dept|
        sheet.add_row([dept.name, dept.code])
      end

      # Add locations
      sheet.add_row([])
      sheet.add_row(['Locations'])
      Location.active.each do |location|
        sheet.add_row([location.name, location.region])
      end
    end
  end

  def create_template_sheet
    @workbook.add_worksheet(name: 'Bulk Upload Template') do |sheet|
      # Add headers
      sheet.add_row(['Item Name', 'Category', 'Department', 'Location', 'Quantity', 'Unit Price'])

      # Add data validation for Category
      sheet.add_data_validation('B2:B10000', {
        type: :list,
        formula1: "=Data!$A$2:$A$#{Category.active.count + 1}",
        showErrorMessage: true,
        errorTitle: 'Invalid Category',
        error: 'Please select a valid category',
        errorStyle: :stop
      })

      # Add data validation for Department
      dept_start = Category.active.count + 4
      dept_end = dept_start + Department.count - 1
      sheet.add_data_validation('C2:C10000', {
        type: :list,
        formula1: "=Data!$A$#{dept_start}:$A$#{dept_end}",
        showErrorMessage: true,
        errorTitle: 'Invalid Department',
        error: 'Please select a valid department',
        errorStyle: :stop
      })

      # Add data validation for Location
      location_start = dept_end + 3
      location_end = location_start + Location.active.count - 1
      sheet.add_data_validation('D2:D10000', {
        type: :list,
        formula1: "=Data!$A$#{location_start}:$A$#{location_end}",
        showErrorMessage: true,
        errorTitle: 'Invalid Location',
        error: 'Please select a valid location',
        errorStyle: :stop
      })

      # Add number validation for Quantity and Unit Price
      sheet.add_data_validation('E2:F10000', {
        type: :decimal,
        operator: :greaterThan,
        formula1: '0',
        showErrorMessage: true,
        errorTitle: 'Invalid Amount',
        error: 'Please enter a positive number',
        errorStyle: :stop
      })
    end
  end

  def add_named_ranges
    # Add named ranges for easier reference
    @workbook.add_defined_name(
      "'Data'!$A$2:$A$#{Category.active.count + 1}",
      { name: 'Categories' }
    )
    @workbook.add_defined_name(
      "'Data'!$A$#{Category.active.count + 4}:$A$#{Category.active.count + 4 + Department.count - 1}",
      { name: 'Departments' }
    )
    @workbook.add_defined_name(
      "'Data'!$A$#{Category.active.count + 4 + Department.count + 2}:$A$#{Category.active.count + 4 + Department.count + 2 + Location.active.count - 1}",
      { name: 'Locations' }
    )
  end
end

Key Features We Implemented

1. Dynamic Database-Driven Dropdowns

   • Created dropdowns that pull data directly from the database at download time
   • Made them context-aware based on user permissions
   • Added error messages for invalid selections
   • Ensured data consistency with active records only

2. Complex Validations

   • Number format validations for quantities and prices
   • Cross-sheet validations for related data
   • Custom validation messages for better user experience

3. Performance Optimizations

   • Used named ranges for better formula management
   • Implemented batch processing for large datasets
   • Optimized database queries for dropdown data

Tips and Tricks We Learned

1. Database Query Optimization

   # Instead of this
   def fetch_categories
     Category.all.map { |c| [c.name, c.description] }
   end
   
   # Do this
   def fetch_categories
     Category.active
            .select(:name, :description)
            .order(:name)
            .map { |c| [c.name, c.description] }
   end
   

2. Error Handling

   def safe_generate
     begin
       generate
     rescue Axlsx::Package::ZipError => e
       Rails.logger.error "Excel generation failed: #{e.message}"
       raise ExcelGenerationError, "Failed to generate bulk upload template"
     end
   end
   

3. Memory Management

   def generate_large_template
     Axlsx::Package.new do |p|
       # Your template generation code here
       p.serialize('bulk_upload_template.xlsx')
     end
   end
   

The Axlsx gem has made it possible to create sophisticated Excel templates programmatically, saving hours of manual work. While it has its quirks, the flexibility it provides is worth the learning curve.