Attachment #65725 for issue #106412

View | Details | Raw Unified | Return to issue 106412
Collapse All | Expand All

Lines 2-8 Link Here

(-)./old/sc/source/ui/vba/vbanames.cxx (-4 / +128 lines)
2	*	2	*
3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.	3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4	*	4	*
5	* Copyright 2008 by Sun Microsystems, Inc.	5	* Copyright 2009 by Sun Microsystems, Inc.
6	*	6	*
7	* OpenOffice.org - a multi-platform office productivity suite	7	* OpenOffice.org - a multi-platform office productivity suite
8	*	8	*
Lines 31-36 Link Here
31		31
32	#include <com/sun/star/table/XCellRange.hpp>	32	#include <com/sun/star/table/XCellRange.hpp>
33	#include <com/sun/star/sheet/XCellRangeAddressable.hpp>	33	#include <com/sun/star/sheet/XCellRangeAddressable.hpp>
		34	#include <ooo/vba/excel/XApplication.hpp> //liuchen 2009-8-31
		35	#include <ooo/vba/excel/XWorkbook.hpp> //liuchen 2009-8-31
		36	#include <ooo/vba/excel/XWorksheet.hpp> //liuchen 2009-8-31
34		37
35	#include "vbanames.hxx"	38	#include "vbanames.hxx"
36	#include "vbaname.hxx"	39	#include "vbaname.hxx"
Lines 41-46 Link Here
41	#include <vcl/msgbox.hxx>	44	#include <vcl/msgbox.hxx>
42	#include "tabvwsh.hxx"	45	#include "tabvwsh.hxx"
43	#include "viewdata.hxx"	46	#include "viewdata.hxx"
		47	#include "address.hxx" //liuchen 2009-8-31
