1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
{-# LANGUAGE CPP #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes #-}
module PivotTableTests
( tests
, testPivotTable
, testPivotSrcCells
) where
#ifdef USE_MICROLENS
import Lens.Micro
#else
import Control.Lens
#endif
import Data.ByteString.Lazy (ByteString)
import qualified Data.Map as M
import Data.Maybe (mapMaybe)
import qualified Data.Text as T
import Test.Tasty (testGroup, TestTree)
import Test.Tasty.HUnit (testCase)
import Text.RawString.QQ
import Text.XML
import Codec.Xlsx
import Codec.Xlsx.Parser.Internal.PivotTable
import Codec.Xlsx.Types.Internal (unsafeRefId)
import Codec.Xlsx.Types.PivotTable.Internal
import Codec.Xlsx.Writer.Internal.PivotTable
import Diff
tests :: TestTree
tests =
testGroup
"Pivot table tests"
[ testCase "proper pivot table rendering" $ do
let ptFiles = renderPivotTableFiles testPivotSrcCells 3 testPivotTable
parseLBS_ def (pvtfTable ptFiles) @==?
stripContentSpaces (parseLBS_ def testPivotTableDefinition)
parseLBS_ def (pvtfCacheDefinition ptFiles) @==?
stripContentSpaces (parseLBS_ def testPivotCacheDefinition)
, testCase "proper pivot table parsing" $ do
let sheetName = "Sheet1"
ref = CellRef "A1:D5"
forCacheId (CacheId 3) = Just (sheetName, ref, testPivotCacheFields)
forCacheId _ = Nothing
-- fields with numeric values go into cache records
testPivotCacheFields' =
[ if cfName cf == PivotFieldName "Color"
then cf
else cf {cfItems = []}
| cf <- testPivotCacheFields
]
Just (sheetName, ref, testPivotCacheFields', Just (unsafeRefId 1)) @==?
parseCache testPivotCacheDefinition
Just testPivotTable @==?
parsePivotTable forCacheId testPivotTableDefinition
]
testPivotTable :: PivotTable
testPivotTable =
PivotTable
{ _pvtName = "PivotTable1"
, _pvtDataCaption = "Values"
, _pvtLocation = CellRef "A3:D12"
, _pvtSrcRef = CellRef "A1:D5"
, _pvtSrcSheet = "Sheet1"
, _pvtRowFields = [FieldPosition colorField, DataPosition]
, _pvtColumnFields = [FieldPosition yearField]
, _pvtDataFields =
[ DataField
{ _dfName = "Sum of field Price"
, _dfField = priceField
, _dfFunction = ConsolidateSum
}
, DataField
{ _dfName = "Sum of field Count"
, _dfField = countField
, _dfFunction = ConsolidateSum
}
]
, _pvtFields =
[ PivotFieldInfo (Just $ colorField) False FieldSortAscending [CellText "green"]
, PivotFieldInfo (Just $ yearField) True FieldSortManual []
, PivotFieldInfo (Just $ priceField) False FieldSortManual []
, PivotFieldInfo (Just $ countField) False FieldSortManual []
]
, _pvtRowGrandTotals = True
, _pvtColumnGrandTotals = False
, _pvtOutline = False
, _pvtOutlineData = False
}
where
colorField = PivotFieldName "Color"
yearField = PivotFieldName "Year"
priceField = PivotFieldName "Price"
countField = PivotFieldName "Count"
testPivotSrcCells :: CellMap
testPivotSrcCells =
M.fromList $
concat
[ [((row, col), def & cellValue ?~ v) | (col, v) <- zip [1 ..] cells]
| (row, cells) <- zip [1 ..] cellMap
]
where
cellMap =
[ [CellText "Color", CellText "Year", CellText "Price", CellText "Count"]
, [CellText "green", CellDouble 2012, CellDouble 12.23, CellDouble 17]
, [CellText "white", CellDouble 2011, CellDouble 73.99, CellDouble 21]
, [CellText "red", CellDouble 2012, CellDouble 10.19, CellDouble 172]
, [CellText "white", CellDouble 2012, CellDouble 34.99, CellDouble 49]
]
testPivotCacheFields :: [CacheField]
testPivotCacheFields =
[ CacheField
(PivotFieldName "Color")
[CellText "green", CellText "white", CellText "red"]
, CacheField (PivotFieldName "Year") [CellDouble 2012, CellDouble 2011]
, CacheField
(PivotFieldName "Price")
[CellDouble 12.23, CellDouble 73.99, CellDouble 10.19, CellDouble 34.99]
, CacheField
(PivotFieldName "Count")
[CellDouble 17, CellDouble 21, CellDouble 172, CellDouble 49]
]
testPivotTableDefinition :: ByteString
testPivotTableDefinition = [r|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><!--Pivot table generated by xlsx-->
<pivotTableDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="PivotTable1" cacheId="3" dataOnRows="1" colGrandTotals="0" dataCaption="Values">
<location ref="A3:D12" firstHeaderRow="1" firstDataRow="2" firstDataCol="1"/>
<pivotFields>
<pivotField name="Color" axis="axisRow" showAll="0" outline="0" sortType="ascending">
<items>
<item h="1" x="0"/><item x="1"/><item x="2"/><item t="default"/>
</items>
</pivotField>
<pivotField name="Year" axis="axisCol" showAll="0" outline="1">
<items>
<item x="0"/><item x="1"/><item t="default"/>
</items>
</pivotField>
<pivotField name="Price" dataField="1" showAll="0" outline="0"/>
<pivotField name="Count" dataField="1" showAll="0" outline="0"/>
</pivotFields>
<rowFields><field x="0"/><field x="-2"/></rowFields>
<colFields><field x="1"/></colFields>
<dataFields>
<dataField name="Sum of field Price" fld="2"/>
<dataField name="Sum of field Count" fld="3"/>
</dataFields>
</pivotTableDefinition>
|]
testPivotCacheDefinition :: ByteString
testPivotCacheDefinition = [r|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><!--Pivot cache definition generated by xlsx-->
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"
invalid="1" refreshOnLoad="1"
xmlns:ns="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<cacheSource type="worksheet">
<worksheetSource ref="A1:D5" sheet="Sheet1"/>
</cacheSource>
<cacheFields>
<cacheField name="Color">
<sharedItems>
<s v="green"/><s v="white"/><s v="red"/>
</sharedItems>
</cacheField>
<cacheField name="Year">
<sharedItems containsNumber="1" containsString="0" containsSemiMixedTypes="0"/>
</cacheField>
<cacheField name="Price">
<sharedItems containsNumber="1" containsString="0" containsSemiMixedTypes="0"/>
</cacheField>
<cacheField name="Count">
<sharedItems containsNumber="1" containsString="0" containsSemiMixedTypes="0"/>
</cacheField>
</cacheFields>
</pivotCacheDefinition>
|]
stripContentSpaces :: Document -> Document
stripContentSpaces doc@Document {documentRoot = root} =
doc {documentRoot = go root}
where
go e@Element {elementNodes = nodes} =
e {elementNodes = mapMaybe goNode nodes}
goNode (NodeElement el) = Just $ NodeElement (go el)
goNode t@(NodeContent txt) =
if T.strip txt == T.empty
then Nothing
else Just t
goNode other = Just $ other
|
