CWS-TOOLING: integrate CWS koheiextref01

25 files changed, 543 insertions, 546 deletions

diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index 2a7044839..4601faa89 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -275,7 +275,6 @@ public:
     void		CalcAfterLoad();
     void		CompileAll();
     void		CompileXML( ScProgress& rProgress );
-    bool        MarkUsedExternalReferences();
 
     void		ResetChanged( SCROW nStartRow, SCROW nEndRow );
 
diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index c82654f4b..372527f5e 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -905,8 +905,8 @@ public:
                     /** Shrink a range to only include used data area. */
     bool            ShrinkToUsedDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow, SCCOL& rEndCol, SCROW& rEndRow, bool bColumnsOnly ) const;
 
-    void			GetDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
-                                    SCCOL& rEndCol, SCROW& rEndRow, BOOL bIncludeOld, bool bOnlyDown );
+    void            GetDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
+                                 SCCOL& rEndCol, SCROW& rEndRow, BOOL bIncludeOld, bool bOnlyDown ) const;
     SC_DLLPUBLIC BOOL			GetCellArea( SCTAB nTab, SCCOL& rEndCol, SCROW& rEndRow ) const;
     SC_DLLPUBLIC BOOL			GetTableArea( SCTAB nTab, SCCOL& rEndCol, SCROW& rEndRow ) const;
     SC_DLLPUBLIC BOOL			GetPrintArea( SCTAB nTab, SCCOL& rEndCol, SCROW& rEndRow,
diff --git a/sc/inc/externalrefmgr.hxx b/sc/inc/externalrefmgr.hxx
index dd6f9f4ef..49627f227 100644
--- a/sc/inc/externalrefmgr.hxx
+++ b/sc/inc/externalrefmgr.hxx
@@ -36,12 +36,14 @@
 #include "vcl/timer.hxx"
 #include "svl/zforlist.hxx"
 #include "scmatrix.hxx"
+#include "rangelst.hxx"
 
 #include <hash_map>
 #include <hash_set>
 #include <boost/shared_ptr.hpp>
 #include <vector>
 #include <list>
+#include <set>
 #include <formula/ExternalReferenceHelper.hxx>
 
 class ScDocument;
@@ -55,6 +57,7 @@ class ScTokenArray;
 class String;
 class SfxObjectShellRef;
 class Window;
+class ScFormulaCell;
 
 class ScExternalRefCache;
 
@@ -126,6 +129,15 @@ public:
     class Table;
     friend class ScExternalRefCache::Table;
 
+    /** 
+     * Represents a single cached table in an external document.  It only 
+     * stores non-empty cells; empty cells should never be stored in the data 
+     * cache. Instead, cached ranges should be used to determine whether or 
+     * not a cell is empty or needs fetching from the source document.  If a 
+     * cell's value is not stored but its address is within the cached ranges, 
+     * that cell is already queried in the source document and we know it's 
+     * empty. 
+     */
     class Table
     {
     public:
@@ -140,7 +152,14 @@ public:
         Table();
         ~Table();
 
-        SC_DLLPUBLIC void setCell(SCCOL nCol, SCROW nRow, TokenRef pToken, sal_uInt32 nFmtIndex = 0);
+        /** 
+         * Add cell value to the cache.
+         *
+         * @param bSetCacheRange if true, mark this cell 'cached'.  This is 
+         *                       false _only when_ adding a range of cell
+         *                       values, for performance reasons.
+         */
+        SC_DLLPUBLIC void setCell(SCCOL nCol, SCROW nRow, TokenRef pToken, sal_uInt32 nFmtIndex = 0, bool bSetCacheRange = true);
         SC_DLLPUBLIC TokenRef getCell(SCCOL nCol, SCROW nRow, sal_uInt32* pnFmtIndex = NULL) const;
         bool hasRow( SCROW nRow ) const;
         /** Set/clear referenced status flag only if current status is not
@@ -151,18 +170,39 @@ public:
         ReferencedFlag getReferencedFlag() const;
         bool isReferenced() const;
         /// Obtain a sorted vector of rows.
-        void getAllRows(::std::vector<SCROW>& rRows) const;
+        void getAllRows(::std::vector<SCROW>& rRows, SCROW nLow = 0, SCROW nHigh = MAXROW) const;
         /// Returns the half-open range of used rows in this table. Returns [0,0) if table is empty.
         SC_DLLPUBLIC ::std::pair< SCROW, SCROW > getRowRange() const;
         /// Obtain a sorted vector of columns.
-        void getAllCols(SCROW nRow, ::std::vector<SCCOL>& rCols) const;
+        void getAllCols(SCROW nRow, ::std::vector<SCCOL>& rCols, SCCOL nLow = 0, SCCOL nHigh = MAXCOL) const;
         /// Returns the half-open range of used columns in the specified row. Returns [0,0) if row is empty.
         SC_DLLPUBLIC ::std::pair< SCCOL, SCCOL > getColRange( SCROW nRow ) const;
         void getAllNumberFormats(::std::vector<sal_uInt32>& rNumFmts) const;
+        const ScRangeList& getCachedRanges() const;
+        bool isRangeCached(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2) const;
+
+        void setCachedCell(SCCOL nCol, SCROW nRow);
+        void setCachedCellRange(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2);
+
+        /** 
+         * Call this to mark the entire table "cached".  This will prevent all
+         * future attempts to access the source document even when non-cached 
+         * cells are queried.  In such case, non-cached cells are treated as 
+         * empty cells.  Useful when loading a document with own external data 
+         * cache. 
+         */
+        SC_DLLPUBLIC void setWholeTableCached();
+    private:
+        bool isInCachedRanges(SCCOL nCol, SCROW nRow) const;
+        TokenRef getEmptyOrNullToken(SCCOL nCol, SCROW nRow) const;
 
     private:
-        RowsDataType   maRows;
-        ReferencedFlag meReferenced;
+        /** Data cache */
+        RowsDataType                    maRows;
+        /** Collection of individual cached ranges.  The table ranges are
+         *  not used & always zero. */
+        ScRangeList                     maCachedRanges;
+        ReferencedFlag                  meReferenced;
     };
 
     typedef ::boost::shared_ptr<Table>      TableTypeRef;
@@ -185,8 +225,7 @@ public:
      * @return pointer to the token instance in the cache.
      */
     ScExternalRefCache::TokenRef getCellData(
-        sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow,
-        bool bEmptyCellOnNull, bool bWriteEmpty, sal_uInt32* pnFmtIndex);
+        sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow, sal_uInt32* pnFmtIndex);
 
     /**
      * Get a cached cell range data.
@@ -196,12 +235,12 @@ public:
      *         guaranteed if the TokenArrayRef is properly used..
      */
     ScExternalRefCache::TokenArrayRef getCellRangeData(
-        sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, bool bEmptyCellOnNull, bool bWriteEmpty);
+        sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange);
 
     ScExternalRefCache::TokenArrayRef getRangeNameTokens(sal_uInt16 nFileId, const String& rName);
     void setRangeNameTokens(sal_uInt16 nFileId, const String& rName, TokenArrayRef pArray);
 
-    void setCellData(sal_uInt16 nFileId, const String& rTabName, SCROW nRow, SCCOL nCol, TokenRef pToken, sal_uInt32 nFmtIndex);
+    void setCellData(sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow, TokenRef pToken, sal_uInt32 nFmtIndex);
 
     struct SingleRangeData
     {
@@ -325,58 +364,8 @@ class SC_DLLPUBLIC ScExternalRefManager : public formula::ExternalReferenceHelpe
 {
 public:
 
-    // SUNWS needs a forward declared friend, otherwise types and members
-    // of the outer class are not accessible.
-    class RefCells;
-    friend class ScExternalRefManager::RefCells;
-
-    /**
-     *  Collection of cell addresses that contain external references. This
-     *  data is used for link updates.
-     */
-    class RefCells
-    {
-    public:
-        RefCells();
-        ~RefCells();
-
-        void insertCell(const ScAddress& rAddr);
-        void removeCell(const ScAddress& rAddr);
-        void moveTable(SCTAB nOldTab, SCTAB nNewTab, bool bCopy);
-        void insertTable(SCTAB nPos);
-        void removeTable(SCTAB nPos);
-        void refreshAllCells(ScExternalRefManager& rRefMgr);
-    private:
-
-        typedef ::std::hash_set<SCROW>              RowSet;
-        typedef ::std::hash_map<SCCOL, RowSet>      ColSet;
-
-        // SUNWS needs a forward declared friend, otherwise types and members
-        // of the outer class are not accessible.
-        struct TabItem;
-        friend struct ScExternalRefManager::RefCells::TabItem;
-
-        struct TabItem
-        {
-            SCTAB       mnIndex;
-            ColSet      maCols;
-            explicit TabItem(SCTAB nIndex);
-            explicit TabItem(const TabItem& r);
-        };
-        typedef ::boost::shared_ptr<TabItem>        TabItemRef;
-
-        /**
-         * Return the position that points either to the specified table
-         * position or to the position where a new table would be inserted in
-         * case the specified table is not present.
-         *
-         * @param nTab index of the desired table
-         */
-        ::std::list<TabItemRef>::iterator getTabPos(SCTAB nTab);
-
-        // This list must be sorted by the table index at all times.
-        ::std::list<TabItemRef> maTables;
-    };
+    typedef ::std::set<ScFormulaCell*>                      RefCellSet;
+    typedef ::std::hash_map<sal_uInt16, RefCellSet>         RefCellMap;
 
     enum LinkUpdateType { LINK_MODIFIED, LINK_BROKEN };
 
@@ -401,6 +390,21 @@ public:
         };
     };
 
+    /** 
+     * Use this guard when performing something from the API that might query 
+     * values from external references.  Interpreting formula strings is one 
+     * such example. 
+     */
+    class ApiGuard
+    {
+    public:
+        ApiGuard(ScDocument* pDoc);
+        ~ApiGuard();
+    private:
+        ScExternalRefManager* mpMgr;
+        bool mbOldInteractionEnabled;
+    };
+
 private:
     /** Shell instance for a source document. */
     struct SrcShell
@@ -412,7 +416,6 @@ private:
     typedef ::std::hash_map<sal_uInt16, SrcShell>           DocShellMap;
     typedef ::std::hash_map<sal_uInt16, bool>               LinkedDocMap;
 
-    typedef ::std::hash_map<sal_uInt16, RefCells>           RefCellMap;
     typedef ::std::hash_map<sal_uInt16, SvNumberFormatterMergeMap> NumFmtMap;
 
 
@@ -516,12 +519,7 @@ public:
      */
     bool markUsedByLinkListeners();
 
-    /**
-     * Set all tables of a document as referenced, used only during
-     * store-to-file.
-     * @returns <TRUE/> if ALL tables of ALL external documents are marked.
-     */
-    bool setCacheDocReferenced( sal_uInt16 nFileId );
+    bool markUsedExternalRefCells();
 
     /**
      * Set a table as referenced, used only during store-to-file.
@@ -540,7 +538,7 @@ public:
      * @returns <TRUE/> if setAllCacheTableReferencedStati(false) was called,
      * <FALSE/> if setAllCacheTableReferencedStati(true) was called.
      */
