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