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