-    bool isInReferenceMarking() const   { return bInReferenceMarking; }
+    bool isInReferenceMarking() const   { return mbInReferenceMarking; }
 
     void storeRangeNameTokens(sal_uInt16 nFileId, const String& rName, const ScTokenArray& rArray);
 
@@ -561,7 +559,8 @@ public:
      * @return shared_ptr to a token array instance.  <i>The caller must not
      *         delete the instance returned by this method.</i>
      */
-    ScExternalRefCache::TokenArrayRef getDoubleRefTokens(sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, const ScAddress* pCurPos);
+    ScExternalRefCache::TokenArrayRef getDoubleRefTokens(
+        sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, const ScAddress* pCurPos);
 
     /**
      * Get an array of tokens corresponding with a specified name in a
@@ -574,7 +573,8 @@ public:
      *
      * @return shared_ptr to array of tokens composing the name
      */
-    ScExternalRefCache::TokenArrayRef getRangeNameTokens(sal_uInt16 nFileId, const String& rName, const ScAddress* pCurPos = NULL);
+    ScExternalRefCache::TokenArrayRef getRangeNameTokens(
+        sal_uInt16 nFileId, const String& rName, const ScAddress* pCurPos = NULL);
 
     const String& getOwnDocumentName() const;
     bool isOwnDocument(const String& rFile) const;
@@ -645,33 +645,13 @@ public:
      */
     void resetSrcFileData(const String& rBaseFileUrl);
 
-    /**
-     * Update a single referencing cell position.
-     *
-     * @param rOldPos old position
-     * @param rNewPos new position
-     */
-    void updateRefCell(const ScAddress& rOldPos, const ScAddress& rNewPos, bool bCopy);
-
-    /**
-     * Update referencing cells affected by sheet movement.
-     *
-     * @param nOldTab old sheet position
-     * @param nNewTab new sheet position
-     * @param bCopy whether this is a sheet move (false) or sheet copy (true)
-     */
-    void updateRefMoveTable(SCTAB nOldTab, SCTAB nNewTab, bool bCopy);
-
-    /**
-     * Update referencing cells affected by sheet insertion.
-     *
-     * @param nPos sheet insertion position.  All sheets to the right
-     *             including the one at the insertion poistion shift to the
-     *             right by one.
+    /** 
+     * Stop tracking a specific formula cell. 
+     *  
+     * @param pCell pointer to cell that formerly contained external 
+     *              reference.
      */
-    void updateRefInsertTable(SCTAB nPos);
-
-    void updateRefDeleteTable(SCTAB nPos);
+    void removeRefCell(ScFormulaCell* pCell);
 
     /**
      * Register a new link listener to a specified external document.  Note
@@ -723,8 +703,6 @@ private:
      */
     void maybeCreateRealFileName(sal_uInt16 nFileId);
 
-    bool compileTokensByCell(const ScAddress& rCell);
-
     /**
      * Purge those source document instances that have not been accessed for
      * the specified duration.
@@ -764,7 +742,13 @@ private:
     ::std::vector<SrcFileData> maSrcFiles;
 
     /** Status whether in reference marking state. See isInReferenceMarking(). */
-    bool bInReferenceMarking;
+    bool mbInReferenceMarking:1;
+
+    /** 
+     * Controls whether or not to allow user interaction.  We don't want any 
+     * user interaction when calling from the API.
+     */
+    bool mbUserInteractionEnabled:1;
 
     AutoTimer maSrcDocTimer;
     DECL_LINK(TimeOutHdl, AutoTimer*);
diff --git a/sc/inc/linkuno.hxx b/sc/inc/linkuno.hxx
index 9b46818d6..e329c3788 100644
--- a/sc/inc/linkuno.hxx
+++ b/sc/inc/linkuno.hxx
@@ -543,7 +543,7 @@ public:
 
                             // XExternalDocLink
     virtual ::com::sun::star::uno::Reference< ::com::sun::star::sheet::XExternalSheetCache >
-        SAL_CALL addSheetCache( const ::rtl::OUString& aSheetName )
+        SAL_CALL addSheetCache( const ::rtl::OUString& aSheetName, sal_Bool bDynamicCache )
             throw (::com::sun::star::uno::RuntimeException);
 
                             // XNameAccess
diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx
index 38039d6e9..d396f73db 100644
--- a/sc/inc/table.hxx
+++ b/sc/inc/table.hxx
@@ -379,8 +379,8 @@ public:
     void		ExtendPrintArea( OutputDevice* pDev,
                         SCCOL nStartCol, SCROW nStartRow, SCCOL& rEndCol, SCROW nEndRow );
 
-    void		GetDataArea( SCCOL& rStartCol, SCROW& rStartRow, SCCOL& rEndCol, SCROW& rEndRow,
-                                BOOL bIncludeOld, bool bOnlyDown ) const;
+    void        GetDataArea( SCCOL& rStartCol, SCROW& rStartRow, SCCOL& rEndCol, SCROW& rEndRow,
+                             BOOL bIncludeOld, bool bOnlyDown ) const;
 
     bool		ShrinkToUsedDataArea( SCCOL& rStartCol, SCROW& rStartRow, SCCOL& rEndCol, SCROW& rEndRow, bool bColumnsOnly ) const;
 
@@ -419,7 +419,6 @@ public:
     void		CalcAfterLoad();
     void		CompileAll();
     void		CompileXML( ScProgress& rProgress );
-    bool        MarkUsedExternalReferences();
 
     void		UpdateReference( UpdateRefMode eUpdateRefMode, SCCOL nCol1, SCROW nRow1, SCTAB nTab1,
                                     SCCOL nCol2, SCROW nRow2, SCTAB nTab2,
diff --git a/sc/source/core/data/cell.cxx b/sc/source/core/data/cell.cxx
index 03531421f..a88dad536 100644
--- a/sc/source/core/data/cell.cxx
+++ b/sc/source/core/data/cell.cxx
@@ -819,6 +819,10 @@ ScFormulaCell::ScFormulaCell( const ScFormulaCell& rCell, ScDocument& rDoc, cons
 ScFormulaCell::~ScFormulaCell()
 {
     pDocument->RemoveFromFormulaTree( this );
+
+    if (pDocument->HasExternalRefManager())
+        pDocument->GetExternalRefManager()->removeRefCell(this);
+
     delete pCode;
 #ifdef DBG_UTIL
     eCellType = CELLTYPE_DESTROYED;
diff --git a/sc/source/core/data/cell2.cxx b/sc/source/core/data/cell2.cxx
index 4ea1ce28d..c056c399e 100644
--- a/sc/source/core/data/cell2.cxx
+++ b/sc/source/core/data/cell2.cxx
@@ -1038,17 +1038,6 @@ void ScFormulaCell::UpdateReference(UpdateRefMode eUpdateRefMode,
 
         delete pOld;
     }
-
-    pCode->Reset();
-    for ( formula::FormulaToken* t = pCode->GetNextReferenceOrName(); t; t = pCode->GetNextReferenceOrName() )
-    {
-        StackVar sv = t->GetType();
-        if (sv == svExternalSingleRef || sv == svExternalDoubleRef || sv == svExternalName)
-        {
-            pDocument->GetExternalRefManager()->updateRefCell(aOldPos, aPos, eUpdateRefMode == URM_COPY);
-            break;
-        }
-    }
 }
 
 void ScFormulaCell::UpdateInsertTab(SCTAB nTable)
diff --git a/sc/source/core/data/column.cxx b/sc/source/core/data/column.cxx
index 5892185b1..23e2e6435 100644
--- a/sc/source/core/data/column.cxx
+++ b/sc/source/core/data/column.cxx
@@ -2100,22 +2100,6 @@ void ScColumn::CalcAfterLoad()
 }
 
 
-bool ScColumn::MarkUsedExternalReferences()
-{
-    bool bAllMarked = false;
-    if (pItems)
-    {
-        for (SCSIZE i = 0; i < nCount && !bAllMarked; ++i)
-        {
-            ScBaseCell* pCell = pItems[i].pCell;
-            if ( pCell->GetCellType() == CELLTYPE_FORMULA )
-                bAllMarked = ((ScFormulaCell*)pCell)->MarkUsedExternalReferences();
-        }
-    }
-    return bAllMarked;
-}
-
-
 void ScColumn::ResetChanged( SCROW nStartRow, SCROW nEndRow )
 {
     if (pItems)
diff --git a/sc/source/core/data/documen2.cxx b/sc/source/core/data/documen2.cxx
index 0f2c7bda9..9ba630ad3 100755
--- a/sc/source/core/data/documen2.cxx
+++ b/sc/source/core/data/documen2.cxx
@@ -804,10 +804,6 @@ BOOL ScDocument::MoveTab( SCTAB nOldPos, SCTAB nNewPos )
                 if (pDrawLayer)
                     DrawMovePage( static_cast<sal_uInt16>(nOldPos), static_cast<sal_uInt16>(nNewPos) );
 
-                // Update cells containing external references.
-                if (pExternalRefMgr.get())
-                    pExternalRefMgr->updateRefMoveTable(nOldPos, nNewPos, false);
-
                 bValid = TRUE;
             }
         }
@@ -925,10 +921,6 @@ BOOL ScDocument::CopyTab( SCTAB nOldPos, SCTAB nNewPos, const ScMarkData* pOnlyM
 
         pTab[nNewPos]->SetPageStyle( pTab[nOldPos]->GetPageStyle() );
         pTab[nNewPos]->SetPendingRowHeights( pTab[nOldPos]->IsPendingRowHeights() );
-
-        // Update cells containing external references.
-        if (pExternalRefMgr.get())
-            pExternalRefMgr->updateRefMoveTable(nOldPos, nNewPos, true);
     }
     else
         SetAutoCalc( bOldAutoCalc );
diff --git a/sc/source/core/data/documen3.cxx b/sc/source/core/data/documen3.cxx
index 13669571d..9b476a3a5 100644
--- a/sc/source/core/data/documen3.cxx
+++ b/sc/source/core/data/documen3.cxx
@@ -465,11 +465,8 @@ void ScDocument::MarkUsedExternalReferences()
     // Charts.
     bool bAllMarked = pExternalRefMgr->markUsedByLinkListeners();
     // Formula cells.
-    for (SCTAB nTab = 0; !bAllMarked && nTab < nMaxTableNumber; ++nTab)
-    {
-        if (pTab[nTab])
-            bAllMarked = pTab[nTab]->MarkUsedExternalReferences();
-    }
+    bAllMarked = pExternalRefMgr->markUsedExternalRefCells();
+
     /* NOTE: Conditional formats and validation objects are marked when
      * collecting them during export. */
 }
