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