search

qrilka / xlsx

Simple and incomplete Excel file parser/writer
MIT License
130 stars 64 forks source link

Pivot tables exported from Excel 2016 lack requisite information #99

Closed mgsloan closed 7 years ago

mgsloan commented 7 years ago

I've tried exporting a pivot table from Excel 2016, and importing via this package. The cells in the pivot table do get imported. However, the pivot table configuration does not get properly imported. Instead, I get:

PivotTable {_pvtName = "PivotTable1", _pvtDataCaption = "Values", _pvtRowFields = [], _pvtColumnFields = [], _pvtDataFields = [], _pvtFields = [], _pvtRowGrandTotals = True, _pvtColumnGrandTotals = True, _pvtOutline = True, _pvtOutlineData = True, _pvtLocation = CellRef {unCellRef = "D1:H6"}, _pvtSrcSheet = "Sheet1", _pvtSrcRef = CellRef {unCellRef = "A1:C4"}}], _wsAutoFilter = Nothing, _wsTables = [], _wsProtection = Nothing}

But the pivot table had a row field, a column field, and a value field.

qrilka commented 7 years ago

@mgsloan could you post a sample xlsx file so I could check out what's going on?

mgsloan commented 7 years ago

Hey! Sorry for the silly example, was experimenting with duplicate header names

sloan_excel_dup_screen

Sloan_excel_dup.xlsx

qrilka commented 7 years ago

thanks, I'll take a look into it later today

qrilka commented 7 years ago

Yep, it's a bug in parsing here - I was using wrong assumption. I'll try to find a good way around it.

qrilka commented 7 years ago

14b6172 fixes this issue :

λ> import qualified Data.ByteString.Lazy as LB
λ> bs <- LB.readFile "/home/qrilka/download/Sloan_excel_dup.xlsx"
λ> _wsPivotTables . snd . head . _xlSheets  $ toXlsx bs
[PivotTable {_pvtName = "PivotTable1", _pvtDataCaption = "Values", _pvtRowFields = [FieldPosition (PivotFieldName "dup2")], _pvtColumnFields = [FieldPosition (PivotFieldName "dup")], _pvtDataFields = [DataField {_dfField = PivotFieldName "cost", _dfName = "Sum of cost", _dfFunction = ConsolidateSum}], _pvtFields = [PivotFieldInfo {_pfiName = Just (PivotFieldName "dup"), _pfiOutline = True, _pfiSortType = FieldSortManual, _pfiHiddenItems = []},PivotFieldInfo {_pfiName = Just (PivotFieldName "dup2"), _pfiOutline = True, _pfiSortType = FieldSortManual, _pfiHiddenItems = []},PivotFieldInfo {_pfiName = Just (PivotFieldName "cost"), _pfiOutline = True, _pfiSortType = FieldSortManual, _pfiHiddenItems = []}], _pvtRowGrandTotals = True, _pvtColumnGrandTotals = True, _pvtOutline = True, _pvtOutlineData = True, _pvtLocation = CellRef {unCellRef = "D1:H6"}, _pvtSrcSheet = "Sheet1", _pvtSrcRef = CellRef {unCellRef = "A1:C4"}}]

please let me know if there will be any other issues with it. Thanks

mgsloan commented 7 years ago

Awesome, thanks for the quick fix!