diff --git a/sc/source/core/data/documen5.cxx b/sc/source/core/data/documen5.cxx
index 3fd47ccb5..f53d2a14b 100644
--- a/sc/source/core/data/documen5.cxx
+++ b/sc/source/core/data/documen5.cxx
@@ -705,6 +705,9 @@ void ScDocument::UpdateChartListenerCollection()
                 SdrPage* pPage = pDrawLayer->GetPage(static_cast<sal_uInt16>(nTab));
                 DBG_ASSERT(pPage,"Page ?");
 
+                if (!pPage)
+                    continue;
+
                 SdrObjListIter aIter( *pPage, IM_DEEPNOGROUPS );
                 SdrObject* pObject = aIter.Next();
                 while (pObject)
diff --git a/sc/source/core/data/document.cxx b/sc/source/core/data/document.cxx
index eeb1a4eda..0a78dd9ba 100644
--- a/sc/source/core/data/document.cxx
+++ b/sc/source/core/data/document.cxx
@@ -375,10 +375,6 @@ BOOL ScDocument::InsertTab( SCTAB nPos, const String& rName,
                 if ( pChartListenerCollection )
                     pChartListenerCollection->UpdateScheduledSeriesRanges();
 
-                // Update cells containing external references.
-                if (pExternalRefMgr.get())
-                    pExternalRefMgr->updateRefInsertTable(nPos);
-
                 SetDirty();
                 bValid = TRUE;
             }
@@ -467,11 +463,6 @@ BOOL ScDocument::DeleteTab( SCTAB nTab, ScDocument* pRefUndoDoc )
                 // #81844# sheet names of references are not valid until sheet is deleted
                 pChartListenerCollection->UpdateScheduledSeriesRanges();
 
-
-                // Update cells containing external references.
-                if (pExternalRefMgr.get())
-                    pExternalRefMgr->updateRefDeleteTable(nTab);
-
                 SetAutoCalc( bOldAutoCalc );
                 bValid = TRUE;
             }
@@ -694,6 +685,10 @@ bool ScDocument::ShrinkToDataArea(SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow
     if (nRow2 < rEndRow)
         rEndRow = nRow2;
 
+    if (rStartCol > rEndCol || rStartRow > rEndRow)
+        // invalid range.
+        return false;
+
     return true;  // success!
 }
 
@@ -708,11 +703,10 @@ bool ScDocument::ShrinkToUsedDataArea( SCTAB nTab, SCCOL& rStartCol,
 //	zusammenhaengender Bereich
 
 void ScDocument::GetDataArea( SCTAB nTab, SCCOL& rStartCol, SCROW& rStartRow,
-                                SCCOL& rEndCol, SCROW& rEndRow, BOOL bIncludeOld, bool bOnlyDown )
+                              SCCOL& rEndCol, SCROW& rEndRow, BOOL bIncludeOld, bool bOnlyDown ) const
 {
-    if (VALIDTAB(nTab))
-        if (pTab[nTab])
-            pTab[nTab]->GetDataArea( rStartCol, rStartRow, rEndCol, rEndRow, bIncludeOld, bOnlyDown );
+    if (ValidTab(nTab) && pTab[nTab])
+        pTab[nTab]->GetDataArea( rStartCol, rStartRow, rEndCol, rEndRow, bIncludeOld, bOnlyDown );
 }
 
 
diff --git a/sc/source/core/data/table1.cxx b/sc/source/core/data/table1.cxx
index 8faa62b63..2b24df939 100644
--- a/sc/source/core/data/table1.cxx
+++ b/sc/source/core/data/table1.cxx
@@ -678,7 +678,7 @@ BOOL ScTable::GetDataStart( SCCOL& rStartCol, SCROW& rStartRow ) const
 }
 
 void ScTable::GetDataArea( SCCOL& rStartCol, SCROW& rStartRow, SCCOL& rEndCol, SCROW& rEndRow,
-                            BOOL bIncludeOld, bool bOnlyDown ) const
+                           BOOL bIncludeOld, bool bOnlyDown ) const
 {
     BOOL bLeft       = FALSE;
     BOOL bRight  = FALSE;
diff --git a/sc/source/core/data/table2.cxx b/sc/source/core/data/table2.cxx
index 2dbcbba7d..5ba73e007 100644
--- a/sc/source/core/data/table2.cxx
+++ b/sc/source/core/data/table2.cxx
@@ -1119,17 +1119,6 @@ void ScTable::CalcAfterLoad()
 }
 
 
-bool ScTable::MarkUsedExternalReferences()
-{
-    bool bAllMarked = false;
-    for (SCCOL i=0; i <= MAXCOL && !bAllMarked; ++i)
-    {
-        bAllMarked = aCol[i].MarkUsedExternalReferences();
-    }
-    return bAllMarked;
-}
-
-
 void ScTable::ResetChanged( const ScRange& rRange )
 {
     SCCOL nStartCol = rRange.aStart.Col();
diff --git a/sc/source/core/tool/queryparam.cxx b/sc/source/core/tool/queryparam.cxx
index b97558df1..7b129e646 100644
--- a/sc/source/core/tool/queryparam.cxx
+++ b/sc/source/core/tool/queryparam.cxx
@@ -171,7 +171,11 @@ ScQueryParamTable::~ScQueryParamTable()
 
 ScQueryParam::ScQueryParam() : 
     ScQueryParamBase(),
-    ScQueryParamTable()
+    ScQueryParamTable(),
+    bDestPers(true),
+    nDestTab(0),
+    nDestCol(0),
+    nDestRow(0)
 {
     Clear();
 }
diff --git a/sc/source/filter/excel/read.cxx b/sc/source/filter/excel/read.cxx
index af7e86614..5b6c5f0c1 100644
--- a/sc/source/filter/excel/read.cxx
+++ b/sc/source/filter/excel/read.cxx
@@ -1211,7 +1211,9 @@ FltError ImportExcel8::Read( void )
 
         pProgress.reset();
 
-        AdjustRowHeight();
+        if (pD->IsAdjustHeightEnabled())
+            AdjustRowHeight();
+
         PostDocLoad();
 
         pD->CalcAfterLoad();
diff --git a/sc/source/filter/excel/xilink.cxx b/sc/source/filter/excel/xilink.cxx
index 1eec535d0..df7b2d241 100644
--- a/sc/source/filter/excel/xilink.cxx
+++ b/sc/source/filter/excel/xilink.cxx
@@ -575,6 +575,7 @@ void XclImpSupbook::LoadCachedValues()
         const String& rTabName = pTab->GetTabName();
         ScExternalRefCache::TableTypeRef pCacheTable = pRefMgr->getCacheTable(nFileId, rTabName, true);
         pTab->LoadCachedValues(pCacheTable);
+        pCacheTable->setWholeTableCached();
     }
 }
 
diff --git a/sc/source/filter/xml/xmlexternaltabi.cxx b/sc/source/filter/xml/xmlexternaltabi.cxx
index 66cef54ec..e91f02185 100644
--- a/sc/source/filter/xml/xmlexternaltabi.cxx
+++ b/sc/source/filter/xml/xmlexternaltabi.cxx
@@ -370,7 +370,7 @@ SvXMLImportContext* ScXMLExternalRefCellContext::CreateChildContext(
     const SvXMLTokenMap& rTokenMap = mrScImport.GetTableRowCellElemTokenMap();
     sal_uInt16 nToken = rTokenMap.Get(nPrefix, rLocalName);
     if (nToken == XML_TOK_TABLE_ROW_CELL_P)
-        return new ScXMLExternalRefCellTextContext(mrScImport, nPrefix, rLocalName, xAttrList, maCellString);
+        return new ScXMLExternalRefCellTextContext(mrScImport, nPrefix, rLocalName, xAttrList, *this);
 
     return new SvXMLImportContext(GetImport(), nPrefix, rLocalName);
 }
@@ -399,14 +399,20 @@ void ScXMLExternalRefCellContext::EndElement()
     }
 }
 
+void ScXMLExternalRefCellContext::SetCellString(const OUString& rStr)
+{
+    maCellString = rStr;
+}
+
 // ============================================================================
 
 ScXMLExternalRefCellTextContext::ScXMLExternalRefCellTextContext(
     ScXMLImport& rImport, USHORT nPrefix, const OUString& rLName,
-    const Reference<XAttributeList>& /*xAttrList*/, OUString& rCellString ) :
+    const Reference<XAttributeList>& /*xAttrList*/,
+    ScXMLExternalRefCellContext& rParent ) :
     SvXMLImportContext( rImport, nPrefix, rLName ),
     mrScImport(rImport),
-    mrCellString(rCellString)
+    mrParent(rParent)
 {
 }
 
@@ -422,9 +428,10 @@ SvXMLImportContext* ScXMLExternalRefCellTextContext::CreateChildContext(
 
 void ScXMLExternalRefCellTextContext::Characters(const OUString& rChar)
 {
-    mrCellString = rChar;
+    maCellStrBuf.append(rChar);
 }
 
 void ScXMLExternalRefCellTextContext::EndElement()
 {
+    mrParent.SetCellString(maCellStrBuf.makeStringAndClear());
 }
diff --git a/sc/source/filter/xml/xmlexternaltabi.hxx b/sc/source/filter/xml/xmlexternaltabi.hxx
index 803305e1a..6e17c1d92 100644
--- a/sc/source/filter/xml/xmlexternaltabi.hxx
+++ b/sc/source/filter/xml/xmlexternaltabi.hxx
@@ -29,6 +29,7 @@
 #define SC_XMLEXTERNALTABI_HXX
 
 #include <xmloff/xmlictxt.hxx>
+#include "rtl/ustrbuf.hxx"
 
 class ScXMLImport;
 struct ScXMLExternalTabData;
@@ -129,6 +130,8 @@ public:
 
     virtual void EndElement();
 
+    void SetCellString(const ::rtl::OUString& rStr);
+
 private:
     ScXMLImport&            mrScImport;
     ScXMLExternalTabData&   mrExternalRefInfo;
@@ -150,7 +153,7 @@ public:
                         const ::rtl::OUString& rLName,
                         const ::com::sun::star::uno::Reference<
                                         ::com::sun::star::xml::sax::XAttributeList>& xAttrList,
-                        ::rtl::OUString& rCellString );
+                        ScXMLExternalRefCellContext& rParent );
 
     virtual ~ScXMLExternalRefCellTextContext();
 
@@ -165,7 +168,9 @@ public:
 
 private:
     ScXMLImport&            mrScImport;
-    ::rtl::OUString&        mrCellString;
+    ScXMLExternalRefCellContext& mrParent;
+
+    ::rtl::OUStringBuffer   maCellStrBuf;
 };
 
 #endif
