allow database ranges from spreadsheet document as tables

3 files changed, 201 insertions, 37 deletions

diff --git a/connectivity/source/drivers/calc/CDatabaseMetaData.cxx b/connectivity/source/drivers/calc/CDatabaseMetaData.cxx
index c01d9ff693fa..f9a842cf9373 100644
--- a/connectivity/source/drivers/calc/CDatabaseMetaData.cxx
+++ b/connectivity/source/drivers/calc/CDatabaseMetaData.cxx
@@ -2,9 +2,9 @@
  *
  *  $RCSfile: CDatabaseMetaData.cxx,v $
  *
- *  $Revision: 1.4 $
+ *  $Revision: 1.5 $
  *
- *  last change: $Author: oj $ $Date: 2001-02-20 13:09:01 $
+ *  last change: $Author: nn $ $Date: 2001-02-21 11:35:24 $
  *
  *  The Contents of this file are made available subject to the terms of
  *  either of the following licenses
@@ -99,6 +99,12 @@
 #ifndef _COM_SUN_STAR_SHEET_XCELLRANGEADDRESSABLE_HPP_
 #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
 #endif
+#ifndef _COM_SUN_STAR_SHEET_XDATABASERANGES_HPP_
+#include <com/sun/star/sheet/XDatabaseRanges.hpp>
+#endif
+#ifndef _COM_SUN_STAR_SHEET_XDATABASERANGE_HPP_
+#include <com/sun/star/sheet/XDatabaseRange.hpp>
+#endif
 #ifndef _URLOBJ_HXX //autogen wg. INetURLObject
 #include <tools/urlobj.hxx>
 #endif
@@ -491,7 +497,8 @@ sal_Bool lcl_IsEmptyOrHidden( const Reference<XSpreadsheets>& xSheets, const ::r
                     return sal_True;                // hidden
         }
 
-        //  test if sheet is empty
+#if 0
+        //  test if whole sheet is empty
 
         Reference<XCellRangeAddressable> xAddr( xSheet, UNO_QUERY );
         Reference<XCellRangesQuery> xQuery( xSheet, UNO_QUERY );
@@ -510,11 +517,60 @@ sal_Bool lcl_IsEmptyOrHidden( const Reference<XSpreadsheets>& xSheets, const ::r
                 }
             }
         }
+#endif
+
+        //  use the same data area as in OCalcTable to test for empty table
+
+        Reference<XSheetCellCursor> xCursor = xSheet->createCursor();
+        Reference<XCellRangeAddressable> xRange( xCursor, UNO_QUERY );
+        if ( xRange.is() )
+        {
+            xCursor->collapseToSize( 1, 1 );        // single (first) cell
+            xCursor->collapseToCurrentRegion();     // contiguous data area
+
+            CellRangeAddress aRangeAddr = xRange->getRangeAddress();
+            if ( aRangeAddr.StartColumn == aRangeAddr.EndColumn &&
+                 aRangeAddr.StartRow == aRangeAddr.EndRow )
+            {
+                //  single cell -> check content
+                Reference<XCell> xCell = xCursor->getCellByPosition( 0, 0 );
+                if ( xCell.is() && xCell->getType() == CellContentType_EMPTY )
+                    return sal_True;
+            }
+        }
     }
 
     return sal_False;
 }
 
+sal_Bool lcl_IsUnnamed( const Reference<XDatabaseRanges>& xRanges, const ::rtl::OUString& rName )
+{
+    sal_Bool bUnnamed = sal_False;
+
+    Any aAny = xRanges->getByName( rName );
+    Reference<XDatabaseRange> xRange;
+    if ( aAny >>= xRange )
+    {
+        Reference<XPropertySet> xRangeProp( xRange, UNO_QUERY );
+        if ( xRangeProp.is() )
+        {
+            try
+            {
+                Any aUserAny = xRangeProp->getPropertyValue( ::rtl::OUString::createFromAscii("IsUserDefined") );
+                sal_Bool bUserDefined;
+                if ( aUserAny >>= bUserDefined )
+                    bUnnamed = !bUserDefined;
+            }
+            catch ( UnknownPropertyException& )
+            {
+                // optional property
+            }
+        }
+    }
+
+    return bUnnamed;
+}
+
 // -------------------------------------------------------------------------
 
 Reference< XResultSet > SAL_CALL OCalcDatabaseMetaData::getTables(
@@ -578,6 +634,32 @@ Reference< XResultSet > SAL_CALL OCalcDatabaseMetaData::getTables(
         }
     }
 