44		48
45	using namespace ::ooo::vba;	49	using namespace ::ooo::vba;
46	using namespace ::com::sun::star;	50	using namespace ::com::sun::star;
Lines 90-95 Link Here
90	return pViewData->GetDocument();	94	return pViewData->GetDocument();
91	}	95	}
92		96
		97	//liuchen 2009-8-31, resolve the defect of Names.Add
		98	enum ReferenceTypes
		99	{
		100	Standard,
		101	R1C1,
		102	R1C1Loc
		103	};
		104
		105	rtl::OUString
		106	R1C1ToStd (const rtl::OUString R1C1)
		107	{
		108	rtl::OUString strRow, strCol;
		109	strRow = R1C1.copy(1, R1C1.lastIndexOf('C') - 1);
		110	strCol = R1C1.copy(R1C1.lastIndexOf('C') + 1);
		111	strCol = ScColToAlpha( static_cast<SCCOL>(strCol.toInt32()) - 1 );
		112
		113	return rtl::OUString('$') + strCol + rtl::OUString('$') + strRow;
		114	}
		115
		116	bool IsR1C1Format(const rtl::OUString& strRange) //liuchen 2009-9-21, in Excel, Names.Add can handel the situation when the parameter "RefersTo" is a R1C1 format
		117	{
		118	//this function looks for the numbers between the first 'R' and 'C' in strRange, if find, return true
		119	//for example, there is an 2 in "Sheet1!R2C3:R4C5"
		120	sal_Int32 nStart = strRange.indexOf('!') < 0 ? 0 : strRange.indexOf('!');
		121	sal_Int32 nR = strRange.toAsciiUpperCase().indexOf('R', nStart);
		122	if (nR > -1)
		123	{
		124	sal_Int32 nC = strRange.toAsciiUpperCase().indexOf('C', nR);
		125	if (nC > -1)
		126	{
		127	rtl::OUString strBetween = strRange.copy(nR + 1, nC - nR - 1);
		128	if (strBetween.indexOf(':') > -1) //the form of "Sheet1!R2:C3" is not a R1C1 format
		129	{
		130	return false;
		131	}
		132
		133	try
		134	{
		135	sal_Int32 nNum = strBetween.toInt32();
		136	if (nNum > 0)
		137	{
		138	return true;
		139	}
		140	}
		141	catch (...)
		142	{
		143	}
		144	}
		145	}
		146
		147	return false;
		148	}
		149
		150	rtl::OUString
		151	GetRangeStr(const css::uno::Any& RefersTo,
		152	const enum ReferenceTypes& types) throw (css::uno::RuntimeException)
		153	{
		154	rtl::OUString strRefersTo, strRange;
		155
		156	RefersTo >>= strRefersTo;
		157	strRange = strRefersTo.copy(1);
		158
		159	if ( types == R1C1 \|\| types == R1C1Loc \|\| IsR1C1Format(strRange)) //liuchen 2009-8-31 maybe the code to process the situation of R1C1Loc should be different from the following code lines
		160	{
161	if ( strRange.indexOf(':') < 0 ) //for example "sheet1!R1C1"
162	{
163	strRange = strRange.copy(0, strRange.indexOf('!') + 1) + R1C1ToStd( strRange.copy( strRange.lastIndexOf('R') ) );
164	}
165	else //for example "sheet1!R1C1:R5C5"
166	{
167	strRange = strRange.copy(0, strRange.indexOf('!') + 1)
168	+ R1C1ToStd( strRange.copy( strRange.indexOf('!') + 1 , strRange.indexOf(':') - strRange.indexOf('!') - 1 ) )
169	+ rtl::OUString(':')
170	+ R1C1ToStd( strRange.copy( strRange.indexOf(':') + 1 ) );
171	}
172	}
173
174	return strRange;
175	}
176
177	css::uno::Reference< excel::XRange >
178	CalculateRange (const css::uno::Any& RefersTo,
179	const css::uno::Reference< ov::XHelperInterface >& xParent,
180	const enum ReferenceTypes& types) throw (css::uno::RuntimeException)
181	{
182	uno::Reference< excel::XRange > xRange;
183	if ( RefersTo.getValueTypeClass() == uno::TypeClass_STRING )
184	{
185	uno::Any aRange, aAny, aRet;
186	rtl::OUString strRange = GetRangeStr(RefersTo, types);
187
188	uno::Reference< excel::XApplication > xApp( xParent, ::uno::UNO_QUERY );
189	if ( xApp.get() )
190	{
191	aRange <<= strRange;
192	aRet = xApp->Range( aRange, aAny );
193	aRet >>= xRange;
194	}
195	else
196	{
197	uno::Reference< excel::XWorkbook > xWb( xParent, ::uno::UNO_QUERY );
198	if ( xWb.get() )
199	{
200	uno::Any aSheet = xWb->Worksheets( uno::makeAny( strRange.copy(0, strRange.indexOf('!') ) ) );
201	uno::Reference< excel::XWorksheet > xSheet;
202	aSheet >>= xSheet;
203	aRange <<= strRange.copy(strRange.indexOf('!') + 1);
204	xRange = xSheet->Range( aRange, aAny );
205	}
206	}
207	}
208	else
209	{
210	RefersTo >>= xRange;
211	}
212
213	return xRange;
214	}
215	//liuchen 2009-8-31
216
93	css::uno::Any	217	css::uno::Any
94	ScVbaNames::Add( const css::uno::Any& Name ,	218	ScVbaNames::Add( const css::uno::Any& Name ,
95	const css::uno::Any& RefersTo,	219	const css::uno::Any& RefersTo,
Lines 130-140 Link Here
130	if ( RefersTo.hasValue() \|\| RefersToR1C1.hasValue() \|\| RefersToR1C1Local.hasValue() )	254	if ( RefersTo.hasValue() \|\| RefersToR1C1.hasValue() \|\| RefersToR1C1Local.hasValue() )
131	{	255	{
132	if ( RefersTo.hasValue() )	256	if ( RefersTo.hasValue() )
133	RefersTo >>= xRange;	257	xRange = CalculateRange( RefersTo, mxParent, Standard ); //liuchen 2009-8-31
134	if ( RefersToR1C1.hasValue() )	258	if ( RefersToR1C1.hasValue() )
135	RefersToR1C1 >>= xRange;	259	xRange = CalculateRange( RefersToR1C1, mxParent, R1C1 ); //liuchen 2009-8-31
136	if ( RefersToR1C1Local.hasValue() )	260	if ( RefersToR1C1Local.hasValue() )
137	RefersToR1C1Local >>= xRange;	261	xRange = CalculateRange( RefersToR1C1Local, mxParent, R1C1Loc ); //liuchen 2009-8-31
138	}	262	}
139		263
140	if ( xRange.is() )	264	if ( xRange.is() )

Lines 2-8 Link Here

(-)./old/sc/source/ui/vba/vbarange.cxx (-17 / +55 lines)
2	*	2	*
3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.	3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4	*	4	*
5	* Copyright 2008 by Sun Microsystems, Inc.	5	* Copyright 2009 by Sun Microsystems, Inc.
6	*	6	*
7	* OpenOffice.org - a multi-platform office productivity suite	7	* OpenOffice.org - a multi-platform office productivity suite
8	*	8	*
Lines 1163-1169 Link Here
1163	}	1163	}
1164		1164
1165		1165
1166	table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh, formula::FormulaGrammar::AddressConvention aConv = formula::FormulaGrammar::CONV_XL_A1) throw ( uno::RuntimeException )	1166	table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh, table::CellRangeAddress& pAddr, formula::FormulaGrammar::AddressConvention aConv = formula::FormulaGrammar::CONV_XL_A1) throw ( uno::RuntimeException )
1167	{	1167	{
1168	uno::Reference< table::XCellRange > xRangeParam;	1168	uno::Reference< table::XCellRange > xRangeParam;
1169	switch ( aParam.getValueTypeClass() )	1169	switch ( aParam.getValueTypeClass() )
Lines 1174-1179 Link Here
1174	aParam >>= rString;	1174	aParam >>= rString;
1175	ScRangeList aCellRanges;	1175	ScRangeList aCellRanges;
1176	ScRange refRange;	1176	ScRange refRange;
		1177	//VBA by minz@cn.ibm.com. Set reference range.
		1178	ScUnoConversion::FillScRange( refRange, pAddr );
1177	if ( getScRangeListForAddress ( rString, pDocSh, refRange, aCellRanges, aConv ) )	1179	if ( getScRangeListForAddress ( rString, pDocSh, refRange, aCellRanges, aConv ) )
1178	{	1180	{
1179	if ( aCellRanges.First() == aCellRanges.Last() )	1181	if ( aCellRanges.First() == aCellRanges.Last() )
Lines 1470-1475 Link Here
1470	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );;	1472	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );;
1471	}	1473	}
1472		1474
		1475	//Add by minz@cn.ibm.com. Support Range.FormulaLocal.
		1476	uno::Any
		1477	ScVbaRange::getFormulaLocal() throw (::com::sun::star::uno::RuntimeException)
		1478	{
		1479	return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
		1480	}
		1481
		1482	void
		1483	ScVbaRange::setFormulaLocal(const uno::Any& rFormula ) throw (uno::RuntimeException)
		1484	{
		1485	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
		1486	}
		1487
