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