xref: /trunk/main/oox/source/xls/condformatbuffer.cxx (revision ca5ec200)
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