1473	uno::Any	1488	uno::Any
1474	ScVbaRange::getFormulaR1C1() throw (::com::sun::star::uno::RuntimeException)	1489	ScVbaRange::getFormulaR1C1() throw (::com::sun::star::uno::RuntimeException)
1475	{	1490	{
Lines 1745-1757 Link Here
1745		1760
1746	//VBA, minz@cn.ibm.com	1761	//VBA, minz@cn.ibm.com
1747	uno::Sequence< uno::Sequence<rtl::OUString> > aFmArray = xCellRangeFormula->getFormulaArray();	1762	uno::Sequence< uno::Sequence<rtl::OUString> > aFmArray = xCellRangeFormula->getFormulaArray();
1748	if( aFmArray.getLength() )	1763	if( aFmArray.getLength() == 0 )
		1764	return aMatrix;
		1765	else if( aFmArray.getLength() == 1 && aFmArray[0].getLength() == 1 )
1749	{	1766	{
1750	if( aFmArray.getLength() == 1 && aFmArray[0].getLength() == 1 )	1767	//liuchen 2009-9-23 the string got by excel Range.FormulaArray() does not contain brackets
1751	aMatrix <<= aFmArray[0][0];	1768	rtl::OUString strMatrix = aFmArray[0][0];
1752	else	1769	if (strMatrix.indexOf('{') == 0 && strMatrix.lastIndexOf('}') == strMatrix.getLength() - 1)
1753	aMatrix = xConverter->convertTo( uno::makeAny( xCellRangeFormula->getFormulaArray() ) , getCppuType((uno::Sequence< uno::Sequence< uno::Any > >*)0) ) ;	1770	{
1754	}	1771	aMatrix <<= strMatrix.copy(1, strMatrix.getLength() - 2);
		1772	}
		1773	//liuchen 2009-9-23
		1774
		1775	return aMatrix;
		1776	}
		1777	aMatrix = xConverter->convertTo( uno::makeAny( xCellRangeFormula->getFormulaArray() ) , getCppuType((uno::Sequence< uno::Sequence< uno::Any > >*)0) ) ;
1755	return aMatrix;	1778	return aMatrix;
1756	}	1779	}
1757		1780
Lines 1868-1875 Link Here
1868	if ( RelativeTo.hasValue() )	1891	if ( RelativeTo.hasValue() )
1869	{	1892	{
1870	// #TODO should I throw an error if R1C1 is not set?	1893	// #TODO should I throw an error if R1C1 is not set?
1871		1894	table::CellRangeAddress refParentAddr;//no use
1872	table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell );	1895	table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell, refParentAddr );
1873	dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, static_cast< SCROW >( refAddress.StartRow ), static_cast< SCCOL >( refAddress.StartColumn ) );	1896	dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, static_cast< SCROW >( refAddress.StartRow ), static_cast< SCCOL >( refAddress.StartColumn ) );
1874	}	1897	}
1875	aRange.Format( sRange, nFlags, pDoc, dDetails );	1898	aRange.Format( sRange, nFlags, pDoc, dDetails );
Lines 2439-2451 Link Here
2439	}	2462	}
2440	else	2463	else
2441	{	2464	{
		2465	//VBA by minz@cn.ibm.com. Set this range as reference range.
		2466	RangeHelper referRange( xReferrer );
		2467	table::CellRangeAddress referAddress = referRange.getCellRangeAddressable()->getRangeAddress();
		2468
2442	table::CellRangeAddress cell1, cell2;	2469	table::CellRangeAddress cell1, cell2;
2443	cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell() );	2470	cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell(), referAddress );
2444	// Cell1 & Cell2 defined	2471	// Cell1 & Cell2 defined
2445	// Excel seems to combine the range as the range defined by	2472	// Excel seems to combine the range as the range defined by
2446	// the combination of Cell1 & Cell2	2473	// the combination of Cell1 & Cell2
2447		2474
2448	cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell() );	2475	cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell(), referAddress );
2449		2476
2450	resultAddress.StartColumn = ( cell1.StartColumn < cell2.StartColumn ) ? cell1.StartColumn : cell2.StartColumn;	2477	resultAddress.StartColumn = ( cell1.StartColumn < cell2.StartColumn ) ? cell1.StartColumn : cell2.StartColumn;
2451	resultAddress.StartRow = ( cell1.StartRow < cell2.StartRow ) ? cell1.StartRow : cell2.StartRow;	2478	resultAddress.StartRow = ( cell1.StartRow < cell2.StartRow ) ? cell1.StartRow : cell2.StartRow;
Lines 2962-2968 Link Here
2962	uno::Reference< excel::XRange > xResultRange = new ScVbaRange( this, mxContext, xCellRange );	2989	uno::Reference< excel::XRange > xResultRange = new ScVbaRange( this, mxContext, xCellRange );
2963	if( xResultRange.is() )	2990	if( xResultRange.is() )
2964	{	2991	{
2965	xResultRange->Select();	2992	//VBA by minz@cn.ibm.com. No need to change focus to the found range.
		2993	//xResultRange->Select();
2966	return xResultRange;	2994	return xResultRange;
2967	}	2995	}
2968	}	2996	}
Lines 5155-5160 Link Here
5155	bool bIsSingleCell = isSingleCellRange();	5183	bool bIsSingleCell = isSingleCellRange();
5156	bool bIsMultiArea = ( m_Areas->getCount() > 1 );	5184	bool bIsMultiArea = ( m_Areas->getCount() > 1 );
5157	ScVbaRange* pRangeToUse = this;	5185	ScVbaRange* pRangeToUse = this;
		5186	uno::Reference< excel::XRange > xUsedRange;
