1 /*************************************************************************
2  *
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * Copyright 2000, 2010 Oracle and/or its affiliates.
6  *
7  * OpenOffice.org - a multi-platform office productivity suite
8  *
9  * This file is part of OpenOffice.org.
10  *
11  * OpenOffice.org is free software: you can redistribute it and/or modify
12  * it under the terms of the GNU Lesser General Public License version 3
13  * only, as published by the Free Software Foundation.
14  *
15  * OpenOffice.org is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU Lesser General Public License version 3 for more details
19  * (a copy is included in the LICENSE file that accompanied this code).
20  *
21  * You should have received a copy of the GNU Lesser General Public License
22  * version 3 along with OpenOffice.org.  If not, see
23  * <http://www.openoffice.org/license.html>
24  * for a copy of the LGPLv3 License.
25  *
26  ************************************************************************/
27 
28 // MARKER(update_precomp.py): autogen include statement, do not remove
29 #include "precompiled_connectivity.hxx"
30 #include "calc/CTable.hxx"
31 #include <com/sun/star/sdbc/ColumnValue.hpp>
32 #include <com/sun/star/sdbc/DataType.hpp>
33 //#ifndef _COM_SUN_STAR_UCB_XCONTENTACCESS_HPP_
34 //#include <com/sun/star/ucb/XContentAccess.hpp>
35 //#endif
36 #include <com/sun/star/sdbc/XRow.hpp>
37 #include <com/sun/star/sheet/XSpreadsheetDocument.hpp>
38 #include <com/sun/star/sheet/XSpreadsheet.hpp>
39 #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
40 #include <com/sun/star/sheet/XCellRangesQuery.hpp>
41 #include <com/sun/star/sheet/XDatabaseRanges.hpp>
42 #include <com/sun/star/sheet/XDatabaseRange.hpp>
43 #include <com/sun/star/sheet/XCellRangeReferrer.hpp>
44 #include <com/sun/star/sheet/XUsedAreaCursor.hpp>
45 #include <com/sun/star/sheet/CellFlags.hpp>
46 #include <com/sun/star/sheet/FormulaResult.hpp>
47 #include <com/sun/star/util/NumberFormat.hpp>
48 #include <com/sun/star/util/XNumberFormatsSupplier.hpp>
49 #include <com/sun/star/text/XText.hpp>
50 #include <svl/converter.hxx>
51 #include "calc/CConnection.hxx"
52 #include "calc/CColumns.hxx"
53 #include "connectivity/sdbcx/VColumn.hxx"
54 #include <rtl/ustrbuf.hxx>
55 #include <osl/thread.h>
56 #include <tools/config.hxx>
57 #include <comphelper/sequence.hxx>
58 #include <svl/zforlist.hxx>
59 #include <rtl/math.hxx>
60 #include <comphelper/extract.hxx>
61 #include <connectivity/dbexception.hxx>
62 #include <connectivity/dbconversion.hxx>
63 #include <comphelper/types.hxx>
64 #include <rtl/logfile.hxx>
65 
66 using namespace connectivity;
67 using namespace connectivity::calc;
68 using namespace connectivity::file;
69 using namespace ::cppu;
70 using namespace ::dbtools;
71 using namespace ::com::sun::star::uno;
72 using namespace ::com::sun::star::beans;
73 using namespace ::com::sun::star::sdbcx;
74 using namespace ::com::sun::star::sdbc;
75 using namespace ::com::sun::star::container;
76 using namespace ::com::sun::star::lang;
77 using namespace ::com::sun::star::sheet;
78 using namespace ::com::sun::star::table;
79 using namespace ::com::sun::star::text;
80 using namespace ::com::sun::star::util;
81 
82 // -------------------------------------------------------------------------
83 
84 void lcl_UpdateArea( const Reference<XCellRange>& xUsedRange, sal_Int32& rEndCol, sal_Int32& rEndRow )
85 {
86     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_UpdateArea" );
87 	//	update rEndCol, rEndRow if any non-empty cell in xUsedRange is right/below
88 
89 	const Reference<XCellRangesQuery> xUsedQuery( xUsedRange, UNO_QUERY );
90 	if ( xUsedQuery.is() )
91 	{
92 		const sal_Int16 nContentFlags =
93 			CellFlags::STRING | CellFlags::VALUE | CellFlags::DATETIME | CellFlags::FORMULA | CellFlags::ANNOTATION;
94 
95 		const Reference<XSheetCellRanges> xUsedRanges = xUsedQuery->queryContentCells( nContentFlags );
96 		const Sequence<CellRangeAddress> aAddresses = xUsedRanges->getRangeAddresses();
97 
98 		const sal_Int32 nCount = aAddresses.getLength();
99 		const CellRangeAddress* pData = aAddresses.getConstArray();
100 		for ( sal_Int32 i=0; i<nCount; i++ )
101 		{
102             rEndCol = pData[i].EndColumn > rEndCol ? pData[i].EndColumn : rEndCol;
103             rEndRow = pData[i].EndRow    > rEndRow ? pData[i].EndRow    : rEndRow;
104 		}
105 	}
106 }
107 
108 void lcl_GetDataArea( const Reference<XSpreadsheet>& xSheet, sal_Int32& rColumnCount, sal_Int32& rRowCount )
109 {
110     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_GetDataArea" );
111 	Reference<XSheetCellCursor> xCursor = xSheet->createCursor();
112 	Reference<XCellRangeAddressable> xRange( xCursor, UNO_QUERY );
113 	if ( !xRange.is() )
114 	{
115 		rColumnCount = rRowCount = 0;
116 		return;
117 	}
118 
119 	// first find the contiguous cell area starting at A1
120 
121 	xCursor->collapseToSize( 1, 1 );		// single (first) cell
122 	xCursor->collapseToCurrentRegion();		// contiguous data area
123 
124 	CellRangeAddress aRegionAddr = xRange->getRangeAddress();
125 	sal_Int32 nEndCol = aRegionAddr.EndColumn;
126 	sal_Int32 nEndRow = aRegionAddr.EndRow;
127 
128 	Reference<XUsedAreaCursor> xUsed( xCursor, UNO_QUERY );
129 	if ( xUsed.is() )
130 	{
131 		//	The used area from XUsedAreaCursor includes visible attributes.
132 		//	If the used area is larger than the contiguous cell area, find non-empty
133 		//	cells in that area.
134 
135 		xUsed->gotoEndOfUsedArea( sal_False );
136 		CellRangeAddress aUsedAddr = xRange->getRangeAddress();
137 
138 		if ( aUsedAddr.EndColumn > aRegionAddr.EndColumn )
139 		{
140 			Reference<XCellRange> xUsedRange = xSheet->getCellRangeByPosition(
141 				aRegionAddr.EndColumn + 1, 0, aUsedAddr.EndColumn, aUsedAddr.EndRow );
142 			lcl_UpdateArea( xUsedRange, nEndCol, nEndRow );
143 		}
144 
145 		if ( aUsedAddr.EndRow > aRegionAddr.EndRow )
146 		{
147 			//	only up to the last column of aRegionAddr, the other columns are handled above
148 			Reference<XCellRange> xUsedRange = xSheet->getCellRangeByPosition(
149 				0, aRegionAddr.EndRow + 1, aRegionAddr.EndColumn, aUsedAddr.EndRow );
150 			lcl_UpdateArea( xUsedRange, nEndCol, nEndRow );
151 		}
152 	}
153 
154 	rColumnCount = nEndCol + 1;		// number of columns
155 	rRowCount = nEndRow;			// first row (headers) is not counted
156 }
157 
158 CellContentType lcl_GetContentOrResultType( const Reference<XCell>& xCell )
159 {
160     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_GetContentOrResultType" );
161 	CellContentType eCellType = xCell->getType();
162 	if ( eCellType == CellContentType_FORMULA )
163 	{
164         static const ::rtl::OUString s_sFormulaResultType(RTL_CONSTASCII_USTRINGPARAM("FormulaResultType"));
165 		Reference<XPropertySet> xProp( xCell, UNO_QUERY );
166 		try
167 		{
168 			xProp->getPropertyValue( s_sFormulaResultType ) >>= eCellType;		// type of formula result
169 		}
170 		catch (UnknownPropertyException&)
171 		{
172 			eCellType = CellContentType_VALUE;	// if FormulaResultType property not available
173 		}
174 	}
175 	return eCellType;
176 }
177 
178 Reference<XCell> lcl_GetUsedCell( const Reference<XSpreadsheet>& xSheet, sal_Int32 nDocColumn, sal_Int32 nDocRow )
179 {
180     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_GetUsedCell" );
181 	Reference<XCell> xCell = xSheet->getCellByPosition( nDocColumn, nDocRow );
182 	if ( xCell.is() && xCell->getType() == CellContentType_EMPTY )
183 	{
184 		//	get first non-empty cell
185 
186 		Reference<XCellRangeAddressable> xAddr( xSheet, UNO_QUERY );
187 		if (xAddr.is())
188 		{
189 			CellRangeAddress aTotalRange = xAddr->getRangeAddress();
190 			sal_Int32 nLastRow = aTotalRange.EndRow;
191 			Reference<XCellRangesQuery> xQuery( xSheet->getCellRangeByPosition( nDocColumn, nDocRow, nDocColumn, nLastRow ), UNO_QUERY );
192 			if (xQuery.is())
193 			{
194 				// queryIntersection to get a ranges object
195 				Reference<XSheetCellRanges> xRanges = xQuery->queryIntersection( aTotalRange );
196 				if (xRanges.is())
197 				{
198 					Reference<XEnumerationAccess> xCells = xRanges->getCells();
199 					if (xCells.is())
200 					{
201 						Reference<XEnumeration> xEnum = xCells->createEnumeration();
202 						if ( xEnum.is() && xEnum->hasMoreElements() )
203 						{
204 							// get first non-empty cell from enumeration
205                             xCell.set(xEnum->nextElement(),UNO_QUERY);
206 						}
207 						// otherwise, keep empty cell
208 					}
209 				}
210 			}
211 		}
212 	}
213 	return xCell;
214 }
215 
216 bool lcl_HasTextInColumn( const Reference<XSpreadsheet>& xSheet, sal_Int32 nDocColumn, sal_Int32 nDocRow )
217 {
218     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_HasTextInColumn" );
219     // look for any text cell or text result in the column
220 
221     Reference<XCellRangeAddressable> xAddr( xSheet, UNO_QUERY );
222     if (xAddr.is())
223     {
224         CellRangeAddress aTotalRange = xAddr->getRangeAddress();
225         sal_Int32 nLastRow = aTotalRange.EndRow;
226         Reference<XCellRangesQuery> xQuery( xSheet->getCellRangeByPosition( nDocColumn, nDocRow, nDocColumn, nLastRow ), UNO_QUERY );
227         if (xQuery.is())
228         {
229             // are there text cells in the column?
230             Reference<XSheetCellRanges> xTextContent = xQuery->queryContentCells( CellFlags::STRING );
231             if ( xTextContent.is() && xTextContent->hasElements() )
232                 return true;
233 
234             // are there formulas with text results in the column?
235             Reference<XSheetCellRanges> xTextFormula = xQuery->queryFormulaCells( FormulaResult::STRING );
236             if ( xTextFormula.is() && xTextFormula->hasElements() )
237                 return true;
238         }
239     }
240 
241     return false;
242 }
243 
244 void lcl_GetColumnInfo( const Reference<XSpreadsheet>& xSheet, const Reference<XNumberFormats>& xFormats,
245 						sal_Int32 nDocColumn, sal_Int32 nStartRow, sal_Bool bHasHeaders,
246 						::rtl::OUString& rName, sal_Int32& rDataType, sal_Bool& rCurrency )
247 {
248     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_GetColumnInfo" );
249 	//!	avoid duplicate field names
250 
251 	//	get column name from first row, if range contains headers
252 
253 	if ( bHasHeaders )
254 	{
255 		Reference<XText> xHeaderText( xSheet->getCellByPosition( nDocColumn, nStartRow ), UNO_QUERY );
256 		if ( xHeaderText.is() )
257 			rName = xHeaderText->getString();
258 	}
259 
260 	// get column type from first data row
261 
262 	sal_Int32 nDataRow = nStartRow;
263 	if ( bHasHeaders )
264 		++nDataRow;
265 	Reference<XCell> xDataCell = lcl_GetUsedCell( xSheet, nDocColumn, nDataRow );
266 
267 	Reference<XPropertySet> xProp( xDataCell, UNO_QUERY );
268 	if ( xProp.is() )
269 	{
270 		rCurrency = sal_False;			// set to true for currency below
271 
272 		const CellContentType eCellType = lcl_GetContentOrResultType( xDataCell );
273         // #i35178# use "text" type if there is any text cell in the column
274         if ( eCellType == CellContentType_TEXT || lcl_HasTextInColumn( xSheet, nDocColumn, nDataRow ) )
275 			rDataType = DataType::VARCHAR;
276 		else if ( eCellType == CellContentType_VALUE )
277 		{
278 			//	get number format to distinguish between different types
279 
280 			sal_Int16 nNumType = NumberFormat::NUMBER;
281 			try
282 			{
283                 static ::rtl::OUString s_NumberFormat(RTL_CONSTASCII_USTRINGPARAM("NumberFormat"));
284                 sal_Int32 nKey = 0;
285 
286 				if ( xProp->getPropertyValue( s_NumberFormat ) >>= nKey )
287 				{
288 					const Reference<XPropertySet> xFormat = xFormats->getByKey( nKey );
289 					if ( xFormat.is() )
290 					{
291 						xFormat->getPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE) ) >>= nNumType;
292 					}
293 				}
294 			}
295 			catch ( Exception& )
296 			{
297 			}
298 
299 			if ( nNumType & NumberFormat::TEXT )
300 				rDataType = DataType::VARCHAR;
301 			else if ( nNumType & NumberFormat::NUMBER )
302 				rDataType = DataType::DECIMAL;
303 			else if ( nNumType & NumberFormat::CURRENCY )
304 			{
305 				rCurrency = sal_True;
306 				rDataType = DataType::DECIMAL;
307 			}
308 			else if ( ( nNumType & NumberFormat::DATETIME ) == NumberFormat::DATETIME )
309 			{
310 				//	NumberFormat::DATETIME is DATE | TIME
311 				rDataType = DataType::TIMESTAMP;
312 			}
313 			else if ( nNumType & NumberFormat::DATE )
314 				rDataType = DataType::DATE;
315 			else if ( nNumType & NumberFormat::TIME )
316 				rDataType = DataType::TIME;
317 			else if ( nNumType & NumberFormat::LOGICAL )
318 				rDataType = DataType::BIT;
319 			else
320 				rDataType = DataType::DECIMAL;
321 		}
322 		else
323 		{
324 			//	whole column empty
325 			rDataType = DataType::VARCHAR;
326 		}
327 	}
328 }
329 
330 // -------------------------------------------------------------------------
331 
332 void lcl_SetValue( ORowSetValue& rValue, const Reference<XSpreadsheet>& xSheet,
333 					sal_Int32 nStartCol, sal_Int32 nStartRow, sal_Bool bHasHeaders,
334 					const ::Date& rNullDate,
335 					sal_Int32 nDBRow, sal_Int32 nDBColumn, sal_Int32 nType )
336 {
337     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_SetValue" );
338 	sal_Int32 nDocColumn = nStartCol + nDBColumn - 1;	// database counts from 1
339 	sal_Int32 nDocRow = nStartRow + nDBRow - 1;
340 	if (bHasHeaders)
341 		++nDocRow;
342 
343 	const Reference<XCell> xCell = xSheet->getCellByPosition( nDocColumn, nDocRow );
344 	if ( xCell.is() )
345 	{
346 	    CellContentType eCellType = lcl_GetContentOrResultType( xCell );
347 	    switch (nType)
348 	    {
349             case DataType::VARCHAR:
350                 if ( eCellType == CellContentType_EMPTY )
351                     rValue.setNull();
352                 else
353                 {
354                     // #i25840# still let Calc convert numbers to text
355                     const Reference<XText> xText( xCell, UNO_QUERY );
356 	                if ( xText.is() )
357 		                rValue = xText->getString();
358                 }
359                 break;
360 		    case DataType::DECIMAL:
361 			    if ( eCellType == CellContentType_VALUE )
362 				    rValue = xCell->getValue();			// double
363 			    else
364 				    rValue.setNull();
365 			    break;
366 		    case DataType::BIT:
367 			    if ( eCellType == CellContentType_VALUE )
368 				    rValue = (sal_Bool)( xCell->getValue() != 0.0 );
369 			    else
370 				    rValue.setNull();
371 			    break;
372 		    case DataType::DATE:
373 			    if ( eCellType == CellContentType_VALUE )
374 			    {
375 				    ::Date aDate( rNullDate );
376 				    aDate += (long)::rtl::math::approxFloor( xCell->getValue() );
377 				    ::com::sun::star::util::Date aDateStruct( aDate.GetDay(), aDate.GetMonth(), aDate.GetYear() );
378 				    rValue = aDateStruct;
379 			    }
380 			    else
381 				    rValue.setNull();
382 			    break;
383 		    case DataType::TIME:
384 			    if ( eCellType == CellContentType_VALUE )
385 			    {
386 				    double fCellVal = xCell->getValue();
387 				    double fTime = fCellVal - rtl::math::approxFloor( fCellVal );
388 				    long nIntTime = (long)rtl::math::round( fTime * 8640000.0 );
389 				    if ( nIntTime == 8640000 )
390 					    nIntTime = 0;						// 23:59:59.995 and above is 00:00:00.00
391 				    ::com::sun::star::util::Time aTime;
392 				    aTime.HundredthSeconds = (sal_uInt16)( nIntTime % 100 );
393 				    nIntTime /= 100;
394 				    aTime.Seconds = (sal_uInt16)( nIntTime % 60 );
395 				    nIntTime /= 60;
396 				    aTime.Minutes = (sal_uInt16)( nIntTime % 60 );
397 				    nIntTime /= 60;
398 				    OSL_ENSURE( nIntTime < 24, "error in time calculation" );
399 				    aTime.Hours = (sal_uInt16) nIntTime;
400 				    rValue = aTime;
401 			    }
402 			    else
403 				    rValue.setNull();
404 			    break;
405 		    case DataType::TIMESTAMP:
406 			    if ( eCellType == CellContentType_VALUE )
407 			    {
408 				    double fCellVal = xCell->getValue();
409 				    double fDays = ::rtl::math::approxFloor( fCellVal );
410 				    double fTime = fCellVal - fDays;
411 				    long nIntDays = (long)fDays;
412 				    long nIntTime = (long)::rtl::math::round( fTime * 8640000.0 );
413 				    if ( nIntTime == 8640000 )
414 				    {
415 					    nIntTime = 0;						// 23:59:59.995 and above is 00:00:00.00
416 					    ++nIntDays;							// (next day)
417 				    }
418 
419 				    ::com::sun::star::util::DateTime aDateTime;
420 
421 				    aDateTime.HundredthSeconds = (sal_uInt16)( nIntTime % 100 );
422 				    nIntTime /= 100;
423 				    aDateTime.Seconds = (sal_uInt16)( nIntTime % 60 );
424 				    nIntTime /= 60;
425 				    aDateTime.Minutes = (sal_uInt16)( nIntTime % 60 );
426 				    nIntTime /= 60;
427 				    OSL_ENSURE( nIntTime < 24, "error in time calculation" );
428 				    aDateTime.Hours = (sal_uInt16) nIntTime;
429 
430 				    ::Date aDate( rNullDate );
431 				    aDate += nIntDays;
432 				    aDateTime.Day = aDate.GetDay();
433 				    aDateTime.Month = aDate.GetMonth();
434 				    aDateTime.Year = aDate.GetYear();
435 
436 				    rValue = aDateTime;
437 			    }
438 			    else
439 				    rValue.setNull();
440 			    break;
441 	    } // switch (nType)
442 	}
443 
444 //	rValue.setTypeKind(nType);
445 }
446 
447 // -------------------------------------------------------------------------
448 
449 ::rtl::OUString lcl_GetColumnStr( sal_Int32 nColumn )
450 {
451     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::lcl_GetColumnStr" );
452 	if ( nColumn < 26 )
453 		return ::rtl::OUString::valueOf( (sal_Unicode) ( 'A' + nColumn ) );
454 	else
455 	{
456 		::rtl::OUStringBuffer aBuffer(2);
457 		aBuffer.setLength( 2 );
458 		aBuffer.setCharAt( 0, (sal_Unicode) ( 'A' + ( nColumn / 26 ) - 1 ) );
459 		aBuffer.setCharAt( 1, (sal_Unicode) ( 'A' + ( nColumn % 26 ) ) );
460 		return aBuffer.makeStringAndClear();
461 	}
462 }
463 
464 void OCalcTable::fillColumns()
465 {
466     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::fillColumns" );
467 	if ( !m_xSheet.is() )
468 		throw SQLException();
469 
470 	String aStrFieldName;
471 	aStrFieldName.AssignAscii("Column");
472 	::rtl::OUString aTypeName;
473 	::comphelper::UStringMixEqual aCase(m_pConnection->getMetaData()->supportsMixedCaseQuotedIdentifiers());
474     const sal_Bool bStoresMixedCaseQuotedIdentifiers = getConnection()->getMetaData()->supportsMixedCaseQuotedIdentifiers();
475 
476 	for (sal_Int32 i = 0; i < m_nDataCols; i++)
477 	{
478 		::rtl::OUString aColumnName;
479 		sal_Int32 eType = DataType::OTHER;
480 		sal_Bool bCurrency = sal_False;
481 
482 		lcl_GetColumnInfo( m_xSheet, m_xFormats, m_nStartCol + i, m_nStartRow, m_bHasHeaders,
483 							aColumnName, eType, bCurrency );
484 
485 		if ( !aColumnName.getLength() )
486 			aColumnName = lcl_GetColumnStr( i );
487 
488 		sal_Int32 nPrecision = 0;	//! ...
489 		sal_Int32 nDecimals = 0;	//! ...
490 
491 		switch ( eType )
492 		{
493 			case DataType::VARCHAR:
494                 {
495                     static const ::rtl::OUString s_sType(RTL_CONSTASCII_USTRINGPARAM("VARCHAR"));
496                     aTypeName = s_sType;
497                 }
498 				break;
499 			case DataType::DECIMAL:
500 				aTypeName = ::rtl::OUString::createFromAscii("DECIMAL");
501 				break;
502 			case DataType::BIT:
503 				aTypeName = ::rtl::OUString::createFromAscii("BOOL");
504 				break;
505 			case DataType::DATE:
506 				aTypeName = ::rtl::OUString::createFromAscii("DATE");
507 				break;
508 			case DataType::TIME:
509 				aTypeName = ::rtl::OUString::createFromAscii("TIME");
510 				break;
511 			case DataType::TIMESTAMP:
512 				aTypeName = ::rtl::OUString::createFromAscii("TIMESTAMP");
513 				break;
514 			default:
515 				OSL_ASSERT("missing type name");
516 				aTypeName = ::rtl::OUString();
517 		}
518 
519 		// check if the column name already exists
520 		::rtl::OUString aAlias = aColumnName;
521 		OSQLColumns::Vector::const_iterator aFind = connectivity::find(m_aColumns->get().begin(),m_aColumns->get().end(),aAlias,aCase);
522 		sal_Int32 nExprCnt = 0;
523 		while(aFind != m_aColumns->get().end())
524 		{
525 			(aAlias = aColumnName) += ::rtl::OUString::valueOf((sal_Int32)++nExprCnt);
526 			aFind = connectivity::find(m_aColumns->get().begin(),m_aColumns->get().end(),aAlias,aCase);
527 		}
528 
529 		sdbcx::OColumn* pColumn = new sdbcx::OColumn( aAlias, aTypeName, ::rtl::OUString(),::rtl::OUString(),
530 												ColumnValue::NULLABLE, nPrecision, nDecimals,
531 												eType, sal_False, sal_False, bCurrency,
532 												bStoresMixedCaseQuotedIdentifiers);
533 		Reference< XPropertySet> xCol = pColumn;
534 		m_aColumns->get().push_back(xCol);
535 		m_aTypes.push_back(eType);
536 		m_aPrecisions.push_back(nPrecision);
537 		m_aScales.push_back(nDecimals);
538 	}
539 }
540 
541 // -------------------------------------------------------------------------
542 OCalcTable::OCalcTable(sdbcx::OCollection* _pTables,OCalcConnection* _pConnection,
543 					const ::rtl::OUString& _Name,
544 					const ::rtl::OUString& _Type,
545 					const ::rtl::OUString& _Description ,
546 					const ::rtl::OUString& _SchemaName,
547 					const ::rtl::OUString& _CatalogName
548 				) : OCalcTable_BASE(_pTables,_pConnection,_Name,
549 								  _Type,
550 								  _Description,
551 								  _SchemaName,
552 								  _CatalogName)
553                 ,m_pConnection(_pConnection)
554 				,m_nStartCol(0)
555 				,m_nStartRow(0)
556 				,m_nDataCols(0)
557 				,m_nDataRows(0)
558 				,m_bHasHeaders(sal_False)
559 {
560     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::OCalcTable" );
561 }
562 // -----------------------------------------------------------------------------
563 void OCalcTable::construct()
564 {
565     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::construct" );
566 	//	get sheet object
567 	Reference< XSpreadsheetDocument> xDoc = m_pConnection->acquireDoc();
568 	if (xDoc.is())
569 	{
570 		Reference<XSpreadsheets> xSheets = xDoc->getSheets();
571 		if ( xSheets.is() && xSheets->hasByName( m_Name ) )
572 		{
573             m_xSheet.set(xSheets->getByName( m_Name ),UNO_QUERY);
574 			if ( m_xSheet.is() )
575 			{
576 				lcl_GetDataArea( m_xSheet, m_nDataCols, m_nDataRows );
577 				m_bHasHeaders = sal_True;
578 				// whole sheet is always assumed to include a header row
579 			}
580 		}
581 		else		// no sheet -> try database range
582 		{
583 			Reference<XPropertySet> xDocProp( xDoc, UNO_QUERY );
584 			if ( xDocProp.is() )
585 			{
586                 Reference<XDatabaseRanges> xRanges(xDocProp->getPropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("DatabaseRanges")) ),UNO_QUERY);
587 
588 				if ( xRanges.is() && xRanges->hasByName( m_Name ) )
589 				{
590                     Reference<XDatabaseRange> xDBRange(xRanges->getByName( m_Name ),UNO_QUERY);
591 					Reference<XCellRangeReferrer> xRefer( xDBRange, UNO_QUERY );
592 					if ( xRefer.is() )
593 					{
594 						//	Header flag is always stored with database range
595 						//	Get flag from FilterDescriptor
596 
597 						sal_Bool bRangeHeader = sal_True;
598 						Reference<XPropertySet> xFiltProp( xDBRange->getFilterDescriptor(), UNO_QUERY );
599 						if ( xFiltProp.is() )
600                             xFiltProp->getPropertyValue(::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("ContainsHeader"))) >>= bRangeHeader;
601 
602 						Reference<XSheetCellRange> xSheetRange( xRefer->getReferredCells(), UNO_QUERY );
603 						Reference<XCellRangeAddressable> xAddr( xSheetRange, UNO_QUERY );
604 						if ( xSheetRange.is() && xAddr.is() )
605 						{
606 							m_xSheet = xSheetRange->getSpreadsheet();
607 							CellRangeAddress aRangeAddr = xAddr->getRangeAddress();
608 							m_nStartCol = aRangeAddr.StartColumn;
609 							m_nStartRow = aRangeAddr.StartRow;
610 							m_nDataCols = aRangeAddr.EndColumn - m_nStartCol + 1;
611                             //	m_nDataRows is excluding header row
612 							m_nDataRows = aRangeAddr.EndRow - m_nStartRow;
613 							if ( !bRangeHeader )
614 							{
615 								//	m_nDataRows counts the whole range
616 								m_nDataRows += 1;
617 							}
618 
619 							m_bHasHeaders = bRangeHeader;
620 						}
621 					}
622 				}
623 			}
624 		}
625 
626 		Reference<XNumberFormatsSupplier> xSupp( xDoc, UNO_QUERY );
627 		if (xSupp.is())
628 			m_xFormats = xSupp->getNumberFormats();
629 
630 		Reference<XPropertySet> xProp( xDoc, UNO_QUERY );
631 		if (xProp.is())
632 		{
633             ::com::sun::star::util::Date aDateStruct;
634             if ( xProp->getPropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("NullDate")) ) >>= aDateStruct )
635 				m_aNullDate = ::Date( aDateStruct.Day, aDateStruct.Month, aDateStruct.Year );
636 		}
637 	}
638 
639 	//!	default if no null date available?
640 
641 	fillColumns();
642 
643 	refreshColumns();
644 }
645 // -------------------------------------------------------------------------
646 void OCalcTable::refreshColumns()
647 {
648     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::refreshColumns" );
649 	::osl::MutexGuard aGuard( m_aMutex );
650 
651 	TStringVector aVector;
652 
653     OSQLColumns::Vector::const_iterator aEnd = m_aColumns->get().end();
654 	for(OSQLColumns::Vector::const_iterator aIter = m_aColumns->get().begin();aIter != aEnd;++aIter)
655 		aVector.push_back(Reference< XNamed>(*aIter,UNO_QUERY)->getName());
656 
657 	if(m_pColumns)
658 		m_pColumns->reFill(aVector);
659 	else
660 		m_pColumns	= new OCalcColumns(this,m_aMutex,aVector);
661 }
662 // -------------------------------------------------------------------------
663 void OCalcTable::refreshIndexes()
664 {
665     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::refreshIndexes" );
666 	//	Calc table has no index
667 }
668 
669 // -------------------------------------------------------------------------
670 void SAL_CALL OCalcTable::disposing(void)
671 {
672     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::disposing" );
673 	OFileTable::disposing();
674 	::osl::MutexGuard aGuard(m_aMutex);
675 	m_aColumns = NULL;
676     if ( m_pConnection )
677         m_pConnection->releaseDoc();
678     m_pConnection = NULL;
679 
680 }
681 // -------------------------------------------------------------------------
682 Sequence< Type > SAL_CALL OCalcTable::getTypes(  ) throw(RuntimeException)
683 {
684     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::getTypes" );
685 	Sequence< Type > aTypes = OTable_TYPEDEF::getTypes();
686 	::std::vector<Type> aOwnTypes;
687 	aOwnTypes.reserve(aTypes.getLength());
688 
689 	const Type* pBegin = aTypes.getConstArray();
690 	const Type* pEnd = pBegin + aTypes.getLength();
691 	for(;pBegin != pEnd;++pBegin)
692 	{
693 		if(!(	*pBegin == ::getCppuType((const Reference<XKeysSupplier>*)0) ||
694 				*pBegin == ::getCppuType((const Reference<XIndexesSupplier>*)0) ||
695 				*pBegin == ::getCppuType((const Reference<XRename>*)0) ||
696 				*pBegin == ::getCppuType((const Reference<XAlterTable>*)0) ||
697 				*pBegin == ::getCppuType((const Reference<XDataDescriptorFactory>*)0)))
698 			aOwnTypes.push_back(*pBegin);
699 	}
700 	aOwnTypes.push_back(::getCppuType( (const Reference< ::com::sun::star::lang::XUnoTunnel > *)0 ));
701 
702 	const Type* pAttrs = aOwnTypes.empty() ? 0 : &aOwnTypes[0];
703 	return Sequence< Type >(pAttrs, aOwnTypes.size());
704 }
705 
706 // -------------------------------------------------------------------------
707 Any SAL_CALL OCalcTable::queryInterface( const Type & rType ) throw(RuntimeException)
708 {
709 	if( rType == ::getCppuType((const Reference<XKeysSupplier>*)0) ||
710 		rType == ::getCppuType((const Reference<XIndexesSupplier>*)0) ||
711 		rType == ::getCppuType((const Reference<XRename>*)0) ||
712 		rType == ::getCppuType((const Reference<XAlterTable>*)0) ||
713 		rType == ::getCppuType((const Reference<XDataDescriptorFactory>*)0))
714 		return Any();
715 
716 	const Any aRet = ::cppu::queryInterface(rType,static_cast< ::com::sun::star::lang::XUnoTunnel*> (this));
717 	return aRet.hasValue() ? aRet : OTable_TYPEDEF::queryInterface(rType);
718 }
719 
720 //--------------------------------------------------------------------------
721 Sequence< sal_Int8 > OCalcTable::getUnoTunnelImplementationId()
722 {
723     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::getUnoTunnelImplementationId" );
724 	static ::cppu::OImplementationId * pId = 0;
725 	if (! pId)
726 	{
727 		::osl::MutexGuard aGuard( ::osl::Mutex::getGlobalMutex() );
728 		if (! pId)
729 		{
730 			static ::cppu::OImplementationId aId;
731 			pId = &aId;
732 		}
733 	}
734 	return pId->getImplementationId();
735 }
736 
737 // com::sun::star::lang::XUnoTunnel
738 //------------------------------------------------------------------
739 sal_Int64 OCalcTable::getSomething( const Sequence< sal_Int8 > & rId ) throw (RuntimeException)
740 {
741     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::getSomething" );
742 	return (rId.getLength() == 16 && 0 == rtl_compareMemory(getUnoTunnelImplementationId().getConstArray(),  rId.getConstArray(), 16 ) )
743 				? reinterpret_cast< sal_Int64 >( this )
744 				: OCalcTable_BASE::getSomething(rId);
745 }
746 //------------------------------------------------------------------
747 sal_Int32 OCalcTable::getCurrentLastPos() const
748 {
749     //RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::getCurrentLastPos" );
750 	return m_nDataRows;
751 }
752 //------------------------------------------------------------------
753 sal_Bool OCalcTable::seekRow(IResultSetHelper::Movement eCursorPosition, sal_Int32 nOffset, sal_Int32& nCurPos)
754 {
755     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::seekRow" );
756 	// ----------------------------------------------------------
757 	// Positionierung vorbereiten:
758 
759 	sal_uInt32 nNumberOfRecords = m_nDataRows;
760 	sal_uInt32 nTempPos = m_nFilePos;
761 	m_nFilePos = nCurPos;
762 
763 	switch(eCursorPosition)
764 	{
765 		case IResultSetHelper::NEXT:
766 			m_nFilePos++;
767 			break;
768 		case IResultSetHelper::PRIOR:
769 			if (m_nFilePos > 0)
770 				m_nFilePos--;
771 			break;
772 		case IResultSetHelper::FIRST:
773 			m_nFilePos = 1;
774 			break;
775 		case IResultSetHelper::LAST:
776 			m_nFilePos = nNumberOfRecords;
777 			break;
778 		case IResultSetHelper::RELATIVE:
779 			m_nFilePos = (((sal_Int32)m_nFilePos) + nOffset < 0) ? 0L
780 							: (sal_uInt32)(((sal_Int32)m_nFilePos) + nOffset);
781 			break;
782 		case IResultSetHelper::ABSOLUTE:
783 		case IResultSetHelper::BOOKMARK:
784 			m_nFilePos = (sal_uInt32)nOffset;
785 			break;
786 	}
787 
788 	if (m_nFilePos > (sal_Int32)nNumberOfRecords)
789 		m_nFilePos = (sal_Int32)nNumberOfRecords + 1;
790 
791 	if (m_nFilePos == 0 || m_nFilePos == (sal_Int32)nNumberOfRecords + 1)
792 		goto Error;
793 	else
794 	{
795 		//!	read buffer / setup row object etc?
796 	}
797 	goto End;
798 
799 Error:
800 	switch(eCursorPosition)
801 	{
802 		case IResultSetHelper::PRIOR:
803 		case IResultSetHelper::FIRST:
804 			m_nFilePos = 0;
805 			break;
806 		case IResultSetHelper::LAST:
807 		case IResultSetHelper::NEXT:
808 		case IResultSetHelper::ABSOLUTE:
809 		case IResultSetHelper::RELATIVE:
810 			if (nOffset > 0)
811 				m_nFilePos = nNumberOfRecords + 1;
812 			else if (nOffset < 0)
813 				m_nFilePos = 0;
814 			break;
815 		case IResultSetHelper::BOOKMARK:
816 			m_nFilePos = nTempPos;	 // vorherige Position
817 	}
818 	//	aStatus.Set(SDB_STAT_NO_DATA_FOUND);
819 	return sal_False;
820 
821 End:
822 	nCurPos = m_nFilePos;
823 	return sal_True;
824 }
825 //------------------------------------------------------------------
826 sal_Bool OCalcTable::fetchRow( OValueRefRow& _rRow, const OSQLColumns & _rCols,
827 								sal_Bool _bUseTableDefs, sal_Bool bRetrieveData )
828 {
829     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::fetchRow" );
830 	// read the bookmark
831 
832 	sal_Bool bIsCurRecordDeleted = sal_False;
833 	_rRow->setDeleted(bIsCurRecordDeleted);
834 	*(_rRow->get())[0] = m_nFilePos;
835 
836 	if (!bRetrieveData)
837 		return sal_True;
838 
839 	// fields
840 
841 	OSQLColumns::Vector::const_iterator aIter = _rCols.get().begin();
842     OSQLColumns::Vector::const_iterator aEnd = _rCols.get().end();
843     const OValueRefVector::Vector::size_type nCount = _rRow->get().size();
844 	for (OValueRefVector::Vector::size_type i = 1; aIter != aEnd && i < nCount;
845          ++aIter, i++)
846 	{
847         if ( (_rRow->get())[i]->isBound() )
848         {
849 		    sal_Int32 nType = 0;
850 		    if ( _bUseTableDefs )
851 			    nType = m_aTypes[i-1];
852 		    else
853 			    (*aIter)->getPropertyValue(OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_TYPE)) >>= nType;
854 
855 
856 		    lcl_SetValue( (_rRow->get())[i]->get(), m_xSheet, m_nStartCol, m_nStartRow, m_bHasHeaders,
857 							    m_aNullDate, m_nFilePos, i, nType );
858         }
859 	}
860 	return sal_True;
861 }
862 // -------------------------------------------------------------------------
863 void OCalcTable::FileClose()
864 {
865     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "calc", "Ocke.Janssen@sun.com", "OCalcTable::FileClose" );
866 	::osl::MutexGuard aGuard(m_aMutex);
867 
868 	OCalcTable_BASE::FileClose();
869 }
870 // -------------------------------------------------------------------------
871 
872