1 /**************************************************************
2 *
3 * Licensed to the Apache Software Foundation (ASF) under one
4 * or more contributor license agreements. See the NOTICE file
5 * distributed with this work for additional information
6 * regarding copyright ownership. The ASF licenses this file
7 * to you under the Apache License, Version 2.0 (the
8 * "License"); you may not use this file except in compliance
9 * with the License. You may obtain a copy of the License at
10 *
11 * http://www.apache.org/licenses/LICENSE-2.0
12 *
13 * Unless required by applicable law or agreed to in writing,
14 * software distributed under the License is distributed on an
15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16 * KIND, either express or implied. See the License for the
17 * specific language governing permissions and limitations
18 * under the License.
19 *
20 *************************************************************/
21
22
23
24 #include "oox/xls/condformatbuffer.hxx"
25
26 #include <com/sun/star/beans/PropertyValue.hpp>
27 #include <com/sun/star/container/XIndexAccess.hpp>
28 #include <com/sun/star/container/XNameContainer.hpp>
29 #include <com/sun/star/sheet/ConditionOperator.hpp>
30 #include <com/sun/star/sheet/XSheetCellRanges.hpp>
31 #include <com/sun/star/sheet/XSheetConditionalEntries.hpp>
32 #include <com/sun/star/sheet/XSpreadsheet.hpp>
33 #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
34 #include <com/sun/star/sheet/XSpreadsheets.hpp>
35 #include <com/sun/star/style/XStyle.hpp>
36 #include <com/sun/star/style/XStyleFamiliesSupplier.hpp>
37 #include <com/sun/star/table/CellAddress.hpp>
38 #include <com/sun/star/table/CellRangeAddress.hpp>
39 #include <com/sun/star/table/XCellRange.hpp>
40 #include <rtl/ustrbuf.hxx>
41 #include "oox/helper/attributelist.hxx"
42 #include "oox/helper/containerhelper.hxx"
43 #include "oox/helper/propertyset.hxx"
44 #include "oox/xls/addressconverter.hxx"
45 #include "oox/xls/biffinputstream.hxx"
46 #include "oox/xls/stylesbuffer.hxx"
47
48 namespace oox {
49 namespace xls {
50
51 // ============================================================================
52
53 using namespace ::com::sun::star::beans;
54 using namespace ::com::sun::star::container;
55 using namespace ::com::sun::star::sheet;
56 using namespace ::com::sun::star::style;
57 using namespace ::com::sun::star::table;
58 using namespace ::com::sun::star::uno;
59
60 using ::rtl::OUString;
61 using ::rtl::OUStringBuffer;
62
63 // ============================================================================
64
65 namespace {
66
67 const sal_Int32 BIFF12_CFRULE_TYPE_CELLIS = 1;
68 const sal_Int32 BIFF12_CFRULE_TYPE_EXPRESSION = 2;
69 const sal_Int32 BIFF12_CFRULE_TYPE_COLORSCALE = 3;
70 const sal_Int32 BIFF12_CFRULE_TYPE_DATABAR = 4;
71 const sal_Int32 BIFF12_CFRULE_TYPE_TOPTEN = 5;
72 const sal_Int32 BIFF12_CFRULE_TYPE_ICONSET = 6;
73
74 const sal_Int32 BIFF12_CFRULE_SUB_CELLIS = 0;
75 const sal_Int32 BIFF12_CFRULE_SUB_EXPRESSION = 1;
76 const sal_Int32 BIFF12_CFRULE_SUB_COLORSCALE = 2;
77 const sal_Int32 BIFF12_CFRULE_SUB_DATABAR = 3;
78 const sal_Int32 BIFF12_CFRULE_SUB_ICONSET = 4;
79 const sal_Int32 BIFF12_CFRULE_SUB_TOPTEN = 5;
80 const sal_Int32 BIFF12_CFRULE_SUB_UNIQUE = 7;
81 const sal_Int32 BIFF12_CFRULE_SUB_TEXT = 8;
82 const sal_Int32 BIFF12_CFRULE_SUB_BLANK = 9;
83 const sal_Int32 BIFF12_CFRULE_SUB_NOTBLANK = 10;
84 const sal_Int32 BIFF12_CFRULE_SUB_ERROR = 11;
85 const sal_Int32 BIFF12_CFRULE_SUB_NOTERROR = 12;
86 const sal_Int32 BIFF12_CFRULE_SUB_TODAY = 15;
87 const sal_Int32 BIFF12_CFRULE_SUB_TOMORROW = 16;
88 const sal_Int32 BIFF12_CFRULE_SUB_YESTERDAY = 17;
89 const sal_Int32 BIFF12_CFRULE_SUB_LAST7DAYS = 18;
90 const sal_Int32 BIFF12_CFRULE_SUB_LASTMONTH = 19;
91 const sal_Int32 BIFF12_CFRULE_SUB_NEXTMONTH = 20;
92 const sal_Int32 BIFF12_CFRULE_SUB_THISWEEK = 21;
93 const sal_Int32 BIFF12_CFRULE_SUB_NEXTWEEK = 22;
94 const sal_Int32 BIFF12_CFRULE_SUB_LASTWEEK = 23;
95 const sal_Int32 BIFF12_CFRULE_SUB_THISMONTH = 24;
96 const sal_Int32 BIFF12_CFRULE_SUB_ABOVEAVERAGE = 25;
97 const sal_Int32 BIFF12_CFRULE_SUB_BELOWAVERAGE = 26;
98 const sal_Int32 BIFF12_CFRULE_SUB_DUPLICATE = 27;
99 const sal_Int32 BIFF12_CFRULE_SUB_EQABOVEAVERAGE = 29;
100 const sal_Int32 BIFF12_CFRULE_SUB_EQBELOWAVERAGE = 30;
101
102 const sal_Int32 BIFF12_CFRULE_TIMEOP_TODAY = 0;
103 const sal_Int32 BIFF12_CFRULE_TIMEOP_YESTERDAY = 1;
104 const sal_Int32 BIFF12_CFRULE_TIMEOP_LAST7DAYS = 2;
105 const sal_Int32 BIFF12_CFRULE_TIMEOP_THISWEEK = 3;
106 const sal_Int32 BIFF12_CFRULE_TIMEOP_LASTWEEK = 4;
107 const sal_Int32 BIFF12_CFRULE_TIMEOP_LASTMONTH = 5;
108 const sal_Int32 BIFF12_CFRULE_TIMEOP_TOMORROW = 6;
109 const sal_Int32 BIFF12_CFRULE_TIMEOP_NEXTWEEK = 7;
110 const sal_Int32 BIFF12_CFRULE_TIMEOP_NEXTMONTH = 8;
111 const sal_Int32 BIFF12_CFRULE_TIMEOP_THISMONTH = 9;
112
113 const sal_uInt16 BIFF12_CFRULE_STOPIFTRUE = 0x0002;
114 const sal_uInt16 BIFF12_CFRULE_ABOVEAVERAGE = 0x0004;
115 const sal_uInt16 BIFF12_CFRULE_BOTTOM = 0x0008;
116 const sal_uInt16 BIFF12_CFRULE_PERCENT = 0x0010;
117
118 // ----------------------------------------------------------------------------
119
120 template< typename Type >
lclAppendProperty(::std::vector<PropertyValue> & orProps,const OUString & rPropName,const Type & rValue)121 void lclAppendProperty( ::std::vector< PropertyValue >& orProps, const OUString& rPropName, const Type& rValue )
122 {
123 orProps.push_back( PropertyValue() );
124 orProps.back().Name = rPropName;
125 orProps.back().Value <<= rValue;
126 }
127
128 } // namespace
129
130 // ============================================================================
131
CondFormatRuleModel()132 CondFormatRuleModel::CondFormatRuleModel() :
133 mnPriority( -1 ),
134 mnType( XML_TOKEN_INVALID ),
135 mnOperator( XML_TOKEN_INVALID ),
136 mnTimePeriod( XML_TOKEN_INVALID ),
137 mnRank( 0 ),
138 mnStdDev( 0 ),
139 mnDxfId( -1 ),
140 mbStopIfTrue( false ),
141 mbBottom( false ),
142 mbPercent( false ),
143 mbAboveAverage( true ),
144 mbEqualAverage( false )
145 {
146 }
147
setBiffOperator(sal_Int32 nOperator)148 void CondFormatRuleModel::setBiffOperator( sal_Int32 nOperator )
149 {
150 static const sal_Int32 spnOperators[] = {
151 XML_TOKEN_INVALID, XML_between, XML_notBetween, XML_equal, XML_notEqual,
152 XML_greaterThan, XML_lessThan, XML_greaterThanOrEqual, XML_lessThanOrEqual };
153 mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
154 }
155
setBiff12TextType(sal_Int32 nOperator)156 void CondFormatRuleModel::setBiff12TextType( sal_Int32 nOperator )
157 {
158 // note: type XML_notContainsText vs. operator XML_notContains
159 static const sal_Int32 spnTypes[] = { XML_containsText, XML_notContainsText, XML_beginsWith, XML_endsWith };
160 mnType = STATIC_ARRAY_SELECT( spnTypes, nOperator, XML_TOKEN_INVALID );
161 static const sal_Int32 spnOperators[] = { XML_containsText, XML_notContains, XML_beginsWith, XML_endsWith };
162 mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
163 }
164
165 // ============================================================================
166
CondFormatRule(const CondFormat & rCondFormat)167 CondFormatRule::CondFormatRule( const CondFormat& rCondFormat ) :
168 WorksheetHelper( rCondFormat ),
169 mrCondFormat( rCondFormat )
170 {
171 }
172
importCfRule(const AttributeList & rAttribs)173 void CondFormatRule::importCfRule( const AttributeList& rAttribs )
174 {
175 maModel.maText = rAttribs.getString( XML_text, OUString() );
176 maModel.mnPriority = rAttribs.getInteger( XML_priority, -1 );
177 maModel.mnType = rAttribs.getToken( XML_type, XML_TOKEN_INVALID );
178 maModel.mnOperator = rAttribs.getToken( XML_operator, XML_TOKEN_INVALID );
179 maModel.mnTimePeriod = rAttribs.getToken( XML_timePeriod, XML_TOKEN_INVALID );
180 maModel.mnRank = rAttribs.getInteger( XML_rank, 0 );
181 maModel.mnStdDev = rAttribs.getInteger( XML_stdDev, 0 );
182 maModel.mnDxfId = rAttribs.getInteger( XML_dxfId, -1 );
183 maModel.mbStopIfTrue = rAttribs.getBool( XML_stopIfTrue, false );
184 maModel.mbBottom = rAttribs.getBool( XML_bottom, false );
185 maModel.mbPercent = rAttribs.getBool( XML_percent, false );
186 maModel.mbAboveAverage = rAttribs.getBool( XML_aboveAverage, true );
187 maModel.mbEqualAverage = rAttribs.getBool( XML_equalAverage, false );
188 }
189
appendFormula(const OUString & rFormula)190 void CondFormatRule::appendFormula( const OUString& rFormula )
191 {
192 CellAddress aBaseAddr = mrCondFormat.getRanges().getBaseAddress();
193 ApiTokenSequence aTokens = getFormulaParser().importFormula( aBaseAddr, rFormula );
194 maModel.maFormulas.push_back( aTokens );
195 }
196
importCfRule(SequenceInputStream & rStrm)197 void CondFormatRule::importCfRule( SequenceInputStream& rStrm )
198 {
199 sal_Int32 nType, nSubType, nOperator, nFmla1Size, nFmla2Size, nFmla3Size;
200 sal_uInt16 nFlags;
201 rStrm >> nType >> nSubType >> maModel.mnDxfId >> maModel.mnPriority >> nOperator;
202 rStrm.skip( 8 );
203 rStrm >> nFlags >> nFmla1Size >> nFmla2Size >> nFmla3Size >> maModel.maText;
204
205 /* Import the formulas. For no obvious reason, the sizes of the formulas
206 are already stored before. Nevertheless the following formulas contain
207 their own sizes. */
208
209 // first formula
210 OSL_ENSURE( (nFmla1Size >= 0) || ((nFmla2Size == 0) && (nFmla3Size == 0)), "CondFormatRule::importCfRule - missing first formula" );
211 OSL_ENSURE( (nFmla1Size > 0) == (rStrm.getRemaining() >= 8), "CondFormatRule::importCfRule - formula size mismatch" );
212 if( rStrm.getRemaining() >= 8 )
213 {
214 CellAddress aBaseAddr = mrCondFormat.getRanges().getBaseAddress();
215 ApiTokenSequence aTokens = getFormulaParser().importFormula( aBaseAddr, FORMULATYPE_CONDFORMAT, rStrm );
216 maModel.maFormulas.push_back( aTokens );
217
218 // second formula
219 OSL_ENSURE( (nFmla2Size >= 0) || (nFmla3Size == 0), "CondFormatRule::importCfRule - missing second formula" );
220 OSL_ENSURE( (nFmla2Size > 0) == (rStrm.getRemaining() >= 8), "CondFormatRule::importCfRule - formula size mismatch" );
221 if( rStrm.getRemaining() >= 8 )
222 {
223 aTokens = getFormulaParser().importFormula( aBaseAddr, FORMULATYPE_CONDFORMAT, rStrm );
224 maModel.maFormulas.push_back( aTokens );
225
226 // third formula
227 OSL_ENSURE( (nFmla3Size > 0) == (rStrm.getRemaining() >= 8), "CondFormatRule::importCfRule - formula size mismatch" );
228 if( rStrm.getRemaining() >= 8 )
229 {
230 aTokens = getFormulaParser().importFormula( aBaseAddr, FORMULATYPE_CONDFORMAT, rStrm );
231 maModel.maFormulas.push_back( aTokens );
232 }
233 }
234 }
235
236 // flags
237 maModel.mbStopIfTrue = getFlag( nFlags, BIFF12_CFRULE_STOPIFTRUE );
238 maModel.mbBottom = getFlag( nFlags, BIFF12_CFRULE_BOTTOM );
239 maModel.mbPercent = getFlag( nFlags, BIFF12_CFRULE_PERCENT );
240 maModel.mbAboveAverage = getFlag( nFlags, BIFF12_CFRULE_ABOVEAVERAGE );
241 // no flag for equalAverage, must be determined from subtype below...
242
243 // Convert the type/operator settings. This is a real mess...
244 switch( nType )
245 {
246 case BIFF12_CFRULE_TYPE_CELLIS:
247 OSL_ENSURE( nSubType == BIFF12_CFRULE_SUB_CELLIS, "CondFormatRule::importCfRule - rule type/subtype mismatch" );
248 maModel.mnType = XML_cellIs;
249 maModel.setBiffOperator( nOperator );
250 OSL_ENSURE( maModel.mnOperator != XML_TOKEN_INVALID, "CondFormatRule::importCfRule - unknown operator" );
251 break;
252 case BIFF12_CFRULE_TYPE_EXPRESSION:
253 // here we have to look at the subtype to find the real type...
254 switch( nSubType )
255 {
256 case BIFF12_CFRULE_SUB_EXPRESSION:
257 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
258 maModel.mnType = XML_expression;
259 break;
260 case BIFF12_CFRULE_SUB_UNIQUE:
261 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
262 maModel.mnType = XML_uniqueValues;
263 break;
264 case BIFF12_CFRULE_SUB_TEXT:
265 maModel.setBiff12TextType( nOperator );
266 OSL_ENSURE( maModel.mnType != XML_TOKEN_INVALID, "CondFormatRule::importCfRule - unexpected operator value" );
267 break;
268 case BIFF12_CFRULE_SUB_BLANK:
269 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
270 maModel.mnType = XML_containsBlanks;
271 break;
272 case BIFF12_CFRULE_SUB_NOTBLANK:
273 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
274 maModel.mnType = XML_notContainsBlanks;
275 break;
276 case BIFF12_CFRULE_SUB_ERROR:
277 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
278 maModel.mnType = XML_containsErrors;
279 break;
280 case BIFF12_CFRULE_SUB_NOTERROR:
281 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
282 maModel.mnType = XML_notContainsErrors;
283 break;
284 case BIFF12_CFRULE_SUB_TODAY:
285 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_TODAY, "CondFormatRule::importCfRule - unexpected time operator value" );
286 maModel.mnType = XML_timePeriod;
287 maModel.mnTimePeriod = XML_today;
288 break;
289 case BIFF12_CFRULE_SUB_TOMORROW:
290 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_TOMORROW, "CondFormatRule::importCfRule - unexpected time operator value" );
291 maModel.mnType = XML_timePeriod;
292 maModel.mnTimePeriod = XML_tomorrow;
293 break;
294 case BIFF12_CFRULE_SUB_YESTERDAY:
295 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_YESTERDAY, "CondFormatRule::importCfRule - unexpected time operator value" );
296 maModel.mnType = XML_timePeriod;
297 maModel.mnTimePeriod = XML_yesterday;
298 break;
299 case BIFF12_CFRULE_SUB_LAST7DAYS:
300 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_LAST7DAYS, "CondFormatRule::importCfRule - unexpected time operator value" );
301 maModel.mnType = XML_timePeriod;
302 maModel.mnTimePeriod = XML_last7Days;
303 break;
304 case BIFF12_CFRULE_SUB_LASTMONTH:
305 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_LASTMONTH, "CondFormatRule::importCfRule - unexpected time operator value" );
306 maModel.mnType = XML_timePeriod;
307 maModel.mnTimePeriod = XML_lastMonth;
308 break;
309 case BIFF12_CFRULE_SUB_NEXTMONTH:
310 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_NEXTMONTH, "CondFormatRule::importCfRule - unexpected time operator value" );
311 maModel.mnType = XML_timePeriod;
312 maModel.mnTimePeriod = XML_nextMonth;
313 break;
314 case BIFF12_CFRULE_SUB_THISWEEK:
315 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_THISWEEK, "CondFormatRule::importCfRule - unexpected time operator value" );
316 maModel.mnType = XML_timePeriod;
317 maModel.mnTimePeriod = XML_thisWeek;
318 break;
319 case BIFF12_CFRULE_SUB_NEXTWEEK:
320 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_NEXTWEEK, "CondFormatRule::importCfRule - unexpected time operator value" );
321 maModel.mnType = XML_timePeriod;
322 maModel.mnTimePeriod = XML_nextWeek;
323 break;
324 case BIFF12_CFRULE_SUB_LASTWEEK:
325 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_LASTWEEK, "CondFormatRule::importCfRule - unexpected time operator value" );
326 maModel.mnType = XML_timePeriod;
327 maModel.mnTimePeriod = XML_lastWeek;
328 break;
329 case BIFF12_CFRULE_SUB_THISMONTH:
330 OSL_ENSURE( nOperator == BIFF12_CFRULE_TIMEOP_THISMONTH, "CondFormatRule::importCfRule - unexpected time operator value" );
331 maModel.mnType = XML_timePeriod;
332 maModel.mnTimePeriod = XML_thisMonth;
333 break;
334 case BIFF12_CFRULE_SUB_ABOVEAVERAGE:
335 OSL_ENSURE( maModel.mbAboveAverage, "CondFormatRule::importCfRule - wrong above-average flag" );
336 maModel.mnType = XML_aboveAverage;
337 maModel.mnStdDev = nOperator; // operator field used for standard deviation
338 maModel.mbAboveAverage = true;
339 maModel.mbEqualAverage = false; // does not exist as real flag...
340 break;
341 case BIFF12_CFRULE_SUB_BELOWAVERAGE:
342 OSL_ENSURE( !maModel.mbAboveAverage, "CondFormatRule::importCfRule - wrong above-average flag" );
343 maModel.mnType = XML_aboveAverage;
344 maModel.mnStdDev = nOperator; // operator field used for standard deviation
345 maModel.mbAboveAverage = false;
346 maModel.mbEqualAverage = false; // does not exist as real flag...
347 break;
348 case BIFF12_CFRULE_SUB_DUPLICATE:
349 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
350 maModel.mnType = XML_duplicateValues;
351 break;
352 case BIFF12_CFRULE_SUB_EQABOVEAVERAGE:
353 OSL_ENSURE( maModel.mbAboveAverage, "CondFormatRule::importCfRule - wrong above-average flag" );
354 maModel.mnType = XML_aboveAverage;
355 maModel.mnStdDev = nOperator; // operator field used for standard deviation
356 maModel.mbAboveAverage = true;
357 maModel.mbEqualAverage = true; // does not exist as real flag...
358 break;
359 case BIFF12_CFRULE_SUB_EQBELOWAVERAGE:
360 OSL_ENSURE( !maModel.mbAboveAverage, "CondFormatRule::importCfRule - wrong above-average flag" );
361 maModel.mnType = XML_aboveAverage;
362 maModel.mnStdDev = nOperator; // operator field used for standard deviation
363 maModel.mbAboveAverage = false;
364 maModel.mbEqualAverage = true; // does not exist as real flag...
365 break;
366 }
367 break;
368 case BIFF12_CFRULE_TYPE_COLORSCALE:
369 OSL_ENSURE( nSubType == BIFF12_CFRULE_SUB_COLORSCALE, "CondFormatRule::importCfRule - rule type/subtype mismatch" );
370 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
371 maModel.mnType = XML_colorScale;
372 break;
373 case BIFF12_CFRULE_TYPE_DATABAR:
374 OSL_ENSURE( nSubType == BIFF12_CFRULE_SUB_DATABAR, "CondFormatRule::importCfRule - rule type/subtype mismatch" );
375 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
376 maModel.mnType = XML_dataBar;
377 break;
378 case BIFF12_CFRULE_TYPE_TOPTEN:
379 OSL_ENSURE( nSubType == BIFF12_CFRULE_SUB_TOPTEN, "CondFormatRule::importCfRule - rule type/subtype mismatch" );
380 maModel.mnType = XML_top10;
381 maModel.mnRank = nOperator; // operator field used for rank value
382 break;
383 case BIFF12_CFRULE_TYPE_ICONSET:
384 OSL_ENSURE( nSubType == BIFF12_CFRULE_SUB_ICONSET, "CondFormatRule::importCfRule - rule type/subtype mismatch" );
385 OSL_ENSURE( nOperator == 0, "CondFormatRule::importCfRule - unexpected operator value" );
386 maModel.mnType = XML_iconSet;
387 break;
388 default:
389 OSL_ENSURE( false, "CondFormatRule::importCfRule - unknown rule type" );
390 }
391 }
392
importCfRule(BiffInputStream & rStrm,sal_Int32 nPriority)393 void CondFormatRule::importCfRule( BiffInputStream& rStrm, sal_Int32 nPriority )
394 {
395 sal_uInt8 nType, nOperator;
396 sal_uInt16 nFmla1Size, nFmla2Size;
397 sal_uInt32 nFlags;
398 rStrm >> nType >> nOperator >> nFmla1Size >> nFmla2Size >> nFlags;
399 rStrm.skip( 2 );
400
401 static const sal_Int32 spnTypeIds[] = { XML_TOKEN_INVALID, XML_cellIs, XML_expression };
402 maModel.mnType = STATIC_ARRAY_SELECT( spnTypeIds, nType, XML_TOKEN_INVALID );
403
404 maModel.setBiffOperator( nOperator );
405 maModel.mnPriority = nPriority;
406 maModel.mbStopIfTrue = true;
407
408 DxfRef xDxf = getStyles().createDxf( &maModel.mnDxfId );
409 xDxf->importCfRule( rStrm, nFlags );
410 xDxf->finalizeImport();
411
412 // import the formulas
413 OSL_ENSURE( (nFmla1Size > 0) || (nFmla2Size == 0), "CondFormatRule::importCfRule - missing first formula" );
414 if( nFmla1Size > 0 )
415 {
416 CellAddress aBaseAddr = mrCondFormat.getRanges().getBaseAddress();
417 ApiTokenSequence aTokens = getFormulaParser().importFormula( aBaseAddr, FORMULATYPE_CONDFORMAT, rStrm, &nFmla1Size );
418 maModel.maFormulas.push_back( aTokens );
419 if( nFmla2Size > 0 )
420 {
421 aTokens = getFormulaParser().importFormula( aBaseAddr, FORMULATYPE_CONDFORMAT, rStrm, &nFmla2Size );
422 maModel.maFormulas.push_back( aTokens );
423 }
424 }
425 }
426
finalizeImport(const Reference<XSheetConditionalEntries> & rxEntries)427 void CondFormatRule::finalizeImport( const Reference< XSheetConditionalEntries >& rxEntries )
428 {
429 ConditionOperator eOperator = ConditionOperator_NONE;
430
431 /* Replacement formula for unsupported rule types (text comparison rules,
432 time period rules, cell type rules). The replacement formulas below may
433 contain several placeholders:
434 - '#B' will be replaced by the current relative base address (may occur
435 several times).
436 - '#R' will be replaced by the entire range list of the conditional
437 formatting (absolute addresses).
438 - '#T' will be replaced by the quoted comparison text.
439 - '#L' will be replaced by the length of the comparison text (from
440 the 'text' attribute) used in text comparison rules.
441 - '#K' will be replaced by the rank (from the 'rank' attribute) used in
442 top-10 rules.
443 - '#M' will be replaced by the top/bottom flag (from the 'bottom'
444 attribute) used in the RANK function in top-10 rules.
445 - '#C' will be replaced by one of the comparison operators <, >, <=, or
446 >=, according to the 'aboveAverage' and 'equalAverage' flags.
447 */
448 OUString aReplaceFormula;
449
450 switch( maModel.mnType )
451 {
452 case XML_cellIs:
453 eOperator = CondFormatBuffer::convertToApiOperator( maModel.mnOperator );
454 break;
455 case XML_expression:
456 eOperator = ConditionOperator_FORMULA;
457 break;
458 case XML_containsText:
459 OSL_ENSURE( maModel.mnOperator == XML_containsText, "CondFormatRule::finalizeImport - unexpected operator" );
460 aReplaceFormula = CREATE_OUSTRING( "NOT(ISERROR(SEARCH(#T,#B)))" );
461 break;
462 case XML_notContainsText:
463 // note: type XML_notContainsText vs. operator XML_notContains
464 OSL_ENSURE( maModel.mnOperator == XML_notContains, "CondFormatRule::finalizeImport - unexpected operator" );
465 aReplaceFormula = CREATE_OUSTRING( "ISERROR(SEARCH(#T,#B))" );
466 break;
467 case XML_beginsWith:
468 OSL_ENSURE( maModel.mnOperator == XML_beginsWith, "CondFormatRule::finalizeImport - unexpected operator" );
469 aReplaceFormula = CREATE_OUSTRING( "LEFT(#B,#L)=#T" );
470 break;
471 case XML_endsWith:
472 OSL_ENSURE( maModel.mnOperator == XML_endsWith, "CondFormatRule::finalizeImport - unexpected operator" );
473 aReplaceFormula = CREATE_OUSTRING( "RIGHT(#B,#L)=#T" );
474 break;
475 case XML_timePeriod:
476 switch( maModel.mnTimePeriod )
477 {
478 case XML_yesterday:
479 aReplaceFormula = CREATE_OUSTRING( "FLOOR(#B,1)=TODAY()-1" );
480 break;
481 case XML_today:
482 aReplaceFormula = CREATE_OUSTRING( "FLOOR(#B,1)=TODAY()" );
483 break;
484 case XML_tomorrow:
485 aReplaceFormula = CREATE_OUSTRING( "FLOOR(#B,1)=TODAY()+1" );
486 break;
487 case XML_last7Days:
488 aReplaceFormula = CREATE_OUSTRING( "AND(TODAY()-7<FLOOR(#B,1),FLOOR(#B,1)<=TODAY())" );
489 break;
490 case XML_lastWeek:
491 aReplaceFormula = CREATE_OUSTRING( "AND(TODAY()-WEEKDAY(TODAY())-7<FLOOR(#B,1),FLOOR(#B,1)<=TODAY()-WEEKDAY(TODAY()))" );
492 break;
493 case XML_thisWeek:
494 aReplaceFormula = CREATE_OUSTRING( "AND(TODAY()-WEEKDAY(TODAY())<FLOOR(#B,1),FLOOR(#B,1)<=TODAY()-WEEKDAY(TODAY())+7)" );
495 break;
496 case XML_nextWeek:
497 aReplaceFormula = CREATE_OUSTRING( "AND(TODAY()-WEEKDAY(TODAY())+7<FLOOR(#B,1),FLOOR(#B,1)<=TODAY()-WEEKDAY(TODAY())+14)" );
498 break;
499 case XML_lastMonth:
500 aReplaceFormula = CREATE_OUSTRING( "OR(AND(MONTH(#B)=MONTH(TODAY())-1,YEAR(#B)=YEAR(TODAY())),AND(MONTH(#B)=12,MONTH(TODAY())=1,YEAR(#B)=YEAR(TODAY())-1))" );
501 break;
502 case XML_thisMonth:
503 aReplaceFormula = CREATE_OUSTRING( "AND(MONTH(#B)=MONTH(TODAY()),YEAR(#B)=YEAR(TODAY()))" );
504 break;
505 case XML_nextMonth:
506 aReplaceFormula = CREATE_OUSTRING( "OR(AND(MONTH(#B)=MONTH(TODAY())+1,YEAR(#B)=YEAR(TODAY())),AND(MONTH(#B)=1,MONTH(TODAY())=12,YEAR(#B)=YEAR(TODAY())+1))" );
507 break;
508 default:
509 OSL_ENSURE( false, "CondFormatRule::finalizeImport - unknown time period type" );
510 }
511 break;
512 case XML_containsBlanks:
513 aReplaceFormula = CREATE_OUSTRING( "LEN(TRIM(#B))=0" );
514 break;
515 case XML_notContainsBlanks:
516 aReplaceFormula = CREATE_OUSTRING( "LEN(TRIM(#B))>0" );
517 break;
518 case XML_containsErrors:
519 aReplaceFormula = CREATE_OUSTRING( "ISERROR(#B)" );
520 break;
521 case XML_notContainsErrors:
522 aReplaceFormula = CREATE_OUSTRING( "NOT(ISERROR(#B))" );
523 break;
524 case XML_top10:
525 if( maModel.mbPercent )
526 aReplaceFormula = CREATE_OUSTRING( "RANK(#B,#R,#M)/COUNT(#R)<=#K%" );
527 else
528 aReplaceFormula = CREATE_OUSTRING( "RANK(#B,#R,#M)<=#K" );
529 break;
530 case XML_aboveAverage:
531 if( maModel.mnStdDev == 0 )
532 aReplaceFormula = CREATE_OUSTRING( "#B#CAVERAGE(#R)" );
533 break;
534 }
535
536 if( aReplaceFormula.getLength() > 0 )
537 {
538 OUString aAddress, aRanges, aText, aComp;
539 sal_Int32 nStrPos = aReplaceFormula.getLength();
540 while( (nStrPos = aReplaceFormula.lastIndexOf( '#', nStrPos )) >= 0 )
541 {
542 switch( aReplaceFormula[ nStrPos + 1 ] )
543 {
544 case 'B': // current base address
545 if( aAddress.getLength() == 0 )
546 aAddress = FormulaProcessorBase::generateAddress2dString( mrCondFormat.getRanges().getBaseAddress(), false );
547 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2, aAddress );
548 break;
549 case 'R': // range list of conditional formatting
550 if( aRanges.getLength() == 0 )
551 aRanges = FormulaProcessorBase::generateRangeList2dString( mrCondFormat.getRanges(), true, ',', true );
552 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2, aRanges );
553 break;
554 case 'T': // comparison text
555 if( aText.getLength() == 0 )
556 // quote the comparison text, and handle embedded quote characters
557 aText = FormulaProcessorBase::generateApiString( maModel.maText );
558 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2, aText );
559 break;
560 case 'L': // length of comparison text
561 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2,
562 OUString::valueOf( maModel.maText.getLength() ) );
563 break;
564 case 'K': // top-10 rank
565 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2,
566 OUString::valueOf( maModel.mnRank ) );
567 break;
568 case 'M': // top-10 top/bottom flag
569 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2,
570 OUString::valueOf( static_cast< sal_Int32 >( maModel.mbBottom ? 1 : 0 ) ) );
571 break;
572 case 'C': // average comparison operator
573 if( aComp.getLength() == 0 )
574 aComp = maModel.mbAboveAverage ?
575 (maModel.mbEqualAverage ? CREATE_OUSTRING( ">=" ) : CREATE_OUSTRING( ">" )) :
576 (maModel.mbEqualAverage ? CREATE_OUSTRING( "<=" ) : CREATE_OUSTRING( "<" ));
577 aReplaceFormula = aReplaceFormula.replaceAt( nStrPos, 2, aComp );
578 break;
579 default:
580 OSL_ENSURE( false, "CondFormatRule::finalizeImport - unknown placeholder" );
581 }
582 }
583
584 // set the replacement formula
585 maModel.maFormulas.clear();
586 appendFormula( aReplaceFormula );
587 eOperator = ConditionOperator_FORMULA;
588 }
589
590 if( rxEntries.is() && (eOperator != ConditionOperator_NONE) && !maModel.maFormulas.empty() )
591 {
592 ::std::vector< PropertyValue > aProps;
593 // create condition properties
594 lclAppendProperty( aProps, CREATE_OUSTRING( "Operator" ), eOperator );
595 lclAppendProperty( aProps, CREATE_OUSTRING( "Formula1" ), maModel.maFormulas[ 0 ] );
596 if( maModel.maFormulas.size() >= 2 )
597 lclAppendProperty( aProps, CREATE_OUSTRING( "Formula2" ), maModel.maFormulas[ 1 ] );
598
599 // style name for the formatting attributes
600 OUString aStyleName = getStyles().createDxfStyle( maModel.mnDxfId );
601 if( aStyleName.getLength() > 0 )
602 lclAppendProperty( aProps, CREATE_OUSTRING( "StyleName" ), aStyleName );
603
604 // append the new rule
605 try
606 {
607 rxEntries->addNew( ContainerHelper::vectorToSequence( aProps ) );
608 }
609 catch( Exception& )
610 {
611 }
612 }
613 }
614
615 // ============================================================================
616
CondFormatModel()617 CondFormatModel::CondFormatModel() :
618 mbPivot( false )
619 {
620 }
621
622 // ============================================================================
623
CondFormat(const WorksheetHelper & rHelper)624 CondFormat::CondFormat( const WorksheetHelper& rHelper ) :
625 WorksheetHelper( rHelper )
626 {
627 }
628
importConditionalFormatting(const AttributeList & rAttribs)629 void CondFormat::importConditionalFormatting( const AttributeList& rAttribs )
630 {
631 getAddressConverter().convertToCellRangeList( maModel.maRanges, rAttribs.getString( XML_sqref, OUString() ), getSheetIndex(), true );
632 maModel.mbPivot = rAttribs.getBool( XML_pivot, false );
633 }
634
importCfRule(const AttributeList & rAttribs)635 CondFormatRuleRef CondFormat::importCfRule( const AttributeList& rAttribs )
636 {
637 CondFormatRuleRef xRule = createRule();
638 xRule->importCfRule( rAttribs );
639 insertRule( xRule );
640 return xRule;
641 }
642
importCondFormatting(SequenceInputStream & rStrm)643 void CondFormat::importCondFormatting( SequenceInputStream& rStrm )
644 {
645 BinRangeList aRanges;
646 rStrm.skip( 8 );
647 rStrm >> aRanges;
648 getAddressConverter().convertToCellRangeList( maModel.maRanges, aRanges, getSheetIndex(), true );
649 }
650
importCfRule(SequenceInputStream & rStrm)651 void CondFormat::importCfRule( SequenceInputStream& rStrm )
652 {
653 CondFormatRuleRef xRule = createRule();
654 xRule->importCfRule( rStrm );
655 insertRule( xRule );
656 }
657
importCfHeader(BiffInputStream & rStrm)658 void CondFormat::importCfHeader( BiffInputStream& rStrm )
659 {
660 // import the CFHEADER record
661 sal_uInt16 nRuleCount;
662 BinRangeList aRanges;
663 rStrm >> nRuleCount;
664 rStrm.skip( 10 );
665 rStrm >> aRanges;
666 getAddressConverter().convertToCellRangeList( maModel.maRanges, aRanges, getSheetIndex(), true );
667
668 // import following list of CFRULE records
669 for( sal_uInt16 nRule = 0; (nRule < nRuleCount) && (rStrm.getNextRecId() == BIFF_ID_CFRULE) && rStrm.startNextRecord(); ++nRule )
670 {
671 CondFormatRuleRef xRule = createRule();
672 xRule->importCfRule( rStrm, nRule + 1 );
673 insertRule( xRule );
674 }
675 }
676
finalizeImport()677 void CondFormat::finalizeImport()
678 {
679 try
680 {
681 Reference< XSheetCellRanges > xRanges( getCellRangeList( maModel.maRanges ), UNO_SET_THROW );
682 PropertySet aPropSet( xRanges );
683 Reference< XSheetConditionalEntries > xEntries( aPropSet.getAnyProperty( PROP_ConditionalFormat ), UNO_QUERY_THROW );
684 // maRules is sorted by rule priority
685 maRules.forEachMem( &CondFormatRule::finalizeImport, ::boost::cref( xEntries ) );
686 aPropSet.setProperty( PROP_ConditionalFormat, xEntries );
687 }
688 catch( Exception& )
689 {
690 }
691 }
692
createRule()693 CondFormatRuleRef CondFormat::createRule()
694 {
695 return CondFormatRuleRef( new CondFormatRule( *this ) );
696 }
697
insertRule(CondFormatRuleRef xRule)698 void CondFormat::insertRule( CondFormatRuleRef xRule )
699 {
700 if( xRule.get() && (xRule->getPriority() > 0) )
701 {
702 OSL_ENSURE( maRules.find( xRule->getPriority() ) == maRules.end(), "CondFormat::insertRule - multiple rules with equal priority" );
703 maRules[ xRule->getPriority() ] = xRule;
704 }
705 }
706
707 // ============================================================================
708
CondFormatBuffer(const WorksheetHelper & rHelper)709 CondFormatBuffer::CondFormatBuffer( const WorksheetHelper& rHelper ) :
710 WorksheetHelper( rHelper )
711 {
712 }
713
importConditionalFormatting(const AttributeList & rAttribs)714 CondFormatRef CondFormatBuffer::importConditionalFormatting( const AttributeList& rAttribs )
715 {
716 CondFormatRef xCondFmt = createCondFormat();
717 xCondFmt->importConditionalFormatting( rAttribs );
718 return xCondFmt;
719 }
720
importCondFormatting(SequenceInputStream & rStrm)721 CondFormatRef CondFormatBuffer::importCondFormatting( SequenceInputStream& rStrm )
722 {
723 CondFormatRef xCondFmt = createCondFormat();
724 xCondFmt->importCondFormatting( rStrm );
725 return xCondFmt;
726 }
727
importCfHeader(BiffInputStream & rStrm)728 void CondFormatBuffer::importCfHeader( BiffInputStream& rStrm )
729 {
730 createCondFormat()->importCfHeader( rStrm );
731 }
732
finalizeImport()733 void CondFormatBuffer::finalizeImport()
734 {
735 maCondFormats.forEachMem( &CondFormat::finalizeImport );
736 }
737
convertToApiOperator(sal_Int32 nToken)738 ConditionOperator CondFormatBuffer::convertToApiOperator( sal_Int32 nToken )
739 {
740 switch( nToken )
741 {
742 case XML_between: return ConditionOperator_BETWEEN;
743 case XML_equal: return ConditionOperator_EQUAL;
744 case XML_greaterThan: return ConditionOperator_GREATER;
745 case XML_greaterThanOrEqual: return ConditionOperator_GREATER_EQUAL;
746 case XML_lessThan: return ConditionOperator_LESS;
747 case XML_lessThanOrEqual: return ConditionOperator_LESS_EQUAL;
748 case XML_notBetween: return ConditionOperator_NOT_BETWEEN;
749 case XML_notEqual: return ConditionOperator_NOT_EQUAL;
750 }
751 return ConditionOperator_NONE;
752 }
753
754 // private --------------------------------------------------------------------
755
createCondFormat()756 CondFormatRef CondFormatBuffer::createCondFormat()
757 {
758 CondFormatRef xCondFmt( new CondFormat( *this ) );
759 maCondFormats.push_back( xCondFmt );
760 return xCondFmt;
761 }
762
763 // ============================================================================
764
765 } // namespace xls
766 } // namespace oox
767