5158	sal_Int32 nType = 0;	5187	sal_Int32 nType = 0;
5159	if ( !( _oType >>= nType ) )	5188	if ( !( _oType >>= nType ) )
5160	DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );	5189	DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );
Lines 5170-5176 Link Here
5170	case excel::XlCellType::xlCellTypeConstants:	5199	case excel::XlCellType::xlCellTypeConstants:
5171	case excel::XlCellType::xlCellTypeFormulas:	5200	case excel::XlCellType::xlCellTypeFormulas:
5172	case excel::XlCellType::xlCellTypeVisible:	5201	case excel::XlCellType::xlCellTypeVisible:
5173	case excel::XlCellType::xlCellTypeLastCell:	5202	// case excel::XlCellType::xlCellTypeLastCell:
5174	{	5203	{
5175	if ( bIsMultiArea )	5204	if ( bIsMultiArea )
5176	{	5205	{
Lines 5217-5228 Link Here
5217	}	5246	}
5218	else if ( bIsSingleCell )	5247	else if ( bIsSingleCell )
5219	{	5248	{
5220	uno::Reference< excel::XRange > xUsedRange = getWorksheet()->getUsedRange();	5249	xUsedRange = getWorksheet()->getUsedRange();
5221	pRangeToUse = static_cast< ScVbaRange* >( xUsedRange.get() );	5250	pRangeToUse = dynamic_cast< ScVbaRange* >( xUsedRange.get() );
5222	}	5251	}
5223		5252
5224	break;	5253	break;
5225	}	5254	}
		5255	//the last cell in the used range
		5256	case excel::XlCellType::xlCellTypeLastCell:
		5257	{
		5258	xUsedRange = getWorksheet()->getUsedRange();
		5259	pRangeToUse = dynamic_cast< ScVbaRange* >( xUsedRange.get() );
		5260	}
		5261	break;
		5262
