search

randym / axlsx

xlsx generation with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client's machine.
MIT License
2.62k stars 695 forks source link

Pivot table with two data fields #404

Open iknowu10 opened 9 years ago

iknowu10 commented 9 years ago

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 commented 9 years ago

more info:

it works on openoffice, but not on MS Excel.

richardonichol commented 9 years ago

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 commented 8 years ago

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 commented 7 years ago

+1

asheynkman commented 7 years ago

have this issue been resolved ? Is there good workaround ?

jotaki commented 7 years ago

This still seems to be an issue.

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

jotaki commented 7 years ago

I attempted to use

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

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 commented 7 years ago

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 commented 7 years ago

<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 commented 3 years ago

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

diego-alay commented 3 years ago

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

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.