+    // also use database ranges
+
+    Reference<XPropertySet> xDocProp( xDoc, UNO_QUERY );
+    if ( xDocProp.is() )
+    {
+        Any aRangesAny = xDocProp->getPropertyValue( ::rtl::OUString::createFromAscii("DatabaseRanges") );
+        Reference<XDatabaseRanges> xRanges;
+        if ( aRangesAny >>= xRanges )
+        {
+            Sequence< ::rtl::OUString > aDBNames = xRanges->getElementNames();
+            sal_Int32 nDBCount = aDBNames.getLength();
+            for (sal_Int32 nRange=0; nRange<nDBCount; nRange++)
+            {
+                ::rtl::OUString aName = aDBNames[nRange];
+                if ( !lcl_IsUnnamed( xRanges, aName ) )
+                {
+                    ORow aRow(3);
+                    aRow.push_back(ORowSetValue(aName));
+                    aRow.push_back(ORowSetValue(aTable));
+                    aRow.push_back(ORowSetValue());
+                    aRows.push_back(aRow);
+                }
+            }
+        }
+    }
+
     pResult->setRows(aRows);
 
     return xRef;
diff --git a/connectivity/source/drivers/calc/CTable.cxx b/connectivity/source/drivers/calc/CTable.cxx
index 24636e36ab04..80ee4b618ffc 100644
--- a/connectivity/source/drivers/calc/CTable.cxx
+++ b/connectivity/source/drivers/calc/CTable.cxx
@@ -2,9 +2,9 @@
  *
  *  $RCSfile: CTable.cxx,v $
  *
- *  $Revision: 1.2 $
+ *  $Revision: 1.3 $
  *
- *  last change: $Author: nn $ $Date: 2001-01-29 19:21:15 $
+ *  last change: $Author: nn $ $Date: 2001-02-21 11:35:24 $
  *
  *  The Contents of this file are made available subject to the terms of
  *  either of the following licenses
@@ -86,6 +86,15 @@
 #ifndef _COM_SUN_STAR_SHEET_XCELLRANGESQUERY_HPP_
 #include <com/sun/star/sheet/XCellRangesQuery.hpp>
 #endif
+#ifndef _COM_SUN_STAR_SHEET_XDATABASERANGES_HPP_
+#include <com/sun/star/sheet/XDatabaseRanges.hpp>
+#endif
+#ifndef _COM_SUN_STAR_SHEET_XDATABASERANGE_HPP_
+#include <com/sun/star/sheet/XDatabaseRange.hpp>
+#endif
+#ifndef _COM_SUN_STAR_SHEET_XCELLRANGEREFERRER_HPP_
+#include <com/sun/star/sheet/XCellRangeReferrer.hpp>
+#endif
 #ifndef _COM_SUN_STAR_UTIL_NUMBERFORMAT_HPP_
 #include <com/sun/star/util/NumberFormat.hpp>
 #endif