5226	default:	5263	default:
5227	DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );	5264	DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );
5228	break;	5265	break;
Lines 5295-5300 Link Here
5295	}	5332	}
5296	case excel::XlCellType::xlCellTypeLastCell:	5333	case excel::XlCellType::xlCellTypeLastCell:
5297	xRange = Cells( uno::makeAny( getCount() ), uno::Any() );	5334	xRange = Cells( uno::makeAny( getCount() ), uno::Any() );
		5335	break;
5298	case excel::XlCellType::xlCellTypeVisible:	5336	case excel::XlCellType::xlCellTypeVisible:
5299	xLocSheetCellRanges = xQuery->queryVisibleCells();	5337	xLocSheetCellRanges = xQuery->queryVisibleCells();
5300	break;	5338	break;

Lines 2-8 Link Here

(-)./old/sc/source/ui/vba/vbarange.hxx (-1 / +3 lines)
2	*	2	*
3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.	3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4	*	4	*
5	* Copyright 2008 by Sun Microsystems, Inc.	5	* Copyright 2009 by Sun Microsystems, Inc.
6	*	6	*
7	* OpenOffice.org - a multi-platform office productivity suite	7	* OpenOffice.org - a multi-platform office productivity suite
8	*	8	*
Lines 142-147 Link Here
142	virtual void SAL_CALL setFormula( const css::uno::Any& rFormula ) throw (css::uno::RuntimeException);	142	virtual void SAL_CALL setFormula( const css::uno::Any& rFormula ) throw (css::uno::RuntimeException);
143	virtual css::uno::Any SAL_CALL getFormulaArray() throw (css::uno::RuntimeException);	143	virtual css::uno::Any SAL_CALL getFormulaArray() throw (css::uno::RuntimeException);
144	virtual void SAL_CALL setFormulaArray(const css::uno::Any& rFormula) throw (css::uno::RuntimeException);	144	virtual void SAL_CALL setFormulaArray(const css::uno::Any& rFormula) throw (css::uno::RuntimeException);
		145	virtual css::uno::Any SAL_CALL getFormulaLocal() throw (css::uno::RuntimeException);
		146	virtual void SAL_CALL setFormulaLocal( const css::uno::Any &rFormula ) throw (css::uno::RuntimeException);
145	virtual css::uno::Any SAL_CALL getFormulaR1C1() throw (css::uno::RuntimeException);	147	virtual css::uno::Any SAL_CALL getFormulaR1C1() throw (css::uno::RuntimeException);
146	virtual void SAL_CALL setFormulaR1C1( const css::uno::Any &rFormula ) throw (css::uno::RuntimeException);	148	virtual void SAL_CALL setFormulaR1C1( const css::uno::Any &rFormula ) throw (css::uno::RuntimeException);
147	virtual ::sal_Int32 SAL_CALL getCount() throw (css::uno::RuntimeException);	149	virtual ::sal_Int32 SAL_CALL getCount() throw (css::uno::RuntimeException);

Lines 2-8 Link Here

(-)./old/sc/source/ui/vba/vbaworksheet.cxx (-2 / +3 lines)
2	*	2	*
3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.	3	* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4	*	4	*
5	* Copyright 2008 by Sun Microsystems, Inc.	5	* Copyright 2009 by Sun Microsystems, Inc.
6	*	6	*
7	* OpenOffice.org - a multi-platform office productivity suite	7	* OpenOffice.org - a multi-platform office productivity suite
8	*	8	*
Lines 347-353 Link Here
347	xUsedCursor->gotoStartOfUsedArea( false );	347	xUsedCursor->gotoStartOfUsedArea( false );
348	xUsedCursor->gotoEndOfUsedArea( true );	348	xUsedCursor->gotoEndOfUsedArea( true );
349	uno::Reference< table::XCellRange > xRange( xSheetCellCursor, uno::UNO_QUERY);	349	uno::Reference< table::XCellRange > xRange( xSheetCellCursor, uno::UNO_QUERY);
350	return new ScVbaRange(this, mxContext, xRange);	350	//return new ScVbaRange(this, mxContext, xRange);
		351	return uno::Reference< excel::XRange >( new ScVbaRange(this, mxContext, xRange) );
351	}	352	}
352		353
353	uno::Reference< excel::XOutline >	354	uno::Reference< excel::XOutline >

Return to issue 106412