CWS changehid: resync to m84

12 files changed, 930 insertions, 413 deletions

diff --git a/sc/source/ui/docshell/dbdocfun.cxx b/sc/source/ui/docshell/dbdocfun.cxx
index 9895f0f43610..cf149d258062 100644
--- a/sc/source/ui/docshell/dbdocfun.cxx
+++ b/sc/source/ui/docshell/dbdocfun.cxx
@@ -937,7 +937,10 @@ BOOL ScDBDocFunc::Query( SCTAB nTab, const ScQueryParam& rQueryParam,
     }
 
     if (!bCopy)
+    {
+        pDoc->InvalidatePageBreaks(nTab);
         pDoc->UpdatePageBreaks( nTab );
+    }
 
     // #i23299# because of Subtotal functions, the whole rows must be set dirty
     ScRange aDirtyRange( 0 , aLocalParam.nRow1, nDestTab,
@@ -1275,6 +1278,13 @@ BOOL ScDBDocFunc::DataPilotUpdate( ScDPObject* pOldObj, const ScDPObject* pNewOb
                 //  output range must be set at pNewObj
 
                 pDestObj = new ScDPObject( *pNewObj );
+
+                // #i94570# When changing the output position in the dialog, a new table is created
+                // with the settings from the old table, including the name.
+                // So we have to check for duplicate names here (before inserting).
+                if ( pDoc->GetDPCollection()->GetByName(pDestObj->GetName()) )
+                    pDestObj->SetName( String() );      // ignore the invalid name, create a new name below
+
                 pDestObj->SetAlive(TRUE);
                 if ( !pDoc->GetDPCollection()->InsertNewTable(pDestObj) )
                 {
diff --git a/sc/source/ui/docshell/docfunc.cxx b/sc/source/ui/docshell/docfunc.cxx
index cd28daa99401..27b8eef7bbd6 100644
--- a/sc/source/ui/docshell/docfunc.cxx
+++ b/sc/source/ui/docshell/docfunc.cxx
@@ -48,6 +48,12 @@
 #include <svl/zforlist.hxx>
 #include <svl/PasswordHelper.hxx>
 
+#include <basic/sbstar.hxx>
+#include <com/sun/star/container/XNameContainer.hpp>
+#include <com/sun/star/script/XLibraryContainer.hpp>
+#include <com/sun/star/script/XVBAModuleInfo.hpp>
+#include <com/sun/star/script/ModuleType.hpp>
+
 #include <list>
 
 #include "docfunc.hxx"
@@ -92,8 +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;
@@ -1035,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
 
@@ -2576,6 +2589,106 @@ BOOL ScDocFunc::MoveBlock( const ScRange& rSource, const ScAddress& rDestPos,
 }
 
 //------------------------------------------------------------------------
+uno::Reference< uno::XInterface > GetDocModuleObject( SfxObjectShell& rDocSh, String& sCodeName )
+{
+    uno::Reference< lang::XMultiServiceFactory> xSF(rDocSh.GetModel(), uno::UNO_QUERY);
+    uno::Reference< container::XNameAccess > xVBACodeNamedObjectAccess;
+    uno::Reference< uno::XInterface > xDocModuleApiObject;
+    if ( xSF.is() )
+    {
+        xVBACodeNamedObjectAccess.set( xSF->createInstance( rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( "ooo.vba.VBAObjectModuleObjectProvider"))), uno::UNO_QUERY );
+        xDocModuleApiObject.set( xVBACodeNamedObjectAccess->getByName( sCodeName ), uno::UNO_QUERY );
+    }
+    return xDocModuleApiObject;
+
+}
+
+script::ModuleInfo lcl_InitModuleInfo( SfxObjectShell& rDocSh, String& sModule )
+{
+    ::rtl::OUString sVbaOption( RTL_CONSTASCII_USTRINGPARAM( "Rem Attribute VBA_ModuleType=VBADocumentModule\nOption VBASupport 1\n" ));
+    script::ModuleInfo sModuleInfo;
+    sModuleInfo.ModuleType = script::ModuleType::DOCUMENT;
+    sModuleInfo.ModuleObject = GetDocModuleObject( rDocSh, sModule );
+    return sModuleInfo;
+}
+
+void VBA_InsertModule( ScDocument& rDoc, SCTAB nTab, String& sModuleName, String& sSource )
+{
+    SFX_APP()->EnterBasicCall();
+    SfxObjectShell& rDocSh = *rDoc.GetDocumentShell();
+    uno::Reference< script::XLibraryContainer > xLibContainer = rDocSh.GetBasicContainer();
+    DBG_ASSERT( xLibContainer.is(), "No BasicContainer!" );
+
+    uno::Reference< container::XNameContainer > xLib;
+    if( xLibContainer.is() )
+    {
+        String aLibName( RTL_CONSTASCII_USTRINGPARAM( "Standard" ) );
+        if ( rDocSh.GetBasicManager() && rDocSh.GetBasicManager()->GetName().Len() )
+            aLibName = rDocSh.GetBasicManager()->GetName();
+        uno::Any aLibAny = xLibContainer->getByName( aLibName );
+        aLibAny >>= xLib;
+    }
+    if( xLib.is() )
+    {
+        // if the Module with codename exists then find a new name
+        sal_Int32 nNum = 0;
+        String genModuleName;
+        if ( sModuleName.Len() )
+            sModuleName = sModuleName;
+        else
+        {
+             genModuleName = String::CreateFromAscii( "Sheet1" );
+             nNum = 1;
+        }
+        while( xLib->hasByName( genModuleName  ) )
+            genModuleName = rtl::OUString::createFromAscii( "Sheet" ) + rtl::OUString::valueOf( ++nNum );
+
+        uno::Any aSourceAny;
+        rtl::OUString sTmpSource = sSource;
+        if ( sTmpSource.getLength() == 0 )
+            sTmpSource = ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "Rem Attribute VBA_ModuleType=VBADocumentModule\nOption VBASupport 1\n" ));
+        aSourceAny <<= sTmpSource;
+        uno::Reference< script::XVBAModuleInfo > xVBAModuleInfo( xLib, uno::UNO_QUERY );
+        if ( xVBAModuleInfo.is() )
+        {
+            String sCodeName( genModuleName );
+            rDoc.SetCodeName( nTab, sCodeName );
+            script::ModuleInfo sModuleInfo = lcl_InitModuleInfo(  rDocSh, genModuleName );
+            xVBAModuleInfo->insertModuleInfo( genModuleName, sModuleInfo );
+            xLib->insertByName( genModuleName, aSourceAny );
+        }
+
+    }
+    SFX_APP()->LeaveBasicCall();
+}
+
+void VBA_DeleteModule( ScDocShell& rDocSh, String& sModuleName )
+{
+    SFX_APP()->EnterBasicCall();
+    uno::Reference< script::XLibraryContainer > xLibContainer = rDocSh.GetBasicContainer();
+    DBG_ASSERT( xLibContainer.is(), "No BasicContainer!" );
+
+    uno::Reference< container::XNameContainer > xLib;
+    if( xLibContainer.is() )
+    {
+        String aLibName( RTL_CONSTASCII_USTRINGPARAM( "Standard" ) );
+        if ( rDocSh.GetBasicManager() && rDocSh.GetBasicManager()->GetName().Len() )
+            aLibName = rDocSh.GetBasicManager()->GetName();
+        uno::Any aLibAny = xLibContainer->getByName( aLibName );
+        aLibAny >>= xLib;
+    }
+    if( xLib.is() )
+    {
+        uno::Reference< script::XVBAModuleInfo > xVBAModuleInfo( xLib, uno::UNO_QUERY );
+        if( xLib->hasByName( sModuleName ) )
+            xLib->removeByName( sModuleName );
+        if ( xVBAModuleInfo.is() )
+            xVBAModuleInfo->removeModuleInfo( sModuleName );
+
+    }
+    SFX_APP()->LeaveBasicCall();
+}
+
 
 BOOL ScDocFunc::InsertTable( SCTAB nTab, const String& rName, BOOL bRecord, BOOL bApi )
 {
@@ -2585,8 +2698,19 @@ BOOL ScDocFunc::InsertTable( SCTAB nTab, const String& rName, BOOL bRecord, BOOL
     ScDocShellModificator aModificator( rDocShell );
 
     ScDocument* pDoc = rDocShell.GetDocument();
-    if (bRecord && !pDoc->IsUndoEnabled())
+
+
+    // Strange loop, also basic is loaded too early ( InsertTable )
+    // is called via the xml import for sheets in described in odf
+    BOOL bInsertDocModule = false;
+
+    if(  !rDocShell.GetDocument()->IsImportingXML() )
+    {
+        bInsertDocModule = pDoc ? pDoc->IsInVBAMode() : false;
+    }
+    if ( bInsertDocModule || ( bRecord && !pDoc->IsUndoEnabled() ) )
         bRecord = FALSE;
+
     if (bRecord)
         pDoc->BeginDrawUndo();                          //  InsertTab erzeugt ein SdrUndoNewPage
 
@@ -2597,10 +2721,17 @@ BOOL ScDocFunc::InsertTable( SCTAB nTab, const String& rName, BOOL bRecord, BOOL
 
     if (pDoc->InsertTab( nTab, rName ))
     {
+        String sCodeName;
         if (bRecord)
             rDocShell.GetUndoManager()->AddUndoAction(
                         new ScUndoInsertTab( &rDocShell, nTab, bAppend, rName));
         //  Views updaten:
+        // Only insert vba modules if vba mode ( and not currently importing XML )
+        if( bInsertDocModule )
+        {
+            String sSource;
+            VBA_InsertModule( *pDoc, nTab, sCodeName, sSource );
+        }
         rDocShell.Broadcast( ScTablesHint( SC_TAB_INSERTED, nTab ) );
 
         rDocShell.PostPaintExtras();
@@ -2622,8 +2753,11 @@ BOOL ScDocFunc::DeleteTable( SCTAB nTab, BOOL bRecord, BOOL /* bApi */ )
 
     BOOL bSuccess = FALSE;
     ScDocument* pDoc = rDocShell.GetDocument();
+    BOOL bVbaEnabled = pDoc ? pDoc->IsInVBAMode() : false;
     if (bRecord && !pDoc->IsUndoEnabled())
         bRecord = FALSE;
+    if ( bVbaEnabled )
+        bRecord = FALSE;
     BOOL bWasLinked = pDoc->IsLinked(nTab);
     ScDocument* pUndoDoc = NULL;
     ScRefUndoData* pUndoData = NULL;
@@ -2657,6 +2791,8 @@ BOOL ScDocFunc::DeleteTable( SCTAB nTab, BOOL bRecord, BOOL /* bApi */ )
             pUndoDoc->SetActiveScenario( nTab, bActive );
         }
         pUndoDoc->SetVisible( nTab, pDoc->IsVisible( nTab ) );
+        pUndoDoc->SetTabBgColor( nTab, pDoc->GetTabBgColor(nTab) );
+        pUndoDoc->SetSheetEvents( nTab, pDoc->GetSheetEvents( nTab ) );
 
         //  Drawing-Layer muss sein Undo selbst in der Hand behalten !!!
         pDoc->BeginDrawUndo();                          //  DeleteTab erzeugt ein SdrUndoDelPage
@@ -2664,6 +2800,8 @@ BOOL ScDocFunc::DeleteTable( SCTAB nTab, BOOL bRecord, BOOL /* bApi */ )
         pUndoData = new ScRefUndoData( pDoc );
     }
 
+    String sCodeName;
+    BOOL bHasCodeName = pDoc->GetCodeName( nTab, sCodeName );
     if (pDoc->DeleteTab( nTab, pUndoDoc ))
     {
         if (bRecord)
@@ -2674,6 +2812,13 @@ BOOL ScDocFunc::DeleteTable( SCTAB nTab, BOOL bRecord, BOOL /* bApi */ )
                         new ScUndoDeleteTab( &rDocShell, theTabs, pUndoDoc, pUndoData ));
         }
         //  Views updaten:
+        if( bVbaEnabled )
+        {
+            if( bHasCodeName )
+            {
+                VBA_DeleteModule( rDocShell, sCodeName );
+            }
+        }
         rDocShell.Broadcast( ScTablesHint( SC_TAB_DELETED, nTab ) );
 
         if (bWasLinked)
@@ -2852,6 +2997,104 @@ BOOL ScDocFunc::RenameTable( SCTAB nTab, const String& rName, BOOL bRecord, BOOL
     return bSuccess;
 }
 
+bool ScDocFunc::SetTabBgColor( SCTAB nTab, const Color& rColor, bool bRecord, bool bApi )
+{
+
+    ScDocument* pDoc = rDocShell.GetDocument();
+    if (bRecord && !pDoc->IsUndoEnabled())
+        bRecord = false;
+    if ( !pDoc->IsDocEditable() || pDoc->IsTabProtected(nTab) )
+    {
+        if (!bApi)
+            rDocShell.ErrorMessage(STR_PROTECTIONERR); //TODO Check to see what this string is...
+        return false;
+    }
+
+    Color aOldTabBgColor;
+    aOldTabBgColor = pDoc->GetTabBgColor(nTab);
+
+    bool bSuccess = false;
+    pDoc->SetTabBgColor(nTab, rColor);
+    if ( pDoc->GetTabBgColor(nTab) == rColor)
+        bSuccess = true;
+    if (bSuccess)
+    {
+        if (bRecord)
+        {
+            rDocShell.GetUndoManager()->AddUndoAction(
+                new ScUndoTabColor( &rDocShell, nTab, aOldTabBgColor, rColor));
+        }
+        rDocShell.PostPaintExtras();
+        ScDocShellModificator aModificator( rDocShell );
+        aModificator.SetDocumentModified();
+        SFX_APP()->Broadcast( SfxSimpleHint( SC_HINT_TABLES_CHANGED ) );
+
+        bSuccess = true;
+    }
+    return bSuccess;
+}
+
+bool ScDocFunc::SetTabBgColor(
+    ScUndoTabColorInfo::List& rUndoTabColorList, bool bRecord, bool bApi )
+{
+    ScDocument* pDoc = rDocShell.GetDocument();
+    if (bRecord && !pDoc->IsUndoEnabled())
+        bRecord = false;
+
+    if ( !pDoc->IsDocEditable() )
+    {
+        if (!bApi)
+            rDocShell.ErrorMessage(STR_PROTECTIONERR); //TODO Get a better String Error...
+        return false;
+    }
+
+    USHORT nTab;
+    Color aNewTabBgColor;
+    bool bSuccess = true;
+    size_t nTabProtectCount = 0;
+    size_t nTabListCount = rUndoTabColorList.size();
+    for ( size_t i = 0; i < nTabListCount; ++i )
+    {
+        ScUndoTabColorInfo& rInfo = rUndoTabColorList[i];
+        nTab = rInfo.mnTabId;
+        if ( !pDoc->IsTabProtected(nTab) )
+        {
+            aNewTabBgColor = rInfo.maNewTabBgColor;
+            rInfo.maOldTabBgColor = pDoc->GetTabBgColor(nTab);
+            pDoc->SetTabBgColor(nTab, aNewTabBgColor);
+            if ( pDoc->GetTabBgColor(nTab) != aNewTabBgColor)
+            {
+                bSuccess = false;
+                break;
+            }
+        }
+        else
+        {
+            nTabProtectCount++;
+        }
+    }
+
+    if ( nTabProtectCount == nTabListCount )
+    {
+        if (!bApi)
+            rDocShell.ErrorMessage(STR_PROTECTIONERR); //TODO Get a better String Error...
+        return false;
+    }
+
+    if (bSuccess)
+    {
+        if (bRecord)
+        {
+            rDocShell.GetUndoManager()->AddUndoAction(
+                new ScUndoTabColor( &rDocShell, rUndoTabColorList));
+        }
+        rDocShell.PostPaintExtras();
+        ScDocShellModificator aModificator( rDocShell );
+        aModificator.SetDocumentModified();
+    }
+    return bSuccess;
+}
+
 //------------------------------------------------------------------------
 
 //! SetWidthOrHeight - noch doppelt zu ViewFunc !!!!!!
@@ -2954,7 +3197,9 @@ BOOL ScDocFunc::SetWidthOrHeight( BOOL bWidth, SCCOLROW nRangeCnt, SCCOLROW* pRa
                     for (SCROW nRow=nStartNo; nRow<=nEndNo; nRow++)
                     {
                         BYTE nOld = pDoc->GetRowFlags(nRow,nTab);
-                        if ( (nOld & CR_HIDDEN) == 0 && ( nOld & CR_MANUALSIZE ) )
+                        SCROW nLastRow = -1;
+                        bool bHidden = pDoc->RowHidden(nRow, nTab, nLastRow);
+                        if ( !bHidden && ( nOld & CR_MANUALSIZE ) )
                             pDoc->SetRowFlags( nRow, nTab, nOld & ~CR_MANUALSIZE );
                     }
                 }
@@ -2989,8 +3234,8 @@ BOOL ScDocFunc::SetWidthOrHeight( BOOL bWidth, SCCOLROW nRangeCnt, SCCOLROW* pRa
         {
             for (SCCOL nCol=static_cast<SCCOL>(nStartNo); nCol<=static_cast<SCCOL>(nEndNo); nCol++)
             {
-                if ( eMode != SC_SIZE_VISOPT ||
-                     (pDoc->GetColFlags( nCol, nTab ) & CR_HIDDEN) == 0 )
+                SCCOL nLastCol = -1;
+                if ( eMode != SC_SIZE_VISOPT || !pDoc->ColHidden(nCol, nTab, nLastCol) )
                 {
                     USHORT nThisSize = nSizeTwips;
 
@@ -3060,20 +3305,22 @@ BOOL ScDocFunc::InsertPageBreak( BOOL bColumn, const ScAddress& rPos,
     if (nPos == 0)
         return FALSE;                   // erste Spalte / Zeile
 
-    BYTE nFlags = bColumn ? pDoc->GetColFlags( static_cast<SCCOL>(nPos), nTab )
-        : pDoc->GetRowFlags( static_cast<SCROW>(nPos), nTab );
-    if (nFlags & CR_MANUALBREAK)
-        return TRUE;                    // Umbruch schon gesetzt
+    ScBreakType nBreak = bColumn ?
+        pDoc->HasColBreak(static_cast<SCCOL>(nPos), nTab) :
+        pDoc->HasRowBreak(static_cast<SCROW>(nPos), nTab);
+    if (nBreak & BREAK_MANUAL)
+        return true;
 
     if (bRecord)
         rDocShell.GetUndoManager()->AddUndoAction(
             new ScUndoPageBreak( &rDocShell, rPos.Col(), rPos.Row(), nTab, bColumn, TRUE ) );
 
-    nFlags |= CR_MANUALBREAK;
     if (bColumn)
-        pDoc->SetColFlags( static_cast<SCCOL>(nPos), nTab, nFlags );
+        pDoc->SetColBreak(static_cast<SCCOL>(nPos), nTab, false, true);
     else
-        pDoc->SetRowFlags( static_cast<SCROW>(nPos), nTab, nFlags );
+        pDoc->SetRowBreak(static_cast<SCROW>(nPos), nTab, false, true);
+
+    pDoc->InvalidatePageBreaks(nTab);
     pDoc->UpdatePageBreaks( nTab );
 
     if (pDoc->IsStreamValid(nTab))
@@ -3119,20 +3366,25 @@ BOOL ScDocFunc::RemovePageBreak( BOOL bColumn, const ScAddress& rPos,
 
     SCCOLROW nPos = bColumn ? static_cast<SCCOLROW>(rPos.Col()) :
         static_cast<SCCOLROW>(rPos.Row());
-    BYTE nFlags = bColumn ? pDoc->GetColFlags( static_cast<SCCOL>(nPos), nTab )
-        : pDoc->GetRowFlags( static_cast<SCROW>(nPos), nTab );
-    if ((nFlags & CR_MANUALBREAK)==0)
-        return FALSE;                           // kein Umbruch gesetzt
+
+    ScBreakType nBreak;
+    if (bColumn)
+        nBreak = pDoc->HasColBreak(static_cast<SCCOL>(nPos), nTab);
+    else
+        nBreak = pDoc->HasRowBreak(static_cast<SCROW>(nPos), nTab);
+    if ((nBreak & BREAK_MANUAL) == 0)
+        // There is no manual break.
+        return false;
 
     if (bRecord)
         rDocShell.GetUndoManager()->AddUndoAction(
             new ScUndoPageBreak( &rDocShell, rPos.Col(), rPos.Row(), nTab, bColumn, FALSE ) );
 
-    nFlags &= ~CR_MANUALBREAK;
     if (bColumn)
-        pDoc->SetColFlags( static_cast<SCCOL>(nPos), nTab, nFlags );
+        pDoc->RemoveColBreak(static_cast<SCCOL>(nPos), nTab, false, true);
     else
-        pDoc->SetRowFlags( static_cast<SCROW>(nPos), nTab, nFlags );
+        pDoc->RemoveRowBreak(static_cast<SCROW>(nPos), nTab, false, true);
+
     pDoc->UpdatePageBreaks( nTab );
 
     if (pDoc->IsStreamValid(nTab))
diff --git a/sc/source/ui/docshell/docsh.cxx b/sc/source/ui/docshell/docsh.cxx
index 135bbcdace49..a446c86b4d1b 100644..100755
--- a/sc/source/ui/docshell/docsh.cxx
+++ b/sc/source/ui/docshell/docsh.cxx
@@ -49,6 +49,7 @@
 #include <svtools/ctrltool.hxx>
 #include <svtools/sfxecode.hxx>
 #include <svl/zforlist.hxx>
+#include <svl/PasswordHelper.hxx>
 #include <sfx2/app.hxx>
 #include <sfx2/bindings.hxx>
 #include <sfx2/dinfdlg.hxx>
@@ -68,6 +69,9 @@
 #include "chgviset.hxx"
 #include <sfx2/request.hxx>
 #include <com/sun/star/document/UpdateDocMode.hpp>
+#include <com/sun/star/container/XContentEnumerationAccess.hpp>
+#include <com/sun/star/sheet/XSpreadsheetView.hpp>
+#include <com/sun/star/task/XJob.hpp>
 
 
 #include "scabstdlg.hxx" //CHINA001
@@ -351,6 +355,20 @@ void ScDocShell::AfterXMLLoading(sal_Bool bRet)
                     // else;  nothing has to happen, because it is a user given name
                 }
             }
+
+            // #i94570# DataPilot table names have to be unique, or the tables can't be accessed by API.
+            // If no name (or an invalid name, skipped in ScXMLDataPilotTableContext::EndElement) was set, create a new name.
+            ScDPCollection* pDPCollection = aDocument.GetDPCollection();
+            if ( pDPCollection )
+            {
+                USHORT nDPCount = pDPCollection->GetCount();
+                for (USHORT nDP=0; nDP<nDPCount; nDP++)
+                {
+                    ScDPObject* pDPObj = (*pDPCollection)[nDP];
+                    if ( !pDPObj->GetName().Len() )
+                        pDPObj->SetName( pDPCollection->CreateNewName() );
+                }
+            }
         }
         ScColumn::bDoubleAlloc = sal_False;
     }
@@ -457,7 +475,7 @@ BOOL __EXPORT ScDocShell::Load( SfxMedium& rMedium )
 
     //  only the latin script language is loaded
     //  -> initialize the others from options (before loading)
-    InitOptions();
+    InitOptions(true);
 
     GetUndoManager()->Clear();
 
@@ -581,6 +599,46 @@ void __EXPORT ScDocShell::Notify( SfxBroadcaster&, const SfxHint& rHint )
                             }
                         }
                     }