diff --git a/sc/source/filter/xml/xmltabi.cxx b/sc/source/filter/xml/xmltabi.cxx
index 1e8322af1..f9123a3f6 100644
--- a/sc/source/filter/xml/xmltabi.cxx
+++ b/sc/source/filter/xml/xmltabi.cxx
@@ -98,21 +98,27 @@ static bool lcl_isExternalRefCache(const rtl::OUString& rName, rtl::OUString& rU
         const sal_Unicode c = p[i];
         if (i <= 7)
         {
+            // Checking the prefix 'file://'.
             if (c != aPrefix[i])
                 return false;
         }
-        else if (c == '#')
+        else if (bInUrl)
         {
-            if (cPrev != '\'')
-                return false;
+            // parsing file URL
+            if (c == '#')
+            {
+                if (cPrev != '\'')
+                    return false;
 
-            rUrl = aUrlBuf.makeStringAndClear();
-            rUrl = rUrl.copy(0, rUrl.getLength()-1); // remove the trailing single-quote.
-            bInUrl = false;
+                rUrl = aUrlBuf.makeStringAndClear();
+                rUrl = rUrl.copy(0, rUrl.getLength()-1); // remove the trailing single-quote.
+                bInUrl = false;
+            }
+            else
+                aUrlBuf.append(c);
         }
-        else if (bInUrl)
-            aUrlBuf.append(c);
         else
+            // parsing sheet name.
             aTabNameBuf.append(c);
 
         cPrev = c;
@@ -206,6 +212,7 @@ ScXMLTableContext::ScXMLTableContext( ScXMLImport& rImport,
                 ScExternalRefManager* pRefMgr = pDoc->GetExternalRefManager();
                 pExternalRefInfo->mnFileId = pRefMgr->getExternalFileId(aExtUrl);
                 pExternalRefInfo->mpCacheTable = pRefMgr->getCacheTable(pExternalRefInfo->mnFileId, aExtTabName, true);
+                pExternalRefInfo->mpCacheTable->setWholeTableCached();
             }
         }
         else
diff --git a/sc/source/ui/docshell/docfunc.cxx b/sc/source/ui/docshell/docfunc.cxx
index 1db212026..0db781181 100644
--- a/sc/source/ui/docshell/docfunc.cxx
+++ b/sc/source/ui/docshell/docfunc.cxx
@@ -98,9 +98,11 @@
 #include "scui_def.hxx" //CHINA001
 #include "tabprotection.hxx"
 #include "clipparam.hxx"
+#include "externalrefmgr.hxx"
 
 #include <memory>
 #include <basic/basmgr.hxx>
+#include <boost/scoped_ptr.hpp>
 
 using namespace com::sun::star;
 using ::com::sun::star::uno::Sequence;
@@ -1042,6 +1044,10 @@ BOOL ScDocFunc::SetCellText( const ScAddress& rPos, const String& rText,
     {
         if ( bEnglish )
         {
+            ::boost::scoped_ptr<ScExternalRefManager::ApiGuard> pExtRefGuard;
+            if (bApi)
+                pExtRefGuard.reset(new ScExternalRefManager::ApiGuard(pDoc));
+
             //	code moved to own method InterpretEnglishString because it is also used in
             //	ScCellRangeObj::setFormulaArray
 
diff --git a/sc/source/ui/docshell/externalrefmgr.cxx b/sc/source/ui/docshell/externalrefmgr.cxx
index 7157f3a74..8291375df 100644
--- a/sc/source/ui/docshell/externalrefmgr.cxx
+++ b/sc/source/ui/docshell/externalrefmgr.cxx
@@ -65,6 +65,8 @@
 #include <memory>
 #include <algorithm>
 
+#include <boost/scoped_ptr.hpp>
+
 using ::std::auto_ptr;
 using ::com::sun::star::uno::Any;
 using ::rtl::OUString;
@@ -135,6 +137,69 @@ private:
     ScExternalRefManager::LinkUpdateType meType;
 };
 
+struct UpdateFormulaCell : public unary_function<ScFormulaCell*, void>
+{
+    void operator() (ScFormulaCell* pCell) const
+    {
+        // Check to make sure the cell really contains ocExternalRef.
+        // External names, external cell and range references all have a
+        // ocExternalRef token.
+        const ScTokenArray* pCode = pCell->GetCode();
+        if (!pCode->HasOpCode( ocExternalRef))
+            return;
+
+        ScTokenArray* pArray = pCell->GetCode();
+        if (pArray)
+            // Clear the error code, or a cell with error won't get re-compiled.
+            pArray->SetCodeError(0);
+
+        pCell->SetCompile(true);
+        pCell->CompileTokenArray();
+        pCell->SetDirty();
+    }
+};
+
+class RemoveFormulaCell : public unary_function<pair<const sal_uInt16, ScExternalRefManager::RefCellSet>, void>
+{
+public:
+    explicit RemoveFormulaCell(ScFormulaCell* p) : mpCell(p) {}
+    void operator() (pair<const sal_uInt16, ScExternalRefManager::RefCellSet>& r) const
+    {
+        r.second.erase(mpCell);
+    }
+private:
+    ScFormulaCell* mpCell;
+};
+
+class ConvertFormulaToStatic : public unary_function<ScFormulaCell*, void>
+{
+public:
+    explicit ConvertFormulaToStatic(ScDocument* pDoc) : mpDoc(pDoc) {}
+    void operator() (ScFormulaCell* pCell) const
+    {
+        ScAddress aPos = pCell->aPos;
+
+        // We don't check for empty cells because empty external cells are 
+        // treated as having a value of 0.
+
+        if (pCell->IsValue())
+        {
+            // Turn this into value cell.
+            double fVal = pCell->GetValue();
+            mpDoc->PutCell(aPos, new ScValueCell(fVal));
+        }
+        else
+        {
+            // string cell otherwise.
+            String aVal;
+            pCell->GetString(aVal);
+            mpDoc->PutCell(aPos, new ScStringCell(aVal));
+        }
+    }
+private:
+    ScDocument* mpDoc;
+};
+
 }
 
 // ============================================================================
@@ -170,7 +235,7 @@ bool ScExternalRefCache::Table::isReferenced() const
     return meReferenced != UNREFERENCED;
 }
 
