caxlsx / axlsx Pivot Table on separate sheet

Question

I am trying to follow this example:

https://github.com/caxlsx/caxlsx/blob/master/examples/pivot_table_example.md

This code works fine in my existing workbook export.

I am trying to add a pivot table to my existing data on another sheet:

wb.add_worksheet(name: "Expenditures") do |sheet|
  ...  
end
   
wb.add_worksheet(name: "Pivot Table") do |sheet|
  
   sheet.add_pivot_table 'M4:M4', 'Expenditures!A1:L100' do |pivot_table|
       pivot_table.rows = ['Vendor', ID]
       pivot_table.columns = ['Month']
       pivot_table.data = [ref: 'Amount', num_fmt: 4]
       pivot_table.pages = ['Year']
  end
    
end

Except it throws this error:

undefined method `row' for nil:NilClass

If I add this to the same sheet it works fine. Can you not reference another sheet for pivot tables?

Answer 1

You can either set data_sheet in the block or use the PivotTable.new constructor directly. Here's what it looks like to do it by setting data_sheet:

expenditures_sheet = wb.add_worksheet(name: "Expenditures") do |sheet|
  # Code to populate the expenditures sheet
end

sheet.add_pivot_table 'M4:M4', 'A1:L100' do |pivot_table|
  pivot_table.data_sheet = expenditures_sheet
  pivot_table.rows = ['Vendor', 'ID']
  pivot_table.columns = ['Month']
  pivot_table.data = [ref: 'Amount', num_fmt: 4]
  pivot_table.pages = ['Year']
end