+
+                    try
+                    {
+                        uno::Reference< uno::XComponentContext > xContext;
+                        uno::Reference< lang::XMultiServiceFactory > xServiceManager = ::comphelper::getProcessServiceFactory();
+                        uno::Reference< beans::XPropertySet > xProp( xServiceManager, uno::UNO_QUERY_THROW );
+                        xProp->getPropertyValue( ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "DefaultContext" ) ) ) >>= xContext;
+                        if ( xContext.is() )
+                        {
+                            uno::Reference< container::XContentEnumerationAccess > xEnumAccess( xServiceManager, uno::UNO_QUERY_THROW );
+                            uno::Reference< container::XEnumeration> xEnum = xEnumAccess->createContentEnumeration(
+                                ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "com.sun.star.sheet.SpreadsheetDocumentJob" ) ) );
+                            if ( xEnum.is() )
+                            {
+                                while ( xEnum->hasMoreElements() )
+                                {
+                                    uno::Any aAny = xEnum->nextElement();
+                                    uno::Reference< lang::XSingleComponentFactory > xFactory;
+                                    aAny >>= xFactory;
+                                    if ( xFactory.is() )
+                                    {
+                                        uno::Reference< task::XJob > xJob( xFactory->createInstanceWithContext( xContext ), uno::UNO_QUERY_THROW );
+                                        uno::Sequence< beans::NamedValue > aArgsForJob(1);
+                                        ScViewData* pViewData = GetViewData();
+                                        SfxViewShell* pViewShell = ( pViewData ? pViewData->GetViewShell() : NULL );
+                                        SfxViewFrame* pViewFrame = ( pViewShell ? pViewShell->GetViewFrame() : NULL );
+                                        SfxFrame* pFrame = ( pViewFrame ? &pViewFrame->GetFrame() : NULL );
+                                        uno::Reference< frame::XController > xController = ( pFrame ? pFrame->GetController() : 0 );
+                                        uno::Reference< sheet::XSpreadsheetView > xSpreadsheetView( xController, uno::UNO_QUERY_THROW );
+                                        aArgsForJob[0] = beans::NamedValue( ::rtl::OUString::createFromAscii( "SpreadsheetView" ),
+                                            uno::makeAny( xSpreadsheetView ) );
+                                        xJob->execute( aArgsForJob );
+                                    }
+                                }
+                            }
+                        }
+                    }
+                    catch ( uno::Exception & )
+                    {
+                    }
                 }
                 break;
             case SFX_EVENT_SAVEDOC:
@@ -867,10 +925,33 @@ static void lcl_parseHtmlFilterOption(const OUString& rOption, LanguageType& rLa
     rDateConvert = static_cast<bool>(aTokens[1].toInt32());
 }
 
+namespace {
+
+class LoadMediumGuard
+{
+public:
+    explicit LoadMediumGuard(ScDocument* pDoc) :
+        mpDoc(pDoc)
+    {
+        mpDoc->SetLoadingMedium(true);
+    }
+
+    ~LoadMediumGuard()
+    {
+        mpDoc->SetLoadingMedium(false);
+    }
+private:
+    ScDocument* mpDoc;
+};
+
+}
+
 BOOL __EXPORT ScDocShell::ConvertFrom( SfxMedium& rMedium )
 {
     RTL_LOGFILE_CONTEXT_AUTHOR ( aLog, "sc", "nn93723", "ScDocShell::ConvertFrom" );
 
+    LoadMediumGuard aLoadGuard(&aDocument);
+
     BOOL bRet = FALSE;              // FALSE heisst Benutzerabbruch !!
                                     // bei Fehler: Fehler am Stream setzen!!
 
@@ -1937,7 +2018,7 @@ BOOL __EXPORT ScDocShell::ConvertTo( SfxMedium &rMed )
             /*  #115980# #i104990# If the imported document contains a medium
                 password, determine if we can save it, otherwise ask the users
                 whether they want to save without it. */
-            if( !::sfx2::CheckMSPasswordCapabilityForExport( aFltName ) )
+            if( (rMed.GetFilter()->GetFilterFlags() & SFX_FILTER_ENCRYPTION) == 0 )
             {
                 SfxItemSet* pItemSet = rMed.GetItemSet();
                 const SfxPoolItem* pItem = 0;
@@ -2652,3 +2733,97 @@ sal_Bool ScDocShell::AcceptStateUpdate() const
     return sal_False;
 }
 //-->Added by PengYunQuan for Validity Cell Range Picker
+
+
+bool ScDocShell::IsChangeRecording() const
+{
+    ScChangeTrack* pChangeTrack = aDocument.GetChangeTrack();
+    return pChangeTrack != NULL;
+}
+
+
+bool ScDocShell::HasChangeRecordProtection() const
+{
+    bool bRes = false;
+    ScChangeTrack* pChangeTrack = aDocument.GetChangeTrack();
+    if (pChangeTrack)
+        bRes = pChangeTrack->IsProtected();
+    return bRes;
+}
+
+
+void ScDocShell::SetChangeRecording( bool bActivate )
+{
+    bool bOldChangeRecording = IsChangeRecording();
+
+    if (bActivate)
+    {
+        aDocument.StartChangeTracking();
+        ScChangeViewSettings aChangeViewSet;
+        aChangeViewSet.SetShowChanges(TRUE);
+        aDocument.SetChangeViewSettings(aChangeViewSet);
+    }
+    else
+    {
+        aDocument.EndChangeTracking();
+        PostPaintGridAll();
+    }
+
+    if (bOldChangeRecording != IsChangeRecording())
+    {
+        UpdateAcceptChangesDialog();
+        // Slots invalidieren
+        SfxBindings* pBindings = GetViewBindings();
+        if (pBindings)
+            pBindings->InvalidateAll(FALSE);
+    }
+}
+
+
+bool ScDocShell::SetProtectionPassword( const String &rNewPassword )
+{
+    bool bRes = false;
+    ScChangeTrack* pChangeTrack = aDocument.GetChangeTrack();
+    if (pChangeTrack)
+    {
+        sal_Bool bProtected = pChangeTrack->IsProtected();
+
+        if (rNewPassword.Len())
+        {
+            // when password protection is applied change tracking must always be active
+            SetChangeRecording( true );
+
+            ::com::sun::star::uno::Sequence< sal_Int8 > aProtectionHash;
+            SvPasswordHelper::GetHashPassword( aProtectionHash, rNewPassword );
+            pChangeTrack->SetProtection( aProtectionHash );
+        }
+        else
+        {
+            pChangeTrack->SetProtection( ::com::sun::star::uno::Sequence< sal_Int8 >() );
+        }
+        bRes = true;
+
+        if ( bProtected != pChangeTrack->IsProtected() )
+        {
+            UpdateAcceptChangesDialog();
+            SetDocumentModified();
+        }
+    }
+
+    return bRes;
+}
+
+
+bool ScDocShell::GetProtectionHash( /*out*/ ::com::sun::star::uno::Sequence< sal_Int8 > &rPasswordHash )
+{
+    bool bRes = false;
+    ScChangeTrack* pChangeTrack = aDocument.GetChangeTrack();
+    if (pChangeTrack && pChangeTrack->IsProtected())
+    {
+        rPasswordHash = pChangeTrack->GetProtection();
+        bRes = true;
+    }
+    return bRes;
+}
+
+
diff --git a/sc/source/ui/docshell/docsh2.cxx b/sc/source/ui/docshell/docsh2.cxx
index 9e3ed217382f..14ff7622818c 100644
--- a/sc/source/ui/docshell/docsh2.cxx
+++ b/sc/source/ui/docshell/docsh2.cxx
@@ -93,7 +93,7 @@ BOOL __EXPORT ScDocShell::InitNew( const uno::Reference < embed::XStorage >& xSt
     aDocument.SetDrawDefaults();        // drawing layer defaults that are set only in InitNew
 
     // InitOptions sets the document languages, must be called before CreateStandardStyles
-    InitOptions();
+    InitOptions(false);
 
     aDocument.GetStyleSheetPool()->CreateStandardStyles();
     aDocument.UpdStlShtPtrsFrmNms();
diff --git a/sc/source/ui/docshell/docsh3.cxx b/sc/source/ui/docshell/docsh3.cxx
index 4709a065792f..233843935bbd 100644
--- a/sc/source/ui/docshell/docsh3.cxx
+++ b/sc/source/ui/docshell/docsh3.cxx
@@ -424,7 +424,7 @@ double ScDocShell::GetOutputFactor() const
 
 //---------------------------------------------------------------------
 
-void ScDocShell::InitOptions()          // Fortsetzung von InitNew (CLOOKs)
+void ScDocShell::InitOptions(bool bForLoading)      // called from InitNew and Load
 {
     //  Einstellungen aus dem SpellCheckCfg kommen in Doc- und ViewOptions
 
@@ -440,6 +440,14 @@ void ScDocShell::InitOptions()          // Fortsetzung von InitNew (CLOOKs)
     // zweistellige Jahreszahleneingabe aus Extras->Optionen->Allgemein->Sonstiges
     aDocOpt.SetYear2000( sal::static_int_cast<USHORT>( ::utl::MiscCfg().GetYear2000() ) );
 
+    if (bForLoading)
+    {
+        // #i112123# No style:decimal-places attribute means automatic decimals, not the configured default,
+        // so it must not be taken from the global options.
+        // Calculation settings are handled separately in ScXMLBodyContext::EndElement.
+        aDocOpt.SetStdPrecision( SvNumberFormatter::UNLIMITED_PRECISION );
+    }
+
     aDocument.SetDocOptions( aDocOpt );
     aDocument.SetViewOptions( aViewOpt );
 
diff --git a/sc/source/ui/docshell/docsh4.cxx b/sc/source/ui/docshell/docsh4.cxx
index 3ab0cc62bdd2..d335865fc435 100644..100755
--- a/sc/source/ui/docshell/docsh4.cxx
+++ b/sc/source/ui/docshell/docsh4.cxx
@@ -66,6 +66,7 @@ using namespace ::com::sun::star;
 #include <svx/svditer.hxx>
 #include <svx/svdpage.hxx>
 #include <svx/fmshell.hxx>
+#include <svtools/xwindowitem.hxx>
 #include <sfx2/passwd.hxx>
 #include <sfx2/filedlghelper.hxx>
 #include <sfx2/docinsert.hxx>
@@ -120,9 +121,9 @@ using namespace ::com::sun::star;
 #include "scresid.hxx" //add by CHINA001
 #include "scabstdlg.hxx" //CHINA001
 #include "externalrefmgr.hxx"
-
 #include "sharedocdlg.hxx"
 #include "conditio.hxx"
+#include "sheetevents.hxx"
 
 //------------------------------------------------------------------
 
@@ -623,8 +624,8 @@ void ScDocShell::Execute( SfxRequest& rReq )
                     // getting real parent window when called from Security-Options TP
                     Window* pParent = NULL;
                     const SfxPoolItem* pParentItem;
-                    if( pReqArgs && SFX_ITEM_SET == pReqArgs->GetItemState( SID_ATTR_PARENTWINDOW, FALSE, &pParentItem ) )
-                        pParent = ( Window* ) ( ( const OfaPtrItem* ) pParentItem )->GetValue();
+                    if( pReqArgs && SFX_ITEM_SET == pReqArgs->GetItemState( SID_ATTR_XWINDOW, FALSE, &pParentItem ) )
+                        pParent = ( ( const XWindowItem* ) pParentItem )->GetWindowPtr();
 
                     // desired state
                     ScChangeTrack* pChangeTrack = pDoc->GetChangeTrack();
@@ -664,17 +665,7 @@ void ScDocShell::Execute( SfxRequest& rReq )
 
                     if ( bDo )
                     {
-                        //  update "accept changes" dialog
-                        //! notify all views
-                        SfxViewFrame* pViewFrm = SfxViewFrame::Current();
-                        if ( pViewFrm && pViewFrm->HasChildWindow(FID_CHG_ACCEPT) )
-                        {
-                            SfxChildWindow* pChild = pViewFrm->GetChildWindow(FID_CHG_ACCEPT);
-                            if (pChild)
-                            {
-                                ((ScAcceptChgDlgWrapper*)pChild)->ReInitDlg();
-                            }
-                        }
+                        UpdateAcceptChangesDialog();
 
                         // Slots invalidieren
                         if (pBindings)
@@ -693,8 +684,8 @@ void ScDocShell::Execute( SfxRequest& rReq )
             {
                 Window* pParent = NULL;
                 const SfxPoolItem* pParentItem;
-                if( pReqArgs && SFX_ITEM_SET == pReqArgs->GetItemState( SID_ATTR_PARENTWINDOW, FALSE, &pParentItem ) )
-                    pParent = ( Window* ) ( ( const OfaPtrItem* ) pParentItem )->GetValue();
+                if( pReqArgs && SFX_ITEM_SET == pReqArgs->GetItemState( SID_ATTR_XWINDOW, FALSE, &pParentItem ) )
+                    pParent = ( ( const XWindowItem* ) pParentItem )->GetWindowPtr();
                 if ( ExecuteChangeProtectionDialog( pParent ) )
                 {
                     rReq.Done();
@@ -1164,6 +1155,21 @@ void ScDocShell::Execute( SfxRequest& rReq )
 
 //------------------------------------------------------------------
 
+void UpdateAcceptChangesDialog()
+{
+    //  update "accept changes" dialog
+    //! notify all views
+    SfxViewFrame* pViewFrm = SfxViewFrame::Current();
+    if ( pViewFrm && pViewFrm->HasChildWindow( FID_CHG_ACCEPT ) )
+    {
+        SfxChildWindow* pChild = pViewFrm->GetChildWindow( FID_CHG_ACCEPT );
+        if ( pChild )
+            ((ScAcceptChgDlgWrapper*)pChild)->ReInitDlg();
+    }
+}
+
+//------------------------------------------------------------------
+
 BOOL ScDocShell::ExecuteChangeProtectionDialog( Window* _pParent, BOOL bJustQueryIfProtected )
 {
     BOOL bDone = FALSE;
@@ -1217,15 +1223,7 @@ BOOL ScDocShell::ExecuteChangeProtectionDialog( Window* _pParent, BOOL bJustQuer
             }
             if ( bProtected != pChangeTrack->IsProtected() )
             {
-                //  update "accept changes" dialog
-                //! notify all views
-                SfxViewFrame* pViewFrm = SfxViewFrame::Current();
-                if ( pViewFrm && pViewFrm->HasChildWindow( FID_CHG_ACCEPT ) )
-                {
-                    SfxChildWindow* pChild = pViewFrm->GetChildWindow( FID_CHG_ACCEPT );
-                    if ( pChild )
-                        ((ScAcceptChgDlgWrapper*)pChild)->ReInitDlg();
-                }
+                UpdateAcceptChangesDialog();
                 bDone = TRUE;
             }
         }
@@ -1291,6 +1289,14 @@ void ScDocShell::DoHardRecalc( BOOL /* bApi */ )
     if ( pSh )
         pSh->UpdateCharts(TRUE);
 
+    // set notification flags for "calculate" event (used in SFX_HINT_DATACHANGED broadcast)
+    // (might check for the presence of any formulas on each sheet)
+    SCTAB nTabCount = aDocument.GetTableCount();
+    SCTAB nTab;
+    if (aDocument.HasSheetEventScript( SC_SHEETEVENT_CALCULATE ))
+        for (nTab=0; nTab<nTabCount; nTab++)
+            aDocument.SetCalcNotification(nTab);
+
     // CalcAll doesn't broadcast value changes, so SC_HINT_CALCALL is broadcasted globally
     // in addition to SFX_HINT_DATACHANGED.
     aDocument.BroadcastUno( SfxSimpleHint( SC_HINT_CALCALL ) );
@@ -1298,8 +1304,7 @@ void ScDocShell::DoHardRecalc( BOOL /* bApi */ )
 
     // use hard recalc also to disable stream-copying of all sheets
     // (somewhat consistent with charts)
-    SCTAB nTabCount = aDocument.GetTableCount();
-    for (SCTAB nTab=0; nTab<nTabCount; nTab++)
+    for (nTab=0; nTab<nTabCount; nTab++)
         if (aDocument.IsStreamValid(nTab))
             aDocument.SetStreamValid(nTab, FALSE);
 
@@ -1484,12 +1489,12 @@ BOOL ScDocShell::AdjustPrintZoom( const ScRange& rRange )
         SCROW nEndRow = rRange.aEnd.Row();
         if ( pRepeatRow && nStartRow >= pRepeatRow->aStart.Row() )
         {
-            nBlkTwipsY += aDocument.FastGetRowHeight( pRepeatRow->aStart.Row(),
+            nBlkTwipsY += aDocument.GetRowHeight( pRepeatRow->aStart.Row(),
                     pRepeatRow->aEnd.Row(), nTab );
             if ( nStartRow <= pRepeatRow->aEnd.Row() )
                 nStartRow = pRepeatRow->aEnd.Row() + 1;
         }
-        nBlkTwipsY += aDocument.FastGetRowHeight( nStartRow, nEndRow, nTab );
+        nBlkTwipsY += aDocument.GetRowHeight( nStartRow, nEndRow, nTab );
 
         Size aPhysPage;
         long nHdr, nFtr;
diff --git a/sc/source/ui/docshell/docsh5.cxx b/sc/source/ui/docshell/docsh5.cxx
index 061ece5efc90..84979e3dbcdf 100644
--- a/sc/source/ui/docshell/docsh5.cxx
+++ b/sc/source/ui/docshell/docsh5.cxx
@@ -65,6 +65,11 @@
 #include "sc.hrc"
 #include "waitoff.hxx"
 #include "sizedev.hxx"
+#include <basic/sbstar.hxx>
+#include <basic/basmgr.hxx>
+
+// defined in docfunc.cxx
+void VBA_InsertModule( ScDocument& rDoc, SCTAB nTab, String& sModuleName, String& sModuleSource );
 
 // ---------------------------------------------------------------------------
 
@@ -866,6 +871,8 @@ BOOL ScDocShell::MoveTable( SCTAB nSrcTab, SCTAB nDestTab, BOOL bCopy, BOOL bRec
         if (bRecord)
             aDocument.BeginDrawUndo();          // drawing layer must do its own undo actions
 
+        String sSrcCodeName;
+        aDocument.GetCodeName( nSrcTab, sSrcCodeName );
         if (!aDocument.CopyTab( nSrcTab, nDestTab ))
         {
             //! EndDrawUndo?
@@ -889,8 +896,38 @@ BOOL ScDocShell::MoveTable( SCTAB nSrcTab, SCTAB nDestTab, BOOL bCopy, BOOL bRec
                 GetUndoManager()->AddUndoAction(
                         new ScUndoCopyTab( this, aSrcList, aDestList ) );
             }
-        }
 
+            BOOL bVbaEnabled = aDocument.IsInVBAMode();
+                        if ( bVbaEnabled )
+                        {
+                StarBASIC* pStarBASIC = GetBasic();
+                            String aLibName( RTL_CONSTASCII_USTRINGPARAM( "Standard" ) );
+                            if ( GetBasicManager()->GetName().Len() > 0 )
+                            {
+                                aLibName = GetBasicManager()->GetName();
+                                pStarBASIC = GetBasicManager()->GetLib( aLibName );
+                            }
+                            SCTAB nTabToUse = nDestTab;
+                            if ( nDestTab == SC_TAB_APPEND )
+                                nTabToUse = aDocument.GetMaxTableNumber() - 1;
+                            String sCodeName;
+                            String sSource;
+                            com::sun::star::uno::Reference< com::sun::star::script::XLibraryContainer > xLibContainer = GetBasicContainer();
+                            com::sun::star::uno::Reference< com::sun::star::container::XNameContainer > xLib;
+                            if( xLibContainer.is() )
+                            {
+                                com::sun::star::uno::Any aLibAny = xLibContainer->getByName( aLibName );
+                                aLibAny >>= xLib;
+                            }
+                            if( xLib.is() )
+                            {
+                                rtl::OUString sRTLSource;
+                                xLib->getByName( sSrcCodeName ) >>= sRTLSource;
+                                sSource = sRTLSource;
+                            }
+                            VBA_InsertModule( aDocument, nTabToUse, sCodeName, sSource );
+                        }
+                }
         Broadcast( ScTablesHint( SC_TAB_COPIED, nSrcTab, nDestTab ) );
     }
     else
diff --git a/sc/source/ui/docshell/docsh8.cxx b/sc/source/ui/docshell/docsh8.cxx
index b761dd75d9df..cb9d625da70c 100644
--- a/sc/source/ui/docshell/docsh8.cxx
+++ b/sc/source/ui/docshell/docsh8.cxx
@@ -597,7 +597,7 @@ void lcl_GetColumnTypes( ScDocShell& rDocShell,
         else if ( nDbType == sdbc::DataType::DECIMAL )
         {   // maximale Feldbreite und Nachkommastellen bestimmen
             xub_StrLen nLen;
-            USHORT nPrec;
+            sal_uInt16 nPrec;
             nLen = pDoc->GetMaxNumberStringLen( nPrec, nTab, nCol,
                 nFirstDataRow, nLastRow );
             // dBaseIII Limit Nachkommastellen: 15
diff --git a/sc/source/ui/docshell/externalrefmgr.cxx b/sc/source/ui/docshell/externalrefmgr.cxx
index e7b04afa9c4f..0569e95605b1 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)
-{
-    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)
+ScExternalRefManager::LinkListener::~LinkListener()
 {
-    // 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/docshell/impex.cxx b/sc/source/ui/docshell/impex.cxx
index 46b6b7af3a63..a17279f409a9 100644
--- a/sc/source/ui/docshell/impex.cxx
+++ b/sc/source/ui/docshell/impex.cxx
@@ -1441,7 +1441,7 @@ BOOL ScImportExport::Doc2Text( SvStream& rStrm )
 
     for (nRow = nStartRow; nRow <= nEndRow; nRow++)
     {
-        if (bIncludeFiltered || !pDoc->IsFiltered( nRow, aRange.aStart.Tab() ))
+        if (bIncludeFiltered || !pDoc->RowFiltered( nRow, aRange.aStart.Tab() ))
         {
             for (nCol = nStartCol; nCol <= nEndCol; nCol++)
             {
diff --git a/sc/source/ui/docshell/olinefun.cxx b/sc/source/ui/docshell/olinefun.cxx
index 2f42c77ed5f4..822c74800ab2 100644
--- a/sc/source/ui/docshell/olinefun.cxx
+++ b/sc/source/ui/docshell/olinefun.cxx
@@ -420,7 +420,7 @@ BOOL ScOutlineDocFunc::SelectLevel( SCTAB nTab, BOOL bColumns, USHORT nLevel,
             if ( bColumns )
                 pDoc->ShowCol( static_cast<SCCOL>(i), nTab, bShow );
             else
-                if ( !bShow || !pDoc->IsFiltered( i,nTab ) )
+                if ( !bShow || !pDoc->RowFiltered( i,nTab ) )
                     pDoc->ShowRow( i, nTab, bShow );
         }
     }
@@ -518,7 +518,7 @@ BOOL ScOutlineDocFunc::ShowMarkedOutlines( const ScRange& rRange, BOOL bRecord,
             }
         }
         for ( i=nMin; i<=nMax; i++ )
-            if ( !pDoc->IsFiltered( i,nTab ) )              // weggefilterte nicht einblenden
+            if ( !pDoc->RowFiltered( i,nTab ) )             // weggefilterte nicht einblenden
                 pDoc->ShowRow( i, nTab, TRUE );
 
         pDoc->UpdatePageBreaks( nTab );
@@ -678,7 +678,7 @@ BOOL ScOutlineDocFunc::ShowOutline( SCTAB nTab, BOOL bColumns, USHORT nLevel, US
         if ( bColumns )
             pDoc->ShowCol( static_cast<SCCOL>(i), nTab, TRUE );
         else
-            if ( !pDoc->IsFiltered( i,nTab ) )              // weggefilterte nicht einblenden
+            if ( !pDoc->RowFiltered( i,nTab ) )             // weggefilterte nicht einblenden
                 pDoc->ShowRow( i, nTab, TRUE );
     }
 
@@ -701,6 +701,7 @@ BOOL ScOutlineDocFunc::ShowOutline( SCTAB nTab, BOOL bColumns, USHORT nLevel, US
 
     pArray->SetVisibleBelow( nLevel, nEntry, TRUE, TRUE );
 
+    pDoc->InvalidatePageBreaks(nTab);
     pDoc->UpdatePageBreaks( nTab );
 
     if (bPaint)
@@ -766,6 +767,7 @@ BOOL ScOutlineDocFunc::HideOutline( SCTAB nTab, BOOL bColumns, USHORT nLevel, US
 
     pArray->SetVisibleBelow( nLevel, nEntry, FALSE );
 
+    pDoc->InvalidatePageBreaks(nTab);
     pDoc->UpdatePageBreaks( nTab );
 
     if (bPaint)
diff --git a/sc/source/ui/docshell/tablink.cxx b/sc/source/ui/docshell/tablink.cxx
index 4d18f9f18c37..697b39052b9f 100644
--- a/sc/source/ui/docshell/tablink.cxx
+++ b/sc/source/ui/docshell/tablink.cxx
@@ -485,6 +485,11 @@ BOOL ScDocumentLoader::GetFilterName( const String& rFileName,
         pDocSh = SfxObjectShell::GetNext( *pDocSh, &aScType );
     }
 
+    INetURLObject aUrl( rFileName );
+    INetProtocol eProt = aUrl.GetProtocol();
+    if ( eProt == INET_PROT_NOT_VALID )         // invalid URL?
+        return FALSE;                           // abort without creating a medium
+
     //  Filter-Detection
 
     const SfxFilter* pSfxFilter = NULL;