-void ScExternalRefCache::Table::setCell(SCCOL nCol, SCROW nRow, TokenRef pToken, sal_uInt32 nFmtIndex)
+void ScExternalRefCache::Table::setCell(SCCOL nCol, SCROW nRow, TokenRef pToken, sal_uInt32 nFmtIndex, bool bSetCacheRange)
 {
     using ::std::pair;
     RowsDataType::iterator itrRow = maRows.find(nRow);
@@ -193,6 +258,8 @@ void ScExternalRefCache::Table::setCell(SCCOL nCol, SCROW nRow, TokenRef pToken,
     aCell.mxToken = pToken;
     aCell.mnFmtIndex = nFmtIndex;
     rRow.insert(RowDataType::value_type(nCol, aCell));
+    if (bSetCacheRange)
+        setCachedCell(nCol, nRow);
 }
 
 ScExternalRefCache::TokenRef ScExternalRefCache::Table::getCell(SCCOL nCol, SCROW nRow, sal_uInt32* pnFmtIndex) const
@@ -201,7 +268,7 @@ ScExternalRefCache::TokenRef ScExternalRefCache::Table::getCell(SCCOL nCol, SCRO
     if (itrTable == maRows.end())
     {
         // this table doesn't have the specified row.
-        return TokenRef();
+        return getEmptyOrNullToken(nCol, nRow);
     }
 
     const RowDataType& rRowData = itrTable->second;
@@ -209,7 +276,7 @@ ScExternalRefCache::TokenRef ScExternalRefCache::Table::getCell(SCCOL nCol, SCRO
     if (itrRow == rRowData.end())
     {
         // this row doesn't have the specified column.
-        return TokenRef();
+        return getEmptyOrNullToken(nCol, nRow);
     }
 
     const Cell& rCell = itrRow->second;
@@ -225,13 +292,14 @@ bool ScExternalRefCache::Table::hasRow( SCROW nRow ) const
     return itrRow != maRows.end();
 }
 
-void ScExternalRefCache::Table::getAllRows(vector<SCROW>& rRows) const
+void ScExternalRefCache::Table::getAllRows(vector<SCROW>& rRows, SCROW nLow, SCROW nHigh) const
 {
     vector<SCROW> aRows;
     aRows.reserve(maRows.size());
     RowsDataType::const_iterator itr = maRows.begin(), itrEnd = maRows.end();
     for (; itr != itrEnd; ++itr)
-        aRows.push_back(itr->first);
+        if (nLow <= itr->first && itr->first <= nHigh)
+            aRows.push_back(itr->first);
 
     // hash map is not ordered, so we need to explicitly sort it.
     ::std::sort(aRows.begin(), aRows.end());
@@ -258,7 +326,7 @@ void ScExternalRefCache::Table::getAllRows(vector<SCROW>& rRows) const
     return aRange;
 }
 
-void ScExternalRefCache::Table::getAllCols(SCROW nRow, vector<SCCOL>& rCols) const
+void ScExternalRefCache::Table::getAllCols(SCROW nRow, vector<SCCOL>& rCols, SCCOL nLow, SCCOL nHigh) const
 {
     RowsDataType::const_iterator itrRow = maRows.find(nRow);
     if (itrRow == maRows.end())
@@ -270,7 +338,8 @@ void ScExternalRefCache::Table::getAllCols(SCROW nRow, vector<SCCOL>& rCols) con
     aCols.reserve(rRowData.size());
     RowDataType::const_iterator itrCol = rRowData.begin(), itrColEnd = rRowData.end();
     for (; itrCol != itrColEnd; ++itrCol)
-        aCols.push_back(itrCol->first);
+        if (nLow <= itrCol->first && itrCol->first <= nHigh)
+            aCols.push_back(itrCol->first);
 
     // hash map is not ordered, so we need to explicitly sort it.
     ::std::sort(aCols.begin(), aCols.end());
@@ -319,6 +388,54 @@ void ScExternalRefCache::Table::getAllNumberFormats(vector<sal_uInt32>& rNumFmts
     }
 }
 
+const ScRangeList& ScExternalRefCache::Table::getCachedRanges() const
+{
+    return maCachedRanges;
+}
+
+bool ScExternalRefCache::Table::isRangeCached(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2) const
+{
+    return maCachedRanges.In(ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0));
+}
+
+void ScExternalRefCache::Table::setCachedCell(SCCOL nCol, SCROW nRow)
+{
+    setCachedCellRange(nCol, nRow, nCol, nRow);
+}
+
+void ScExternalRefCache::Table::setCachedCellRange(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2)
+{
+    ScRange aRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
+    if (!maCachedRanges.Count())
+        maCachedRanges.Append(aRange);
+    else
+        maCachedRanges.Join(aRange);
+
+    String aStr;
+    maCachedRanges.Format(aStr, SCA_VALID);
+}
+
+void ScExternalRefCache::Table::setWholeTableCached()
+{
+    setCachedCellRange(0, 0, MAXCOL, MAXROW);
+}
+
+bool ScExternalRefCache::Table::isInCachedRanges(SCCOL nCol, SCROW nRow) const
+{
+    return maCachedRanges.In(ScRange(nCol, nRow, 0, nCol, nRow, 0));
+}
+
+ScExternalRefCache::TokenRef ScExternalRefCache::Table::getEmptyOrNullToken(
+    SCCOL nCol, SCROW nRow) const
+{
+    if (isInCachedRanges(nCol, nRow))
+    {
+        TokenRef p(new ScEmptyCellToken(false, false));
+        return p;
+    }
+    return TokenRef();
+}
+
 // ----------------------------------------------------------------------------
 
 ScExternalRefCache::TableName::TableName(const String& rUpper, const String& rReal) :
@@ -383,8 +500,7 @@ const String* ScExternalRefCache::getRealRangeName(sal_uInt16 nFileId, const Str
 }
 
 ScExternalRefCache::TokenRef ScExternalRefCache::getCellData(
-    sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow,
-    bool bEmptyCellOnNull, bool bWriteEmpty, sal_uInt32* pnFmtIndex)
+    sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow, sal_uInt32* pnFmtIndex)
 {
     DocDataType::const_iterator itrDoc = maDocs.find(nFileId);
     if (itrDoc == maDocs.end())
@@ -409,18 +525,11 @@ ScExternalRefCache::TokenRef ScExternalRefCache::getCellData(
         return TokenRef();
     }
 
-    TokenRef pToken = pTableData->getCell(nCol, nRow, pnFmtIndex);
-    if (!pToken && bEmptyCellOnNull)
-    {
-        pToken.reset(new ScEmptyCellToken(false, false));
-        if (bWriteEmpty)
-            pTableData->setCell(nCol, nRow, pToken);
-    }
-    return pToken;
+    return pTableData->getCell(nCol, nRow, pnFmtIndex);
 }
 
 ScExternalRefCache::TokenArrayRef ScExternalRefCache::getCellRangeData(
-    sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, bool bEmptyCellOnNull, bool bWriteEmpty)
+    sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange)
 {
     DocDataType::iterator itrDoc = maDocs.find(nFileId);
     if (itrDoc == maDocs.end())
@@ -450,13 +559,14 @@ ScExternalRefCache::TokenArrayRef ScExternalRefCache::getCellRangeData(
         return TokenArrayRef();
 
     ScRange aCacheRange( nCol1, nRow1, static_cast<SCTAB>(nTabFirstId), nCol2, nRow2, static_cast<SCTAB>(nTabLastId));
+
     RangeArrayMap::const_iterator itrRange = rDoc.maRangeArrays.find( aCacheRange);
     if (itrRange != rDoc.maRangeArrays.end())
-    {
+        // Cache hit!
         return itrRange->second;
-    }
 
-    TokenArrayRef pArray(new ScTokenArray);
+    ::boost::scoped_ptr<ScRange> pNewRange;
+    TokenArrayRef pArray;
     bool bFirstTab = true;
     for (size_t nTab = nTabFirstId; nTab <= nTabLastId; ++nTab)
     {
@@ -464,27 +574,72 @@ ScExternalRefCache::TokenArrayRef ScExternalRefCache::getCellRangeData(
         if (!pTab.get())
             return TokenArrayRef();
 
+        SCCOL nDataCol1 = nCol1, nDataCol2 = nCol2;
+        SCROW nDataRow1 = nRow1, nDataRow2 = nRow2;
+
+        if (!pTab->isRangeCached(nDataCol1, nDataRow1, nDataCol2, nDataRow2))
+        {
+            // specified range is not entirely within cached ranges.
+            return TokenArrayRef();
+        }
+
         ScMatrixRef xMat = new ScMatrix(
-            static_cast<SCSIZE>(nCol2-nCol1+1), static_cast<SCSIZE>(nRow2-nRow1+1));
+            static_cast<SCSIZE>(nDataCol2-nDataCol1+1), static_cast<SCSIZE>(nDataRow2-nDataRow1+1));
 
-        for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+#if 0
+        // TODO: Switch to this code block once we have support for sparsely-filled 
+        // matrices in ScMatrix.
+
+        // Only fill non-empty cells, for better performance.
+        vector<SCROW> aRows;
+        pTab->getAllRows(aRows, nDataRow1, nDataRow2);
+        for (vector<SCROW>::const_iterator itr = aRows.begin(), itrEnd = aRows.end(); itr != itrEnd; ++itr)
         {
-            for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+            SCROW nRow = *itr;
+            vector<SCCOL> aCols;
+            pTab->getAllCols(nRow, aCols, nDataCol1, nDataCol2);
+            for (vector<SCCOL>::const_iterator itrCol = aCols.begin(), itrColEnd = aCols.end(); itrCol != itrColEnd; ++itrCol)
             {
+                SCCOL nCol = *itrCol;
                 TokenRef pToken = pTab->getCell(nCol, nRow);
                 if (!pToken)
+                    // This should never happen!
+                    return TokenArrayRef();
+
+                SCSIZE nC = nCol - nDataCol1, nR = nRow - nDataRow1;
+                switch (pToken->GetType())
                 {
-                    if (bEmptyCellOnNull)
-                    {
-                        pToken.reset(new ScEmptyCellToken(false, false));
-                        if (bWriteEmpty)
-                            pTab->setCell(nCol, nRow, pToken);
-                    }
-                    else
-                        return TokenArrayRef();
+                    case svDouble:
+                        xMat->PutDouble(pToken->GetDouble(), nC, nR);
+                    break;
+                    case svString:
+                        xMat->PutString(pToken->GetString(), nC, nR);
+                    break;
+                    default:
+                        ;
                 }
+            }
+        }
+#else
+        vector<SCROW> aRows;
+        pTab->getAllRows(aRows, nDataRow1, nDataRow2);
+        if (aRows.empty())
+            // Cache is empty.
+            return TokenArrayRef();
+        else
+            // Trim the column below the last non-empty row.
+            nDataRow2 = aRows.back();
 
+        // Empty all matrix elements first, and fill only non-empty elements.
+        for (SCROW nRow = nDataRow1; nRow <= nDataRow2; ++nRow)
+        {
+            for (SCCOL nCol = nDataCol1; nCol <= nDataCol2; ++nCol)
+            {
+                TokenRef pToken = pTab->getCell(nCol, nRow);
                 SCSIZE nC = nCol - nCol1, nR = nRow - nRow1;
+                if (!pToken)
+                    return TokenArrayRef();
+
                 switch (pToken->GetType())
                 {
                     case svDouble:
@@ -498,17 +653,27 @@ ScExternalRefCache::TokenArrayRef ScExternalRefCache::getCellRangeData(
                 }
             }
         }
+#endif
 
         if (!bFirstTab)
             pArray->AddOpCode(ocSep);
 
         ScMatrix* pMat2 = xMat;
         ScMatrixToken aToken(pMat2);
+        if (!pArray)
+            pArray.reset(new ScTokenArray);
         pArray->AddToken(aToken);
 
         bFirstTab = false;
+
+        if (!pNewRange)
+            pNewRange.reset(new ScRange(nDataCol1, nDataRow1, 0, nDataCol2, nDataRow2, 0));
+        else
+            pNewRange->ExtendTo(ScRange(nDataCol1, nDataRow1, 0, nDataCol2, nDataRow2, 0));
     }
-    rDoc.maRangeArrays.insert( RangeArrayMap::value_type( aCacheRange, pArray));
+
+    if (pNewRange)
+        rDoc.maRangeArrays.insert( RangeArrayMap::value_type(*pNewRange, pArray));
     return pArray;
 }
 
@@ -539,7 +704,7 @@ void ScExternalRefCache::setRangeNameTokens(sal_uInt16 nFileId, const String& rN
     pDoc->maRealRangeNameMap.insert(NamePairMap::value_type(aUpperName, rName));
 }
 
-void ScExternalRefCache::setCellData(sal_uInt16 nFileId, const String& rTabName, SCROW nRow, SCCOL nCol,
+void ScExternalRefCache::setCellData(sal_uInt16 nFileId, const String& rTabName, SCCOL nCol, SCROW nRow,
                                      TokenRef pToken, sal_uInt32 nFmtIndex)
 {
     if (!isDocInitialized(nFileId))
@@ -564,6 +729,7 @@ void ScExternalRefCache::setCellData(sal_uInt16 nFileId, const String& rTabName,
         pTableData.reset(new Table);
 
     pTableData->setCell(nCol, nRow, pToken, nFmtIndex);
+    pTableData->setCachedCell(nCol, nRow);
 }
 
 void ScExternalRefCache::setCellRangeData(sal_uInt16 nFileId, const ScRange& rRange, const vector<SingleRangeData>& rData,
@@ -609,20 +775,27 @@ void ScExternalRefCache::setCellRangeData(sal_uInt16 nFileId, const ScRange& rRa
                 SCSIZE nC = nCol - nCol1, nR = nRow - nRow1;
                 TokenRef pToken;
                 const ScMatrixRef& pMat = itrData->mpRangeData;
+                if (pMat->IsEmpty(nC, nR))
+                    // Don't cache empty cells.
+                    continue;
+
                 if (pMat->IsValue(nC, nR))
                     pToken.reset(new formula::FormulaDoubleToken(pMat->GetDouble(nC, nR)));
                 else if (pMat->IsString(nC, nR))
                     pToken.reset(new formula::FormulaStringToken(pMat->GetString(nC, nR)));
-                else
-                    pToken.reset(new ScEmptyCellToken(false, false));
 
-                pTabData->setCell(nCol, nRow, pToken);
+                if (pToken)
+                    // Don't mark this cell 'cached' here, for better performance.
+                    pTabData->setCell(nCol, nRow, pToken, 0, false);
             }
         }
+        // Mark the whole range 'cached'.
+        pTabData->setCachedCellRange(nCol1, nRow1, nCol2, nRow2);
     }
 
     size_t nTabLastId = nTabFirstId + rRange.aEnd.Tab() - rRange.aStart.Tab();
     ScRange aCacheRange( nCol1, nRow1, static_cast<SCTAB>(nTabFirstId), nCol2, nRow2, static_cast<SCTAB>(nTabLastId));
+
     rDoc.maRangeArrays.insert( RangeArrayMap::value_type( aCacheRange, pArray));
 }
 
@@ -1019,6 +1192,9 @@ ScExternalRefCache::TableTypeRef ScExternalRefCache::getCacheTable(sal_uInt16 nF
     {
         // specified table found.
         if( pnIndex ) *pnIndex = nIndex;
+        if (bCreateNew && !rDoc.maTables[nIndex])
+            rDoc.maTables[nIndex].reset(new Table);
+
         return rDoc.maTables[nIndex];
     }
 
@@ -1186,11 +1362,11 @@ static FormulaToken* lcl_convertToToken(ScBaseCell* pCell)
     return NULL;
 }
 
-static ScTokenArray* lcl_convertToTokenArray(ScDocument* pSrcDoc, const ScRange& rRange,
+static ScTokenArray* lcl_convertToTokenArray(ScDocument* pSrcDoc, ScRange& rRange,
                                              vector<ScExternalRefCache::SingleRangeData>& rCacheData)
 {
-    const ScAddress& s = rRange.aStart;
-    const ScAddress& e = rRange.aEnd;
+    ScAddress& s = rRange.aStart;
+    ScAddress& e = rRange.aEnd;
 
     SCTAB nTab1 = s.Tab(), nTab2 = e.Tab();
     SCCOL nCol1 = s.Col(), nCol2 = e.Col();
@@ -1204,19 +1380,35 @@ static ScTokenArray* lcl_convertToTokenArray(ScDocument* pSrcDoc, const ScRange&
         // range to it.
         return NULL;
 
+    ::boost::scoped_ptr<ScRange> pUsedRange;
+
     auto_ptr<ScTokenArray> pArray(new ScTokenArray);
     bool bFirstTab = true;
     vector<ScExternalRefCache::SingleRangeData>::iterator
         itrCache = rCacheData.begin(), itrCacheEnd = rCacheData.end();
+
     for (SCTAB nTab = nTab1; nTab <= nTab2 && itrCache != itrCacheEnd; ++nTab, ++itrCache)
     {
+        // Only loop within the data area.
+        SCCOL nDataCol1 = nCol1, nDataCol2 = nCol2;
+        SCROW nDataRow1 = nRow1, nDataRow2 = nRow2;
+        if (!pSrcDoc->ShrinkToDataArea(nTab, nDataCol1, nDataRow1, nDataCol2, nDataRow2))
+            // no data within specified range.
+            continue;
+
+        if (pUsedRange.get())
+            // Make sure the used area only grows, not shrinks.
+            pUsedRange->ExtendTo(ScRange(nDataCol1, nDataRow1, 0, nDataCol2, nDataRow2, 0));
+        else
+            pUsedRange.reset(new ScRange(nDataCol1, nDataRow1, 0, nDataCol2, nDataRow2, 0));
+
         ScMatrixRef xMat = new ScMatrix(
-            static_cast<SCSIZE>(nCol2-nCol1+1),
-            static_cast<SCSIZE>(nRow2-nRow1+1));
+            static_cast<SCSIZE>(nDataCol2-nDataCol1+1),
+            static_cast<SCSIZE>(nDataRow2-nDataRow1+1));
 
-        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+        for (SCCOL nCol = nDataCol1; nCol <= nDataCol2; ++nCol)
         {
-            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+            for (SCROW nRow = nDataRow1; nRow <= nDataRow2; ++nRow)
             {
                 SCSIZE nC = nCol - nCol1, nR = nRow - nRow1;
                 ScBaseCell* pCell;
@@ -1283,12 +1475,38 @@ static ScTokenArray* lcl_convertToTokenArray(ScDocument* pSrcDoc, const ScRange&
 
         bFirstTab = false;
     }
+
+    if (!pUsedRange.get())
+        return NULL;
+
+    s.SetCol(pUsedRange->aStart.Col());
+    s.SetRow(pUsedRange->aStart.Row());
+    e.SetCol(pUsedRange->aEnd.Col());
+    e.SetRow(pUsedRange->aEnd.Row());
+
+    return pArray.release();
+}
+
+static ScTokenArray* lcl_fillEmptyMatrix(const ScRange& rRange)
+{
+    SCSIZE nC = static_cast<SCSIZE>(rRange.aEnd.Col()-rRange.aStart.Col()+1);
+    SCSIZE nR = static_cast<SCSIZE>(rRange.aEnd.Row()-rRange.aStart.Row()+1);
+    ScMatrixRef xMat = new ScMatrix(nC, nR);
+    for (SCSIZE i = 0; i < nC; ++i)
+        for (SCSIZE j = 0; j < nR; ++j)
+            xMat->PutEmpty(i, j);
+
+    ScMatrix* pMat2 = xMat;
+    ScMatrixToken aToken(pMat2);
+    auto_ptr<ScTokenArray> pArray(new ScTokenArray);
+    pArray->AddToken(aToken);
     return pArray.release();
 }
 
 ScExternalRefManager::ScExternalRefManager(ScDocument* pDoc) :
     mpDoc(pDoc),
-    bInReferenceMarking(false)
+    mbInReferenceMarking(false),
+    mbUserInteractionEnabled(true)
 {
     maSrcDocTimer.SetTimeoutHdl( LINK(this, ScExternalRefManager, TimeOutHdl) );
     maSrcDocTimer.SetTimeout(SRCDOC_SCAN_INTERVAL);
@@ -1316,236 +1534,28 @@ ScExternalRefCache::TableTypeRef ScExternalRefManager::getCacheTable(sal_uInt16
 
 // ============================================================================
 
-ScExternalRefManager::RefCells::TabItem::TabItem(SCTAB nIndex) :
-    mnIndex(nIndex)
-{
-}
-
-ScExternalRefManager::RefCells::TabItem::TabItem(const TabItem& r) :
-    mnIndex(r.mnIndex),
-    maCols(r.maCols)
-{
-}
-
-ScExternalRefManager::RefCells::RefCells()
-{
-}
-
-ScExternalRefManager::RefCells::~RefCells()
-{
-}
-
-list<ScExternalRefManager::RefCells::TabItemRef>::iterator ScExternalRefManager::RefCells::getTabPos(SCTAB nTab)
-{
-    list<TabItemRef>::iterator itr = maTables.begin(), itrEnd = maTables.end();
-    for (; itr != itrEnd; ++itr)
-        if ((*itr)->mnIndex >= nTab)
-            return itr;
-    // Not found.  return the end position.
-    return itrEnd;
-}
-
-void ScExternalRefManager::RefCells::insertCell(const ScAddress& rAddr)
-{
-    SCTAB nTab = rAddr.Tab();
-    SCCOL nCol = rAddr.Col();
-    SCROW nRow = rAddr.Row();
-
-    // Search by table index.
-    list<TabItemRef>::iterator itrTab = getTabPos(nTab);
-    TabItemRef xTabRef;
-    if (itrTab == maTables.end())
-    {
-        // All previous tables come before the specificed table.
-        xTabRef.reset(new TabItem(nTab));
-        maTables.push_back(xTabRef);
-    }
-    else if ((*itrTab)->mnIndex > nTab)
-    {
-        // Insert at the current iterator position.
-        xTabRef.reset(new TabItem(nTab));
-        maTables.insert(itrTab, xTabRef);
-    }
-    else if ((*itrTab)->mnIndex == nTab)
-    {
-        // The table found.
-        xTabRef = *itrTab;
-    }
-    ColSet& rCols = xTabRef->maCols;
-
-    // Then by column index.
-    ColSet::iterator itrCol = rCols.find(nCol);
-    if (itrCol == rCols.end())
-    {
-        RowSet aRows;
-        pair<ColSet::iterator, bool> r = rCols.insert(ColSet::value_type(nCol, aRows));
-        if (!r.second)
-            // column insertion failed.
-            return;
-        itrCol = r.first;
-    }
-    RowSet& rRows = itrCol->second;
-
-    // Finally, insert the row index.
-    rRows.insert(nRow);
-}
-
-void ScExternalRefManager::RefCells::removeCell(const ScAddress& rAddr)
-{
-    SCTAB nTab = rAddr.Tab();
-    SCCOL nCol = rAddr.Col();
-    SCROW nRow = rAddr.Row();
-
-    // Search by table index.
-    list<TabItemRef>::iterator itrTab = getTabPos(nTab);
-    if (itrTab == maTables.end() || (*itrTab)->mnIndex != nTab)
-        // No such table.
-        return;
-
-    ColSet& rCols = (*itrTab)->maCols;
-
-    // Then by column index.
-    ColSet::iterator itrCol = rCols.find(nCol);
-    if (itrCol == rCols.end())
-        // No such column
-        return;
-
-    RowSet& rRows = itrCol->second;
-    rRows.erase(nRow);
-}
-
-void ScExternalRefManager::RefCells::moveTable(SCTAB nOldTab, SCTAB nNewTab, bool bCopy)
-{
-    if (nOldTab == nNewTab)
-        // Nothing to do here.
-        return;
-
-    list<TabItemRef>::iterator itrOld = getTabPos(nOldTab);
-    if (itrOld == maTables.end() || (*itrOld)->mnIndex != nOldTab)
-        // No table to move or copy.
-        return;
-
-    list<TabItemRef>::iterator itrNew = getTabPos(nNewTab);
-    if (bCopy)
-    {
-        // Simply make a duplicate of the original table, insert it at the
-        // new tab position, and increment the table index for all tables
-        // that come after that inserted table.
-
-        TabItemRef xNewTab(new TabItem(*(*itrOld)));
-        xNewTab->mnIndex = nNewTab;
-        maTables.insert(itrNew, xNewTab);
-        list<TabItemRef>::iterator itr = itrNew, itrEnd = maTables.end();
-        if (itr != itrEnd)  // #i99807# check that itr is not at end already
-            for (++itr; itr != itrEnd; ++itr)
-                (*itr)->mnIndex += 1;
-    }
-    else
-    {
-        if (itrOld == itrNew)
-        {
-            // No need to move the table.  Just update the table index.
-            (*itrOld)->mnIndex = nNewTab;
-            return;
-        }
-
-        if (nOldTab < nNewTab)
-        {
-            // Iterate from the old tab position to the new tab position (not
-            // inclusive of the old tab itself), and decrement their tab
-            // index by one.
-            list<TabItemRef>::iterator itr = itrOld;
-            for (++itr; itr != itrNew; ++itr)
-                (*itr)->mnIndex -= 1;
-
-            // Insert a duplicate of the original table.  This does not
-            // invalidate the iterators.
-            (*itrOld)->mnIndex = nNewTab - 1;
-            if (itrNew == maTables.end())
-                maTables.push_back(*itrOld);
-            else
-                maTables.insert(itrNew, *itrOld);
-
-            // Remove the original table.
-            maTables.erase(itrOld);
-        }
-        else
-        {
-            // nNewTab < nOldTab
-
-            // Iterate from the new tab position to the one before the old tab
-            // position, and increment their tab index by one.
-            list<TabItemRef>::iterator itr = itrNew;
-            for (++itr; itr != itrOld; ++itr)
-                (*itr)->mnIndex += 1;
-
-            (*itrOld)->mnIndex = nNewTab;
-            maTables.insert(itrNew, *itrOld);
-
-            // Remove the original table.
-            maTables.erase(itrOld);
-        }
-    }
-}
-
-void ScExternalRefManager::RefCells::insertTable(SCTAB nPos)
+ScExternalRefManager::LinkListener::LinkListener()
 {
-    TabItemRef xNewTab(new TabItem(nPos));
-    list<TabItemRef>::iterator itr = getTabPos(nPos);
-    if (itr == maTables.end())
-        maTables.push_back(xNewTab);
-    else
-        maTables.insert(itr, xNewTab);
 }
 
-void ScExternalRefManager::RefCells::removeTable(SCTAB nPos)
+ScExternalRefManager::LinkListener::~LinkListener()
 {
-    list<TabItemRef>::iterator itr = getTabPos(nPos);
-    if (itr == maTables.end())
-        // nothing to remove.
-        return;
-
-    maTables.erase(itr);
-}
-
-void ScExternalRefManager::RefCells::refreshAllCells(ScExternalRefManager& rRefMgr)
-{
-    // Get ALL the cell positions for re-compilation.
-    for (list<TabItemRef>::iterator itrTab = maTables.begin(), itrTabEnd = maTables.end();
-          itrTab != itrTabEnd; ++itrTab)
-    {
-        SCTAB nTab = (*itrTab)->mnIndex;
-        ColSet& rCols = (*itrTab)->maCols;
-        for (ColSet::iterator itrCol = rCols.begin(), itrColEnd = rCols.end();
-              itrCol != itrColEnd; ++itrCol)
-        {
-            SCCOL nCol = itrCol->first;
-            RowSet& rRows = itrCol->second;
-            RowSet aNewRows;
-            for (RowSet::iterator itrRow = rRows.begin(), itrRowEnd = rRows.end();
-                  itrRow != itrRowEnd; ++itrRow)
-            {
-                SCROW nRow = *itrRow;
-                ScAddress aCell(nCol, nRow, nTab);
-                if (rRefMgr.compileTokensByCell(aCell))
-                    // This cell still contains an external refernce.
-                    aNewRows.insert(nRow);
-            }
-            // Update the rows so that cells with no external references are
-            // no longer tracked.
-            rRows.swap(aNewRows);
-        }
-    }
 }
 
 // ----------------------------------------------------------------------------
 
-ScExternalRefManager::LinkListener::LinkListener()
+ScExternalRefManager::ApiGuard::ApiGuard(ScDocument* pDoc) :
+    mpMgr(pDoc->GetExternalRefManager()),
+    mbOldInteractionEnabled(mpMgr->mbUserInteractionEnabled)
 {
+    // We don't want user interaction handled in the API.
+    mpMgr->mbUserInteractionEnabled = false;
 }
 
-ScExternalRefManager::LinkListener::~LinkListener()
+ScExternalRefManager::ApiGuard::~ApiGuard()
 {
+    // Restore old value.
+    mpMgr->mbUserInteractionEnabled = mbOldInteractionEnabled;
 }
 
 // ----------------------------------------------------------------------------
@@ -1595,9 +1605,22 @@ bool ScExternalRefManager::markUsedByLinkListeners()
     return bAllMarked;
 }
 
-bool ScExternalRefManager::setCacheDocReferenced( sal_uInt16 nFileId )
+bool ScExternalRefManager::markUsedExternalRefCells()
 {
-    return maRefCache.setCacheDocReferenced( nFileId);
+    RefCellMap::iterator itr = maRefCells.begin(), itrEnd = maRefCells.end();
+    for (; itr != itrEnd; ++itr)
+    {
+        RefCellSet::iterator itrCell = itr->second.begin(), itrCellEnd = itr->second.end();
+        for (; itrCell != itrCellEnd; ++itrCell)
+        {
+            ScFormulaCell* pCell = *itrCell;    
+            bool bUsed = pCell->MarkUsedExternalReferences();
+            if (bUsed)
+                // Return true when at least one cell references external docs.
+                return true;
+        }
+    }
+    return false;
 }
 
 bool ScExternalRefManager::setCacheTableReferenced( sal_uInt16 nFileId, const String& rTabName, size_t nSheets )
@@ -1617,7 +1640,7 @@ void ScExternalRefManager::setCacheTableReferencedPermanently( sal_uInt16 nFileI
 
 void ScExternalRefManager::setAllCacheTableReferencedStati( bool bReferenced )
 {
-    bInReferenceMarking = !bReferenced;
+    mbInReferenceMarking = !bReferenced;
     maRefCache.setAllCacheTableReferencedStati( bReferenced );
 }
 
@@ -1642,20 +1665,13 @@ ScExternalRefCache::TokenRef ScExternalRefManager::getSingleRefToken(
     if (pFmt)
         pFmt->mbIsSet = false;
 
-    bool bLoading = mpDoc->IsImportingXML();
-
     // Check if the given table name and the cell position is cached.
-    // #i101304# When loading a file, the saved cache (hidden sheet)
-    // is assumed to contain all data for the loaded formulas.
-    // No cache entries are created from empty cells in the saved sheet,
-    // so they have to be created here (bWriteEmpty parameter).
-    // Otherwise, later interpretation of the loaded formulas would
-    // load the source document even if the user didn't want to update.
     sal_uInt32 nFmtIndex = 0;
     ScExternalRefCache::TokenRef pToken = maRefCache.getCellData(
-        nFileId, rTabName, rCell.Col(), rCell.Row(), bLoading, bLoading, &nFmtIndex);
+        nFileId, rTabName, rCell.Col(), rCell.Row(), &nFmtIndex);
     if (pToken)
     {
+        // Cache hit !
         if (pFmt)
         {
             short nFmtType = mpDoc->GetFormatTable()->GetType(nFmtIndex);
@@ -1673,11 +1689,8 @@ ScExternalRefCache::TokenRef ScExternalRefManager::getSingleRefToken(
     ScDocument* pSrcDoc = getSrcDocument(nFileId);
     if (!pSrcDoc)
     {
-        // Source document is not reachable.  Try to get data from the cache
-        // once again, but this time treat a non-cached cell as an empty cell
-        // as long as the table itself is cached.
-        pToken = maRefCache.getCellData(
-            nFileId, rTabName, rCell.Col(), rCell.Row(), true, false, &nFmtIndex);
+        // Source document not reachable.  Throw a reference error.
+        pToken.reset(new FormulaErrorToken(errNoRef));
         return pToken;
     }
 
@@ -1686,12 +1699,30 @@ ScExternalRefCache::TokenRef ScExternalRefManager::getSingleRefToken(
     if (!pSrcDoc->GetTable(rTabName, nTab))
     {
         // specified table name doesn't exist in the source document.
-        return ScExternalRefCache::TokenRef();
+        pToken.reset(new FormulaErrorToken(errNoRef));
+        return pToken;
     }
 
     if (pTab)
         *pTab = nTab;
 
+    SCCOL nDataCol1 = 0, nDataCol2 = MAXCOL;
+    SCROW nDataRow1 = 0, nDataRow2 = MAXROW;
+    pSrcDoc->ShrinkToDataArea(nTab, nDataCol1, nDataRow1, nDataCol2, nDataRow2);
+    if (rCell.Col() < nDataCol1 || nDataCol2 < rCell.Col() || rCell.Row() < nDataRow1 || nDataRow2 < rCell.Row())
+    {
+        // requested cell is outside the data area.  Don't even bother caching
+        // this data, but add it to the cached range to prevent accessing the
+        // source document time and time again.
+        ScExternalRefCache::TableTypeRef pCacheTab = 
+            maRefCache.getCacheTable(nFileId, rTabName, true, NULL);
+        if (pCacheTab)
+            pCacheTab->setCachedCell(rCell.Col(), rCell.Row());
+
+        pToken.reset(new ScEmptyCellToken(false, false));
+        return pToken;
+    }
+
     pSrcDoc->GetCell(rCell.Col(), rCell.Row(), nTab, pCell);
     ScExternalRefCache::TokenRef pTok(lcl_convertToToken(pCell));
 
@@ -1714,39 +1745,45 @@ ScExternalRefCache::TokenRef ScExternalRefManager::getSingleRefToken(
         pTok.reset( new FormulaErrorToken( errNoValue));
     }
 
-    // Now, insert the token into cache table.
-    maRefCache.setCellData(nFileId, rTabName, rCell.Row(), rCell.Col(), pTok, nFmtIndex);
+    // Now, insert the token into cache table but don't cache empty cells.
+    if (pTok->GetType() != formula::svEmptyCell)
+        maRefCache.setCellData(nFileId, rTabName, rCell.Col(), rCell.Row(), pTok, nFmtIndex);
+
     return pTok;
 }
 
-ScExternalRefCache::TokenArrayRef ScExternalRefManager::getDoubleRefTokens(sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, const ScAddress* pCurPos)
+ScExternalRefCache::TokenArrayRef ScExternalRefManager::getDoubleRefTokens(
+    sal_uInt16 nFileId, const String& rTabName, const ScRange& rRange, const ScAddress* pCurPos)
 {
     if (pCurPos)
         insertRefCell(nFileId, *pCurPos);
 
     maybeLinkExternalFile(nFileId);
 
-    bool bLoading = mpDoc->IsImportingXML();
-
     // Check if the given table name and the cell position is cached.
-    // #i101304# When loading, put empty cells into cache, see getSingleRefToken.
-    ScExternalRefCache::TokenArrayRef p = maRefCache.getCellRangeData(nFileId, rTabName, rRange, bLoading, bLoading);
-    if (p.get())
-        return p;
+    ScExternalRefCache::TokenArrayRef pArray = 
+        maRefCache.getCellRangeData(nFileId, rTabName, rRange);
+    if (pArray)
+        // Cache hit !
+        return pArray;
 
     ScDocument* pSrcDoc = getSrcDocument(nFileId);
     if (!pSrcDoc)
     {
-        // Source document is not reachable.  Try to get data from the cache
-        // once again, but this time treat non-cached cells as empty cells as
-        // long as the table itself is cached.
-        return maRefCache.getCellRangeData(nFileId, rTabName, rRange, true, false);
+        // Source document is not reachable.  Throw a reference error.
+        pArray.reset(new ScTokenArray);
+        pArray->AddToken(FormulaErrorToken(errNoRef));
+        return pArray;
     }
 
     SCTAB nTab1;
     if (!pSrcDoc->GetTable(rTabName, nTab1))
+    {
         // specified table name doesn't exist in the source document.
-        return ScExternalRefCache::TokenArrayRef();
+        pArray.reset(new ScTokenArray);
+        pArray->AddToken(FormulaErrorToken(errNoRef));
+        return pArray;
+    }
 
     ScRange aRange(rRange);
     SCTAB nTabSpan = aRange.aEnd.Tab() - aRange.aStart.Tab();
@@ -1770,12 +1807,24 @@ ScExternalRefCache::TokenArrayRef ScExternalRefManager::getDoubleRefTokens(sal_u
     aRange.aStart.SetTab(nTab1);
     aRange.aEnd.SetTab(nTab1 + nTabSpan);
 
-    ScExternalRefCache::TokenArrayRef pArray;
     pArray.reset(lcl_convertToTokenArray(pSrcDoc, aRange, aCacheData));
 
     if (pArray)
         // Cache these values.
-        maRefCache.setCellRangeData(nFileId, rRange, aCacheData, pArray);
+        maRefCache.setCellRangeData(nFileId, aRange, aCacheData, pArray);
+    else
+    {    
+        // Array is empty.  Fill it with an empty matrix of the required size.
+        pArray.reset(lcl_fillEmptyMatrix(rRange));
+
+        // Make sure to set this range 'cached', to prevent unnecessarily 
+        // accessing the src document time and time again.
+        ScExternalRefCache::TableTypeRef pCacheTab = 
+            maRefCache.getCacheTable(nFileId, rTabName, true, NULL);
+        if (pCacheTab)
+            pCacheTab->setCachedCellRange(
+                rRange.aStart.Col(), rRange.aStart.Row(), rRange.aEnd.Col(), rRange.aEnd.Row());
+    }
 
     return pArray;
 }
@@ -1858,8 +1907,8 @@ void ScExternalRefManager::refreshAllRefCells(sal_uInt16 nFileId)
     if (itrFile == maRefCells.end())
         return;
 
-    RefCells& rRefCells = itrFile->second;
-    rRefCells.refreshAllCells(*this);
+    RefCellSet& rRefCells = itrFile->second;
+    for_each(rRefCells.begin(), rRefCells.end(), UpdateFormulaCell());
 
     ScViewData* pViewData = ScDocShell::GetViewData();
     if (!pViewData)
@@ -1880,7 +1929,7 @@ void ScExternalRefManager::insertRefCell(sal_uInt16 nFileId, const ScAddress& rC
     RefCellMap::iterator itr = maRefCells.find(nFileId);
     if (itr == maRefCells.end())
     {
-        RefCells aRefCells;
+        RefCellSet aRefCells;
         pair<RefCellMap::iterator, bool> r = maRefCells.insert(
             RefCellMap::value_type(nFileId, aRefCells));
         if (!r.second)
@@ -1889,7 +1938,10 @@ void ScExternalRefManager::insertRefCell(sal_uInt16 nFileId, const ScAddress& rC
 
         itr = r.first;
     }
-    itr->second.insertCell(rCell);
+
+    ScBaseCell* pCell = mpDoc->GetCell(rCell);
+    if (pCell && pCell->GetCellType() == CELLTYPE_FORMULA)
+        itr->second.insert(static_cast<ScFormulaCell*>(pCell));
 }
 
 ScDocument* ScExternalRefManager::getSrcDocument(sal_uInt16 nFileId)
@@ -1902,6 +1954,12 @@ ScDocument* ScExternalRefManager::getSrcDocument(sal_uInt16 nFileId)
 
     if (itr != itrEnd)
     {
+        // document already loaded.
+
+        // TODO: Find out a way to access a document that's already open in
+        // memory and re-use that instance, instead of loading it from the
+        // disk again.
+
         SfxObjectShell* p = itr->second.maShell;
         itr->second.maLastAccess = Time();
         return static_cast<ScDocShell*>(p)->GetDocument();
@@ -1996,7 +2054,8 @@ SfxObjectShellRef ScExternalRefManager::loadSrcDocument(sal_uInt16 nFileId, Stri
     if (pMedium->GetError() != ERRCODE_NONE)
         return NULL;
 
-    pMedium->UseInteractionHandler(false);
+    // To load encrypted documents with password, user interaction needs to be enabled.
+    pMedium->UseInteractionHandler(mbUserInteractionEnabled);
 
     ScDocShell* pNewShell = new ScDocShell(SFX_CREATE_MODE_INTERNAL);
     SfxObjectShellRef aRef = pNewShell;
@@ -2005,6 +2064,10 @@ SfxObjectShellRef ScExternalRefManager::loadSrcDocument(sal_uInt16 nFileId, Stri
     ScExtDocOptions* pExtOpt = mpDoc->GetExtDocOptions();
     sal_uInt32 nLinkCount = pExtOpt ? pExtOpt->GetDocSettings().mnLinkCnt : 0;
     ScDocument* pSrcDoc = pNewShell->GetDocument();
+    pSrcDoc->EnableExecuteLink(false); // to prevent circular access of external references.
+    pSrcDoc->EnableUndo(false);
+    pSrcDoc->EnableAdjustHeight(false);
+
     ScExtDocOptions* pExtOptNew = pSrcDoc->GetExtDocOptions();
     if (!pExtOptNew)
     {
@@ -2082,35 +2145,6 @@ void ScExternalRefManager::maybeCreateRealFileName(sal_uInt16 nFileId)
     maSrcFiles[nFileId].maybeCreateRealFileName(getOwnDocumentName());
 }
 
-bool ScExternalRefManager::compileTokensByCell(const ScAddress& rCell)
-{
-    ScBaseCell* pCell;
-    mpDoc->GetCell(rCell.Col(), rCell.Row(), rCell.Tab(), pCell);
-
-    if (!pCell || pCell->GetCellType() != CELLTYPE_FORMULA)
-        return false;
-
-    ScFormulaCell* pFC = static_cast<ScFormulaCell*>(pCell);
-
-    // Check to make sure the cell really contains ocExternalRef.
-    // External names, external cell and range references all have a
-    // ocExternalRef token.
-    const ScTokenArray* pCode = pFC->GetCode();
-    if (!pCode->HasOpCode( ocExternalRef))
-        return false;
-
-    ScTokenArray* pArray = pFC->GetCode();
-    if (pArray)
-        // Clear the error code, or a cell with error won't get re-compiled.
-        pArray->SetCodeError(0);
-
-    pFC->SetCompile(true);
-    pFC->CompileTokenArray();
-    pFC->SetDirty();
-
-    return true;
-}
-
 const String& ScExternalRefManager::getOwnDocumentName() const
 {
     SfxObjectShell* pShell = mpDoc->GetDocumentShell();
@@ -2222,6 +2256,18 @@ void ScExternalRefManager::refreshNames(sal_uInt16 nFileId)
 
 void ScExternalRefManager::breakLink(sal_uInt16 nFileId)
 {
+    // Turn all formula cells referencing this external document into static
+    // cells.
+    RefCellMap::iterator itrRefs = maRefCells.find(nFileId);
+    if (itrRefs != maRefCells.end())
+    {
+        // Make a copy because removing the formula cells below will modify
+        // the original container.
+        RefCellSet aSet = itrRefs->second;
+        for_each(aSet.begin(), aSet.end(), ConvertFormulaToStatic(mpDoc));
+        maRefCells.erase(nFileId);
+    }
+
     lcl_removeByFileId(nFileId, maDocShells);
 
     if (maDocShells.empty())
@@ -2293,32 +2339,9 @@ void ScExternalRefManager::resetSrcFileData(const String& rBaseFileUrl)
     }
 }
 
-void ScExternalRefManager::updateRefCell(const ScAddress& rOldPos, const ScAddress& rNewPos, bool bCopy)
-{
-    for (RefCellMap::iterator itr = maRefCells.begin(), itrEnd = maRefCells.end(); itr != itrEnd; ++itr)
-    {
-        if (!bCopy)
-            itr->second.removeCell(rOldPos);
-        itr->second.insertCell(rNewPos);
-    }
-}
-
-void ScExternalRefManager::updateRefMoveTable(SCTAB nOldTab, SCTAB nNewTab, bool bCopy)
-{
-    for (RefCellMap::iterator itr = maRefCells.begin(), itrEnd = maRefCells.end(); itr != itrEnd; ++itr)
-        itr->second.moveTable(nOldTab, nNewTab, bCopy);
-}
-
-void ScExternalRefManager::updateRefInsertTable(SCTAB nPos)
-{
-    for (RefCellMap::iterator itr = maRefCells.begin(), itrEnd = maRefCells.end(); itr != itrEnd; ++itr)
-        itr->second.insertTable(nPos);
-}
-
-void ScExternalRefManager::updateRefDeleteTable(SCTAB nPos)
+void ScExternalRefManager::removeRefCell(ScFormulaCell* pCell)
 {
-    for (RefCellMap::iterator itr = maRefCells.begin(), itrEnd = maRefCells.end(); itr != itrEnd; ++itr)
-        itr->second.removeTable(nPos);
+    for_each(maRefCells.begin(), maRefCells.end(), RemoveFormulaCell(pCell));
 }
 
 void ScExternalRefManager::addLinkListener(sal_uInt16 nFileId, LinkListener* pListener)
diff --git a/sc/source/ui/unoobj/cellsuno.cxx b/sc/source/ui/unoobj/cellsuno.cxx
index 145e6b3ab..35d2e5877 100644
--- a/sc/source/ui/unoobj/cellsuno.cxx
+++ b/sc/source/ui/unoobj/cellsuno.cxx
@@ -5265,6 +5265,8 @@ void SAL_CALL ScCellRangeObj::setFormulaArray(
     ScDocShell* pDocSh = GetDocShell();
     if (pDocSh)
     {
+        ScExternalRefManager::ApiGuard aExtRefGuard(pDocSh->GetDocument());
+
         // GRAM_PODF_A1 for API compatibility.
         bDone = lcl_PutFormulaArray( *pDocSh, aRange, aArray, EMPTY_STRING, formula::FormulaGrammar::GRAM_PODF_A1 );
     }
diff --git a/sc/source/ui/unoobj/linkuno.cxx b/sc/source/ui/unoobj/linkuno.cxx
index fb7e1f0a6..b6dd5d039 100644
--- a/sc/source/ui/unoobj/linkuno.cxx
+++ b/sc/source/ui/unoobj/linkuno.cxx
@@ -1605,12 +1605,16 @@ ScExternalDocLinkObj::~ScExternalDocLinkObj()
 }
 
 Reference< sheet::XExternalSheetCache > SAL_CALL ScExternalDocLinkObj::addSheetCache(
-    const OUString& aSheetName )
+    const OUString& aSheetName, sal_Bool bDynamicCache )
         throw (RuntimeException)
 {
     ScUnoGuard aGuard;
     size_t nIndex = 0;
     ScExternalRefCache::TableTypeRef pTable = mpRefMgr->getCacheTable(mnFileId, aSheetName, true, &nIndex);
+    if (!bDynamicCache)
+        // Set the whole table cached to prevent access to the source document.
+        pTable->setWholeTableCached();
+
     Reference< sheet::XExternalSheetCache > aSheetCache(new ScExternalSheetCacheObj(pTable, nIndex));
     return aSheetCache;
 }
diff --git a/sc/source/ui/unoobj/tokenuno.cxx b/sc/source/ui/unoobj/tokenuno.cxx
index f699ef4cb..91f7fedc5 100644
--- a/sc/source/ui/unoobj/tokenuno.cxx
+++ b/sc/source/ui/unoobj/tokenuno.cxx
@@ -140,9 +140,11 @@ uno::Sequence<sheet::FormulaToken> SAL_CALL ScFormulaParserObj::parseFormula(
 
     if (mpDocShell)
     {
+        ScDocument* pDoc = mpDocShell->GetDocument();
+        ScExternalRefManager::ApiGuard aExtRefGuard(pDoc);
+
         ScAddress aRefPos( ScAddress::UNINITIALIZED );
         ScUnoConversion::FillScAddress( aRefPos, rReferencePos );
-        ScDocument* pDoc = mpDocShell->GetDocument();
         ScCompiler aCompiler( pDoc, aRefPos);
         aCompiler.SetGrammar(pDoc->GetGrammar());
         SetCompilerFlags( aCompiler );