connectivity/source/drivers/calc/CTable.cxx

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