oox/source/xls/condformatbuffer.cxx

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