@@ -261,32 +270,34 @@ Reference<XCell> lcl_GetUsedCell( const Reference<XSpreadsheet>& xSheet, sal_Int
 }
 
 void lcl_GetColumnInfo( const Reference<XSpreadsheet>& xSheet, const Reference<XNumberFormats>& xFormats,
-                        sal_Int32 nDocColumn,
+                        sal_Int32 nDocColumn, sal_Int32 nStartRow, sal_Bool bHasHeaders,
                         ::rtl::OUString& rName, sal_Int32& rDataType, sal_Bool& rCurrency )
 {
-    //  nDocColumn is 0-based
-
-    //! detect missing header row
     //! avoid duplicate field names
 
-    //  get column name from first row
+    //  get column name from first row, if range contains headers
 
-    Reference<XCell> xCell = xSheet->getCellByPosition( nDocColumn, 0 );
-    Reference<XText> xText( xCell, UNO_QUERY );
-    if ( xText.is() )
-        rName = xText->getString();
+    if ( bHasHeaders )
+    {
+        Reference<XCell> xHeaderCell = xSheet->getCellByPosition( nDocColumn, nStartRow );
+        Reference<XText> xHeaderText( xHeaderCell, UNO_QUERY );
+        if ( xHeaderText.is() )
+            rName = xHeaderText->getString();
+    }
 
     // get column type from first data row
 
-    sal_Int32 nDataRow = 1;         //! detect missing header row
-    xCell = lcl_GetUsedCell( xSheet, nDocColumn, nDataRow );
+    sal_Int32 nDataRow = nStartRow;
+    if ( bHasHeaders )
+        ++nDataRow;
+    Reference<XCell> xDataCell = lcl_GetUsedCell( xSheet, nDocColumn, nDataRow );
 
-    Reference<XPropertySet> xProp( xCell, UNO_QUERY );
+    Reference<XPropertySet> xProp( xDataCell, UNO_QUERY );
     if ( xProp.is() )
     {
         rCurrency = sal_False;          // set to true for currency below
 
-        CellContentType eCellType = lcl_GetContentOrResultType( xCell );
+        CellContentType eCellType = lcl_GetContentOrResultType( xDataCell );
         if ( eCellType == CellContentType_TEXT )
             rDataType = DataType::VARCHAR;
         else if ( eCellType == CellContentType_VALUE )
@@ -344,11 +355,14 @@ void lcl_GetColumnInfo( const Reference<XSpreadsheet>& xSheet, const Reference<X
 // -------------------------------------------------------------------------
 
 void lcl_SetValue( file::ORowSetValue& rValue, const Reference<XSpreadsheet>& xSheet,
+                    sal_Int32 nStartCol, sal_Int32 nStartRow, sal_Bool bHasHeaders,
                     const ::Date& rNullDate,
                     sal_Int32 nDBRow, sal_Int32 nDBColumn, sal_Int32 nType )
 {
-    sal_Int32 nDocColumn = nDBColumn - 1;   // database counts from 1
-    sal_Int32 nDocRow = nDBRow;             //! detect missing header row
+    sal_Int32 nDocColumn = nStartCol + nDBColumn - 1;   // database counts from 1
+    sal_Int32 nDocRow = nStartRow + nDBRow - 1;
+    if (bHasHeaders)
+        ++nDocRow;
 
     Reference<XCell> xCell = xSheet->getCellByPosition( nDocColumn, nDocRow );
     if ( xCell.is() )
@@ -474,22 +488,19 @@ void OCalcTable::fillColumns()
     if ( !m_xSheet.is() )
         throw SQLException();
 
-    m_nDataRows = lcl_RowCount( m_xSheet );
-
-    sal_Int32 nFieldCount = lcl_ColumnCount( m_xSheet );
-
     String aStrFieldName;
     aStrFieldName.AssignAscii("Column");
     ::rtl::OUString aTypeName;
     ::comphelper::UStringMixEqual aCase(m_pConnection->getMetaData()->storesMixedCaseQuotedIdentifiers());
 
-    for (sal_Int32 i = 0; i < nFieldCount; i++)
+    for (sal_Int32 i = 0; i < m_nDataCols; i++)
     {
         ::rtl::OUString aColumnName;
         sal_Int32 eType = DataType::OTHER;
         sal_Bool bCurrency = sal_False;
 
-        lcl_GetColumnInfo( m_xSheet, m_xFormats, i, aColumnName, eType, bCurrency );
+        lcl_GetColumnInfo( m_xSheet, m_xFormats, m_nStartCol + i, m_nStartRow, m_bHasHeaders,
+                            aColumnName, eType, bCurrency );
 
         if ( !aColumnName.getLength() )
             aColumnName = lcl_GetColumnStr( i );
@@ -547,7 +558,11 @@ void OCalcTable::fillColumns()
 // -------------------------------------------------------------------------
 OCalcTable::OCalcTable(OCalcConnection* _pConnection)
         :OCalcTable_BASE(_pConnection)
+        ,m_nStartCol(0)
+        ,m_nStartRow(0)
+        ,m_nDataCols(0)
         ,m_nDataRows(0)
+        ,m_bHasHeaders(sal_False)
 {
 }
 // -------------------------------------------------------------------------
@@ -562,7 +577,11 @@ OCalcTable::OCalcTable(OCalcConnection* _pConnection,
                                   _Description,
                                   _SchemaName,
                                   _CatalogName)
+                ,m_nStartCol(0)
+                ,m_nStartRow(0)
+                ,m_nDataCols(0)
                 ,m_nDataRows(0)
+                ,m_bHasHeaders(sal_False)
 {
     //  get sheet object
 
@@ -570,15 +589,75 @@ OCalcTable::OCalcTable(OCalcConnection* _pConnection,
     if (xDoc.is())
     {
         Reference<XSpreadsheets> xSheets = xDoc->getSheets();
-        if (xSheets.is())
+        if ( xSheets.is() && xSheets->hasByName( _Name ) )
         {
-            try
+            Any aAny = xSheets->getByName( _Name );
+            if ( aAny >>= m_xSheet )
             {
-                Any aAny = xSheets->getByName( _Name );
-                aAny >>= m_xSheet;
+                m_nDataCols = lcl_ColumnCount( m_xSheet );
+                m_nDataRows = lcl_RowCount( m_xSheet );
+                m_bHasHeaders = sal_True;
+                // whole sheet is always assumed to include a header row
             }
-            catch (NoSuchElementException&)
+        }
+        else        // no sheet -> try database range
+        {
+            Reference<XPropertySet> xDocProp( xDoc, UNO_QUERY );
+            if ( xDocProp.is() )
             {
+                Any aRangesAny = xDocProp->getPropertyValue( ::rtl::OUString::createFromAscii("DatabaseRanges") );
+                Reference<XDatabaseRanges> xRanges;
+                if ( aRangesAny >>= xRanges )
+                {
+                    if ( xRanges.is() && xRanges->hasByName( _Name ) )
+                    {
+                        Any aAny = xRanges->getByName( _Name );
+                        Reference<XDatabaseRange> xDBRange;
+                        if ( aAny >>= xDBRange )
+                        {
+                            Reference<XCellRangeReferrer> xRefer( xDBRange, UNO_QUERY );
+                            if ( xRefer.is() )
+                            {
+                                //  Header flag is always stored with database range
+                                //  Get flag from FilterDescriptor
+
+                                sal_Bool bRangeHeader = sal_True;
+                                Reference<XSheetFilterDescriptor> xFilter = xDBRange->getFilterDescriptor();
+                                Reference<XPropertySet> xFiltProp( xFilter, UNO_QUERY );
+                                if ( xFiltProp.is() )
+                                {
+                                    Any aHdrAny = xFiltProp->getPropertyValue(
+                                            ::rtl::OUString::createFromAscii("ContainsHeader") );
+                                    aHdrAny >>= bRangeHeader;
+                                }
+
+                                Reference<XCellRange> xCellRange = xRefer->getReferredCells();
+                                Reference<XSheetCellRange> xSheetRange( xCellRange, UNO_QUERY );
+                                Reference<XCellRangeAddressable> xAddr( xCellRange, UNO_QUERY );
+                                if ( xSheetRange.is() && xAddr.is() )
+                                {
+                                    m_xSheet = xSheetRange->getSpreadsheet();
+                                    CellRangeAddress aRangeAddr = xAddr->getRangeAddress();
+                                    m_nStartCol = aRangeAddr.StartColumn;
+                                    m_nStartRow = aRangeAddr.StartRow;
+                                    m_nDataCols = aRangeAddr.EndColumn - m_nStartCol + 1;
+                                    if ( bRangeHeader )
+                                    {
+                                        //  m_nDataRows is excluding header row
+                                        m_nDataRows = aRangeAddr.EndRow - m_nStartRow;
+                                    }
+                                    else
+                                    {
+                                        //  m_nDataRows counts the whole range
+                                        m_nDataRows = aRangeAddr.EndRow - m_nStartRow + 1;
+                                    }
+
+                                    m_bHasHeaders = bRangeHeader;
+                                }
+                            }
+                        }
+                    }
+                }
             }
         }
 
@@ -598,8 +677,6 @@ OCalcTable::OCalcTable(OCalcConnection* _pConnection,
 
     //! default if no null date available?
 
-    //! detect missing header row
-
     fillColumns();
 
     refreshColumns();
@@ -811,7 +888,8 @@ sal_Bool OCalcTable::fetchRow( file::OValueRow _rRow, const OSQLColumns & _rCols
             xColumn->getPropertyValue(PROPERTY_TYPE) >>= nType;
 
         if ((*_rRow)[i].isBound())
-            lcl_SetValue( (*_rRow)[i], m_xSheet, m_aNullDate, m_nFilePos, i, nType );
+            lcl_SetValue( (*_rRow)[i], m_xSheet, m_nStartCol, m_nStartRow, m_bHasHeaders,
+                            m_aNullDate, m_nFilePos, i, nType );
     }
     return sal_True;
 }
diff --git a/connectivity/source/inc/calc/CTable.hxx b/connectivity/source/inc/calc/CTable.hxx
index f52fc573c42a..57030e7f0c15 100644
--- a/connectivity/source/inc/calc/CTable.hxx
+++ b/connectivity/source/inc/calc/CTable.hxx
@@ -2,9 +2,9 @@
  *
  *  $RCSfile: CTable.hxx,v $
  *
- *  $Revision: 1.2 $
+ *  $Revision: 1.3 $
  *
- *  last change: $Author: nn $ $Date: 2001-01-29 19:19:53 $
+ *  last change: $Author: nn $ $Date: 2001-02-21 11:34:47 $
  *
  *  The Contents of this file are made available subject to the terms of
  *  either of the following licenses
@@ -96,9 +96,13 @@ namespace connectivity
             ::std::vector<sal_Int32> m_aPrecisions; // same as aboth
             ::std::vector<sal_Int32> m_aScales;
             ::com::sun::star::uno::Reference< ::com::sun::star::sheet::XSpreadsheet > m_xSheet;
+            sal_Int32 m_nStartCol;
+            sal_Int32 m_nStartRow;
+            sal_Int32 m_nDataCols;
+            sal_Int32 m_nDataRows;
+            sal_Bool m_bHasHeaders;
             ::com::sun::star::uno::Reference< ::com::sun::star::util::XNumberFormats > m_xFormats;
             ::Date m_aNullDate;
-            sal_Int32 m_nDataRows;
 
             void fillColumns();