fix baseline build (old cairo) - 'cairo_rectangle_int_t' does not name a type
[LibreOffice.git] / sc / source / ui / vba / vbarange.cxx
blobf47c1c0d4c63cc94ef6168f7b630c505ea3dc777
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3 * This file is part of the LibreOffice project.
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/.
9 * This file incorporates work covered by the following license notice:
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 .
20 #include "vbarange.hxx"
22 #include <vbahelper/helperdecl.hxx>
24 #include <comphelper/unwrapargs.hxx>
25 #include <comphelper/processfactory.hxx>
26 #include <sfx2/objsh.hxx>
28 #include <com/sun/star/script/ArrayWrapper.hpp>
29 #include <com/sun/star/script/vba/VBAEventId.hpp>
30 #include <com/sun/star/script/vba/XVBAEventProcessor.hpp>
31 #include <com/sun/star/sheet/XDatabaseRange.hpp>
32 #include <com/sun/star/sheet/XUnnamedDatabaseRanges.hpp>
33 #include <com/sun/star/sheet/XGoalSeek.hpp>
34 #include <com/sun/star/sheet/XSheetOperation.hpp>
35 #include <com/sun/star/sheet/CellFlags.hpp>
36 #include <com/sun/star/table/XColumnRowRange.hpp>
37 #include <com/sun/star/sheet/XCellAddressable.hpp>
38 #include <com/sun/star/table/CellContentType.hpp>
39 #include <com/sun/star/sheet/XCellSeries.hpp>
40 #include <com/sun/star/text/XTextRange.hpp>
41 #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
42 #include <com/sun/star/table/CellAddress.hpp>
43 #include <com/sun/star/table/CellRangeAddress.hpp>
44 #include <com/sun/star/sheet/XSpreadsheetView.hpp>
45 #include <com/sun/star/sheet/XCellRangeReferrer.hpp>
46 #include <com/sun/star/sheet/XSheetCellRange.hpp>
47 #include <com/sun/star/sheet/XSpreadsheet.hpp>
48 #include <com/sun/star/sheet/XSheetCellCursor.hpp>
49 #include <com/sun/star/sheet/XArrayFormulaRange.hpp>
50 #include <com/sun/star/sheet/XNamedRange.hpp>
51 #include <com/sun/star/sheet/XPrintAreas.hpp>
52 #include <com/sun/star/sheet/XCellRangesQuery.hpp>
53 #include <com/sun/star/beans/XPropertySet.hpp>
54 #include <com/sun/star/sheet/XFunctionAccess.hpp>
55 #include <com/sun/star/frame/XModel.hpp>
56 #include <com/sun/star/view/XSelectionSupplier.hpp>
57 #include <com/sun/star/table/XCellCursor.hpp>
58 #include <com/sun/star/table/XTableRows.hpp>
59 #include <com/sun/star/table/XTableColumns.hpp>
60 #include <com/sun/star/table/TableSortField.hpp>
61 #include <com/sun/star/util/XMergeable.hpp>
62 #include <com/sun/star/uno/XComponentContext.hpp>
63 #include <com/sun/star/lang/XMultiComponentFactory.hpp>
64 #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
65 #include <com/sun/star/util/XNumberFormatsSupplier.hpp>
66 #include <com/sun/star/util/XNumberFormats.hpp>
67 #include <com/sun/star/util/NumberFormat.hpp>
68 #include <com/sun/star/util/XNumberFormatTypes.hpp>
69 #include <com/sun/star/util/XReplaceable.hpp>
70 #include <com/sun/star/util/XSortable.hpp>
71 #include <com/sun/star/sheet/XCellRangeMovement.hpp>
72 #include <com/sun/star/sheet/XCellRangeData.hpp>
73 #include <com/sun/star/sheet/FormulaResult.hpp>
74 #include <com/sun/star/sheet/FilterOperator2.hpp>
75 #include <com/sun/star/sheet/TableFilterField.hpp>
76 #include <com/sun/star/sheet/TableFilterField2.hpp>
77 #include <com/sun/star/sheet/XSheetFilterDescriptor2.hpp>
78 #include <com/sun/star/sheet/XSheetFilterable.hpp>
79 #include <com/sun/star/sheet/FilterConnection.hpp>
80 #include <com/sun/star/util/CellProtection.hpp>
81 #include <com/sun/star/util/TriState.hpp>
83 #include <com/sun/star/style/XStyleFamiliesSupplier.hpp>
84 #include <com/sun/star/awt/XDevice.hpp>
86 #include <com/sun/star/sheet/XSubTotalCalculatable.hpp>
87 #include <com/sun/star/sheet/XSubTotalDescriptor.hpp>
88 #include <com/sun/star/sheet/GeneralFunction.hpp>
90 #include <com/sun/star/sheet/XSheetAnnotationsSupplier.hpp>
91 #include <com/sun/star/sheet/XSheetAnnotations.hpp>
93 #include <ooo/vba/excel/XlPasteSpecialOperation.hpp>
94 #include <ooo/vba/excel/XlPasteType.hpp>
95 #include <ooo/vba/excel/Constants.hpp>
96 #include <ooo/vba/excel/XlFindLookIn.hpp>
97 #include <ooo/vba/excel/XlLookAt.hpp>
98 #include <ooo/vba/excel/XlSearchOrder.hpp>
99 #include <ooo/vba/excel/XlSortOrder.hpp>
100 #include <ooo/vba/excel/XlYesNoGuess.hpp>
101 #include <ooo/vba/excel/XlSortOrientation.hpp>
102 #include <ooo/vba/excel/XlSortMethod.hpp>
103 #include <ooo/vba/excel/XlDirection.hpp>
104 #include <ooo/vba/excel/XlSortDataOption.hpp>
105 #include <ooo/vba/excel/XlDeleteShiftDirection.hpp>
106 #include <ooo/vba/excel/XlInsertShiftDirection.hpp>
107 #include <ooo/vba/excel/XlReferenceStyle.hpp>
108 #include <ooo/vba/excel/XlBordersIndex.hpp>
109 #include <ooo/vba/excel/XlPageBreak.hpp>
110 #include <ooo/vba/excel/XlAutoFilterOperator.hpp>
111 #include <ooo/vba/excel/XlAutoFillType.hpp>
112 #include <ooo/vba/excel/XlTextParsingType.hpp>
113 #include <ooo/vba/excel/XlTextQualifier.hpp>
114 #include <ooo/vba/excel/XlCellType.hpp>
115 #include <ooo/vba/excel/XlSpecialCellsValue.hpp>
116 #include <ooo/vba/excel/XlConsolidationFunction.hpp>
117 #include <ooo/vba/excel/XlSearchDirection.hpp>
119 #include <scitems.hxx>
120 #include <svl/srchitem.hxx>
121 #include <cellsuno.hxx>
122 #include <dbdata.hxx>
123 #include "docfunc.hxx"
124 #include <docuno.hxx>
126 #include <sfx2/dispatch.hxx>
127 #include <sfx2/app.hxx>
128 #include <sfx2/bindings.hxx>
129 #include <sfx2/request.hxx>
130 #include <sfx2/viewfrm.hxx>
131 #include <sfx2/itemwrapper.hxx>
132 #include <sc.hrc>
133 #include <globstr.hrc>
134 #include <unonames.hxx>
136 #include "vbaapplication.hxx"
137 #include "vbafont.hxx"
138 #include "vbacomment.hxx"
139 #include "vbainterior.hxx"
140 #include "vbacharacters.hxx"
141 #include "vbaborders.hxx"
142 #include "vbaworksheet.hxx"
143 #include "vbavalidation.hxx"
144 #include "vbahyperlinks.hxx"
146 #include "tabvwsh.hxx"
147 #include "rangelst.hxx"
148 #include "convuno.hxx"
149 #include "compiler.hxx"
150 #include "attrib.hxx"
151 #include "undodat.hxx"
152 #include "dbdocfun.hxx"
153 #include "patattr.hxx"
154 #include "olinetab.hxx"
155 #include "transobj.hxx"
156 #include "queryentry.hxx"
157 #include "markdata.hxx"
158 #include <comphelper/anytostring.hxx>
160 #include <global.hxx>
162 #include "vbaglobals.hxx"
163 #include "vbastyle.hxx"
164 #include "vbaname.hxx"
165 #include <vector>
166 #include <vbahelper/vbacollectionimpl.hxx>
167 // begin test includes
168 #include <com/sun/star/sheet/FunctionArgument.hpp>
169 // end test includes
171 #include <ooo/vba/excel/Range.hpp>
172 #include <com/sun/star/bridge/oleautomation/Date.hpp>
173 #include "tokenarray.hxx"
174 #include "tokenuno.hxx"
175 #include <columnspanset.hxx>
177 #include <boost/scoped_ptr.hpp>
179 using namespace ::ooo::vba;
180 using namespace ::com::sun::star;
181 using ::std::vector;
183 // difference between VBA and file format width, in character units
184 const double fExtraWidth = 182.0 / 256.0;
186 // * 1 point = 1/72 inch = 20 twips
187 // * 1 inch = 72 points = 1440 twips
188 // * 1 cm = 567 twips
189 static double lcl_hmmToPoints( double nVal ) { return ( (double)((nVal /1000 ) * 567 ) / 20 ); }
191 static const sal_Int16 supportedIndexTable[] = { excel::XlBordersIndex::xlEdgeLeft, excel::XlBordersIndex::xlEdgeTop, excel::XlBordersIndex::xlEdgeBottom, excel::XlBordersIndex::xlEdgeRight, excel::XlBordersIndex::xlDiagonalDown, excel::XlBordersIndex::xlDiagonalUp, excel::XlBordersIndex::xlInsideVertical, excel::XlBordersIndex::xlInsideHorizontal };
193 static sal_uInt16 lcl_pointsToTwips( double nVal )
195 nVal = nVal * static_cast<double>(20);
196 short nTwips = static_cast<short>(nVal);
197 return nTwips;
199 static double lcl_TwipsToPoints( sal_uInt16 nVal )
201 double nPoints = nVal;
202 return nPoints / 20;
205 static double lcl_Round2DecPlaces( double nVal )
207 nVal = (nVal * (double)100);
208 long tmp = static_cast<long>(nVal);
209 if ( ( ( nVal - tmp ) >= 0.5 ) )
210 ++tmp;
211 nVal = tmp;
212 nVal = nVal/100;
213 return nVal;
216 static uno::Any lcl_makeRange( const uno::Reference< XHelperInterface >& rParent, const uno::Reference< uno::XComponentContext >& rContext, const uno::Any& rAny, bool bIsRows, bool bIsColumns )
218 uno::Reference< table::XCellRange > xCellRange(rAny, uno::UNO_QUERY_THROW);
219 return uno::makeAny( uno::Reference< excel::XRange >( new ScVbaRange( rParent, rContext, xCellRange, bIsRows, bIsColumns ) ) );
222 static uno::Reference< excel::XRange > lcl_makeXRangeFromSheetCellRanges( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< sheet::XSheetCellRanges >& xLocSheetCellRanges, ScDocShell* pDoc )
224 uno::Reference< excel::XRange > xRange;
225 uno::Sequence< table::CellRangeAddress > sAddresses = xLocSheetCellRanges->getRangeAddresses();
226 ScRangeList aCellRanges;
227 sal_Int32 nLen = sAddresses.getLength();
228 if ( nLen )
230 for ( sal_Int32 index = 0; index < nLen; ++index )
232 ScRange refRange;
233 ScUnoConversion::FillScRange( refRange, sAddresses[ index ] );
234 aCellRanges.Append( refRange );
236 // Single range
237 if ( aCellRanges.size() == 1 )
239 uno::Reference< table::XCellRange > xTmpRange( new ScCellRangeObj( pDoc, *aCellRanges.front() ) );
240 xRange = new ScVbaRange( xParent, xContext, xTmpRange );
242 else
244 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pDoc, aCellRanges ) );
245 xRange = new ScVbaRange( xParent, xContext, xRanges );
248 return xRange;
251 ScCellRangesBase* ScVbaRange::getCellRangesBase() throw ( uno::RuntimeException )
253 if( mxRanges.is() )
254 return ScCellRangesBase::getImplementation( mxRanges );
255 if( mxRange.is() )
256 return ScCellRangesBase::getImplementation( mxRange );
257 throw uno::RuntimeException("General Error creating range - Unknown" );
260 ScCellRangeObj* ScVbaRange::getCellRangeObj() throw ( uno::RuntimeException )
262 return dynamic_cast< ScCellRangeObj* >( getCellRangesBase() );
265 SfxItemSet* ScVbaRange::getCurrentDataSet( ) throw ( uno::RuntimeException )
267 SfxItemSet* pDataSet = excel::ScVbaCellRangeAccess::GetDataSet( getCellRangesBase() );
268 if ( !pDataSet )
269 throw uno::RuntimeException("Can't access Itemset for range" );
270 return pDataSet;
273 void ScVbaRange::fireChangeEvent()
275 if( ScVbaApplication::getDocumentEventsEnabled() )
277 ScDocument& rDoc = getScDocument();
278 uno::Reference< script::vba::XVBAEventProcessor > xVBAEvents = rDoc.GetVbaEventProcessor();
279 if( xVBAEvents.is() ) try
281 uno::Sequence< uno::Any > aArgs( 1 );
282 aArgs[ 0 ] <<= uno::Reference< excel::XRange >( this );
283 xVBAEvents->processVbaEvent( script::vba::VBAEventId::WORKSHEET_CHANGE, aArgs );
285 catch( uno::Exception& )
291 class SingleRangeEnumeration : public EnumerationHelper_BASE
293 uno::Reference< XHelperInterface > m_xParent;
294 uno::Reference< table::XCellRange > m_xRange;
295 uno::Reference< uno::XComponentContext > mxContext;
296 bool bHasMore;
297 public:
299 SingleRangeEnumeration( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< css::uno::XComponentContext >& xContext, const uno::Reference< table::XCellRange >& xRange ) throw ( uno::RuntimeException ) : m_xParent( xParent ), m_xRange( xRange ), mxContext( xContext ), bHasMore( true ) { }
300 virtual sal_Bool SAL_CALL hasMoreElements( ) throw (uno::RuntimeException, std::exception) SAL_OVERRIDE { return bHasMore; }
301 virtual uno::Any SAL_CALL nextElement( ) throw (container::NoSuchElementException, lang::WrappedTargetException, uno::RuntimeException, std::exception) SAL_OVERRIDE
303 if ( !bHasMore )
304 throw container::NoSuchElementException();
305 bHasMore = false;
306 return uno::makeAny( m_xRange );
310 // very simple class to pass to ScVbaCollectionBaseImpl containing
311 // just one item
312 typedef ::cppu::WeakImplHelper2< container::XIndexAccess, container::XEnumerationAccess > SingleRange_BASE;
314 class SingleRangeIndexAccess : public SingleRange_BASE
316 private:
317 uno::Reference< XHelperInterface > mxParent;
318 uno::Reference< table::XCellRange > m_xRange;
319 uno::Reference< uno::XComponentContext > mxContext;
321 public:
322 SingleRangeIndexAccess( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< table::XCellRange >& xRange ):mxParent( xParent ), m_xRange( xRange ), mxContext( xContext ) {}
323 // XIndexAccess
324 virtual ::sal_Int32 SAL_CALL getCount() throw (::uno::RuntimeException, std::exception) SAL_OVERRIDE { return 1; }
325 virtual uno::Any SAL_CALL getByIndex( ::sal_Int32 Index ) throw (lang::IndexOutOfBoundsException, lang::WrappedTargetException, uno::RuntimeException, std::exception) SAL_OVERRIDE
327 if ( Index != 0 )
328 throw lang::IndexOutOfBoundsException();
329 return uno::makeAny( m_xRange );
331 // XElementAccess
332 virtual uno::Type SAL_CALL getElementType() throw (uno::RuntimeException, std::exception) SAL_OVERRIDE { return cppu::UnoType<table::XCellRange>::get(); }
334 virtual sal_Bool SAL_CALL hasElements() throw (uno::RuntimeException, std::exception) SAL_OVERRIDE { return sal_True; }
335 // XEnumerationAccess
336 virtual uno::Reference< container::XEnumeration > SAL_CALL createEnumeration() throw (uno::RuntimeException, std::exception) SAL_OVERRIDE { return new SingleRangeEnumeration( mxParent, mxContext, m_xRange ); }
340 class RangesEnumerationImpl : public EnumerationHelperImpl
342 bool mbIsRows;
343 bool mbIsColumns;
344 public:
346 RangesEnumerationImpl( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< container::XEnumeration >& xEnumeration, bool bIsRows, bool bIsColumns ) throw ( uno::RuntimeException ) : EnumerationHelperImpl( xParent, xContext, xEnumeration ), mbIsRows( bIsRows ), mbIsColumns( bIsColumns ) {}
347 virtual uno::Any SAL_CALL nextElement( ) throw (container::NoSuchElementException, lang::WrappedTargetException, uno::RuntimeException, std::exception) SAL_OVERRIDE
349 return lcl_makeRange( m_xParent, m_xContext, m_xEnumeration->nextElement(), mbIsRows, mbIsColumns );
353 class ScVbaRangeAreas : public ScVbaCollectionBaseImpl
355 bool mbIsRows;
356 bool mbIsColumns;
357 public:
358 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 ) {}
360 // XEnumerationAccess
361 virtual uno::Reference< container::XEnumeration > SAL_CALL createEnumeration() throw (uno::RuntimeException) SAL_OVERRIDE;
363 // XElementAccess
364 virtual uno::Type SAL_CALL getElementType() throw (uno::RuntimeException) SAL_OVERRIDE { return cppu::UnoType<excel::XRange>::get(); }
366 virtual uno::Any createCollectionObject( const uno::Any& aSource ) SAL_OVERRIDE;
368 virtual OUString getServiceImplName() SAL_OVERRIDE { return OUString(); }
370 virtual uno::Sequence< OUString > getServiceNames() SAL_OVERRIDE { return uno::Sequence< OUString >(); }
374 uno::Reference< container::XEnumeration > SAL_CALL
375 ScVbaRangeAreas::createEnumeration() throw (uno::RuntimeException)
377 uno::Reference< container::XEnumerationAccess > xEnumAccess( m_xIndexAccess, uno::UNO_QUERY_THROW );
378 return new RangesEnumerationImpl( mxParent, mxContext, xEnumAccess->createEnumeration(), mbIsRows, mbIsColumns );
381 uno::Any
382 ScVbaRangeAreas::createCollectionObject( const uno::Any& aSource )
384 return lcl_makeRange( mxParent, mxContext, aSource, mbIsRows, mbIsColumns );
387 // assume that xIf is infact a ScCellRangesBase
388 ScDocShell*
389 getDocShellFromIf( const uno::Reference< uno::XInterface >& xIf ) throw ( uno::RuntimeException )
391 ScCellRangesBase* pUno = ScCellRangesBase::getImplementation( xIf );
392 if ( !pUno )
393 throw uno::RuntimeException("Failed to access underlying uno range object" );
394 return pUno->GetDocShell();
397 ScDocShell*
398 getDocShellFromRange( const uno::Reference< table::XCellRange >& xRange ) throw ( uno::RuntimeException )
400 // need the ScCellRangesBase to get docshell
401 uno::Reference< uno::XInterface > xIf( xRange );
402 return getDocShellFromIf(xIf );
405 ScDocShell*
406 getDocShellFromRanges( const uno::Reference< sheet::XSheetCellRangeContainer >& xRanges ) throw ( uno::RuntimeException )
408 // need the ScCellRangesBase to get docshell
409 uno::Reference< uno::XInterface > xIf( xRanges );
410 return getDocShellFromIf(xIf );
413 uno::Reference< frame::XModel > getModelFromXIf( const uno::Reference< uno::XInterface >& xIf ) throw ( uno::RuntimeException )
415 ScDocShell* pDocShell = getDocShellFromIf(xIf );
416 return pDocShell->GetModel();
419 uno::Reference< frame::XModel > getModelFromRange( const uno::Reference< table::XCellRange >& xRange ) throw ( uno::RuntimeException )
421 // the XInterface for getImplementation can be any derived interface, no need for queryInterface
422 uno::Reference< uno::XInterface > xIf( xRange );
423 return getModelFromXIf( xIf );
426 ScDocument&
427 getDocumentFromRange( const uno::Reference< table::XCellRange >& xRange )
429 ScDocShell* pDocShell = getDocShellFromRange( xRange );
430 if ( !pDocShell )
431 throw uno::RuntimeException("Failed to access underlying docshell from uno range object" );
432 ScDocument& rDoc = pDocShell->GetDocument();
433 return rDoc;
436 ScDocument&
437 ScVbaRange::getScDocument() throw (uno::RuntimeException)
439 if ( mxRanges.is() )
441 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
442 uno::Reference< table::XCellRange > xRange( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW );
443 return getDocumentFromRange( xRange );
445 return getDocumentFromRange( mxRange );
448 ScDocShell*
449 ScVbaRange::getScDocShell() throw (uno::RuntimeException)
451 if ( mxRanges.is() )
453 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
454 uno::Reference< table::XCellRange > xRange( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW );
455 return getDocShellFromRange( xRange );
457 return getDocShellFromRange( mxRange );
460 ScVbaRange* ScVbaRange::getImplementation( const uno::Reference< excel::XRange >& rxRange )
462 // FIXME: always save to use dynamic_cast? Or better to (implement and) use XTunnel?
463 return dynamic_cast< ScVbaRange* >( rxRange.get() );
466 uno::Reference< frame::XModel > ScVbaRange::getUnoModel() throw (uno::RuntimeException)
468 if( ScDocShell* pDocShell = getScDocShell() )
469 return pDocShell->GetModel();
470 throw uno::RuntimeException();
473 uno::Reference< frame::XModel > ScVbaRange::getUnoModel( const uno::Reference< excel::XRange >& rxRange ) throw (uno::RuntimeException)
475 if( ScVbaRange* pScVbaRange = getImplementation( rxRange ) )
476 return pScVbaRange->getUnoModel();
477 throw uno::RuntimeException();
480 const ScRangeList& ScVbaRange::getScRangeList() throw (uno::RuntimeException)
482 if( ScCellRangesBase* pScRangesBase = getCellRangesBase() )
483 return pScRangesBase->GetRangeList();
484 throw uno::RuntimeException("Cannot obtain UNO range implementation object" );
487 const ScRangeList& ScVbaRange::getScRangeList( const uno::Reference< excel::XRange >& rxRange ) throw (uno::RuntimeException)
489 if( ScVbaRange* pScVbaRange = getImplementation( rxRange ) )
490 return pScVbaRange->getScRangeList();
491 throw uno::RuntimeException("Cannot obtain VBA range implementation object" );
494 class NumFormatHelper
496 uno::Reference< util::XNumberFormatsSupplier > mxSupplier;
497 uno::Reference< beans::XPropertySet > mxRangeProps;
498 uno::Reference< util::XNumberFormats > mxFormats;
499 public:
500 NumFormatHelper( const uno::Reference< table::XCellRange >& xRange )
502 mxSupplier.set( getModelFromRange( xRange ), uno::UNO_QUERY_THROW );
503 mxRangeProps.set( xRange, uno::UNO_QUERY_THROW);
504 mxFormats = mxSupplier->getNumberFormats();
506 uno::Reference< beans::XPropertySet > getNumberProps()
508 long nIndexKey = 0;
509 uno::Any aValue = mxRangeProps->getPropertyValue( "NumberFormat" );
510 aValue >>= nIndexKey;
512 if ( mxFormats.is() )
513 return mxFormats->getByKey( nIndexKey );
514 return uno::Reference< beans::XPropertySet > ();
517 bool isBooleanType()
520 if ( getNumberFormat() & util::NumberFormat::LOGICAL )
521 return true;
522 return false;
525 bool isDateType()
527 sal_Int16 nType = getNumberFormat();
528 if(( nType & util::NumberFormat::DATETIME ))
530 return true;
532 return false;
535 OUString getNumberFormatString()
537 uno::Reference< uno::XInterface > xIf( mxRangeProps, uno::UNO_QUERY_THROW );
538 ScCellRangesBase* pUnoCellRange = ScCellRangesBase::getImplementation( xIf );
539 if ( pUnoCellRange )
542 SfxItemSet* pDataSet = excel::ScVbaCellRangeAccess::GetDataSet( pUnoCellRange );
543 SfxItemState eState = pDataSet->GetItemState( ATTR_VALUE_FORMAT, true, NULL);
544 // one of the cells in the range is not like the other ;-)
545 // so return a zero length format to indicate that
546 if ( eState == SfxItemState::DONTCARE )
547 return OUString();
550 uno::Reference< beans::XPropertySet > xNumberProps( getNumberProps(), uno::UNO_QUERY_THROW );
551 OUString aFormatString;
552 uno::Any aString = xNumberProps->getPropertyValue( "FormatString" );
553 aString >>= aFormatString;
554 return aFormatString;
557 sal_Int16 getNumberFormat()
559 uno::Reference< beans::XPropertySet > xNumberProps = getNumberProps();
560 sal_Int16 nType = ::comphelper::getINT16(
561 xNumberProps->getPropertyValue( "Type" ) );
562 return nType;
565 bool setNumberFormat( const OUString& rFormat )
567 // #163288# treat "General" as "Standard" format
568 sal_Int32 nNewIndex = 0;
569 if( !rFormat.equalsIgnoreAsciiCase( "General" ) )
571 lang::Locale aLocale;
572 uno::Reference< beans::XPropertySet > xNumProps = getNumberProps();
573 xNumProps->getPropertyValue( "Locale" ) >>= aLocale;
574 nNewIndex = mxFormats->queryKey( rFormat, aLocale, false );
575 if ( nNewIndex == -1 ) // format not defined
576 nNewIndex = mxFormats->addNew( rFormat, aLocale );
578 mxRangeProps->setPropertyValue( "NumberFormat", uno::makeAny( nNewIndex ) );
579 return true;
582 bool setNumberFormat( sal_Int16 nType )
584 uno::Reference< beans::XPropertySet > xNumberProps = getNumberProps();
585 lang::Locale aLocale;
586 xNumberProps->getPropertyValue( "Locale" ) >>= aLocale;
587 uno::Reference<util::XNumberFormatTypes> xTypes( mxFormats, uno::UNO_QUERY );
588 if ( xTypes.is() )
590 sal_Int32 nNewIndex = xTypes->getStandardFormat( nType, aLocale );
591 mxRangeProps->setPropertyValue( "NumberFormat", uno::makeAny( nNewIndex ) );
592 return true;
594 return false;
599 struct CellPos
601 CellPos( sal_Int32 nRow, sal_Int32 nCol, sal_Int32 nArea ):m_nRow(nRow), m_nCol(nCol), m_nArea( nArea ) {};
602 sal_Int32 m_nRow;
603 sal_Int32 m_nCol;
604 sal_Int32 m_nArea;
607 typedef ::cppu::WeakImplHelper1< container::XEnumeration > CellsEnumeration_BASE;
608 typedef ::std::vector< CellPos > vCellPos;
610 // #FIXME - QUICK
611 // we could probably could and should modify CellsEnumeration below
612 // to handle rows and columns ( but I do this separately for now
613 // and.. this class only handles singe areas ( does it have to handle
614 // multi area ranges?? )
615 class ColumnsRowEnumeration: public CellsEnumeration_BASE
617 uno::Reference< uno::XComponentContext > mxContext;
618 uno::Reference< excel::XRange > mxRange;
619 sal_Int32 mMaxElems;
620 sal_Int32 mCurElem;
622 public:
623 ColumnsRowEnumeration( const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< excel::XRange >& xRange, sal_Int32 nElems ) : mxContext( xContext ), mxRange( xRange ), mMaxElems( nElems ), mCurElem( 0 )
627 virtual sal_Bool SAL_CALL hasMoreElements() throw (::uno::RuntimeException, std::exception) SAL_OVERRIDE { return mCurElem < mMaxElems; }
629 virtual uno::Any SAL_CALL nextElement() throw (container::NoSuchElementException, lang::WrappedTargetException, uno::RuntimeException, std::exception) SAL_OVERRIDE
631 if ( !hasMoreElements() )
632 throw container::NoSuchElementException();
633 sal_Int32 vbaIndex = 1 + mCurElem++;
634 return uno::makeAny( mxRange->Item( uno::makeAny( vbaIndex ), uno::Any() ) );
638 class CellsEnumeration : public CellsEnumeration_BASE
640 uno::WeakReference< XHelperInterface > mxParent;
641 uno::Reference< uno::XComponentContext > mxContext;
642 uno::Reference< XCollection > m_xAreas;
643 vCellPos m_CellPositions;
644 vCellPos::const_iterator m_it;
646 uno::Reference< table::XCellRange > getArea( sal_Int32 nVBAIndex ) throw ( uno::RuntimeException )
648 if ( nVBAIndex < 1 || nVBAIndex > m_xAreas->getCount() )
649 throw uno::RuntimeException();
650 uno::Reference< excel::XRange > xRange( m_xAreas->Item( uno::makeAny(nVBAIndex), uno::Any() ), uno::UNO_QUERY_THROW );
651 uno::Reference< table::XCellRange > xCellRange( ScVbaRange::getCellRange( xRange ), uno::UNO_QUERY_THROW );
652 return xCellRange;
655 void populateArea( sal_Int32 nVBAIndex )
657 uno::Reference< table::XCellRange > xRange = getArea( nVBAIndex );
658 uno::Reference< table::XColumnRowRange > xColumnRowRange(xRange, uno::UNO_QUERY_THROW );
659 sal_Int32 nRowCount = xColumnRowRange->getRows()->getCount();
660 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount();
661 for ( sal_Int32 i=0; i<nRowCount; ++i )
663 for ( sal_Int32 j=0; j<nColCount; ++j )
664 m_CellPositions.push_back( CellPos( i,j,nVBAIndex ) );
667 public:
668 CellsEnumeration( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< XCollection >& xAreas ): mxParent( xParent ), mxContext( xContext ), m_xAreas( xAreas )
670 sal_Int32 nItems = m_xAreas->getCount();
671 for ( sal_Int32 index=1; index <= nItems; ++index )
673 populateArea( index );
675 m_it = m_CellPositions.begin();
677 virtual sal_Bool SAL_CALL hasMoreElements() throw (::uno::RuntimeException, std::exception) SAL_OVERRIDE { return m_it != m_CellPositions.end(); }
679 virtual uno::Any SAL_CALL nextElement() throw (container::NoSuchElementException, lang::WrappedTargetException, uno::RuntimeException, std::exception) SAL_OVERRIDE
681 if ( !hasMoreElements() )
682 throw container::NoSuchElementException();
683 CellPos aPos = *(m_it)++;
685 uno::Reference< table::XCellRange > xRangeArea = getArea( aPos.m_nArea );
686 uno::Reference< table::XCellRange > xCellRange( xRangeArea->getCellByPosition( aPos.m_nCol, aPos.m_nRow ), uno::UNO_QUERY_THROW );
687 return uno::makeAny( uno::Reference< excel::XRange >( new ScVbaRange( mxParent, mxContext, xCellRange ) ) );
692 static const char ISVISIBLE[] = "IsVisible";
693 static const char POSITION[] = "Position";
694 static const char EQUALS[] = "=";
695 static const char NOTEQUALS[] = "<>";
696 static const char GREATERTHAN[] = ">";
697 static const char GREATERTHANEQUALS[] = ">=";
698 static const char LESSTHAN[] = "<";
699 static const char LESSTHANEQUALS[] = "<=";
700 static const char CONTS_HEADER[] = "ContainsHeader";
701 static const char INSERTPAGEBREAKS[] = "InsertPageBreaks";
702 static const char STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY[] = "The command you chose cannot be performed with multiple selections.\nSelect a single range and click the command again";
703 static const char STR_ERRORMESSAGE_NOCELLSWEREFOUND[] = "No cells were found";
704 static const char CELLSTYLE[] = "CellStyle";
706 class CellValueSetter : public ValueSetter
708 protected:
709 uno::Any maValue;
710 public:
711 CellValueSetter( const uno::Any& aValue );
712 virtual bool processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE;
713 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE;
717 CellValueSetter::CellValueSetter( const uno::Any& aValue ): maValue( aValue ) {}
719 void
720 CellValueSetter::visitNode( sal_Int32 /*i*/, sal_Int32 /*j*/, const uno::Reference< table::XCell >& xCell )
722 processValue( maValue, xCell );
725 bool
726 CellValueSetter::processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell )
729 bool isExtracted = false;
730 switch ( aValue.getValueTypeClass() )
732 case uno::TypeClass_BOOLEAN:
734 bool bState = false;
735 if ( aValue >>= bState )
737 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW );
738 if ( bState )
739 xCell->setValue( (double) 1 );
740 else
741 xCell->setValue( (double) 0 );
742 NumFormatHelper cellNumFormat( xRange );
743 cellNumFormat.setNumberFormat( util::NumberFormat::LOGICAL );
745 break;
747 case uno::TypeClass_STRING:
749 OUString aString;
750 if ( aValue >>= aString )
752 // The required behavior for a string value is:
753 // 1. If the first character is a single quote, use the rest as a string cell, regardless of the cell's number format.
754 // 2. Otherwise, if the cell's number format is "text", use the string value as a string cell.
755 // 3. Otherwise, parse the string value in English locale, and apply a corresponding number format with the cell's locale
756 // if the cell's number format was "General".
757 // Case 1 is handled here, the rest in ScCellObj::InputEnglishString
759 if ( aString.toChar() == '\'' ) // case 1 - handle with XTextRange
761 OUString aRemainder( aString.copy(1) ); // strip the quote
762 uno::Reference< text::XTextRange > xTextRange( xCell, uno::UNO_QUERY_THROW );
763 xTextRange->setString( aRemainder );
765 else
767 // call implementation method InputEnglishString
768 ScCellObj* pCellObj = dynamic_cast< ScCellObj* >( xCell.get() );
769 if ( pCellObj )
770 pCellObj->InputEnglishString( aString );
773 else
774 isExtracted = false;
775 break;
777 default:
779 double nDouble = 0.0;
780 if ( aValue >>= nDouble )
782 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW );
783 NumFormatHelper cellFormat( xRange );
784 // If we are setting a number and the cell types was logical
785 // then we need to reset the logical format. ( see case uno::TypeClass_BOOLEAN:
786 // handling above )
787 if ( cellFormat.isBooleanType() )
788 cellFormat.setNumberFormat("General");
789 xCell->setValue( nDouble );
791 else
792 isExtracted = false;
793 break;
796 return isExtracted;
800 class CellValueGetter : public ValueGetter
802 protected:
803 uno::Any maValue;
804 public:
805 CellValueGetter() {}
806 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE;
807 virtual void processValue( sal_Int32 x, sal_Int32 y, const uno::Any& aValue ) SAL_OVERRIDE;
808 const uno::Any& getValue() const SAL_OVERRIDE { return maValue; }
812 void
813 CellValueGetter::processValue( sal_Int32 /*x*/, sal_Int32 /*y*/, const uno::Any& aValue )
815 maValue = aValue;
817 void CellValueGetter::visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell )
819 uno::Any aValue;
820 table::CellContentType eType = xCell->getType();
821 if( eType == table::CellContentType_VALUE || eType == table::CellContentType_FORMULA )
823 if ( eType == table::CellContentType_FORMULA )
826 OUString sFormula = xCell->getFormula();
827 if ( sFormula == "=TRUE()" )
828 aValue <<= sal_True;
829 else if ( sFormula == "=FALSE()" )
830 aValue <<= false;
831 else
833 uno::Reference< beans::XPropertySet > xProp( xCell, uno::UNO_QUERY_THROW );
835 table::CellContentType eFormulaType = table::CellContentType_VALUE;
836 // some formulas give textual results
837 xProp->getPropertyValue( "FormulaResultType" ) >>= eFormulaType;
839 if ( eFormulaType == table::CellContentType_TEXT )
841 uno::Reference< text::XTextRange > xTextRange(xCell, ::uno::UNO_QUERY_THROW);
842 aValue <<= xTextRange->getString();
844 else
845 aValue <<= xCell->getValue();
848 else
850 uno::Reference< table::XCellRange > xRange( xCell, uno::UNO_QUERY_THROW );
851 NumFormatHelper cellFormat( xRange );
852 if ( cellFormat.isBooleanType() )
853 aValue = uno::makeAny( ( xCell->getValue() != 0.0 ) );
854 else if ( cellFormat.isDateType() )
855 aValue = uno::makeAny( bridge::oleautomation::Date( xCell->getValue() ) );
856 else
857 aValue <<= xCell->getValue();
860 if( eType == table::CellContentType_TEXT )
862 uno::Reference< text::XTextRange > xTextRange(xCell, ::uno::UNO_QUERY_THROW);
863 aValue <<= xTextRange->getString();
865 processValue( x,y,aValue );
868 class CellFormulaValueSetter : public CellValueSetter
870 private:
871 ScDocument* m_pDoc;
872 formula::FormulaGrammar::Grammar m_eGrammar;
873 public:
874 CellFormulaValueSetter( const uno::Any& aValue, ScDocument* pDoc, formula::FormulaGrammar::Grammar eGram ):CellValueSetter( aValue ), m_pDoc( pDoc ), m_eGrammar( eGram ){}
875 protected:
876 bool processValue( const uno::Any& aValue, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE
878 OUString sFormula;
879 double aDblValue = 0.0;
880 if ( aValue >>= sFormula )
882 // convert to GRAM_PODF_A1 style grammar because XCell::setFormula
883 // always compile it in that grammar. Perhaps
884 // css.sheet.FormulaParser should be used in future to directly
885 // pass formula tokens when that API stabilizes.
886 if ( m_eGrammar != formula::FormulaGrammar::GRAM_PODF_A1 && ( sFormula.trim().startsWith("=") ) )
888 uno::Reference< uno::XInterface > xIf( xCell, uno::UNO_QUERY_THROW );
889 ScCellRangesBase* pUnoRangesBase = dynamic_cast< ScCellRangesBase* >( xIf.get() );
890 if ( pUnoRangesBase )
892 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
893 ScCompiler aCompiler( m_pDoc, aCellRanges.front()->aStart );
894 aCompiler.SetGrammar(m_eGrammar);
895 // compile the string in the format passed in
896 boost::scoped_ptr<ScTokenArray> pArray(aCompiler.CompileString(sFormula));
897 // set desired convention to that of the document
898 aCompiler.SetGrammar( formula::FormulaGrammar::GRAM_PODF_A1 );
899 OUString sConverted;
900 aCompiler.CreateStringFromTokenArray(sConverted);
901 sFormula = EQUALS + sConverted;
905 xCell->setFormula( sFormula );
906 return true;
908 else if ( aValue >>= aDblValue )
910 xCell->setValue( aDblValue );
911 return true;
913 return false;
918 class CellFormulaValueGetter : public CellValueGetter
920 private:
921 ScDocument* m_pDoc;
922 formula::FormulaGrammar::Grammar m_eGrammar;
923 public:
924 CellFormulaValueGetter(ScDocument* pDoc, formula::FormulaGrammar::Grammar eGram ) : CellValueGetter( ), m_pDoc( pDoc ), m_eGrammar( eGram ) {}
925 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE
927 uno::Any aValue;
928 aValue <<= xCell->getFormula();
929 OUString sVal;
930 aValue >>= sVal;
931 uno::Reference< uno::XInterface > xIf( xCell, uno::UNO_QUERY_THROW );
932 ScCellRangesBase* pUnoRangesBase = dynamic_cast< ScCellRangesBase* >( xIf.get() );
933 if ( ( xCell->getType() == table::CellContentType_FORMULA ) &&
934 pUnoRangesBase )
936 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
937 ScCompiler aCompiler( m_pDoc, aCellRanges.front()->aStart );
938 aCompiler.SetGrammar(formula::FormulaGrammar::GRAM_DEFAULT);
939 boost::scoped_ptr<ScTokenArray> pArray(aCompiler.CompileString(sVal));
940 // set desired convention
941 aCompiler.SetGrammar( m_eGrammar );
942 OUString sConverted;
943 aCompiler.CreateStringFromTokenArray(sConverted);
944 sVal = EQUALS + sConverted;
945 aValue <<= sVal;
948 processValue( x,y,aValue );
953 class Dim2ArrayValueGetter : public ArrayVisitor
955 protected:
956 uno::Any maValue;
957 ValueGetter& mValueGetter;
958 void processValue( sal_Int32 x, sal_Int32 y, const uno::Any& aValue )
960 uno::Sequence< uno::Sequence< uno::Any > >& aMatrix = *const_cast<css::uno::Sequence<css::uno::Sequence<css::uno::Any>> *>(static_cast<uno::Sequence< uno::Sequence< uno::Any > > const *>(maValue.getValue()));
961 aMatrix[x][y] = aValue;
964 public:
965 Dim2ArrayValueGetter(sal_Int32 nRowCount, sal_Int32 nColCount, ValueGetter& rValueGetter ): mValueGetter(rValueGetter)
967 uno::Sequence< uno::Sequence< uno::Any > > aMatrix;
968 aMatrix.realloc( nRowCount );
969 for ( sal_Int32 index = 0; index < nRowCount; ++index )
970 aMatrix[index].realloc( nColCount );
971 maValue <<= aMatrix;
973 void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE
976 mValueGetter.visitNode( x, y, xCell );
977 processValue( x, y, mValueGetter.getValue() );
979 const uno::Any& getValue() const { return maValue; }
983 static const char sNA[] = "#N/A";
985 class Dim1ArrayValueSetter : public ArrayVisitor
987 uno::Sequence< uno::Any > aMatrix;
988 sal_Int32 nColCount;
989 ValueSetter& mCellValueSetter;
990 public:
991 Dim1ArrayValueSetter( const uno::Any& aValue, ValueSetter& rCellValueSetter ):mCellValueSetter( rCellValueSetter )
993 aValue >>= aMatrix;
994 nColCount = aMatrix.getLength();
996 virtual void visitNode( sal_Int32 /*x*/, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE
998 if ( y < nColCount )
999 mCellValueSetter.processValue( aMatrix[ y ], xCell );
1000 else
1001 mCellValueSetter.processValue( uno::makeAny( OUString(sNA) ), xCell );
1005 class Dim2ArrayValueSetter : public ArrayVisitor
1007 uno::Sequence< uno::Sequence< uno::Any > > aMatrix;
1008 ValueSetter& mCellValueSetter;
1009 sal_Int32 nRowCount;
1010 sal_Int32 nColCount;
1011 public:
1012 Dim2ArrayValueSetter( const uno::Any& aValue, ValueSetter& rCellValueSetter ) : mCellValueSetter( rCellValueSetter )
1014 aValue >>= aMatrix;
1015 nRowCount = aMatrix.getLength();
1016 nColCount = aMatrix[0].getLength();
1019 virtual void visitNode( sal_Int32 x, sal_Int32 y, const uno::Reference< table::XCell >& xCell ) SAL_OVERRIDE
1021 if ( x < nRowCount && y < nColCount )
1022 mCellValueSetter.processValue( aMatrix[ x ][ y ], xCell );
1023 else
1024 mCellValueSetter.processValue( uno::makeAny( OUString(sNA) ), xCell );
1029 class RangeProcessor
1031 public:
1032 virtual void process( const uno::Reference< excel::XRange >& xRange ) = 0;
1034 protected:
1035 ~RangeProcessor() {}
1038 class RangeValueProcessor : public RangeProcessor
1040 const uno::Any& m_aVal;
1041 public:
1042 RangeValueProcessor( const uno::Any& rVal ):m_aVal( rVal ) {}
1043 virtual ~RangeValueProcessor() {}
1044 virtual void process( const uno::Reference< excel::XRange >& xRange ) SAL_OVERRIDE
1046 xRange->setValue( m_aVal );
1050 class RangeFormulaProcessor : public RangeProcessor
1052 const uno::Any& m_aVal;
1053 public:
1054 RangeFormulaProcessor( const uno::Any& rVal ):m_aVal( rVal ) {}
1055 virtual ~RangeFormulaProcessor() {}
1056 virtual void process( const uno::Reference< excel::XRange >& xRange ) SAL_OVERRIDE
1058 xRange->setFormula( m_aVal );
1062 class RangeCountProcessor : public RangeProcessor
1064 sal_Int32 nCount;
1065 public:
1066 RangeCountProcessor():nCount(0){}
1067 virtual ~RangeCountProcessor() {}
1068 virtual void process( const uno::Reference< excel::XRange >& xRange ) SAL_OVERRIDE
1070 nCount = nCount + xRange->getCount();
1072 sal_Int32 value() { return nCount; }
1074 class AreasVisitor
1076 private:
1077 uno::Reference< XCollection > m_Areas;
1078 public:
1079 AreasVisitor( const uno::Reference< XCollection >& rAreas ):m_Areas( rAreas ){}
1081 void visit( RangeProcessor& processor )
1083 if ( m_Areas.is() )
1085 sal_Int32 nItems = m_Areas->getCount();
1086 for ( sal_Int32 index=1; index <= nItems; ++index )
1088 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
1089 processor.process( xRange );
1095 class RangeHelper
1097 uno::Reference< table::XCellRange > m_xCellRange;
1099 public:
1100 RangeHelper( const uno::Reference< table::XCellRange >& xCellRange ) throw (uno::RuntimeException) : m_xCellRange( xCellRange )
1102 if ( !m_xCellRange.is() )
1103 throw uno::RuntimeException();
1105 RangeHelper( const uno::Any& rCellRange ) throw (uno::RuntimeException)
1107 m_xCellRange.set(rCellRange, uno::UNO_QUERY_THROW);
1109 uno::Reference< sheet::XSheetCellRange > getSheetCellRange() throw (uno::RuntimeException)
1111 return uno::Reference< sheet::XSheetCellRange >(m_xCellRange, uno::UNO_QUERY_THROW);
1113 uno::Reference< sheet::XSpreadsheet > getSpreadSheet() throw (uno::RuntimeException)
1115 return getSheetCellRange()->getSpreadsheet();
1118 uno::Reference< table::XCellRange > getCellRangeFromSheet() throw (uno::RuntimeException)
1120 return uno::Reference< table::XCellRange >(getSpreadSheet(), uno::UNO_QUERY_THROW );
1123 uno::Reference< sheet::XCellRangeAddressable > getCellRangeAddressable() throw (uno::RuntimeException)
1125 return uno::Reference< sheet::XCellRangeAddressable >(m_xCellRange, ::uno::UNO_QUERY_THROW);
1129 uno::Reference< sheet::XSheetCellCursor > getSheetCellCursor() throw ( uno::RuntimeException )
1131 return uno::Reference< sheet::XSheetCellCursor >( getSpreadSheet()->createCursorByRange( getSheetCellRange() ), uno::UNO_QUERY_THROW );
1134 static uno::Reference< excel::XRange > createRangeFromRange( const uno::Reference< XHelperInterface >& xParent, const uno::Reference<uno::XComponentContext >& xContext,
1135 const uno::Reference< table::XCellRange >& xRange, const uno::Reference< sheet::XCellRangeAddressable >& xCellRangeAddressable,
1136 sal_Int32 nStartColOffset = 0, sal_Int32 nStartRowOffset = 0, sal_Int32 nEndColOffset = 0, sal_Int32 nEndRowOffset = 0 )
1138 return uno::Reference< excel::XRange >( new ScVbaRange( xParent, xContext,
1139 xRange->getCellRangeByPosition(
1140 xCellRangeAddressable->getRangeAddress().StartColumn + nStartColOffset,
1141 xCellRangeAddressable->getRangeAddress().StartRow + nStartRowOffset,
1142 xCellRangeAddressable->getRangeAddress().EndColumn + nEndColOffset,
1143 xCellRangeAddressable->getRangeAddress().EndRow + nEndRowOffset ) ) );
1148 bool
1149 ScVbaRange::getCellRangesForAddress( sal_uInt16& rResFlags, const OUString& sAddress, ScDocShell* pDocSh, ScRangeList& rCellRanges, formula::FormulaGrammar::AddressConvention& eConv, char cDelimiter )
1152 if ( pDocSh )
1154 ScDocument& rDoc = pDocSh->GetDocument();
1155 sal_uInt16 nMask = SCA_VALID;
1156 rResFlags = rCellRanges.Parse( sAddress, &rDoc, nMask, eConv, 0, cDelimiter );
1157 if ( rResFlags & SCA_VALID )
1159 return true;
1162 return false;
1165 bool getScRangeListForAddress( const OUString& sName, ScDocShell* pDocSh, ScRange& refRange, ScRangeList& aCellRanges, formula::FormulaGrammar::AddressConvention aConv ) throw ( uno::RuntimeException )
1167 // see if there is a match with a named range
1168 uno::Reference< beans::XPropertySet > xProps( pDocSh->GetModel(), uno::UNO_QUERY_THROW );
1169 uno::Reference< container::XNameAccess > xNameAccess( xProps->getPropertyValue( "NamedRanges" ), uno::UNO_QUERY_THROW );
1170 // Strangly enough you can have Range( "namedRange1, namedRange2, etc," )
1171 // loop around each ',' separated name
1172 std::vector< OUString > vNames;
1173 sal_Int32 nIndex = 0;
1176 OUString aToken = sName.getToken( 0, ',', nIndex );
1177 vNames.push_back( aToken );
1178 } while ( nIndex >= 0 );
1180 if ( vNames.empty() )
1181 vNames.push_back( sName );
1183 std::vector< OUString >::iterator it = vNames.begin();
1184 std::vector< OUString >::iterator it_end = vNames.end();
1185 for ( ; it != it_end; ++it )
1188 formula::FormulaGrammar::AddressConvention eConv = aConv;
1189 // spaces are illegal ( but the user of course can enter them )
1190 OUString sAddress = (*it).trim();
1191 // if a local name ( on the active sheet ) exists this will
1192 // take precedence over a global with the same name
1193 if ( !xNameAccess->hasByName( sAddress ) )
1195 // try a local name
1196 ScDocument& rDoc = pDocSh->GetDocument();
1197 SCTAB nCurTab = ScDocShell::GetCurTab();
1198 ScRangeName* pRangeName = rDoc.GetRangeName(nCurTab);
1199 if (pRangeName)
1201 bool bLocalName = pRangeName->findByUpperName(ScGlobal::pCharClass->uppercase(sAddress)) != NULL;
1202 // TODO: Handle local names correctly.
1203 (void)bLocalName;
1206 char aChar = 0;
1207 if ( xNameAccess->hasByName( sAddress ) )
1209 uno::Reference< sheet::XNamedRange > xNamed( xNameAccess->getByName( sAddress ), uno::UNO_QUERY_THROW );
1210 sAddress = xNamed->getContent();
1211 // As the address comes from OOO, the addressing
1212 // style is may not be XL_A1
1213 eConv = pDocSh->GetDocument().GetAddressConvention();
1214 aChar = ';';
1217 sal_uInt16 nFlags = 0;
1218 if ( !ScVbaRange::getCellRangesForAddress( nFlags, sAddress, pDocSh, aCellRanges, eConv, aChar ) )
1219 return false;
1221 bool bTabFromReferrer = !( nFlags & SCA_TAB_3D );
1223 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i )
1225 ScRange* pRange = aCellRanges[ i ];
1226 pRange->aStart.SetCol( refRange.aStart.Col() + pRange->aStart.Col() );
1227 pRange->aStart.SetRow( refRange.aStart.Row() + pRange->aStart.Row() );
1228 pRange->aStart.SetTab( bTabFromReferrer ? refRange.aStart.Tab() : pRange->aStart.Tab() );
1229 pRange->aEnd.SetCol( refRange.aStart.Col() + pRange->aEnd.Col() );
1230 pRange->aEnd.SetRow( refRange.aStart.Row() + pRange->aEnd.Row() );
1231 pRange->aEnd.SetTab( bTabFromReferrer ? refRange.aEnd.Tab() : pRange->aEnd.Tab() );
1234 return true;
1237 ScVbaRange*
1238 getRangeForName( const uno::Reference< uno::XComponentContext >& xContext, const OUString& sName, ScDocShell* pDocSh, table::CellRangeAddress& pAddr, formula::FormulaGrammar::AddressConvention eConv = formula::FormulaGrammar::CONV_XL_A1 ) throw ( uno::RuntimeException )
1240 ScRangeList aCellRanges;
1241 ScRange refRange;
1242 ScUnoConversion::FillScRange( refRange, pAddr );
1243 if ( !getScRangeListForAddress ( sName, pDocSh, refRange, aCellRanges, eConv ) )
1244 throw uno::RuntimeException();
1245 // Single range
1246 if ( aCellRanges.size() == 1 )
1248 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pDocSh, *aCellRanges.front() ) );
1249 uno::Reference< XHelperInterface > xFixThisParent = excel::getUnoSheetModuleObj( xRange );
1250 return new ScVbaRange( xFixThisParent, xContext, xRange );
1252 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pDocSh, aCellRanges ) );
1254 uno::Reference< XHelperInterface > xFixThisParent = excel::getUnoSheetModuleObj( xRanges );
1255 return new ScVbaRange( xFixThisParent, xContext, xRanges );
1258 namespace {
1260 template< typename RangeType >
1261 inline table::CellRangeAddress lclGetRangeAddress( const uno::Reference< RangeType >& rxCellRange ) throw (uno::RuntimeException)
1263 return uno::Reference< sheet::XCellRangeAddressable >( rxCellRange, uno::UNO_QUERY_THROW )->getRangeAddress();
1266 void lclClearRange( const uno::Reference< table::XCellRange >& rxCellRange ) throw (uno::RuntimeException)
1268 using namespace ::com::sun::star::sheet::CellFlags;
1269 sal_Int32 nFlags = VALUE | DATETIME | STRING | ANNOTATION | FORMULA | HARDATTR | STYLES | EDITATTR | FORMATTED;
1270 uno::Reference< sheet::XSheetOperation > xSheetOperation( rxCellRange, uno::UNO_QUERY_THROW );
1271 xSheetOperation->clearContents( nFlags );
1274 uno::Reference< sheet::XSheetCellRange > lclExpandToMerged( const uno::Reference< table::XCellRange >& rxCellRange, bool bRecursive ) throw (uno::RuntimeException)
1276 uno::Reference< sheet::XSheetCellRange > xNewCellRange( rxCellRange, uno::UNO_QUERY_THROW );
1277 uno::Reference< sheet::XSpreadsheet > xSheet( xNewCellRange->getSpreadsheet(), uno::UNO_SET_THROW );
1278 table::CellRangeAddress aNewAddress = lclGetRangeAddress( xNewCellRange );
1279 table::CellRangeAddress aOldAddress;
1280 // expand as long as there are new merged ranges included
1283 aOldAddress = aNewAddress;
1284 uno::Reference< sheet::XSheetCellCursor > xCursor( xSheet->createCursorByRange( xNewCellRange ), uno::UNO_SET_THROW );
1285 xCursor->collapseToMergedArea();
1286 xNewCellRange.set( xCursor, uno::UNO_QUERY_THROW );
1287 aNewAddress = lclGetRangeAddress( xNewCellRange );
1289 while( bRecursive && (aOldAddress != aNewAddress) );
1290 return xNewCellRange;
1293 uno::Reference< sheet::XSheetCellRangeContainer > lclExpandToMerged( const uno::Reference< sheet::XSheetCellRangeContainer >& rxCellRanges, bool bRecursive ) throw (uno::RuntimeException)
1295 if( !rxCellRanges.is() )
1296 throw uno::RuntimeException("Missing cell ranges object" );
1297 sal_Int32 nCount = rxCellRanges->getCount();
1298 if( nCount < 1 )
1299 throw uno::RuntimeException("Missing cell ranges object" );
1301 ScRangeList aScRanges;
1302 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex )
1304 uno::Reference< table::XCellRange > xRange( rxCellRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW );
1305 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( lclExpandToMerged( xRange, bRecursive ) );
1306 ScRange aScRange;
1307 ScUnoConversion::FillScRange( aScRange, aRangeAddr );
1308 aScRanges.Append( aScRange );
1310 return new ScCellRangesObj( getDocShellFromRanges( rxCellRanges ), aScRanges );
1313 void lclExpandAndMerge( const uno::Reference< table::XCellRange >& rxCellRange, bool bMerge ) throw (uno::RuntimeException)
1315 uno::Reference< util::XMergeable > xMerge( lclExpandToMerged( rxCellRange, true ), uno::UNO_QUERY_THROW );
1316 // Calc cannot merge over merged ranges, always unmerge first
1317 xMerge->merge( false );
1318 if( bMerge )
1320 // clear all contents of the covered cells (not the top-left cell)
1321 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxCellRange );
1322 sal_Int32 nLastColIdx = aRangeAddr.EndColumn - aRangeAddr.StartColumn;
1323 sal_Int32 nLastRowIdx = aRangeAddr.EndRow - aRangeAddr.StartRow;
1324 // clear cells of top row, right of top-left cell
1325 if( nLastColIdx > 0 )
1326 lclClearRange( rxCellRange->getCellRangeByPosition( 1, 0, nLastColIdx, 0 ) );
1327 // clear all rows below top row
1328 if( nLastRowIdx > 0 )
1329 lclClearRange( rxCellRange->getCellRangeByPosition( 0, 1, nLastColIdx, nLastRowIdx ) );
1330 // merge the range
1331 xMerge->merge( sal_True );
1335 util::TriState lclGetMergedState( const uno::Reference< table::XCellRange >& rxCellRange ) throw (uno::RuntimeException)
1337 /* 1) Check if range is completely inside one single merged range. To do
1338 this, try to extend from top-left cell only (not from entire range).
1339 This will exclude cases where this range consists of several merged
1340 ranges (or parts of them). */
1341 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxCellRange );
1342 uno::Reference< table::XCellRange > xTopLeft( rxCellRange->getCellRangeByPosition( 0, 0, 0, 0 ), uno::UNO_SET_THROW );
1343 uno::Reference< sheet::XSheetCellRange > xExpanded( lclExpandToMerged( xTopLeft, false ), uno::UNO_SET_THROW );
1344 table::CellRangeAddress aExpAddr = lclGetRangeAddress( xExpanded );
1345 // check that expanded range has more than one cell (really merged)
1346 if( ((aExpAddr.StartColumn < aExpAddr.EndColumn) || (aExpAddr.StartRow < aExpAddr.EndRow)) && ScUnoConversion::Contains( aExpAddr, aRangeAddr ) )
1347 return util::TriState_YES;
1349 /* 2) Check if this range contains any merged cells (completely or
1350 partly). This seems to be hardly possible via API, as
1351 XMergeable::getIsMerged() returns only true, if the top-left cell of a
1352 merged range is part of this range, so cases where just the lower part
1353 of a merged range is part of this range are not covered. */
1354 ScRange aScRange;
1355 ScUnoConversion::FillScRange( aScRange, aRangeAddr );
1356 bool bHasMerged = getDocumentFromRange( rxCellRange ).HasAttrib( aScRange, HASATTR_MERGED | HASATTR_OVERLAPPED );
1357 return bHasMerged ? util::TriState_INDETERMINATE : util::TriState_NO;
1360 } // namespace
1362 css::uno::Reference< excel::XRange >
1363 ScVbaRange::getRangeObjectForName(
1364 const uno::Reference< uno::XComponentContext >& xContext, const OUString& sRangeName,
1365 ScDocShell* pDocSh, formula::FormulaGrammar::AddressConvention eConv ) throw ( uno::RuntimeException )
1367 table::CellRangeAddress refAddr;
1368 return getRangeForName( xContext, sRangeName, pDocSh, refAddr, eConv );
1371 table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh, formula::FormulaGrammar::AddressConvention aConv = formula::FormulaGrammar::CONV_XL_A1) throw ( uno::RuntimeException )
1373 uno::Reference< table::XCellRange > xRangeParam;
1374 switch ( aParam.getValueTypeClass() )
1376 case uno::TypeClass_STRING:
1378 OUString rString;
1379 aParam >>= rString;
1380 ScRangeList aCellRanges;
1381 ScRange refRange;
1382 if ( getScRangeListForAddress ( rString, pDocSh, refRange, aCellRanges, aConv ) )
1384 if ( aCellRanges.size() == 1 )
1386 table::CellRangeAddress aRangeAddress;
1387 ScUnoConversion::FillApiRange( aRangeAddress, *aCellRanges.front() );
1388 return aRangeAddress;
1392 break;
1394 case uno::TypeClass_INTERFACE:
1396 uno::Reference< excel::XRange > xRange;
1397 aParam >>= xRange;
1398 if ( xRange.is() )
1399 xRange->getCellRange() >>= xRangeParam;
1401 break;
1403 default:
1404 throw uno::RuntimeException("Can't extact CellRangeAddress from type" );
1406 return lclGetRangeAddress( xRangeParam );
1409 static uno::Reference< XCollection >
1410 lcl_setupBorders( const uno::Reference< excel::XRange >& xParentRange, const uno::Reference<uno::XComponentContext>& xContext, const uno::Reference< table::XCellRange >& xRange ) throw( uno::RuntimeException )
1412 uno::Reference< XHelperInterface > xParent( xParentRange, uno::UNO_QUERY_THROW );
1413 ScDocument& rDoc = getDocumentFromRange(xRange);
1414 ScVbaPalette aPalette( rDoc.GetDocumentShell() );
1415 uno::Reference< XCollection > borders( new ScVbaBorders( xParent, xContext, xRange, aPalette ) );
1416 return borders;
1419 ScVbaRange::ScVbaRange( uno::Sequence< uno::Any> const & args,
1420 uno::Reference< uno::XComponentContext> const & xContext ) throw ( lang::IllegalArgumentException, uno::RuntimeException ) : ScVbaRange_BASE( getXSomethingFromArgs< XHelperInterface >( args, 0 ), xContext, getXSomethingFromArgs< beans::XPropertySet >( args, 1, false ), getModelFromXIf( getXSomethingFromArgs< uno::XInterface >( args, 1 ) ), true ), mbIsRows( false ), mbIsColumns( false )
1422 mxRange.set( mxPropertySet, uno::UNO_QUERY );
1423 mxRanges.set( mxPropertySet, uno::UNO_QUERY );
1424 uno::Reference< container::XIndexAccess > xIndex;
1425 if ( mxRange.is() )
1427 xIndex = new SingleRangeIndexAccess( mxParent, mxContext, mxRange );
1429 else if ( mxRanges.is() )
1431 xIndex.set( mxRanges, uno::UNO_QUERY_THROW );
1433 m_Areas = new ScVbaRangeAreas( mxParent, mxContext, xIndex, mbIsRows, mbIsColumns );
1436 ScVbaRange::ScVbaRange( const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< table::XCellRange >& xRange, bool bIsRows, bool bIsColumns ) throw( lang::IllegalArgumentException )
1437 : ScVbaRange_BASE( xParent, xContext, uno::Reference< beans::XPropertySet >( xRange, uno::UNO_QUERY_THROW ), getModelFromRange( xRange), true ), mxRange( xRange ),
1438 mbIsRows( bIsRows ),
1439 mbIsColumns( bIsColumns )
1441 if ( !xContext.is() )
1442 throw lang::IllegalArgumentException("context is not set ", uno::Reference< uno::XInterface >() , 1 );
1443 if ( !xRange.is() )
1444 throw lang::IllegalArgumentException("range is not set ", uno::Reference< uno::XInterface >() , 1 );
1446 uno::Reference< container::XIndexAccess > xIndex( new SingleRangeIndexAccess( mxParent, mxContext, xRange ) );
1447 m_Areas = new ScVbaRangeAreas( mxParent, mxContext, xIndex, mbIsRows, mbIsColumns );
1451 ScVbaRange::ScVbaRange(const uno::Reference< XHelperInterface >& xParent, const uno::Reference< uno::XComponentContext >& xContext, const uno::Reference< sheet::XSheetCellRangeContainer >& xRanges, bool bIsRows, bool bIsColumns)
1452 throw (lang::IllegalArgumentException, uno::RuntimeException)
1453 : 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 )
1456 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
1457 m_Areas = new ScVbaRangeAreas( xParent, mxContext, xIndex, mbIsRows, mbIsColumns );
1461 ScVbaRange::~ScVbaRange()
1465 uno::Reference< XCollection >& ScVbaRange::getBorders()
1467 if ( !m_Borders.is() )
1469 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
1470 m_Borders = lcl_setupBorders( this, mxContext, uno::Reference< table::XCellRange >( xRange->getCellRange(), uno::UNO_QUERY_THROW ) );
1472 return m_Borders;
1475 void
1476 ScVbaRange::visitArray( ArrayVisitor& visitor )
1478 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( mxRange );
1479 sal_Int32 nRowCount = aRangeAddr.EndRow - aRangeAddr.StartRow + 1;
1480 sal_Int32 nColCount = aRangeAddr.EndColumn - aRangeAddr.StartColumn + 1;
1481 for ( sal_Int32 i=0; i<nRowCount; ++i )
1483 for ( sal_Int32 j=0; j<nColCount; ++j )
1485 uno::Reference< table::XCell > xCell( mxRange->getCellByPosition( j, i ), uno::UNO_QUERY_THROW );
1487 visitor.visitNode( i, j, xCell );
1492 uno::Any
1493 ScVbaRange::getValue( ValueGetter& valueGetter) throw (uno::RuntimeException)
1495 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW );
1496 // single cell range
1497 if ( isSingleCellRange() )
1499 visitArray( valueGetter );
1500 return valueGetter.getValue();
1502 sal_Int32 nRowCount = xColumnRowRange->getRows()->getCount();
1503 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount();
1504 // multi cell range ( return array )
1505 Dim2ArrayValueGetter arrayGetter( nRowCount, nColCount, valueGetter );
1506 visitArray( arrayGetter );
1507 return uno::makeAny( script::ArrayWrapper( false, arrayGetter.getValue() ) );
1510 uno::Any SAL_CALL
1511 ScVbaRange::getValue() throw (uno::RuntimeException, std::exception)
1513 // #TODO code within the test below "if ( m_Areas.... " can be removed
1514 // Test is performed only because m_xRange is NOT set to be
1515 // the first range in m_Areas ( to force failure while
1516 // the implementations for each method are being updated )
1517 if ( m_Areas->getCount() > 1 )
1519 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1520 return xRange->getValue();
1523 CellValueGetter valueGetter;
1524 return getValue( valueGetter );
1528 void
1529 ScVbaRange::setValue( const uno::Any& aValue, ValueSetter& valueSetter, bool bFireEvent ) throw (uno::RuntimeException)
1531 uno::TypeClass aClass = aValue.getValueTypeClass();
1532 if ( aClass == uno::TypeClass_SEQUENCE )
1534 uno::Reference< script::XTypeConverter > xConverter = getTypeConverter( mxContext );
1535 uno::Any aConverted;
1538 // test for single dimension, could do
1539 // with a better test than this
1540 if ( aValue.getValueTypeName().indexOf('[') == aValue.getValueTypeName().lastIndexOf('[') )
1542 aConverted = xConverter->convertTo( aValue, cppu::UnoType<uno::Sequence< uno::Any >>::get() );
1543 Dim1ArrayValueSetter setter( aConverted, valueSetter );
1544 visitArray( setter );
1546 else
1548 aConverted = xConverter->convertTo( aValue, cppu::UnoType<uno::Sequence< uno::Sequence< uno::Any > >>::get() );
1549 Dim2ArrayValueSetter setter( aConverted, valueSetter );
1550 visitArray( setter );
1553 catch ( const uno::Exception& e )
1555 OSL_TRACE("Bahhh, caught exception %s",
1556 OUStringToOString( e.Message,
1557 RTL_TEXTENCODING_UTF8 ).getStr() );
1560 else
1562 visitArray( valueSetter );
1564 if( bFireEvent ) fireChangeEvent();
1567 void SAL_CALL
1568 ScVbaRange::setValue( const uno::Any &aValue ) throw (uno::RuntimeException, std::exception)
1570 // If this is a multiple selection apply setValue over all areas
1571 if ( m_Areas->getCount() > 1 )
1573 AreasVisitor aVisitor( m_Areas );
1574 RangeValueProcessor valueProcessor( aValue );
1575 aVisitor.visit( valueProcessor );
1576 return;
1578 CellValueSetter valueSetter( aValue );
1579 setValue( aValue, valueSetter, true );
1582 void SAL_CALL
1583 ScVbaRange::Clear() throw (uno::RuntimeException, std::exception)
1585 using namespace ::com::sun::star::sheet::CellFlags;
1586 sal_Int32 nFlags = VALUE | DATETIME | STRING | FORMULA | HARDATTR | EDITATTR | FORMATTED;
1587 ClearContents( nFlags, true );
1590 //helper ClearContent
1591 void
1592 ScVbaRange::ClearContents( sal_Int32 nFlags, bool bFireEvent ) throw (uno::RuntimeException)
1594 // #TODO code within the test below "if ( m_Areas.... " can be removed
1595 // Test is performed only because m_xRange is NOT set to be
1596 // the first range in m_Areas ( to force failure while
1597 // the implementations for each method are being updated )
1598 if ( m_Areas->getCount() > 1 )
1600 sal_Int32 nItems = m_Areas->getCount();
1601 for ( sal_Int32 index=1; index <= nItems; ++index )
1603 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
1604 ScVbaRange* pRange = getImplementation( xRange );
1605 if ( pRange )
1606 pRange->ClearContents( nFlags, false ); // do not fire for single ranges
1608 // fire change event for the entire range list
1609 if( bFireEvent ) fireChangeEvent();
1610 return;
1613 uno::Reference< sheet::XSheetOperation > xSheetOperation(mxRange, uno::UNO_QUERY_THROW);
1614 xSheetOperation->clearContents( nFlags );
1615 if( bFireEvent ) fireChangeEvent();
1618 void SAL_CALL
1619 ScVbaRange::ClearComments() throw (uno::RuntimeException, std::exception)
1621 ClearContents( sheet::CellFlags::ANNOTATION, false );
1624 void SAL_CALL
1625 ScVbaRange::ClearContents() throw (uno::RuntimeException, std::exception)
1627 using namespace ::com::sun::star::sheet::CellFlags;
1628 sal_Int32 nFlags = VALUE | DATETIME | STRING | FORMULA;
1629 ClearContents( nFlags, true );
1632 void SAL_CALL
1633 ScVbaRange::ClearFormats() throw (uno::RuntimeException, std::exception)
1635 // FIXME: need to check if we need to combine FORMATTED
1636 using namespace ::com::sun::star::sheet::CellFlags;
1637 sal_Int32 nFlags = HARDATTR | FORMATTED | EDITATTR;
1638 ClearContents( nFlags, false );
1641 void
1642 ScVbaRange::setFormulaValue( const uno::Any& rFormula, formula::FormulaGrammar::Grammar eGram, bool bFireEvent ) throw (uno::RuntimeException)
1644 // If this is a multiple selection apply setFormula over all areas
1645 if ( m_Areas->getCount() > 1 )
1647 AreasVisitor aVisitor( m_Areas );
1648 RangeFormulaProcessor valueProcessor( rFormula );
1649 aVisitor.visit( valueProcessor );
1650 return;
1652 CellFormulaValueSetter formulaValueSetter( rFormula, &getScDocument(), eGram );
1653 setValue( rFormula, formulaValueSetter, bFireEvent );
1656 uno::Any
1657 ScVbaRange::getFormulaValue( formula::FormulaGrammar::Grammar eGram ) throw (uno::RuntimeException)
1659 // #TODO code within the test below "if ( m_Areas.... " can be removed
1660 // Test is performed only because m_xRange is NOT set to be
1661 // the first range in m_Areas ( to force failure while
1662 // the implementations for each method are being updated )
1663 if ( m_Areas->getCount() > 1 )
1665 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1666 return xRange->getFormula();
1668 CellFormulaValueGetter valueGetter( &getScDocument(), eGram );
1669 return getValue( valueGetter );
1673 void
1674 ScVbaRange::setFormula(const uno::Any &rFormula ) throw (uno::RuntimeException, std::exception)
1676 // #FIXME converting "=$a$1" e.g. CONV_XL_A1 -> CONV_OOO // results in "=$a$1:a1", temporalily disable conversion
1677 setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1, true );
1680 uno::Any
1681 ScVbaRange::getFormulaR1C1() throw (::com::sun::star::uno::RuntimeException, std::exception)
1683 return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
1686 void
1687 ScVbaRange::setFormulaR1C1(const uno::Any& rFormula ) throw (uno::RuntimeException, std::exception)
1689 setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1, true );
1692 uno::Any
1693 ScVbaRange::getFormula() throw (::com::sun::star::uno::RuntimeException, std::exception)
1695 return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
1698 sal_Int32
1699 ScVbaRange::getCount() throw (uno::RuntimeException, std::exception)
1701 // If this is a multiple selection apply setValue over all areas
1702 if ( m_Areas->getCount() > 1 )
1704 AreasVisitor aVisitor( m_Areas );
1705 RangeCountProcessor valueProcessor;
1706 aVisitor.visit( valueProcessor );
1707 return valueProcessor.value();
1709 sal_Int32 rowCount = 0;
1710 sal_Int32 colCount = 0;
1711 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW );
1712 rowCount = xColumnRowRange->getRows()->getCount();
1713 colCount = xColumnRowRange->getColumns()->getCount();
1715 if( IsRows() )
1716 return rowCount;
1717 if( IsColumns() )
1718 return colCount;
1719 return rowCount * colCount;
1722 sal_Int32
1723 ScVbaRange::getRow() throw (uno::RuntimeException, std::exception)
1725 // #TODO code within the test below "if ( m_Areas.... " can be removed
1726 // Test is performed only because m_xRange is NOT set to be
1727 // the first range in m_Areas ( to force failure while
1728 // the implementations for each method are being updated )
1729 if ( m_Areas->getCount() > 1 )
1731 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1732 return xRange->getRow();
1734 uno::Reference< sheet::XCellAddressable > xCellAddressable(mxRange->getCellByPosition(0, 0), uno::UNO_QUERY_THROW );
1735 return xCellAddressable->getCellAddress().Row + 1; // Zero value indexing
1738 sal_Int32
1739 ScVbaRange::getColumn() throw (uno::RuntimeException, std::exception)
1741 // #TODO code within the test below "if ( m_Areas.... " can be removed
1742 // Test is performed only because m_xRange is NOT set to be
1743 // the first range in m_Areas ( to force failure while
1744 // the implementations for each method are being updated )
1745 if ( m_Areas->getCount() > 1 )
1747 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1748 return xRange->getColumn();
1750 uno::Reference< sheet::XCellAddressable > xCellAddressable(mxRange->getCellByPosition(0, 0), uno::UNO_QUERY_THROW );
1751 return xCellAddressable->getCellAddress().Column + 1; // Zero value indexing
1754 uno::Any
1755 ScVbaRange::HasFormula() throw (uno::RuntimeException, std::exception)
1757 if ( m_Areas->getCount() > 1 )
1759 sal_Int32 nItems = m_Areas->getCount();
1760 uno::Any aResult = aNULL();
1761 for ( sal_Int32 index=1; index <= nItems; ++index )
1763 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
1764 // if the HasFormula for any area is different to another
1765 // return null
1766 if ( index > 1 )
1767 if ( aResult != xRange->HasFormula() )
1768 return aNULL();
1769 aResult = xRange->HasFormula();
1770 if ( aNULL() == aResult )
1771 return aNULL();
1773 return aResult;
1775 uno::Reference< uno::XInterface > xIf( mxRange, uno::UNO_QUERY_THROW );
1776 ScCellRangesBase* pThisRanges = dynamic_cast< ScCellRangesBase * > ( xIf.get() );
1777 if ( pThisRanges )
1779 uno::Reference<uno::XInterface> xRanges( pThisRanges->queryFormulaCells( ( sheet::FormulaResult::ERROR | sheet::FormulaResult::VALUE | sheet::FormulaResult::STRING ) ), uno::UNO_QUERY_THROW );
1780 ScCellRangesBase* pFormulaRanges = dynamic_cast< ScCellRangesBase * > ( xRanges.get() );
1781 // check if there are no formula cell, return false
1782 if ( pFormulaRanges->GetRangeList().empty() )
1783 return uno::makeAny(sal_False);
1785 // chech if there are holes (where some cells are not formulas)
1786 // or returned range is not equal to this range
1787 if ( ( pFormulaRanges->GetRangeList().size() > 1 )
1788 || ( pFormulaRanges->GetRangeList().front()->aStart != pThisRanges->GetRangeList().front()->aStart )
1789 || ( pFormulaRanges->GetRangeList().front()->aEnd != pThisRanges->GetRangeList().front()->aEnd )
1791 return aNULL(); // should return aNULL;
1793 return uno::makeAny( sal_True );
1795 void
1796 ScVbaRange::fillSeries( sheet::FillDirection nFillDirection, sheet::FillMode nFillMode, sheet::FillDateMode nFillDateMode, double fStep, double fEndValue ) throw( uno::RuntimeException )
1798 if ( m_Areas->getCount() > 1 )
1800 // Multi-Area Range
1801 uno::Reference< XCollection > xCollection( m_Areas, uno::UNO_QUERY_THROW );
1802 for ( sal_Int32 index = 1; index <= xCollection->getCount(); ++index )
1804 uno::Reference< excel::XRange > xRange( xCollection->Item( uno::makeAny( index ), uno::Any() ), uno::UNO_QUERY_THROW );
1805 ScVbaRange* pThisRange = getImplementation( xRange );
1806 pThisRange->fillSeries( nFillDirection, nFillMode, nFillDateMode, fStep, fEndValue );
1809 return;
1812 uno::Reference< sheet::XCellSeries > xCellSeries(mxRange, uno::UNO_QUERY_THROW );
1813 xCellSeries->fillSeries( nFillDirection, nFillMode, nFillDateMode, fStep, fEndValue );
1814 fireChangeEvent();
1817 void
1818 ScVbaRange::FillLeft() throw (uno::RuntimeException, std::exception)
1820 fillSeries(sheet::FillDirection_TO_LEFT,
1821 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF);
1824 void
1825 ScVbaRange::FillRight() throw (uno::RuntimeException, std::exception)
1827 fillSeries(sheet::FillDirection_TO_RIGHT,
1828 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF);
1831 void
1832 ScVbaRange::FillUp() throw (uno::RuntimeException, std::exception)
1834 fillSeries(sheet::FillDirection_TO_TOP,
1835 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF);
1838 void
1839 ScVbaRange::FillDown() throw (uno::RuntimeException, std::exception)
1841 fillSeries(sheet::FillDirection_TO_BOTTOM,
1842 sheet::FillMode_SIMPLE, sheet::FillDateMode_FILL_DATE_DAY, 0, 0x7FFFFFFF);
1845 OUString
1846 ScVbaRange::getText() throw (uno::RuntimeException, std::exception)
1848 // #TODO code within the test below "if ( m_Areas.... " can be removed
1849 // Test is performed only because m_xRange is NOT set to be
1850 // the first range in m_Areas ( to force failure while
1851 // the implementations for each method are being updated )
1852 if ( m_Areas->getCount() > 1 )
1854 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1855 return xRange->getText();
1857 uno::Reference< text::XTextRange > xTextRange(mxRange->getCellByPosition(0,0), uno::UNO_QUERY_THROW );
1858 return xTextRange->getString();
1861 uno::Reference< excel::XRange >
1862 ScVbaRange::Offset( const ::uno::Any &nRowOff, const uno::Any &nColOff ) throw (uno::RuntimeException, std::exception)
1864 SCROW nRowOffset = 0;
1865 SCCOL nColOffset = 0;
1866 bool bIsRowOffset = ( nRowOff >>= nRowOffset );
1867 bool bIsColumnOffset = ( nColOff >>= nColOffset );
1868 ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
1870 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
1872 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i )
1874 ScRange* pRange = aCellRanges[ i ];
1875 if ( bIsColumnOffset )
1877 pRange->aStart.SetCol( pRange->aStart.Col() + nColOffset );
1878 pRange->aEnd.SetCol( pRange->aEnd.Col() + nColOffset );
1880 if ( bIsRowOffset )
1882 pRange->aStart.SetRow( pRange->aStart.Row() + nRowOffset );
1883 pRange->aEnd.SetRow( pRange->aEnd.Row() + nRowOffset );
1887 if ( aCellRanges.size() > 1 ) // Multi-Area
1889 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pUnoRangesBase->GetDocShell(), aCellRanges ) );
1890 return new ScVbaRange( mxParent, mxContext, xRanges );
1892 // normal range
1893 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), *aCellRanges.front() ) );
1894 return new ScVbaRange( mxParent, mxContext, xRange );
1897 uno::Reference< excel::XRange >
1898 ScVbaRange::CurrentRegion() throw (uno::RuntimeException, std::exception)
1900 // #TODO code within the test below "if ( m_Areas.... " can be removed
1901 // Test is performed only because m_xRange is NOT set to be
1902 // the first range in m_Areas ( to force failure while
1903 // the implementations for each method are being updated )
1904 if ( m_Areas->getCount() > 1 )
1906 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1907 return xRange->CurrentRegion();
1910 RangeHelper helper( mxRange );
1911 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor =
1912 helper.getSheetCellCursor();
1913 xSheetCellCursor->collapseToCurrentRegion();
1914 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW);
1915 return RangeHelper::createRangeFromRange( mxParent, mxContext, helper.getCellRangeFromSheet(), xCellRangeAddressable );
1918 uno::Reference< excel::XRange >
1919 ScVbaRange::CurrentArray() throw (uno::RuntimeException, std::exception)
1921 // #TODO code within the test below "if ( m_Areas.... " can be removed
1922 // Test is performed only because m_xRange is NOT set to be
1923 // the first range in m_Areas ( to force failure while
1924 // the implementations for each method are being updated )
1925 if ( m_Areas->getCount() > 1 )
1927 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1928 return xRange->CurrentArray();
1930 RangeHelper helper( mxRange );
1931 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor =
1932 helper.getSheetCellCursor();
1933 xSheetCellCursor->collapseToCurrentArray();
1934 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW);
1935 return RangeHelper::createRangeFromRange( mxParent, mxContext, helper.getCellRangeFromSheet(), xCellRangeAddressable );
1938 uno::Any
1939 ScVbaRange::getFormulaArray() throw (uno::RuntimeException, std::exception)
1941 // #TODO code within the test below "if ( m_Areas.... " can be removed
1942 // Test is performed only because m_xRange is NOT set to be
1943 // the first range in m_Areas ( to force failure while
1944 // the implementations for each method are being updated )
1945 if ( m_Areas->getCount() > 1 )
1947 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1948 return xRange->getFormulaArray();
1951 // return a formula if there is one or else an array
1952 // still not sure when the return as array code should run
1953 // ( I think it is if there is more than one formula ) at least
1954 // that is what the doc says ( but I am not even sure how to detect that )
1955 // for the moment any tests we have pass
1956 uno::Reference< sheet::XArrayFormulaRange> xFormulaArray( mxRange, uno::UNO_QUERY_THROW );
1957 if ( xFormulaArray.is() && !xFormulaArray->getArrayFormula().isEmpty() )
1958 return uno::makeAny( xFormulaArray->getArrayFormula() );
1960 uno::Reference< sheet::XCellRangeFormula> xCellRangeFormula( mxRange, uno::UNO_QUERY_THROW );
1961 uno::Reference< script::XTypeConverter > xConverter = getTypeConverter( mxContext );
1962 uno::Any aSingleValueOrMatrix;
1963 // When dealing with a single element ( embedded in the sequence of sequence ) unwrap and return
1964 // that value
1965 uno::Sequence< uno::Sequence<rtl::OUString> > aTmpSeq = xCellRangeFormula->getFormulaArray();
1966 if ( aTmpSeq.getLength() == 1 )
1968 if ( aTmpSeq[ 0 ].getLength() == 1 )
1969 aSingleValueOrMatrix <<= aTmpSeq[ 0 ][ 0 ];
1971 else
1972 aSingleValueOrMatrix = xConverter->convertTo( uno::makeAny( aTmpSeq ) , cppu::UnoType<uno::Sequence< uno::Sequence< uno::Any > >>::get() ) ;
1973 return aSingleValueOrMatrix;
1976 void
1977 ScVbaRange::setFormulaArray(const uno::Any& rFormula) throw (uno::RuntimeException, std::exception)
1979 // #TODO code within the test below "if ( m_Areas.... " can be removed
1980 // Test is performed only because m_xRange is NOT set to be
1981 // the first range in m_Areas ( to force failure while
1982 // the implementations for each method are being updated )
1983 if ( m_Areas->getCount() > 1 )
1985 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
1986 return xRange->setFormulaArray( rFormula );
1988 // #TODO need to distinguish between getFormula and getFormulaArray e.g. (R1C1)
1989 // but for the moment its just easier to treat them the same for setting
1990 // seems
1991 uno::Reference< lang::XMultiServiceFactory > xModelFactory( getUnoModel(), uno::UNO_QUERY_THROW );
1992 uno::Reference< sheet::XFormulaParser > xParser( xModelFactory->createInstance( "com.sun.star.sheet.FormulaParser" ), uno::UNO_QUERY_THROW );
1993 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY_THROW);
1995 table::CellRangeAddress aRangeAddress = xSource->getRangeAddress();
1996 // #TODO check if api orders the address
1997 // e.g. do we need to order the RangeAddress to get the topleft ( or can we assume it
1998 // is in the correct order )
1999 table::CellAddress aAddress;
2000 aAddress.Sheet = aRangeAddress.Sheet;
2001 aAddress.Column = aRangeAddress.StartColumn;
2002 aAddress.Row = aRangeAddress.StartRow;
2003 OUString sFormula;
2004 rFormula >>= sFormula;
2005 uno::Sequence<sheet::FormulaToken> aTokens = xParser->parseFormula( sFormula, aAddress );
2006 ScTokenArray aTokenArray;
2007 (void)ScTokenConversion::ConvertToTokenArray( getScDocument(), aTokenArray, aTokens );
2009 getScDocShell()->GetDocFunc().EnterMatrix( *getScRangeList()[0], NULL, &aTokenArray, OUString(), true, true, EMPTY_OUSTRING, formula::FormulaGrammar::GRAM_PODF_A1 );
2012 OUString
2013 ScVbaRange::Characters(const uno::Any& Start, const uno::Any& Length) throw (uno::RuntimeException, std::exception)
2015 // #TODO code within the test below "if ( m_Areas.... " can be removed
2016 // Test is performed only because m_xRange is NOT set to be
2017 // the first range in m_Areas ( to force failure while
2018 // the implementations for each method are being updated )
2019 if ( m_Areas->getCount() > 1 )
2021 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
2022 return xRange->Characters( Start, Length );
2025 long nIndex = 0, nCount = 0;
2026 OUString rString;
2027 uno::Reference< text::XTextRange > xTextRange(mxRange, ::uno::UNO_QUERY_THROW );
2028 rString = xTextRange->getString();
2029 if( !( Start >>= nIndex ) && !( Length >>= nCount ) )
2030 return rString;
2031 if(!( Start >>= nIndex ) )
2032 nIndex = 1;
2033 if(!( Length >>= nCount ) )
2034 nIndex = rString.getLength();
2035 return rString.copy( --nIndex, nCount ); // Zero value indexing
2038 OUString
2039 ScVbaRange::Address( const uno::Any& RowAbsolute, const uno::Any& ColumnAbsolute, const uno::Any& ReferenceStyle, const uno::Any& External, const uno::Any& RelativeTo ) throw (uno::RuntimeException, std::exception)
2041 if ( m_Areas->getCount() > 1 )
2043 // Multi-Area Range
2044 OUString sAddress;
2045 uno::Reference< XCollection > xCollection( m_Areas, uno::UNO_QUERY_THROW );
2046 uno::Any aExternalCopy = External;
2047 for ( sal_Int32 index = 1; index <= xCollection->getCount(); ++index )
2049 uno::Reference< excel::XRange > xRange( xCollection->Item( uno::makeAny( index ), uno::Any() ), uno::UNO_QUERY_THROW );
2050 if ( index > 1 )
2052 sAddress += OUString( ',' );
2053 // force external to be false
2054 // only first address should have the
2055 // document and sheet specifications
2056 aExternalCopy = uno::makeAny(sal_False);
2058 sAddress += xRange->Address( RowAbsolute, ColumnAbsolute, ReferenceStyle, aExternalCopy, RelativeTo );
2060 return sAddress;
2063 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 );
2064 if ( ReferenceStyle.hasValue() )
2066 sal_Int32 refStyle = excel::XlReferenceStyle::xlA1;
2067 ReferenceStyle >>= refStyle;
2068 if ( refStyle == excel::XlReferenceStyle::xlR1C1 )
2069 dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, 0, 0 );
2071 sal_uInt16 nFlags = SCA_VALID;
2072 ScDocShell* pDocShell = getScDocShell();
2073 ScDocument& rDoc = pDocShell->GetDocument();
2075 RangeHelper thisRange( mxRange );
2076 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
2077 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 ) );
2078 sal_uInt16 ROW_ABSOLUTE = ( SCA_ROW_ABSOLUTE | SCA_ROW2_ABSOLUTE );
2079 sal_uInt16 COL_ABSOLUTE = ( SCA_COL_ABSOLUTE | SCA_COL2_ABSOLUTE );
2080 // default
2081 nFlags |= ( SCA_TAB_ABSOLUTE | SCA_COL_ABSOLUTE | SCA_ROW_ABSOLUTE | SCA_TAB2_ABSOLUTE | SCA_COL2_ABSOLUTE | SCA_ROW2_ABSOLUTE );
2082 if ( RowAbsolute.hasValue() )
2084 bool bVal = true;
2085 RowAbsolute >>= bVal;
2086 if ( !bVal )
2087 nFlags &= ~ROW_ABSOLUTE;
2089 if ( ColumnAbsolute.hasValue() )
2091 bool bVal = true;
2092 ColumnAbsolute >>= bVal;
2093 if ( !bVal )
2094 nFlags &= ~COL_ABSOLUTE;
2096 if ( External.hasValue() )
2098 bool bLocal = false;
2099 External >>= bLocal;
2100 if ( bLocal )
2101 nFlags |= SCA_TAB_3D | SCA_FORCE_DOC;
2103 if ( RelativeTo.hasValue() )
2105 // #TODO should I throw an error if R1C1 is not set?
2107 table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell );
2108 dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, static_cast< SCROW >( refAddress.StartRow ), static_cast< SCCOL >( refAddress.StartColumn ) );
2110 return aRange.Format(nFlags, &rDoc, dDetails);
2113 uno::Reference < excel::XFont >
2114 ScVbaRange::Font() throw ( script::BasicErrorException, uno::RuntimeException)
2116 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY );
2117 ScDocument& rDoc = getScDocument();
2118 if ( mxRange.is() )
2119 xProps.set(mxRange, ::uno::UNO_QUERY );
2120 else if ( mxRanges.is() )
2121 xProps.set(mxRanges, ::uno::UNO_QUERY );
2123 ScVbaPalette aPalette( rDoc.GetDocumentShell() );
2124 ScCellRangeObj* pRangeObj = NULL;
2127 pRangeObj = getCellRangeObj();
2129 catch( uno::Exception& )
2132 return new ScVbaFont( this, mxContext, aPalette, xProps, pRangeObj );
2135 uno::Reference< excel::XRange >
2136 ScVbaRange::Cells( const uno::Any &nRowIndex, const uno::Any &nColumnIndex ) throw(uno::RuntimeException, std::exception)
2138 // #TODO code within the test below "if ( m_Areas.... " can be removed
2139 // Test is performed only because m_xRange is NOT set to be
2140 // the first range in m_Areas ( to force failure while
2141 // the implementations for each method are being updated )
2142 if ( m_Areas->getCount() > 1 )
2144 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
2145 return xRange->Cells( nRowIndex, nColumnIndex );
2148 // Performance: Use a common helper method for ScVbaRange::Cells and ScVbaWorksheet::Cells,
2149 // instead of creating a new ScVbaRange object in often-called ScVbaWorksheet::Cells
2150 return CellsHelper( mxParent, mxContext, mxRange, nRowIndex, nColumnIndex );
2153 // static
2154 uno::Reference< excel::XRange >
2155 ScVbaRange::CellsHelper( const uno::Reference< ov::XHelperInterface >& xParent,
2156 const uno::Reference< uno::XComponentContext >& xContext,
2157 const uno::Reference< css::table::XCellRange >& xRange,
2158 const uno::Any &nRowIndex, const uno::Any &nColumnIndex ) throw(uno::RuntimeException)
2160 sal_Int32 nRow = 0, nColumn = 0;
2162 bool bIsIndex = nRowIndex.hasValue();
2163 bool bIsColumnIndex = nColumnIndex.hasValue();
2165 // Sometimes we might get a float or a double or whatever
2166 // set in the Any, we should convert as appropriate
2167 // #FIXME - perhaps worth turning this into some sort of
2168 // conversion routine e.g. bSuccess = getValueFromAny( nRow, nRowIndex, cppu::UnoType<sal_Int32>::get() )
2169 uno::Any aRowIndexAny = nRowIndex;
2170 if ( aRowIndexAny.hasValue() && !( aRowIndexAny >>= nRow ) )
2172 uno::Reference< script::XTypeConverter > xConverter = getTypeConverter( xContext );
2173 uno::Any aConverted;
2176 aConverted = xConverter->convertTo( aRowIndexAny, cppu::UnoType<sal_Int32>::get() );
2177 bIsIndex = ( aConverted >>= nRow );
2179 catch( uno::Exception& ) {} // silence any errors
2182 uno::Any aColumnAny = nColumnIndex;
2184 if ( bIsColumnIndex )
2186 // Column index can be a col address e.g Cells( 1, "B" ) etc.
2187 OUString sCol;
2188 if ( nColumnIndex >>= sCol )
2190 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 );
2191 ScRange tmpRange;
2192 sal_uInt16 flags = tmpRange.ParseCols( sCol, &getDocumentFromRange( xRange ), dDetails );
2193 if ( ( flags & 0x200 ) != 0x200 )
2194 throw uno::RuntimeException();
2195 nColumn = tmpRange.aStart.Col() + 1;
2197 else
2199 if ( !( aColumnAny >>= nColumn ) )
2201 uno::Reference< script::XTypeConverter > xConverter = getTypeConverter( xContext );
2202 uno::Any aConverted;
2205 aConverted = xConverter->convertTo( aColumnAny, cppu::UnoType<sal_Int32>::get() );
2206 bIsColumnIndex = ( aConverted >>= nColumn );
2208 catch( uno::Exception& ) {} // silence any errors
2212 RangeHelper thisRange( xRange );
2213 table::CellRangeAddress thisRangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
2214 uno::Reference< table::XCellRange > xSheetRange = thisRange.getCellRangeFromSheet();
2215 if( !bIsIndex && !bIsColumnIndex ) // .Cells
2216 // #FIXE needs proper parent ( Worksheet )
2217 return uno::Reference< excel::XRange >( new ScVbaRange( xParent, xContext, xRange ) );
2219 sal_Int32 nIndex = --nRow;
2220 if( bIsIndex && !bIsColumnIndex ) // .Cells(n)
2222 uno::Reference< table::XColumnRowRange > xColumnRowRange(xRange, ::uno::UNO_QUERY_THROW);
2223 sal_Int32 nColCount = xColumnRowRange->getColumns()->getCount();
2225 if ( !nIndex || nIndex < 0 )
2226 nRow = 0;
2227 else
2228 nRow = nIndex / nColCount;
2229 nColumn = nIndex % nColCount;
2231 else
2232 --nColumn;
2233 nRow = nRow + thisRangeAddress.StartRow;
2234 nColumn = nColumn + thisRangeAddress.StartColumn;
2235 return new ScVbaRange( xParent, xContext, xSheetRange->getCellRangeByPosition( nColumn, nRow, nColumn, nRow ) );
2238 void
2239 ScVbaRange::Select() throw (uno::RuntimeException, std::exception)
2241 ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
2242 if ( !pUnoRangesBase )
2243 throw uno::RuntimeException("Failed to access underlying uno range object" );
2244 ScDocShell* pShell = pUnoRangesBase->GetDocShell();
2245 if ( pShell )
2247 uno::Reference< frame::XModel > xModel( pShell->GetModel(), uno::UNO_QUERY_THROW );
2248 uno::Reference< view::XSelectionSupplier > xSelection( xModel->getCurrentController(), uno::UNO_QUERY_THROW );
2249 if ( mxRanges.is() )
2250 xSelection->select( uno::Any( lclExpandToMerged( mxRanges, true ) ) );
2251 else
2252 xSelection->select( uno::Any( lclExpandToMerged( mxRange, true ) ) );
2253 // set focus on document e.g.
2254 // ThisComponent.CurrentController.Frame.getContainerWindow.SetFocus
2257 uno::Reference< frame::XController > xController( xModel->getCurrentController(), uno::UNO_QUERY_THROW );
2258 uno::Reference< frame::XFrame > xFrame( xController->getFrame(), uno::UNO_QUERY_THROW );
2259 uno::Reference< awt::XWindow > xWin( xFrame->getContainerWindow(), uno::UNO_QUERY_THROW );
2260 xWin->setFocus();
2262 catch( uno::Exception& )
2268 bool cellInRange( const table::CellRangeAddress& rAddr, const sal_Int32& nCol, const sal_Int32& nRow )
2270 if ( nCol >= rAddr.StartColumn && nCol <= rAddr.EndColumn &&
2271 nRow >= rAddr.StartRow && nRow <= rAddr.EndRow )
2272 return true;
2273 return false;
2276 void setCursor( const SCCOL& nCol, const SCROW& nRow, const uno::Reference< frame::XModel >& xModel, bool bInSel = true )
2278 ScTabViewShell* pShell = excel::getBestViewShell( xModel );
2279 if ( pShell )
2281 if ( bInSel )
2282 pShell->SetCursor( nCol, nRow );
2283 else
2284 pShell->MoveCursorAbs( nCol, nRow, SC_FOLLOW_NONE, false, false, true, false );
2288 void
2289 ScVbaRange::Activate() throw (uno::RuntimeException, std::exception)
2291 // get first cell of current range
2292 uno::Reference< table::XCellRange > xCellRange;
2293 if ( mxRanges.is() )
2295 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
2296 xCellRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW );
2298 else
2299 xCellRange.set( mxRange, uno::UNO_QUERY_THROW );
2301 RangeHelper thisRange( xCellRange );
2302 uno::Reference< sheet::XCellRangeAddressable > xThisRangeAddress = thisRange.getCellRangeAddressable();
2303 table::CellRangeAddress thisRangeAddress = xThisRangeAddress->getRangeAddress();
2304 uno::Reference< frame::XModel > xModel;
2305 ScDocShell* pShell = getScDocShell();
2307 if ( pShell )
2308 xModel = pShell->GetModel();
2310 if ( !xModel.is() )
2311 throw uno::RuntimeException();
2313 // get current selection
2314 uno::Reference< sheet::XCellRangeAddressable > xRange( xModel->getCurrentSelection(), ::uno::UNO_QUERY);
2316 uno::Reference< sheet::XSheetCellRanges > xRanges( xModel->getCurrentSelection(), ::uno::UNO_QUERY);
2318 if ( xRanges.is() )
2320 uno::Sequence< table::CellRangeAddress > nAddrs = xRanges->getRangeAddresses();
2321 for ( sal_Int32 index = 0; index < nAddrs.getLength(); ++index )
2323 if ( cellInRange( nAddrs[index], thisRangeAddress.StartColumn, thisRangeAddress.StartRow ) )
2325 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel );
2326 return;
2332 if ( xRange.is() && cellInRange( xRange->getRangeAddress(), thisRangeAddress.StartColumn, thisRangeAddress.StartRow ) )
2333 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel );
2334 else
2336 // if this range is multi cell select the range other
2337 // wise just position the cell at this single range position
2338 if ( isSingleCellRange() )
2339 // This top-leftmost cell of this Range is not in the current
2340 // selection so just select this range
2341 setCursor( static_cast< SCCOL >( thisRangeAddress.StartColumn ), static_cast< SCROW >( thisRangeAddress.StartRow ), xModel, false );
2342 else
2343 Select();
2348 uno::Reference< excel::XRange >
2349 ScVbaRange::Rows(const uno::Any& aIndex ) throw (uno::RuntimeException, std::exception)
2351 OUString sAddress;
2353 if ( aIndex.hasValue() )
2355 sal_Int32 nValue = 0;
2356 ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
2357 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
2359 ScRange aRange = *aCellRanges.front();
2360 if( aIndex >>= nValue )
2362 aRange.aStart.SetRow( aRange.aStart.Row() + --nValue );
2363 aRange.aEnd.SetRow( aRange.aStart.Row() );
2365 else if ( aIndex >>= sAddress )
2367 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 );
2368 ScRange tmpRange;
2369 tmpRange.ParseRows( sAddress, &getDocumentFromRange( mxRange ), dDetails );
2370 SCROW nStartRow = tmpRange.aStart.Row();
2371 SCROW nEndRow = tmpRange.aEnd.Row();
2373 aRange.aStart.SetRow( aRange.aStart.Row() + nStartRow );
2374 aRange.aEnd.SetRow( aRange.aStart.Row() + ( nEndRow - nStartRow ));
2376 else
2377 throw uno::RuntimeException("Illegal param" );
2379 if ( aRange.aStart.Row() < 0 || aRange.aEnd.Row() < 0 )
2380 throw uno::RuntimeException("Internal failure, illegal param" );
2381 // return a normal range ( even for multi-selection
2382 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange ) );
2383 return new ScVbaRange( mxParent, mxContext, xRange, true );
2385 // Rows() - no params
2386 if ( m_Areas->getCount() > 1 )
2387 return new ScVbaRange( mxParent, mxContext, mxRanges, true );
2388 return new ScVbaRange( mxParent, mxContext, mxRange, true );
2391 uno::Reference< excel::XRange >
2392 ScVbaRange::Columns(const uno::Any& aIndex ) throw (uno::RuntimeException, std::exception)
2394 OUString sAddress;
2396 ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
2397 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
2399 ScRange aRange = *aCellRanges.front();
2400 if ( aIndex.hasValue() )
2402 sal_Int32 nValue = 0;
2403 if ( aIndex >>= nValue )
2405 aRange.aStart.SetCol( aRange.aStart.Col() + static_cast< SCCOL > ( --nValue ) );
2406 aRange.aEnd.SetCol( aRange.aStart.Col() );
2409 else if ( aIndex >>= sAddress )
2411 ScAddress::Details dDetails( formula::FormulaGrammar::CONV_XL_A1, 0, 0 );
2412 ScRange tmpRange;
2413 tmpRange.ParseCols( sAddress, &getDocumentFromRange( mxRange ), dDetails );
2414 SCCOL nStartCol = tmpRange.aStart.Col();
2415 SCCOL nEndCol = tmpRange.aEnd.Col();
2417 aRange.aStart.SetCol( aRange.aStart.Col() + nStartCol );
2418 aRange.aEnd.SetCol( aRange.aStart.Col() + ( nEndCol - nStartCol ));
2420 else
2421 throw uno::RuntimeException("Illegal param" );
2423 if ( aRange.aStart.Col() < 0 || aRange.aEnd.Col() < 0 )
2424 throw uno::RuntimeException("Internal failure, illegal param" );
2426 // Columns() - no params
2427 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), aRange ) );
2428 return new ScVbaRange( mxParent, mxContext, xRange, false, true );
2431 void
2432 ScVbaRange::setMergeCells( const uno::Any& aIsMerged ) throw (script::BasicErrorException, uno::RuntimeException)
2434 bool bMerge = extractBoolFromAny( aIsMerged );
2436 if( mxRanges.is() )
2438 sal_Int32 nCount = mxRanges->getCount();
2440 // VBA does nothing (no error) if the own ranges overlap somehow
2441 ::std::vector< table::CellRangeAddress > aList;
2442 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex )
2444 uno::Reference< sheet::XCellRangeAddressable > xRangeAddr( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW );
2445 table::CellRangeAddress aAddress = xRangeAddr->getRangeAddress();
2446 for( ::std::vector< table::CellRangeAddress >::const_iterator aIt = aList.begin(), aEnd = aList.end(); aIt != aEnd; ++aIt )
2447 if( ScUnoConversion::Intersects( *aIt, aAddress ) )
2448 return;
2449 aList.push_back( aAddress );
2452 // (un)merge every range after it has been extended to intersecting merged ranges from sheet
2453 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex )
2455 uno::Reference< table::XCellRange > xRange( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW );
2456 lclExpandAndMerge( xRange, bMerge );
2458 return;
2461 // otherwise, merge single range
2462 lclExpandAndMerge( mxRange, bMerge );
2465 uno::Any
2466 ScVbaRange::getMergeCells() throw (script::BasicErrorException, uno::RuntimeException)
2468 if( mxRanges.is() )
2470 sal_Int32 nCount = mxRanges->getCount();
2471 for( sal_Int32 nIndex = 0; nIndex < nCount; ++nIndex )
2473 uno::Reference< table::XCellRange > xRange( mxRanges->getByIndex( nIndex ), uno::UNO_QUERY_THROW );
2474 util::TriState eMerged = lclGetMergedState( xRange );
2475 /* Excel always returns NULL, if one range of the range list is
2476 partly or completely merged. Even if all ranges are completely
2477 merged, the return value is still NULL. */
2478 if( eMerged != util::TriState_NO )
2479 return aNULL();
2481 // no range is merged anyhow, return false
2482 return uno::Any( false );
2485 // otherwise, check single range
2486 switch( lclGetMergedState( mxRange ) )
2488 case util::TriState_YES: return uno::Any( true );
2489 case util::TriState_NO: return uno::Any( false );
2490 default: return aNULL();
2494 void
2495 ScVbaRange::Copy(const ::uno::Any& Destination) throw (uno::RuntimeException, std::exception)
2497 if ( m_Areas->getCount() > 1 )
2498 throw uno::RuntimeException("That command cannot be used on multiple selections" );
2499 if ( Destination.hasValue() )
2501 uno::Reference< excel::XRange > xRange( Destination, uno::UNO_QUERY_THROW );
2502 uno::Any aRange = xRange->getCellRange();
2503 uno::Reference< table::XCellRange > xCellRange;
2504 aRange >>= xCellRange;
2505 uno::Reference< sheet::XSheetCellRange > xSheetCellRange(xCellRange, ::uno::UNO_QUERY_THROW);
2506 uno::Reference< sheet::XSpreadsheet > xSheet = xSheetCellRange->getSpreadsheet();
2507 uno::Reference< table::XCellRange > xDest( xSheet, uno::UNO_QUERY_THROW );
2508 uno::Reference< sheet::XCellRangeMovement > xMover( xSheet, uno::UNO_QUERY_THROW);
2509 uno::Reference< sheet::XCellAddressable > xDestination( xDest->getCellByPosition(
2510 xRange->getColumn()-1,xRange->getRow()-1), uno::UNO_QUERY_THROW );
2511 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY);
2512 xMover->copyRange( xDestination->getCellAddress(), xSource->getRangeAddress() );
2513 if ( ScVbaRange* pRange = getImplementation( xRange ) )
2514 pRange->fireChangeEvent();
2516 else
2518 uno::Reference< frame::XModel > xModel = getModelFromRange( mxRange );
2519 Select();
2520 excel::implnCopy( xModel );
2524 void
2525 ScVbaRange::Cut(const ::uno::Any& Destination) throw (uno::RuntimeException, std::exception)
2527 if ( m_Areas->getCount() > 1 )
2528 throw uno::RuntimeException("That command cannot be used on multiple selections" );
2529 if (Destination.hasValue())
2531 uno::Reference< excel::XRange > xRange( Destination, uno::UNO_QUERY_THROW );
2532 uno::Reference< table::XCellRange > xCellRange( xRange->getCellRange(), uno::UNO_QUERY_THROW );
2533 uno::Reference< sheet::XSheetCellRange > xSheetCellRange(xCellRange, ::uno::UNO_QUERY_THROW );
2534 uno::Reference< sheet::XSpreadsheet > xSheet = xSheetCellRange->getSpreadsheet();
2535 uno::Reference< table::XCellRange > xDest( xSheet, uno::UNO_QUERY_THROW );
2536 uno::Reference< sheet::XCellRangeMovement > xMover( xSheet, uno::UNO_QUERY_THROW);
2537 uno::Reference< sheet::XCellAddressable > xDestination( xDest->getCellByPosition(
2538 xRange->getColumn()-1,xRange->getRow()-1), uno::UNO_QUERY);
2539 uno::Reference< sheet::XCellRangeAddressable > xSource( mxRange, uno::UNO_QUERY);
2540 xMover->moveRange( xDestination->getCellAddress(), xSource->getRangeAddress() );
2542 else
2544 uno::Reference< frame::XModel > xModel = getModelFromRange( mxRange );
2545 Select();
2546 excel::implnCut( xModel );
2550 void
2551 ScVbaRange::setNumberFormat( const uno::Any& aFormat ) throw ( script::BasicErrorException, uno::RuntimeException)
2553 OUString sFormat;
2554 aFormat >>= sFormat;
2555 if ( m_Areas->getCount() > 1 )
2557 sal_Int32 nItems = m_Areas->getCount();
2558 for ( sal_Int32 index=1; index <= nItems; ++index )
2560 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
2561 xRange->setNumberFormat( aFormat );
2563 return;
2565 NumFormatHelper numFormat( mxRange );
2566 numFormat.setNumberFormat( sFormat );
2569 uno::Any
2570 ScVbaRange::getNumberFormat() throw ( script::BasicErrorException, uno::RuntimeException)
2573 if ( m_Areas->getCount() > 1 )
2575 sal_Int32 nItems = m_Areas->getCount();
2576 uno::Any aResult = aNULL();
2577 for ( sal_Int32 index=1; index <= nItems; ++index )
2579 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
2580 // if the numberformat of one area is different to another
2581 // return null
2582 if ( index > 1 )
2583 if ( aResult != xRange->getNumberFormat() )
2584 return aNULL();
2585 aResult = xRange->getNumberFormat();
2586 if ( aNULL() == aResult )
2587 return aNULL();
2589 return aResult;
2591 NumFormatHelper numFormat( mxRange );
2592 OUString sFormat = numFormat.getNumberFormatString();
2593 if ( !sFormat.isEmpty() )
2594 return uno::makeAny( sFormat );
2595 return aNULL();
2598 uno::Reference< excel::XRange >
2599 ScVbaRange::Resize( const uno::Any &RowSize, const uno::Any &ColumnSize ) throw (uno::RuntimeException, std::exception)
2601 long nRowSize = 0, nColumnSize = 0;
2602 bool bIsRowChanged = ( RowSize >>= nRowSize ), bIsColumnChanged = ( ColumnSize >>= nColumnSize );
2603 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, ::uno::UNO_QUERY_THROW);
2604 uno::Reference< sheet::XSheetCellRange > xSheetRange(mxRange, ::uno::UNO_QUERY_THROW);
2605 uno::Reference< sheet::XSheetCellCursor > xCursor( xSheetRange->getSpreadsheet()->createCursorByRange(xSheetRange), ::uno::UNO_QUERY_THROW );
2607 if( !bIsRowChanged )
2608 nRowSize = xColumnRowRange->getRows()->getCount();
2609 if( !bIsColumnChanged )
2610 nColumnSize = xColumnRowRange->getColumns()->getCount();
2612 xCursor->collapseToSize( nColumnSize, nRowSize );
2613 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xCursor, ::uno::UNO_QUERY_THROW );
2614 uno::Reference< table::XCellRange > xRange( xSheetRange->getSpreadsheet(), ::uno::UNO_QUERY_THROW );
2615 return new ScVbaRange( mxParent, mxContext,xRange->getCellRangeByPosition(
2616 xCellRangeAddressable->getRangeAddress().StartColumn,
2617 xCellRangeAddressable->getRangeAddress().StartRow,
2618 xCellRangeAddressable->getRangeAddress().EndColumn,
2619 xCellRangeAddressable->getRangeAddress().EndRow ) );
2622 void
2623 ScVbaRange::setWrapText( const uno::Any& aIsWrapped ) throw (script::BasicErrorException, uno::RuntimeException)
2625 if ( m_Areas->getCount() > 1 )
2627 sal_Int32 nItems = m_Areas->getCount();
2628 for ( sal_Int32 index=1; index <= nItems; ++index )
2630 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
2631 xRange->setWrapText( aIsWrapped );
2633 return;
2636 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY_THROW );
2637 bool bIsWrapped = extractBoolFromAny( aIsWrapped );
2638 xProps->setPropertyValue( "IsTextWrapped", uno::Any( bIsWrapped ) );
2641 uno::Any
2642 ScVbaRange::getWrapText() throw (script::BasicErrorException, uno::RuntimeException)
2644 if ( m_Areas->getCount() > 1 )
2646 sal_Int32 nItems = m_Areas->getCount();
2647 uno::Any aResult;
2648 for ( sal_Int32 index=1; index <= nItems; ++index )
2650 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
2651 if ( index > 1 )
2652 if ( aResult != xRange->getWrapText() )
2653 return aNULL();
2654 aResult = xRange->getWrapText();
2656 return aResult;
2659 SfxItemSet* pDataSet = getCurrentDataSet();
2661 SfxItemState eState = pDataSet->GetItemState( ATTR_LINEBREAK, true, NULL);
2662 if ( eState == SfxItemState::DONTCARE )
2663 return aNULL();
2665 uno::Reference< beans::XPropertySet > xProps(mxRange, ::uno::UNO_QUERY_THROW );
2666 uno::Any aValue = xProps->getPropertyValue( "IsTextWrapped" );
2667 return aValue;
2670 uno::Reference< excel::XInterior > ScVbaRange::Interior( ) throw ( script::BasicErrorException, uno::RuntimeException)
2672 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW );
2673 return new ScVbaInterior ( this, mxContext, xProps, &getScDocument() );
2675 uno::Reference< excel::XRange >
2676 ScVbaRange::Range( const uno::Any &Cell1, const uno::Any &Cell2 ) throw (uno::RuntimeException, std::exception)
2678 return Range( Cell1, Cell2, false );
2680 uno::Reference< excel::XRange >
2681 ScVbaRange::Range( const uno::Any &Cell1, const uno::Any &Cell2, bool bForceUseInpuRangeTab ) throw (uno::RuntimeException)
2684 uno::Reference< table::XCellRange > xCellRange = mxRange;
2686 if ( m_Areas->getCount() > 1 )
2688 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
2689 xCellRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW );
2691 else
2692 xCellRange.set( mxRange );
2694 RangeHelper thisRange( xCellRange );
2695 uno::Reference< table::XCellRange > xRanges = thisRange.getCellRangeFromSheet();
2696 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xRanges, uno::UNO_QUERY_THROW );
2698 uno::Reference< table::XCellRange > xReferrer =
2699 xRanges->getCellRangeByPosition( getColumn()-1, getRow()-1,
2700 xAddressable->getRangeAddress().EndColumn,
2701 xAddressable->getRangeAddress().EndRow );
2702 // xAddressable now for this range
2703 xAddressable.set( xReferrer, uno::UNO_QUERY_THROW );
2705 if( !Cell1.hasValue() )
2706 throw uno::RuntimeException( "Invalid Argument" );
2708 table::CellRangeAddress resultAddress;
2709 table::CellRangeAddress parentRangeAddress = xAddressable->getRangeAddress();
2711 ScRange aRange;
2712 // Cell1 defined only
2713 if ( !Cell2.hasValue() )
2715 OUString sName;
2716 Cell1 >>= sName;
2717 RangeHelper referRange( xReferrer );
2718 table::CellRangeAddress referAddress = referRange.getCellRangeAddressable()->getRangeAddress();
2719 return getRangeForName( mxContext, sName, getScDocShell(), referAddress );
2722 else
2724 table::CellRangeAddress cell1, cell2;
2725 cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell() );
2726 // Cell1 & Cell2 defined
2727 // Excel seems to combine the range as the range defined by
2728 // the combination of Cell1 & Cell2
2730 cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell() );
2732 resultAddress.StartColumn = ( cell1.StartColumn < cell2.StartColumn ) ? cell1.StartColumn : cell2.StartColumn;
2733 resultAddress.StartRow = ( cell1.StartRow < cell2.StartRow ) ? cell1.StartRow : cell2.StartRow;
2734 resultAddress.EndColumn = ( cell1.EndColumn > cell2.EndColumn ) ? cell1.EndColumn : cell2.EndColumn;
2735 resultAddress.EndRow = ( cell1.EndRow > cell2.EndRow ) ? cell1.EndRow : cell2.EndRow;
2736 if ( bForceUseInpuRangeTab )
2738 // this is a call from Application.Range( x,y )
2739 // its possiblefor x or y to specify a different sheet from
2740 // the current or active on ( but they must be the same )
2741 if ( cell1.Sheet != cell2.Sheet )
2742 throw uno::RuntimeException();
2743 parentRangeAddress.Sheet = cell1.Sheet;
2745 else
2747 // this is not a call from Application.Range( x,y )
2748 // if a different sheet from this range is specified it's
2749 // an error
2750 if ( parentRangeAddress.Sheet != cell1.Sheet
2751 || parentRangeAddress.Sheet != cell2.Sheet
2753 throw uno::RuntimeException();
2756 ScUnoConversion::FillScRange( aRange, resultAddress );
2758 ScRange parentAddress;
2759 ScUnoConversion::FillScRange( parentAddress, parentRangeAddress);
2760 if ( aRange.aStart.Col() >= 0 && aRange.aStart.Row() >= 0 && aRange.aEnd.Col() >= 0 && aRange.aEnd.Row() >= 0 )
2762 sal_Int32 nStartX = parentAddress.aStart.Col() + aRange.aStart.Col();
2763 sal_Int32 nStartY = parentAddress.aStart.Row() + aRange.aStart.Row();
2764 sal_Int32 nEndX = parentAddress.aStart.Col() + aRange.aEnd.Col();
2765 sal_Int32 nEndY = parentAddress.aStart.Row() + aRange.aEnd.Row();
2767 if ( nStartX <= nEndX && nEndX <= parentAddress.aEnd.Col() &&
2768 nStartY <= nEndY && nEndY <= parentAddress.aEnd.Row() )
2770 ScRange aNew( (SCCOL)nStartX, (SCROW)nStartY, parentAddress.aStart.Tab(),
2771 (SCCOL)nEndX, (SCROW)nEndY, parentAddress.aEnd.Tab() );
2772 xCellRange = new ScCellRangeObj( getScDocShell(), aNew );
2776 return new ScVbaRange( mxParent, mxContext, xCellRange );
2780 // Allow access to underlying openoffice uno api ( useful for debugging
2781 // with openoffice basic )
2782 uno::Any SAL_CALL ScVbaRange::getCellRange( ) throw (uno::RuntimeException, std::exception)
2784 uno::Any aAny;
2785 if ( mxRanges.is() )
2786 aAny <<= mxRanges;
2787 else if ( mxRange.is() )
2788 aAny <<= mxRange;
2789 return aAny;
2792 uno::Any ScVbaRange::getCellRange( const uno::Reference< excel::XRange >& rxRange ) throw (uno::RuntimeException)
2794 if( ScVbaRange* pVbaRange = getImplementation( rxRange ) )
2795 return pVbaRange->getCellRange();
2796 throw uno::RuntimeException();
2799 static InsertDeleteFlags getPasteFlags (sal_Int32 Paste)
2801 InsertDeleteFlags nFlags = IDF_NONE;
2802 switch (Paste) {
2803 case excel::XlPasteType::xlPasteComments:
2804 nFlags = IDF_NOTE;break;
2805 case excel::XlPasteType::xlPasteFormats:
2806 nFlags = IDF_ATTRIB;break;
2807 case excel::XlPasteType::xlPasteFormulas:
2808 nFlags = IDF_FORMULA;break;
2809 case excel::XlPasteType::xlPasteFormulasAndNumberFormats :
2810 case excel::XlPasteType::xlPasteValues:
2811 nFlags = ( IDF_VALUE | IDF_DATETIME | IDF_STRING | IDF_SPECIAL_BOOLEAN ); break;
2812 case excel::XlPasteType::xlPasteValuesAndNumberFormats:
2813 nFlags = IDF_VALUE | IDF_ATTRIB; break;
2814 case excel::XlPasteType::xlPasteColumnWidths:
2815 case excel::XlPasteType::xlPasteValidation:
2816 nFlags = IDF_NONE;break;
2817 case excel::XlPasteType::xlPasteAll:
2818 case excel::XlPasteType::xlPasteAllExceptBorders:
2819 default:
2820 nFlags = IDF_ALL;break;
2822 return nFlags;
2825 static sal_uInt16
2826 getPasteFormulaBits( sal_Int32 Operation)
2828 sal_uInt16 nFormulaBits = PASTE_NOFUNC ;
2829 switch (Operation)
2831 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationAdd:
2832 nFormulaBits = PASTE_ADD;break;
2833 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationSubtract:
2834 nFormulaBits = PASTE_SUB;break;
2835 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationMultiply:
2836 nFormulaBits = PASTE_MUL;break;
2837 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationDivide:
2838 nFormulaBits = PASTE_DIV;break;
2840 case excel::XlPasteSpecialOperation::xlPasteSpecialOperationNone:
2841 default:
2842 nFormulaBits = PASTE_NOFUNC; break;
2845 return nFormulaBits;
2847 void SAL_CALL
2848 ScVbaRange::PasteSpecial( const uno::Any& Paste, const uno::Any& Operation, const uno::Any& SkipBlanks, const uno::Any& Transpose ) throw (::com::sun::star::uno::RuntimeException, std::exception)
2850 if ( m_Areas->getCount() > 1 )
2851 throw uno::RuntimeException("That command cannot be used on multiple selections" );
2852 ScDocShell* pShell = getScDocShell();
2854 if (!pShell)
2855 throw uno::RuntimeException("That command cannot be used with no ScDocShell" );
2857 uno::Reference< frame::XModel > xModel(pShell->GetModel(), uno::UNO_QUERY_THROW);
2858 uno::Reference< view::XSelectionSupplier > xSelection( xModel->getCurrentController(), uno::UNO_QUERY_THROW );
2859 // select this range
2860 xSelection->select( uno::makeAny( mxRange ) );
2861 // set up defaults
2862 sal_Int32 nPaste = excel::XlPasteType::xlPasteAll;
2863 sal_Int32 nOperation = excel::XlPasteSpecialOperation::xlPasteSpecialOperationNone;
2864 bool bTranspose = false;
2865 bool bSkipBlanks = false;
2867 if ( Paste.hasValue() )
2868 Paste >>= nPaste;
2869 if ( Operation.hasValue() )
2870 Operation >>= nOperation;
2871 if ( SkipBlanks.hasValue() )
2872 SkipBlanks >>= bSkipBlanks;
2873 if ( Transpose.hasValue() )
2874 Transpose >>= bTranspose;
2876 InsertDeleteFlags nFlags = getPasteFlags(nPaste);
2877 sal_uInt16 nFormulaBits = getPasteFormulaBits(nOperation);
2878 excel::implnPasteSpecial(pShell->GetModel(), nFlags,nFormulaBits,bSkipBlanks,bTranspose);
2881 uno::Reference< excel::XRange >
2882 ScVbaRange::getEntireColumnOrRow( bool bColumn ) throw (uno::RuntimeException)
2884 ScCellRangesBase* pUnoRangesBase = getCellRangesBase();
2885 // copy the range list
2886 ScRangeList aCellRanges = pUnoRangesBase->GetRangeList();
2888 for ( size_t i = 0, nRanges = aCellRanges.size(); i < nRanges; ++i )
2890 ScRange* pRange = aCellRanges[ i ];
2891 if ( bColumn )
2893 pRange->aStart.SetRow( 0 );
2894 pRange->aEnd.SetRow( MAXROW );
2896 else
2898 pRange->aStart.SetCol( 0 );
2899 pRange->aEnd.SetCol( MAXCOL );
2902 if ( aCellRanges.size() > 1 ) // Multi-Area
2904 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( pUnoRangesBase->GetDocShell(), aCellRanges ) );
2906 return new ScVbaRange( mxParent, mxContext, xRanges, !bColumn, bColumn );
2908 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( pUnoRangesBase->GetDocShell(), *aCellRanges.front() ) );
2909 return new ScVbaRange( mxParent, mxContext, xRange, !bColumn, bColumn );
2912 uno::Reference< excel::XRange > SAL_CALL
2913 ScVbaRange::getEntireRow() throw (uno::RuntimeException, std::exception)
2915 return getEntireColumnOrRow(false);
2918 uno::Reference< excel::XRange > SAL_CALL
2919 ScVbaRange::getEntireColumn() throw (uno::RuntimeException, std::exception)
2921 return getEntireColumnOrRow();
2924 uno::Reference< excel::XComment > SAL_CALL
2925 ScVbaRange::AddComment( const uno::Any& Text ) throw (uno::RuntimeException, std::exception)
2927 // if there is already a comment in the top-left cell then throw
2928 if( getComment().is() )
2929 throw uno::RuntimeException();
2931 // workaround: Excel allows to create empty comment, Calc does not
2932 OUString aNoteText;
2933 if( Text.hasValue() && !(Text >>= aNoteText) )
2934 throw uno::RuntimeException();
2935 if( aNoteText.isEmpty() )
2936 aNoteText = " ";
2938 // try to create a new annotation
2939 table::CellRangeAddress aRangePos = lclGetRangeAddress( mxRange );
2940 table::CellAddress aNotePos( aRangePos.Sheet, aRangePos.StartColumn, aRangePos.StartRow );
2941 uno::Reference< sheet::XSheetCellRange > xCellRange( mxRange, uno::UNO_QUERY_THROW );
2942 uno::Reference< sheet::XSheetAnnotationsSupplier > xAnnosSupp( xCellRange->getSpreadsheet(), uno::UNO_QUERY_THROW );
2943 uno::Reference< sheet::XSheetAnnotations > xAnnos( xAnnosSupp->getAnnotations(), uno::UNO_SET_THROW );
2944 xAnnos->insertNew( aNotePos, aNoteText );
2945 return new ScVbaComment( this, mxContext, getUnoModel(), mxRange );
2948 uno::Reference< excel::XComment > SAL_CALL
2949 ScVbaRange::getComment() throw (uno::RuntimeException, std::exception)
2951 // intentional behavior to return a null object if no
2952 // comment defined
2953 uno::Reference< excel::XComment > xComment( new ScVbaComment( this, mxContext, getUnoModel(), mxRange ) );
2954 if ( xComment->Text( uno::Any(), uno::Any(), uno::Any() ).isEmpty() )
2955 return NULL;
2956 return xComment;
2960 uno::Reference< beans::XPropertySet >
2961 getRowOrColumnProps( const uno::Reference< table::XCellRange >& xCellRange, bool bRows ) throw ( uno::RuntimeException )
2963 uno::Reference< table::XColumnRowRange > xColRow( xCellRange, uno::UNO_QUERY_THROW );
2964 uno::Reference< beans::XPropertySet > xProps;
2965 if ( bRows )
2966 xProps.set( xColRow->getRows(), uno::UNO_QUERY_THROW );
2967 else
2968 xProps.set( xColRow->getColumns(), uno::UNO_QUERY_THROW );
2969 return xProps;
2972 uno::Any SAL_CALL
2973 ScVbaRange::getHidden() throw (uno::RuntimeException, std::exception)
2975 // if multi-area result is the result of the
2976 // first area
2977 if ( m_Areas->getCount() > 1 )
2979 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(sal_Int32(1)), uno::Any() ), uno::UNO_QUERY_THROW );
2980 return xRange->getHidden();
2982 bool bIsVisible = false;
2985 uno::Reference< beans::XPropertySet > xProps = getRowOrColumnProps( mxRange, mbIsRows );
2986 if ( !( xProps->getPropertyValue( ISVISIBLE ) >>= bIsVisible ) )
2987 throw uno::RuntimeException("Failed to get IsVisible property" );
2989 catch( const uno::Exception& e )
2991 throw uno::RuntimeException( e.Message );
2993 return uno::makeAny( !bIsVisible );
2996 void SAL_CALL
2997 ScVbaRange::setHidden( const uno::Any& _hidden ) throw (uno::RuntimeException, std::exception)
2999 if ( m_Areas->getCount() > 1 )
3001 sal_Int32 nItems = m_Areas->getCount();
3002 for ( sal_Int32 index=1; index <= nItems; ++index )
3004 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
3005 xRange->setHidden( _hidden );
3007 return;
3010 bool bHidden = extractBoolFromAny( _hidden );
3013 uno::Reference< beans::XPropertySet > xProps = getRowOrColumnProps( mxRange, mbIsRows );
3014 xProps->setPropertyValue( ISVISIBLE, uno::Any( !bHidden ) );
3016 catch( const uno::Exception& e )
3018 throw uno::RuntimeException( e.Message );
3022 sal_Bool SAL_CALL
3023 ScVbaRange::Replace( const OUString& What, const OUString& Replacement, const uno::Any& LookAt, const uno::Any& SearchOrder, const uno::Any& MatchCase, const uno::Any& MatchByte, const uno::Any& SearchFormat, const uno::Any& ReplaceFormat ) throw (uno::RuntimeException, std::exception)
3025 if ( m_Areas->getCount() > 1 )
3027 for ( sal_Int32 index = 1; index <= m_Areas->getCount(); ++index )
3029 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( index ), uno::Any() ), uno::UNO_QUERY_THROW );
3030 xRange->Replace( What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat );
3032 return sal_True; // seems to return true always ( or at least I haven't found the trick of
3035 // sanity check required params
3036 if ( What.isEmpty() )
3037 throw uno::RuntimeException("Range::Replace, missing params" );
3038 OUString sWhat = VBAToRegexp( What);
3039 // #TODO #FIXME SearchFormat & ReplacesFormat are not processed
3040 // What do we do about MatchByte.. we don't seem to support that
3041 const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem();
3042 SvxSearchItem newOptions( globalSearchOptions );
3044 sal_Int16 nLook = globalSearchOptions.GetWordOnly() ? excel::XlLookAt::xlPart : excel::XlLookAt::xlWhole;
3045 sal_Int16 nSearchOrder = globalSearchOptions.GetRowDirection() ? excel::XlSearchOrder::xlByRows : excel::XlSearchOrder::xlByColumns;
3047 uno::Reference< util::XReplaceable > xReplace( mxRange, uno::UNO_QUERY );
3048 if ( xReplace.is() )
3050 uno::Reference< util::XReplaceDescriptor > xDescriptor =
3051 xReplace->createReplaceDescriptor();
3053 xDescriptor->setSearchString( sWhat);
3054 xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::makeAny( sal_True ) );
3055 xDescriptor->setReplaceString( Replacement);
3056 if ( LookAt.hasValue() )
3058 // sets SearchWords ( true is Cell match )
3059 nLook = ::comphelper::getINT16( LookAt );
3060 bool bSearchWords = false;
3061 if ( nLook == excel::XlLookAt::xlPart )
3062 bSearchWords = false;
3063 else if ( nLook == excel::XlLookAt::xlWhole )
3064 bSearchWords = true;
3065 else
3066 throw uno::RuntimeException("Range::Replace, illegal value for LookAt" );
3067 // set global search props ( affects the find dialog
3068 // and of course the defaults for this method
3069 newOptions.SetWordOnly( bSearchWords );
3070 xDescriptor->setPropertyValue( SC_UNO_SRCHWORDS, uno::makeAny( bSearchWords ) );
3072 // sets SearchByRow ( true for Rows )
3073 if ( SearchOrder.hasValue() )
3075 nSearchOrder = ::comphelper::getINT16( SearchOrder );
3076 bool bSearchByRow = false;
3077 if ( nSearchOrder == excel::XlSearchOrder::xlByColumns )
3078 bSearchByRow = false;
3079 else if ( nSearchOrder == excel::XlSearchOrder::xlByRows )
3080 bSearchByRow = true;
3081 else
3082 throw uno::RuntimeException("Range::Replace, illegal value for SearchOrder" );
3084 newOptions.SetRowDirection( bSearchByRow );
3085 xDescriptor->setPropertyValue( SC_UNO_SRCHBYROW, uno::makeAny( bSearchByRow ) );
3087 if ( MatchCase.hasValue() )
3089 bool bMatchCase = false;
3091 // SearchCaseSensitive
3092 MatchCase >>= bMatchCase;
3093 xDescriptor->setPropertyValue( SC_UNO_SRCHCASE, uno::makeAny( bMatchCase ) );
3096 ScGlobal::SetSearchItem( newOptions );
3097 // ignore MatchByte for the moment, its not supported in
3098 // OOo.org afaik
3100 uno::Reference< util::XSearchDescriptor > xSearch( xDescriptor, uno::UNO_QUERY );
3101 uno::Reference< container::XIndexAccess > xIndexAccess = xReplace->findAll( xSearch );
3102 xReplace->replaceAll( xSearch );
3103 if ( xIndexAccess.is() && xIndexAccess->getCount() > 0 )
3105 for ( sal_Int32 i = 0; i < xIndexAccess->getCount(); ++i )
3107 uno::Reference< table::XCellRange > xCellRange( xIndexAccess->getByIndex( i ), uno::UNO_QUERY );
3108 if ( xCellRange.is() )
3110 uno::Reference< excel::XRange > xRange( new ScVbaRange( mxParent, mxContext, xCellRange ) );
3111 uno::Reference< container::XEnumerationAccess > xEnumAccess( xRange, uno::UNO_QUERY_THROW );
3112 uno::Reference< container::XEnumeration > xEnum = xEnumAccess->createEnumeration();
3113 while ( xEnum->hasMoreElements() )
3115 uno::Reference< excel::XRange > xNextRange( xEnum->nextElement(), uno::UNO_QUERY_THROW );
3116 ScVbaRange* pRange = dynamic_cast< ScVbaRange * > ( xNextRange.get() );
3117 if ( pRange )
3118 pRange->fireChangeEvent();
3124 return sal_True; // always
3127 uno::Reference< excel::XRange > SAL_CALL
3128 ScVbaRange::Find( const uno::Any& What, const uno::Any& After, const uno::Any& LookIn, const uno::Any& LookAt, const uno::Any& SearchOrder, const uno::Any& SearchDirection, const uno::Any& MatchCase, const uno::Any& /*MatchByte*/, const uno::Any& /*SearchFormat*/ ) throw (uno::RuntimeException, std::exception)
3130 // return a Range object that represents the first cell where that information is found.
3131 OUString sWhat;
3132 sal_Int32 nWhat = 0;
3133 double fWhat = 0.0;
3135 // string.
3136 if( What >>= sWhat )
3138 if( sWhat.isEmpty() )
3139 throw uno::RuntimeException("Range::Find, missing params" );
3141 else if( What >>= nWhat )
3143 sWhat = OUString::number( nWhat );
3145 else if( What >>= fWhat )
3147 sWhat = OUString::number( fWhat );
3149 else
3150 throw uno::RuntimeException("Range::Find, missing params" );
3152 OUString sSearch = VBAToRegexp( sWhat );
3154 const SvxSearchItem& globalSearchOptions = ScGlobal::GetSearchItem();
3155 SvxSearchItem newOptions( globalSearchOptions );
3157 sal_Int16 nSearchOrder = globalSearchOptions.GetRowDirection() ? excel::XlSearchOrder::xlByRows : excel::XlSearchOrder::xlByColumns;
3159 uno::Reference< util::XSearchable > xSearch( mxRange, uno::UNO_QUERY );
3160 if( xSearch.is() )
3162 uno::Reference< util::XSearchDescriptor > xDescriptor = xSearch->createSearchDescriptor();
3163 xDescriptor->setSearchString( sSearch );
3164 xDescriptor->setPropertyValue( SC_UNO_SRCHREGEXP, uno::Any( true ) );
3166 uno::Reference< excel::XRange > xAfterRange;
3167 uno::Reference< table::XCellRange > xStartCell;
3168 if( After >>= xAfterRange )
3170 // After must be a single cell in the range
3171 if( xAfterRange->getCount() > 1 )
3172 throw uno::RuntimeException("After must be a single cell." );
3173 uno::Reference< excel::XRange > xCell( Cells( uno::makeAny( xAfterRange->getRow() ), uno::makeAny( xAfterRange->getColumn() ) ), uno::UNO_QUERY );
3174 if( !xCell.is() )
3175 throw uno::RuntimeException("After must be in range." );
3176 xStartCell.set( xAfterRange->getCellRange(), uno::UNO_QUERY_THROW );
3179 // LookIn
3180 if( LookIn.hasValue() )
3182 sal_Int32 nLookIn = 0;
3183 if( LookIn >>= nLookIn )
3185 SvxSearchCellType nSearchType;
3186 switch( nLookIn )
3188 case excel::XlFindLookIn::xlComments :
3189 nSearchType = SvxSearchCellType::NOTE; // Notes
3190 break;
3191 case excel::XlFindLookIn::xlFormulas :
3192 nSearchType = SvxSearchCellType::FORMULA;
3193 break;
3194 case excel::XlFindLookIn::xlValues :
3195 nSearchType = SvxSearchCellType::VALUE;
3196 break;
3197 default:
3198 throw uno::RuntimeException("Range::Replace, illegal value for LookIn." );
3200 newOptions.SetCellType( nSearchType );
3201 xDescriptor->setPropertyValue( "SearchType", uno::makeAny( static_cast<sal_uInt16>(nSearchType) ) );
3205 // LookAt
3206 if ( LookAt.hasValue() )
3208 sal_Int16 nLookAt = ::comphelper::getINT16( LookAt );
3209 bool bSearchWords = false;
3210 if ( nLookAt == excel::XlLookAt::xlPart )
3211 bSearchWords = false;
3212 else if ( nLookAt == excel::XlLookAt::xlWhole )
3213 bSearchWords = true;
3214 else
3215 throw uno::RuntimeException("Range::Replace, illegal value for LookAt" );
3216 newOptions.SetWordOnly( bSearchWords );
3217 xDescriptor->setPropertyValue( SC_UNO_SRCHWORDS, uno::makeAny( bSearchWords ) );
3220 // SearchOrder
3221 if ( SearchOrder.hasValue() )
3223 nSearchOrder = ::comphelper::getINT16( SearchOrder );
3224 bool bSearchByRow = false;
3225 if ( nSearchOrder == excel::XlSearchOrder::xlByColumns )
3226 bSearchByRow = false;
3227 else if ( nSearchOrder == excel::XlSearchOrder::xlByRows )
3228 bSearchByRow = true;
3229 else
3230 throw uno::RuntimeException("Range::Replace, illegal value for SearchOrder" );
3232 newOptions.SetRowDirection( bSearchByRow );
3233 xDescriptor->setPropertyValue( SC_UNO_SRCHBYROW, uno::makeAny( bSearchByRow ) );
3236 // SearchDirection
3237 if ( SearchDirection.hasValue() )
3239 sal_Int32 nSearchDirection = 0;
3240 if( SearchDirection >>= nSearchDirection )
3242 bool bSearchBackwards = false;
3243 if ( nSearchDirection == excel::XlSearchDirection::xlNext )
3244 bSearchBackwards = false;
3245 else if( nSearchDirection == excel::XlSearchDirection::xlPrevious )
3246 bSearchBackwards = true;
3247 else
3248 throw uno::RuntimeException("Range::Replace, illegal value for SearchDirection" );
3249 newOptions.SetBackward( bSearchBackwards );
3250 xDescriptor->setPropertyValue( "SearchBackwards", uno::makeAny( bSearchBackwards ) );
3254 // MatchCase
3255 bool bMatchCase = false;
3256 if ( MatchCase.hasValue() )
3258 // SearchCaseSensitive
3259 if( !( MatchCase >>= bMatchCase ) )
3260 throw uno::RuntimeException("Range::Replace, illegal value for MatchCase" );
3262 xDescriptor->setPropertyValue( SC_UNO_SRCHCASE, uno::makeAny( bMatchCase ) );
3264 // MatchByte
3265 // SearchFormat
3266 // ignore
3268 ScGlobal::SetSearchItem( newOptions );
3270 uno::Reference< uno::XInterface > xInterface = xStartCell.is() ? xSearch->findNext( xStartCell, xDescriptor) : xSearch->findFirst( xDescriptor );
3271 uno::Reference< table::XCellRange > xCellRange( xInterface, uno::UNO_QUERY );
3272 // if we are searching from a starting cell and failed to find a match
3273 // then try from the beginning
3274 if ( !xCellRange.is() && xStartCell.is() )
3276 xInterface = xSearch->findFirst( xDescriptor );
3277 xCellRange.set( xInterface, uno::UNO_QUERY );
3279 if ( xCellRange.is() )
3281 uno::Reference< excel::XRange > xResultRange = new ScVbaRange( mxParent, mxContext, xCellRange );
3282 if( xResultRange.is() )
3284 return xResultRange;
3290 return uno::Reference< excel::XRange >();
3293 uno::Reference< table::XCellRange > processKey( const uno::Any& Key, uno::Reference< uno::XComponentContext >& xContext, ScDocShell* pDocSh )
3295 uno::Reference< excel::XRange > xKeyRange;
3296 if ( Key.getValueType() == cppu::UnoType<excel::XRange>::get() )
3298 xKeyRange.set( Key, uno::UNO_QUERY_THROW );
3300 else if ( Key.getValueType() == ::cppu::UnoType<OUString>::get() )
3303 OUString sRangeName = ::comphelper::getString( Key );
3304 table::CellRangeAddress aRefAddr;
3305 if ( !pDocSh )
3306 throw uno::RuntimeException("Range::Sort no docshell to calculate key param" );
3307 xKeyRange = getRangeForName( xContext, sRangeName, pDocSh, aRefAddr );
3309 else
3310 throw uno::RuntimeException("Range::Sort illegal type value for key param" );
3311 uno::Reference< table::XCellRange > xKey;
3312 xKey.set( xKeyRange->getCellRange(), uno::UNO_QUERY_THROW );
3313 return xKey;
3316 // helper method for Sort
3317 sal_Int32 findSortPropertyIndex( const uno::Sequence< beans::PropertyValue >& props,
3318 const OUString& sPropName ) throw( uno::RuntimeException )
3320 const beans::PropertyValue* pProp = props.getConstArray();
3321 sal_Int32 nItems = props.getLength();
3323 sal_Int32 count=0;
3324 for ( ; count < nItems; ++count, ++pProp )
3325 if ( pProp->Name.equals( sPropName ) )
3326 return count;
3327 if ( count == nItems )
3328 throw uno::RuntimeException("Range::Sort unknown sort property" );
3329 return -1; //should never reach here ( satisfy compiler )
3332 // helper method for Sort
3333 void updateTableSortField( const uno::Reference< table::XCellRange >& xParentRange,
3334 const uno::Reference< table::XCellRange >& xColRowKey, sal_Int16 nOrder,
3335 table::TableSortField& aTableField, bool bIsSortColumn, bool bMatchCase ) throw ( uno::RuntimeException )
3337 RangeHelper parentRange( xParentRange );
3338 RangeHelper colRowRange( xColRowKey );
3340 table::CellRangeAddress parentRangeAddress = parentRange.getCellRangeAddressable()->getRangeAddress();
3342 table::CellRangeAddress colRowKeyAddress = colRowRange.getCellRangeAddressable()->getRangeAddress();
3344 // make sure that upper left poing of key range is within the
3345 // parent range
3346 if ( ( !bIsSortColumn && colRowKeyAddress.StartColumn >= parentRangeAddress.StartColumn &&
3347 colRowKeyAddress.StartColumn <= parentRangeAddress.EndColumn ) || ( bIsSortColumn &&
3348 colRowKeyAddress.StartRow >= parentRangeAddress.StartRow &&
3349 colRowKeyAddress.StartRow <= parentRangeAddress.EndRow ) )
3351 //determine col/row index
3352 if ( bIsSortColumn )
3353 aTableField.Field = colRowKeyAddress.StartRow - parentRangeAddress.StartRow;
3354 else
3355 aTableField.Field = colRowKeyAddress.StartColumn - parentRangeAddress.StartColumn;
3356 aTableField.IsCaseSensitive = bMatchCase;
3358 if ( nOrder == excel::XlSortOrder::xlAscending )
3359 aTableField.IsAscending = sal_True;
3360 else
3361 aTableField.IsAscending = false;
3363 else
3364 throw uno::RuntimeException("Illegal Key param" );
3368 void SAL_CALL
3369 ScVbaRange::Sort( const uno::Any& Key1, const uno::Any& Order1, const uno::Any& Key2, const uno::Any& /*Type*/, const uno::Any& Order2, const uno::Any& Key3, const uno::Any& Order3, const uno::Any& Header, const uno::Any& OrderCustom, const uno::Any& MatchCase, const uno::Any& Orientation, const uno::Any& SortMethod, const uno::Any& DataOption1, const uno::Any& DataOption2, const uno::Any& DataOption3 ) throw (uno::RuntimeException, std::exception)
3371 // #TODO# #FIXME# can we do something with Type
3372 if ( m_Areas->getCount() > 1 )
3373 throw uno::RuntimeException("That command cannot be used on multiple selections" );
3375 sal_Int16 nDataOption1 = excel::XlSortDataOption::xlSortNormal;
3376 sal_Int16 nDataOption2 = excel::XlSortDataOption::xlSortNormal;
3377 sal_Int16 nDataOption3 = excel::XlSortDataOption::xlSortNormal;
3379 ScDocument& rDoc = getScDocument();
3381 RangeHelper thisRange( mxRange );
3382 table::CellRangeAddress thisRangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3383 ScSortParam aSortParam;
3384 SCTAB nTab = thisRangeAddress.Sheet;
3385 rDoc.GetSortParam( aSortParam, nTab );
3387 if ( DataOption1.hasValue() )
3388 DataOption1 >>= nDataOption1;
3389 if ( DataOption2.hasValue() )
3390 DataOption2 >>= nDataOption2;
3391 if ( DataOption3.hasValue() )
3392 DataOption3 >>= nDataOption3;
3394 // 1) #TODO #FIXME need to process DataOption[1..3] not used currently
3395 // 2) #TODO #FIXME need to refactor this ( below ) into a IsSingleCell() method
3396 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY_THROW );
3398 // set up defaults
3400 sal_Int16 nOrder1 = aSortParam.maKeyState[1].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending;
3401 sal_Int16 nOrder2 = aSortParam.maKeyState[2].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending;
3402 sal_Int16 nOrder3 = aSortParam.maKeyState[3].bAscending ? excel::XlSortOrder::xlAscending : excel::XlSortOrder::xlDescending;
3404 sal_Int16 nCustom = aSortParam.nUserIndex;
3405 sal_Int16 nSortMethod = excel::XlSortMethod::xlPinYin;
3406 bool bMatchCase = aSortParam.bCaseSens;
3408 // seems to work opposite to expected, see below
3409 sal_Int16 nOrientation = aSortParam.bByRow ? excel::XlSortOrientation::xlSortColumns : excel::XlSortOrientation::xlSortRows;
3411 if ( Orientation.hasValue() )
3413 // Documentation says xlSortRows is default but that doesn't appear to be
3414 // the case. Also it appears that xlSortColumns is the default which
3415 // strangely enough sorts by Row
3416 nOrientation = ::comphelper::getINT16( Orientation );
3417 // persist new option to be next calls default
3418 if ( nOrientation == excel::XlSortOrientation::xlSortRows )
3419 aSortParam.bByRow = false;
3420 else
3421 aSortParam.bByRow = true;
3425 bool bIsSortColumns=false; // sort by row
3427 if ( nOrientation == excel::XlSortOrientation::xlSortRows )
3428 bIsSortColumns = true;
3429 sal_Int16 nHeader = 0;
3430 nHeader = aSortParam.nCompatHeader;
3431 bool bContainsHeader = false;
3433 if ( Header.hasValue() )
3435 nHeader = ::comphelper::getINT16( Header );
3436 aSortParam.nCompatHeader = nHeader;
3439 if ( nHeader == excel::XlYesNoGuess::xlGuess )
3441 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 ));
3442 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 ) );
3443 if ( bHasColHeader || bHasRowHeader )
3444 nHeader = excel::XlYesNoGuess::xlYes;
3445 else
3446 nHeader = excel::XlYesNoGuess::xlNo;
3447 aSortParam.nCompatHeader = nHeader;
3450 if ( nHeader == excel::XlYesNoGuess::xlYes )
3451 bContainsHeader = true;
3453 if ( SortMethod.hasValue() )
3455 nSortMethod = ::comphelper::getINT16( SortMethod );
3458 if ( OrderCustom.hasValue() )
3460 OrderCustom >>= nCustom;
3461 --nCustom; // 0-based in OOo
3462 aSortParam.nUserIndex = nCustom;
3465 if ( MatchCase.hasValue() )
3467 MatchCase >>= bMatchCase;
3468 aSortParam.bCaseSens = bMatchCase;
3471 if ( Order1.hasValue() )
3473 nOrder1 = ::comphelper::getINT16(Order1);
3474 if ( nOrder1 == excel::XlSortOrder::xlAscending )
3475 aSortParam.maKeyState[0].bAscending = true;
3476 else
3477 aSortParam.maKeyState[0].bAscending = false;
3480 if ( Order2.hasValue() )
3482 nOrder2 = ::comphelper::getINT16(Order2);
3483 if ( nOrder2 == excel::XlSortOrder::xlAscending )
3484 aSortParam.maKeyState[1].bAscending = true;
3485 else
3486 aSortParam.maKeyState[1].bAscending = false;
3488 if ( Order3.hasValue() )
3490 nOrder3 = ::comphelper::getINT16(Order3);
3491 if ( nOrder3 == excel::XlSortOrder::xlAscending )
3492 aSortParam.maKeyState[2].bAscending = true;
3493 else
3494 aSortParam.maKeyState[2].bAscending = false;
3497 uno::Reference< table::XCellRange > xKey1;
3498 uno::Reference< table::XCellRange > xKey2;
3499 uno::Reference< table::XCellRange > xKey3;
3500 ScDocShell* pDocShell = getScDocShell();
3501 xKey1 = processKey( Key1, mxContext, pDocShell );
3502 if ( !xKey1.is() )
3503 throw uno::RuntimeException("Range::Sort needs a key1 param" );
3505 if ( Key2.hasValue() )
3506 xKey2 = processKey( Key2, mxContext, pDocShell );
3507 if ( Key3.hasValue() )
3508 xKey3 = processKey( Key3, mxContext, pDocShell );
3510 uno::Reference< util::XSortable > xSort( mxRange, uno::UNO_QUERY_THROW );
3511 uno::Sequence< beans::PropertyValue > sortDescriptor = xSort->createSortDescriptor();
3512 sal_Int32 nTableSortFieldIndex = findSortPropertyIndex( sortDescriptor, OUString( "SortFields" ) );
3514 uno::Sequence< table::TableSortField > sTableFields(1);
3515 sal_Int32 nTableIndex = 0;
3516 updateTableSortField( mxRange, xKey1, nOrder1, sTableFields[ nTableIndex++ ], bIsSortColumns, bMatchCase );
3518 if ( xKey2.is() )
3520 sTableFields.realloc( sTableFields.getLength() + 1 );
3521 updateTableSortField( mxRange, xKey2, nOrder2, sTableFields[ nTableIndex++ ], bIsSortColumns, bMatchCase );
3523 if ( xKey3.is() )
3525 sTableFields.realloc( sTableFields.getLength() + 1 );
3526 updateTableSortField( mxRange, xKey3, nOrder3, sTableFields[ nTableIndex++ ], bIsSortColumns, bMatchCase );
3528 sortDescriptor[ nTableSortFieldIndex ].Value <<= sTableFields;
3530 sal_Int32 nIndex = findSortPropertyIndex( sortDescriptor, OUString("IsSortColumns") );
3531 sortDescriptor[ nIndex ].Value <<= bIsSortColumns;
3533 nIndex = findSortPropertyIndex( sortDescriptor, CONTS_HEADER );
3534 sortDescriptor[ nIndex ].Value <<= bContainsHeader;
3536 rDoc.SetSortParam( aSortParam, nTab );
3537 xSort->sort( sortDescriptor );
3539 // #FIXME #TODO
3540 // The SortMethod param is not processed ( not sure what its all about, need to
3541 (void)nSortMethod;
3544 uno::Reference< excel::XRange > SAL_CALL
3545 ScVbaRange::End( ::sal_Int32 Direction ) throw (uno::RuntimeException, std::exception)
3547 if ( m_Areas->getCount() > 1 )
3549 uno::Reference< excel::XRange > xRange( getArea( 0 ), uno::UNO_QUERY_THROW );
3550 return xRange->End( Direction );
3553 // #FIXME #TODO
3554 // euch! found my orig implementation sucked, so
3555 // trying this even suckier one ( really need to use/expose code in
3556 // around ScTabView::MoveCursorArea(), thats the bit that calcutes
3557 // where the cursor should go )
3558 // Main problem with this method is the ultra hacky attempt to preserve
3559 // the ActiveCell, there should be no need to go to these extremes
3561 // Save ActiveSheet/ActiveCell pos ( to restore later )
3562 uno::Any aDft;
3563 uno::Reference< excel::XApplication > xApplication( Application(), uno::UNO_QUERY_THROW );
3564 uno::Reference< excel::XWorksheet > sActiveSheet = xApplication->getActiveSheet();
3565 OUString sActiveCell = xApplication->getActiveCell()->Address(aDft, aDft, aDft, aDft, aDft );
3567 // position current cell upper left of this range
3568 Cells( uno::makeAny( (sal_Int32) 1 ), uno::makeAny( (sal_Int32) 1 ) )->Select();
3570 uno::Reference< frame::XModel > xModel = getModelFromRange( mxRange );
3572 SfxViewFrame* pViewFrame = excel::getViewFrame( xModel );
3573 if ( pViewFrame )
3575 SfxAllItemSet aArgs( SfxGetpApp()->GetPool() );
3576 // Hoping this will make sure this slot is called
3577 // synchronously
3578 SfxBoolItem sfxAsync( SID_ASYNCHRON, false );
3579 aArgs.Put( sfxAsync, sfxAsync.Which() );
3580 SfxDispatcher* pDispatcher = pViewFrame->GetDispatcher();
3582 sal_uInt16 nSID = 0;
3584 switch( Direction )
3586 case excel::XlDirection::xlDown:
3587 nSID = SID_CURSORBLKDOWN;
3588 break;
3589 case excel::XlDirection::xlUp:
3590 nSID = SID_CURSORBLKUP;
3591 break;
3592 case excel::XlDirection::xlToLeft:
3593 nSID = SID_CURSORBLKLEFT;
3594 break;
3595 case excel::XlDirection::xlToRight:
3596 nSID = SID_CURSORBLKRIGHT;
3597 break;
3598 default:
3599 throw uno::RuntimeException(": Invalid ColumnIndex" );
3601 if ( pDispatcher )
3603 pDispatcher->Execute( nSID, (SfxCallMode)SfxCallMode::SYNCHRON, aArgs );
3607 // result is the ActiveCell
3608 OUString sMoved = xApplication->getActiveCell()->Address(aDft, aDft, aDft, aDft, aDft );
3610 uno::Any aVoid;
3611 uno::Reference< excel::XRange > resultCell;
3612 resultCell.set( xApplication->getActiveSheet()->Range( uno::makeAny( sMoved ), aVoid ), uno::UNO_QUERY_THROW );
3614 // restore old ActiveCell
3615 uno::Reference< excel::XRange > xOldActiveCell( sActiveSheet->Range( uno::makeAny( sActiveCell ), aVoid ), uno::UNO_QUERY_THROW );
3616 xOldActiveCell->Select();
3619 // return result
3620 return resultCell;
3623 bool
3624 ScVbaRange::isSingleCellRange()
3626 uno::Reference< sheet::XCellRangeAddressable > xAddressable( mxRange, uno::UNO_QUERY );
3627 if ( xAddressable.is() )
3629 table::CellRangeAddress aRangeAddr = xAddressable->getRangeAddress();
3630 return ( aRangeAddr.EndColumn == aRangeAddr.StartColumn && aRangeAddr.EndRow == aRangeAddr.StartRow );
3632 return false;
3635 uno::Reference< excel::XCharacters > SAL_CALL
3636 ScVbaRange::characters( const uno::Any& Start, const uno::Any& Length ) throw (uno::RuntimeException, std::exception)
3638 if ( !isSingleCellRange() )
3639 throw uno::RuntimeException("Can't create Characters property for multicell range " );
3640 uno::Reference< text::XSimpleText > xSimple(mxRange->getCellByPosition(0,0) , uno::UNO_QUERY_THROW );
3641 ScDocument& rDoc = getDocumentFromRange(mxRange);
3643 ScVbaPalette aPalette( rDoc.GetDocumentShell() );
3644 return new ScVbaCharacters( this, mxContext, aPalette, xSimple, Start, Length );
3647 void SAL_CALL
3648 ScVbaRange::Delete( const uno::Any& Shift ) throw (uno::RuntimeException, std::exception)
3650 if ( m_Areas->getCount() > 1 )
3652 sal_Int32 nItems = m_Areas->getCount();
3653 for ( sal_Int32 index=1; index <= nItems; ++index )
3655 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
3656 xRange->Delete( Shift );
3658 return;
3660 sheet::CellDeleteMode mode = sheet::CellDeleteMode_NONE ;
3661 RangeHelper thisRange( mxRange );
3662 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3663 if ( Shift.hasValue() )
3665 sal_Int32 nShift = 0;
3666 Shift >>= nShift;
3667 switch ( nShift )
3669 case excel::XlDeleteShiftDirection::xlShiftUp:
3670 mode = sheet::CellDeleteMode_UP;
3671 break;
3672 case excel::XlDeleteShiftDirection::xlShiftToLeft:
3673 mode = sheet::CellDeleteMode_LEFT;
3674 break;
3675 default:
3676 throw uno::RuntimeException("Illegal parameter " );
3679 else
3681 bool bFullRow = ( thisAddress.StartColumn == 0 && thisAddress.EndColumn == MAXCOL );
3682 sal_Int32 nCols = thisAddress.EndColumn - thisAddress.StartColumn;
3683 sal_Int32 nRows = thisAddress.EndRow - thisAddress.StartRow;
3684 if ( mbIsRows || bFullRow || ( nCols >= nRows ) )
3685 mode = sheet::CellDeleteMode_UP;
3686 else
3687 mode = sheet::CellDeleteMode_LEFT;
3689 uno::Reference< sheet::XCellRangeMovement > xCellRangeMove( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
3690 xCellRangeMove->removeRange( thisAddress, mode );
3694 //XElementAccess
3695 sal_Bool SAL_CALL
3696 ScVbaRange::hasElements() throw (uno::RuntimeException, std::exception)
3698 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY );
3699 if ( xColumnRowRange.is() )
3700 if ( xColumnRowRange->getRows()->getCount() ||
3701 xColumnRowRange->getColumns()->getCount() )
3702 return sal_True;
3703 return false;
3706 // XEnumerationAccess
3707 uno::Reference< container::XEnumeration > SAL_CALL
3708 ScVbaRange::createEnumeration() throw (uno::RuntimeException, std::exception)
3710 if ( mbIsColumns || mbIsRows )
3712 uno::Reference< table::XColumnRowRange > xColumnRowRange(mxRange, uno::UNO_QUERY );
3713 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
3714 sal_Int32 nElems = 0;
3715 if ( mbIsColumns )
3716 nElems = xColumnRowRange->getColumns()->getCount();
3717 else
3718 nElems = xColumnRowRange->getRows()->getCount();
3719 return new ColumnsRowEnumeration( mxContext, xRange, nElems );
3722 return new CellsEnumeration( mxParent, mxContext, m_Areas );
3725 OUString SAL_CALL
3726 ScVbaRange::getDefaultMethodName( ) throw (uno::RuntimeException, std::exception)
3728 return OUString( "Item" );
3731 // returns calc internal col. width ( in points )
3732 double
3733 ScVbaRange::getCalcColWidth(const table::CellRangeAddress& rAddress)
3734 throw (uno::RuntimeException, std::exception)
3736 ScDocument& rDoc = getScDocument();
3737 sal_uInt16 nWidth = rDoc.GetOriginalWidth( static_cast< SCCOL >( rAddress.StartColumn ), static_cast< SCTAB >( rAddress.Sheet ) );
3738 double nPoints = lcl_TwipsToPoints( nWidth );
3739 nPoints = lcl_Round2DecPlaces( nPoints );
3740 return nPoints;
3743 double
3744 ScVbaRange::getCalcRowHeight(const table::CellRangeAddress& rAddress)
3745 throw (uno::RuntimeException, std::exception)
3747 ScDocument& rDoc = getDocumentFromRange( mxRange );
3748 sal_uInt16 nWidth = rDoc.GetOriginalHeight( rAddress.StartRow, rAddress.Sheet );
3749 double nPoints = lcl_TwipsToPoints( nWidth );
3750 nPoints = lcl_Round2DecPlaces( nPoints );
3751 return nPoints;
3754 // return Char Width in points
3755 double getDefaultCharWidth( ScDocShell* pDocShell )
3757 ScDocument& rDoc = pDocShell->GetDocument();
3758 OutputDevice* pRefDevice = rDoc.GetRefDevice();
3759 ScPatternAttr* pAttr = rDoc.GetDefPattern();
3760 vcl::Font aDefFont;
3761 pAttr->GetFont( aDefFont, SC_AUTOCOL_BLACK, pRefDevice );
3762 pRefDevice->SetFont( aDefFont );
3763 long nCharWidth = pRefDevice->GetTextWidth( OUString( '0' ) ); // 1/100th mm
3764 return lcl_hmmToPoints( nCharWidth );
3767 uno::Any SAL_CALL
3768 ScVbaRange::getColumnWidth() throw (uno::RuntimeException, std::exception)
3770 sal_Int32 nLen = m_Areas->getCount();
3771 if ( nLen > 1 )
3773 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
3774 return xRange->getColumnWidth();
3777 double nColWidth = 0;
3778 ScDocShell* pShell = getScDocShell();
3779 if ( pShell )
3781 double defaultCharWidth = getDefaultCharWidth( pShell );
3782 RangeHelper thisRange( mxRange );
3783 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3784 sal_Int32 nStartCol = thisAddress.StartColumn;
3785 sal_Int32 nEndCol = thisAddress.EndColumn;
3786 sal_uInt16 nColTwips = 0;
3787 for( sal_Int32 nCol = nStartCol ; nCol <= nEndCol; ++nCol )
3789 thisAddress.StartColumn = nCol;
3790 sal_uInt16 nCurTwips = pShell->GetDocument().GetOriginalWidth( static_cast< SCCOL >( thisAddress.StartColumn ), static_cast< SCTAB >( thisAddress.Sheet ) );
3791 if ( nCol == nStartCol )
3792 nColTwips = nCurTwips;
3793 if ( nColTwips != nCurTwips )
3794 return aNULL();
3796 nColWidth = lcl_TwipsToPoints( nColTwips );
3797 if ( nColWidth != 0.0 )
3798 nColWidth = ( nColWidth / defaultCharWidth ) - fExtraWidth;
3800 nColWidth = lcl_Round2DecPlaces( nColWidth );
3801 return uno::makeAny( nColWidth );
3804 void SAL_CALL
3805 ScVbaRange::setColumnWidth( const uno::Any& _columnwidth ) throw (uno::RuntimeException, std::exception)
3807 sal_Int32 nLen = m_Areas->getCount();
3808 if ( nLen > 1 )
3810 for ( sal_Int32 index = 1; index != nLen; ++index )
3812 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(index) ), uno::Any() ), uno::UNO_QUERY_THROW );
3813 xRange->setColumnWidth( _columnwidth );
3815 return;
3817 double nColWidth = 0;
3818 _columnwidth >>= nColWidth;
3819 nColWidth = lcl_Round2DecPlaces( nColWidth );
3820 ScDocShell* pDocShell = getScDocShell();
3821 if ( pDocShell )
3823 if ( nColWidth != 0.0 )
3824 nColWidth = ( nColWidth + fExtraWidth ) * getDefaultCharWidth( pDocShell );
3825 RangeHelper thisRange( mxRange );
3826 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3827 sal_uInt16 nTwips = lcl_pointsToTwips( nColWidth );
3829 std::vector<sc::ColRowSpan> aColArr(1, sc::ColRowSpan(thisAddress.StartColumn, thisAddress.EndColumn));
3830 // #163561# use mode SC_SIZE_DIRECT: hide for width 0, show for other values
3831 pDocShell->GetDocFunc().SetWidthOrHeight(
3832 true, aColArr, thisAddress.Sheet, SC_SIZE_DIRECT, nTwips, true, true);
3836 uno::Any SAL_CALL
3837 ScVbaRange::getWidth() throw (uno::RuntimeException, std::exception)
3839 if ( m_Areas->getCount() > 1 )
3841 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
3842 return xRange->getWidth();
3844 uno::Reference< table::XColumnRowRange > xColRowRange( mxRange, uno::UNO_QUERY_THROW );
3845 uno::Reference< container::XIndexAccess > xIndexAccess( xColRowRange->getColumns(), uno::UNO_QUERY_THROW );
3846 sal_Int32 nElems = xIndexAccess->getCount();
3847 double nWidth = 0;
3848 for ( sal_Int32 index=0; index<nElems; ++index )
3850 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xIndexAccess->getByIndex( index ), uno::UNO_QUERY_THROW );
3851 double nTmpWidth = getCalcColWidth( xAddressable->getRangeAddress() );
3852 nWidth += nTmpWidth;
3854 return uno::makeAny( nWidth );
3857 uno::Any SAL_CALL
3858 ScVbaRange::Areas( const uno::Any& item) throw (uno::RuntimeException, std::exception)
3860 if ( !item.hasValue() )
3861 return uno::makeAny( m_Areas );
3862 return m_Areas->Item( item, uno::Any() );
3865 uno::Reference< excel::XRange >
3866 ScVbaRange::getArea( sal_Int32 nIndex ) throw( css::uno::RuntimeException )
3868 if ( !m_Areas.is() )
3869 throw uno::RuntimeException("No areas available" );
3870 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( ++nIndex ), uno::Any() ), uno::UNO_QUERY_THROW );
3871 return xRange;
3874 uno::Any
3875 ScVbaRange::Borders( const uno::Any& item ) throw( script::BasicErrorException, uno::RuntimeException )
3877 if ( !item.hasValue() )
3878 return uno::makeAny( getBorders() );
3879 return getBorders()->Item( item, uno::Any() );
3882 uno::Any SAL_CALL
3883 ScVbaRange::BorderAround( const css::uno::Any& LineStyle, const css::uno::Any& Weight,
3884 const css::uno::Any& ColorIndex, const css::uno::Any& Color ) throw (css::uno::RuntimeException, std::exception)
3886 sal_Int32 nCount = getBorders()->getCount();
3888 for( sal_Int32 i = 0; i < nCount; i++ )
3890 const sal_Int32 nLineType = supportedIndexTable[i];
3891 switch( nLineType )
3893 case excel::XlBordersIndex::xlEdgeLeft:
3894 case excel::XlBordersIndex::xlEdgeTop:
3895 case excel::XlBordersIndex::xlEdgeBottom:
3896 case excel::XlBordersIndex::xlEdgeRight:
3898 uno::Reference< excel::XBorder > xBorder( m_Borders->Item( uno::makeAny( nLineType ), uno::Any() ), uno::UNO_QUERY_THROW );
3899 if( LineStyle.hasValue() )
3901 xBorder->setLineStyle( LineStyle );
3903 if( Weight.hasValue() )
3905 xBorder->setWeight( Weight );
3907 if( ColorIndex.hasValue() )
3909 xBorder->setColorIndex( ColorIndex );
3911 if( Color.hasValue() )
3913 xBorder->setColor( Color );
3915 break;
3917 case excel::XlBordersIndex::xlInsideVertical:
3918 case excel::XlBordersIndex::xlInsideHorizontal:
3919 case excel::XlBordersIndex::xlDiagonalDown:
3920 case excel::XlBordersIndex::xlDiagonalUp:
3921 break;
3922 default:
3923 return uno::makeAny( false );
3926 return uno::makeAny( sal_True );
3929 uno::Any SAL_CALL
3930 ScVbaRange::getRowHeight()
3931 throw (uno::RuntimeException, std::exception)
3933 sal_Int32 nLen = m_Areas->getCount();
3934 if ( nLen > 1 )
3936 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
3937 return xRange->getRowHeight();
3940 // if any row's RowHeight in the
3941 // range is different from any other then return NULL
3942 RangeHelper thisRange( mxRange );
3943 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3945 sal_Int32 nStartRow = thisAddress.StartRow;
3946 sal_Int32 nEndRow = thisAddress.EndRow;
3947 sal_uInt16 nRowTwips = 0;
3948 // #TODO probably possible to use the SfxItemSet ( and see if
3949 // SfxItemState::DONTCARE is set ) to improve performance
3950 // #CHECKME looks like this is general behaviour not just row Range specific
3951 // if ( mbIsRows )
3952 ScDocShell* pShell = getScDocShell();
3953 if ( pShell )
3955 for ( sal_Int32 nRow = nStartRow ; nRow <= nEndRow; ++nRow )
3957 thisAddress.StartRow = nRow;
3958 sal_uInt16 nCurTwips = pShell->GetDocument().GetOriginalHeight( thisAddress.StartRow, thisAddress.Sheet );
3959 if ( nRow == nStartRow )
3960 nRowTwips = nCurTwips;
3961 if ( nRowTwips != nCurTwips )
3962 return aNULL();
3965 double nHeight = lcl_Round2DecPlaces( lcl_TwipsToPoints( nRowTwips ) );
3966 return uno::makeAny( nHeight );
3969 void SAL_CALL
3970 ScVbaRange::setRowHeight( const uno::Any& _rowheight) throw (uno::RuntimeException, std::exception)
3972 sal_Int32 nLen = m_Areas->getCount();
3973 if ( nLen > 1 )
3975 for ( sal_Int32 index = 1; index != nLen; ++index )
3977 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(index) ), uno::Any() ), uno::UNO_QUERY_THROW );
3978 xRange->setRowHeight( _rowheight );
3980 return;
3982 double nHeight = 0; // Incoming height is in points
3983 _rowheight >>= nHeight;
3984 nHeight = lcl_Round2DecPlaces( nHeight );
3985 RangeHelper thisRange( mxRange );
3986 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
3987 sal_uInt16 nTwips = lcl_pointsToTwips( nHeight );
3989 ScDocShell* pDocShell = getDocShellFromRange( mxRange );
3990 std::vector<sc::ColRowSpan> aRowArr(1, sc::ColRowSpan(thisAddress.StartRow, thisAddress.EndRow));
3991 pDocShell->GetDocFunc().SetWidthOrHeight(
3992 false, aRowArr, thisAddress.Sheet, SC_SIZE_ORIGINAL, nTwips, true, true);
3995 uno::Any SAL_CALL
3996 ScVbaRange::getPageBreak() throw (uno::RuntimeException, std::exception)
3998 sal_Int32 nPageBreak = excel::XlPageBreak::xlPageBreakNone;
3999 ScDocShell* pShell = getDocShellFromRange( mxRange );
4000 if ( pShell )
4002 RangeHelper thisRange( mxRange );
4003 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4004 bool bColumn = false;
4006 if (thisAddress.StartRow==0)
4007 bColumn = true;
4009 uno::Reference< frame::XModel > xModel = pShell->GetModel();
4010 if ( xModel.is() )
4012 ScDocument& rDoc = getDocumentFromRange( mxRange );
4014 ScBreakType nBreak = BREAK_NONE;
4015 if ( !bColumn )
4016 nBreak = rDoc.HasRowBreak(thisAddress.StartRow, thisAddress.Sheet);
4017 else
4018 nBreak = rDoc.HasColBreak(thisAddress.StartColumn, thisAddress.Sheet);
4020 if (nBreak & BREAK_PAGE)
4021 nPageBreak = excel::XlPageBreak::xlPageBreakAutomatic;
4023 if (nBreak & BREAK_MANUAL)
4024 nPageBreak = excel::XlPageBreak::xlPageBreakManual;
4028 return uno::makeAny( nPageBreak );
4031 void SAL_CALL
4032 ScVbaRange::setPageBreak( const uno::Any& _pagebreak) throw (uno::RuntimeException, std::exception)
4034 sal_Int32 nPageBreak = 0;
4035 _pagebreak >>= nPageBreak;
4037 ScDocShell* pShell = getDocShellFromRange( mxRange );
4038 if ( pShell )
4040 RangeHelper thisRange( mxRange );
4041 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4042 if ((thisAddress.StartColumn==0) && (thisAddress.StartRow==0))
4043 return;
4044 bool bColumn = false;
4046 if (thisAddress.StartRow==0)
4047 bColumn = true;
4049 ScAddress aAddr( static_cast<SCCOL>(thisAddress.StartColumn), thisAddress.StartRow, thisAddress.Sheet );
4050 uno::Reference< frame::XModel > xModel = pShell->GetModel();
4051 if ( xModel.is() )
4053 ScTabViewShell* pViewShell = excel::getBestViewShell( xModel );
4054 if ( nPageBreak == excel::XlPageBreak::xlPageBreakManual )
4055 pViewShell->InsertPageBreak( bColumn, true, &aAddr);
4056 else if ( nPageBreak == excel::XlPageBreak::xlPageBreakNone )
4057 pViewShell->DeletePageBreak( bColumn, true, &aAddr);
4062 uno::Any SAL_CALL
4063 ScVbaRange::getHeight() throw (uno::RuntimeException, std::exception)
4065 if ( m_Areas->getCount() > 1 )
4067 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(1) ), uno::Any() ), uno::UNO_QUERY_THROW );
4068 return xRange->getHeight();
4071 uno::Reference< table::XColumnRowRange > xColRowRange( mxRange, uno::UNO_QUERY_THROW );
4072 uno::Reference< container::XIndexAccess > xIndexAccess( xColRowRange->getRows(), uno::UNO_QUERY_THROW );
4073 sal_Int32 nElems = xIndexAccess->getCount();
4074 double nHeight = 0;
4075 for ( sal_Int32 index=0; index<nElems; ++index )
4077 uno::Reference< sheet::XCellRangeAddressable > xAddressable( xIndexAccess->getByIndex( index ), uno::UNO_QUERY_THROW );
4078 nHeight += getCalcRowHeight(xAddressable->getRangeAddress() );
4080 return uno::makeAny( nHeight );
4083 awt::Point
4084 ScVbaRange::getPosition() throw ( uno::RuntimeException )
4086 awt::Point aPoint;
4087 uno::Reference< beans::XPropertySet > xProps;
4088 if ( mxRange.is() )
4089 xProps.set( mxRange, uno::UNO_QUERY_THROW );
4090 else
4091 xProps.set( mxRanges, uno::UNO_QUERY_THROW );
4092 xProps->getPropertyValue( POSITION ) >>= aPoint;
4093 return aPoint;
4095 uno::Any SAL_CALL
4096 ScVbaRange::getLeft() throw (uno::RuntimeException, std::exception)
4098 // helperapi returns the first ranges left ( and top below )
4099 if ( m_Areas->getCount() > 1 )
4100 return getArea( 0 )->getLeft();
4101 awt::Point aPoint = getPosition();
4102 return uno::makeAny( lcl_hmmToPoints( aPoint.X ) );
4105 uno::Any SAL_CALL
4106 ScVbaRange::getTop() throw (uno::RuntimeException, std::exception)
4108 // helperapi returns the first ranges top
4109 if ( m_Areas->getCount() > 1 )
4110 return getArea( 0 )->getTop();
4111 awt::Point aPoint= getPosition();
4112 return uno::makeAny( lcl_hmmToPoints( aPoint.Y ) );
4115 uno::Reference< sheet::XCellRangeReferrer > getNamedRange( const uno::Reference< uno::XInterface >& xIf, const uno::Reference< table::XCellRange >& thisRange )
4117 uno::Reference< beans::XPropertySet > xProps( xIf, uno::UNO_QUERY_THROW );
4118 uno::Reference< container::XNameAccess > xNameAccess( xProps->getPropertyValue( "NamedRanges" ), uno::UNO_QUERY_THROW );
4120 uno::Sequence< OUString > sNames = xNameAccess->getElementNames();
4121 // uno::Reference< table::XCellRange > thisRange( getCellRange(), uno::UNO_QUERY_THROW );
4122 uno::Reference< sheet::XCellRangeReferrer > xNamedRange;
4123 for ( sal_Int32 i=0; i < sNames.getLength(); ++i )
4125 uno::Reference< sheet::XCellRangeReferrer > xName( xNameAccess->getByName( sNames[ i ] ), uno::UNO_QUERY );
4126 if ( xName.is() )
4128 if ( thisRange == xName->getReferredCells() )
4130 xNamedRange = xName;
4131 break;
4135 return xNamedRange;
4138 uno::Reference< excel::XName >
4139 ScVbaRange::getName() throw (uno::RuntimeException, std::exception)
4141 uno::Reference< beans::XPropertySet > xProps( getUnoModel(), uno::UNO_QUERY );
4142 uno::Reference< table::XCellRange > thisRange( getCellRange(), uno::UNO_QUERY_THROW );
4143 // Application range
4144 uno::Reference< sheet::XCellRangeReferrer > xNamedRange = getNamedRange( xProps, thisRange );
4146 if ( !xNamedRange.is() )
4148 // not in application range then assume it might be in
4149 // sheet namedranges
4150 RangeHelper aRange( thisRange );
4151 uno::Reference< sheet::XSpreadsheet > xSheet = aRange.getSpreadSheet();
4152 xProps.set( xSheet, uno::UNO_QUERY );
4153 // impl here
4154 xNamedRange = getNamedRange( xProps, thisRange );
4156 if ( xProps.is() && xNamedRange.is() )
4158 uno::Reference< sheet::XNamedRanges > xNamedRanges( xProps, uno::UNO_QUERY_THROW );
4159 uno::Reference< sheet::XNamedRange > xName( xNamedRange, uno::UNO_QUERY_THROW );
4160 return new ScVbaName( mxParent, mxContext, xName, xNamedRanges, getUnoModel() );
4162 return uno::Reference< excel::XName >();
4165 uno::Reference< excel::XWorksheet >
4166 ScVbaRange::getWorksheet() throw (uno::RuntimeException, std::exception)
4168 // #TODO #FIXME parent should always be set up ( currently thats not
4169 // the case )
4170 uno::Reference< excel::XWorksheet > xSheet( getParent(), uno::UNO_QUERY );
4171 if ( !xSheet.is() )
4173 uno::Reference< table::XCellRange > xRange = mxRange;
4175 if ( mxRanges.is() ) // assign xRange to first range
4177 uno::Reference< container::XIndexAccess > xIndex( mxRanges, uno::UNO_QUERY_THROW );
4178 xRange.set( xIndex->getByIndex( 0 ), uno::UNO_QUERY_THROW );
4180 ScDocShell* pDocShell = getDocShellFromRange(xRange);
4181 RangeHelper rHelper(xRange);
4182 // parent should be Thisworkbook
4183 xSheet.set( new ScVbaWorksheet( uno::Reference< XHelperInterface >(), mxContext,rHelper.getSpreadSheet(),pDocShell->GetModel()) );
4185 return xSheet;
4188 // #TODO remove this ugly application processing
4189 // Process an application Range request e.g. 'Range("a1,b2,a4:b6")
4190 uno::Reference< excel::XRange >
4191 ScVbaRange::ApplicationRange( const uno::Reference< uno::XComponentContext >& xContext, const css::uno::Any &Cell1, const css::uno::Any &Cell2 ) throw (css::uno::RuntimeException)
4193 // Although the documentation seems clear that Range without a
4194 // qualifier then it's a shortcut for ActiveSheet.Range
4195 // however, similarly Application.Range is apparently also a
4196 // shortcut for ActiveSheet.Range
4197 // The is however a subtle behavioural difference I've come across
4198 // wrt to named ranges.
4199 // If a named range "test" exists { Sheet1!$A1 } and the active sheet
4200 // is Sheet2 then the following will fail
4201 // msgbox ActiveSheet.Range("test").Address ' failes
4202 // msgbox WorkSheets("Sheet2").Range("test").Address
4203 // but !!!
4204 // msgbox Range("test").Address ' works
4205 // msgbox Application.Range("test").Address ' works
4207 // Single param Range
4208 OUString sRangeName;
4209 Cell1 >>= sRangeName;
4210 if ( Cell1.hasValue() && !Cell2.hasValue() && !sRangeName.isEmpty() )
4212 static const char sNamedRanges[] = "NamedRanges";
4213 uno::Reference< beans::XPropertySet > xPropSet( getCurrentExcelDoc(xContext), uno::UNO_QUERY_THROW );
4215 uno::Reference< container::XNameAccess > xNamed( xPropSet->getPropertyValue( sNamedRanges ), uno::UNO_QUERY_THROW );
4216 uno::Reference< sheet::XCellRangeReferrer > xReferrer;
4219 xReferrer.set ( xNamed->getByName( sRangeName ), uno::UNO_QUERY );
4221 catch( uno::Exception& /*e*/ )
4223 // do nothing
4225 if ( xReferrer.is() )
4227 uno::Reference< table::XCellRange > xRange = xReferrer->getReferredCells();
4228 if ( xRange.is() )
4230 uno::Reference< excel::XRange > xVbRange = new ScVbaRange( excel::getUnoSheetModuleObj( xRange ), xContext, xRange );
4231 return xVbRange;
4236 uno::Reference< sheet::XSpreadsheetView > xView( getCurrentExcelDoc(xContext)->getCurrentController(), uno::UNO_QUERY );
4237 uno::Reference< table::XCellRange > xSheetRange( xView->getActiveSheet(), uno::UNO_QUERY_THROW );
4238 ScVbaRange* pRange = new ScVbaRange( excel::getUnoSheetModuleObj( xSheetRange ), xContext, xSheetRange );
4239 uno::Reference< excel::XRange > xVbSheetRange( pRange );
4240 return pRange->Range( Cell1, Cell2, true );
4243 // Helper functions for AutoFilter
4244 static ScDBData* lcl_GetDBData_Impl( ScDocShell* pDocShell, sal_Int16 nSheet )
4246 ScDBData* pRet = NULL;
4247 if (pDocShell)
4249 pRet = pDocShell->GetDocument().GetAnonymousDBData(nSheet);
4251 return pRet;
4254 static void lcl_SelectAll( ScDocShell* pDocShell, ScQueryParam& aParam )
4256 if ( pDocShell )
4258 ScViewData* pViewData = ScDocShell::GetViewData();
4259 if ( pViewData )
4261 OSL_TRACE("Pushing out SelectAll query");
4262 pViewData->GetView()->Query( aParam, NULL, true );
4267 static ScQueryParam lcl_GetQueryParam( ScDocShell* pDocShell, sal_Int16 nSheet )
4269 ScDBData* pDBData = lcl_GetDBData_Impl( pDocShell, nSheet );
4270 ScQueryParam aParam;
4271 if (pDBData)
4273 pDBData->GetQueryParam( aParam );
4275 return aParam;
4278 static void lcl_SetAllQueryForField( ScDocShell* pDocShell, SCCOLROW nField, sal_Int16 nSheet )
4280 ScQueryParam aParam = lcl_GetQueryParam( pDocShell, nSheet );
4281 aParam.RemoveEntryByField(nField);
4282 lcl_SelectAll( pDocShell, aParam );
4285 // Modifies sCriteria, and nOp depending on the value of sCriteria
4286 static void lcl_setTableFieldsFromCriteria( OUString& sCriteria1, uno::Reference< beans::XPropertySet >& xDescProps, sheet::TableFilterField2& rFilterField )
4288 // #TODO make this more efficient and cycle through
4289 // sCriteria1 character by character to pick up <,<>,=, * etc.
4290 // right now I am more concerned with just getting it to work right
4292 sCriteria1 = sCriteria1.trim();
4293 // table of translation of criteria text to FilterOperators
4294 // <>searchtext - NOT_EQUAL
4295 // =searchtext - EQUAL
4296 // *searchtext - startwith
4297 // <>*searchtext - doesn't startwith
4298 // *searchtext* - contains
4299 // <>*searchtext* - doesn't contain
4300 // [>|>=|<=|...]searchtext for GREATER_value, GREATER_EQUAL_value etc.
4301 bool bIsNumeric = false;
4302 if ( sCriteria1.startsWith( EQUALS ) )
4304 if ( sCriteria1.getLength() == (sal_Int32)strlen(EQUALS) )
4305 rFilterField.Operator = sheet::FilterOperator2::EMPTY;
4306 else
4308 rFilterField.Operator = sheet::FilterOperator2::EQUAL;
4309 sCriteria1 = sCriteria1.copy( strlen(EQUALS) );
4310 sCriteria1 = VBAToRegexp( sCriteria1 );
4311 // UseRegularExpressions
4312 if ( xDescProps.is() )
4313 xDescProps->setPropertyValue( "UseRegularExpressions", uno::Any( sal_True ) );
4317 else if ( sCriteria1.startsWith( NOTEQUALS ) )
4319 if ( sCriteria1.getLength() == (sal_Int32)strlen(NOTEQUALS) )
4320 rFilterField.Operator = sheet::FilterOperator2::NOT_EMPTY;
4321 else
4323 rFilterField.Operator = sheet::FilterOperator2::NOT_EQUAL;
4324 sCriteria1 = sCriteria1.copy( strlen(NOTEQUALS) );
4325 sCriteria1 = VBAToRegexp( sCriteria1 );
4326 // UseRegularExpressions
4327 if ( xDescProps.is() )
4328 xDescProps->setPropertyValue( "UseRegularExpressions", uno::Any( sal_True ) );
4331 else if ( sCriteria1.startsWith( GREATERTHAN ) )
4333 bIsNumeric = true;
4334 if ( sCriteria1.startsWith( GREATERTHANEQUALS ) )
4336 sCriteria1 = sCriteria1.copy( strlen(GREATERTHANEQUALS) );
4337 rFilterField.Operator = sheet::FilterOperator2::GREATER_EQUAL;
4339 else
4341 sCriteria1 = sCriteria1.copy( strlen(GREATERTHAN) );
4342 rFilterField.Operator = sheet::FilterOperator2::GREATER;
4346 else if ( sCriteria1.startsWith( LESSTHAN ) )
4348 bIsNumeric = true;
4349 if ( sCriteria1.startsWith( LESSTHANEQUALS ) )
4351 sCriteria1 = sCriteria1.copy( strlen(LESSTHANEQUALS) );
4352 rFilterField.Operator = sheet::FilterOperator2::LESS_EQUAL;
4354 else
4356 sCriteria1 = sCriteria1.copy( strlen(LESSTHAN) );
4357 rFilterField.Operator = sheet::FilterOperator2::LESS;
4361 else
4362 rFilterField.Operator = sheet::FilterOperator2::EQUAL;
4364 if ( bIsNumeric )
4366 rFilterField.IsNumeric= sal_True;
4367 rFilterField.NumericValue = sCriteria1.toDouble();
4369 rFilterField.StringValue = sCriteria1;
4372 void SAL_CALL
4373 ScVbaRange::AutoFilter( const uno::Any& aField, const uno::Any& Criteria1, const uno::Any& Operator, const uno::Any& Criteria2, const uno::Any& VisibleDropDown )
4374 throw (uno::RuntimeException, std::exception)
4376 // Is there an existing autofilter
4377 RangeHelper thisRange( mxRange );
4378 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4379 sal_Int16 nSheet = thisAddress.Sheet;
4380 ScDocShell* pShell = getScDocShell();
4381 bool bHasAuto = false;
4382 uno::Reference< sheet::XDatabaseRange > xDataBaseRange = excel::GetAutoFiltRange( pShell, nSheet );
4383 if ( xDataBaseRange.is() )
4384 bHasAuto = true;
4386 uno::Reference< table::XCellRange > xFilterRange;
4387 if ( !bHasAuto )
4389 if ( m_Areas->getCount() > 1 )
4390 throw uno::RuntimeException( STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY );
4392 table::CellRangeAddress autoFiltAddress;
4393 //CurrentRegion()
4394 if ( isSingleCellRange() )
4396 uno::Reference< excel::XRange > xCurrent( CurrentRegion() );
4397 if ( xCurrent.is() )
4399 ScVbaRange* pRange = getImplementation( xCurrent );
4400 if ( pRange )
4402 if ( pRange->isSingleCellRange() )
4403 throw uno::RuntimeException("Can't create AutoFilter" );
4404 RangeHelper currentRegion( pRange->mxRange );
4405 autoFiltAddress = currentRegion.getCellRangeAddressable()->getRangeAddress();
4409 else // multi-cell range
4411 RangeHelper multiCellRange( mxRange );
4412 autoFiltAddress = multiCellRange.getCellRangeAddressable()->getRangeAddress();
4413 // #163530# Filter box shows only entry of first row
4414 ScDocument* pDocument = ( pShell ? &pShell->GetDocument() : NULL );
4415 if ( pDocument )
4417 SCCOL nStartCol = autoFiltAddress.StartColumn;
4418 SCROW nStartRow = autoFiltAddress.StartRow;
4419 SCCOL nEndCol = autoFiltAddress.EndColumn;
4420 SCROW nEndRow = autoFiltAddress.EndRow;
4421 pDocument->GetDataArea( autoFiltAddress.Sheet, nStartCol, nStartRow, nEndCol, nEndRow, true, true );
4422 autoFiltAddress.StartColumn = nStartCol;
4423 autoFiltAddress.StartRow = nStartRow;
4424 autoFiltAddress.EndColumn = nEndCol;
4425 autoFiltAddress.EndRow = nEndRow;
4429 uno::Reference< sheet::XUnnamedDatabaseRanges > xDBRanges = excel::GetUnnamedDataBaseRanges( pShell );
4430 if ( xDBRanges.is() )
4432 OSL_TRACE("Going to add new autofilter range.. sheet %i", nSheet );
4433 if ( !xDBRanges->hasByTable( nSheet ) )
4434 xDBRanges->setByTable( autoFiltAddress );
4435 xDataBaseRange.set( xDBRanges->getByTable(nSheet ), uno::UNO_QUERY_THROW );
4437 if ( !xDataBaseRange.is() )
4438 throw uno::RuntimeException("Failed to find the autofilter placeholder range" );
4440 uno::Reference< beans::XPropertySet > xDBRangeProps( xDataBaseRange, uno::UNO_QUERY_THROW );
4441 // set autofilt
4442 xDBRangeProps->setPropertyValue( "AutoFilter", uno::Any(sal_True) );
4443 // set header (autofilter always need column headers)
4444 uno::Reference< beans::XPropertySet > xFiltProps( xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY_THROW );
4445 bool bHasColHeader = false;
4446 ScDocument* pDoc = pShell ? &pShell->GetDocument() : NULL;
4447 if (pDoc)
4449 bHasColHeader = pDoc->HasColHeader( static_cast< SCCOL >( autoFiltAddress.StartColumn ), static_cast< SCROW >( autoFiltAddress.StartRow ), static_cast< SCCOL >( autoFiltAddress.EndColumn ), static_cast< SCROW >( autoFiltAddress.EndRow ), static_cast< SCTAB >( autoFiltAddress.Sheet ) );
4451 xFiltProps->setPropertyValue( "ContainsHeader", uno::Any( bHasColHeader ) );
4454 sal_Int32 nField = 0; // *IS* 1 based
4455 OUString sCriteria1;
4456 sal_Int32 nOperator = excel::XlAutoFilterOperator::xlAnd;
4458 bool bVisible = true;
4459 VisibleDropDown >>= bVisible;
4461 if ( bVisible == bHasAuto ) // dropdown is displayed/notdisplayed as required
4462 bVisible = false;
4463 sheet::FilterConnection nConn = sheet::FilterConnection_AND;
4464 double nCriteria1 = 0;
4466 bool bHasCritValue = Criteria1.hasValue();
4467 bool bCritHasNumericValue = false; // not sure if a numeric criteria is possible
4468 if ( bHasCritValue )
4469 bCritHasNumericValue = ( Criteria1 >>= nCriteria1 );
4471 if ( !aField.hasValue() && ( Criteria1.hasValue() || Operator.hasValue() || Criteria2.hasValue() ) )
4472 throw uno::RuntimeException();
4473 uno::Any Field( aField );
4474 if ( !( Field >>= nField ) )
4476 uno::Reference< script::XTypeConverter > xConverter = getTypeConverter( mxContext );
4479 Field = xConverter->convertTo( aField, cppu::UnoType<sal_Int32>::get() );
4481 catch( uno::Exception& )
4485 // Use the normal uno api, sometimes e.g. when you want to use ALL as the filter
4486 // we can't use refresh as the uno interface doesn't have a concept of ALL
4487 // in this case we just call the core calc functionality -
4488 if ( ( Field >>= nField ) )
4490 bool bAll = false;
4492 uno::Reference< sheet::XSheetFilterDescriptor2 > xDesc(
4493 xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY );
4494 if ( xDesc.is() )
4496 uno::Sequence< sheet::TableFilterField2 > sTabFilts;
4497 uno::Reference< beans::XPropertySet > xDescProps( xDesc, uno::UNO_QUERY_THROW );
4498 if ( Criteria1.hasValue() )
4500 sTabFilts.realloc( 1 );
4501 sTabFilts[0].Operator = sheet::FilterOperator2::EQUAL;// sensible default
4502 if ( !bCritHasNumericValue )
4504 Criteria1 >>= sCriteria1;
4505 sTabFilts[0].IsNumeric = bCritHasNumericValue;
4506 if ( bHasCritValue && !sCriteria1.isEmpty() )
4507 lcl_setTableFieldsFromCriteria( sCriteria1, xDescProps, sTabFilts[0] );
4508 else
4509 bAll = true;
4511 else // numeric
4513 sTabFilts[0].IsNumeric = sal_True;
4514 sTabFilts[0].NumericValue = nCriteria1;
4517 else // no value specified
4518 bAll = true;
4519 // not sure what the relationship between Criteria1 and Operator is,
4520 // e.g. can you have a Operator without a Criteria ? in openoffice it
4521 if ( Operator.hasValue() && ( Operator >>= nOperator ) )
4523 // if it's a bottom/top Ten(Percent/Value) and there
4524 // is no value specified for criteria1 set it to 10
4525 if ( !bCritHasNumericValue && sCriteria1.isEmpty() && ( nOperator != excel::XlAutoFilterOperator::xlOr ) && ( nOperator != excel::XlAutoFilterOperator::xlAnd ) )
4527 sTabFilts[0].IsNumeric = sal_True;
4528 sTabFilts[0].NumericValue = 10;
4529 bAll = false;
4531 switch ( nOperator )
4533 case excel::XlAutoFilterOperator::xlBottom10Items:
4534 sTabFilts[0].Operator = sheet::FilterOperator2::BOTTOM_VALUES;
4535 break;
4536 case excel::XlAutoFilterOperator::xlBottom10Percent:
4537 sTabFilts[0].Operator = sheet::FilterOperator2::BOTTOM_PERCENT;
4538 break;
4539 case excel::XlAutoFilterOperator::xlTop10Items:
4540 sTabFilts[0].Operator = sheet::FilterOperator2::TOP_VALUES;
4541 break;
4542 case excel::XlAutoFilterOperator::xlTop10Percent:
4543 sTabFilts[0].Operator = sheet::FilterOperator2::TOP_PERCENT;
4544 break;
4545 case excel::XlAutoFilterOperator::xlOr:
4546 nConn = sheet::FilterConnection_OR;
4547 break;
4548 case excel::XlAutoFilterOperator::xlAnd:
4549 nConn = sheet::FilterConnection_AND;
4550 break;
4551 default:
4552 throw uno::RuntimeException("UnknownOption" );
4557 if ( !bAll )
4559 sTabFilts[0].Connection = sheet::FilterConnection_AND;
4560 sTabFilts[0].Field = (nField - 1);
4562 OUString sCriteria2;
4563 if ( Criteria2.hasValue() ) // there is a Criteria2
4565 sTabFilts.realloc(2);
4566 sTabFilts[1].Field = sTabFilts[0].Field;
4567 sTabFilts[1].Connection = nConn;
4569 if ( Criteria2 >>= sCriteria2 )
4571 if ( !sCriteria2.isEmpty() )
4573 uno::Reference< beans::XPropertySet > xProps;
4574 lcl_setTableFieldsFromCriteria( sCriteria2, xProps, sTabFilts[1] );
4575 sTabFilts[1].IsNumeric = false;
4578 else // numeric
4580 Criteria2 >>= sTabFilts[1].NumericValue;
4581 sTabFilts[1].IsNumeric = sal_True;
4582 sTabFilts[1].Operator = sheet::FilterOperator2::EQUAL;
4587 xDesc->setFilterFields2( sTabFilts );
4588 if ( !bAll )
4590 xDataBaseRange->refresh();
4592 else
4593 // was 0 based now seems to be 1
4594 lcl_SetAllQueryForField( pShell, nField, nSheet );
4597 else
4599 // this is just to toggle autofilter on and off ( not to be confused with
4600 // a VisibleDropDown option combined with a field, in that case just the
4601 // button should be disabled ) - currently we don't support that
4602 uno::Reference< beans::XPropertySet > xDBRangeProps( xDataBaseRange, uno::UNO_QUERY_THROW );
4603 if ( bHasAuto )
4605 // find the any field with the query and select all
4606 ScQueryParam aParam = lcl_GetQueryParam( pShell, nSheet );
4607 for (SCSIZE i = 0; i< aParam.GetEntryCount(); ++i)
4609 ScQueryEntry& rEntry = aParam.GetEntry(i);
4610 if ( rEntry.bDoQuery )
4611 lcl_SetAllQueryForField( pShell, rEntry.nField, nSheet );
4613 // remove exising filters
4614 uno::Reference< sheet::XSheetFilterDescriptor2 > xSheetFilterDescriptor(
4615 xDataBaseRange->getFilterDescriptor(), uno::UNO_QUERY );
4616 if( xSheetFilterDescriptor.is() )
4617 xSheetFilterDescriptor->setFilterFields2( uno::Sequence< sheet::TableFilterField2 >() );
4619 xDBRangeProps->setPropertyValue( "AutoFilter", uno::Any(!bHasAuto) );
4624 void SAL_CALL
4625 ScVbaRange::Insert( const uno::Any& Shift, const uno::Any& /*CopyOrigin*/ ) throw (uno::RuntimeException, std::exception)
4627 // It appears ( from the web ) that the undocumented CopyOrigin
4628 // param should contain member of enum XlInsertFormatOrigin
4629 // which can have values xlFormatFromLeftOrAbove or xlFormatFromRightOrBelow
4630 // #TODO investigate resultant behaviour using these constants
4631 // currently just processing Shift
4633 sheet::CellInsertMode mode = sheet::CellInsertMode_NONE;
4634 if ( Shift.hasValue() )
4636 sal_Int32 nShift = 0;
4637 Shift >>= nShift;
4638 switch ( nShift )
4640 case excel::XlInsertShiftDirection::xlShiftToRight:
4641 mode = sheet::CellInsertMode_RIGHT;
4642 break;
4643 case excel::XlInsertShiftDirection::xlShiftDown:
4644 mode = sheet::CellInsertMode_DOWN;
4645 break;
4646 default:
4647 throw uno::RuntimeException("Illegal parameter " );
4650 else
4652 if ( getRow() >= getColumn() )
4653 mode = sheet::CellInsertMode_DOWN;
4654 else
4655 mode = sheet::CellInsertMode_RIGHT;
4657 RangeHelper thisRange( mxRange );
4658 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4659 uno::Reference< sheet::XCellRangeMovement > xCellRangeMove( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
4660 xCellRangeMove->insertCells( thisAddress, mode );
4662 // Paste from clipboard only if the clipboard content was copied via VBA, and not already pasted via VBA again.
4663 // "Insert" behavior should not depend on random clipboard content previously copied by the user.
4664 ScTransferObj* pClipObj = ScTransferObj::GetOwnClipboard( NULL );
4665 if ( pClipObj && pClipObj->GetUseInApi() )
4667 // After the insert ( this range ) actually has moved
4668 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 ) );
4669 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getDocShellFromRange( mxRange ) , aRange ) );
4670 uno::Reference< excel::XRange > xVbaRange( new ScVbaRange( mxParent, mxContext, xRange, mbIsRows, mbIsColumns ) );
4671 xVbaRange->PasteSpecial( uno::Any(), uno::Any(), uno::Any(), uno::Any() );
4675 void SAL_CALL
4676 ScVbaRange::Autofit() throw (script::BasicErrorException, uno::RuntimeException, std::exception)
4678 sal_Int32 nLen = m_Areas->getCount();
4679 if ( nLen > 1 )
4681 for ( sal_Int32 index = 1; index != nLen; ++index )
4683 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32(index) ), uno::Any() ), uno::UNO_QUERY_THROW );
4684 xRange->Autofit();
4686 return;
4688 // if the range is a not a row or column range autofit will
4689 // throw an error
4691 if ( !( mbIsColumns || mbIsRows ) )
4692 DebugHelper::basicexception(SbERR_METHOD_FAILED, OUString());
4693 ScDocShell* pDocShell = getDocShellFromRange( mxRange );
4694 if ( pDocShell )
4696 RangeHelper thisRange( mxRange );
4697 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4699 std::vector<sc::ColRowSpan> aColArr(1, sc::ColRowSpan(thisAddress.StartColumn,thisAddress.EndColumn));
4700 bool bDirection = true;
4701 if ( mbIsRows )
4703 bDirection = false;
4704 aColArr[0].mnStart = thisAddress.StartRow;
4705 aColArr[0].mnEnd = thisAddress.EndRow;
4707 pDocShell->GetDocFunc().SetWidthOrHeight(
4708 bDirection, aColArr, thisAddress.Sheet, SC_SIZE_OPTIMAL, 0, true, true);
4712 uno::Any SAL_CALL
4713 ScVbaRange::Hyperlinks( const uno::Any& aIndex ) throw (uno::RuntimeException, std::exception)
4715 /* The range object always returns a new Hyperlinks object containing a
4716 fixed list of existing hyperlinks in the range.
4717 See vbahyperlinks.hxx for more details. */
4719 // get the global hyperlink object of the sheet (sheet should always be the parent of a Range object)
4720 uno::Reference< excel::XWorksheet > xWorksheet( getParent(), uno::UNO_QUERY_THROW );
4721 uno::Reference< excel::XHyperlinks > xSheetHlinks( xWorksheet->Hyperlinks( uno::Any() ), uno::UNO_QUERY_THROW );
4722 ScVbaHyperlinksRef xScSheetHlinks( dynamic_cast< ScVbaHyperlinks* >( xSheetHlinks.get() ) );
4723 if( !xScSheetHlinks.is() )
4724 throw uno::RuntimeException("Cannot obtain hyperlinks implementation object" );
4726 // create a new local hyperlinks object based on the sheet hyperlinks
4727 ScVbaHyperlinksRef xHlinks( new ScVbaHyperlinks( getParent(), mxContext, xScSheetHlinks, getScRangeList() ) );
4728 if( aIndex.hasValue() )
4729 return xHlinks->Item( aIndex, uno::Any() );
4730 return uno::Any( uno::Reference< excel::XHyperlinks >( xHlinks.get() ) );
4733 css::uno::Reference< excel::XValidation > SAL_CALL
4734 ScVbaRange::getValidation() throw (css::uno::RuntimeException, std::exception)
4736 if ( !m_xValidation.is() )
4737 m_xValidation = new ScVbaValidation( this, mxContext, mxRange );
4738 return m_xValidation;
4741 namespace {
4743 sal_Unicode lclGetPrefixChar( const uno::Reference< table::XCell >& rxCell ) throw (uno::RuntimeException)
4745 /* TODO/FIXME: We need an apostroph-prefix property at the cell to
4746 implement this correctly. For now, return an apostroph for every text
4747 cell.
4749 TODO/FIXME: When Application.TransitionNavigKeys is supported and true,
4750 this function needs to inspect the cell formatting and return different
4751 prefixes according to the horizontal cell alignment.
4753 return (rxCell->getType() == table::CellContentType_TEXT) ? '\'' : 0;
4756 sal_Unicode lclGetPrefixChar( const uno::Reference< table::XCellRange >& rxRange ) throw (uno::RuntimeException)
4758 /* This implementation is able to handle different prefixes (needed if
4759 Application.TransitionNavigKeys is true). The function lclGetPrefixChar
4760 for single cells called from here may return any prefix. If that
4761 function returns an empty prefix (NUL character) or different non-empty
4762 prefixes for two cells, this function returns 0.
4764 sal_Unicode cCurrPrefix = 0;
4765 table::CellRangeAddress aRangeAddr = lclGetRangeAddress( rxRange );
4766 sal_Int32 nEndCol = aRangeAddr.EndColumn - aRangeAddr.StartColumn;
4767 sal_Int32 nEndRow = aRangeAddr.EndRow - aRangeAddr.StartRow;
4768 for( sal_Int32 nRow = 0; nRow <= nEndRow; ++nRow )
4770 for( sal_Int32 nCol = 0; nCol <= nEndCol; ++nCol )
4772 uno::Reference< table::XCell > xCell( rxRange->getCellByPosition( nCol, nRow ), uno::UNO_SET_THROW );
4773 sal_Unicode cNewPrefix = lclGetPrefixChar( xCell );
4774 if( (cNewPrefix == 0) || ((cCurrPrefix != 0) && (cNewPrefix != cCurrPrefix)) )
4775 return 0;
4776 cCurrPrefix = cNewPrefix;
4779 // all cells contain the same prefix - return it
4780 return cCurrPrefix;
4783 sal_Unicode lclGetPrefixChar( const uno::Reference< sheet::XSheetCellRangeContainer >& rxRanges ) throw (uno::RuntimeException)
4785 sal_Unicode cCurrPrefix = 0;
4786 uno::Reference< container::XEnumerationAccess > xRangesEA( rxRanges, uno::UNO_QUERY_THROW );
4787 uno::Reference< container::XEnumeration > xRangesEnum( xRangesEA->createEnumeration(), uno::UNO_SET_THROW );
4788 while( xRangesEnum->hasMoreElements() )
4790 uno::Reference< table::XCellRange > xRange( xRangesEnum->nextElement(), uno::UNO_QUERY_THROW );
4791 sal_Unicode cNewPrefix = lclGetPrefixChar( xRange );
4792 if( (cNewPrefix == 0) || ((cCurrPrefix != 0) && (cNewPrefix != cCurrPrefix)) )
4793 return 0;
4794 cCurrPrefix = cNewPrefix;
4796 // all ranges contain the same prefix - return it
4797 return cCurrPrefix;
4800 inline uno::Any lclGetPrefixVariant( sal_Unicode cPrefixChar )
4802 return uno::Any( (cPrefixChar == 0) ? OUString() : OUString( cPrefixChar ) );
4805 } // namespace
4807 uno::Any SAL_CALL ScVbaRange::getPrefixCharacter() throw (uno::RuntimeException, std::exception)
4809 /* (1) If Application.TransitionNavigKeys is false, this function returns
4810 an apostroph character if the text cell begins with an apostroph
4811 character (formula return values are not taken into account); otherwise
4812 an empty string.
4814 (2) If Application.TransitionNavigKeys is true, this function returns
4815 an apostroph character, if the cell is left-aligned; a double-quote
4816 character, if the cell is right-aligned; a circumflex character, if the
4817 cell is centered; a backslash character, if the cell is set to filled;
4818 or an empty string, if nothing of the above.
4820 If a range or a list of ranges contains texts with leading apostroph
4821 character as well as other cells, this function returns an empty
4822 string.
4825 if( mxRange.is() )
4826 return lclGetPrefixVariant( lclGetPrefixChar( mxRange ) );
4827 if( mxRanges.is() )
4828 return lclGetPrefixVariant( lclGetPrefixChar( mxRanges ) );
4829 throw uno::RuntimeException("Unexpected empty Range object" );
4832 uno::Any ScVbaRange::getShowDetail() throw ( css::uno::RuntimeException, std::exception)
4834 // #FIXME, If the specified range is in a PivotTable report
4836 // In MSO VBA, the specified range must be a single summary column or row in an outline. otherwise throw exception
4837 if( m_Areas->getCount() > 1 )
4838 throw uno::RuntimeException("Can not get Range.ShowDetail attribute " );
4840 RangeHelper helper( mxRange );
4841 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = helper.getSheetCellCursor();
4842 xSheetCellCursor->collapseToCurrentRegion();
4843 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW);
4844 table::CellRangeAddress aOutlineAddress = xCellRangeAddressable->getRangeAddress();
4846 // check if the specified range is a single summary column or row.
4847 table::CellRangeAddress thisAddress = helper.getCellRangeAddressable()->getRangeAddress();
4848 if( (thisAddress.StartRow == thisAddress.EndRow && thisAddress.EndRow == aOutlineAddress.EndRow ) ||
4849 (thisAddress.StartColumn == thisAddress.EndColumn && thisAddress.EndColumn == aOutlineAddress.EndColumn ))
4851 bool bColumn = thisAddress.StartRow != thisAddress.EndRow;
4852 ScDocument& rDoc = getDocumentFromRange( mxRange );
4853 ScOutlineTable* pOutlineTable = rDoc.GetOutlineTable(static_cast<SCTAB>(thisAddress.Sheet), true);
4854 const ScOutlineArray& rOutlineArray = bColumn ? pOutlineTable->GetColArray(): pOutlineTable->GetRowArray();
4855 SCCOLROW nPos = bColumn ? (SCCOLROW)(thisAddress.EndColumn-1):(SCCOLROW)(thisAddress.EndRow-1);
4856 const ScOutlineEntry* pEntry = rOutlineArray.GetEntryByPos( 0, nPos );
4857 if( pEntry )
4859 const bool bShowDetail = !pEntry->IsHidden();
4860 return uno::makeAny( bShowDetail );
4863 else
4865 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" );
4867 return aNULL();
4870 void ScVbaRange::setShowDetail(const uno::Any& aShowDetail) throw ( css::uno::RuntimeException, std::exception)
4872 // #FIXME, If the specified range is in a PivotTable report
4874 // In MSO VBA, the specified range must be a single summary column or row in an outline. otherwise throw exception
4875 if( m_Areas->getCount() > 1 )
4876 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" );
4878 bool bShowDetail = extractBoolFromAny( aShowDetail );
4880 RangeHelper helper( mxRange );
4881 uno::Reference< sheet::XSheetCellCursor > xSheetCellCursor = helper.getSheetCellCursor();
4882 xSheetCellCursor->collapseToCurrentRegion();
4883 uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable(xSheetCellCursor, uno::UNO_QUERY_THROW);
4884 table::CellRangeAddress aOutlineAddress = xCellRangeAddressable->getRangeAddress();
4886 // check if the specified range is a single summary column or row.
4887 table::CellRangeAddress thisAddress = helper.getCellRangeAddressable()->getRangeAddress();
4888 if( (thisAddress.StartRow == thisAddress.EndRow && thisAddress.EndRow == aOutlineAddress.EndRow ) ||
4889 (thisAddress.StartColumn == thisAddress.EndColumn && thisAddress.EndColumn == aOutlineAddress.EndColumn ))
4891 // #FIXME, seems there is a different behavior between MSO and OOo.
4892 // In OOo, the showDetail will show all the level entrys, while only show the first level entry in MSO
4893 uno::Reference< sheet::XSheetOutline > xSheetOutline( helper.getSpreadSheet(), uno::UNO_QUERY_THROW );
4894 if( bShowDetail )
4895 xSheetOutline->showDetail( aOutlineAddress );
4896 else
4897 xSheetOutline->hideDetail( aOutlineAddress );
4899 else
4901 throw uno::RuntimeException("Can not set Range.ShowDetail attribute" );
4905 uno::Reference< excel::XRange > SAL_CALL
4906 ScVbaRange::MergeArea() throw (script::BasicErrorException, uno::RuntimeException, std::exception)
4908 uno::Reference< sheet::XSheetCellRange > xMergeShellCellRange(mxRange->getCellRangeByPosition(0,0,0,0), uno::UNO_QUERY_THROW);
4909 uno::Reference< sheet::XSheetCellCursor > xMergeSheetCursor(xMergeShellCellRange->getSpreadsheet()->createCursorByRange( xMergeShellCellRange ), uno::UNO_QUERY_THROW);
4910 if( xMergeSheetCursor.is() )
4912 xMergeSheetCursor->collapseToMergedArea();
4913 uno::Reference<sheet::XCellRangeAddressable> xMergeCellAddress(xMergeSheetCursor, uno::UNO_QUERY_THROW);
4914 if( xMergeCellAddress.is() )
4916 table::CellRangeAddress aCellAddress = xMergeCellAddress->getRangeAddress();
4917 if( aCellAddress.StartColumn ==0 && aCellAddress.EndColumn==0 &&
4918 aCellAddress.StartRow==0 && aCellAddress.EndRow==0)
4920 return new ScVbaRange( mxParent,mxContext,mxRange );
4922 else
4924 ScRange refRange( static_cast< SCCOL >( aCellAddress.StartColumn ), static_cast< SCROW >( aCellAddress.StartRow ), static_cast< SCTAB >( aCellAddress.Sheet ),
4925 static_cast< SCCOL >( aCellAddress.EndColumn ), static_cast< SCROW >( aCellAddress.EndRow ), static_cast< SCTAB >( aCellAddress.Sheet ) );
4926 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell() , refRange ) );
4927 return new ScVbaRange( mxParent, mxContext,xRange );
4931 return new ScVbaRange( mxParent, mxContext, mxRange );
4934 void SAL_CALL
4935 ScVbaRange::PrintOut( const uno::Any& From, const uno::Any& To, const uno::Any& Copies, const uno::Any& Preview, const uno::Any& ActivePrinter, const uno::Any& PrintToFile, const uno::Any& Collate, const uno::Any& PrToFileName ) throw (uno::RuntimeException, std::exception)
4937 ScDocShell* pShell = NULL;
4939 sal_Int32 nItems = m_Areas->getCount();
4940 uno::Sequence< table::CellRangeAddress > printAreas( nItems );
4941 uno::Reference< sheet::XPrintAreas > xPrintAreas;
4942 for ( sal_Int32 index=1; index <= nItems; ++index )
4944 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
4946 RangeHelper thisRange( xRange->getCellRange() );
4947 table::CellRangeAddress rangeAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4948 if ( index == 1 )
4950 ScVbaRange* pRange = getImplementation( xRange );
4951 // initialise the doc shell and the printareas
4952 pShell = getDocShellFromRange( pRange->mxRange );
4953 xPrintAreas.set( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
4955 printAreas[ index - 1 ] = rangeAddress;
4957 if ( pShell )
4959 if ( xPrintAreas.is() )
4961 xPrintAreas->setPrintAreas( printAreas );
4962 uno::Reference< frame::XModel > xModel = pShell->GetModel();
4963 PrintOutHelper( excel::getBestViewShell( xModel ), From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, true );
4968 void SAL_CALL
4969 ScVbaRange::AutoFill( const uno::Reference< excel::XRange >& Destination, const uno::Any& Type ) throw (uno::RuntimeException, std::exception)
4971 uno::Reference< excel::XRange > xDest( Destination, uno::UNO_QUERY_THROW );
4972 ScVbaRange* pRange = getImplementation( xDest );
4973 RangeHelper destRangeHelper( pRange->mxRange );
4974 table::CellRangeAddress destAddress = destRangeHelper.getCellRangeAddressable()->getRangeAddress();
4976 RangeHelper thisRange( mxRange );
4977 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
4978 ScRange sourceRange;
4979 ScRange destRange;
4981 ScUnoConversion::FillScRange( destRange, destAddress );
4982 ScUnoConversion::FillScRange( sourceRange, thisAddress );
4984 FillDir eDir = FILL_TO_BOTTOM;
4985 double fStep = 1.0;
4987 ScRange aRange( destRange );
4988 ScRange aSourceRange( destRange );
4990 // default to include the number of Rows in the source range;
4991 SCCOLROW nSourceCount = ( sourceRange.aEnd.Row() - sourceRange.aStart.Row() ) + 1;
4992 SCCOLROW nCount = 0;
4994 if ( sourceRange != destRange )
4996 // Find direction of fill, vertical or horizontal
4997 if ( sourceRange.aStart == destRange.aStart )
4999 if ( sourceRange.aEnd.Row() == destRange.aEnd.Row() )
5001 nSourceCount = ( sourceRange.aEnd.Col() - sourceRange.aStart.Col() + 1 );
5002 aSourceRange.aEnd.SetCol( static_cast<SCCOL>( aSourceRange.aStart.Col() + nSourceCount - 1 ) );
5003 eDir = FILL_TO_RIGHT;
5004 nCount = aRange.aEnd.Col() - aSourceRange.aEnd.Col();
5006 else if ( sourceRange.aEnd.Col() == destRange.aEnd.Col() )
5008 aSourceRange.aEnd.SetRow( static_cast<SCROW>( aSourceRange.aStart.Row() + nSourceCount ) - 1 );
5009 nCount = aRange.aEnd.Row() - aSourceRange.aEnd.Row();
5010 eDir = FILL_TO_BOTTOM;
5014 else if ( aSourceRange.aEnd == destRange.aEnd )
5016 if ( sourceRange.aStart.Col() == destRange.aStart.Col() )
5018 aSourceRange.aStart.SetRow( static_cast<SCROW>( aSourceRange.aEnd.Row() - nSourceCount + 1 ) );
5019 nCount = aSourceRange.aStart.Row() - aRange.aStart.Row();
5020 eDir = FILL_TO_TOP;
5021 fStep = -fStep;
5023 else if ( sourceRange.aStart.Row() == destRange.aStart.Row() )
5025 nSourceCount = ( sourceRange.aEnd.Col() - sourceRange.aStart.Col() ) + 1;
5026 aSourceRange.aStart.SetCol( static_cast<SCCOL>( aSourceRange.aEnd.Col() - nSourceCount + 1 ) );
5027 nCount = aSourceRange.aStart.Col() - aRange.aStart.Col();
5028 eDir = FILL_TO_LEFT;
5029 fStep = -fStep;
5034 FillCmd eCmd = FILL_AUTO;
5035 FillDateCmd eDateCmd = FILL_DAY;
5037 if ( Type.hasValue() )
5039 sal_Int16 nFillType = excel::XlAutoFillType::xlFillDefault;
5040 Type >>= nFillType;
5041 switch ( nFillType )
5043 case excel::XlAutoFillType::xlFillCopy:
5044 eCmd = FILL_SIMPLE;
5045 fStep = 0.0;
5046 break;
5047 case excel::XlAutoFillType::xlFillDays:
5048 eCmd = FILL_DATE;
5049 break;
5050 case excel::XlAutoFillType::xlFillMonths:
5051 eCmd = FILL_DATE;
5052 eDateCmd = FILL_MONTH;
5053 break;
5054 case excel::XlAutoFillType::xlFillWeekdays:
5055 eCmd = FILL_DATE;
5056 eDateCmd = FILL_WEEKDAY;
5057 break;
5058 case excel::XlAutoFillType::xlFillYears:
5059 eCmd = FILL_DATE;
5060 eDateCmd = FILL_YEAR;
5061 break;
5062 case excel::XlAutoFillType::xlGrowthTrend:
5063 eCmd = FILL_GROWTH;
5064 break;
5065 case excel::XlAutoFillType::xlFillFormats:
5066 throw uno::RuntimeException("xlFillFormat not supported for AutoFill" );
5067 case excel::XlAutoFillType::xlFillValues:
5068 case excel::XlAutoFillType::xlFillSeries:
5069 case excel::XlAutoFillType::xlLinearTrend:
5070 eCmd = FILL_LINEAR;
5071 break;
5072 case excel::XlAutoFillType::xlFillDefault:
5073 default:
5074 eCmd = FILL_AUTO;
5075 break;
5078 double fEndValue = MAXDOUBLE;
5079 ScDocShell* pDocSh = getDocShellFromRange( mxRange );
5080 pDocSh->GetDocFunc().FillAuto( aSourceRange, NULL, eDir, eCmd, eDateCmd,
5081 nCount, fStep, fEndValue, true, true );
5083 sal_Bool SAL_CALL
5084 ScVbaRange::GoalSeek( const uno::Any& Goal, const uno::Reference< excel::XRange >& ChangingCell ) throw (uno::RuntimeException, std::exception)
5086 ScDocShell* pDocShell = getScDocShell();
5087 bool bRes = true;
5088 ScVbaRange* pRange = static_cast< ScVbaRange* >( ChangingCell.get() );
5089 if ( pDocShell && pRange )
5091 uno::Reference< sheet::XGoalSeek > xGoalSeek( pDocShell->GetModel(), uno::UNO_QUERY_THROW );
5092 RangeHelper thisRange( mxRange );
5093 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
5094 RangeHelper changingCellRange( pRange->mxRange );
5095 table::CellRangeAddress changingCellAddr = changingCellRange.getCellRangeAddressable()->getRangeAddress();
5096 OUString sGoal = getAnyAsString( Goal );
5097 table::CellAddress thisCell( thisAddress.Sheet, thisAddress.StartColumn, thisAddress.StartRow );
5098 table::CellAddress changingCell( changingCellAddr.Sheet, changingCellAddr.StartColumn, changingCellAddr.StartRow );
5099 sheet::GoalResult res = xGoalSeek->seekGoal( thisCell, changingCell, sGoal );
5100 ChangingCell->setValue( uno::makeAny( res.Result ) );
5102 // openoffice behaves differently, result is 0 if the divergence is too great
5103 // but... if it detects 0 is the value it requires then it will use that
5104 // e.g. divergence & result both = 0.0 does NOT mean there is an error
5105 if ( ( res.Divergence != 0.0 ) && ( res.Result == 0.0 ) )
5106 bRes = false;
5108 else
5109 bRes = false;
5110 return bRes;
5113 void
5114 ScVbaRange::Calculate( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5116 getWorksheet()->Calculate();
5119 uno::Reference< excel::XRange > SAL_CALL
5120 ScVbaRange::Item( const uno::Any& row, const uno::Any& column ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5122 if ( mbIsRows || mbIsColumns )
5124 if ( column.hasValue() )
5125 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString() );
5126 uno::Reference< excel::XRange > xRange;
5127 if ( mbIsColumns )
5128 xRange = Columns( row );
5129 else
5130 xRange = Rows( row );
5131 return xRange;
5133 return Cells( row, column );
5136 void
5137 ScVbaRange::AutoOutline( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5139 // #TODO #FIXME needs to check for summary row/col ( whatever they are )
5140 // not valid for multi Area Addresses
5141 if ( m_Areas->getCount() > 1 )
5142 DebugHelper::basicexception(SbERR_METHOD_FAILED, STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY);
5143 // So needs to either span an entire Row or a just be a single cell
5144 // ( that contains a summary RowColumn )
5145 // also the Single cell cause doesn't seem to be handled specially in
5146 // this code ( ported from the helperapi RangeImpl.java,
5147 // RangeRowsImpl.java, RangesImpl.java, RangeSingleCellImpl.java
5148 RangeHelper thisRange( mxRange );
5149 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
5151 if ( isSingleCellRange() || mbIsRows )
5153 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
5154 xSheetOutline->autoOutline( thisAddress );
5156 else
5157 DebugHelper::basicexception(SbERR_METHOD_FAILED, OUString());
5160 void SAL_CALL
5161 ScVbaRange:: ClearOutline( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5163 if ( m_Areas->getCount() > 1 )
5165 sal_Int32 nItems = m_Areas->getCount();
5166 for ( sal_Int32 index=1; index <= nItems; ++index )
5168 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
5169 xRange->ClearOutline();
5171 return;
5173 RangeHelper thisRange( mxRange );
5174 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
5175 xSheetOutline->clearOutline();
5178 void
5179 ScVbaRange::groupUnGroup( bool bUnGroup ) throw ( script::BasicErrorException, uno::RuntimeException )
5181 if ( m_Areas->getCount() > 1 )
5182 DebugHelper::basicexception(SbERR_METHOD_FAILED, STR_ERRORMESSAGE_APPLIESTOSINGLERANGEONLY);
5183 table::TableOrientation nOrient = table::TableOrientation_ROWS;
5184 if ( mbIsColumns )
5185 nOrient = table::TableOrientation_COLUMNS;
5186 RangeHelper thisRange( mxRange );
5187 table::CellRangeAddress thisAddress = thisRange.getCellRangeAddressable()->getRangeAddress();
5188 uno::Reference< sheet::XSheetOutline > xSheetOutline( thisRange.getSpreadSheet(), uno::UNO_QUERY_THROW );
5189 if ( bUnGroup )
5190 xSheetOutline->ungroup( thisAddress, nOrient );
5191 else
5192 xSheetOutline->group( thisAddress, nOrient );
5195 void SAL_CALL
5196 ScVbaRange::Group( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5198 groupUnGroup();
5200 void SAL_CALL
5201 ScVbaRange::Ungroup( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5203 groupUnGroup(true);
5206 static void lcl_mergeCellsOfRange( const uno::Reference< table::XCellRange >& xCellRange, bool _bMerge = true ) throw ( uno::RuntimeException )
5208 uno::Reference< util::XMergeable > xMergeable( xCellRange, uno::UNO_QUERY_THROW );
5209 xMergeable->merge(_bMerge);
5211 void SAL_CALL
5212 ScVbaRange::Merge( const uno::Any& Across ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5214 if ( m_Areas->getCount() > 1 )
5216 sal_Int32 nItems = m_Areas->getCount();
5217 for ( sal_Int32 index=1; index <= nItems; ++index )
5219 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
5220 xRange->Merge(Across);
5222 return;
5224 uno::Reference< table::XCellRange > oCellRange;
5225 bool bAcross = false;
5226 Across >>= bAcross;
5227 if ( !bAcross )
5228 lcl_mergeCellsOfRange( mxRange );
5229 else
5231 uno::Reference< excel::XRange > oRangeRowsImpl = Rows( uno::Any() );
5232 // #TODO #FIXME this seems incredibly lame, this can't be right
5233 for (sal_Int32 i=1; i <= oRangeRowsImpl->getCount();i++)
5235 oRangeRowsImpl->Cells( uno::makeAny( i ), uno::Any() )->Merge( uno::makeAny( false ) );
5240 void SAL_CALL
5241 ScVbaRange::UnMerge( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5243 if ( m_Areas->getCount() > 1 )
5245 sal_Int32 nItems = m_Areas->getCount();
5246 for ( sal_Int32 index=1; index <= nItems; ++index )
5248 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
5249 xRange->UnMerge();
5251 return;
5253 lcl_mergeCellsOfRange( mxRange, false);
5256 uno::Any SAL_CALL
5257 ScVbaRange::getStyle() throw (css::script::BasicErrorException, uno::RuntimeException, std::exception)
5259 if ( m_Areas->getCount() > 1 )
5261 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW );
5262 return xRange->getStyle();
5264 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW );
5265 OUString sStyleName;
5266 xProps->getPropertyValue( CELLSTYLE ) >>= sStyleName;
5267 ScDocShell* pShell = getScDocShell();
5268 uno::Reference< frame::XModel > xModel( pShell->GetModel() );
5269 uno::Reference< excel::XStyle > xStyle = new ScVbaStyle( this, mxContext, sStyleName, xModel );
5270 return uno::makeAny( xStyle );
5272 void SAL_CALL
5273 ScVbaRange::setStyle( const uno::Any& _style ) throw (uno::RuntimeException, std::exception)
5275 if ( m_Areas->getCount() > 1 )
5277 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW );
5278 xRange->setStyle( _style );
5279 return;
5281 uno::Reference< beans::XPropertySet > xProps( mxRange, uno::UNO_QUERY_THROW );
5282 uno::Reference< excel::XStyle > xStyle;
5283 _style >>= xStyle;
5284 xProps->setPropertyValue( CELLSTYLE, uno::makeAny( xStyle->getName() ) );
5287 uno::Reference< excel::XRange >
5288 ScVbaRange::PreviousNext( bool bIsPrevious )
5290 ScMarkData markedRange;
5291 ScRange refRange;
5292 RangeHelper thisRange( mxRange );
5294 ScUnoConversion::FillScRange( refRange, thisRange.getCellRangeAddressable()->getRangeAddress());
5295 markedRange. SetMarkArea( refRange );
5296 short nMove = bIsPrevious ? -1 : 1;
5298 SCCOL nNewX = refRange.aStart.Col();
5299 SCROW nNewY = refRange.aStart.Row();
5300 SCTAB nTab = refRange.aStart.Tab();
5302 ScDocument& rDoc = getScDocument();
5303 rDoc.GetNextPos( nNewX,nNewY, nTab, nMove,0, true,true, markedRange );
5304 refRange.aStart.SetCol( nNewX );
5305 refRange.aStart.SetRow( nNewY );
5306 refRange.aStart.SetTab( nTab );
5307 refRange.aEnd.SetCol( nNewX );
5308 refRange.aEnd.SetRow( nNewY );
5309 refRange.aEnd.SetTab( nTab );
5311 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell() , refRange ) );
5313 return new ScVbaRange( mxParent, mxContext, xRange );
5316 uno::Reference< excel::XRange > SAL_CALL
5317 ScVbaRange::Next() throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5319 if ( m_Areas->getCount() > 1 )
5321 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32( 1 ) ), uno::Any() ) , uno::UNO_QUERY_THROW );
5322 return xRange->Next();
5324 return PreviousNext( false );
5327 uno::Reference< excel::XRange > SAL_CALL
5328 ScVbaRange::Previous() throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5330 if ( m_Areas->getCount() > 1 )
5332 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny( sal_Int32( 1 ) ), uno::Any() ), uno::UNO_QUERY_THROW );
5333 return xRange->Previous();
5335 return PreviousNext( true );
5338 uno::Reference< excel::XRange > SAL_CALL
5339 ScVbaRange::SpecialCells( const uno::Any& _oType, const uno::Any& _oValue)
5340 throw (script::BasicErrorException, uno::RuntimeException,
5341 std::exception)
5343 bool bIsSingleCell = isSingleCellRange();
5344 bool bIsMultiArea = ( m_Areas->getCount() > 1 );
5345 ScVbaRange* pRangeToUse = this;
5346 uno::Reference< excel::XRange > xUsedRange( getWorksheet()->getUsedRange() );
5347 sal_Int32 nType = 0;
5348 if ( !( _oType >>= nType ) )
5349 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString() );
5350 switch(nType)
5352 case excel::XlCellType::xlCellTypeSameFormatConditions:
5353 case excel::XlCellType::xlCellTypeAllValidation:
5354 case excel::XlCellType::xlCellTypeSameValidation:
5355 DebugHelper::basicexception(SbERR_NOT_IMPLEMENTED, OUString());
5356 break;
5357 case excel::XlCellType::xlCellTypeBlanks:
5358 case excel::XlCellType::xlCellTypeComments:
5359 case excel::XlCellType::xlCellTypeConstants:
5360 case excel::XlCellType::xlCellTypeFormulas:
5361 case excel::XlCellType::xlCellTypeVisible:
5362 case excel::XlCellType::xlCellTypeLastCell:
5364 if ( bIsMultiArea )
5366 // need to process each area, gather the results and
5367 // create a new range from those
5368 std::vector< table::CellRangeAddress > rangeResults;
5369 sal_Int32 nItems = ( m_Areas->getCount() + 1 );
5370 for ( sal_Int32 index=1; index <= nItems; ++index )
5372 uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
5373 xRange = xRange->SpecialCells( _oType, _oValue);
5374 ScVbaRange* pRange = getImplementation( xRange );
5375 if ( xRange.is() && pRange )
5377 sal_Int32 nElems = ( pRange->m_Areas->getCount() + 1 );
5378 for ( sal_Int32 nArea = 1; nArea < nElems; ++nArea )
5380 uno::Reference< excel::XRange > xTmpRange( m_Areas->Item( uno::makeAny( nArea ), uno::Any() ), uno::UNO_QUERY_THROW );
5381 RangeHelper rHelper( xTmpRange->getCellRange() );
5382 rangeResults.push_back( rHelper.getCellRangeAddressable()->getRangeAddress() );
5386 ScRangeList aCellRanges;
5387 std::vector< table::CellRangeAddress >::iterator it = rangeResults.begin();
5388 std::vector< table::CellRangeAddress >::iterator it_end = rangeResults.end();
5389 for ( ; it != it_end; ++ it )
5391 ScRange refRange;
5392 ScUnoConversion::FillScRange( refRange, *it );
5393 aCellRanges.Append( refRange );
5395 // Single range
5396 if ( aCellRanges.size() == 1 )
5398 uno::Reference< table::XCellRange > xRange( new ScCellRangeObj( getScDocShell(), *aCellRanges.front() ) );
5399 return new ScVbaRange( mxParent, mxContext, xRange );
5401 uno::Reference< sheet::XSheetCellRangeContainer > xRanges( new ScCellRangesObj( getScDocShell(), aCellRanges ) );
5403 return new ScVbaRange( mxParent, mxContext, xRanges );
5405 else if ( bIsSingleCell )
5407 pRangeToUse = static_cast< ScVbaRange* >( xUsedRange.get() );
5410 break;
5412 default:
5413 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString() );
5414 break;
5416 if ( !pRangeToUse )
5417 DebugHelper::basicexception(SbERR_METHOD_FAILED, OUString() );
5418 return pRangeToUse->SpecialCellsImpl( nType, _oValue );
5421 static sal_Int32 lcl_getFormulaResultFlags(const uno::Any& aType) throw ( script::BasicErrorException )
5423 sal_Int32 nType = excel::XlSpecialCellsValue::xlNumbers;
5424 aType >>= nType;
5425 sal_Int32 nRes = sheet::FormulaResult::VALUE;
5427 switch(nType)
5429 case excel::XlSpecialCellsValue::xlErrors:
5430 nRes= sheet::FormulaResult::ERROR;
5431 break;
5432 case excel::XlSpecialCellsValue::xlLogical:
5433 //TODO bc93774: ask NN if this is really an appropriate substitute
5434 nRes = sheet::FormulaResult::VALUE;
5435 break;
5436 case excel::XlSpecialCellsValue::xlNumbers:
5437 nRes = sheet::FormulaResult::VALUE;
5438 break;
5439 case excel::XlSpecialCellsValue::xlTextValues:
5440 nRes = sheet::FormulaResult::STRING;
5441 break;
5442 default:
5443 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString() );
5445 return nRes;
5448 uno::Reference< excel::XRange >
5449 ScVbaRange::SpecialCellsImpl( sal_Int32 nType, const uno::Any& _oValue) throw ( script::BasicErrorException )
5451 uno::Reference< excel::XRange > xRange;
5454 uno::Reference< sheet::XCellRangesQuery > xQuery( mxRange, uno::UNO_QUERY_THROW );
5455 uno::Reference< excel::XRange > oLocRangeImpl;
5456 uno::Reference< sheet::XSheetCellRanges > xLocSheetCellRanges;
5457 switch(nType)
5459 case excel::XlCellType::xlCellTypeAllFormatConditions:
5460 case excel::XlCellType::xlCellTypeSameFormatConditions:
5461 case excel::XlCellType::xlCellTypeAllValidation:
5462 case excel::XlCellType::xlCellTypeSameValidation:
5463 // Shouldn't get here ( should be filtered out by
5464 // ScVbaRange::SpecialCells()
5465 DebugHelper::basicexception(SbERR_NOT_IMPLEMENTED, OUString());
5466 break;
5467 case excel::XlCellType::xlCellTypeBlanks:
5468 xLocSheetCellRanges = xQuery->queryEmptyCells();
5469 break;
5470 case excel::XlCellType::xlCellTypeComments:
5471 xLocSheetCellRanges = xQuery->queryContentCells(sheet::CellFlags::ANNOTATION);
5472 break;
5473 case excel::XlCellType::xlCellTypeConstants:
5474 xLocSheetCellRanges = xQuery->queryContentCells(23);
5475 break;
5476 case excel::XlCellType::xlCellTypeFormulas:
5478 sal_Int32 nFormulaResult = lcl_getFormulaResultFlags(_oValue);
5479 xLocSheetCellRanges = xQuery->queryFormulaCells(nFormulaResult);
5480 break;
5482 case excel::XlCellType::xlCellTypeLastCell:
5483 xRange = Cells( uno::makeAny( getCount() ), uno::Any() );
5484 case excel::XlCellType::xlCellTypeVisible:
5485 xLocSheetCellRanges = xQuery->queryVisibleCells();
5486 break;
5487 default:
5488 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString() );
5489 break;
5491 if (xLocSheetCellRanges.is())
5493 xRange = lcl_makeXRangeFromSheetCellRanges( getParent(), mxContext, xLocSheetCellRanges, getScDocShell() );
5496 catch (uno::Exception& )
5498 DebugHelper::basicexception(SbERR_METHOD_FAILED, STR_ERRORMESSAGE_NOCELLSWEREFOUND);
5500 return xRange;
5503 void SAL_CALL
5504 ScVbaRange::RemoveSubtotal( ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5506 uno::Reference< sheet::XSubTotalCalculatable > xSub( mxRange, uno::UNO_QUERY_THROW );
5507 xSub->removeSubTotals();
5510 void SAL_CALL
5511 ScVbaRange::Subtotal( ::sal_Int32 _nGroupBy, ::sal_Int32 _nFunction, const uno::Sequence< ::sal_Int32 >& _nTotalList, const uno::Any& aReplace, const uno::Any& PageBreaks, const uno::Any& /*SummaryBelowData*/ ) throw (script::BasicErrorException, uno::RuntimeException, std::exception)
5515 bool bDoReplace = false;
5516 aReplace >>= bDoReplace;
5517 bool bAddPageBreaks = false;
5518 PageBreaks >>= bAddPageBreaks;
5520 uno::Reference< sheet::XSubTotalCalculatable> xSub(mxRange, uno::UNO_QUERY_THROW );
5521 uno::Reference< sheet::XSubTotalDescriptor > xSubDesc = xSub->createSubTotalDescriptor(sal_True);
5522 uno::Reference< beans::XPropertySet > xSubDescPropertySet( xSubDesc, uno::UNO_QUERY_THROW );
5523 xSubDescPropertySet->setPropertyValue(INSERTPAGEBREAKS, uno::makeAny( bAddPageBreaks));
5524 sal_Int32 nLen = _nTotalList.getLength();
5525 uno::Sequence< sheet::SubTotalColumn > aColumns( nLen );
5526 for (int i = 0; i < nLen; i++)
5528 aColumns[i].Column = _nTotalList[i] - 1;
5529 switch (_nFunction)
5531 case excel::XlConsolidationFunction::xlAverage:
5532 aColumns[i].Function = sheet::GeneralFunction_AVERAGE;
5533 break;
5534 case excel::XlConsolidationFunction::xlCount:
5535 aColumns[i].Function = sheet::GeneralFunction_COUNT;
5536 break;
5537 case excel::XlConsolidationFunction::xlCountNums:
5538 aColumns[i].Function = sheet::GeneralFunction_COUNTNUMS;
5539 break;
5540 case excel::XlConsolidationFunction::xlMax:
5541 aColumns[i].Function = sheet::GeneralFunction_MAX;
5542 break;
5543 case excel::XlConsolidationFunction::xlMin:
5544 aColumns[i].Function = sheet::GeneralFunction_MIN;
5545 break;
5546 case excel::XlConsolidationFunction::xlProduct:
5547 aColumns[i].Function = sheet::GeneralFunction_PRODUCT;
5548 break;
5549 case excel::XlConsolidationFunction::xlStDev:
5550 aColumns[i].Function = sheet::GeneralFunction_STDEV;
5551 break;
5552 case excel::XlConsolidationFunction::xlStDevP:
5553 aColumns[i].Function = sheet::GeneralFunction_STDEVP;
5554 break;
5555 case excel::XlConsolidationFunction::xlSum:
5556 aColumns[i].Function = sheet::GeneralFunction_SUM;
5557 break;
5558 case excel::XlConsolidationFunction::xlUnknown:
5559 aColumns[i].Function = sheet::GeneralFunction_NONE;
5560 break;
5561 case excel::XlConsolidationFunction::xlVar:
5562 aColumns[i].Function = sheet::GeneralFunction_VAR;
5563 break;
5564 case excel::XlConsolidationFunction::xlVarP:
5565 aColumns[i].Function = sheet::GeneralFunction_VARP;
5566 break;
5567 default:
5568 DebugHelper::basicexception(SbERR_BAD_PARAMETER, OUString()) ;
5569 return;
5572 xSubDesc->addNew(aColumns, _nGroupBy - 1);
5573 xSub->applySubTotals(xSubDesc, bDoReplace);
5575 catch (const uno::Exception&)
5577 DebugHelper::basicexception(SbERR_METHOD_FAILED, OUString());
5581 OUString
5582 ScVbaRange::getServiceImplName()
5584 return OUString("ScVbaRange");
5587 uno::Sequence< OUString >
5588 ScVbaRange::getServiceNames()
5590 static uno::Sequence< OUString > aServiceNames;
5591 if ( aServiceNames.getLength() == 0 )
5593 aServiceNames.realloc( 1 );
5594 aServiceNames[ 0 ] = "ooo.vba.excel.Range";
5596 return aServiceNames;
5599 sal_Bool SAL_CALL
5600 ScVbaRange::hasError() throw (uno::RuntimeException, std::exception)
5602 double dResult = 0.0;
5603 uno::Reference< excel::XApplication > xApplication( Application(), uno::UNO_QUERY_THROW );
5604 uno::Reference< script::XInvocation > xInvoc( xApplication->WorksheetFunction(), uno::UNO_QUERY_THROW );
5606 static const char FunctionName[] = "IsError";
5607 uno::Sequence< uno::Any > Params(1);
5608 uno::Reference< excel::XRange > aRange( this );
5609 Params[0] = uno::makeAny( aRange );
5610 uno::Sequence< sal_Int16 > OutParamIndex;
5611 uno::Sequence< uno::Any > OutParam;
5612 xInvoc->invoke( FunctionName, Params, OutParamIndex, OutParam ) >>= dResult;
5613 return dResult > 0.0;
5616 namespace range
5618 namespace sdecl = comphelper::service_decl;
5619 sdecl::vba_service_class_<ScVbaRange, sdecl::with_args<true> > serviceImpl;
5620 extern sdecl::ServiceDecl const serviceDecl(
5621 serviceImpl,
5622 "SvVbaRange",
5623 "ooo.vba.excel.Range" );
5626 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */