Pivot table with two data fields

[iknowu10]

it seems the pivot table does not work with two data fields specified. i tried it with the pivot_test.rb example by adding an extra numeric column in the dataset. it throws a error when i open the file.

the following are the codes:

   p = Axlsx::Package.new
    wb = p.workbook
    wb.add_worksheet(:name => "Data Sheet") do |sheet|
      sheet.add_row ['Month', 'Year', 'Type', 'Sales', 'Top','Region']
      2.times { sheet.add_row [month, year, type, sales,top_sales, region] }
    end

    wb.add_worksheet(:name => 'Summary') do |sheet|
      pivot_table = Axlsx::PivotTable.new 'A1:H8', "A1:F3", wb.worksheets[0]
      pivot_table.rows = ['Month', 'Year']
      pivot_table.columns = ['Type']
      pivot_table.data = [ 'Sales','Top']
      sheet.pivot_tables << pivot_table
    end

what did i miss?

[iknowu10]

more info:

it works on openoffice, but not on MS Excel.

[richardonichol]

I have the same problem. The example above works in Open Office 4.1.1 but does not work in Excel for Windows 2007 or for Mac 2011. The Summary worksheet comes up empty.

[NoSkillGuy]

I do have the same problem. Works in OpenOffice, Blank sheet in Excel. Is their any fix made or any hack to solve this.

[samzhao2008]

+1

[asheynkman]

have this issue been resolved ? Is there good workaround ?

[jotaki]

This still seems to be an issue.

Has there ever been a work around? what were other peoples solutions?

[jotaki]

I attempted to use

<pivotCacheDefinition ... invalid="1" refreshOnLoad="1" ...>...</pivotCacheDefinition>

in a working xlsx document, and got a similar result... I'm wondering if Excel doesn't like this for some reason?

The issue I see with this though, is if that is the case then axlsx I guess needs to generate the cache?

[jotaki]

managed to get a broken xlsx document to work by adding cache data and ensuring <colFields> was set in <pivotTableDefinition>

Need to experiment a bit more, but I believe that's the issue.

[jotaki]

<pivotTableDefinition ... dataOnRows="0" ...> <colFields> <field x="-2"/> </colFields>

Seems to be the magic that gets it working.

I'll be monkey patching my code to fix this, but it probably wont be the right way to implement into AXLSX. If I get time I'll look into it some more.

Otherwise, hopefully this helps anyone else who comes across this issue.

[diego-alay]

Hi did you solve the problem? did you add an initializer to override the method?

[diego-alay]

<pivotTableDefinition ... dataOnRows="0" ...> <colFields> </colFields>

Seems to be the magic that gets it working.

I'll be monkey patching my code to fix this, but it probably wont be the right way to implement into AXLSX. If I get time I'll look into it some more.

Otherwise, hopefully this helps anyone else who comes across this issue.

Hallo !! Can you help me, please? I read your response but I can't figure out how to solve the problem.