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