caxlsx / axlsx Pivot Table on same sheet

Question

I'm trying to do the same as that question caxlsx / axlsx Pivot Table on separate sheet but in the same sheet.

When i do:

summary_sheet.add_pivot_table 'A91:E140', 'A53:O68' do |pivot_table|
  pivot_table.rows = ['Activity']
  pivot_table.columns = ['Project']
end

And then go to inspect what happened, i find that the source data on the generated file have $'DPCache_Data Sheet'.$A$1:$O$16 while i been waiting $Summary.$A53$O68

And yes, don't return what i want, giving pivot_table.data_sheet to itself with something like

pivot_table.data_sheet = summary_sheet

or this

pivot_table.data_sheet = pivot_table

not works anyway.

UPDATE FOR @kevinluo201

Your reply do that

Thank you but not solves.

My file starts with

wb = xlsx_package.workbook

Doing with

wb = Axlsx::Package.new.workbook

Return an empty file

Answer 1

:add_pivot_table can be used as add_pivot_table(:pivot_table_position, :data_source)

If you want to create a worksheet called "Summary" and insert data and then create a pivot table, here my version I modified a little bit from the https://github.com/caxlsx/caxlsx/blob/master/examples/pivot_table_example.md

require 'caxlsx'

p = Axlsx::Package.new
wb = p.workbook

wb.add_worksheet(name: 'Summary') do |sheet|
  sheet.add_row ['Activity', 'Project']

  # Generate some data here in the range of 'A53:O68'
  # ...
  # ...
  # ...

  # within the same worksheet, call :add_pivot_table
  sheet.add_pivot_table '$G$5', 'A53:O68', sort_on_headers: ['Activities'] do |pivot_table|
    pivot_table.rows = ['Activities]
    pivot_table.columns = ['Project']
  end
end

p.serialize 'pivot_table_example.xlsx'

Open the generated pivot_table_example.xlsx and go to G5 cell, you'll see the pivot table there with the data source from 'A53:O68'.