1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ 2 /* 3 * This file is part of the LibreOffice project. 4 * 5 * This Source Code Form is subject to the terms of the Mozilla Public 6 * License, v. 2.0. If a copy of the MPL was not distributed with this 7 * file, You can obtain one at http://mozilla.org/MPL/2.0/. 8 * 9 * This file incorporates work covered by the following license notice: 10 * 11 * Licensed to the Apache Software Foundation (ASF) under one or more 12 * contributor license agreements. See the NOTICE file distributed 13 * with this work for additional information regarding copyright 14 * ownership. The ASF licenses this file to you under the Apache 15 * License, Version 2.0 (the "License"); you may not use this file 16 * except in compliance with the License. You may obtain a copy of 17 * the License at http://www.apache.org/licenses/LICENSE-2.0 . 18 */ 19 20 #include "vbarange.hxx" 21 22 #include <comphelper/types.hxx> 23 #include <cppuhelper/exc_hlp.hxx> 24 #include <o3tl/any.hxx> 25 #include <o3tl/safeint.hxx> 26 #include <o3tl/unit_conversion.hxx> 27 #include <rtl/math.hxx> 28 #include <tools/diagnose_ex.h> 29 #include <o3tl/string_view.hxx> 30 31 #include <com/sun/star/script/ArrayWrapper.hpp> 32 #include <com/sun/star/script/XTypeConverter.hpp> 33 #include <com/sun/star/script/vba/VBAEventId.hpp> 34 #include <com/sun/star/script/vba/XVBAEventProcessor.hpp> 35 #include <com/sun/star/sheet/XDatabaseRange.hpp> 36 #include <com/sun/star/sheet/XUnnamedDatabaseRanges.hpp> 37 #include <com/sun/star/sheet/XGoalSeek.hpp> 38 #include <com/sun/star/sheet/XSheetOperation.hpp> 39 #include <com/sun/star/sheet/CellFlags.hpp> 40 #include <com/sun/star/table/XColumnRowRange.hpp> 41 #include <com/sun/star/sheet/XCellAddressable.hpp> 42 #include <com/sun/star/table/CellContentType.hpp> 43 #include <com/sun/star/sheet/XCellSeries.hpp> 44 #include <com/sun/star/text/XTextRange.hpp> 45 #include <com/sun/star/sheet/XCellRangeAddressable.hpp> 46 #include <com/sun/star/table/CellAddress.hpp> 47 #include <com/sun/star/table/CellRangeAddress.hpp> 48 #include <com/sun/star/sheet/XSpreadsheetView.hpp> 49 #include <com/sun/star/sheet/XCellRangeReferrer.hpp> 50 #include <com/sun/star/sheet/XSheetCellRange.hpp> 51 #include <com/sun/star/sheet/XSpreadsheet.hpp> 52 #include <com/sun/star/sheet/XSheetCellCursor.hpp> 53 #include <com/sun/star/sheet/XArrayFormulaRange.hpp> 54 #include <com/sun/star/sheet/XNamedRange.hpp> 55 #include <com/sun/star/sheet/XNamedRanges.hpp> 56 #include <com/sun/star/sheet/XPrintAreas.hpp> 57 #include <com/sun/star/sheet/XCellRangesQuery.hpp> 58 #include <com/sun/star/beans/XPropertySet.hpp> 59 #include <com/sun/star/frame/XModel.hpp> 60 #include <com/sun/star/view/XSelectionSupplier.hpp> 61 #include <com/sun/star/table/XTableRows.hpp> 62 #include <com/sun/star/table/XTableColumns.hpp> 63 #include <com/sun/star/table/TableSortField.hpp> 64 #include <com/sun/star/util/XMergeable.hpp> 65 #include <com/sun/star/uno/XComponentContext.hpp> 66 #include <com/sun/star/lang/WrappedTargetRuntimeException.hpp> 67 #include <com/sun/star/util/XNumberFormatsSupplier.hpp> 68 #include <com/sun/star/util/XNumberFormats.hpp> 69 #include <com/sun/star/util/NumberFormat.hpp> 70 #include <com/sun/star/util/XNumberFormatTypes.hpp> 71 #include <com/sun/star/util/XReplaceable.hpp> 72 #include <com/sun/star/util/XSortable.hpp> 73 #include <com/sun/star/sheet/XCellRangeMovement.hpp> 74 #include <com/sun/star/sheet/FormulaResult.hpp> 75 #include <com/sun/star/sheet/FilterOperator2.hpp> 76 #include <com/sun/star/sheet/TableFilterField2.hpp> 77 #include <com/sun/star/sheet/XSheetFilterDescriptor2.hpp> 78 #include <com/sun/star/sheet/FilterConnection.hpp> 79 #include <com/sun/star/util/TriState.hpp> 80 81 #include <com/sun/star/sheet/XSubTotalCalculatable.hpp> 82 #include <com/sun/star/sheet/XSubTotalDescriptor.hpp> 83 #include <com/sun/star/sheet/GeneralFunction.hpp> 84 85 #include <com/sun/star/sheet/XSheetAnnotationsSupplier.hpp> 86 #include <com/sun/star/sheet/XSheetAnnotations.hpp> 87 88 #include <ooo/vba/excel/XlPasteSpecialOperation.hpp> 89 #include <ooo/vba/excel/XlPasteType.hpp> 90 #include <ooo/vba/excel/XlFindLookIn.hpp> 91 #include <ooo/vba/excel/XlLookAt.hpp> 92 #include <ooo/vba/excel/XlSearchOrder.hpp> 93 #include <ooo/vba/excel/XlSortOrder.hpp> 94 #include <ooo/vba/excel/XlYesNoGuess.hpp> 95 #include <ooo/vba/excel/XlSortOrientation.hpp> 96 #include <ooo/vba/excel/XlSortMethod.hpp> 97 #include <ooo/vba/excel/XlDirection.hpp> 98 #include <ooo/vba/excel/XlSortDataOption.hpp> 99 #include <ooo/vba/excel/XlDeleteShiftDirection.hpp> 100 #include <ooo/vba/excel/XlInsertShiftDirection.hpp> 101 #include <ooo/vba/excel/XlReferenceStyle.hpp> 102 #include <ooo/vba/excel/XlBordersIndex.hpp> 103 #include <ooo/vba/excel/XlPageBreak.hpp> 104 #include <ooo/vba/excel/XlAutoFilterOperator.hpp> 105 #include <ooo/vba/excel/XlAutoFillType.hpp> 106 #include <ooo/vba/excel/XlCellType.hpp> 107 #include <ooo/vba/excel/XlSpecialCellsValue.hpp> 108 #include <ooo/vba/excel/XlConsolidationFunction.hpp> 109 #include <ooo/vba/excel/XlSearchDirection.hpp> 110 111 #include <scitems.hxx> 112 #include <svl/srchitem.hxx> 113 #include <cellsuno.hxx> 114 #include <dbdata.hxx> 115 #include <docfunc.hxx> 116 #include <columnspanset.hxx> 117 #include <queryparam.hxx> 118 #include <sortparam.hxx> 119 120 #include <sfx2/dispatch.hxx> 121 #include <sfx2/app.hxx> 122 #include <sfx2/bindings.hxx> 123 #include <sfx2/viewfrm.hxx> 124 #include <sc.hrc> 125 #include <unonames.hxx> 126 127 #include "excelvbahelper.hxx" 128 #include "vbaapplication.hxx" 129 #include "vbafont.hxx" 130 #include "vbacomment.hxx" 131 #include "vbainterior.hxx" 132 #include "vbacharacters.hxx" 133 #include "vbaborders.hxx" 134 #include "vbaworksheet.hxx" 135 #include "vbavalidation.hxx" 136 #include "vbahyperlinks.hxx" 137 138 #include <tabvwsh.hxx> 139 #include <rangelst.hxx> 140 #include <convuno.hxx> 141 #include <compiler.hxx> 142 #include <patattr.hxx> 143 #include <olinetab.hxx> 144 #include <transobj.hxx> 145 #include <queryentry.hxx> 146 #include <markdata.hxx> 147 #include <basic/sberrors.hxx> 148 #include <cppuhelper/implbase.hxx> 149 150 #include <global.hxx> 151 152 #include "vbastyle.hxx" 153 #include "vbaname.hxx" 154 #include <vector> 155 #include <vbahelper/vbacollectionimpl.hxx> 156 157 #include <com/sun/star/bridge/oleautomation/Date.hpp> 158 #include <tokenarray.hxx> 159 #include <tokenuno.hxx> 160 161 #include <memory> 162 163 using namespace ::ooo::vba; 164 using namespace ::com::sun::star; 165 using ::std::vector; 166 167 // difference between VBA and file format width, in character units 168 const double fExtraWidth = 182.0 / 256.0; 169 170 const sal_Int16 supportedIndexTable[] = { excel::XlBordersIndex::xlEdgeLeft, excel::XlBordersIndex::xlEdgeTop, excel::XlBordersIndex::xlEdgeBottom, excel::XlBordersIndex::xlEdgeRight, excel::XlBordersIndex::xlDiagonalDown, excel::XlBordersIndex::xlDiagonalUp, excel::XlBordersIndex::xlInsideVertical, excel::XlBordersIndex::xlInsideHorizontal }; 171 172 static sal_uInt16 lcl_pointsToTwips( double nVal ) 173 { 174 nVal = nVal * static_cast<double>(20); 175 short nTwips = static_cast<short>(nVal); 176 return nTwips; 177 } 178 static double lcl_TwipsToPoints( sal_uInt16 nVal ) 179 { 180 double nPoints = nVal; 181 return nPoints / 20; 182 } 183 184 static double lcl_Round2DecPlaces( double nVal ) 185 { 186 nVal = (nVal * double(100)); 187 tools::Long tmp = static_cast<tools::Long>(nVal); 188 if ( ( nVal - tmp ) >= 0.5 ) 189 ++tmp; 190 nVal = double(tmp)/100; 191 return nVal; 192 } 193 194 static uno::Any lcl_makeRange( const uno::Reference< XHelperInterface >& rParent, const uno::Reference< uno::XComponentContext >& rContext, const uno::Any& rAny, bool bIsRows, bool bIsColumns ) 195 { 196 uno::Reference< table::XCellRange > xCellRange(rAny, uno::UNO_QUERY_THROW); 197 return uno::Any( uno::Reference< excel::XRange >( new ScVbaRange( rParent, rContext, xCellRange, bIsRows, bIsColumns ) ) ); 198 } 199 200 static uno::Reference< excel::XRange > lcl_makeXRangeFromSheetCellRanges( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< sheet::XSheetCellRanges >& xLocSheetCellRanges, ScDocShell* pDoc ) 201 { 202 uno::Reference< excel::XRange > xRange; 203 const uno::Sequence< table::CellRangeAddress > sAddresses = xLocSheetCellRanges->getRangeAddresses(); 204 ScRangeList aCellRanges; 205 if ( sAddresses.hasElements() ) 206 { 207 for ( const auto& rAddress : sAddresses ) 208 { 209 ScRange refRange; 210 ScUnoConversion::FillScRange( refRange, rAddress ); 211 aCellRanges.push_back( refRange ); 212 } 213 // Single range 214 if ( aCellRanges.size() == 1 ) 215 { 216 uno::Reference< table::XCellRange > xTmpRange( new ScCellRangeObj( pDoc, aCellRanges.front() ) ); 217 xRange = new ScVbaRange( xParent, xContext, xTmpRange ); 218 } 219 else 220 { 221 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pDoc, aCellRanges ) ); 222 xRange = new ScVbaRange( xParent, xContext, xRanges ); 223 } 224 } 225 return xRange; 226 } 227 228 ScCellRangesBase* ScVbaRange::getCellRangesBase() 229 { 230 if( mxRanges.is() ) 231 return comphelper::getFromUnoTunnel<ScCellRangesBase>( mxRanges ); 232 if( mxRange.is() ) 233 return comphelper::getFromUnoTunnel<ScCellRangesBase>( mxRange ); 234 throw uno::RuntimeException("General Error creating range - Unknown" ); 235 } 236 237 ScCellRangeObj* ScVbaRange::getCellRangeObj() 238 { 239 return dynamic_cast< ScCellRangeObj* >( getCellRangesBase() ); 240 } 241 242 SfxItemSet* ScVbaRange::getCurrentDataSet( ) 243 { 244 SfxItemSet* pDataSet = excel::ScVbaCellRangeAccess::GetDataSet( getCellRangesBase() ); 245 if ( !pDataSet ) 246 throw uno::RuntimeException("Can't access Itemset for range" ); 247 return pDataSet; 248 } 249 250 void ScVbaRange::fireChangeEvent() 251 { 252 if( !ScVbaApplication::getDocumentEventsEnabled() ) 253 return; 254 255 ScDocument& rDoc = getScDocument(); 256 const uno::Reference< script::vba::XVBAEventProcessor >& xVBAEvents = rDoc.GetVbaEventProcessor(); 257 if( xVBAEvents.is() ) try 258 { 259 uno::Sequence< uno::Any > aArgs{ uno::Any(uno::Reference< excel::XRange >( this )) }; 260 xVBAEvents->processVbaEvent( script::vba::VBAEventId::WORKSHEET_CHANGE, aArgs ); 261 } 262 catch( uno::Exception& ) 263 { 264 } 265 } 266 267 namespace { 268 269 class SingleRangeEnumeration : public EnumerationHelper_BASE 270 { 271 uno::Reference< table::XCellRange > m_xRange; 272 bool bHasMore; 273 public: 274 /// @throws uno::RuntimeException 275 explicit SingleRangeEnumeration( const uno::Reference< table::XCellRange >& xRange ) : m_xRange( xRange ), bHasMore( true ) { } 276 virtual sal_Bool SAL_CALL hasMoreElements( ) override { return bHasMore; } 277 virtual uno::Any SAL_CALL nextElement( ) override 278 { 279 if ( !bHasMore ) 280 throw container::NoSuchElementException(); 281 bHasMore = false; 282 return uno::Any( m_xRange ); 283 } 284 }; 285 286 // very simple class to pass to ScVbaCollectionBaseImpl containing 287 // just one item 288 289 class SingleRangeIndexAccess : public ::cppu::WeakImplHelper< container::XIndexAccess, 290 container::XEnumerationAccess > 291 { 292 private: 293 uno::Reference< table::XCellRange > m_xRange; 294 295 public: 296 explicit SingleRangeIndexAccess( const uno::Reference< table::XCellRange >& xRange ) : m_xRange( xRange ) {} 297 // XIndexAccess 298 virtual ::sal_Int32 SAL_CALL getCount() override { return 1; } 299 virtual uno::Any SAL_CALL getByIndex( ::sal_Int32 Index ) override 300 { 301 if ( Index != 0 ) 302 throw lang::IndexOutOfBoundsException(); 303 return uno::Any( m_xRange ); 304 } 305 // XElementAccess 306 virtual uno::Type SAL_CALL getElementType() override { return cppu::UnoType<table::XCellRange>::get(); } 307 virtual sal_Bool SAL_CALL hasElements() override { return true; } 308 // XEnumerationAccess 309 virtual uno::Reference< container::XEnumeration > SAL_CALL createEnumeration() override { return new SingleRangeEnumeration( m_xRange ); } 310 311 }; 312 313 class RangesEnumerationImpl : public EnumerationHelperImpl 314 { 315 bool mbIsRows; 316 bool mbIsColumns; 317 public: 318 /// @throws uno::RuntimeException 319 RangesEnumerationImpl( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< container::XEnumeration >& xEnumeration, bool bIsRows, bool bIsColumns ) : EnumerationHelperImpl( xParent, xContext, xEnumeration ), mbIsRows( bIsRows ), mbIsColumns( bIsColumns ) {} 320 virtual uno::Any SAL_CALL nextElement( ) override 321 { 322 return lcl_makeRange( m_xParent, m_xContext, m_xEnumeration->nextElement(), mbIsRows, mbIsColumns ); 323 } 324 }; 325 326 class ScVbaRangeAreas : public ScVbaCollectionBaseImpl 327 { 328 bool mbIsRows; 329 bool mbIsColumns; 330 public: 331 ScVbaRangeAreas( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< container::XIndexAccess >& xIndexAccess, bool bIsRows, bool bIsColumns ) : ScVbaCollectionBaseImpl( xParent, xContext, xIndexAccess ), mbIsRows( bIsRows ), mbIsColumns( bIsColumns ) {} 332 333 // XEnumerationAccess 334 virtual uno::Reference< container::XEnumeration > SAL_CALL createEnumeration() override; 335 336 // XElementAccess 337 virtual uno::Type SAL_CALL getElementType() override { return cppu::UnoType<excel::XRange>::get(); } 338 339 virtual uno::Any createCollectionObject( const uno::Any& aSource ) override; 340 341 virtual OUString getServiceImplName() override { return OUString(); } 342 343 virtual uno::Sequence< OUString > getServiceNames() override { return uno::Sequence< OUString >(); } 344 345 }; 346 347 } 348 349 uno::Reference< container::XEnumeration > SAL_CALL 350 ScVbaRangeAreas::createEnumeration() 351 { 352 uno::Reference< container::XEnumerationAccess > xEnumAccess( m_xIndexAccess, uno::UNO_QUERY_THROW ); 353 return new RangesEnumerationImpl( mxParent, mxContext, xEnumAccess->createEnumeration(), mbIsRows, mbIsColumns ); 354 } 355 356 uno::Any 357 ScVbaRangeAreas::createCollectionObject( const uno::Any& aSource ) 358 { 359 return lcl_makeRange( mxParent, mxContext, aSource, mbIsRows, mbIsColumns ); 360 } 361 362 // assume that xIf is in fact a ScCellRangesBase 363 /// @throws uno::RuntimeException 364 static ScDocShell* 365 getDocShellFromIf( const uno::Reference< uno::XInterface >& xIf ) 366 { 367 ScCellRangesBase* pUno = comphelper::getFromUnoTunnel<ScCellRangesBase>( xIf ); 368 if ( !pUno ) 369 throw uno::RuntimeException("Failed to access underlying uno range object" ); 370 return pUno->GetDocShell(); 371 } 372 373 /// @throws uno::RuntimeException 374 static ScDocShell* 375 getDocShellFromRange( const uno::Reference< table::XCellRange >& xRange ) 376 { 377 // need the ScCellRangesBase to get docshell 378 uno::Reference< uno::XInterface > xIf( xRange ); 379 return getDocShellFromIf(xIf ); 380 } 381 382 /// @throws uno::RuntimeException 383 static ScDocShell* 384 getDocShellFromRanges( const uno::Reference< sheet::XSheetCellRangeContainer >& xRanges ) 385 { 386 // need the ScCellRangesBase to get docshell 387 uno::Reference< uno::XInterface > xIf( xRanges ); 388 return getDocShellFromIf(xIf ); 389 } 390 391 /// @throws uno::RuntimeException 392 static uno::Reference< frame::XModel > getModelFromXIf( const uno::Reference< uno::XInterface >& xIf ) 393 { 394 ScDocShell* pDocShell = getDocShellFromIf(xIf ); 395 return pDocShell->GetModel(); 396 } 397 398 /// @throws uno::RuntimeException 399 static uno::Reference< frame::XModel > getModelFromRange( const uno::Reference< table::XCellRange >& xRange ) 400 { 401 // the XInterface for getImplementation can be any derived interface, no need for queryInterface 402 uno::Reference< uno::XInterface > xIf( xRange ); 403 return getModelFromXIf( xIf ); 404 } 405 406 static ScDocument& 407 getDocumentFromRange( const uno::Reference< table::XCellRange >& xRange ) 408 { 409 ScDocShell* pDocShell = getDocShellFromRange( xRange ); 410 if ( !pDocShell ) 411 throw uno::RuntimeException("Failed to access underlying docshell from uno range object" ); 412 ScDocument& rDoc = pDocShell->GetDocument(); 413 return rDoc; 414 } 415 416 ScDocument& 417 ScVbaRange::getScDocument() 418 { 419 if ( mxRanges.is() ) 420 { 421 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 422 uno::Reference< table::XCellRange > xRange( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW ); 423 return getDocumentFromRange( xRange ); 424 } 425 return getDocumentFromRange( mxRange ); 426 } 427 428 ScDocShell* 429 ScVbaRange::getScDocShell() 430 { 431 if ( mxRanges.is() ) 432 { 433 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 434 uno::Reference< table::XCellRange > xRange( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW ); 435 return getDocShellFromRange( xRange ); 436 } 437 return getDocShellFromRange( mxRange ); 438 } 439 440 ScVbaRange* ScVbaRange::getImplementation( const uno::Reference< excel::XRange >& rxRange ) 441 { 442 // FIXME: always save to use dynamic_cast? Or better to (implement and) use XTunnel? 443 return dynamic_cast< ScVbaRange* >( rxRange.get() ); 444 } 445 446 uno::Reference< frame::XModel > ScVbaRange::getUnoModel() 447 { 448 if( ScDocShell* pDocShell = getScDocShell() ) 449 return pDocShell->GetModel(); 450 throw uno::RuntimeException(); 451 } 452 453 uno::Reference< frame::XModel > ScVbaRange::getUnoModel( const uno::Reference< excel::XRange >& rxRange ) 454 { 455 if( ScVbaRange* pScVbaRange = getImplementation( rxRange ) ) 456 return pScVbaRange->getUnoModel(); 457 throw uno::RuntimeException(); 458 } 459 460 const ScRangeList& ScVbaRange::getScRangeList() 461 { 462 if( ScCellRangesBase* pScRangesBase = getCellRangesBase() ) 463 return pScRangesBase->GetRangeList(); 464 throw uno::RuntimeException("Cannot obtain UNO range implementation object" ); 465 } 466 467 const ScRangeList& ScVbaRange::getScRangeList( const uno::Reference< excel::XRange >& rxRange ) 468 { 469 if( ScVbaRange* pScVbaRange = getImplementation( rxRange ) ) 470 return pScVbaRange->getScRangeList(); 471 throw uno::RuntimeException("Cannot obtain VBA range implementation object" ); 472 } 473 474 namespace { 475 476 class NumFormatHelper 477 { 478 uno::Reference< util::XNumberFormatsSupplier > mxSupplier; 479 uno::Reference< beans::XPropertySet > mxRangeProps; 480 uno::Reference< util::XNumberFormats > mxFormats; 481 public: 482 explicit NumFormatHelper( const uno::Reference< table::XCellRange >& xRange ) 483 { 484 mxSupplier.set( getModelFromRange( xRange ), uno::UNO_QUERY_THROW ); 485 mxRangeProps.set( xRange, uno::UNO_QUERY_THROW); 486 mxFormats = mxSupplier->getNumberFormats(); 487 } 488 uno::Reference< beans::XPropertySet > getNumberProps() 489 { 490 tools::Long nIndexKey = 0; 491 uno::Any aValue = mxRangeProps->getPropertyValue( "NumberFormat" ); 492 aValue >>= nIndexKey; 493 494 if ( mxFormats.is() ) 495 return mxFormats->getByKey( nIndexKey ); 496 return uno::Reference< beans::XPropertySet > (); 497 } 498 499 bool isBooleanType() 500 { 501 502 return (getNumberFormat() & util::NumberFormat::LOGICAL) != 0; 503 } 504 505 bool isDateType() 506 { 507 sal_Int16 nType = getNumberFormat(); 508 return ( nType & util::NumberFormat::DATETIME ) != 0; 509 } 510 511 OUString getNumberFormatString() 512 { 513 uno::Reference< uno::XInterface > xIf( mxRangeProps, uno::UNO_QUERY_THROW ); 514 ScCellRangesBase* pUnoCellRange = comphelper::getFromUnoTunnel<ScCellRangesBase>( xIf ); 515 if ( pUnoCellRange ) 516 { 517 518 SfxItemSet* pDataSet = excel::ScVbaCellRangeAccess::GetDataSet( pUnoCellRange ); 519 SfxItemState eState = pDataSet->GetItemState( ATTR_VALUE_FORMAT); 520 // one of the cells in the range is not like the other ;-) 521 // so return a zero length format to indicate that 522 if ( eState == SfxItemState::DONTCARE ) 523 return OUString(); 524 } 525 526 uno::Reference< beans::XPropertySet > xNumberProps( getNumberProps(), uno::UNO_SET_THROW ); 527 OUString aFormatString; 528 uno::Any aString = xNumberProps->getPropertyValue( "FormatString" ); 529 aString >>= aFormatString; 530 return aFormatString; 531 } 532 533 sal_Int16 getNumberFormat() 534 { 535 uno::Reference< beans::XPropertySet > xNumberProps = getNumberProps(); 536 sal_Int16 nType = ::comphelper::getINT16( 537 xNumberProps->getPropertyValue( "Type" ) ); 538 return nType; 539 } 540 541 void setNumberFormat( const OUString& rFormat ) 542 { 543 // #163288# treat "General" as "Standard" format 544 sal_Int32 nNewIndex = 0; 545 if( !rFormat.equalsIgnoreAsciiCase( "General" ) ) 546 { 547 lang::Locale aLocale; 548 uno::Reference< beans::XPropertySet > xNumProps = getNumberProps(); 549 xNumProps->getPropertyValue( "Locale" ) >>= aLocale; 550 nNewIndex = mxFormats->queryKey( rFormat, aLocale, false ); 551 if ( nNewIndex == -1 ) // format not defined 552 nNewIndex = mxFormats->addNew( rFormat, aLocale ); 553 } 554 mxRangeProps->setPropertyValue( "NumberFormat", uno::Any( nNewIndex ) ); 555 } 556 557 void setNumberFormat( sal_Int16 nType ) 558 { 559 uno::Reference< beans::XPropertySet > xNumberProps = getNumberProps(); 560 lang::Locale aLocale; 561 xNumberProps->getPropertyValue( "Locale" ) >>= aLocale; 562 uno::Reference<util::XNumberFormatTypes> xTypes( mxFormats, uno::UNO_QUERY ); 563 if ( xTypes.is() ) 564 { 565 sal_Int32 nNewIndex = xTypes->getStandardFormat( nType, aLocale ); 566 mxRangeProps->setPropertyValue( "NumberFormat", uno::Any( nNewIndex ) ); 567 } 568 } 569 570 }; 571 572 struct CellPos 573 { 574 CellPos( sal_Int32 nRow, sal_Int32 nCol, sal_Int32 nArea ):m_nRow(nRow), m_nCol(nCol), m_nArea( nArea ) {}; 575 sal_Int32 m_nRow; 576 sal_Int32 m_nCol; 577 sal_Int32 m_nArea; 578 }; 579 580 } 581 582 typedef ::cppu::WeakImplHelper< container::XEnumeration > CellsEnumeration_BASE; 583 typedef ::std::vector< CellPos > vCellPos; 584 585 namespace { 586 587 // #FIXME - QUICK 588 // we could probably could and should modify CellsEnumeration below 589 // to handle rows and columns (but I do this separately for now 590 // and... this class only handles single areas (does it have to handle 591 // multi area ranges??) 592 class ColumnsRowEnumeration: public CellsEnumeration_BASE 593 { 594 uno::Reference< excel::XRange > mxRange; 595 sal_Int32 mMaxElems; 596 sal_Int32 mCurElem; 597 598 public: 599 ColumnsRowEnumeration( const uno::Reference< excel::XRange >& xRange, sal_Int32 nElems ) : mxRange( xRange ), mMaxElems( nElems ), mCurElem( 0 ) 600 { 601 } 602 603 virtual sal_Bool SAL_CALL hasMoreElements() override { return mCurElem < mMaxElems; } 604 605 virtual uno::Any SAL_CALL nextElement() override 606 { 607 if ( !hasMoreElements() ) 608 throw container::NoSuchElementException(); 609 sal_Int32 vbaIndex = 1 + mCurElem++; 610 return uno::Any( mxRange->Item( uno::Any( vbaIndex ), uno::Any() ) ); 611 } 612 }; 613 614 class CellsEnumeration : public CellsEnumeration_BASE 615 { 616 uno::WeakReference< XHelperInterface > mxParent; 617 uno::Reference< uno::XComponentContext > mxContext; 618 uno::Reference< XCollection > m_xAreas; 619 vCellPos m_CellPositions; 620 vCellPos::const_iterator m_it; 621 622 /// @throws uno::RuntimeException 623 uno::Reference< table::XCellRange > getArea( sal_Int32 nVBAIndex ) 624 { 625 if ( nVBAIndex < 1 || nVBAIndex > m_xAreas->getCount() ) 626 throw uno::RuntimeException(); 627 uno::Reference< excel::XRange > xRange( m_xAreas->Item( uno::Any(nVBAIndex), uno::Any() ), uno::UNO_QUERY_THROW ); 628 uno::Reference< table::XCellRange > xCellRange( ScVbaRange::getCellRange( xRange ), uno::UNO_QUERY_THROW ); 629 return xCellRange; 630 } 631 632 void populateArea( sal_Int32 nVBAIndex ) 633 { 634 uno::Reference< table::XCellRange > xRange = getArea( nVBAIndex ); 635 uno::Reference< table::XColumnRowRange > xColumnRowRange(xRange, uno::UNO_QUERY_THROW ); 636 sal_Int32 nRowCount = xColumnRowRange->getRows()->getCount(); 637 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount(); 638 for ( sal_Int32 i=0; i<nRowCount; ++i ) 639 { 640 for ( sal_Int32 j=0; j<nColCount; ++j ) 641 m_CellPositions.emplace_back( i,j,nVBAIndex ); 642 } 643 } 644 public: 645 CellsEnumeration( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< XCollection >& xAreas ): mxParent( xParent ), mxContext( xContext ), m_xAreas( xAreas ) 646 { 647 sal_Int32 nItems = m_xAreas->getCount(); 648 for ( sal_Int32 index=1; index <= nItems; ++index ) 649 { 650 populateArea( index ); 651 } 652 m_it = m_CellPositions.begin(); 653 } 654 virtual sal_Bool SAL_CALL hasMoreElements() override { return m_it != m_CellPositions.end(); } 655 656 virtual uno::Any SAL_CALL nextElement() override 657 { 658 if ( !hasMoreElements() ) 659 throw container::NoSuchElementException(); 660 CellPos aPos = *m_it++; 661 662 uno::Reference< table::XCellRange > xRangeArea = getArea( aPos.m_nArea ); 663 uno::Reference< table::XCellRange > xCellRange( xRangeArea->getCellByPosition( aPos.m_nCol, aPos.m_nRow ), uno::UNO_QUERY_THROW ); 664 return uno::Any( uno::Reference< excel::XRange >( new ScVbaRange( mxParent, mxContext, xCellRange ) ) ); 665 666 } 667 }; 668 669 } 670 671 constexpr OUStringLiteral ISVISIBLE = u"IsVisible"; 672 const char EQUALS[] = "="; 673 const char NOTEQUALS[] = "<>"; 674 const char GREATERTHAN[] = ">"; 675 const char GREATERTHANEQUALS[] = ">="; 676 const char LESSTHAN[] = "<"; 677 const char LESSTHANEQUALS[] = "<="; 678 constexpr OUStringLiteral STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY(u"The command you chose cannot be performed with multiple selections.\nSelect a single range and click the command again"); 679 constexpr OUStringLiteral CELLSTYLE = u"CellStyle"; 680 681 namespace { 682 683 class CellValueSetter : public ValueSetter 684 { 685 protected: 686 uno::Any maValue; 687 public: 688 explicit CellValueSetter( const uno::Any& aValue ); 689 virtual bool processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell ) override; 690 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) override; 691 692 }; 693 694 } 695 696 CellValueSetter::CellValueSetter( const uno::Any& aValue ): maValue( aValue ) {} 697 698 void 699 CellValueSetter::visitNode( sal_Int32 /*i*/, sal_Int32 /*j*/, const uno::Reference< table::XCell >& xCell ) 700 { 701 processValue( maValue, xCell ); 702 } 703 704 bool 705 CellValueSetter::processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell ) 706 { 707 708 bool isExtracted = false; 709 switch ( aValue.getValueTypeClass() ) 710 { 711 case uno::TypeClass_BOOLEAN: 712 { 713 bool bState = false; 714 if ( aValue >>= bState ) 715 { 716 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW ); 717 if ( bState ) 718 xCell->setValue( double(1) ); 719 else 720 xCell->setValue( double(0) ); 721 NumFormatHelper cellNumFormat( xRange ); 722 cellNumFormat.setNumberFormat( util::NumberFormat::LOGICAL ); 723 } 724 break; 725 } 726 case uno::TypeClass_STRING: 727 { 728 OUString aString; 729 if ( aValue >>= aString ) 730 { 731 // The required behavior for a string value is: 732 // 1. If the first character is a single quote, use the rest as a string cell, regardless of the cell's number format. 733 // 2. Otherwise, if the cell's number format is "text", use the string value as a string cell. 734 // 3. Otherwise, parse the string value in English locale, and apply a corresponding number format with the cell's locale 735 // if the cell's number format was "General". 736 // Case 1 is handled here, the rest in ScCellObj::InputEnglishString 737 738 if ( aString.toChar() == '\'' ) // case 1 - handle with XTextRange 739 { 740 OUString aRemainder( aString.copy(1) ); // strip the quote 741 uno::Reference< text::XTextRange > xTextRange( xCell, uno::UNO_QUERY_THROW ); 742 xTextRange->setString( aRemainder ); 743 } 744 else 745 { 746 // call implementation method InputEnglishString 747 ScCellObj* pCellObj = dynamic_cast< ScCellObj* >( xCell.get() ); 748 if ( pCellObj ) 749 pCellObj->InputEnglishString( aString ); 750 } 751 } 752 else 753 isExtracted = false; 754 break; 755 } 756 default: 757 { 758 double nDouble = 0.0; 759 if ( aValue >>= nDouble ) 760 { 761 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW ); 762 NumFormatHelper cellFormat( xRange ); 763 // If we are setting a number and the cell types was logical 764 // then we need to reset the logical format. ( see case uno::TypeClass_BOOLEAN: 765 // handling above ) 766 if ( cellFormat.isBooleanType() ) 767 cellFormat.setNumberFormat("General"); 768 xCell->setValue( nDouble ); 769 } 770 else 771 isExtracted = false; 772 break; 773 } 774 } 775 return isExtracted; 776 777 } 778 779 namespace { 780 781 class CellValueGetter : public ValueGetter 782 { 783 protected: 784 uno::Any maValue; 785 public: 786 CellValueGetter() {} 787 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) override; 788 virtual void processValue( const uno::Any& aValue ) override; 789 const uno::Any& getValue() const override { return maValue; } 790 791 }; 792 793 } 794 795 void 796 CellValueGetter::processValue( const uno::Any& aValue ) 797 { 798 maValue = aValue; 799 } 800 void CellValueGetter::visitNode( sal_Int32 /*x*/, sal_Int32 /*y*/, const uno::Reference< table::XCell >& xCell ) 801 { 802 uno::Any aValue; 803 table::CellContentType eType = xCell->getType(); 804 if( eType == table::CellContentType_VALUE || eType == table::CellContentType_FORMULA ) 805 { 806 if ( eType == table::CellContentType_FORMULA ) 807 { 808 809 OUString sFormula = xCell->getFormula(); 810 if ( sFormula == "=TRUE()" ) 811 aValue <<= true; 812 else if ( sFormula == "=FALSE()" ) 813 aValue <<= false; 814 else 815 { 816 uno::Reference< beans::XPropertySet > xProp( xCell, uno::UNO_QUERY_THROW ); 817 818 sal_Int32 nResultType = sheet::FormulaResult::VALUE; 819 // some formulas give textual results 820 xProp->getPropertyValue( "FormulaResultType2" ) >>= nResultType; 821 822 if ( nResultType == sheet::FormulaResult::STRING ) 823 { 824 uno::Reference< text::XTextRange > xTextRange(xCell, ::uno::UNO_QUERY_THROW); 825 aValue <<= xTextRange->getString(); 826 } 827 else 828 aValue <<= xCell->getValue(); 829 } 830 } 831 else 832 { 833 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW ); 834 NumFormatHelper cellFormat( xRange ); 835 if ( cellFormat.isBooleanType() ) 836 aValue <<= ( xCell->getValue() != 0.0 ); 837 else if ( cellFormat.isDateType() ) 838 aValue <<= bridge::oleautomation::Date( xCell->getValue() ); 839 else 840 aValue <<= xCell->getValue(); 841 } 842 } 843 if( eType == table::CellContentType_TEXT ) 844 { 845 uno::Reference< text::XTextRange > xTextRange(xCell, ::uno::UNO_QUERY_THROW); 846 aValue <<= xTextRange->getString(); 847 } 848 processValue( aValue ); 849 } 850 851 namespace { 852 853 class CellFormulaValueSetter : public CellValueSetter 854 { 855 private: 856 ScDocument& m_rDoc; 857 formula::FormulaGrammar::Grammar m_eGrammar; 858 public: 859 CellFormulaValueSetter( const uno::Any& aValue, ScDocument& rDoc, formula::FormulaGrammar::Grammar eGram ):CellValueSetter( aValue ), m_rDoc( rDoc ), m_eGrammar( eGram ){} 860 protected: 861 bool processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell ) override 862 { 863 OUString sFormula; 864 double aDblValue = 0.0; 865 if ( aValue >>= sFormula ) 866 { 867 // convert to GRAM_API style grammar because XCell::setFormula 868 // always compile it in that grammar. Perhaps 869 // css.sheet.FormulaParser should be used in future to directly 870 // pass formula tokens when that API stabilizes. 871 if ( m_eGrammar != formula::FormulaGrammar::GRAM_API && ( o3tl::starts_with(o3tl::trim(sFormula), u"=") ) ) 872 { 873 uno::Reference< uno::XInterface > xIf( xCell, uno::UNO_QUERY_THROW ); 874 ScCellRangesBase* pUnoRangesBase = dynamic_cast< ScCellRangesBase* >( xIf.get() ); 875 if ( pUnoRangesBase ) 876 { 877 const ScRangeList& rCellRanges = pUnoRangesBase->GetRangeList(); 878 if (!rCellRanges.empty()) 879 { 880 ScCompiler aCompiler( m_rDoc, rCellRanges.front().aStart, m_eGrammar ); 881 // compile the string in the format passed in 882 std::unique_ptr<ScTokenArray> pArray(aCompiler.CompileString(sFormula)); 883 // convert to API grammar 884 aCompiler.SetGrammar( formula::FormulaGrammar::GRAM_API ); 885 OUString sConverted; 886 aCompiler.CreateStringFromTokenArray(sConverted); 887 sFormula = EQUALS + sConverted; 888 } 889 } 890 } 891 892 xCell->setFormula( sFormula ); 893 return true; 894 } 895 else if ( aValue >>= aDblValue ) 896 { 897 xCell->setValue( aDblValue ); 898 return true; 899 } 900 return false; 901 } 902 903 }; 904 905 class CellFormulaValueGetter : public CellValueGetter 906 { 907 private: 908 ScDocument& m_rDoc; 909 formula::FormulaGrammar::Grammar m_eGrammar; 910 public: 911 CellFormulaValueGetter(ScDocument& rDoc, formula::FormulaGrammar::Grammar eGram ) : m_rDoc( rDoc ), m_eGrammar( eGram ) {} 912 virtual void visitNode( sal_Int32 /*x*/, sal_Int32 /*y*/, const uno::Reference< table::XCell >& xCell ) override 913 { 914 uno::Any aValue; 915 aValue <<= xCell->getFormula(); 916 // XCell::getFormula() returns the formula in API grammar, convert. 917 if ((xCell->getType() == table::CellContentType_FORMULA) 918 && m_eGrammar != formula::FormulaGrammar::GRAM_API) 919 { 920 uno::Reference< uno::XInterface > xIf( xCell, uno::UNO_QUERY_THROW ); 921 ScCellRangesBase* pUnoRangesBase = dynamic_cast< ScCellRangesBase* >( xIf.get() ); 922 if (pUnoRangesBase) 923 { 924 OUString sVal; 925 aValue >>= sVal; 926 const ScRangeList& rCellRanges = pUnoRangesBase->GetRangeList(); 927 if (!rCellRanges.empty()) 928 { 929 // Compile string from API grammar. 930 ScCompiler aCompiler( m_rDoc, rCellRanges.front().aStart, formula::FormulaGrammar::GRAM_API ); 931 std::unique_ptr<ScTokenArray> pArray(aCompiler.CompileString(sVal)); 932 // Convert to desired grammar. 933 aCompiler.SetGrammar( m_eGrammar ); 934 OUString sConverted; 935 aCompiler.CreateStringFromTokenArray(sConverted); 936 sVal = EQUALS + sConverted; 937 aValue <<= sVal; 938 } 939 } 940 } 941 942 processValue( aValue ); 943 } 944 945 }; 946 947 class Dim2ArrayValueGetter : public ArrayVisitor 948 { 949 protected: 950 uno::Any maValue; 951 ValueGetter& mValueGetter; 952 void processValue( sal_Int32 x, sal_Int32 y, const uno::Any& aValue ) 953 { 954 uno::Sequence< uno::Sequence< uno::Any > >& aMatrix = const_cast<css::uno::Sequence<css::uno::Sequence<css::uno::Any>> &>(*o3tl::doAccess<uno::Sequence<uno::Sequence<uno::Any>>>(maValue)); 955 aMatrix.getArray()[x].getArray()[y] = aValue; 956 } 957 958 public: 959 Dim2ArrayValueGetter(sal_Int32 nRowCount, sal_Int32 nColCount, ValueGetter& rValueGetter ): mValueGetter(rValueGetter) 960 { 961 uno::Sequence< uno::Sequence< uno::Any > > aMatrix; 962 aMatrix.realloc( nRowCount ); 963 auto pMatrix = aMatrix.getArray(); 964 for ( sal_Int32 index = 0; index < nRowCount; ++index ) 965 pMatrix[index].realloc( nColCount ); 966 maValue <<= aMatrix; 967 } 968 void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) override 969 970 { 971 mValueGetter.visitNode( x, y, xCell ); 972 processValue( x, y, mValueGetter.getValue() ); 973 } 974 const uno::Any& getValue() const { return maValue; } 975 976 }; 977 978 } 979 980 constexpr OUStringLiteral sNA = u"#N/A"; 981 982 namespace { 983 984 class Dim1ArrayValueSetter : public ArrayVisitor 985 { 986 uno::Sequence< uno::Any > aMatrix; 987 sal_Int32 nColCount; 988 ValueSetter& mCellValueSetter; 989 public: 990 Dim1ArrayValueSetter( const uno::Any& aValue, ValueSetter& rCellValueSetter ):mCellValueSetter( rCellValueSetter ) 991 { 992 aValue >>= aMatrix; 993 nColCount = aMatrix.getLength(); 994 } 995 virtual void visitNode( sal_Int32 /*x*/, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) override 996 { 997 if ( y < nColCount ) 998 mCellValueSetter.processValue( aMatrix[ y ], xCell ); 999 else 1000 mCellValueSetter.processValue( uno::Any( OUString(sNA) ), xCell ); 1001 } 1002 }; 1003 1004 class Dim2ArrayValueSetter : public ArrayVisitor 1005 { 1006 uno::Sequence< uno::Sequence< uno::Any > > aMatrix; 1007 ValueSetter& mCellValueSetter; 1008 sal_Int32 nRowCount; 1009 sal_Int32 nColCount; 1010 public: 1011 Dim2ArrayValueSetter( const uno::Any& aValue, ValueSetter& rCellValueSetter ) : mCellValueSetter( rCellValueSetter ) 1012 { 1013 aValue >>= aMatrix; 1014 nRowCount = aMatrix.getLength(); 1015 nColCount = aMatrix[0].getLength(); 1016 } 1017 1018 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) override 1019 { 1020 if ( x < nRowCount && y < nColCount ) 1021 mCellValueSetter.processValue( aMatrix[ x ][ y ], xCell ); 1022 else 1023 mCellValueSetter.processValue( uno::Any( OUString(sNA) ), xCell ); 1024 1025 } 1026 }; 1027 1028 class RangeProcessor 1029 { 1030 public: 1031 virtual void process( const uno::Reference< excel::XRange >& xRange ) = 0; 1032 1033 protected: 1034 ~RangeProcessor() {} 1035 }; 1036 1037 class RangeValueProcessor : public RangeProcessor 1038 { 1039 const uno::Any& m_aVal; 1040 public: 1041 explicit RangeValueProcessor( const uno::Any& rVal ):m_aVal( rVal ) {} 1042 virtual ~RangeValueProcessor() {} 1043 virtual void process( const uno::Reference< excel::XRange >& xRange ) override 1044 { 1045 xRange->setValue( m_aVal ); 1046 } 1047 }; 1048 1049 class RangeFormulaProcessor : public RangeProcessor 1050 { 1051 const uno::Any& m_aVal; 1052 public: 1053 explicit RangeFormulaProcessor( const uno::Any& rVal ):m_aVal( rVal ) {} 1054 virtual ~RangeFormulaProcessor() {} 1055 virtual void process( const uno::Reference< excel::XRange >& xRange ) override 1056 { 1057 xRange->setFormula( m_aVal ); 1058 } 1059 }; 1060 1061 class RangeCountProcessor : public RangeProcessor 1062 { 1063 sal_Int32 nCount; 1064 public: 1065 RangeCountProcessor():nCount(0){} 1066 virtual ~RangeCountProcessor() {} 1067 virtual void process( const uno::Reference< excel::XRange >& xRange ) override 1068 { 1069 nCount = nCount + xRange->getCount(); 1070 } 1071 sal_Int32 value() { return nCount; } 1072 }; 1073 class AreasVisitor 1074 { 1075 private: 1076 uno::Reference< XCollection > m_Areas; 1077 public: 1078 explicit AreasVisitor( const uno::Reference< XCollection >& rAreas ):m_Areas( rAreas ){} 1079 1080 void visit( RangeProcessor& processor ) 1081 { 1082 if ( m_Areas.is() ) 1083 { 1084 sal_Int32 nItems = m_Areas->getCount(); 1085 for ( sal_Int32 index=1; index <= nItems; ++index ) 1086 { 1087 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 1088 processor.process( xRange ); 1089 } 1090 } 1091 } 1092 }; 1093 1094 class RangeHelper 1095 { 1096 uno::Reference< table::XCellRange > m_xCellRange; 1097 1098 public: 1099 /// @throws uno::RuntimeException 1100 explicit RangeHelper( const uno::Reference< table::XCellRange >& xCellRange ) : m_xCellRange( xCellRange ) 1101 { 1102 if ( !m_xCellRange.is() ) 1103 throw uno::RuntimeException(); 1104 } 1105 /// @throws uno::RuntimeException 1106 explicit RangeHelper( const uno::Any& rCellRange ) 1107 { 1108 m_xCellRange.set(rCellRange, uno::UNO_QUERY_THROW); 1109 } 1110 /// @throws uno::RuntimeException 1111 uno::Reference< sheet::XSheetCellRange > getSheetCellRange() const 1112 { 1113 return uno::Reference< sheet::XSheetCellRange >(m_xCellRange, uno::UNO_QUERY_THROW); 1114 } 1115 /// @throws uno::RuntimeException 1116 uno::Reference< sheet::XSpreadsheet > getSpreadSheet() const 1117 { 1118 return getSheetCellRange()->getSpreadsheet(); 1119 } 1120 1121 /// @throws uno::RuntimeException 1122 uno::Reference< table::XCellRange > getCellRangeFromSheet() const 1123 { 1124 return uno::Reference< table::XCellRange >(getSpreadSheet(), uno::UNO_QUERY_THROW ); 1125 } 1126 1127 /// @throws uno::RuntimeException 1128 uno::Reference< sheet::XCellRangeAddressable > getCellRangeAddressable() const 1129 { 1130 return uno::Reference< sheet::XCellRangeAddressable >(m_xCellRange, ::uno::UNO_QUERY_THROW); 1131 1132 } 1133 1134 /// @throws uno::RuntimeException 1135 uno::Reference< sheet::XSheetCellCursor > getSheetCellCursor() const 1136 { 1137 return uno::Reference< sheet::XSheetCellCursor >( getSpreadSheet()->createCursorByRange( getSheetCellRange() ), uno::UNO_SET_THROW ); 1138 } 1139 1140 static uno::Reference< excel::XRange > createRangeFromRange( const uno::Reference< XHelperInterface >& xParent, const uno::Reference<uno::XComponentContext >& xContext, 1141 const uno::Reference< table::XCellRange >& xRange, const uno::Reference< sheet::XCellRangeAddressable >& xCellRangeAddressable ) 1142 { 1143 return uno::Reference< excel::XRange >( new ScVbaRange( xParent, xContext, 1144 xRange->getCellRangeByPosition( 1145 xCellRangeAddressable->getRangeAddress().StartColumn, 1146 xCellRangeAddressable->getRangeAddress().StartRow, 1147 xCellRangeAddressable->getRangeAddress().EndColumn, 1148 xCellRangeAddressable->getRangeAddress().EndRow ) ) ); 1149 } 1150 1151 }; 1152 1153 } 1154 1155 bool 1156 ScVbaRange::getCellRangesForAddress( ScRefFlags& rResFlags, std::u16string_view sAddress, ScDocShell* pDocSh, ScRangeList& rCellRanges, formula::FormulaGrammar::AddressConvention eConv, char cDelimiter ) 1157 { 1158 1159 if ( pDocSh ) 1160 { 1161 ScDocument& rDoc = pDocSh->GetDocument(); 1162 rResFlags = rCellRanges.Parse( sAddress, rDoc, eConv, 0, cDelimiter ); 1163 if ( rResFlags & ScRefFlags::VALID ) 1164 { 1165 return true; 1166 } 1167 } 1168 return false; 1169 } 1170 1171 bool getScRangeListForAddress( const OUString& sName, ScDocShell* pDocSh, const ScRange& refRange, ScRangeList& aCellRanges, formula::FormulaGrammar::AddressConvention aConv ) 1172 { 1173 // see if there is a match with a named range 1174 uno::Reference< beans::XPropertySet > xProps( pDocSh->GetModel(), uno::UNO_QUERY_THROW ); 1175 uno::Reference< container::XNameAccess > xNameAccess( xProps->getPropertyValue( "NamedRanges" ), uno::UNO_QUERY_THROW ); 1176 // Strange enough you can have Range( "namedRange1, namedRange2, etc," ) 1177 // loop around each ',' separated name 1178 std::vector< OUString > vNames; 1179 sal_Int32 nIndex = 0; 1180 do 1181 { 1182 OUString aToken = sName.getToken( 0, ',', nIndex ); 1183 vNames.push_back( aToken ); 1184 } while ( nIndex >= 0 ); 1185 1186 if ( vNames.empty() ) 1187 vNames.push_back( sName ); 1188 1189 for ( const auto& rName : vNames ) 1190 { 1191 formula::FormulaGrammar::AddressConvention eConv = aConv; 1192 // spaces are illegal ( but the user of course can enter them ) 1193 OUString sAddress = rName.trim(); 1194 // if a local name ( on the active sheet ) exists this will 1195 // take precedence over a global with the same name 1196 if ( !xNameAccess->hasByName( sAddress ) ) 1197 { 1198 // try a local name 1199 ScDocument& rDoc = pDocSh->GetDocument(); 1200 SCTAB nCurTab = ScDocShell::GetCurTab(); 1201 ScRangeName* pRangeName = rDoc.GetRangeName(nCurTab); 1202 if (pRangeName) 1203 { 1204 // TODO: Handle local names correctly: 1205 // bool bLocalName = pRangeName->findByUpperName(ScGlobal::getCharClass().uppercase(sAddress)) != nullptr; 1206 } 1207 } 1208 char aChar = 0; 1209 if ( xNameAccess->hasByName( sAddress ) ) 1210 { 1211 uno::Reference< sheet::XNamedRange > xNamed( xNameAccess->getByName( sAddress ), uno::UNO_QUERY_THROW ); 1212 sAddress = xNamed->getContent(); 1213 // As the address comes from OOO, the addressing 1214 // style is may not be XL_A1 1215 eConv = pDocSh->GetDocument().GetAddressConvention(); 1216 aChar = ';'; 1217 } 1218 1219 ScRefFlags nFlags = ScRefFlags::ZERO; 1220 if ( !ScVbaRange::getCellRangesForAddress( nFlags, sAddress, pDocSh, aCellRanges, eConv, aChar ) ) 1221 return false; 1222 1223 bool bTabFromReferrer = !( nFlags & ScRefFlags::TAB_3D ); 1224 1225 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i ) 1226 { 1227 ScRange & rRange = aCellRanges[ i ]; 1228 rRange.aStart.SetCol( refRange.aStart.Col() + rRange.aStart.Col() ); 1229 rRange.aStart.SetRow( refRange.aStart.Row() + rRange.aStart.Row() ); 1230 rRange.aStart.SetTab( bTabFromReferrer ? refRange.aStart.Tab() : rRange.aStart.Tab() ); 1231 rRange.aEnd.SetCol( refRange.aStart.Col() + rRange.aEnd.Col() ); 1232 rRange.aEnd.SetRow( refRange.aStart.Row() + rRange.aEnd.Row() ); 1233 rRange.aEnd.SetTab( bTabFromReferrer ? refRange.aEnd.Tab() : rRange.aEnd.Tab() ); 1234 } 1235 } 1236 return true; 1237 } 1238 1239 /// @throws uno::RuntimeException 1240 static rtl::Reference<ScVbaRange> 1241 getRangeForName( const uno::Reference< uno::XComponentContext >& xContext, const OUString& sName, ScDocShell* pDocSh, const table::CellRangeAddress& pAddr, formula::FormulaGrammar::AddressConvention eConv = formula::FormulaGrammar::CONV_XL_A1 ) 1242 { 1243 ScRangeList aCellRanges; 1244 ScRange refRange; 1245 ScUnoConversion::FillScRange( refRange, pAddr ); 1246 if ( !getScRangeListForAddress ( sName, pDocSh, refRange, aCellRanges, eConv ) ) 1247 throw uno::RuntimeException(); 1248 // Single range 1249 if ( aCellRanges.size() == 1 ) 1250 { 1251 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pDocSh, aCellRanges.front() ) ); 1252 uno::Reference< XHelperInterface > xFixThisParent = excel::getUnoSheetModuleObj( xRange ); 1253 return new ScVbaRange( xFixThisParent, xContext, xRange ); 1254 } 1255 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pDocSh, aCellRanges ) ); 1256 1257 uno::Reference< XHelperInterface > xFixThisParent = excel::getUnoSheetModuleObj( xRanges ); 1258 return new ScVbaRange( xFixThisParent, xContext, xRanges ); 1259 } 1260 1261 namespace { 1262 1263 /// @throws uno::RuntimeException 1264 template< typename RangeType > 1265 table::CellRangeAddress lclGetRangeAddress( const uno::Reference< RangeType >& rxCellRange ) 1266 { 1267 return uno::Reference< sheet::XCellRangeAddressable >( rxCellRange, uno::UNO_QUERY_THROW )->getRangeAddress(); 1268 } 1269 1270 /// @throws uno::RuntimeException 1271 void lclClearRange( const uno::Reference< table::XCellRange >& rxCellRange ) 1272 { 1273 using namespace ::com::sun::star::sheet::CellFlags; 1274 sal_Int32 const nFlags = VALUE | DATETIME | STRING | ANNOTATION | FORMULA | HARDATTR | STYLES | EDITATTR | FORMATTED; 1275 uno::Reference< sheet::XSheetOperation > xSheetOperation( rxCellRange, uno::UNO_QUERY_THROW ); 1276 xSheetOperation->clearContents( nFlags ); 1277 } 1278 1279 /// @throws uno::RuntimeException 1280 uno::Reference< sheet::XSheetCellRange > lclExpandToMerged( const uno::Reference< table::XCellRange >& rxCellRange, bool bRecursive ) 1281 { 1282 uno::Reference< sheet::XSheetCellRange > xNewCellRange( rxCellRange, uno::UNO_QUERY_THROW ); 1283 uno::Reference< sheet::XSpreadsheet > xSheet( xNewCellRange->getSpreadsheet(), uno::UNO_SET_THROW ); 1284 table::CellRangeAddress aNewAddress = lclGetRangeAddress( xNewCellRange ); 1285 table::CellRangeAddress aOldAddress; 1286 // expand as long as there are new merged ranges included 1287 do 1288 { 1289 aOldAddress = aNewAddress; 1290 uno::Reference< sheet::XSheetCellCursor > xCursor( xSheet->createCursorByRange( xNewCellRange ), uno::UNO_SET_THROW ); 1291 xCursor->collapseToMergedArea(); 1292 xNewCellRange.set( xCursor, uno::UNO_QUERY_THROW ); 1293 aNewAddress = lclGetRangeAddress( xNewCellRange ); 1294 } 1295 while( bRecursive && (aOldAddress != aNewAddress) ); 1296 return xNewCellRange; 1297 } 1298 1299 /// @throws uno::RuntimeException 1300 uno::Reference< sheet::XSheetCellRangeContainer > lclExpandToMerged( const uno::Reference< sheet::XSheetCellRangeContainer >& rxCellRanges ) 1301 { 1302 if( !rxCellRanges.is() ) 1303 throw uno::RuntimeException("Missing cell ranges object" ); 1304 sal_Int32 nCount = rxCellRanges->getCount(); 1305 if( nCount < 1 ) 1306 throw uno::RuntimeException("Missing cell ranges object" ); 1307 1308 ScRangeList aScRanges; 1309 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex ) 1310 { 1311 uno::Reference< table::XCellRange > xRange( rxCellRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW ); 1312 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( lclExpandToMerged( xRange, /*bRecursive*/true ) ); 1313 ScRange aScRange; 1314 ScUnoConversion::FillScRange( aScRange, aRangeAddr ); 1315 aScRanges.push_back( aScRange ); 1316 } 1317 return new ScCellRangesObj( getDocShellFromRanges( rxCellRanges ), aScRanges ); 1318 } 1319 1320 /// @throws uno::RuntimeException 1321 void lclExpandAndMerge( const uno::Reference< table::XCellRange >& rxCellRange, bool bMerge ) 1322 { 1323 uno::Reference< util::XMergeable > xMerge( lclExpandToMerged( rxCellRange, true ), uno::UNO_QUERY_THROW ); 1324 // Calc cannot merge over merged ranges, always unmerge first 1325 xMerge->merge( false ); 1326 if( !bMerge ) 1327 return; 1328 1329 // clear all contents of the covered cells (not the top-left cell) 1330 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxCellRange ); 1331 sal_Int32 nLastColIdx = aRangeAddr.EndColumn - aRangeAddr.StartColumn; 1332 sal_Int32 nLastRowIdx = aRangeAddr.EndRow - aRangeAddr.StartRow; 1333 // clear cells of top row, right of top-left cell 1334 if( nLastColIdx > 0 ) 1335 lclClearRange( rxCellRange->getCellRangeByPosition( 1, 0, nLastColIdx, 0 ) ); 1336 // clear all rows below top row 1337 if( nLastRowIdx > 0 ) 1338 lclClearRange( rxCellRange->getCellRangeByPosition( 0, 1, nLastColIdx, nLastRowIdx ) ); 1339 // merge the range 1340 xMerge->merge( true ); 1341 } 1342 1343 /// @throws uno::RuntimeException 1344 util::TriState lclGetMergedState( const uno::Reference< table::XCellRange >& rxCellRange ) 1345 { 1346 /* 1) Check if range is completely inside one single merged range. To do 1347 this, try to extend from top-left cell only (not from entire range). 1348 This will exclude cases where this range consists of several merged 1349 ranges (or parts of them). */ 1350 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxCellRange ); 1351 uno::Reference< table::XCellRange > xTopLeft( rxCellRange->getCellRangeByPosition( 0, 0, 0, 0 ), uno::UNO_SET_THROW ); 1352 uno::Reference< sheet::XSheetCellRange > xExpanded( lclExpandToMerged( xTopLeft, false ), uno::UNO_SET_THROW ); 1353 table::CellRangeAddress aExpAddr = lclGetRangeAddress( xExpanded ); 1354 // check that expanded range has more than one cell (really merged) 1355 if( ((aExpAddr.StartColumn < aExpAddr.EndColumn) || (aExpAddr.StartRow < aExpAddr.EndRow)) && ScUnoConversion::Contains( aExpAddr, aRangeAddr ) ) 1356 return util::TriState_YES; 1357 1358 /* 2) Check if this range contains any merged cells (completely or 1359 partly). This seems to be hardly possible via API, as 1360 XMergeable::getIsMerged() returns only true, if the top-left cell of a 1361 merged range is part of this range, so cases where just the lower part 1362 of a merged range is part of this range are not covered. */ 1363 ScRange aScRange; 1364 ScUnoConversion::FillScRange( aScRange, aRangeAddr ); 1365 bool bHasMerged = getDocumentFromRange( rxCellRange ).HasAttrib( aScRange, HasAttrFlags::Merged | HasAttrFlags::Overlapped ); 1366 return bHasMerged ? util::TriState_INDETERMINATE : util::TriState_NO; 1367 } 1368 1369 } // namespace 1370 1371 css::uno::Reference< excel::XRange > 1372 ScVbaRange::getRangeObjectForName( 1373 const uno::Reference< uno::XComponentContext >& xContext, const OUString& sRangeName, 1374 ScDocShell* pDocSh, formula::FormulaGrammar::AddressConvention eConv ) 1375 { 1376 table::CellRangeAddress refAddr; 1377 return getRangeForName( xContext, sRangeName, pDocSh, refAddr, eConv ); 1378 } 1379 1380 /// @throws uno::RuntimeException 1381 static table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh ) 1382 { 1383 uno::Reference< table::XCellRange > xRangeParam; 1384 switch ( aParam.getValueTypeClass() ) 1385 { 1386 case uno::TypeClass_STRING: 1387 { 1388 OUString rString; 1389 aParam >>= rString; 1390 ScRangeList aCellRanges; 1391 ScRange refRange; 1392 if ( getScRangeListForAddress ( rString, pDocSh, refRange, aCellRanges ) ) 1393 { 1394 if ( aCellRanges.size() == 1 ) 1395 { 1396 table::CellRangeAddress aRangeAddress; 1397 ScUnoConversion::FillApiRange( aRangeAddress, aCellRanges.front() ); 1398 return aRangeAddress; 1399 } 1400 } 1401 } 1402 break; 1403 1404 case uno::TypeClass_INTERFACE: 1405 { 1406 uno::Reference< excel::XRange > xRange; 1407 aParam >>= xRange; 1408 if ( xRange.is() ) 1409 xRange->getCellRange() >>= xRangeParam; 1410 } 1411 break; 1412 1413 default: 1414 throw uno::RuntimeException("Can't extract CellRangeAddress from type" ); 1415 } 1416 return lclGetRangeAddress( xRangeParam ); 1417 } 1418 1419 /// @throws uno::RuntimeException 1420 static uno::Reference< XCollection > 1421 lcl_setupBorders( const uno::Reference< excel::XRange >& xParentRange, const uno::Reference<uno::XComponentContext>& xContext, const uno::Reference< table::XCellRange >& xRange ) 1422 { 1423 uno::Reference< XHelperInterface > xParent( xParentRange, uno::UNO_QUERY_THROW ); 1424 ScDocument& rDoc = getDocumentFromRange(xRange); 1425 ScVbaPalette aPalette( rDoc.GetDocumentShell() ); 1426 uno::Reference< XCollection > borders( new ScVbaBorders( xParent, xContext, xRange, aPalette ) ); 1427 return borders; 1428 } 1429 1430 ScVbaRange::ScVbaRange( uno::Sequence< uno::Any> const & args, 1431 uno::Reference< uno::XComponentContext> const & xContext ) : ScVbaRange_BASE( getXSomethingFromArgs< XHelperInterface >( args, 0 ), xContext, getXSomethingFromArgs< beans::XPropertySet >( args, 1, false ), getModelFromXIf( getXSomethingFromArgs< uno::XInterface >( args, 1 ) ), true ), mbIsRows( false ), mbIsColumns( false ) 1432 { 1433 mxRange.set( mxPropertySet, uno::UNO_QUERY ); 1434 mxRanges.set( mxPropertySet, uno::UNO_QUERY ); 1435 uno::Reference< container::XIndexAccess > xIndex; 1436 if ( mxRange.is() ) 1437 { 1438 xIndex = new SingleRangeIndexAccess( mxRange ); 1439 } 1440 else if ( mxRanges.is() ) 1441 { 1442 xIndex.set( mxRanges, uno::UNO_QUERY_THROW ); 1443 } 1444 m_Areas = new ScVbaRangeAreas( mxParent, mxContext, xIndex, mbIsRows, mbIsColumns ); 1445 } 1446 1447 ScVbaRange::ScVbaRange( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< table::XCellRange >& xRange, bool bIsRows, bool bIsColumns ) 1448 : ScVbaRange_BASE( xParent, xContext, uno::Reference< beans::XPropertySet >( xRange, uno::UNO_QUERY_THROW ), getModelFromRange( xRange), true ), mxRange( xRange ), 1449 mbIsRows( bIsRows ), 1450 mbIsColumns( bIsColumns ) 1451 { 1452 if ( !xContext.is() ) 1453 throw lang::IllegalArgumentException("context is not set ", uno::Reference< uno::XInterface >() , 1 ); 1454 if ( !xRange.is() ) 1455 throw lang::IllegalArgumentException("range is not set ", uno::Reference< uno::XInterface >() , 1 ); 1456 1457 uno::Reference< container::XIndexAccess > xIndex( new SingleRangeIndexAccess( xRange ) ); 1458 m_Areas = new ScVbaRangeAreas( mxParent, mxContext, xIndex, mbIsRows, mbIsColumns ); 1459 1460 } 1461 1462 ScVbaRange::ScVbaRange(const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< sheet::XSheetCellRangeContainer >& xRanges, bool bIsRows, bool bIsColumns) 1463 : ScVbaRange_BASE( xParent, xContext, uno::Reference< beans::XPropertySet >( xRanges, uno::UNO_QUERY_THROW ), getModelFromXIf( uno::Reference< uno::XInterface >( xRanges, uno::UNO_QUERY_THROW ) ), true ), mxRanges( xRanges ),mbIsRows( bIsRows ), mbIsColumns( bIsColumns ) 1464 1465 { 1466 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 1467 m_Areas = new ScVbaRangeAreas( xParent, mxContext, xIndex, mbIsRows, mbIsColumns ); 1468 1469 } 1470 1471 ScVbaRange::~ScVbaRange() 1472 { 1473 } 1474 1475 uno::Reference< XCollection >& ScVbaRange::getBorders() 1476 { 1477 if ( !m_Borders.is() ) 1478 { 1479 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 1480 m_Borders = lcl_setupBorders( this, mxContext, uno::Reference< table::XCellRange >( xRange->getCellRange(), uno::UNO_QUERY_THROW ) ); 1481 } 1482 return m_Borders; 1483 } 1484 1485 void 1486 ScVbaRange::visitArray( ArrayVisitor& visitor ) 1487 { 1488 ScDocShell* pDocSh = nullptr; 1489 if(ScCellRangeObj* range = dynamic_cast<ScCellRangeObj*>(mxRange.get())) 1490 pDocSh = range->GetDocShell(); 1491 if ( pDocSh ) 1492 pDocSh->LockPaint(); 1493 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( mxRange ); 1494 sal_Int32 nRowCount = aRangeAddr.EndRow - aRangeAddr.StartRow + 1; 1495 sal_Int32 nColCount = aRangeAddr.EndColumn - aRangeAddr.StartColumn + 1; 1496 for ( sal_Int32 i=0; i<nRowCount; ++i ) 1497 { 1498 for ( sal_Int32 j=0; j<nColCount; ++j ) 1499 { 1500 uno::Reference< table::XCell > xCell( mxRange->getCellByPosition( j, i ), uno::UNO_SET_THROW ); 1501 1502 visitor.visitNode( i, j, xCell ); 1503 } 1504 } 1505 if ( pDocSh ) 1506 pDocSh->UnlockPaint(); 1507 } 1508 1509 uno::Any 1510 ScVbaRange::getValue( ValueGetter& valueGetter) 1511 { 1512 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW ); 1513 // single cell range 1514 if ( isSingleCellRange() ) 1515 { 1516 visitArray( valueGetter ); 1517 return valueGetter.getValue(); 1518 } 1519 sal_Int32 nRowCount = xColumnRowRange->getRows()->getCount(); 1520 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount(); 1521 // multi cell range ( return array ) 1522 Dim2ArrayValueGetter arrayGetter( nRowCount, nColCount, valueGetter ); 1523 visitArray( arrayGetter ); 1524 return uno::Any( script::ArrayWrapper( false, arrayGetter.getValue() ) ); 1525 } 1526 1527 uno::Any SAL_CALL 1528 ScVbaRange::getValue() 1529 { 1530 // #TODO code within the test below "if ( m_Areas... " can be removed 1531 // Test is performed only because m_xRange is NOT set to be 1532 // the first range in m_Areas ( to force failure while 1533 // the implementations for each method are being updated ) 1534 if ( m_Areas->getCount() > 1 ) 1535 { 1536 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1537 return xRange->getValue(); 1538 } 1539 1540 CellValueGetter valueGetter; 1541 return getValue( valueGetter ); 1542 1543 } 1544 1545 void 1546 ScVbaRange::setValue( const uno::Any& aValue, ValueSetter& valueSetter ) 1547 { 1548 uno::TypeClass aClass = aValue.getValueTypeClass(); 1549 if ( aClass == uno::TypeClass_SEQUENCE ) 1550 { 1551 const uno::Reference< script::XTypeConverter >& xConverter = getTypeConverter( mxContext ); 1552 uno::Any aConverted; 1553 try 1554 { 1555 // test for single dimension, could do 1556 // with a better test than this 1557 if ( aValue.getValueTypeName().indexOf('[') == aValue.getValueTypeName().lastIndexOf('[') ) 1558 { 1559 aConverted = xConverter->convertTo( aValue, cppu::UnoType<uno::Sequence< uno::Any >>::get() ); 1560 Dim1ArrayValueSetter setter( aConverted, valueSetter ); 1561 visitArray( setter ); 1562 } 1563 else 1564 { 1565 aConverted = xConverter->convertTo( aValue, cppu::UnoType<uno::Sequence< uno::Sequence< uno::Any > >>::get() ); 1566 Dim2ArrayValueSetter setter( aConverted, valueSetter ); 1567 visitArray( setter ); 1568 } 1569 } 1570 catch ( const uno::Exception& ) 1571 { 1572 TOOLS_WARN_EXCEPTION("sc", "Bahhh, caught" ); 1573 } 1574 } 1575 else 1576 { 1577 visitArray( valueSetter ); 1578 } 1579 fireChangeEvent(); 1580 } 1581 1582 void SAL_CALL 1583 ScVbaRange::setValue( const uno::Any &aValue ) 1584 { 1585 // If this is a multiple selection apply setValue over all areas 1586 if ( m_Areas->getCount() > 1 ) 1587 { 1588 AreasVisitor aVisitor( m_Areas ); 1589 RangeValueProcessor valueProcessor( aValue ); 1590 aVisitor.visit( valueProcessor ); 1591 return; 1592 } 1593 CellValueSetter valueSetter( aValue ); 1594 setValue( aValue, valueSetter ); 1595 } 1596 1597 void SAL_CALL 1598 ScVbaRange::Clear() 1599 { 1600 using namespace ::com::sun::star::sheet::CellFlags; 1601 sal_Int32 const nFlags = VALUE | DATETIME | STRING | FORMULA | HARDATTR | EDITATTR | FORMATTED; 1602 ClearContents( nFlags, true ); 1603 } 1604 1605 //helper ClearContent 1606 void 1607 ScVbaRange::ClearContents( sal_Int32 nFlags, bool bFireEvent ) 1608 { 1609 // #TODO code within the test below "if ( m_Areas... " can be removed 1610 // Test is performed only because m_xRange is NOT set to be 1611 // the first range in m_Areas ( to force failure while 1612 // the implementations for each method are being updated ) 1613 if ( m_Areas->getCount() > 1 ) 1614 { 1615 sal_Int32 nItems = m_Areas->getCount(); 1616 for ( sal_Int32 index=1; index <= nItems; ++index ) 1617 { 1618 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 1619 ScVbaRange* pRange = getImplementation( xRange ); 1620 if ( pRange ) 1621 pRange->ClearContents( nFlags, false ); // do not fire for single ranges 1622 } 1623 // fire change event for the entire range list 1624 if( bFireEvent ) fireChangeEvent(); 1625 return; 1626 } 1627 1628 uno::Reference< sheet::XSheetOperation > xSheetOperation(mxRange, uno::UNO_QUERY_THROW); 1629 xSheetOperation->clearContents( nFlags ); 1630 if( bFireEvent ) fireChangeEvent(); 1631 } 1632 1633 void SAL_CALL 1634 ScVbaRange::ClearComments() 1635 { 1636 ClearContents( sheet::CellFlags::ANNOTATION, false ); 1637 } 1638 1639 void SAL_CALL 1640 ScVbaRange::ClearContents() 1641 { 1642 using namespace ::com::sun::star::sheet::CellFlags; 1643 sal_Int32 const nFlags = VALUE | DATETIME | STRING | FORMULA; 1644 ClearContents( nFlags, true ); 1645 } 1646 1647 void SAL_CALL 1648 ScVbaRange::ClearFormats() 1649 { 1650 // FIXME: need to check if we need to combine FORMATTED 1651 using namespace ::com::sun::star::sheet::CellFlags; 1652 sal_Int32 const nFlags = HARDATTR | FORMATTED | EDITATTR; 1653 ClearContents( nFlags, false ); 1654 } 1655 1656 void 1657 ScVbaRange::setFormulaValue( const uno::Any& rFormula, formula::FormulaGrammar::Grammar eGram ) 1658 { 1659 // If this is a multiple selection apply setFormula over all areas 1660 if ( m_Areas->getCount() > 1 ) 1661 { 1662 AreasVisitor aVisitor( m_Areas ); 1663 RangeFormulaProcessor valueProcessor( rFormula ); 1664 aVisitor.visit( valueProcessor ); 1665 return; 1666 } 1667 CellFormulaValueSetter formulaValueSetter( rFormula, getScDocument(), eGram ); 1668 setValue( rFormula, formulaValueSetter ); 1669 } 1670 1671 uno::Any 1672 ScVbaRange::getFormulaValue( formula::FormulaGrammar::Grammar eGram ) 1673 { 1674 // #TODO code within the test below "if ( m_Areas... " can be removed 1675 // Test is performed only because m_xRange is NOT set to be 1676 // the first range in m_Areas ( to force failure while 1677 // the implementations for each method are being updated ) 1678 if ( m_Areas->getCount() > 1 ) 1679 { 1680 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1681 return xRange->getFormula(); 1682 } 1683 CellFormulaValueGetter valueGetter( getScDocument(), eGram ); 1684 return getValue( valueGetter ); 1685 1686 } 1687 1688 uno::Any 1689 ScVbaRange::getFormula() 1690 { 1691 return getFormulaValue( formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 ); 1692 } 1693 1694 void 1695 ScVbaRange::setFormula(const uno::Any &rFormula ) 1696 { 1697 setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 ); 1698 } 1699 1700 uno::Any 1701 ScVbaRange::getFormulaR1C1() 1702 { 1703 return getFormulaValue( formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1 ); 1704 } 1705 1706 void 1707 ScVbaRange::setFormulaR1C1(const uno::Any& rFormula ) 1708 { 1709 setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1 ); 1710 } 1711 1712 uno::Any 1713 ScVbaRange::getFormulaLocal() 1714 { 1715 return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_A1 ); 1716 } 1717 1718 void 1719 ScVbaRange::setFormulaLocal(const uno::Any &rFormula ) 1720 { 1721 setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_NATIVE_XL_A1 ); 1722 } 1723 1724 uno::Any 1725 ScVbaRange::getFormulaR1C1Local() 1726 { 1727 return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 ); 1728 } 1729 1730 void 1731 ScVbaRange::setFormulaR1C1Local(const uno::Any& rFormula ) 1732 { 1733 setFormulaValue( rFormula, formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 ); 1734 } 1735 1736 sal_Int32 1737 ScVbaRange::getCount() 1738 { 1739 // If this is a multiple selection apply setValue over all areas 1740 if ( m_Areas->getCount() > 1 ) 1741 { 1742 AreasVisitor aVisitor( m_Areas ); 1743 RangeCountProcessor valueProcessor; 1744 aVisitor.visit( valueProcessor ); 1745 return valueProcessor.value(); 1746 } 1747 sal_Int32 rowCount = 0; 1748 sal_Int32 colCount = 0; 1749 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW ); 1750 rowCount = xColumnRowRange->getRows()->getCount(); 1751 colCount = xColumnRowRange->getColumns()->getCount(); 1752 1753 if( mbIsRows ) 1754 return rowCount; 1755 if( mbIsColumns ) 1756 return colCount; 1757 return rowCount * colCount; 1758 } 1759 1760 sal_Int32 1761 ScVbaRange::getRow() 1762 { 1763 // #TODO code within the test below "if ( m_Areas... " can be removed 1764 // Test is performed only because m_xRange is NOT set to be 1765 // the first range in m_Areas ( to force failure while 1766 // the implementations for each method are being updated ) 1767 if ( m_Areas->getCount() > 1 ) 1768 { 1769 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1770 return xRange->getRow(); 1771 } 1772 uno::Reference< sheet::XCellAddressable > xCellAddressable(mxRange->getCellByPosition(0, 0), uno::UNO_QUERY_THROW ); 1773 return xCellAddressable->getCellAddress().Row + 1; // Zero value indexing 1774 } 1775 1776 sal_Int32 1777 ScVbaRange::getColumn() 1778 { 1779 // #TODO code within the test below "if ( m_Areas... " can be removed 1780 // Test is performed only because m_xRange is NOT set to be 1781 // the first range in m_Areas ( to force failure while 1782 // the implementations for each method are being updated ) 1783 if ( m_Areas->getCount() > 1 ) 1784 { 1785 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1786 return xRange->getColumn(); 1787 } 1788 uno::Reference< sheet::XCellAddressable > xCellAddressable(mxRange->getCellByPosition(0, 0), uno::UNO_QUERY_THROW ); 1789 return xCellAddressable->getCellAddress().Column + 1; // Zero value indexing 1790 } 1791 1792 uno::Any 1793 ScVbaRange::HasFormula() 1794 { 1795 if ( m_Areas->getCount() > 1 ) 1796 { 1797 sal_Int32 nItems = m_Areas->getCount(); 1798 uno::Any aResult = aNULL(); 1799 for ( sal_Int32 index=1; index <= nItems; ++index ) 1800 { 1801 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 1802 // if the HasFormula for any area is different to another 1803 // return null 1804 if ( index > 1 ) 1805 if ( aResult != xRange->HasFormula() ) 1806 return aNULL(); 1807 aResult = xRange->HasFormula(); 1808 if ( aNULL() == aResult ) 1809 return aNULL(); 1810 } 1811 return aResult; 1812 } 1813 uno::Reference< uno::XInterface > xIf( mxRange, uno::UNO_QUERY_THROW ); 1814 ScCellRangesBase* pThisRanges = dynamic_cast< ScCellRangesBase * > ( xIf.get() ); 1815 if ( pThisRanges ) 1816 { 1817 uno::Reference<uno::XInterface> xRanges( pThisRanges->queryFormulaCells( sheet::FormulaResult::ERROR | sheet::FormulaResult::VALUE | sheet::FormulaResult::STRING ), uno::UNO_QUERY_THROW ); 1818 ScCellRangesBase* pFormulaRanges = dynamic_cast< ScCellRangesBase * > ( xRanges.get() ); 1819 assert(pFormulaRanges); 1820 // check if there are no formula cell, return false 1821 if ( pFormulaRanges->GetRangeList().empty() ) 1822 return uno::Any(false); 1823 1824 // check if there are holes (where some cells are not formulas) 1825 // or returned range is not equal to this range 1826 if ( ( pFormulaRanges->GetRangeList().size() > 1 ) 1827 || ( pFormulaRanges->GetRangeList().front().aStart != pThisRanges->GetRangeList().front().aStart ) 1828 || ( pFormulaRanges->GetRangeList().front().aEnd != pThisRanges->GetRangeList().front().aEnd ) 1829 ) 1830 return aNULL(); // should return aNULL; 1831 } 1832 return uno::Any( true ); 1833 } 1834 void 1835 ScVbaRange::fillSeries( sheet::FillDirection nFillDirection, sheet::FillMode nFillMode, sheet::FillDateMode nFillDateMode, double fStep, double fEndValue ) 1836 { 1837 if ( m_Areas->getCount() > 1 ) 1838 { 1839 // Multi-Area Range 1840 uno::Reference< XCollection > xCollection( m_Areas, uno::UNO_SET_THROW ); 1841 for ( sal_Int32 index = 1; index <= xCollection->getCount(); ++index ) 1842 { 1843 uno::Reference< excel::XRange > xRange( xCollection->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 1844 ScVbaRange* pThisRange = getImplementation( xRange ); 1845 pThisRange->fillSeries( nFillDirection, nFillMode, nFillDateMode, fStep, fEndValue ); 1846 1847 } 1848 return; 1849 } 1850 1851 uno::Reference< sheet::XCellSeries > xCellSeries(mxRange, uno::UNO_QUERY_THROW ); 1852 xCellSeries->fillSeries( nFillDirection, nFillMode, nFillDateMode, fStep, fEndValue ); 1853 fireChangeEvent(); 1854 } 1855 1856 void 1857 ScVbaRange::FillLeft() 1858 { 1859 fillSeries(sheet::FillDirection_TO_LEFT, 1860 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF); 1861 } 1862 1863 void 1864 ScVbaRange::FillRight() 1865 { 1866 fillSeries(sheet::FillDirection_TO_RIGHT, 1867 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF); 1868 } 1869 1870 void 1871 ScVbaRange::FillUp() 1872 { 1873 fillSeries(sheet::FillDirection_TO_TOP, 1874 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF); 1875 } 1876 1877 void 1878 ScVbaRange::FillDown() 1879 { 1880 fillSeries(sheet::FillDirection_TO_BOTTOM, 1881 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF); 1882 } 1883 1884 OUString 1885 ScVbaRange::getText() 1886 { 1887 // #TODO code within the test below "if ( m_Areas... " can be removed 1888 // Test is performed only because m_xRange is NOT set to be 1889 // the first range in m_Areas ( to force failure while 1890 // the implementations for each method are being updated ) 1891 if ( m_Areas->getCount() > 1 ) 1892 { 1893 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1894 return xRange->getText(); 1895 } 1896 uno::Reference< text::XTextRange > xTextRange(mxRange->getCellByPosition(0,0), uno::UNO_QUERY_THROW ); 1897 return xTextRange->getString(); 1898 } 1899 1900 uno::Reference< excel::XRange > 1901 ScVbaRange::Offset( const ::uno::Any &nRowOff, const uno::Any &nColOff ) 1902 { 1903 SCROW nRowOffset = 0; 1904 SCCOL nColOffset = 0; 1905 bool bIsRowOffset = ( nRowOff >>= nRowOffset ); 1906 bool bIsColumnOffset = ( nColOff >>= nColOffset ); 1907 ScCellRangesBase* pUnoRangesBase = getCellRangesBase(); 1908 1909 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList(); 1910 1911 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i ) 1912 { 1913 ScRange & rRange = aCellRanges[ i ]; 1914 if ( bIsColumnOffset ) 1915 { 1916 rRange.aStart.SetCol( rRange.aStart.Col() + nColOffset ); 1917 rRange.aEnd.SetCol( rRange.aEnd.Col() + nColOffset ); 1918 } 1919 if ( bIsRowOffset ) 1920 { 1921 rRange.aStart.SetRow( rRange.aStart.Row() + nRowOffset ); 1922 rRange.aEnd.SetRow( rRange.aEnd.Row() + nRowOffset ); 1923 } 1924 } 1925 1926 if ( aCellRanges.size() > 1 ) // Multi-Area 1927 { 1928 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pUnoRangesBase->GetDocShell(), aCellRanges ) ); 1929 return new ScVbaRange( mxParent, mxContext, xRanges ); 1930 } 1931 // normal range 1932 const ScRange aRange( obtainRangeEvenIfRangeListIsEmpty( aCellRanges)); 1933 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange)); 1934 return new ScVbaRange( mxParent, mxContext, xRange ); 1935 } 1936 1937 uno::Reference< excel::XRange > 1938 ScVbaRange::CurrentRegion() 1939 { 1940 // #TODO code within the test below "if ( m_Areas... " can be removed 1941 // Test is performed only because m_xRange is NOT set to be 1942 // the first range in m_Areas ( to force failure while 1943 // the implementations for each method are being updated ) 1944 if ( m_Areas->getCount() > 1 ) 1945 { 1946 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1947 return xRange->CurrentRegion(); 1948 } 1949 1950 RangeHelper helper( mxRange ); 1951 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = 1952 helper.getSheetCellCursor(); 1953 xSheetCellCursor->collapseToCurrentRegion(); 1954 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW); 1955 return RangeHelper::createRangeFromRange( mxParent, mxContext, helper.getCellRangeFromSheet(), xCellRangeAddressable ); 1956 } 1957 1958 uno::Reference< excel::XRange > 1959 ScVbaRange::CurrentArray() 1960 { 1961 // #TODO code within the test below "if ( m_Areas... " can be removed 1962 // Test is performed only because m_xRange is NOT set to be 1963 // the first range in m_Areas ( to force failure while 1964 // the implementations for each method are being updated ) 1965 if ( m_Areas->getCount() > 1 ) 1966 { 1967 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1968 return xRange->CurrentArray(); 1969 } 1970 RangeHelper helper( mxRange ); 1971 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = 1972 helper.getSheetCellCursor(); 1973 xSheetCellCursor->collapseToCurrentArray(); 1974 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW); 1975 return RangeHelper::createRangeFromRange( mxParent, mxContext, helper.getCellRangeFromSheet(), xCellRangeAddressable ); 1976 } 1977 1978 uno::Any 1979 ScVbaRange::getFormulaArray() 1980 { 1981 // #TODO code within the test below "if ( m_Areas... " can be removed 1982 // Test is performed only because m_xRange is NOT set to be 1983 // the first range in m_Areas ( to force failure while 1984 // the implementations for each method are being updated ) 1985 if ( m_Areas->getCount() > 1 ) 1986 { 1987 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 1988 return xRange->getFormulaArray(); 1989 } 1990 1991 // return a formula if there is one or else an array 1992 // still not sure when the return as array code should run 1993 // ( I think it is if there is more than one formula ) at least 1994 // that is what the doc says ( but I am not even sure how to detect that ) 1995 // for the moment any tests we have pass 1996 uno::Reference< sheet::XArrayFormulaRange> xFormulaArray( mxRange, uno::UNO_QUERY_THROW ); 1997 if ( !xFormulaArray->getArrayFormula().isEmpty() ) 1998 return uno::Any( xFormulaArray->getArrayFormula() ); 1999 2000 uno::Reference< sheet::XCellRangeFormula> xCellRangeFormula( mxRange, uno::UNO_QUERY_THROW ); 2001 const uno::Reference< script::XTypeConverter >& xConverter = getTypeConverter( mxContext ); 2002 uno::Any aSingleValueOrMatrix; 2003 // When dealing with a single element ( embedded in the sequence of sequence ) unwrap and return 2004 // that value 2005 uno::Sequence< uno::Sequence<OUString> > aTmpSeq = xCellRangeFormula->getFormulaArray(); 2006 if ( aTmpSeq.getLength() == 1 ) 2007 { 2008 if ( aTmpSeq[ 0 ].getLength() == 1 ) 2009 aSingleValueOrMatrix <<= aTmpSeq[ 0 ][ 0 ]; 2010 } 2011 else 2012 aSingleValueOrMatrix = xConverter->convertTo( uno::Any( aTmpSeq ) , cppu::UnoType<uno::Sequence< uno::Sequence< uno::Any > >>::get() ) ; 2013 return aSingleValueOrMatrix; 2014 } 2015 2016 void 2017 ScVbaRange::setFormulaArray(const uno::Any& rFormula) 2018 { 2019 // #TODO code within the test below "if ( m_Areas... " can be removed 2020 // Test is performed only because m_xRange is NOT set to be 2021 // the first range in m_Areas ( to force failure while 2022 // the implementations for each method are being updated ) 2023 if ( m_Areas->getCount() > 1 ) 2024 { 2025 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 2026 return xRange->setFormulaArray( rFormula ); 2027 } 2028 // #TODO need to distinguish between getFormula and getFormulaArray e.g. (R1C1) 2029 // but for the moment it's just easier to treat them the same for setting 2030 // seems 2031 uno::Reference< lang::XMultiServiceFactory > xModelFactory( getUnoModel(), uno::UNO_QUERY_THROW ); 2032 uno::Reference< sheet::XFormulaParser > xParser( xModelFactory->createInstance( "com.sun.star.sheet.FormulaParser" ), uno::UNO_QUERY_THROW ); 2033 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY_THROW); 2034 2035 table::CellRangeAddress aRangeAddress = xSource->getRangeAddress(); 2036 // #TODO check if api orders the address 2037 // e.g. do we need to order the RangeAddress to get the topleft ( or can we assume it 2038 // is in the correct order ) 2039 table::CellAddress aAddress; 2040 aAddress.Sheet = aRangeAddress.Sheet; 2041 aAddress.Column = aRangeAddress.StartColumn; 2042 aAddress.Row = aRangeAddress.StartRow; 2043 OUString sFormula; 2044 rFormula >>= sFormula; 2045 uno::Sequence<sheet::FormulaToken> aTokens = xParser->parseFormula( sFormula, aAddress ); 2046 ScTokenArray aTokenArray(getScDocument()); 2047 (void)ScTokenConversion::ConvertToTokenArray( getScDocument(), aTokenArray, aTokens ); 2048 2049 getScDocShell()->GetDocFunc().EnterMatrix( getScRangeList()[0], nullptr, &aTokenArray, OUString(), true, true, OUString(), formula::FormulaGrammar::GRAM_API ); 2050 } 2051 2052 OUString 2053 ScVbaRange::Characters(const uno::Any& Start, const uno::Any& Length) 2054 { 2055 // #TODO code within the test below "if ( m_Areas... " can be removed 2056 // Test is performed only because m_xRange is NOT set to be 2057 // the first range in m_Areas ( to force failure while 2058 // the implementations for each method are being updated ) 2059 if ( m_Areas->getCount() > 1 ) 2060 { 2061 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 2062 return xRange->Characters( Start, Length ); 2063 } 2064 2065 tools::Long nIndex = 0, nCount = 0; 2066 OUString rString; 2067 uno::Reference< text::XTextRange > xTextRange(mxRange, ::uno::UNO_QUERY_THROW ); 2068 rString = xTextRange->getString(); 2069 if( !( Start >>= nIndex ) && !( Length >>= nCount ) ) 2070 return rString; 2071 if(!( Start >>= nIndex ) ) 2072 nIndex = 1; 2073 if(!( Length >>= nCount ) ) 2074 nIndex = rString.getLength(); 2075 return rString.copy( --nIndex, nCount ); // Zero value indexing 2076 } 2077 2078 OUString 2079 ScVbaRange::Address( const uno::Any& RowAbsolute, const uno::Any& ColumnAbsolute, const uno::Any& ReferenceStyle, const uno::Any& External, const uno::Any& RelativeTo ) 2080 { 2081 if ( m_Areas->getCount() > 1 ) 2082 { 2083 // Multi-Area Range 2084 OUStringBuffer sAddress; 2085 uno::Reference< XCollection > xCollection( m_Areas, uno::UNO_SET_THROW ); 2086 uno::Any aExternalCopy = External; 2087 for ( sal_Int32 index = 1; index <= xCollection->getCount(); ++index ) 2088 { 2089 uno::Reference< excel::XRange > xRange( xCollection->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 2090 if ( index > 1 ) 2091 { 2092 sAddress.append(","); 2093 // force external to be false 2094 // only first address should have the 2095 // document and sheet specifications 2096 aExternalCopy <<= false; 2097 } 2098 sAddress.append(xRange->Address( RowAbsolute, ColumnAbsolute, ReferenceStyle, aExternalCopy, RelativeTo )); 2099 } 2100 return sAddress.makeStringAndClear(); 2101 2102 } 2103 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 ); 2104 if ( ReferenceStyle.hasValue() ) 2105 { 2106 sal_Int32 refStyle = excel::XlReferenceStyle::xlA1; 2107 ReferenceStyle >>= refStyle; 2108 if ( refStyle == excel::XlReferenceStyle::xlR1C1 ) 2109 dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, 0, 0 ); 2110 } 2111 // default 2112 ScRefFlags nFlags = ScRefFlags::RANGE_ABS; 2113 ScDocShell* pDocShell = getScDocShell(); 2114 ScDocument& rDoc = pDocShell->GetDocument(); 2115 2116 RangeHelper thisRange( mxRange ); 2117 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 2118 ScRange aRange( static_cast< SCCOL >( thisAddress.StartColumn ), static_cast< SCROW >( thisAddress.StartRow ), static_cast< SCTAB >( thisAddress.Sheet ), static_cast< SCCOL >( thisAddress.EndColumn ), static_cast< SCROW >( thisAddress.EndRow ), static_cast< SCTAB >( thisAddress.Sheet ) ); 2119 constexpr ScRefFlags ROW_ABS = ScRefFlags::ROW_ABS | ScRefFlags::ROW2_ABS; 2120 constexpr ScRefFlags COL_ABS = ScRefFlags::COL_ABS | ScRefFlags::COL2_ABS; 2121 2122 if ( RowAbsolute.hasValue() ) 2123 { 2124 bool bVal = true; 2125 RowAbsolute >>= bVal; 2126 if ( !bVal ) 2127 nFlags &= ~ROW_ABS; 2128 } 2129 if ( ColumnAbsolute.hasValue() ) 2130 { 2131 bool bVal = true; 2132 ColumnAbsolute >>= bVal; 2133 if ( !bVal ) 2134 nFlags &= ~COL_ABS; 2135 } 2136 if ( External.hasValue() ) 2137 { 2138 bool bLocal = false; 2139 External >>= bLocal; 2140 if ( bLocal ) 2141 nFlags |= ScRefFlags::TAB_3D | ScRefFlags::FORCE_DOC; 2142 } 2143 if ( RelativeTo.hasValue() ) 2144 { 2145 // #TODO should I throw an error if R1C1 is not set? 2146 2147 table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell ); 2148 dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, static_cast< SCROW >( refAddress.StartRow ), static_cast< SCCOL >( refAddress.StartColumn ) ); 2149 } 2150 return aRange.Format(rDoc, nFlags, dDetails); 2151 } 2152 2153 uno::Reference < excel::XFont > 2154 ScVbaRange::Font() 2155 { 2156 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY ); 2157 ScDocument& rDoc = getScDocument(); 2158 if ( mxRange.is() ) 2159 xProps.set(mxRange, ::uno::UNO_QUERY ); 2160 else if ( mxRanges.is() ) 2161 xProps.set(mxRanges, ::uno::UNO_QUERY ); 2162 2163 ScVbaPalette aPalette( rDoc.GetDocumentShell() ); 2164 ScCellRangeObj* pRangeObj = nullptr; 2165 try 2166 { 2167 pRangeObj = getCellRangeObj(); 2168 } 2169 catch( uno::Exception& ) 2170 { 2171 } 2172 return new ScVbaFont( this, mxContext, aPalette, xProps, pRangeObj ); 2173 } 2174 2175 uno::Reference< excel::XRange > 2176 ScVbaRange::Cells( const uno::Any &nRowIndex, const uno::Any &nColumnIndex ) 2177 { 2178 // #TODO code within the test below "if ( m_Areas... " can be removed 2179 // Test is performed only because m_xRange is NOT set to be 2180 // the first range in m_Areas ( to force failure while 2181 // the implementations for each method are being updated ) 2182 if ( m_Areas->getCount() > 1 ) 2183 { 2184 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 2185 return xRange->Cells( nRowIndex, nColumnIndex ); 2186 } 2187 2188 // Performance: Use a common helper method for ScVbaRange::Cells and ScVbaWorksheet::Cells, 2189 // instead of creating a new ScVbaRange object in often-called ScVbaWorksheet::Cells 2190 return CellsHelper( getScDocument(), mxParent, mxContext, mxRange, nRowIndex, nColumnIndex ); 2191 } 2192 2193 // static 2194 uno::Reference< excel::XRange > 2195 ScVbaRange::CellsHelper( const ScDocument& rDoc, 2196 const uno::Reference< ov::XHelperInterface >& xParent, 2197 const uno::Reference< uno::XComponentContext >& xContext, 2198 const uno::Reference< css::table::XCellRange >& xRange, 2199 const uno::Any &nRowIndex, const uno::Any &nColumnIndex ) 2200 { 2201 sal_Int32 nRow = 0, nColumn = 0; 2202 2203 bool bIsIndex = nRowIndex.hasValue(); 2204 bool bIsColumnIndex = nColumnIndex.hasValue(); 2205 2206 // Sometimes we might get a float or a double or whatever 2207 // set in the Any, we should convert as appropriate 2208 // #FIXME - perhaps worth turning this into some sort of 2209 // conversion routine e.g. bSuccess = getValueFromAny( nRow, nRowIndex, cppu::UnoType<sal_Int32>::get() ) 2210 if ( nRowIndex.hasValue() && !( nRowIndex >>= nRow ) ) 2211 { 2212 const uno::Reference< script::XTypeConverter >& xConverter = getTypeConverter( xContext ); 2213 uno::Any aConverted; 2214 try 2215 { 2216 aConverted = xConverter->convertTo( nRowIndex, cppu::UnoType<sal_Int32>::get() ); 2217 bIsIndex = ( aConverted >>= nRow ); 2218 } 2219 catch( uno::Exception& ) {} // silence any errors 2220 } 2221 2222 if ( bIsColumnIndex ) 2223 { 2224 // Column index can be a col address e.g Cells( 1, "B" ) etc. 2225 OUString sCol; 2226 if ( nColumnIndex >>= sCol ) 2227 { 2228 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 ); 2229 ScRange tmpRange; 2230 ScRefFlags flags = tmpRange.ParseCols( rDoc, sCol, dDetails ); 2231 if ( (flags & ScRefFlags::COL_VALID) == ScRefFlags::ZERO ) 2232 throw uno::RuntimeException(); 2233 nColumn = tmpRange.aStart.Col() + 1; 2234 } 2235 else 2236 { 2237 if ( !( nColumnIndex >>= nColumn ) ) 2238 { 2239 const uno::Reference< script::XTypeConverter >& xConverter = getTypeConverter( xContext ); 2240 uno::Any aConverted; 2241 try 2242 { 2243 aConverted = xConverter->convertTo( nColumnIndex, cppu::UnoType<sal_Int32>::get() ); 2244 bIsColumnIndex = ( aConverted >>= nColumn ); 2245 } 2246 catch( uno::Exception& ) {} // silence any errors 2247 } 2248 } 2249 } 2250 RangeHelper thisRange( xRange ); 2251 table::CellRangeAddress thisRangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 2252 uno::Reference< table::XCellRange > xSheetRange = thisRange.getCellRangeFromSheet(); 2253 if( !bIsIndex && !bIsColumnIndex ) // .Cells 2254 // #FIXME needs proper parent ( Worksheet ) 2255 return uno::Reference< excel::XRange >( new ScVbaRange( xParent, xContext, xRange ) ); 2256 2257 sal_Int32 nIndex = --nRow; 2258 if( bIsIndex && !bIsColumnIndex ) // .Cells(n) 2259 { 2260 uno::Reference< table::XColumnRowRange > xColumnRowRange(xRange, ::uno::UNO_QUERY_THROW); 2261 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount(); 2262 2263 if ( !nIndex || nIndex < 0 ) 2264 nRow = 0; 2265 else 2266 nRow = nIndex / nColCount; 2267 nColumn = nIndex % nColCount; 2268 } 2269 else 2270 --nColumn; 2271 nRow = nRow + thisRangeAddress.StartRow; 2272 nColumn = nColumn + thisRangeAddress.StartColumn; 2273 return new ScVbaRange( xParent, xContext, xSheetRange->getCellRangeByPosition( nColumn, nRow, nColumn, nRow ) ); 2274 } 2275 2276 void 2277 ScVbaRange::Select() 2278 { 2279 ScCellRangesBase* pUnoRangesBase = getCellRangesBase(); 2280 if ( !pUnoRangesBase ) 2281 throw uno::RuntimeException("Failed to access underlying uno range object" ); 2282 ScDocShell* pShell = pUnoRangesBase->GetDocShell(); 2283 if ( !pShell ) 2284 return; 2285 2286 uno::Reference< frame::XModel > xModel( pShell->GetModel(), uno::UNO_SET_THROW ); 2287 uno::Reference< view::XSelectionSupplier > xSelection( xModel->getCurrentController(), uno::UNO_QUERY_THROW ); 2288 if ( mxRanges.is() ) 2289 xSelection->select( uno::Any( lclExpandToMerged( mxRanges ) ) ); 2290 else 2291 xSelection->select( uno::Any( lclExpandToMerged( mxRange, true ) ) ); 2292 // set focus on document e.g. 2293 // ThisComponent.CurrentController.Frame.getContainerWindow.SetFocus 2294 try 2295 { 2296 uno::Reference< frame::XController > xController( xModel->getCurrentController(), uno::UNO_SET_THROW ); 2297 uno::Reference< frame::XFrame > xFrame( xController->getFrame(), uno::UNO_SET_THROW ); 2298 uno::Reference< awt::XWindow > xWin( xFrame->getContainerWindow(), uno::UNO_SET_THROW ); 2299 xWin->setFocus(); 2300 } 2301 catch( uno::Exception& ) 2302 { 2303 } 2304 } 2305 2306 static bool cellInRange( const table::CellRangeAddress& rAddr, sal_Int32 nCol, sal_Int32 nRow ) 2307 { 2308 return nCol >= rAddr.StartColumn && nCol <= rAddr.EndColumn && 2309 nRow >= rAddr.StartRow && nRow <= rAddr.EndRow; 2310 } 2311 2312 static void setCursor( SCCOL nCol, SCROW nRow, const uno::Reference< frame::XModel >& xModel, bool bInSel = true ) 2313 { 2314 ScTabViewShell* pShell = excel::getBestViewShell( xModel ); 2315 if ( pShell ) 2316 { 2317 if ( bInSel ) 2318 pShell->SetCursor( nCol, nRow ); 2319 else 2320 pShell->MoveCursorAbs( nCol, nRow, SC_FOLLOW_NONE, false, false, true ); 2321 } 2322 } 2323 2324 void 2325 ScVbaRange::Activate() 2326 { 2327 // get first cell of current range 2328 uno::Reference< table::XCellRange > xCellRange; 2329 if ( mxRanges.is() ) 2330 { 2331 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 2332 xCellRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW ); 2333 } 2334 else 2335 xCellRange.set( mxRange, uno::UNO_SET_THROW ); 2336 2337 RangeHelper thisRange( xCellRange ); 2338 uno::Reference< sheet::XCellRangeAddressable > xThisRangeAddress = thisRange.getCellRangeAddressable(); 2339 table::CellRangeAddress thisRangeAddress = xThisRangeAddress->getRangeAddress(); 2340 uno::Reference< frame::XModel > xModel; 2341 ScDocShell* pShell = getScDocShell(); 2342 2343 if ( pShell ) 2344 xModel = pShell->GetModel(); 2345 2346 if ( !xModel.is() ) 2347 throw uno::RuntimeException(); 2348 2349 // get current selection 2350 uno::Reference< sheet::XCellRangeAddressable > xRange( xModel->getCurrentSelection(), ::uno::UNO_QUERY); 2351 2352 uno::Reference< sheet::XSheetCellRanges > xRanges( xModel->getCurrentSelection(), ::uno::UNO_QUERY); 2353 2354 if ( xRanges.is() ) 2355 { 2356 const uno::Sequence< table::CellRangeAddress > nAddrs = xRanges->getRangeAddresses(); 2357 for ( const auto& rAddr : nAddrs ) 2358 { 2359 if ( cellInRange( rAddr, thisRangeAddress.StartColumn, thisRangeAddress.StartRow ) ) 2360 { 2361 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel ); 2362 return; 2363 } 2364 2365 } 2366 } 2367 2368 if ( xRange.is() && cellInRange( xRange->getRangeAddress(), thisRangeAddress.StartColumn, thisRangeAddress.StartRow ) ) 2369 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel ); 2370 else 2371 { 2372 // if this range is multi cell select the range other 2373 // wise just position the cell at this single range position 2374 if ( isSingleCellRange() ) 2375 // This top-leftmost cell of this Range is not in the current 2376 // selection so just select this range 2377 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel, false ); 2378 else 2379 Select(); 2380 } 2381 2382 } 2383 2384 ScRange ScVbaRange::obtainRangeEvenIfRangeListIsEmpty( const ScRangeList& rCellRanges ) const 2385 { 2386 // XXX It may be that using the current range list was never correct, but 2387 // always the initial sheet range would be instead, history is unclear. 2388 2389 if (!rCellRanges.empty()) 2390 return rCellRanges.front(); 2391 2392 table::CellRangeAddress aRA( lclGetRangeAddress( mxRange )); 2393 return ScRange( aRA.StartColumn, aRA.StartRow, aRA.Sheet, aRA.EndColumn, aRA.EndRow, aRA.Sheet); 2394 } 2395 2396 uno::Reference< excel::XRange > 2397 ScVbaRange::Rows(const uno::Any& aIndex ) 2398 { 2399 if ( aIndex.hasValue() ) 2400 { 2401 ScCellRangesBase* pUnoRangesBase = getCellRangesBase(); 2402 ScRange aRange( obtainRangeEvenIfRangeListIsEmpty( pUnoRangesBase->GetRangeList())); 2403 2404 sal_Int32 nValue = 0; 2405 OUString sAddress; 2406 if( aIndex >>= nValue ) 2407 { 2408 aRange.aStart.SetRow( aRange.aStart.Row() + --nValue ); 2409 aRange.aEnd.SetRow( aRange.aStart.Row() ); 2410 } 2411 else if ( aIndex >>= sAddress ) 2412 { 2413 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 ); 2414 ScRange tmpRange; 2415 tmpRange.ParseRows( getScDocument(), sAddress, dDetails ); 2416 SCROW nStartRow = tmpRange.aStart.Row(); 2417 SCROW nEndRow = tmpRange.aEnd.Row(); 2418 2419 aRange.aStart.SetRow( aRange.aStart.Row() + nStartRow ); 2420 aRange.aEnd.SetRow( aRange.aStart.Row() + ( nEndRow - nStartRow )); 2421 } 2422 else 2423 throw uno::RuntimeException("Illegal param" ); 2424 2425 if ( aRange.aStart.Row() < 0 || aRange.aEnd.Row() < 0 ) 2426 throw uno::RuntimeException("Internal failure, illegal param" ); 2427 // return a normal range ( even for multi-selection 2428 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange ) ); 2429 return new ScVbaRange( mxParent, mxContext, xRange, true ); 2430 } 2431 // Rows() - no params 2432 if ( m_Areas->getCount() > 1 ) 2433 return new ScVbaRange( mxParent, mxContext, mxRanges, true ); 2434 return new ScVbaRange( mxParent, mxContext, mxRange, true ); 2435 } 2436 2437 uno::Reference< excel::XRange > 2438 ScVbaRange::Columns(const uno::Any& aIndex ) 2439 { 2440 ScCellRangesBase* pUnoRangesBase = getCellRangesBase(); 2441 ScRange aRange( obtainRangeEvenIfRangeListIsEmpty( pUnoRangesBase->GetRangeList())); 2442 2443 if ( aIndex.hasValue() ) 2444 { 2445 OUString sAddress; 2446 sal_Int32 nValue = 0; 2447 if ( aIndex >>= nValue ) 2448 { 2449 aRange.aStart.SetCol( aRange.aStart.Col() + static_cast< SCCOL > ( --nValue ) ); 2450 aRange.aEnd.SetCol( aRange.aStart.Col() ); 2451 } 2452 2453 else if ( aIndex >>= sAddress ) 2454 { 2455 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 ); 2456 ScRange tmpRange; 2457 tmpRange.ParseCols( getScDocument(), sAddress, dDetails ); 2458 SCCOL nStartCol = tmpRange.aStart.Col(); 2459 SCCOL nEndCol = tmpRange.aEnd.Col(); 2460 2461 aRange.aStart.SetCol( aRange.aStart.Col() + nStartCol ); 2462 aRange.aEnd.SetCol( aRange.aStart.Col() + ( nEndCol - nStartCol )); 2463 } 2464 else 2465 throw uno::RuntimeException("Illegal param" ); 2466 2467 if ( aRange.aStart.Col() < 0 || aRange.aEnd.Col() < 0 ) 2468 throw uno::RuntimeException("Internal failure, illegal param" ); 2469 } 2470 // Columns() - no params 2471 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange ) ); 2472 return new ScVbaRange( mxParent, mxContext, xRange, false, true ); 2473 } 2474 2475 void 2476 ScVbaRange::setMergeCells( const uno::Any& aIsMerged ) 2477 { 2478 bool bMerge = extractBoolFromAny( aIsMerged ); 2479 2480 if( mxRanges.is() ) 2481 { 2482 sal_Int32 nCount = mxRanges->getCount(); 2483 2484 // VBA does nothing (no error) if the own ranges overlap somehow 2485 ::std::vector< table::CellRangeAddress > aList; 2486 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex ) 2487 { 2488 uno::Reference< sheet::XCellRangeAddressable > xRangeAddr( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW ); 2489 table::CellRangeAddress aAddress = xRangeAddr->getRangeAddress(); 2490 if (std::any_of(aList.begin(), aList.end(), 2491 [&aAddress](const table::CellRangeAddress& rAddress) 2492 { return ScUnoConversion::Intersects( rAddress, aAddress ); })) 2493 return; 2494 aList.push_back( aAddress ); 2495 } 2496 2497 // (un)merge every range after it has been extended to intersecting merged ranges from sheet 2498 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex ) 2499 { 2500 uno::Reference< table::XCellRange > xRange( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW ); 2501 lclExpandAndMerge( xRange, bMerge ); 2502 } 2503 return; 2504 } 2505 2506 // otherwise, merge single range 2507 lclExpandAndMerge( mxRange, bMerge ); 2508 } 2509 2510 uno::Any 2511 ScVbaRange::getMergeCells() 2512 { 2513 if( mxRanges.is() ) 2514 { 2515 sal_Int32 nCount = mxRanges->getCount(); 2516 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex ) 2517 { 2518 uno::Reference< table::XCellRange > xRange( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW ); 2519 util::TriState eMerged = lclGetMergedState( xRange ); 2520 /* Excel always returns NULL, if one range of the range list is 2521 partly or completely merged. Even if all ranges are completely 2522 merged, the return value is still NULL. */ 2523 if( eMerged != util::TriState_NO ) 2524 return aNULL(); 2525 } 2526 // no range is merged anyhow, return false 2527 return uno::Any( false ); 2528 } 2529 2530 // otherwise, check single range 2531 switch( lclGetMergedState( mxRange ) ) 2532 { 2533 case util::TriState_YES: return uno::Any( true ); 2534 case util::TriState_NO: return uno::Any( false ); 2535 default: return aNULL(); 2536 } 2537 } 2538 2539 void 2540 ScVbaRange::Copy(const ::uno::Any& Destination) 2541 { 2542 if ( Destination.hasValue() ) 2543 { 2544 // TODO copy with multiple selections should work here too 2545 if ( m_Areas->getCount() > 1 ) 2546 throw uno::RuntimeException("That command cannot be used on multiple selections" ); 2547 uno::Reference< excel::XRange > xRange( Destination, uno::UNO_QUERY_THROW ); 2548 uno::Any aRange = xRange->getCellRange(); 2549 uno::Reference< table::XCellRange > xCellRange; 2550 aRange >>= xCellRange; 2551 uno::Reference< sheet::XSheetCellRange > xSheetCellRange(xCellRange, ::uno::UNO_QUERY_THROW); 2552 uno::Reference< sheet::XSpreadsheet > xSheet = xSheetCellRange->getSpreadsheet(); 2553 uno::Reference< table::XCellRange > xDest( xSheet, uno::UNO_QUERY_THROW ); 2554 uno::Reference< sheet::XCellRangeMovement > xMover( xSheet, uno::UNO_QUERY_THROW); 2555 uno::Reference< sheet::XCellAddressable > xDestination( xDest->getCellByPosition( 2556 xRange->getColumn()-1,xRange->getRow()-1), uno::UNO_QUERY_THROW ); 2557 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY); 2558 xMover->copyRange( xDestination->getCellAddress(), xSource->getRangeAddress() ); 2559 if ( ScVbaRange* pRange = getImplementation( xRange ) ) 2560 pRange->fireChangeEvent(); 2561 } 2562 else 2563 { 2564 Select(); 2565 excel::implnCopy(getUnoModel()); 2566 } 2567 } 2568 2569 void 2570 ScVbaRange::Cut(const ::uno::Any& Destination) 2571 { 2572 if ( m_Areas->getCount() > 1 ) 2573 throw uno::RuntimeException("That command cannot be used on multiple selections" ); 2574 if (Destination.hasValue()) 2575 { 2576 uno::Reference< excel::XRange > xRange( Destination, uno::UNO_QUERY_THROW ); 2577 uno::Reference< table::XCellRange > xCellRange( xRange->getCellRange(), uno::UNO_QUERY_THROW ); 2578 uno::Reference< sheet::XSheetCellRange > xSheetCellRange(xCellRange, ::uno::UNO_QUERY_THROW ); 2579 uno::Reference< sheet::XSpreadsheet > xSheet = xSheetCellRange->getSpreadsheet(); 2580 uno::Reference< table::XCellRange > xDest( xSheet, uno::UNO_QUERY_THROW ); 2581 uno::Reference< sheet::XCellRangeMovement > xMover( xSheet, uno::UNO_QUERY_THROW); 2582 uno::Reference< sheet::XCellAddressable > xDestination( xDest->getCellByPosition( 2583 xRange->getColumn()-1,xRange->getRow()-1), uno::UNO_QUERY); 2584 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY); 2585 xMover->moveRange( xDestination->getCellAddress(), xSource->getRangeAddress() ); 2586 } 2587 else 2588 { 2589 uno::Reference< frame::XModel > xModel = getModelFromRange( mxRange ); 2590 Select(); 2591 excel::implnCut( xModel ); 2592 } 2593 } 2594 2595 void 2596 ScVbaRange::setNumberFormat( const uno::Any& aFormat ) 2597 { 2598 OUString sFormat; 2599 aFormat >>= sFormat; 2600 if ( m_Areas->getCount() > 1 ) 2601 { 2602 sal_Int32 nItems = m_Areas->getCount(); 2603 for ( sal_Int32 index=1; index <= nItems; ++index ) 2604 { 2605 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 2606 xRange->setNumberFormat( aFormat ); 2607 } 2608 return; 2609 } 2610 NumFormatHelper numFormat( mxRange ); 2611 numFormat.setNumberFormat( sFormat ); 2612 } 2613 2614 uno::Any 2615 ScVbaRange::getNumberFormat() 2616 { 2617 2618 if ( m_Areas->getCount() > 1 ) 2619 { 2620 sal_Int32 nItems = m_Areas->getCount(); 2621 uno::Any aResult = aNULL(); 2622 for ( sal_Int32 index=1; index <= nItems; ++index ) 2623 { 2624 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 2625 // if the numberformat of one area is different to another 2626 // return null 2627 if ( index > 1 ) 2628 if ( aResult != xRange->getNumberFormat() ) 2629 return aNULL(); 2630 aResult = xRange->getNumberFormat(); 2631 if ( aNULL() == aResult ) 2632 return aNULL(); 2633 } 2634 return aResult; 2635 } 2636 NumFormatHelper numFormat( mxRange ); 2637 OUString sFormat = numFormat.getNumberFormatString(); 2638 if ( !sFormat.isEmpty() ) 2639 return uno::Any( sFormat ); 2640 return aNULL(); 2641 } 2642 2643 uno::Reference< excel::XRange > 2644 ScVbaRange::Resize( const uno::Any &RowSize, const uno::Any &ColumnSize ) 2645 { 2646 tools::Long nRowSize = 0, nColumnSize = 0; 2647 bool bIsRowChanged = ( RowSize >>= nRowSize ), bIsColumnChanged = ( ColumnSize >>= nColumnSize ); 2648 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, ::uno::UNO_QUERY_THROW); 2649 uno::Reference< sheet::XSheetCellRange > xSheetRange(mxRange, ::uno::UNO_QUERY_THROW); 2650 uno::Reference< sheet::XSheetCellCursor > xCursor( xSheetRange->getSpreadsheet()->createCursorByRange(xSheetRange), ::uno::UNO_SET_THROW ); 2651 2652 if( !bIsRowChanged ) 2653 nRowSize = xColumnRowRange->getRows()->getCount(); 2654 if( !bIsColumnChanged ) 2655 nColumnSize = xColumnRowRange->getColumns()->getCount(); 2656 2657 xCursor->collapseToSize( nColumnSize, nRowSize ); 2658 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xCursor, ::uno::UNO_QUERY_THROW ); 2659 uno::Reference< table::XCellRange > xRange( xSheetRange->getSpreadsheet(), ::uno::UNO_QUERY_THROW ); 2660 return new ScVbaRange( mxParent, mxContext,xRange->getCellRangeByPosition( 2661 xCellRangeAddressable->getRangeAddress().StartColumn, 2662 xCellRangeAddressable->getRangeAddress().StartRow, 2663 xCellRangeAddressable->getRangeAddress().EndColumn, 2664 xCellRangeAddressable->getRangeAddress().EndRow ) ); 2665 } 2666 2667 void 2668 ScVbaRange::setWrapText( const uno::Any& aIsWrapped ) 2669 { 2670 if ( m_Areas->getCount() > 1 ) 2671 { 2672 sal_Int32 nItems = m_Areas->getCount(); 2673 for ( sal_Int32 index=1; index <= nItems; ++index ) 2674 { 2675 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 2676 xRange->setWrapText( aIsWrapped ); 2677 } 2678 return; 2679 } 2680 2681 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY_THROW ); 2682 bool bIsWrapped = extractBoolFromAny( aIsWrapped ); 2683 xProps->setPropertyValue( "IsTextWrapped", uno::Any( bIsWrapped ) ); 2684 } 2685 2686 uno::Any 2687 ScVbaRange::getWrapText() 2688 { 2689 if ( m_Areas->getCount() > 1 ) 2690 { 2691 sal_Int32 nItems = m_Areas->getCount(); 2692 uno::Any aResult; 2693 for ( sal_Int32 index=1; index <= nItems; ++index ) 2694 { 2695 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 2696 if ( index > 1 ) 2697 if ( aResult != xRange->getWrapText() ) 2698 return aNULL(); 2699 aResult = xRange->getWrapText(); 2700 } 2701 return aResult; 2702 } 2703 2704 SfxItemSet* pDataSet = getCurrentDataSet(); 2705 2706 SfxItemState eState = pDataSet->GetItemState( ATTR_LINEBREAK); 2707 if ( eState == SfxItemState::DONTCARE ) 2708 return aNULL(); 2709 2710 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY_THROW ); 2711 uno::Any aValue = xProps->getPropertyValue( "IsTextWrapped" ); 2712 return aValue; 2713 } 2714 2715 uno::Reference< excel::XInterior > ScVbaRange::Interior( ) 2716 { 2717 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW ); 2718 return new ScVbaInterior ( this, mxContext, xProps, &getScDocument() ); 2719 } 2720 uno::Reference< excel::XRange > 2721 ScVbaRange::Range( const uno::Any &Cell1, const uno::Any &Cell2 ) 2722 { 2723 return Range( Cell1, Cell2, false ); 2724 } 2725 uno::Reference< excel::XRange > 2726 ScVbaRange::Range( const uno::Any &Cell1, const uno::Any &Cell2, bool bForceUseInpuRangeTab ) 2727 2728 { 2729 uno::Reference< table::XCellRange > xCellRange = mxRange; 2730 2731 if ( m_Areas->getCount() > 1 ) 2732 { 2733 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 2734 xCellRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW ); 2735 } 2736 else 2737 xCellRange.set( mxRange ); 2738 2739 RangeHelper thisRange( xCellRange ); 2740 uno::Reference< table::XCellRange > xRanges = thisRange.getCellRangeFromSheet(); 2741 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xRanges, uno::UNO_QUERY_THROW ); 2742 2743 uno::Reference< table::XCellRange > xReferrer = 2744 xRanges->getCellRangeByPosition( getColumn()-1, getRow()-1, 2745 xAddressable->getRangeAddress().EndColumn, 2746 xAddressable->getRangeAddress().EndRow ); 2747 // xAddressable now for this range 2748 xAddressable.set( xReferrer, uno::UNO_QUERY_THROW ); 2749 2750 if( !Cell1.hasValue() ) 2751 throw uno::RuntimeException( "Invalid Argument" ); 2752 2753 table::CellRangeAddress parentRangeAddress = xAddressable->getRangeAddress(); 2754 2755 ScRange aRange; 2756 // Cell1 defined only 2757 if ( !Cell2.hasValue() ) 2758 { 2759 OUString sName; 2760 Cell1 >>= sName; 2761 RangeHelper referRange( xReferrer ); 2762 table::CellRangeAddress referAddress = referRange.getCellRangeAddressable()->getRangeAddress(); 2763 return getRangeForName( mxContext, sName, getScDocShell(), referAddress ); 2764 2765 } 2766 else 2767 { 2768 table::CellRangeAddress cell1, cell2; 2769 cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell() ); 2770 // Cell1 & Cell2 defined 2771 // Excel seems to combine the range as the range defined by 2772 // the combination of Cell1 & Cell2 2773 2774 cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell() ); 2775 2776 table::CellRangeAddress resultAddress; 2777 resultAddress.StartColumn = ( cell1.StartColumn < cell2.StartColumn ) ? cell1.StartColumn : cell2.StartColumn; 2778 resultAddress.StartRow = ( cell1.StartRow < cell2.StartRow ) ? cell1.StartRow : cell2.StartRow; 2779 resultAddress.EndColumn = std::max( cell1.EndColumn, cell2.EndColumn ); 2780 resultAddress.EndRow = std::max( cell1.EndRow, cell2.EndRow ); 2781 if ( bForceUseInpuRangeTab ) 2782 { 2783 // this is a call from Application.Range( x,y ) 2784 // it's possible for x or y to specify a different sheet from 2785 // the current or active on ( but they must be the same ) 2786 if ( cell1.Sheet != cell2.Sheet ) 2787 throw uno::RuntimeException(); 2788 parentRangeAddress.Sheet = cell1.Sheet; 2789 } 2790 else 2791 { 2792 // this is not a call from Application.Range( x,y ) 2793 // if a different sheet from this range is specified it's 2794 // an error 2795 if ( parentRangeAddress.Sheet != cell1.Sheet 2796 || parentRangeAddress.Sheet != cell2.Sheet 2797 ) 2798 throw uno::RuntimeException(); 2799 2800 } 2801 ScUnoConversion::FillScRange( aRange, resultAddress ); 2802 } 2803 ScRange parentAddress; 2804 ScUnoConversion::FillScRange( parentAddress, parentRangeAddress); 2805 if ( aRange.aStart.Col() >= 0 && aRange.aStart.Row() >= 0 && aRange.aEnd.Col() >= 0 && aRange.aEnd.Row() >= 0 ) 2806 { 2807 sal_Int32 nStartX = parentAddress.aStart.Col() + aRange.aStart.Col(); 2808 sal_Int32 nStartY = parentAddress.aStart.Row() + aRange.aStart.Row(); 2809 sal_Int32 nEndX = parentAddress.aStart.Col() + aRange.aEnd.Col(); 2810 sal_Int32 nEndY = parentAddress.aStart.Row() + aRange.aEnd.Row(); 2811 2812 if ( nStartX <= nEndX && nEndX <= parentAddress.aEnd.Col() && 2813 nStartY <= nEndY && nEndY <= parentAddress.aEnd.Row() ) 2814 { 2815 ScRange aNew( static_cast<SCCOL>(nStartX), static_cast<SCROW>(nStartY), parentAddress.aStart.Tab(), 2816 static_cast<SCCOL>(nEndX), static_cast<SCROW>(nEndY), parentAddress.aEnd.Tab() ); 2817 xCellRange = new ScCellRangeObj( getScDocShell(), aNew ); 2818 } 2819 } 2820 2821 return new ScVbaRange( mxParent, mxContext, xCellRange ); 2822 2823 } 2824 2825 // Allow access to underlying openoffice uno api ( useful for debugging 2826 // with openoffice basic ) 2827 uno::Any SAL_CALL ScVbaRange::getCellRange( ) 2828 { 2829 uno::Any aAny; 2830 if ( mxRanges.is() ) 2831 aAny <<= mxRanges; 2832 else if ( mxRange.is() ) 2833 aAny <<= mxRange; 2834 return aAny; 2835 } 2836 2837 uno::Any ScVbaRange::getCellRange( const uno::Reference< excel::XRange >& rxRange ) 2838 { 2839 if( ScVbaRange* pVbaRange = getImplementation( rxRange ) ) 2840 return pVbaRange->getCellRange(); 2841 throw uno::RuntimeException(); 2842 } 2843 2844 static InsertDeleteFlags getPasteFlags (sal_Int32 Paste) 2845 { 2846 InsertDeleteFlags nFlags = InsertDeleteFlags::NONE; 2847 switch (Paste) { 2848 case excel::XlPasteType::xlPasteComments: 2849 nFlags = InsertDeleteFlags::NOTE;break; 2850 case excel::XlPasteType::xlPasteFormats: 2851 nFlags = InsertDeleteFlags::ATTRIB;break; 2852 case excel::XlPasteType::xlPasteFormulas: 2853 nFlags = InsertDeleteFlags::FORMULA;break; 2854 case excel::XlPasteType::xlPasteFormulasAndNumberFormats : 2855 case excel::XlPasteType::xlPasteValues: 2856 nFlags = ( InsertDeleteFlags::VALUE | InsertDeleteFlags::DATETIME | InsertDeleteFlags::STRING | InsertDeleteFlags::SPECIAL_BOOLEAN ); break; 2857 case excel::XlPasteType::xlPasteValuesAndNumberFormats: 2858 nFlags = InsertDeleteFlags::VALUE | InsertDeleteFlags::ATTRIB; break; 2859 case excel::XlPasteType::xlPasteColumnWidths: 2860 case excel::XlPasteType::xlPasteValidation: 2861 nFlags = InsertDeleteFlags::NONE;break; 2862 case excel::XlPasteType::xlPasteAll: 2863 case excel::XlPasteType::xlPasteAllExceptBorders: 2864 default: 2865 nFlags = InsertDeleteFlags::ALL;break; 2866 } 2867 return nFlags; 2868 } 2869 2870 static ScPasteFunc 2871 getPasteFormulaBits( sal_Int32 Operation) 2872 { 2873 ScPasteFunc nFormulaBits = ScPasteFunc::NONE; 2874 switch (Operation) 2875 { 2876 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationAdd: 2877 nFormulaBits = ScPasteFunc::ADD; break; 2878 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationSubtract: 2879 nFormulaBits = ScPasteFunc::SUB;break; 2880 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationMultiply: 2881 nFormulaBits = ScPasteFunc::MUL;break; 2882 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationDivide: 2883 nFormulaBits = ScPasteFunc::DIV;break; 2884 2885 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationNone: 2886 default: 2887 nFormulaBits = ScPasteFunc::NONE; break; 2888 } 2889 2890 return nFormulaBits; 2891 } 2892 void SAL_CALL 2893 ScVbaRange::PasteSpecial( const uno::Any& Paste, const uno::Any& Operation, const uno::Any& SkipBlanks, const uno::Any& Transpose ) 2894 { 2895 if ( m_Areas->getCount() > 1 ) 2896 throw uno::RuntimeException("That command cannot be used on multiple selections" ); 2897 ScDocShell* pShell = getScDocShell(); 2898 2899 if (!pShell) 2900 throw uno::RuntimeException("That command cannot be used with no ScDocShell" ); 2901 2902 uno::Reference< frame::XModel > xModel(pShell->GetModel(), uno::UNO_SET_THROW); 2903 uno::Reference< view::XSelectionSupplier > xSelection( xModel->getCurrentController(), uno::UNO_QUERY_THROW ); 2904 // select this range 2905 xSelection->select( uno::Any( mxRange ) ); 2906 // set up defaults 2907 sal_Int32 nPaste = excel::XlPasteType::xlPasteAll; 2908 sal_Int32 nOperation = excel::XlPasteSpecialOperation::xlPasteSpecialOperationNone; 2909 bool bTranspose = false; 2910 bool bSkipBlanks = false; 2911 2912 if ( Paste.hasValue() ) 2913 Paste >>= nPaste; 2914 if ( Operation.hasValue() ) 2915 Operation >>= nOperation; 2916 if ( SkipBlanks.hasValue() ) 2917 SkipBlanks >>= bSkipBlanks; 2918 if ( Transpose.hasValue() ) 2919 Transpose >>= bTranspose; 2920 2921 InsertDeleteFlags nFlags = getPasteFlags(nPaste); 2922 ScPasteFunc nFormulaBits = getPasteFormulaBits(nOperation); 2923 2924 excel::implnPasteSpecial(xModel, nFlags, nFormulaBits, bSkipBlanks, bTranspose); 2925 } 2926 2927 uno::Reference< excel::XRange > 2928 ScVbaRange::getEntireColumnOrRow( bool bColumn ) 2929 { 2930 ScCellRangesBase* pUnoRangesBase = getCellRangesBase(); 2931 // copy the range list 2932 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList(); 2933 ScDocument& rDoc = getScDocument(); 2934 2935 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i ) 2936 { 2937 ScRange & rRange = aCellRanges[ i ]; 2938 if ( bColumn ) 2939 { 2940 rRange.aStart.SetRow( 0 ); 2941 rRange.aEnd.SetRow( rDoc.MaxRow() ); 2942 } 2943 else 2944 { 2945 rRange.aStart.SetCol( 0 ); 2946 rRange.aEnd.SetCol( rDoc.MaxCol() ); 2947 } 2948 } 2949 if ( aCellRanges.size() > 1 ) // Multi-Area 2950 { 2951 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pUnoRangesBase->GetDocShell(), aCellRanges ) ); 2952 2953 return new ScVbaRange( mxParent, mxContext, xRanges, !bColumn, bColumn ); 2954 } 2955 const ScRange aRange( obtainRangeEvenIfRangeListIsEmpty( aCellRanges)); 2956 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange)); 2957 return new ScVbaRange( mxParent, mxContext, xRange, !bColumn, bColumn ); 2958 } 2959 2960 uno::Reference< excel::XRange > SAL_CALL 2961 ScVbaRange::getEntireRow() 2962 { 2963 return getEntireColumnOrRow(false); 2964 } 2965 2966 uno::Reference< excel::XRange > SAL_CALL 2967 ScVbaRange::getEntireColumn() 2968 { 2969 return getEntireColumnOrRow(true); 2970 } 2971 2972 uno::Reference< excel::XComment > SAL_CALL 2973 ScVbaRange::AddComment( const uno::Any& Text ) 2974 { 2975 // if there is already a comment in the top-left cell then throw 2976 if( getComment().is() ) 2977 throw uno::RuntimeException(); 2978 2979 // workaround: Excel allows to create empty comment, Calc does not 2980 OUString aNoteText; 2981 if( Text.hasValue() && !(Text >>= aNoteText) ) 2982 throw uno::RuntimeException(); 2983 if( aNoteText.isEmpty() ) 2984 aNoteText = " "; 2985 2986 // try to create a new annotation 2987 table::CellRangeAddress aRangePos = lclGetRangeAddress( mxRange ); 2988 table::CellAddress aNotePos( aRangePos.Sheet, aRangePos.StartColumn, aRangePos.StartRow ); 2989 uno::Reference< sheet::XSheetCellRange > xCellRange( mxRange, uno::UNO_QUERY_THROW ); 2990 uno::Reference< sheet::XSheetAnnotationsSupplier > xAnnosSupp( xCellRange->getSpreadsheet(), uno::UNO_QUERY_THROW ); 2991 uno::Reference< sheet::XSheetAnnotations > xAnnos( xAnnosSupp->getAnnotations(), uno::UNO_SET_THROW ); 2992 xAnnos->insertNew( aNotePos, aNoteText ); 2993 return new ScVbaComment( this, mxContext, getUnoModel(), mxRange ); 2994 } 2995 2996 uno::Reference< excel::XComment > SAL_CALL 2997 ScVbaRange::getComment() 2998 { 2999 // intentional behavior to return a null object if no 3000 // comment defined 3001 uno::Reference< excel::XComment > xComment( new ScVbaComment( this, mxContext, getUnoModel(), mxRange ) ); 3002 if ( xComment->Text( uno::Any(), uno::Any(), uno::Any() ).isEmpty() ) 3003 return nullptr; 3004 return xComment; 3005 3006 } 3007 3008 /// @throws uno::RuntimeException 3009 static uno::Reference< beans::XPropertySet > 3010 getRowOrColumnProps( const uno::Reference< table::XCellRange >& xCellRange, bool bRows ) 3011 { 3012 uno::Reference< table::XColumnRowRange > xColRow( xCellRange, uno::UNO_QUERY_THROW ); 3013 uno::Reference< beans::XPropertySet > xProps; 3014 if ( bRows ) 3015 xProps.set( xColRow->getRows(), uno::UNO_QUERY_THROW ); 3016 else 3017 xProps.set( xColRow->getColumns(), uno::UNO_QUERY_THROW ); 3018 return xProps; 3019 } 3020 3021 uno::Any SAL_CALL 3022 ScVbaRange::getHidden() 3023 { 3024 // if multi-area result is the result of the 3025 // first area 3026 if ( m_Areas->getCount() > 1 ) 3027 { 3028 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(sal_Int32(1)), uno::Any() ), uno::UNO_QUERY_THROW ); 3029 return xRange->getHidden(); 3030 } 3031 bool bIsVisible = false; 3032 try 3033 { 3034 uno::Reference< beans::XPropertySet > xProps = getRowOrColumnProps( mxRange, mbIsRows ); 3035 if ( !( xProps->getPropertyValue( ISVISIBLE ) >>= bIsVisible ) ) 3036 throw uno::RuntimeException("Failed to get IsVisible property" ); 3037 } 3038 catch( const uno::Exception& e ) 3039 { 3040 css::uno::Any anyEx = cppu::getCaughtException(); 3041 throw css::lang::WrappedTargetRuntimeException( e.Message, 3042 nullptr, anyEx ); 3043 } 3044 return uno::Any( !bIsVisible ); 3045 } 3046 3047 void SAL_CALL 3048 ScVbaRange::setHidden( const uno::Any& _hidden ) 3049 { 3050 if ( m_Areas->getCount() > 1 ) 3051 { 3052 sal_Int32 nItems = m_Areas->getCount(); 3053 for ( sal_Int32 index=1; index <= nItems; ++index ) 3054 { 3055 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 3056 xRange->setHidden( _hidden ); 3057 } 3058 return; 3059 } 3060 3061 bool bHidden = extractBoolFromAny( _hidden ); 3062 try 3063 { 3064 uno::Reference< beans::XPropertySet > xProps = getRowOrColumnProps( mxRange, mbIsRows ); 3065 xProps->setPropertyValue( ISVISIBLE, uno::Any( !bHidden ) ); 3066 } 3067 catch( const uno::Exception& e ) 3068 { 3069 css::uno::Any anyEx = cppu::getCaughtException(); 3070 throw css::lang::WrappedTargetRuntimeException( e.Message, 3071 nullptr, anyEx ); 3072 } 3073 } 3074 3075 sal_Bool SAL_CALL 3076 ScVbaRange::Replace( const OUString& What, const OUString& Replacement, const uno::Any& LookAt, const uno::Any& SearchOrder, const uno::Any& MatchCase, const uno::Any& MatchByte, const uno::Any& SearchFormat, const uno::Any& ReplaceFormat ) 3077 { 3078 if ( m_Areas->getCount() > 1 ) 3079 { 3080 for ( sal_Int32 index = 1; index <= m_Areas->getCount(); ++index ) 3081 { 3082 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 3083 xRange->Replace( What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat ); 3084 } 3085 return true; // seems to return true always ( or at least I haven't found the trick of 3086 } 3087 3088 // sanity check required params 3089 if ( What.isEmpty() ) 3090 throw uno::RuntimeException("Range::Replace, missing params" ); 3091 OUString sWhat = VBAToRegexp( What); 3092 // #TODO #FIXME SearchFormat & ReplacesFormat are not processed 3093 // What do we do about MatchByte... we don't seem to support that 3094 const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem(); 3095 SvxSearchItem newOptions( globalSearchOptions ); 3096 3097 uno::Reference< util::XReplaceable > xReplace( mxRange, uno::UNO_QUERY ); 3098 if ( xReplace.is() ) 3099 { 3100 uno::Reference< util::XReplaceDescriptor > xDescriptor = 3101 xReplace->createReplaceDescriptor(); 3102 3103 xDescriptor->setSearchString( sWhat); 3104 xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::Any( true ) ); 3105 xDescriptor->setReplaceString( Replacement); 3106 if ( LookAt.hasValue() ) 3107 { 3108 // sets SearchWords ( true is Cell match ) 3109 sal_Int16 nLook = ::comphelper::getINT16( LookAt ); 3110 bool bSearchWords = false; 3111 if ( nLook == excel::XlLookAt::xlPart ) 3112 bSearchWords = false; 3113 else if ( nLook == excel::XlLookAt::xlWhole ) 3114 bSearchWords = true; 3115 else 3116 throw uno::RuntimeException("Range::Replace, illegal value for LookAt" ); 3117 // set global search props ( affects the find dialog 3118 // and of course the defaults for this method 3119 newOptions.SetWordOnly( bSearchWords ); 3120 xDescriptor->setPropertyValue( SC_UNO_SRCHWORDS, uno::Any( bSearchWords ) ); 3121 } 3122 // sets SearchByRow ( true for Rows ) 3123 if ( SearchOrder.hasValue() ) 3124 { 3125 sal_Int16 nSearchOrder = ::comphelper::getINT16( SearchOrder ); 3126 bool bSearchByRow = false; 3127 if ( nSearchOrder == excel::XlSearchOrder::xlByColumns ) 3128 bSearchByRow = false; 3129 else if ( nSearchOrder == excel::XlSearchOrder::xlByRows ) 3130 bSearchByRow = true; 3131 else 3132 throw uno::RuntimeException("Range::Replace, illegal value for SearchOrder" ); 3133 3134 newOptions.SetRowDirection( bSearchByRow ); 3135 xDescriptor->setPropertyValue( SC_UNO_SRCHBYROW, uno::Any( bSearchByRow ) ); 3136 } 3137 if ( MatchCase.hasValue() ) 3138 { 3139 bool bMatchCase = false; 3140 3141 // SearchCaseSensitive 3142 MatchCase >>= bMatchCase; 3143 xDescriptor->setPropertyValue( SC_UNO_SRCHCASE, uno::Any( bMatchCase ) ); 3144 } 3145 3146 ScGlobal::SetSearchItem( newOptions ); 3147 // ignore MatchByte for the moment, it's not supported in 3148 // OOo.org afaik 3149 3150 uno::Reference< container::XIndexAccess > xIndexAccess = xReplace->findAll( xDescriptor ); 3151 xReplace->replaceAll( xDescriptor ); 3152 if ( xIndexAccess.is() && xIndexAccess->getCount() > 0 ) 3153 { 3154 for ( sal_Int32 i = 0; i < xIndexAccess->getCount(); ++i ) 3155 { 3156 uno::Reference< table::XCellRange > xCellRange( xIndexAccess->getByIndex( i ), uno::UNO_QUERY ); 3157 if ( xCellRange.is() ) 3158 { 3159 uno::Reference< excel::XRange > xRange( new ScVbaRange( mxParent, mxContext, xCellRange ) ); 3160 uno::Reference< container::XEnumerationAccess > xEnumAccess( xRange, uno::UNO_QUERY_THROW ); 3161 uno::Reference< container::XEnumeration > xEnum = xEnumAccess->createEnumeration(); 3162 while ( xEnum->hasMoreElements() ) 3163 { 3164 uno::Reference< excel::XRange > xNextRange( xEnum->nextElement(), uno::UNO_QUERY_THROW ); 3165 ScVbaRange* pRange = dynamic_cast< ScVbaRange * > ( xNextRange.get() ); 3166 if ( pRange ) 3167 pRange->fireChangeEvent(); 3168 } 3169 } 3170 } 3171 } 3172 } 3173 return true; // always 3174 } 3175 3176 uno::Reference< excel::XRange > SAL_CALL 3177 ScVbaRange::Find( const uno::Any& What, const uno::Any& After, const uno::Any& LookIn, const uno::Any& LookAt, const uno::Any& SearchOrder, const uno::Any& SearchDirection, const uno::Any& MatchCase, const uno::Any& /*MatchByte*/, const uno::Any& /*SearchFormat*/ ) 3178 { 3179 // return a Range object that represents the first cell where that information is found. 3180 OUString sWhat; 3181 sal_Int32 nWhat = 0; 3182 double fWhat = 0.0; 3183 3184 // string. 3185 if( What >>= sWhat ) 3186 {} 3187 else if( What >>= nWhat ) 3188 { 3189 sWhat = OUString::number( nWhat ); 3190 } 3191 else if( What >>= fWhat ) 3192 { 3193 sWhat = OUString::number( fWhat ); 3194 } 3195 else 3196 throw uno::RuntimeException("Range::Find, missing search-for-what param" ); 3197 3198 OUString sSearch = VBAToRegexp( sWhat ); 3199 3200 const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem(); 3201 SvxSearchItem newOptions( globalSearchOptions ); 3202 3203 uno::Reference< util::XSearchable > xSearch( mxRange, uno::UNO_QUERY ); 3204 if( xSearch.is() ) 3205 { 3206 uno::Reference< util::XSearchDescriptor > xDescriptor = xSearch->createSearchDescriptor(); 3207 xDescriptor->setSearchString( sSearch ); 3208 xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::Any( true ) ); 3209 3210 uno::Reference< excel::XRange > xAfterRange; 3211 uno::Reference< table::XCellRange > xStartCell; 3212 if( After >>= xAfterRange ) 3213 { 3214 // After must be a single cell in the range 3215 if( xAfterRange->getCount() > 1 ) 3216 throw uno::RuntimeException("After must be a single cell." ); 3217 uno::Reference< excel::XRange > xCell( Cells( uno::Any( xAfterRange->getRow() ), uno::Any( xAfterRange->getColumn() ) ), uno::UNO_SET_THROW ); 3218 xStartCell.set( xAfterRange->getCellRange(), uno::UNO_QUERY_THROW ); 3219 } 3220 3221 // LookIn 3222 if( LookIn.hasValue() ) 3223 { 3224 sal_Int32 nLookIn = 0; 3225 if( LookIn >>= nLookIn ) 3226 { 3227 SvxSearchCellType nSearchType; 3228 switch( nLookIn ) 3229 { 3230 case excel::XlFindLookIn::xlComments : 3231 nSearchType = SvxSearchCellType::NOTE; // Notes 3232 break; 3233 case excel::XlFindLookIn::xlFormulas : 3234 nSearchType = SvxSearchCellType::FORMULA; 3235 break; 3236 case excel::XlFindLookIn::xlValues : 3237 nSearchType = SvxSearchCellType::VALUE; 3238 break; 3239 default: 3240 throw uno::RuntimeException("Range::Find, illegal value for LookIn." ); 3241 } 3242 newOptions.SetCellType( nSearchType ); 3243 xDescriptor->setPropertyValue( "SearchType", uno::Any( static_cast<sal_uInt16>(nSearchType) ) ); 3244 } 3245 } 3246 3247 // LookAt 3248 if ( LookAt.hasValue() ) 3249 { 3250 sal_Int16 nLookAt = ::comphelper::getINT16( LookAt ); 3251 bool bSearchWords = false; 3252 if ( nLookAt == excel::XlLookAt::xlPart ) 3253 bSearchWords = false; 3254 else if ( nLookAt == excel::XlLookAt::xlWhole ) 3255 bSearchWords = true; 3256 else 3257 throw uno::RuntimeException("Range::Find, illegal value for LookAt" ); 3258 newOptions.SetWordOnly( bSearchWords ); 3259 xDescriptor->setPropertyValue( SC_UNO_SRCHWORDS, uno::Any( bSearchWords ) ); 3260 } 3261 3262 // SearchOrder 3263 if ( SearchOrder.hasValue() ) 3264 { 3265 sal_Int16 nSearchOrder = ::comphelper::getINT16( SearchOrder ); 3266 bool bSearchByRow = false; 3267 if ( nSearchOrder == excel::XlSearchOrder::xlByColumns ) 3268 bSearchByRow = false; 3269 else if ( nSearchOrder == excel::XlSearchOrder::xlByRows ) 3270 bSearchByRow = true; 3271 else 3272 throw uno::RuntimeException("Range::Find, illegal value for SearchOrder" ); 3273 3274 newOptions.SetRowDirection( bSearchByRow ); 3275 xDescriptor->setPropertyValue( SC_UNO_SRCHBYROW, uno::Any( bSearchByRow ) ); 3276 } 3277 3278 // SearchDirection 3279 if ( SearchDirection.hasValue() ) 3280 { 3281 sal_Int32 nSearchDirection = 0; 3282 if( SearchDirection >>= nSearchDirection ) 3283 { 3284 bool bSearchBackwards = false; 3285 if ( nSearchDirection == excel::XlSearchDirection::xlNext ) 3286 bSearchBackwards = false; 3287 else if( nSearchDirection == excel::XlSearchDirection::xlPrevious ) 3288 bSearchBackwards = true; 3289 else 3290 throw uno::RuntimeException("Range::Find, illegal value for SearchDirection" ); 3291 newOptions.SetBackward( bSearchBackwards ); 3292 xDescriptor->setPropertyValue( "SearchBackwards", uno::Any( bSearchBackwards ) ); 3293 } 3294 } 3295 3296 // MatchCase 3297 bool bMatchCase = false; 3298 if ( MatchCase.hasValue() ) 3299 { 3300 // SearchCaseSensitive 3301 if( !( MatchCase >>= bMatchCase ) ) 3302 throw uno::RuntimeException("Range::Find illegal value for MatchCase" ); 3303 } 3304 xDescriptor->setPropertyValue( SC_UNO_SRCHCASE, uno::Any( bMatchCase ) ); 3305 3306 // MatchByte 3307 // SearchFormat 3308 // ignore 3309 3310 ScGlobal::SetSearchItem( newOptions ); 3311 3312 uno::Reference< uno::XInterface > xInterface = xStartCell.is() ? xSearch->findNext( xStartCell, xDescriptor) : xSearch->findFirst( xDescriptor ); 3313 uno::Reference< table::XCellRange > xCellRange( xInterface, uno::UNO_QUERY ); 3314 // if we are searching from a starting cell and failed to find a match 3315 // then try from the beginning 3316 if ( !xCellRange.is() && xStartCell.is() ) 3317 { 3318 xInterface = xSearch->findFirst( xDescriptor ); 3319 xCellRange.set( xInterface, uno::UNO_QUERY ); 3320 } 3321 if ( xCellRange.is() ) 3322 { 3323 uno::Reference< excel::XRange > xResultRange = new ScVbaRange( mxParent, mxContext, xCellRange ); 3324 if( xResultRange.is() ) 3325 { 3326 return xResultRange; 3327 } 3328 } 3329 3330 } 3331 3332 return uno::Reference< excel::XRange >(); 3333 } 3334 3335 static uno::Reference< table::XCellRange > processKey( const uno::Any& Key, const uno::Reference< uno::XComponentContext >& xContext, ScDocShell* pDocSh ) 3336 { 3337 uno::Reference< excel::XRange > xKeyRange; 3338 if ( Key.getValueType() == cppu::UnoType<excel::XRange>::get() ) 3339 { 3340 xKeyRange.set( Key, uno::UNO_QUERY_THROW ); 3341 } 3342 else if ( Key.getValueType() == ::cppu::UnoType<OUString>::get() ) 3343 3344 { 3345 OUString sRangeName = ::comphelper::getString( Key ); 3346 table::CellRangeAddress aRefAddr; 3347 if ( !pDocSh ) 3348 throw uno::RuntimeException("Range::Sort no docshell to calculate key param" ); 3349 xKeyRange = getRangeForName( xContext, sRangeName, pDocSh, aRefAddr ); 3350 } 3351 else 3352 throw uno::RuntimeException("Range::Sort illegal type value for key param" ); 3353 uno::Reference< table::XCellRange > xKey; 3354 xKey.set( xKeyRange->getCellRange(), uno::UNO_QUERY_THROW ); 3355 return xKey; 3356 } 3357 3358 // helper method for Sort 3359 /// @throws uno::RuntimeException 3360 static sal_Int32 findSortPropertyIndex( const uno::Sequence< beans::PropertyValue >& props, 3361 const OUString& sPropName ) 3362 { 3363 const beans::PropertyValue* pProp = std::find_if(props.begin(), props.end(), 3364 [&sPropName](const beans::PropertyValue& rProp) { return rProp.Name == sPropName; }); 3365 3366 if ( pProp == props.end() ) 3367 throw uno::RuntimeException("Range::Sort unknown sort property" ); 3368 return static_cast<sal_Int32>(std::distance(props.begin(), pProp)); 3369 } 3370 3371 // helper method for Sort 3372 /// @throws uno::RuntimeException 3373 static void updateTableSortField( const uno::Reference< table::XCellRange >& xParentRange, 3374 const uno::Reference< table::XCellRange >& xColRowKey, sal_Int16 nOrder, 3375 table::TableSortField& aTableField, bool bIsSortColumn, bool bMatchCase ) 3376 { 3377 RangeHelper parentRange( xParentRange ); 3378 RangeHelper colRowRange( xColRowKey ); 3379 3380 table::CellRangeAddress parentRangeAddress = parentRange.getCellRangeAddressable()->getRangeAddress(); 3381 3382 table::CellRangeAddress colRowKeyAddress = colRowRange.getCellRangeAddressable()->getRangeAddress(); 3383 3384 // make sure that upper left point of key range is within the 3385 // parent range 3386 if ( 3387 ( bIsSortColumn || colRowKeyAddress.StartColumn < parentRangeAddress.StartColumn || 3388 colRowKeyAddress.StartColumn > parentRangeAddress.EndColumn ) 3389 && 3390 ( !bIsSortColumn || colRowKeyAddress.StartRow < parentRangeAddress.StartRow || 3391 colRowKeyAddress.StartRow > parentRangeAddress.EndRow ) 3392 ) 3393 throw uno::RuntimeException("Illegal Key param" ); 3394 3395 //determine col/row index 3396 if ( bIsSortColumn ) 3397 aTableField.Field = colRowKeyAddress.StartRow - parentRangeAddress.StartRow; 3398 else 3399 aTableField.Field = colRowKeyAddress.StartColumn - parentRangeAddress.StartColumn; 3400 aTableField.IsCaseSensitive = bMatchCase; 3401 3402 if ( nOrder == excel::XlSortOrder::xlAscending ) 3403 aTableField.IsAscending = true; 3404 else 3405 aTableField.IsAscending = false; 3406 3407 3408 } 3409 3410 void SAL_CALL 3411 ScVbaRange::Sort( const uno::Any& Key1, const uno::Any& Order1, const uno::Any& Key2, const uno::Any& /*Type*/, const uno::Any& Order2, const uno::Any& Key3, const uno::Any& Order3, const uno::Any& Header, const uno::Any& OrderCustom, const uno::Any& MatchCase, const uno::Any& Orientation, const uno::Any& SortMethod, const uno::Any& DataOption1, const uno::Any& DataOption2, const uno::Any& DataOption3 ) 3412 { 3413 // #TODO# #FIXME# can we do something with Type 3414 if ( m_Areas->getCount() > 1 ) 3415 throw uno::RuntimeException("That command cannot be used on multiple selections" ); 3416 3417 sal_Int16 nDataOption1 = excel::XlSortDataOption::xlSortNormal; 3418 sal_Int16 nDataOption2 = excel::XlSortDataOption::xlSortNormal; 3419 sal_Int16 nDataOption3 = excel::XlSortDataOption::xlSortNormal; 3420 3421 ScDocument& rDoc = getScDocument(); 3422 3423 RangeHelper thisRange( mxRange ); 3424 table::CellRangeAddress thisRangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 3425 ScSortParam aSortParam; 3426 SCTAB nTab = thisRangeAddress.Sheet; 3427 rDoc.GetSortParam( aSortParam, nTab ); 3428 3429 if ( DataOption1.hasValue() ) 3430 DataOption1 >>= nDataOption1; 3431 if ( DataOption2.hasValue() ) 3432 DataOption2 >>= nDataOption2; 3433 if ( DataOption3.hasValue() ) 3434 DataOption3 >>= nDataOption3; 3435 3436 // 1) #TODO #FIXME need to process DataOption[1..3] not used currently 3437 // 2) #TODO #FIXME need to refactor this ( below ) into an IsSingleCell() method 3438 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW ); 3439 3440 // set up defaults 3441 3442 sal_Int16 nOrder1 = aSortParam.maKeyState[0].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending; 3443 sal_Int16 nOrder2 = aSortParam.maKeyState[1].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending; 3444 sal_Int16 nOrder3 = aSortParam.maKeyState[2].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending; 3445 3446 sal_Int16 nCustom = aSortParam.nUserIndex; 3447 sal_Int16 nSortMethod = excel::XlSortMethod::xlPinYin; 3448 bool bMatchCase = aSortParam.bCaseSens; 3449 3450 // seems to work opposite to expected, see below 3451 sal_Int16 nOrientation = aSortParam.bByRow ? excel::XlSortOrientation::xlSortColumns : excel::XlSortOrientation::xlSortRows; 3452 3453 if ( Orientation.hasValue() ) 3454 { 3455 // Documentation says xlSortRows is default but that doesn't appear to be 3456 // the case. Also it appears that xlSortColumns is the default which 3457 // strangely enough sorts by Row 3458 nOrientation = ::comphelper::getINT16( Orientation ); 3459 // persist new option to be next calls default 3460 if ( nOrientation == excel::XlSortOrientation::xlSortRows ) 3461 aSortParam.bByRow = false; 3462 else 3463 aSortParam.bByRow = true; 3464 3465 } 3466 3467 bool bIsSortColumns=false; // sort by row 3468 3469 if ( nOrientation == excel::XlSortOrientation::xlSortRows ) 3470 bIsSortColumns = true; 3471 sal_Int16 nHeader = aSortParam.nCompatHeader; 3472 bool bContainsHeader = false; 3473 3474 if ( Header.hasValue() ) 3475 { 3476 nHeader = ::comphelper::getINT16( Header ); 3477 aSortParam.nCompatHeader = nHeader; 3478 } 3479 3480 if ( nHeader == excel::XlYesNoGuess::xlGuess ) 3481 { 3482 bool bHasColHeader = rDoc.HasColHeader( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), static_cast< SCCOL >( thisRangeAddress.EndColumn ), static_cast< SCROW >( thisRangeAddress.EndRow ), static_cast< SCTAB >( thisRangeAddress.Sheet )); 3483 bool bHasRowHeader = rDoc.HasRowHeader( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), static_cast< SCCOL >( thisRangeAddress.EndColumn ), static_cast< SCROW >( thisRangeAddress.EndRow ), static_cast< SCTAB >( thisRangeAddress.Sheet ) ); 3484 if ( bHasColHeader || bHasRowHeader ) 3485 nHeader = excel::XlYesNoGuess::xlYes; 3486 else 3487 nHeader = excel::XlYesNoGuess::xlNo; 3488 aSortParam.nCompatHeader = nHeader; 3489 } 3490 3491 if ( nHeader == excel::XlYesNoGuess::xlYes ) 3492 bContainsHeader = true; 3493 3494 if ( SortMethod.hasValue() ) 3495 { 3496 nSortMethod = ::comphelper::getINT16( SortMethod ); 3497 } 3498 3499 if ( OrderCustom.hasValue() ) 3500 { 3501 OrderCustom >>= nCustom; 3502 --nCustom; // 0-based in OOo 3503 aSortParam.nUserIndex = nCustom; 3504 } 3505 3506 if ( MatchCase.hasValue() ) 3507 { 3508 MatchCase >>= bMatchCase; 3509 aSortParam.bCaseSens = bMatchCase; 3510 } 3511 3512 if ( Order1.hasValue() ) 3513 { 3514 nOrder1 = ::comphelper::getINT16(Order1); 3515 if ( nOrder1 == excel::XlSortOrder::xlAscending ) 3516 aSortParam.maKeyState[0].bAscending = true; 3517 else 3518 aSortParam.maKeyState[0].bAscending = false; 3519 3520 } 3521 if ( Order2.hasValue() ) 3522 { 3523 nOrder2 = ::comphelper::getINT16(Order2); 3524 if ( nOrder2 == excel::XlSortOrder::xlAscending ) 3525 aSortParam.maKeyState[1].bAscending = true; 3526 else 3527 aSortParam.maKeyState[1].bAscending = false; 3528 } 3529 if ( Order3.hasValue() ) 3530 { 3531 nOrder3 = ::comphelper::getINT16(Order3); 3532 if ( nOrder3 == excel::XlSortOrder::xlAscending ) 3533 aSortParam.maKeyState[2].bAscending = true; 3534 else 3535 aSortParam.maKeyState[2].bAscending = false; 3536 } 3537 3538 uno::Reference< table::XCellRange > xKey1; 3539 uno::Reference< table::XCellRange > xKey2; 3540 uno::Reference< table::XCellRange > xKey3; 3541 ScDocShell* pDocShell = getScDocShell(); 3542 xKey1 = processKey( Key1, mxContext, pDocShell ); 3543 if ( !xKey1.is() ) 3544 throw uno::RuntimeException("Range::Sort needs a key1 param" ); 3545 3546 if ( Key2.hasValue() ) 3547 xKey2 = processKey( Key2, mxContext, pDocShell ); 3548 if ( Key3.hasValue() ) 3549 xKey3 = processKey( Key3, mxContext, pDocShell ); 3550 3551 uno::Reference< util::XSortable > xSort( mxRange, uno::UNO_QUERY_THROW ); 3552 uno::Sequence< beans::PropertyValue > sortDescriptor = xSort->createSortDescriptor(); 3553 auto psortDescriptor = sortDescriptor.getArray(); 3554 sal_Int32 nTableSortFieldIndex = findSortPropertyIndex( sortDescriptor, "SortFields" ); 3555 3556 uno::Sequence< table::TableSortField > sTableFields(1); 3557 sal_Int32 nTableIndex = 0; 3558 updateTableSortField( mxRange, xKey1, nOrder1, sTableFields.getArray()[ nTableIndex++ ], bIsSortColumns, bMatchCase ); 3559 3560 if ( xKey2.is() ) 3561 { 3562 sTableFields.realloc( sTableFields.getLength() + 1 ); 3563 updateTableSortField( mxRange, xKey2, nOrder2, sTableFields.getArray()[ nTableIndex++ ], bIsSortColumns, bMatchCase ); 3564 } 3565 if ( xKey3.is() ) 3566 { 3567 sTableFields.realloc( sTableFields.getLength() + 1 ); 3568 updateTableSortField( mxRange, xKey3, nOrder3, sTableFields.getArray()[ nTableIndex++ ], bIsSortColumns, bMatchCase ); 3569 } 3570 psortDescriptor[ nTableSortFieldIndex ].Value <<= sTableFields; 3571 3572 sal_Int32 nIndex = findSortPropertyIndex( sortDescriptor, "IsSortColumns" ); 3573 psortDescriptor[ nIndex ].Value <<= bIsSortColumns; 3574 3575 nIndex = findSortPropertyIndex( sortDescriptor, "ContainsHeader" ); 3576 psortDescriptor[ nIndex ].Value <<= bContainsHeader; 3577 3578 rDoc.SetSortParam( aSortParam, nTab ); 3579 xSort->sort( sortDescriptor ); 3580 3581 // #FIXME #TODO 3582 // The SortMethod param is not processed ( not sure what its all about, need to 3583 (void)nSortMethod; 3584 } 3585 3586 uno::Reference< excel::XRange > SAL_CALL 3587 ScVbaRange::End( ::sal_Int32 Direction ) 3588 { 3589 if ( m_Areas->getCount() > 1 ) 3590 { 3591 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_SET_THROW ); 3592 return xRange->End( Direction ); 3593 } 3594 3595 // #FIXME #TODO 3596 // euch! found my orig implementation sucked, so 3597 // trying this even sucker one (really need to use/expose code in 3598 // around ScTabView::MoveCursorArea(), that's the bit that calculates 3599 // where the cursor should go) 3600 // Main problem with this method is the ultra hacky attempt to preserve 3601 // the ActiveCell, there should be no need to go to these extremes 3602 3603 // Save ActiveSheet/ActiveCell pos (to restore later) 3604 uno::Any aDft; 3605 uno::Reference< excel::XApplication > xApplication( Application(), uno::UNO_QUERY_THROW ); 3606 uno::Reference< excel::XWorksheet > sActiveSheet = xApplication->getActiveSheet(); 3607 OUString sActiveCell = xApplication->getActiveCell()->Address(aDft, aDft, aDft, aDft, aDft ); 3608 3609 // position current cell upper left of this range 3610 Cells( uno::Any( sal_Int32(1) ), uno::Any( sal_Int32(1) ) )->Select(); 3611 3612 uno::Reference< frame::XModel > xModel = getModelFromRange( mxRange ); 3613 3614 SfxViewFrame* pViewFrame = excel::getViewFrame( xModel ); 3615 if ( pViewFrame ) 3616 { 3617 SfxAllItemSet aArgs( SfxGetpApp()->GetPool() ); 3618 // Hoping this will make sure this slot is called 3619 // synchronously 3620 SfxBoolItem sfxAsync( SID_ASYNCHRON, false ); 3621 aArgs.Put( sfxAsync, sfxAsync.Which() ); 3622 SfxDispatcher* pDispatcher = pViewFrame->GetDispatcher(); 3623 3624 sal_uInt16 nSID = 0; 3625 3626 switch( Direction ) 3627 { 3628 case excel::XlDirection::xlDown: 3629 nSID = SID_CURSORBLKDOWN; 3630 break; 3631 case excel::XlDirection::xlUp: 3632 nSID = SID_CURSORBLKUP; 3633 break; 3634 case excel::XlDirection::xlToLeft: 3635 nSID = SID_CURSORBLKLEFT; 3636 break; 3637 case excel::XlDirection::xlToRight: 3638 nSID = SID_CURSORBLKRIGHT; 3639 break; 3640 default: 3641 throw uno::RuntimeException(": Invalid ColumnIndex" ); 3642 } 3643 if ( pDispatcher ) 3644 { 3645 pDispatcher->Execute( nSID, SfxCallMode::SYNCHRON, aArgs ); 3646 } 3647 } 3648 3649 // result is the ActiveCell 3650 OUString sMoved = xApplication->getActiveCell()->Address(aDft, aDft, aDft, aDft, aDft ); 3651 3652 uno::Any aVoid; 3653 uno::Reference< excel::XRange > resultCell; 3654 resultCell.set( xApplication->getActiveSheet()->Range( uno::Any( sMoved ), aVoid ), uno::UNO_SET_THROW ); 3655 3656 // restore old ActiveCell 3657 uno::Reference< excel::XRange > xOldActiveCell( sActiveSheet->Range( uno::Any( sActiveCell ), aVoid ), uno::UNO_SET_THROW ); 3658 xOldActiveCell->Select(); 3659 3660 3661 // return result 3662 return resultCell; 3663 } 3664 3665 bool 3666 ScVbaRange::isSingleCellRange() const 3667 { 3668 uno::Reference< sheet::XCellRangeAddressable > xAddressable( mxRange, uno::UNO_QUERY ); 3669 if ( xAddressable.is() ) 3670 { 3671 table::CellRangeAddress aRangeAddr = xAddressable->getRangeAddress(); 3672 return ( aRangeAddr.EndColumn == aRangeAddr.StartColumn && aRangeAddr.EndRow == aRangeAddr.StartRow ); 3673 } 3674 return false; 3675 } 3676 3677 uno::Reference< excel::XCharacters > SAL_CALL 3678 ScVbaRange::characters( const uno::Any& Start, const uno::Any& Length ) 3679 { 3680 if ( !isSingleCellRange() ) 3681 throw uno::RuntimeException("Can't create Characters property for multicell range " ); 3682 uno::Reference< text::XSimpleText > xSimple(mxRange->getCellByPosition(0,0) , uno::UNO_QUERY_THROW ); 3683 ScDocument& rDoc = getDocumentFromRange(mxRange); 3684 3685 ScVbaPalette aPalette( rDoc.GetDocumentShell() ); 3686 return new ScVbaCharacters( this, mxContext, aPalette, xSimple, Start, Length ); 3687 } 3688 3689 void SAL_CALL 3690 ScVbaRange::Delete( const uno::Any& Shift ) 3691 { 3692 if ( m_Areas->getCount() > 1 ) 3693 { 3694 sal_Int32 nItems = m_Areas->getCount(); 3695 for ( sal_Int32 index=1; index <= nItems; ++index ) 3696 { 3697 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 3698 xRange->Delete( Shift ); 3699 } 3700 return; 3701 } 3702 sheet::CellDeleteMode mode = sheet::CellDeleteMode_NONE ; 3703 RangeHelper thisRange( mxRange ); 3704 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 3705 if ( Shift.hasValue() ) 3706 { 3707 sal_Int32 nShift = 0; 3708 Shift >>= nShift; 3709 switch ( nShift ) 3710 { 3711 case excel::XlDeleteShiftDirection::xlShiftUp: 3712 mode = sheet::CellDeleteMode_UP; 3713 break; 3714 case excel::XlDeleteShiftDirection::xlShiftToLeft: 3715 mode = sheet::CellDeleteMode_LEFT; 3716 break; 3717 default: 3718 throw uno::RuntimeException("Illegal parameter " ); 3719 } 3720 } 3721 else 3722 { 3723 ScDocument& rDoc = getScDocument(); 3724 bool bFullRow = ( thisAddress.StartColumn == 0 && thisAddress.EndColumn == rDoc.MaxCol() ); 3725 sal_Int32 nCols = thisAddress.EndColumn - thisAddress.StartColumn; 3726 sal_Int32 nRows = thisAddress.EndRow - thisAddress.StartRow; 3727 if ( mbIsRows || bFullRow || ( nCols >= nRows ) ) 3728 mode = sheet::CellDeleteMode_UP; 3729 else 3730 mode = sheet::CellDeleteMode_LEFT; 3731 } 3732 uno::Reference< sheet::XCellRangeMovement > xCellRangeMove( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 3733 xCellRangeMove->removeRange( thisAddress, mode ); 3734 3735 } 3736 3737 //XElementAccess 3738 sal_Bool SAL_CALL 3739 ScVbaRange::hasElements() 3740 { 3741 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY ); 3742 if ( xColumnRowRange.is() ) 3743 if ( xColumnRowRange->getRows()->getCount() || 3744 xColumnRowRange->getColumns()->getCount() ) 3745 return true; 3746 return false; 3747 } 3748 3749 // XEnumerationAccess 3750 uno::Reference< container::XEnumeration > SAL_CALL 3751 ScVbaRange::createEnumeration() 3752 { 3753 if ( mbIsColumns || mbIsRows ) 3754 { 3755 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY ); 3756 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 3757 sal_Int32 nElems = 0; 3758 if ( mbIsColumns ) 3759 nElems = xColumnRowRange->getColumns()->getCount(); 3760 else 3761 nElems = xColumnRowRange->getRows()->getCount(); 3762 return new ColumnsRowEnumeration( xRange, nElems ); 3763 3764 } 3765 return new CellsEnumeration( mxParent, mxContext, m_Areas ); 3766 } 3767 3768 OUString SAL_CALL 3769 ScVbaRange::getDefaultMethodName( ) 3770 { 3771 return "Item"; 3772 } 3773 3774 // returns calc internal col. width ( in points ) 3775 double 3776 ScVbaRange::getCalcColWidth(const table::CellRangeAddress& rAddress) 3777 { 3778 ScDocument& rDoc = getScDocument(); 3779 sal_uInt16 nWidth = rDoc.GetOriginalWidth( static_cast< SCCOL >( rAddress.StartColumn ), static_cast< SCTAB >( rAddress.Sheet ) ); 3780 double nPoints = lcl_TwipsToPoints( nWidth ); 3781 nPoints = lcl_Round2DecPlaces( nPoints ); 3782 return nPoints; 3783 } 3784 3785 double 3786 ScVbaRange::getCalcRowHeight(const table::CellRangeAddress& rAddress) 3787 { 3788 ScDocument& rDoc = getDocumentFromRange( mxRange ); 3789 sal_uInt16 nWidth = rDoc.GetOriginalHeight( rAddress.StartRow, rAddress.Sheet ); 3790 double nPoints = lcl_TwipsToPoints( nWidth ); 3791 nPoints = lcl_Round2DecPlaces( nPoints ); 3792 return nPoints; 3793 } 3794 3795 // return Char Width in points 3796 static double getDefaultCharWidth( ScDocShell* pDocShell ) 3797 { 3798 ScDocument& rDoc = pDocShell->GetDocument(); 3799 OutputDevice* pRefDevice = rDoc.GetRefDevice(); 3800 ScPatternAttr* pAttr = rDoc.GetDefPattern(); 3801 vcl::Font aDefFont; 3802 pAttr->GetFont( aDefFont, SC_AUTOCOL_BLACK, pRefDevice ); 3803 pRefDevice->SetFont( aDefFont ); 3804 tools::Long nCharWidth = pRefDevice->GetTextWidth( OUString( '0' ) ); // 1/100th mm 3805 return o3tl::convert<double>(nCharWidth, o3tl::Length::mm100, o3tl::Length::pt); 3806 } 3807 3808 uno::Any SAL_CALL 3809 ScVbaRange::getColumnWidth() 3810 { 3811 sal_Int32 nLen = m_Areas->getCount(); 3812 if ( nLen > 1 ) 3813 { 3814 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 3815 return xRange->getColumnWidth(); 3816 } 3817 3818 double nColWidth = 0; 3819 ScDocShell* pShell = getScDocShell(); 3820 if ( pShell ) 3821 { 3822 double defaultCharWidth = getDefaultCharWidth( pShell ); 3823 RangeHelper thisRange( mxRange ); 3824 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 3825 sal_Int32 nStartCol = thisAddress.StartColumn; 3826 sal_Int32 nEndCol = thisAddress.EndColumn; 3827 sal_uInt16 nColTwips = 0; 3828 for( sal_Int32 nCol = nStartCol ; nCol <= nEndCol; ++nCol ) 3829 { 3830 thisAddress.StartColumn = nCol; 3831 sal_uInt16 nCurTwips = pShell->GetDocument().GetOriginalWidth( static_cast< SCCOL >( thisAddress.StartColumn ), static_cast< SCTAB >( thisAddress.Sheet ) ); 3832 if ( nCol == nStartCol ) 3833 nColTwips = nCurTwips; 3834 if ( nColTwips != nCurTwips ) 3835 return aNULL(); 3836 } 3837 nColWidth = lcl_TwipsToPoints( nColTwips ); 3838 if ( nColWidth != 0.0 ) 3839 nColWidth = ( nColWidth / defaultCharWidth ) - fExtraWidth; 3840 } 3841 nColWidth = lcl_Round2DecPlaces( nColWidth ); 3842 return uno::Any( nColWidth ); 3843 } 3844 3845 void SAL_CALL 3846 ScVbaRange::setColumnWidth( const uno::Any& _columnwidth ) 3847 { 3848 sal_Int32 nLen = m_Areas->getCount(); 3849 if ( nLen > 1 ) 3850 { 3851 for ( sal_Int32 index = 1; index != nLen; ++index ) 3852 { 3853 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 3854 xRange->setColumnWidth( _columnwidth ); 3855 } 3856 return; 3857 } 3858 double nColWidth = 0; 3859 _columnwidth >>= nColWidth; 3860 nColWidth = lcl_Round2DecPlaces( nColWidth ); 3861 ScDocShell* pDocShell = getScDocShell(); 3862 if ( !pDocShell ) 3863 return; 3864 3865 if ( nColWidth != 0.0 ) 3866 nColWidth = ( nColWidth + fExtraWidth ) * getDefaultCharWidth( pDocShell ); 3867 RangeHelper thisRange( mxRange ); 3868 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 3869 sal_uInt16 nTwips = lcl_pointsToTwips( nColWidth ); 3870 3871 std::vector<sc::ColRowSpan> aColArr(1, sc::ColRowSpan(thisAddress.StartColumn, thisAddress.EndColumn)); 3872 // #163561# use mode SC_SIZE_DIRECT: hide for width 0, show for other values 3873 pDocShell->GetDocFunc().SetWidthOrHeight( 3874 true, aColArr, thisAddress.Sheet, SC_SIZE_DIRECT, nTwips, true, true); 3875 } 3876 3877 uno::Any SAL_CALL 3878 ScVbaRange::getWidth() 3879 { 3880 if ( m_Areas->getCount() > 1 ) 3881 { 3882 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 3883 return xRange->getWidth(); 3884 } 3885 uno::Reference< table::XColumnRowRange > xColRowRange( mxRange, uno::UNO_QUERY_THROW ); 3886 uno::Reference< container::XIndexAccess > xIndexAccess( xColRowRange->getColumns(), uno::UNO_QUERY_THROW ); 3887 sal_Int32 nElems = xIndexAccess->getCount(); 3888 double nWidth = 0; 3889 for ( sal_Int32 index=0; index<nElems; ++index ) 3890 { 3891 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xIndexAccess->getByIndex( index ), uno::UNO_QUERY_THROW ); 3892 double nTmpWidth = getCalcColWidth( xAddressable->getRangeAddress() ); 3893 nWidth += nTmpWidth; 3894 } 3895 return uno::Any( nWidth ); 3896 } 3897 3898 uno::Any SAL_CALL 3899 ScVbaRange::Areas( const uno::Any& item) 3900 { 3901 if ( !item.hasValue() ) 3902 return uno::Any( m_Areas ); 3903 return m_Areas->Item( item, uno::Any() ); 3904 } 3905 3906 uno::Reference< excel::XRange > 3907 ScVbaRange::getArea( sal_Int32 nIndex ) 3908 { 3909 if ( !m_Areas.is() ) 3910 throw uno::RuntimeException("No areas available" ); 3911 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( ++nIndex ), uno::Any() ), uno::UNO_QUERY_THROW ); 3912 return xRange; 3913 } 3914 3915 uno::Any 3916 ScVbaRange::Borders( const uno::Any& item ) 3917 { 3918 if ( !item.hasValue() ) 3919 return uno::Any( getBorders() ); 3920 return getBorders()->Item( item, uno::Any() ); 3921 } 3922 3923 uno::Any SAL_CALL 3924 ScVbaRange::BorderAround( const css::uno::Any& LineStyle, const css::uno::Any& Weight, 3925 const css::uno::Any& ColorIndex, const css::uno::Any& Color ) 3926 { 3927 sal_Int32 nCount = getBorders()->getCount(); 3928 3929 for( sal_Int32 i = 0; i < nCount; i++ ) 3930 { 3931 const sal_Int32 nLineType = supportedIndexTable[i]; 3932 switch( nLineType ) 3933 { 3934 case excel::XlBordersIndex::xlEdgeLeft: 3935 case excel::XlBordersIndex::xlEdgeTop: 3936 case excel::XlBordersIndex::xlEdgeBottom: 3937 case excel::XlBordersIndex::xlEdgeRight: 3938 { 3939 uno::Reference< excel::XBorder > xBorder( m_Borders->Item( uno::Any( nLineType ), uno::Any() ), uno::UNO_QUERY_THROW ); 3940 if( LineStyle.hasValue() ) 3941 { 3942 xBorder->setLineStyle( LineStyle ); 3943 } 3944 if( Weight.hasValue() ) 3945 { 3946 xBorder->setWeight( Weight ); 3947 } 3948 if( ColorIndex.hasValue() ) 3949 { 3950 xBorder->setColorIndex( ColorIndex ); 3951 } 3952 if( Color.hasValue() ) 3953 { 3954 xBorder->setColor( Color ); 3955 } 3956 break; 3957 } 3958 case excel::XlBordersIndex::xlInsideVertical: 3959 case excel::XlBordersIndex::xlInsideHorizontal: 3960 case excel::XlBordersIndex::xlDiagonalDown: 3961 case excel::XlBordersIndex::xlDiagonalUp: 3962 break; 3963 default: 3964 return uno::Any( false ); 3965 } 3966 } 3967 return uno::Any( true ); 3968 } 3969 3970 uno::Any SAL_CALL 3971 ScVbaRange::getRowHeight() 3972 { 3973 sal_Int32 nLen = m_Areas->getCount(); 3974 if ( nLen > 1 ) 3975 { 3976 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 3977 return xRange->getRowHeight(); 3978 } 3979 3980 // if any row's RowHeight in the 3981 // range is different from any other, then return NULL 3982 RangeHelper thisRange( mxRange ); 3983 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 3984 3985 sal_Int32 nStartRow = thisAddress.StartRow; 3986 sal_Int32 nEndRow = thisAddress.EndRow; 3987 sal_uInt16 nRowTwips = 0; 3988 // #TODO probably possible to use the SfxItemSet (and see if 3989 // SfxItemState::DONTCARE is set) to improve performance 3990 // #CHECKME looks like this is general behaviour not just row Range specific 3991 // if ( mbIsRows ) 3992 ScDocShell* pShell = getScDocShell(); 3993 if ( pShell ) 3994 { 3995 for ( sal_Int32 nRow = nStartRow ; nRow <= nEndRow; ++nRow ) 3996 { 3997 thisAddress.StartRow = nRow; 3998 sal_uInt16 nCurTwips = pShell->GetDocument().GetOriginalHeight( thisAddress.StartRow, thisAddress.Sheet ); 3999 if ( nRow == nStartRow ) 4000 nRowTwips = nCurTwips; 4001 if ( nRowTwips != nCurTwips ) 4002 return aNULL(); 4003 } 4004 } 4005 double nHeight = lcl_Round2DecPlaces( lcl_TwipsToPoints( nRowTwips ) ); 4006 return uno::Any( nHeight ); 4007 } 4008 4009 void SAL_CALL 4010 ScVbaRange::setRowHeight( const uno::Any& _rowheight) 4011 { 4012 sal_Int32 nLen = m_Areas->getCount(); 4013 if ( nLen > 1 ) 4014 { 4015 for ( sal_Int32 index = 1; index != nLen; ++index ) 4016 { 4017 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 4018 xRange->setRowHeight( _rowheight ); 4019 } 4020 return; 4021 } 4022 double nHeight = 0; // Incoming height is in points 4023 _rowheight >>= nHeight; 4024 nHeight = lcl_Round2DecPlaces( nHeight ); 4025 RangeHelper thisRange( mxRange ); 4026 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4027 sal_uInt16 nTwips = lcl_pointsToTwips( nHeight ); 4028 4029 ScDocShell* pDocShell = getDocShellFromRange( mxRange ); 4030 std::vector<sc::ColRowSpan> aRowArr(1, sc::ColRowSpan(thisAddress.StartRow, thisAddress.EndRow)); 4031 pDocShell->GetDocFunc().SetWidthOrHeight( 4032 false, aRowArr, thisAddress.Sheet, SC_SIZE_ORIGINAL, nTwips, true, true); 4033 } 4034 4035 uno::Any SAL_CALL 4036 ScVbaRange::getPageBreak() 4037 { 4038 sal_Int32 nPageBreak = excel::XlPageBreak::xlPageBreakNone; 4039 ScDocShell* pShell = getDocShellFromRange( mxRange ); 4040 if ( pShell ) 4041 { 4042 RangeHelper thisRange( mxRange ); 4043 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4044 bool bColumn = false; 4045 4046 if (thisAddress.StartRow==0) 4047 bColumn = true; 4048 4049 uno::Reference< frame::XModel > xModel = pShell->GetModel(); 4050 if ( xModel.is() ) 4051 { 4052 ScDocument& rDoc = getDocumentFromRange( mxRange ); 4053 4054 ScBreakType nBreak = ScBreakType::NONE; 4055 if ( !bColumn ) 4056 nBreak = rDoc.HasRowBreak(thisAddress.StartRow, thisAddress.Sheet); 4057 else 4058 nBreak = rDoc.HasColBreak(thisAddress.StartColumn, thisAddress.Sheet); 4059 4060 if (nBreak & ScBreakType::Page) 4061 nPageBreak = excel::XlPageBreak::xlPageBreakAutomatic; 4062 4063 if (nBreak & ScBreakType::Manual) 4064 nPageBreak = excel::XlPageBreak::xlPageBreakManual; 4065 } 4066 } 4067 4068 return uno::Any( nPageBreak ); 4069 } 4070 4071 void SAL_CALL 4072 ScVbaRange::setPageBreak( const uno::Any& _pagebreak) 4073 { 4074 sal_Int32 nPageBreak = 0; 4075 _pagebreak >>= nPageBreak; 4076 4077 ScDocShell* pShell = getDocShellFromRange( mxRange ); 4078 if ( !pShell ) 4079 return; 4080 4081 RangeHelper thisRange( mxRange ); 4082 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4083 if ((thisAddress.StartColumn==0) && (thisAddress.StartRow==0)) 4084 return; 4085 bool bColumn = false; 4086 4087 if (thisAddress.StartRow==0) 4088 bColumn = true; 4089 4090 ScAddress aAddr( static_cast<SCCOL>(thisAddress.StartColumn), thisAddress.StartRow, thisAddress.Sheet ); 4091 uno::Reference< frame::XModel > xModel = pShell->GetModel(); 4092 if ( xModel.is() ) 4093 { 4094 ScTabViewShell* pViewShell = excel::getBestViewShell( xModel ); 4095 if ( nPageBreak == excel::XlPageBreak::xlPageBreakManual ) 4096 pViewShell->InsertPageBreak( bColumn, true, &aAddr); 4097 else if ( nPageBreak == excel::XlPageBreak::xlPageBreakNone ) 4098 pViewShell->DeletePageBreak( bColumn, true, &aAddr); 4099 } 4100 } 4101 4102 uno::Any SAL_CALL 4103 ScVbaRange::getHeight() 4104 { 4105 if ( m_Areas->getCount() > 1 ) 4106 { 4107 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW ); 4108 return xRange->getHeight(); 4109 } 4110 4111 uno::Reference< table::XColumnRowRange > xColRowRange( mxRange, uno::UNO_QUERY_THROW ); 4112 uno::Reference< container::XIndexAccess > xIndexAccess( xColRowRange->getRows(), uno::UNO_QUERY_THROW ); 4113 sal_Int32 nElems = xIndexAccess->getCount(); 4114 double nHeight = 0; 4115 for ( sal_Int32 index=0; index<nElems; ++index ) 4116 { 4117 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xIndexAccess->getByIndex( index ), uno::UNO_QUERY_THROW ); 4118 nHeight += getCalcRowHeight(xAddressable->getRangeAddress() ); 4119 } 4120 return uno::Any( nHeight ); 4121 } 4122 4123 awt::Point 4124 ScVbaRange::getPosition() const 4125 { 4126 awt::Point aPoint; 4127 uno::Reference< beans::XPropertySet > xProps; 4128 if ( mxRange.is() ) 4129 xProps.set( mxRange, uno::UNO_QUERY_THROW ); 4130 else 4131 xProps.set( mxRanges, uno::UNO_QUERY_THROW ); 4132 xProps->getPropertyValue( "Position" ) >>= aPoint; 4133 return aPoint; 4134 } 4135 uno::Any SAL_CALL 4136 ScVbaRange::getLeft() 4137 { 4138 // helperapi returns the first ranges left ( and top below ) 4139 if ( m_Areas->getCount() > 1 ) 4140 return getArea( 0 )->getLeft(); 4141 awt::Point aPoint = getPosition(); 4142 return uno::Any(o3tl::convert<double>(aPoint.X, o3tl::Length::mm100, o3tl::Length::pt)); 4143 } 4144 4145 uno::Any SAL_CALL 4146 ScVbaRange::getTop() 4147 { 4148 // helperapi returns the first ranges top 4149 if ( m_Areas->getCount() > 1 ) 4150 return getArea( 0 )->getTop(); 4151 awt::Point aPoint= getPosition(); 4152 return uno::Any(o3tl::convert<double>(aPoint.Y, o3tl::Length::mm100, o3tl::Length::pt)); 4153 } 4154 4155 static uno::Reference< sheet::XCellRangeReferrer > getNamedRange( const uno::Reference< uno::XInterface >& xIf, const uno::Reference< table::XCellRange >& thisRange ) 4156 { 4157 uno::Reference< beans::XPropertySet > xProps( xIf, uno::UNO_QUERY_THROW ); 4158 uno::Reference< container::XNameAccess > xNameAccess( xProps->getPropertyValue( "NamedRanges" ), uno::UNO_QUERY_THROW ); 4159 4160 const uno::Sequence< OUString > sNames = xNameAccess->getElementNames(); 4161 // uno::Reference< table::XCellRange > thisRange( getCellRange(), uno::UNO_QUERY_THROW ); 4162 uno::Reference< sheet::XCellRangeReferrer > xNamedRange; 4163 for ( const auto& rName : sNames ) 4164 { 4165 uno::Reference< sheet::XCellRangeReferrer > xName( xNameAccess->getByName( rName ), uno::UNO_QUERY ); 4166 if ( xName.is() ) 4167 { 4168 if ( thisRange == xName->getReferredCells() ) 4169 { 4170 xNamedRange = xName; 4171 break; 4172 } 4173 } 4174 } 4175 return xNamedRange; 4176 } 4177 4178 uno::Reference< excel::XName > 4179 ScVbaRange::getName() 4180 { 4181 uno::Reference< beans::XPropertySet > xProps( getUnoModel(), uno::UNO_QUERY ); 4182 uno::Reference< table::XCellRange > thisRange( getCellRange(), uno::UNO_QUERY_THROW ); 4183 // Application range 4184 uno::Reference< sheet::XCellRangeReferrer > xNamedRange = getNamedRange( xProps, thisRange ); 4185 4186 if ( !xNamedRange.is() ) 4187 { 4188 // not in application range then assume it might be in 4189 // sheet namedranges 4190 RangeHelper aRange( thisRange ); 4191 uno::Reference< sheet::XSpreadsheet > xSheet = aRange.getSpreadSheet(); 4192 xProps.set( xSheet, uno::UNO_QUERY ); 4193 // impl here 4194 xNamedRange = getNamedRange( xProps, thisRange ); 4195 } 4196 if ( xProps.is() && xNamedRange.is() ) 4197 { 4198 uno::Reference< sheet::XNamedRanges > xNamedRanges( xProps, uno::UNO_QUERY_THROW ); 4199 uno::Reference< sheet::XNamedRange > xName( xNamedRange, uno::UNO_QUERY_THROW ); 4200 return new ScVbaName( mxParent, mxContext, xName, xNamedRanges, getUnoModel() ); 4201 } 4202 return uno::Reference< excel::XName >(); 4203 } 4204 4205 uno::Reference< excel::XWorksheet > 4206 ScVbaRange::getWorksheet() 4207 { 4208 // #TODO #FIXME parent should always be set up ( currently that's not 4209 // the case ) 4210 uno::Reference< excel::XWorksheet > xSheet( getParent(), uno::UNO_QUERY ); 4211 if ( !xSheet.is() ) 4212 { 4213 uno::Reference< table::XCellRange > xRange = mxRange; 4214 4215 if ( mxRanges.is() ) // assign xRange to first range 4216 { 4217 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW ); 4218 xRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW ); 4219 } 4220 ScDocShell* pDocShell = getDocShellFromRange(xRange); 4221 RangeHelper rHelper(xRange); 4222 // parent should be Thisworkbook 4223 xSheet.set( new ScVbaWorksheet( uno::Reference< XHelperInterface >(), mxContext,rHelper.getSpreadSheet(),pDocShell->GetModel()) ); 4224 } 4225 return xSheet; 4226 } 4227 4228 // #TODO remove this ugly application processing 4229 // Process an application Range request e.g. 'Range("a1,b2,a4:b6") 4230 uno::Reference< excel::XRange > 4231 ScVbaRange::ApplicationRange( const uno::Reference< uno::XComponentContext >& xContext, const css::uno::Any &Cell1, const css::uno::Any &Cell2 ) 4232 { 4233 // Although the documentation seems clear that Range without a 4234 // qualifier then it's a shortcut for ActiveSheet.Range 4235 // however, similarly Application.Range is apparently also a 4236 // shortcut for ActiveSheet.Range 4237 // The is however a subtle behavioural difference I've come across 4238 // wrt to named ranges. 4239 // If a named range "test" exists { Sheet1!$A1 } and the active sheet 4240 // is Sheet2 then the following will fail 4241 // msgbox ActiveSheet.Range("test").Address ' fails 4242 // msgbox WorkSheets("Sheet2").Range("test").Address 4243 // but!!! 4244 // msgbox Range("test").Address ' works 4245 // msgbox Application.Range("test").Address ' works 4246 4247 // Single param Range 4248 OUString sRangeName; 4249 Cell1 >>= sRangeName; 4250 if ( Cell1.hasValue() && !Cell2.hasValue() && !sRangeName.isEmpty() ) 4251 { 4252 uno::Reference< beans::XPropertySet > xPropSet( getCurrentExcelDoc(xContext), uno::UNO_QUERY_THROW ); 4253 4254 uno::Reference< container::XNameAccess > xNamed( xPropSet->getPropertyValue( "NamedRanges" ), uno::UNO_QUERY_THROW ); 4255 uno::Reference< sheet::XCellRangeReferrer > xReferrer; 4256 try 4257 { 4258 xReferrer.set ( xNamed->getByName( sRangeName ), uno::UNO_QUERY ); 4259 } 4260 catch( uno::Exception& /*e*/ ) 4261 { 4262 // do nothing 4263 } 4264 if ( xReferrer.is() ) 4265 { 4266 uno::Reference< table::XCellRange > xRange = xReferrer->getReferredCells(); 4267 if ( xRange.is() ) 4268 { 4269 uno::Reference< excel::XRange > xVbRange = new ScVbaRange( excel::getUnoSheetModuleObj( xRange ), xContext, xRange ); 4270 return xVbRange; 4271 } 4272 } 4273 } 4274 4275 uno::Reference<table::XCellRange> xSheetRange; 4276 4277 try 4278 { 4279 uno::Reference<sheet::XSpreadsheetView> xView( 4280 getCurrentExcelDoc(xContext)->getCurrentController(), uno::UNO_QUERY_THROW); 4281 4282 xSheetRange.set(xView->getActiveSheet(), uno::UNO_QUERY_THROW); 4283 } 4284 catch (const uno::Exception&) 4285 { 4286 return uno::Reference<excel::XRange>(); 4287 } 4288 4289 rtl::Reference<ScVbaRange> pRange = new ScVbaRange( excel::getUnoSheetModuleObj( xSheetRange ), xContext, xSheetRange ); 4290 return pRange->Range( Cell1, Cell2, true ); 4291 } 4292 4293 // Helper functions for AutoFilter 4294 static ScDBData* lcl_GetDBData_Impl( ScDocShell* pDocShell, sal_Int16 nSheet ) 4295 { 4296 ScDBData* pRet = nullptr; 4297 if (pDocShell) 4298 { 4299 pRet = pDocShell->GetDocument().GetAnonymousDBData(nSheet); 4300 } 4301 return pRet; 4302 } 4303 4304 static void lcl_SelectAll( ScDocShell* pDocShell, const ScQueryParam& aParam ) 4305 { 4306 if ( !pDocShell ) 4307 return; 4308 4309 ScViewData* pViewData = ScDocShell::GetViewData(); 4310 if ( !pViewData ) 4311 { 4312 ScTabViewShell* pViewSh = pDocShell->GetBestViewShell( true ); 4313 pViewData = pViewSh ? &pViewSh->GetViewData() : nullptr; 4314 } 4315 4316 if ( pViewData ) 4317 { 4318 pViewData->GetView()->Query( aParam, nullptr, true ); 4319 } 4320 } 4321 4322 static ScQueryParam lcl_GetQueryParam( ScDocShell* pDocShell, sal_Int16 nSheet ) 4323 { 4324 ScDBData* pDBData = lcl_GetDBData_Impl( pDocShell, nSheet ); 4325 ScQueryParam aParam; 4326 if (pDBData) 4327 { 4328 pDBData->GetQueryParam( aParam ); 4329 } 4330 return aParam; 4331 } 4332 4333 static void lcl_SetAllQueryForField( ScDocShell* pDocShell, SCCOLROW nField, sal_Int16 nSheet ) 4334 { 4335 ScQueryParam aParam = lcl_GetQueryParam( pDocShell, nSheet ); 4336 aParam.RemoveEntryByField(nField); 4337 lcl_SelectAll( pDocShell, aParam ); 4338 } 4339 4340 // Modifies sCriteria, and nOp depending on the value of sCriteria 4341 static void lcl_setTableFieldsFromCriteria( OUString& sCriteria1, const uno::Reference< beans::XPropertySet >& xDescProps, sheet::TableFilterField2& rFilterField ) 4342 { 4343 // #TODO make this more efficient and cycle through 4344 // sCriteria1 character by character to pick up <,<>,=, * etc. 4345 // right now I am more concerned with just getting it to work right 4346 4347 sCriteria1 = sCriteria1.trim(); 4348 // table of translation of criteria text to FilterOperators 4349 // <>searchtext - NOT_EQUAL 4350 // =searchtext - EQUAL 4351 // *searchtext - startwith 4352 // <>*searchtext - doesn't startwith 4353 // *searchtext* - contains 4354 // <>*searchtext* - doesn't contain 4355 // [>|>=|<=|...]searchtext for GREATER_value, GREATER_EQUAL_value etc. 4356 if ( sCriteria1.startsWith( EQUALS ) ) 4357 { 4358 if ( o3tl::make_unsigned(sCriteria1.getLength()) == strlen(EQUALS) ) 4359 rFilterField.Operator = sheet::FilterOperator2::EMPTY; 4360 else 4361 { 4362 rFilterField.Operator = sheet::FilterOperator2::EQUAL; 4363 sCriteria1 = sCriteria1.copy( strlen(EQUALS) ); 4364 sCriteria1 = VBAToRegexp( sCriteria1 ); 4365 // UseRegularExpressions 4366 if ( xDescProps.is() ) 4367 xDescProps->setPropertyValue( "UseRegularExpressions", uno::Any( true ) ); 4368 } 4369 4370 } 4371 else if ( sCriteria1.startsWith( NOTEQUALS ) ) 4372 { 4373 if ( o3tl::make_unsigned(sCriteria1.getLength()) == strlen(NOTEQUALS) ) 4374 rFilterField.Operator = sheet::FilterOperator2::NOT_EMPTY; 4375 else 4376 { 4377 rFilterField.Operator = sheet::FilterOperator2::NOT_EQUAL; 4378 sCriteria1 = sCriteria1.copy( strlen(NOTEQUALS) ); 4379 sCriteria1 = VBAToRegexp( sCriteria1 ); 4380 // UseRegularExpressions 4381 if ( xDescProps.is() ) 4382 xDescProps->setPropertyValue( "UseRegularExpressions", uno::Any( true ) ); 4383 } 4384 } 4385 else if ( sCriteria1.startsWith( GREATERTHAN ) ) 4386 { 4387 if ( sCriteria1.startsWith( GREATERTHANEQUALS ) ) 4388 { 4389 sCriteria1 = sCriteria1.copy( strlen(GREATERTHANEQUALS) ); 4390 rFilterField.Operator = sheet::FilterOperator2::GREATER_EQUAL; 4391 } 4392 else 4393 { 4394 sCriteria1 = sCriteria1.copy( strlen(GREATERTHAN) ); 4395 rFilterField.Operator = sheet::FilterOperator2::GREATER; 4396 } 4397 4398 } 4399 else if ( sCriteria1.startsWith( LESSTHAN ) ) 4400 { 4401 if ( sCriteria1.startsWith( LESSTHANEQUALS ) ) 4402 { 4403 sCriteria1 = sCriteria1.copy( strlen(LESSTHANEQUALS) ); 4404 rFilterField.Operator = sheet::FilterOperator2::LESS_EQUAL; 4405 } 4406 else 4407 { 4408 sCriteria1 = sCriteria1.copy( strlen(LESSTHAN) ); 4409 rFilterField.Operator = sheet::FilterOperator2::LESS; 4410 } 4411 4412 } 4413 else 4414 rFilterField.Operator = sheet::FilterOperator2::EQUAL; 4415 4416 // tdf#107885 - check if criteria is numeric using locale dependent settings without group separator 4417 // or, if the decimal separator is different from the English locale, without any locale. 4418 sal_Int32 nParseEnd = 0; 4419 rtl_math_ConversionStatus eStatus = rtl_math_ConversionStatus_Ok; 4420 double fValue = ScGlobal::getLocaleData().stringToDouble( sCriteria1, false, &eStatus, &nParseEnd ); 4421 if ( nParseEnd == sCriteria1.getLength() && eStatus == rtl_math_ConversionStatus_Ok ) 4422 { 4423 rFilterField.IsNumeric = true; 4424 rFilterField.NumericValue = fValue; 4425 } 4426 else if ( ScGlobal::getLocaleData().getNumDecimalSep().toChar() != '.' ) 4427 { 4428 eStatus = rtl_math_ConversionStatus_Ok; 4429 fValue = ::rtl::math::stringToDouble( sCriteria1, '.', 0, &eStatus, &nParseEnd ); 4430 if ( nParseEnd == sCriteria1.getLength() && eStatus == rtl_math_ConversionStatus_Ok ) 4431 { 4432 rFilterField.IsNumeric = true; 4433 rFilterField.NumericValue = fValue; 4434 } 4435 } 4436 4437 rFilterField.StringValue = sCriteria1; 4438 } 4439 4440 void SAL_CALL 4441 ScVbaRange::AutoFilter( const uno::Any& aField, const uno::Any& Criteria1, const uno::Any& Operator, const uno::Any& Criteria2, const uno::Any& /*VisibleDropDown*/ ) 4442 { 4443 // Is there an existing autofilter 4444 RangeHelper thisRange( mxRange ); 4445 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4446 sal_Int16 nSheet = thisAddress.Sheet; 4447 ScDocShell* pShell = getScDocShell(); 4448 bool bHasAuto = false; 4449 uno::Reference< sheet::XDatabaseRange > xDataBaseRange = excel::GetAutoFiltRange( pShell, nSheet ); 4450 if ( xDataBaseRange.is() ) 4451 bHasAuto = true; 4452 4453 if ( !bHasAuto ) 4454 { 4455 if ( m_Areas->getCount() > 1 ) 4456 throw uno::RuntimeException( STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY ); 4457 4458 table::CellRangeAddress autoFiltAddress; 4459 //CurrentRegion() 4460 if ( isSingleCellRange() ) 4461 { 4462 uno::Reference< excel::XRange > xCurrent( CurrentRegion() ); 4463 if ( xCurrent.is() ) 4464 { 4465 ScVbaRange* pRange = getImplementation( xCurrent ); 4466 if ( pRange ) 4467 { 4468 if ( pRange->isSingleCellRange() ) 4469 throw uno::RuntimeException("Can't create AutoFilter" ); 4470 RangeHelper currentRegion( pRange->mxRange ); 4471 autoFiltAddress = currentRegion.getCellRangeAddressable()->getRangeAddress(); 4472 } 4473 } 4474 } 4475 else // multi-cell range 4476 { 4477 RangeHelper multiCellRange( mxRange ); 4478 autoFiltAddress = multiCellRange.getCellRangeAddressable()->getRangeAddress(); 4479 // #163530# Filter box shows only entry of first row 4480 ScDocument* pDocument = ( pShell ? &pShell->GetDocument() : nullptr ); 4481 if ( pDocument ) 4482 { 4483 SCCOL nStartCol = autoFiltAddress.StartColumn; 4484 SCROW nStartRow = autoFiltAddress.StartRow; 4485 SCCOL nEndCol = autoFiltAddress.EndColumn; 4486 SCROW nEndRow = autoFiltAddress.EndRow; 4487 pDocument->GetDataArea( autoFiltAddress.Sheet, nStartCol, nStartRow, nEndCol, nEndRow, true, true ); 4488 autoFiltAddress.StartColumn = nStartCol; 4489 autoFiltAddress.StartRow = nStartRow; 4490 autoFiltAddress.EndColumn = nEndCol; 4491 autoFiltAddress.EndRow = nEndRow; 4492 } 4493 } 4494 4495 uno::Reference< sheet::XUnnamedDatabaseRanges > xDBRanges = excel::GetUnnamedDataBaseRanges( pShell ); 4496 if ( xDBRanges.is() ) 4497 { 4498 if ( !xDBRanges->hasByTable( nSheet ) ) 4499 xDBRanges->setByTable( autoFiltAddress ); 4500 xDataBaseRange.set( xDBRanges->getByTable(nSheet ), uno::UNO_QUERY_THROW ); 4501 } 4502 if ( !xDataBaseRange.is() ) 4503 throw uno::RuntimeException("Failed to find the autofilter placeholder range" ); 4504 4505 uno::Reference< beans::XPropertySet > xDBRangeProps( xDataBaseRange, uno::UNO_QUERY_THROW ); 4506 // set autofilter 4507 xDBRangeProps->setPropertyValue( "AutoFilter", uno::Any(true) ); 4508 // set header (autofilter always need column headers) 4509 uno::Reference< beans::XPropertySet > xFiltProps( xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY_THROW ); 4510 xFiltProps->setPropertyValue( "ContainsHeader", uno::Any( true ) ); 4511 } 4512 4513 sal_Int32 nField = 0; // *IS* 1 based 4514 sal_Int32 nOperator = excel::XlAutoFilterOperator::xlAnd; 4515 4516 sheet::FilterConnection nConn = sheet::FilterConnection_AND; 4517 double nCriteria1 = 0; 4518 4519 bool bHasCritValue = Criteria1.hasValue(); 4520 bool bCritHasNumericValue = false; // not sure if a numeric criteria is possible 4521 if ( bHasCritValue ) 4522 bCritHasNumericValue = ( Criteria1 >>= nCriteria1 ); 4523 4524 if ( !aField.hasValue() && ( Criteria1.hasValue() || Operator.hasValue() || Criteria2.hasValue() ) ) 4525 throw uno::RuntimeException(); 4526 uno::Any Field( aField ); 4527 if ( !( Field >>= nField ) ) 4528 { 4529 const uno::Reference< script::XTypeConverter >& xConverter = getTypeConverter( mxContext ); 4530 try 4531 { 4532 Field = xConverter->convertTo( aField, cppu::UnoType<sal_Int32>::get() ); 4533 } 4534 catch( uno::Exception& ) 4535 { 4536 } 4537 } 4538 // Use the normal uno api, sometimes e.g. when you want to use ALL as the filter 4539 // we can't use refresh as the uno interface doesn't have a concept of ALL 4540 // in this case we just call the core calc functionality - 4541 if ( Field >>= nField ) 4542 { 4543 uno::Reference< sheet::XSheetFilterDescriptor2 > xDesc( 4544 xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY ); 4545 if ( xDesc.is() ) 4546 { 4547 OUString sCriteria1; 4548 bool bAcceptCriteria2 = true; 4549 bool bAll = false; 4550 uno::Sequence< sheet::TableFilterField2 > sTabFilts; 4551 sheet::TableFilterField2* pTabFilts = nullptr; 4552 uno::Reference< beans::XPropertySet > xDescProps( xDesc, uno::UNO_QUERY_THROW ); 4553 if ( Criteria1.hasValue() ) 4554 { 4555 sTabFilts.realloc( 1 ); 4556 pTabFilts = sTabFilts.getArray(); 4557 pTabFilts[0].Operator = sheet::FilterOperator2::EQUAL;// sensible default 4558 if ( !bCritHasNumericValue ) 4559 { 4560 Criteria1 >>= sCriteria1; 4561 if ( sCriteria1.isEmpty() ) 4562 { 4563 uno::Sequence< OUString > aCriteria1; 4564 Criteria1 >>= aCriteria1; 4565 sal_uInt16 nLength = aCriteria1.getLength(); 4566 if ( nLength ) 4567 { 4568 // When sequence is provided for Criteria1 don't care about Criteria2 4569 bAcceptCriteria2 = false; 4570 4571 auto pCriteria1 = aCriteria1.getArray(); 4572 sTabFilts.realloc( nLength ); 4573 pTabFilts = sTabFilts.getArray(); 4574 for ( sal_uInt16 i = 0; i < nLength; ++i ) 4575 { 4576 lcl_setTableFieldsFromCriteria( pCriteria1[i], xDescProps, pTabFilts[i] ); 4577 pTabFilts[i].Connection = sheet::FilterConnection_OR; 4578 pTabFilts[i].Field = (nField - 1); 4579 } 4580 } 4581 else 4582 bAll = true; 4583 } 4584 else 4585 { 4586 pTabFilts[0].IsNumeric = bCritHasNumericValue; 4587 if ( bHasCritValue && !sCriteria1.isEmpty() ) 4588 lcl_setTableFieldsFromCriteria( sCriteria1, xDescProps, pTabFilts[0] ); 4589 else 4590 bAll = true; 4591 } 4592 } 4593 else // numeric 4594 { 4595 pTabFilts[0].IsNumeric = true; 4596 pTabFilts[0].NumericValue = nCriteria1; 4597 } 4598 } 4599 else // no value specified 4600 bAll = true; 4601 // not sure what the relationship between Criteria1 and Operator is, 4602 // e.g. can you have an Operator without a Criteria? In LibreOffice it 4603 if ( Operator.hasValue() && ( Operator >>= nOperator ) ) 4604 { 4605 // if it's a bottom/top Ten(Percent/Value) and there 4606 // is no value specified for criteria1 set it to 10 4607 if ( !bCritHasNumericValue && sCriteria1.isEmpty() && ( nOperator != excel::XlAutoFilterOperator::xlOr ) && ( nOperator != excel::XlAutoFilterOperator::xlAnd ) ) 4608 { 4609 pTabFilts[0].IsNumeric = true; 4610 pTabFilts[0].NumericValue = 10; 4611 bAll = false; 4612 } 4613 switch ( nOperator ) 4614 { 4615 case excel::XlAutoFilterOperator::xlBottom10Items: 4616 pTabFilts[0].Operator = sheet::FilterOperator2::BOTTOM_VALUES; 4617 break; 4618 case excel::XlAutoFilterOperator::xlBottom10Percent: 4619 pTabFilts[0].Operator = sheet::FilterOperator2::BOTTOM_PERCENT; 4620 break; 4621 case excel::XlAutoFilterOperator::xlTop10Items: 4622 pTabFilts[0].Operator = sheet::FilterOperator2::TOP_VALUES; 4623 break; 4624 case excel::XlAutoFilterOperator::xlTop10Percent: 4625 pTabFilts[0].Operator = sheet::FilterOperator2::TOP_PERCENT; 4626 break; 4627 case excel::XlAutoFilterOperator::xlOr: 4628 nConn = sheet::FilterConnection_OR; 4629 break; 4630 case excel::XlAutoFilterOperator::xlAnd: 4631 nConn = sheet::FilterConnection_AND; 4632 break; 4633 default: 4634 throw uno::RuntimeException("UnknownOption" ); 4635 4636 } 4637 4638 } 4639 if ( !bAll && bAcceptCriteria2 ) 4640 { 4641 pTabFilts[0].Connection = sheet::FilterConnection_AND; 4642 pTabFilts[0].Field = (nField - 1); 4643 4644 uno::Sequence< OUString > aCriteria2; 4645 if ( Criteria2.hasValue() ) // there is a Criteria2 4646 { 4647 sTabFilts.realloc(2); 4648 pTabFilts = sTabFilts.getArray(); 4649 pTabFilts[1].Field = sTabFilts[0].Field; 4650 pTabFilts[1].Connection = nConn; 4651 4652 OUString sCriteria2; 4653 if ( Criteria2 >>= sCriteria2 ) 4654 { 4655 if ( !sCriteria2.isEmpty() ) 4656 { 4657 uno::Reference< beans::XPropertySet > xProps; 4658 lcl_setTableFieldsFromCriteria( sCriteria2, xProps, pTabFilts[1] ); 4659 pTabFilts[1].IsNumeric = false; 4660 } 4661 } 4662 else if ( Criteria2 >>= aCriteria2 ) 4663 { 4664 sal_uInt16 nLength = aCriteria2.getLength(); 4665 if ( nLength ) 4666 { 4667 // For compatibility use only the last value from the sequence 4668 lcl_setTableFieldsFromCriteria( aCriteria2.getArray()[nLength - 1], xDescProps, pTabFilts[1] ); 4669 } 4670 } 4671 else // numeric 4672 { 4673 Criteria2 >>= pTabFilts[1].NumericValue; 4674 pTabFilts[1].IsNumeric = true; 4675 pTabFilts[1].Operator = sheet::FilterOperator2::EQUAL; 4676 } 4677 } 4678 } 4679 4680 xDesc->setFilterFields2( sTabFilts ); 4681 if ( !bAll ) 4682 { 4683 xDataBaseRange->refresh(); 4684 } 4685 else 4686 // was 0 based now seems to be 1 4687 lcl_SetAllQueryForField( pShell, nField, nSheet ); 4688 } 4689 } 4690 else 4691 { 4692 // this is just to toggle autofilter on and off ( not to be confused with 4693 // a VisibleDropDown option combined with a field, in that case just the 4694 // button should be disabled ) - currently we don't support that 4695 uno::Reference< beans::XPropertySet > xDBRangeProps( xDataBaseRange, uno::UNO_QUERY_THROW ); 4696 if ( bHasAuto ) 4697 { 4698 // find the any field with the query and select all 4699 ScQueryParam aParam = lcl_GetQueryParam( pShell, nSheet ); 4700 for (SCSIZE i = 0; i< aParam.GetEntryCount(); ++i) 4701 { 4702 ScQueryEntry& rEntry = aParam.GetEntry(i); 4703 if ( rEntry.bDoQuery ) 4704 lcl_SetAllQueryForField( pShell, rEntry.nField, nSheet ); 4705 } 4706 // remove existing filters 4707 uno::Reference< sheet::XSheetFilterDescriptor2 > xSheetFilterDescriptor( 4708 xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY ); 4709 if( xSheetFilterDescriptor.is() ) 4710 xSheetFilterDescriptor->setFilterFields2( uno::Sequence< sheet::TableFilterField2 >() ); 4711 } 4712 xDBRangeProps->setPropertyValue( "AutoFilter", uno::Any(!bHasAuto) ); 4713 4714 } 4715 } 4716 4717 void SAL_CALL 4718 ScVbaRange::Insert( const uno::Any& Shift, const uno::Any& /*CopyOrigin*/ ) 4719 { 4720 // It appears (from the web) that the undocumented CopyOrigin 4721 // param should contain member of enum XlInsertFormatOrigin 4722 // which can have values xlFormatFromLeftOrAbove or xlFormatFromRightOrBelow 4723 // #TODO investigate resultant behaviour using these constants 4724 // currently just processing Shift 4725 4726 sheet::CellInsertMode mode = sheet::CellInsertMode_NONE; 4727 if ( Shift.hasValue() ) 4728 { 4729 sal_Int32 nShift = 0; 4730 Shift >>= nShift; 4731 switch ( nShift ) 4732 { 4733 case excel::XlInsertShiftDirection::xlShiftToRight: 4734 mode = sheet::CellInsertMode_RIGHT; 4735 break; 4736 case excel::XlInsertShiftDirection::xlShiftDown: 4737 mode = sheet::CellInsertMode_DOWN; 4738 break; 4739 default: 4740 throw uno::RuntimeException("Illegal parameter " ); 4741 } 4742 } 4743 else 4744 { 4745 if ( getRow() >= getColumn() ) 4746 mode = sheet::CellInsertMode_DOWN; 4747 else 4748 mode = sheet::CellInsertMode_RIGHT; 4749 } 4750 RangeHelper thisRange( mxRange ); 4751 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4752 uno::Reference< sheet::XCellRangeMovement > xCellRangeMove( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 4753 xCellRangeMove->insertCells( thisAddress, mode ); 4754 4755 // Paste from clipboard only if the clipboard content was copied via VBA, and not already pasted via VBA again. 4756 // "Insert" behavior should not depend on random clipboard content previously copied by the user. 4757 ScDocShell* pDocShell = getDocShellFromRange( mxRange ); 4758 const ScTransferObj* pClipObj = pDocShell ? ScTransferObj::GetOwnClipboard(pDocShell->GetClipData()) : nullptr; 4759 if ( pClipObj && pClipObj->GetUseInApi() ) 4760 { 4761 // After the insert ( this range ) actually has moved 4762 ScRange aRange( static_cast< SCCOL >( thisAddress.StartColumn ), static_cast< SCROW >( thisAddress.StartRow ), static_cast< SCTAB >( thisAddress.Sheet ), static_cast< SCCOL >( thisAddress.EndColumn ), static_cast< SCROW >( thisAddress.EndRow ), static_cast< SCTAB >( thisAddress.Sheet ) ); 4763 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getDocShellFromRange( mxRange ) , aRange ) ); 4764 uno::Reference< excel::XRange > xVbaRange( new ScVbaRange( mxParent, mxContext, xRange, mbIsRows, mbIsColumns ) ); 4765 xVbaRange->PasteSpecial( uno::Any(), uno::Any(), uno::Any(), uno::Any() ); 4766 } 4767 } 4768 4769 void SAL_CALL 4770 ScVbaRange::Autofit() 4771 { 4772 sal_Int32 nLen = m_Areas->getCount(); 4773 if ( nLen > 1 ) 4774 { 4775 for ( sal_Int32 index = 1; index != nLen; ++index ) 4776 { 4777 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( index ), uno::Any() ), uno::UNO_QUERY_THROW ); 4778 xRange->Autofit(); 4779 } 4780 return; 4781 } 4782 4783 // if the range is a not a row or column range autofit will 4784 // throw an error 4785 if ( !( mbIsColumns || mbIsRows ) ) 4786 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, {}); 4787 ScDocShell* pDocShell = getDocShellFromRange( mxRange ); 4788 if ( !pDocShell ) 4789 return; 4790 4791 RangeHelper thisRange( mxRange ); 4792 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 4793 4794 std::vector<sc::ColRowSpan> aColArr(1, sc::ColRowSpan(thisAddress.StartColumn,thisAddress.EndColumn)); 4795 bool bDirection = true; 4796 if ( mbIsRows ) 4797 { 4798 bDirection = false; 4799 aColArr[0].mnStart = thisAddress.StartRow; 4800 aColArr[0].mnEnd = thisAddress.EndRow; 4801 } 4802 pDocShell->GetDocFunc().SetWidthOrHeight( 4803 bDirection, aColArr, thisAddress.Sheet, SC_SIZE_OPTIMAL, 0, true, true); 4804 } 4805 4806 uno::Any SAL_CALL 4807 ScVbaRange::Hyperlinks( const uno::Any& aIndex ) 4808 { 4809 /* The range object always returns a new Hyperlinks object containing a 4810 fixed list of existing hyperlinks in the range. 4811 See vbahyperlinks.hxx for more details. */ 4812 4813 // get the global hyperlink object of the sheet (sheet should always be the parent of a Range object) 4814 uno::Reference< excel::XWorksheet > xWorksheet( getParent(), uno::UNO_QUERY_THROW ); 4815 uno::Reference< excel::XHyperlinks > xSheetHlinks( xWorksheet->Hyperlinks( uno::Any() ), uno::UNO_QUERY_THROW ); 4816 ScVbaHyperlinksRef xScSheetHlinks( dynamic_cast< ScVbaHyperlinks* >( xSheetHlinks.get() ) ); 4817 if( !xScSheetHlinks.is() ) 4818 throw uno::RuntimeException("Cannot obtain hyperlinks implementation object" ); 4819 4820 // create a new local hyperlinks object based on the sheet hyperlinks 4821 ScVbaHyperlinksRef xHlinks( new ScVbaHyperlinks( getParent(), mxContext, xScSheetHlinks, getScRangeList() ) ); 4822 if( aIndex.hasValue() ) 4823 return xHlinks->Item( aIndex, uno::Any() ); 4824 return uno::Any( uno::Reference< excel::XHyperlinks >( xHlinks ) ); 4825 } 4826 4827 css::uno::Reference< excel::XValidation > SAL_CALL 4828 ScVbaRange::getValidation() 4829 { 4830 if ( !m_xValidation.is() ) 4831 m_xValidation = new ScVbaValidation( this, mxContext, mxRange ); 4832 return m_xValidation; 4833 } 4834 4835 namespace { 4836 4837 /// @throws uno::RuntimeException 4838 sal_Unicode lclGetPrefixChar( const uno::Reference< table::XCell >& rxCell ) 4839 { 4840 /* TODO/FIXME: We need an apostroph-prefix property at the cell to 4841 implement this correctly. For now, return an apostroph for every text 4842 cell. 4843 4844 TODO/FIXME: When Application.TransitionNavigKeys is supported and true, 4845 this function needs to inspect the cell formatting and return different 4846 prefixes according to the horizontal cell alignment. 4847 */ 4848 return (rxCell->getType() == table::CellContentType_TEXT) ? '\'' : 0; 4849 } 4850 4851 /// @throws uno::RuntimeException 4852 sal_Unicode lclGetPrefixChar( const uno::Reference< table::XCellRange >& rxRange ) 4853 { 4854 /* This implementation is able to handle different prefixes (needed if 4855 Application.TransitionNavigKeys is true). The function lclGetPrefixChar 4856 for single cells called from here may return any prefix. If that 4857 function returns an empty prefix (NUL character) or different non-empty 4858 prefixes for two cells, this function returns 0. 4859 */ 4860 sal_Unicode cCurrPrefix = 0; 4861 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxRange ); 4862 sal_Int32 nEndCol = aRangeAddr.EndColumn - aRangeAddr.StartColumn; 4863 sal_Int32 nEndRow = aRangeAddr.EndRow - aRangeAddr.StartRow; 4864 for( sal_Int32 nRow = 0; nRow <= nEndRow; ++nRow ) 4865 { 4866 for( sal_Int32 nCol = 0; nCol <= nEndCol; ++nCol ) 4867 { 4868 uno::Reference< table::XCell > xCell( rxRange->getCellByPosition( nCol, nRow ), uno::UNO_SET_THROW ); 4869 sal_Unicode cNewPrefix = lclGetPrefixChar( xCell ); 4870 if( (cNewPrefix == 0) || ((cCurrPrefix != 0) && (cNewPrefix != cCurrPrefix)) ) 4871 return 0; 4872 cCurrPrefix = cNewPrefix; 4873 } 4874 } 4875 // all cells contain the same prefix - return it 4876 return cCurrPrefix; 4877 } 4878 4879 /// @throws uno::RuntimeException 4880 sal_Unicode lclGetPrefixChar( const uno::Reference< sheet::XSheetCellRangeContainer >& rxRanges ) 4881 { 4882 sal_Unicode cCurrPrefix = 0; 4883 uno::Reference< container::XEnumerationAccess > xRangesEA( rxRanges, uno::UNO_QUERY_THROW ); 4884 uno::Reference< container::XEnumeration > xRangesEnum( xRangesEA->createEnumeration(), uno::UNO_SET_THROW ); 4885 while( xRangesEnum->hasMoreElements() ) 4886 { 4887 uno::Reference< table::XCellRange > xRange( xRangesEnum->nextElement(), uno::UNO_QUERY_THROW ); 4888 sal_Unicode cNewPrefix = lclGetPrefixChar( xRange ); 4889 if( (cNewPrefix == 0) || ((cCurrPrefix != 0) && (cNewPrefix != cCurrPrefix)) ) 4890 return 0; 4891 cCurrPrefix = cNewPrefix; 4892 } 4893 // all ranges contain the same prefix - return it 4894 return cCurrPrefix; 4895 } 4896 4897 uno::Any lclGetPrefixVariant( sal_Unicode cPrefixChar ) 4898 { 4899 return uno::Any( (cPrefixChar == 0) ? OUString() : OUString( cPrefixChar ) ); 4900 } 4901 4902 } // namespace 4903 4904 uno::Any SAL_CALL ScVbaRange::getPrefixCharacter() 4905 { 4906 /* (1) If Application.TransitionNavigKeys is false, this function returns 4907 an apostroph character if the text cell begins with an apostroph 4908 character (formula return values are not taken into account); otherwise 4909 an empty string. 4910 4911 (2) If Application.TransitionNavigKeys is true, this function returns 4912 an apostroph character, if the cell is left-aligned; a double-quote 4913 character, if the cell is right-aligned; a circumflex character, if the 4914 cell is centered; a backslash character, if the cell is set to filled; 4915 or an empty string, if nothing of the above. 4916 4917 If a range or a list of ranges contains texts with leading apostroph 4918 character as well as other cells, this function returns an empty 4919 string. 4920 */ 4921 4922 if( mxRange.is() ) 4923 return lclGetPrefixVariant( lclGetPrefixChar( mxRange ) ); 4924 if( mxRanges.is() ) 4925 return lclGetPrefixVariant( lclGetPrefixChar( mxRanges ) ); 4926 throw uno::RuntimeException("Unexpected empty Range object" ); 4927 } 4928 4929 uno::Any ScVbaRange::getShowDetail() 4930 { 4931 // #FIXME, If the specified range is in a PivotTable report 4932 4933 // In MSO VBA, the specified range must be a single summary column or row in an outline. otherwise throw exception 4934 if( m_Areas->getCount() > 1 ) 4935 throw uno::RuntimeException("Can not get Range.ShowDetail attribute " ); 4936 4937 RangeHelper helper( mxRange ); 4938 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = helper.getSheetCellCursor(); 4939 xSheetCellCursor->collapseToCurrentRegion(); 4940 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW); 4941 table::CellRangeAddress aOutlineAddress = xCellRangeAddressable->getRangeAddress(); 4942 4943 // check if the specified range is a single summary column or row. 4944 table::CellRangeAddress thisAddress = helper.getCellRangeAddressable()->getRangeAddress(); 4945 if( (thisAddress.StartRow != thisAddress.EndRow || thisAddress.EndRow != aOutlineAddress.EndRow ) && 4946 (thisAddress.StartColumn != thisAddress.EndColumn || thisAddress.EndColumn != aOutlineAddress.EndColumn )) 4947 { 4948 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" ); 4949 } 4950 4951 bool bColumn = thisAddress.StartRow != thisAddress.EndRow; 4952 ScDocument& rDoc = getDocumentFromRange( mxRange ); 4953 ScOutlineTable* pOutlineTable = rDoc.GetOutlineTable(static_cast<SCTAB>(thisAddress.Sheet), true); 4954 const ScOutlineArray& rOutlineArray = bColumn ? pOutlineTable->GetColArray(): pOutlineTable->GetRowArray(); 4955 SCCOLROW nPos = bColumn ? static_cast<SCCOLROW>(thisAddress.EndColumn-1):static_cast<SCCOLROW>(thisAddress.EndRow-1); 4956 const ScOutlineEntry* pEntry = rOutlineArray.GetEntryByPos( 0, nPos ); 4957 if( pEntry ) 4958 { 4959 const bool bShowDetail = !pEntry->IsHidden(); 4960 return uno::Any( bShowDetail ); 4961 } 4962 4963 return aNULL(); 4964 } 4965 4966 void ScVbaRange::setShowDetail(const uno::Any& aShowDetail) 4967 { 4968 // #FIXME, If the specified range is in a PivotTable report 4969 4970 // In MSO VBA, the specified range must be a single summary column or row in an outline. otherwise throw exception 4971 if( m_Areas->getCount() > 1 ) 4972 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" ); 4973 4974 bool bShowDetail = extractBoolFromAny( aShowDetail ); 4975 4976 RangeHelper helper( mxRange ); 4977 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = helper.getSheetCellCursor(); 4978 xSheetCellCursor->collapseToCurrentRegion(); 4979 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW); 4980 table::CellRangeAddress aOutlineAddress = xCellRangeAddressable->getRangeAddress(); 4981 4982 // check if the specified range is a single summary column or row. 4983 table::CellRangeAddress thisAddress = helper.getCellRangeAddressable()->getRangeAddress(); 4984 if( (thisAddress.StartRow != thisAddress.EndRow || thisAddress.EndRow != aOutlineAddress.EndRow ) && 4985 (thisAddress.StartColumn != thisAddress.EndColumn || thisAddress.EndColumn != aOutlineAddress.EndColumn )) 4986 { 4987 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" ); 4988 } 4989 4990 // #FIXME, seems there is a different behavior between MSO and OOo. 4991 // In OOo, the showDetail will show all the level entries, while only show the first level entry in MSO 4992 uno::Reference< sheet::XSheetOutline > xSheetOutline( helper.getSpreadSheet(), uno::UNO_QUERY_THROW ); 4993 if( bShowDetail ) 4994 xSheetOutline->showDetail( aOutlineAddress ); 4995 else 4996 xSheetOutline->hideDetail( aOutlineAddress ); 4997 4998 } 4999 5000 uno::Reference< excel::XRange > SAL_CALL 5001 ScVbaRange::MergeArea() 5002 { 5003 uno::Reference< sheet::XSheetCellRange > xMergeShellCellRange(mxRange->getCellRangeByPosition(0,0,0,0), uno::UNO_QUERY_THROW); 5004 uno::Reference< sheet::XSheetCellCursor > xMergeSheetCursor(xMergeShellCellRange->getSpreadsheet()->createCursorByRange( xMergeShellCellRange ), uno::UNO_SET_THROW); 5005 if( xMergeSheetCursor.is() ) 5006 { 5007 xMergeSheetCursor->collapseToMergedArea(); 5008 uno::Reference<sheet::XCellRangeAddressable> xMergeCellAddress(xMergeSheetCursor, uno::UNO_QUERY_THROW); 5009 table::CellRangeAddress aCellAddress = xMergeCellAddress->getRangeAddress(); 5010 if( aCellAddress.StartColumn ==0 && aCellAddress.EndColumn==0 && 5011 aCellAddress.StartRow==0 && aCellAddress.EndRow==0) 5012 { 5013 return new ScVbaRange( mxParent,mxContext,mxRange ); 5014 } 5015 else 5016 { 5017 ScRange refRange( static_cast< SCCOL >( aCellAddress.StartColumn ), static_cast< SCROW >( aCellAddress.StartRow ), static_cast< SCTAB >( aCellAddress.Sheet ), 5018 static_cast< SCCOL >( aCellAddress.EndColumn ), static_cast< SCROW >( aCellAddress.EndRow ), static_cast< SCTAB >( aCellAddress.Sheet ) ); 5019 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell() , refRange ) ); 5020 return new ScVbaRange( mxParent, mxContext,xRange ); 5021 } 5022 } 5023 return new ScVbaRange( mxParent, mxContext, mxRange ); 5024 } 5025 5026 void SAL_CALL 5027 ScVbaRange::PrintOut( const uno::Any& From, const uno::Any& To, const uno::Any& Copies, const uno::Any& Preview, const uno::Any& ActivePrinter, const uno::Any& PrintToFile, const uno::Any& Collate, const uno::Any& PrToFileName ) 5028 { 5029 ScDocShell* pShell = nullptr; 5030 5031 sal_Int32 nItems = m_Areas->getCount(); 5032 uno::Sequence< table::CellRangeAddress > printAreas( nItems ); 5033 auto printAreasRange = asNonConstRange(printAreas); 5034 uno::Reference< sheet::XPrintAreas > xPrintAreas; 5035 for ( sal_Int32 index=1; index <= nItems; ++index ) 5036 { 5037 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 5038 5039 RangeHelper thisRange( xRange->getCellRange() ); 5040 table::CellRangeAddress rangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 5041 if ( index == 1 ) 5042 { 5043 ScVbaRange* pRange = getImplementation( xRange ); 5044 // initialise the doc shell and the printareas 5045 pShell = getDocShellFromRange( pRange->mxRange ); 5046 xPrintAreas.set( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 5047 } 5048 printAreasRange[ index - 1 ] = rangeAddress; 5049 } 5050 if ( pShell && xPrintAreas.is() ) 5051 { 5052 xPrintAreas->setPrintAreas( printAreas ); 5053 uno::Reference< frame::XModel > xModel = pShell->GetModel(); 5054 PrintOutHelper( excel::getBestViewShell( xModel ), From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, true ); 5055 } 5056 } 5057 5058 void SAL_CALL 5059 ScVbaRange::AutoFill( const uno::Reference< excel::XRange >& Destination, const uno::Any& Type ) 5060 { 5061 uno::Reference< excel::XRange > xDest( Destination, uno::UNO_SET_THROW ); 5062 ScVbaRange* pRange = getImplementation( xDest ); 5063 RangeHelper destRangeHelper( pRange->mxRange ); 5064 table::CellRangeAddress destAddress = destRangeHelper.getCellRangeAddressable()->getRangeAddress(); 5065 5066 RangeHelper thisRange( mxRange ); 5067 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 5068 ScRange sourceRange; 5069 ScRange destRange; 5070 5071 ScUnoConversion::FillScRange( destRange, destAddress ); 5072 ScUnoConversion::FillScRange( sourceRange, thisAddress ); 5073 5074 FillDir eDir = FILL_TO_BOTTOM; 5075 double fStep = 1.0; 5076 5077 ScRange aRange( destRange ); 5078 ScRange aSourceRange( destRange ); 5079 5080 // default to include the number of Rows in the source range; 5081 SCCOLROW nSourceCount = ( sourceRange.aEnd.Row() - sourceRange.aStart.Row() ) + 1; 5082 SCCOLROW nCount = 0; 5083 5084 if ( sourceRange != destRange ) 5085 { 5086 // Find direction of fill, vertical or horizontal 5087 if ( sourceRange.aStart == destRange.aStart ) 5088 { 5089 if ( sourceRange.aEnd.Row() == destRange.aEnd.Row() ) 5090 { 5091 nSourceCount = ( sourceRange.aEnd.Col() - sourceRange.aStart.Col() + 1 ); 5092 aSourceRange.aEnd.SetCol( static_cast<SCCOL>( aSourceRange.aStart.Col() + nSourceCount - 1 ) ); 5093 eDir = FILL_TO_RIGHT; 5094 nCount = aRange.aEnd.Col() - aSourceRange.aEnd.Col(); 5095 } 5096 else if ( sourceRange.aEnd.Col() == destRange.aEnd.Col() ) 5097 { 5098 aSourceRange.aEnd.SetRow( static_cast<SCROW>( aSourceRange.aStart.Row() + nSourceCount ) - 1 ); 5099 nCount = aRange.aEnd.Row() - aSourceRange.aEnd.Row(); 5100 eDir = FILL_TO_BOTTOM; 5101 } 5102 } 5103 5104 else if ( aSourceRange.aEnd == destRange.aEnd ) 5105 { 5106 if ( sourceRange.aStart.Col() == destRange.aStart.Col() ) 5107 { 5108 aSourceRange.aStart.SetRow( static_cast<SCROW>( aSourceRange.aEnd.Row() - nSourceCount + 1 ) ); 5109 nCount = aSourceRange.aStart.Row() - aRange.aStart.Row(); 5110 eDir = FILL_TO_TOP; 5111 fStep = -fStep; 5112 } 5113 else if ( sourceRange.aStart.Row() == destRange.aStart.Row() ) 5114 { 5115 nSourceCount = ( sourceRange.aEnd.Col() - sourceRange.aStart.Col() ) + 1; 5116 aSourceRange.aStart.SetCol( static_cast<SCCOL>( aSourceRange.aEnd.Col() - nSourceCount + 1 ) ); 5117 nCount = aSourceRange.aStart.Col() - aRange.aStart.Col(); 5118 eDir = FILL_TO_LEFT; 5119 fStep = -fStep; 5120 } 5121 } 5122 } 5123 5124 FillCmd eCmd = FILL_AUTO; 5125 FillDateCmd eDateCmd = FILL_DAY; 5126 5127 if ( Type.hasValue() ) 5128 { 5129 sal_Int16 nFillType = excel::XlAutoFillType::xlFillDefault; 5130 Type >>= nFillType; 5131 switch ( nFillType ) 5132 { 5133 case excel::XlAutoFillType::xlFillCopy: 5134 eCmd = FILL_SIMPLE; 5135 fStep = 0.0; 5136 break; 5137 case excel::XlAutoFillType::xlFillDays: 5138 eCmd = FILL_DATE; 5139 break; 5140 case excel::XlAutoFillType::xlFillMonths: 5141 eCmd = FILL_DATE; 5142 eDateCmd = FILL_MONTH; 5143 break; 5144 case excel::XlAutoFillType::xlFillWeekdays: 5145 eCmd = FILL_DATE; 5146 eDateCmd = FILL_WEEKDAY; 5147 break; 5148 case excel::XlAutoFillType::xlFillYears: 5149 eCmd = FILL_DATE; 5150 eDateCmd = FILL_YEAR; 5151 break; 5152 case excel::XlAutoFillType::xlGrowthTrend: 5153 eCmd = FILL_GROWTH; 5154 break; 5155 case excel::XlAutoFillType::xlFillFormats: 5156 throw uno::RuntimeException("xlFillFormat not supported for AutoFill" ); 5157 case excel::XlAutoFillType::xlFillValues: 5158 case excel::XlAutoFillType::xlFillSeries: 5159 case excel::XlAutoFillType::xlLinearTrend: 5160 eCmd = FILL_LINEAR; 5161 break; 5162 case excel::XlAutoFillType::xlFillDefault: 5163 default: 5164 eCmd = FILL_AUTO; 5165 break; 5166 } 5167 } 5168 ScDocShell* pDocSh = getDocShellFromRange( mxRange ); 5169 pDocSh->GetDocFunc().FillAuto( aSourceRange, nullptr, eDir, eCmd, eDateCmd, 5170 nCount, fStep, MAXDOUBLE/*fEndValue*/, true, true ); 5171 } 5172 sal_Bool SAL_CALL 5173 ScVbaRange::GoalSeek( const uno::Any& Goal, const uno::Reference< excel::XRange >& ChangingCell ) 5174 { 5175 ScDocShell* pDocShell = getScDocShell(); 5176 bool bRes = true; 5177 ScVbaRange* pRange = static_cast< ScVbaRange* >( ChangingCell.get() ); 5178 if ( pDocShell && pRange ) 5179 { 5180 uno::Reference< sheet::XGoalSeek > xGoalSeek( pDocShell->GetModel(), uno::UNO_QUERY_THROW ); 5181 RangeHelper thisRange( mxRange ); 5182 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 5183 RangeHelper changingCellRange( pRange->mxRange ); 5184 table::CellRangeAddress changingCellAddr = changingCellRange.getCellRangeAddressable()->getRangeAddress(); 5185 OUString sGoal = getAnyAsString( Goal ); 5186 table::CellAddress thisCell( thisAddress.Sheet, thisAddress.StartColumn, thisAddress.StartRow ); 5187 table::CellAddress changingCell( changingCellAddr.Sheet, changingCellAddr.StartColumn, changingCellAddr.StartRow ); 5188 sheet::GoalResult res = xGoalSeek->seekGoal( thisCell, changingCell, sGoal ); 5189 ChangingCell->setValue( uno::Any( res.Result ) ); 5190 5191 // openoffice behaves differently, result is 0 if the divergence is too great 5192 // but... if it detects 0 is the value it requires then it will use that 5193 // e.g. divergence & result both = 0.0 does NOT mean there is an error 5194 if ( ( res.Divergence != 0.0 ) && ( res.Result == 0.0 ) ) 5195 bRes = false; 5196 } 5197 else 5198 bRes = false; 5199 return bRes; 5200 } 5201 5202 void 5203 ScVbaRange::Calculate( ) 5204 { 5205 getWorksheet()->Calculate(); 5206 } 5207 5208 uno::Reference< excel::XRange > SAL_CALL 5209 ScVbaRange::Item( const uno::Any& row, const uno::Any& column ) 5210 { 5211 if ( mbIsRows || mbIsColumns ) 5212 { 5213 if ( column.hasValue() ) 5214 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {} ); 5215 uno::Reference< excel::XRange > xRange; 5216 if ( mbIsColumns ) 5217 xRange = Columns( row ); 5218 else 5219 xRange = Rows( row ); 5220 return xRange; 5221 } 5222 return Cells( row, column ); 5223 } 5224 5225 void 5226 ScVbaRange::AutoOutline( ) 5227 { 5228 // #TODO #FIXME needs to check for summary row/col ( whatever they are ) 5229 // not valid for multi Area Addresses 5230 if ( m_Areas->getCount() > 1 ) 5231 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY); 5232 // So needs to either span an entire Row or a just be a single cell 5233 // ( that contains a summary RowColumn ) 5234 // also the Single cell cause doesn't seem to be handled specially in 5235 // this code ( ported from the helperapi RangeImpl.java, 5236 // RangeRowsImpl.java, RangesImpl.java, RangeSingleCellImpl.java 5237 RangeHelper thisRange( mxRange ); 5238 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 5239 5240 if ( isSingleCellRange() || mbIsRows ) 5241 { 5242 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 5243 xSheetOutline->autoOutline( thisAddress ); 5244 } 5245 else 5246 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, {}); 5247 } 5248 5249 void SAL_CALL 5250 ScVbaRange:: ClearOutline( ) 5251 { 5252 if ( m_Areas->getCount() > 1 ) 5253 { 5254 sal_Int32 nItems = m_Areas->getCount(); 5255 for ( sal_Int32 index=1; index <= nItems; ++index ) 5256 { 5257 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 5258 xRange->ClearOutline(); 5259 } 5260 return; 5261 } 5262 RangeHelper thisRange( mxRange ); 5263 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 5264 xSheetOutline->clearOutline(); 5265 } 5266 5267 void 5268 ScVbaRange::groupUnGroup( bool bUnGroup ) 5269 { 5270 if ( m_Areas->getCount() > 1 ) 5271 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY); 5272 table::TableOrientation nOrient = table::TableOrientation_ROWS; 5273 if ( mbIsColumns ) 5274 nOrient = table::TableOrientation_COLUMNS; 5275 RangeHelper thisRange( mxRange ); 5276 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress(); 5277 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW ); 5278 if ( bUnGroup ) 5279 xSheetOutline->ungroup( thisAddress, nOrient ); 5280 else 5281 xSheetOutline->group( thisAddress, nOrient ); 5282 } 5283 5284 void SAL_CALL 5285 ScVbaRange::Group( ) 5286 { 5287 groupUnGroup(false); 5288 } 5289 void SAL_CALL 5290 ScVbaRange::Ungroup( ) 5291 { 5292 groupUnGroup(true); 5293 } 5294 5295 /// @throws uno::RuntimeException 5296 static void lcl_mergeCellsOfRange( const uno::Reference< table::XCellRange >& xCellRange, bool _bMerge ) 5297 { 5298 uno::Reference< util::XMergeable > xMergeable( xCellRange, uno::UNO_QUERY_THROW ); 5299 xMergeable->merge(_bMerge); 5300 } 5301 void SAL_CALL 5302 ScVbaRange::Merge( const uno::Any& Across ) 5303 { 5304 if ( m_Areas->getCount() > 1 ) 5305 { 5306 sal_Int32 nItems = m_Areas->getCount(); 5307 for ( sal_Int32 index=1; index <= nItems; ++index ) 5308 { 5309 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 5310 xRange->Merge(Across); 5311 } 5312 return; 5313 } 5314 bool bAcross = false; 5315 Across >>= bAcross; 5316 if ( !bAcross ) 5317 lcl_mergeCellsOfRange( mxRange, true ); 5318 else 5319 { 5320 uno::Reference< excel::XRange > oRangeRowsImpl = Rows( uno::Any() ); 5321 // #TODO #FIXME this seems incredibly lame, this can't be right 5322 for (sal_Int32 i=1; i <= oRangeRowsImpl->getCount();i++) 5323 { 5324 oRangeRowsImpl->Cells( uno::Any( i ), uno::Any() )->Merge( uno::Any( false ) ); 5325 } 5326 } 5327 } 5328 5329 void SAL_CALL 5330 ScVbaRange::UnMerge( ) 5331 { 5332 if ( m_Areas->getCount() > 1 ) 5333 { 5334 sal_Int32 nItems = m_Areas->getCount(); 5335 for ( sal_Int32 index=1; index <= nItems; ++index ) 5336 { 5337 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 5338 xRange->UnMerge(); 5339 } 5340 return; 5341 } 5342 lcl_mergeCellsOfRange( mxRange, false); 5343 } 5344 5345 uno::Any SAL_CALL 5346 ScVbaRange::getStyle() 5347 { 5348 if ( m_Areas->getCount() > 1 ) 5349 { 5350 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW ); 5351 return xRange->getStyle(); 5352 } 5353 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW ); 5354 OUString sStyleName; 5355 xProps->getPropertyValue( CELLSTYLE ) >>= sStyleName; 5356 ScDocShell* pShell = getScDocShell(); 5357 uno::Reference< frame::XModel > xModel( pShell->GetModel() ); 5358 uno::Reference< excel::XStyle > xStyle = new ScVbaStyle( this, mxContext, sStyleName, xModel ); 5359 return uno::Any( xStyle ); 5360 } 5361 void SAL_CALL 5362 ScVbaRange::setStyle( const uno::Any& _style ) 5363 { 5364 if ( m_Areas->getCount() > 1 ) 5365 { 5366 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW ); 5367 xRange->setStyle( _style ); 5368 return; 5369 } 5370 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW ); 5371 uno::Reference< excel::XStyle > xStyle; 5372 _style >>= xStyle; 5373 if ( xStyle.is() ) 5374 xProps->setPropertyValue( CELLSTYLE, uno::Any( xStyle->getName() ) ); 5375 } 5376 5377 uno::Reference< excel::XRange > 5378 ScVbaRange::PreviousNext( bool bIsPrevious ) 5379 { 5380 ScMarkData markedRange(getScDocument().GetSheetLimits()); 5381 ScRange refRange; 5382 RangeHelper thisRange( mxRange ); 5383 5384 ScUnoConversion::FillScRange( refRange, thisRange.getCellRangeAddressable()->getRangeAddress()); 5385 markedRange. SetMarkArea( refRange ); 5386 short nMove = bIsPrevious ? -1 : 1; 5387 5388 SCCOL nNewX = refRange.aStart.Col(); 5389 SCROW nNewY = refRange.aStart.Row(); 5390 SCTAB nTab = refRange.aStart.Tab(); 5391 5392 ScDocument& rDoc = getScDocument(); 5393 rDoc.GetNextPos( nNewX,nNewY, nTab, nMove,0, true,true, markedRange ); 5394 refRange.aStart.SetCol( nNewX ); 5395 refRange.aStart.SetRow( nNewY ); 5396 refRange.aStart.SetTab( nTab ); 5397 refRange.aEnd.SetCol( nNewX ); 5398 refRange.aEnd.SetRow( nNewY ); 5399 refRange.aEnd.SetTab( nTab ); 5400 5401 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell() , refRange ) ); 5402 5403 return new ScVbaRange( mxParent, mxContext, xRange ); 5404 } 5405 5406 uno::Reference< excel::XRange > SAL_CALL 5407 ScVbaRange::Next() 5408 { 5409 if ( m_Areas->getCount() > 1 ) 5410 { 5411 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32( 1 ) ), uno::Any() ) , uno::UNO_QUERY_THROW ); 5412 return xRange->Next(); 5413 } 5414 return PreviousNext( false ); 5415 } 5416 5417 uno::Reference< excel::XRange > SAL_CALL 5418 ScVbaRange::Previous() 5419 { 5420 if ( m_Areas->getCount() > 1 ) 5421 { 5422 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW ); 5423 return xRange->Previous(); 5424 } 5425 return PreviousNext( true ); 5426 } 5427 5428 uno::Reference< excel::XRange > SAL_CALL 5429 ScVbaRange::SpecialCells( const uno::Any& _oType, const uno::Any& _oValue) 5430 { 5431 bool bIsSingleCell = isSingleCellRange(); 5432 bool bIsMultiArea = ( m_Areas->getCount() > 1 ); 5433 ScVbaRange* pRangeToUse = this; 5434 uno::Reference< excel::XRange > xUsedRange( getWorksheet()->getUsedRange() ); 5435 sal_Int32 nType = 0; 5436 if ( !( _oType >>= nType ) ) 5437 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {} ); 5438 switch(nType) 5439 { 5440 case excel::XlCellType::xlCellTypeSameFormatConditions: 5441 case excel::XlCellType::xlCellTypeAllValidation: 5442 case excel::XlCellType::xlCellTypeSameValidation: 5443 DebugHelper::basicexception(ERRCODE_BASIC_NOT_IMPLEMENTED, {}); 5444 break; 5445 case excel::XlCellType::xlCellTypeBlanks: 5446 case excel::XlCellType::xlCellTypeComments: 5447 case excel::XlCellType::xlCellTypeConstants: 5448 case excel::XlCellType::xlCellTypeFormulas: 5449 case excel::XlCellType::xlCellTypeVisible: 5450 case excel::XlCellType::xlCellTypeLastCell: 5451 { 5452 if ( bIsMultiArea ) 5453 { 5454 // need to process each area, gather the results and 5455 // create a new range from those 5456 std::vector< table::CellRangeAddress > rangeResults; 5457 sal_Int32 nItems = m_Areas->getCount() + 1; 5458 for ( sal_Int32 index=1; index <= nItems; ++index ) 5459 { 5460 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::Any(index), uno::Any() ), uno::UNO_QUERY_THROW ); 5461 xRange = xRange->SpecialCells( _oType, _oValue); 5462 ScVbaRange* pRange = getImplementation( xRange ); 5463 if ( xRange.is() && pRange ) 5464 { 5465 sal_Int32 nElems = pRange->m_Areas->getCount() + 1; 5466 for ( sal_Int32 nArea = 1; nArea < nElems; ++nArea ) 5467 { 5468 uno::Reference< excel::XRange > xTmpRange( m_Areas->Item( uno::Any( nArea ), uno::Any() ), uno::UNO_QUERY_THROW ); 5469 RangeHelper rHelper( xTmpRange->getCellRange() ); 5470 rangeResults.push_back( rHelper.getCellRangeAddressable()->getRangeAddress() ); 5471 } 5472 } 5473 } 5474 ScRangeList aCellRanges; 5475 for ( const auto& rRangeResult : rangeResults ) 5476 { 5477 ScRange refRange; 5478 ScUnoConversion::FillScRange( refRange, rRangeResult ); 5479 aCellRanges.push_back( refRange ); 5480 } 5481 // Single range 5482 if ( aCellRanges.size() == 1 ) 5483 { 5484 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell(), aCellRanges.front() ) ); 5485 return new ScVbaRange( mxParent, mxContext, xRange ); 5486 } 5487 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( getScDocShell(), aCellRanges ) ); 5488 5489 return new ScVbaRange( mxParent, mxContext, xRanges ); 5490 } 5491 else if ( bIsSingleCell ) 5492 { 5493 pRangeToUse = static_cast< ScVbaRange* >( xUsedRange.get() ); 5494 } 5495 5496 break; 5497 } 5498 default: 5499 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {} ); 5500 break; 5501 } 5502 if ( !pRangeToUse ) 5503 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, {} ); 5504 return pRangeToUse->SpecialCellsImpl( nType, _oValue ); 5505 } 5506 5507 /// @throws script::BasicErrorException 5508 static sal_Int32 lcl_getFormulaResultFlags(const uno::Any& aType) 5509 { 5510 sal_Int32 nType = excel::XlSpecialCellsValue::xlNumbers; 5511 aType >>= nType; 5512 sal_Int32 nRes = sheet::FormulaResult::VALUE; 5513 5514 switch(nType) 5515 { 5516 case excel::XlSpecialCellsValue::xlErrors: 5517 nRes= sheet::FormulaResult::ERROR; 5518 break; 5519 case excel::XlSpecialCellsValue::xlLogical: 5520 //TODO bc93774: ask NN if this is really an appropriate substitute 5521 nRes = sheet::FormulaResult::VALUE; 5522 break; 5523 case excel::XlSpecialCellsValue::xlNumbers: 5524 nRes = sheet::FormulaResult::VALUE; 5525 break; 5526 case excel::XlSpecialCellsValue::xlTextValues: 5527 nRes = sheet::FormulaResult::STRING; 5528 break; 5529 default: 5530 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {} ); 5531 } 5532 return nRes; 5533 } 5534 5535 uno::Reference< excel::XRange > 5536 ScVbaRange::SpecialCellsImpl( sal_Int32 nType, const uno::Any& _oValue) 5537 { 5538 uno::Reference< excel::XRange > xRange; 5539 try 5540 { 5541 uno::Reference< sheet::XCellRangesQuery > xQuery( mxRange, uno::UNO_QUERY_THROW ); 5542 uno::Reference< sheet::XSheetCellRanges > xLocSheetCellRanges; 5543 switch(nType) 5544 { 5545 case excel::XlCellType::xlCellTypeAllFormatConditions: 5546 case excel::XlCellType::xlCellTypeSameFormatConditions: 5547 case excel::XlCellType::xlCellTypeAllValidation: 5548 case excel::XlCellType::xlCellTypeSameValidation: 5549 // Shouldn't get here ( should be filtered out by 5550 // ScVbaRange::SpecialCells() 5551 DebugHelper::basicexception(ERRCODE_BASIC_NOT_IMPLEMENTED, {}); 5552 break; 5553 case excel::XlCellType::xlCellTypeBlanks: 5554 xLocSheetCellRanges = xQuery->queryEmptyCells(); 5555 break; 5556 case excel::XlCellType::xlCellTypeComments: 5557 xLocSheetCellRanges = xQuery->queryContentCells(sheet::CellFlags::ANNOTATION); 5558 break; 5559 case excel::XlCellType::xlCellTypeConstants: 5560 xLocSheetCellRanges = xQuery->queryContentCells(23); 5561 break; 5562 case excel::XlCellType::xlCellTypeFormulas: 5563 { 5564 sal_Int32 nFormulaResult = lcl_getFormulaResultFlags(_oValue); 5565 xLocSheetCellRanges = xQuery->queryFormulaCells(nFormulaResult); 5566 break; 5567 } 5568 case excel::XlCellType::xlCellTypeLastCell: 5569 xRange = Cells( uno::Any( getCount() ), uno::Any() ); 5570 [[fallthrough]]; //TODO ??? 5571 case excel::XlCellType::xlCellTypeVisible: 5572 xLocSheetCellRanges = xQuery->queryVisibleCells(); 5573 break; 5574 default: 5575 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {} ); 5576 break; 5577 } 5578 if (xLocSheetCellRanges.is()) 5579 { 5580 xRange = lcl_makeXRangeFromSheetCellRanges( getParent(), mxContext, xLocSheetCellRanges, getScDocShell() ); 5581 } 5582 } 5583 catch (uno::Exception& ) 5584 { 5585 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, u"No cells were found"); 5586 } 5587 return xRange; 5588 } 5589 5590 void SAL_CALL 5591 ScVbaRange::RemoveSubtotal( ) 5592 { 5593 uno::Reference< sheet::XSubTotalCalculatable > xSub( mxRange, uno::UNO_QUERY_THROW ); 5594 xSub->removeSubTotals(); 5595 } 5596 5597 void SAL_CALL 5598 ScVbaRange::Subtotal( ::sal_Int32 _nGroupBy, ::sal_Int32 _nFunction, const uno::Sequence< ::sal_Int32 >& _nTotalList, const uno::Any& aReplace, const uno::Any& PageBreaks, const uno::Any& /*SummaryBelowData*/ ) 5599 { 5600 try 5601 { 5602 bool bDoReplace = false; 5603 aReplace >>= bDoReplace; 5604 bool bAddPageBreaks = false; 5605 PageBreaks >>= bAddPageBreaks; 5606 5607 uno::Reference< sheet::XSubTotalCalculatable> xSub(mxRange, uno::UNO_QUERY_THROW ); 5608 uno::Reference< sheet::XSubTotalDescriptor > xSubDesc = xSub->createSubTotalDescriptor(true); 5609 uno::Reference< beans::XPropertySet > xSubDescPropertySet( xSubDesc, uno::UNO_QUERY_THROW ); 5610 xSubDescPropertySet->setPropertyValue("InsertPageBreaks", uno::Any( bAddPageBreaks)); 5611 sal_Int32 nLen = _nTotalList.getLength(); 5612 uno::Sequence< sheet::SubTotalColumn > aColumns( nLen ); 5613 auto aColumnsRange = asNonConstRange(aColumns); 5614 for (int i = 0; i < nLen; i++) 5615 { 5616 aColumnsRange[i].Column = _nTotalList[i] - 1; 5617 switch (_nFunction) 5618 { 5619 case excel::XlConsolidationFunction::xlAverage: 5620 aColumnsRange[i].Function = sheet::GeneralFunction_AVERAGE; 5621 break; 5622 case excel::XlConsolidationFunction::xlCount: 5623 aColumnsRange[i].Function = sheet::GeneralFunction_COUNT; 5624 break; 5625 case excel::XlConsolidationFunction::xlCountNums: 5626 aColumnsRange[i].Function = sheet::GeneralFunction_COUNTNUMS; 5627 break; 5628 case excel::XlConsolidationFunction::xlMax: 5629 aColumnsRange[i].Function = sheet::GeneralFunction_MAX; 5630 break; 5631 case excel::XlConsolidationFunction::xlMin: 5632 aColumnsRange[i].Function = sheet::GeneralFunction_MIN; 5633 break; 5634 case excel::XlConsolidationFunction::xlProduct: 5635 aColumnsRange[i].Function = sheet::GeneralFunction_PRODUCT; 5636 break; 5637 case excel::XlConsolidationFunction::xlStDev: 5638 aColumnsRange[i].Function = sheet::GeneralFunction_STDEV; 5639 break; 5640 case excel::XlConsolidationFunction::xlStDevP: 5641 aColumnsRange[i].Function = sheet::GeneralFunction_STDEVP; 5642 break; 5643 case excel::XlConsolidationFunction::xlSum: 5644 aColumnsRange[i].Function = sheet::GeneralFunction_SUM; 5645 break; 5646 case excel::XlConsolidationFunction::xlUnknown: 5647 aColumnsRange[i].Function = sheet::GeneralFunction_NONE; 5648 break; 5649 case excel::XlConsolidationFunction::xlVar: 5650 aColumnsRange[i].Function = sheet::GeneralFunction_VAR; 5651 break; 5652 case excel::XlConsolidationFunction::xlVarP: 5653 aColumnsRange[i].Function = sheet::GeneralFunction_VARP; 5654 break; 5655 default: 5656 DebugHelper::basicexception(ERRCODE_BASIC_BAD_PARAMETER, {}) ; 5657 return; 5658 } 5659 } 5660 xSubDesc->addNew(aColumns, _nGroupBy - 1); 5661 xSub->applySubTotals(xSubDesc, bDoReplace); 5662 } 5663 catch (const uno::Exception&) 5664 { 5665 DebugHelper::basicexception(ERRCODE_BASIC_METHOD_FAILED, {}); 5666 } 5667 } 5668 5669 OUString 5670 ScVbaRange::getServiceImplName() 5671 { 5672 return "ScVbaRange"; 5673 } 5674 5675 uno::Sequence< OUString > 5676 ScVbaRange::getServiceNames() 5677 { 5678 return { "ooo.vba.excel.Range" }; 5679 } 5680 5681 sal_Bool SAL_CALL 5682 ScVbaRange::hasError() 5683 { 5684 double dResult = 0.0; 5685 uno::Reference< excel::XApplication > xApplication( Application(), uno::UNO_QUERY_THROW ); 5686 uno::Reference< script::XInvocation > xInvoc( xApplication->WorksheetFunction(), uno::UNO_QUERY_THROW ); 5687 5688 uno::Reference< excel::XRange > aRange( this ); 5689 uno::Sequence< uno::Any > Params{ uno::Any(aRange) }; 5690 uno::Sequence< sal_Int16 > OutParamIndex; 5691 uno::Sequence< uno::Any > OutParam; 5692 xInvoc->invoke( "IsError", Params, OutParamIndex, OutParam ) >>= dResult; 5693 return dResult > 0.0; 5694 } 5695 5696 5697 extern "C" SAL_DLLPUBLIC_EXPORT css::uno::XInterface* 5698 Calc_ScVbaRange_get_implementation( 5699 css::uno::XComponentContext* context, css::uno::Sequence<css::uno::Any> const& args) 5700 { 5701 return cppu::acquire(new ScVbaRange(args, context)); 5702 } 5703 5704 5705 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ 5706