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 > 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 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 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 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 167 CondFormatRule::CondFormatRule( const CondFormat& rCondFormat ) : 168 WorksheetHelper( rCondFormat ), 169 mrCondFormat( rCondFormat ) 170 { 171 } 172 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 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 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 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 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 617 CondFormatModel::CondFormatModel() : 618 mbPivot( false ) 619 { 620 } 621 622 // ============================================================================ 623 624 CondFormat::CondFormat( const WorksheetHelper& rHelper ) : 625 WorksheetHelper( rHelper ) 626 { 627 } 628 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 635 CondFormatRuleRef CondFormat::importCfRule( const AttributeList& rAttribs ) 636 { 637 CondFormatRuleRef xRule = createRule(); 638 xRule->importCfRule( rAttribs ); 639 insertRule( xRule ); 640 return xRule; 641 } 642 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 651 void CondFormat::importCfRule( SequenceInputStream& rStrm ) 652 { 653 CondFormatRuleRef xRule = createRule(); 654 xRule->importCfRule( rStrm ); 655 insertRule( xRule ); 656 } 657 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 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 693 CondFormatRuleRef CondFormat::createRule() 694 { 695 return CondFormatRuleRef( new CondFormatRule( *this ) ); 696 } 697 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 709 CondFormatBuffer::CondFormatBuffer( const WorksheetHelper& rHelper ) : 710 WorksheetHelper( rHelper ) 711 { 712 } 713 714 CondFormatRef CondFormatBuffer::importConditionalFormatting( const AttributeList& rAttribs ) 715 { 716 CondFormatRef xCondFmt = createCondFormat(); 717 xCondFmt->importConditionalFormatting( rAttribs ); 718 return xCondFmt; 719 } 720 721 CondFormatRef CondFormatBuffer::importCondFormatting( SequenceInputStream& rStrm ) 722 { 723 CondFormatRef xCondFmt = createCondFormat(); 724 xCondFmt->importCondFormatting( rStrm ); 725 return xCondFmt; 726 } 727 728 void CondFormatBuffer::importCfHeader( BiffInputStream& rStrm ) 729 { 730 createCondFormat()->importCfHeader( rStrm ); 731 } 732 733 void CondFormatBuffer::finalizeImport() 734 { 735 maCondFormats.forEachMem( &CondFormat::finalizeImport ); 736 } 737 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 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