Work around another Excel bug with incorrect shared formula range.

Take the start row position from the preceding formula record, rather
than believing what's in the shared formula record. The latter can be
wrong, and can be wrong often.

Change-Id: I3a4da110727a7719e5f8cb3e6250c0e1bef04c64

5 files changed, 151 insertions, 42 deletions

diff --git a/sc/source/filter/excel/excform.cxx b/sc/source/filter/excel/excform.cxx
index b557de542741..4c49461d8ed8 100644
--- a/sc/source/filter/excel/excform.cxx
+++ b/sc/source/filter/excel/excform.cxx
@@ -102,6 +102,9 @@ void ImportExcel::Formula4()
 void ImportExcel::Formula(
     const XclAddress& rXclPos, sal_uInt16 nXF, sal_uInt16 nFormLen, double fCurVal, bool bShrFmla)
 {
+    if (!nFormLen)
+        return;
+
     ScAddress aScPos( ScAddress::UNINITIALIZED );
     if (!GetAddressConverter().ConvertAddress(aScPos, rXclPos, GetCurrScTab(), true))
         // Conversion failed.
@@ -116,22 +119,39 @@ void ImportExcel::Formula(
     if (bShrFmla)
     {
         // This is a shared formula. Get the token array from the shared formula pool.
-        ScFormulaCellGroupRef xGroup = pFormConv->GetSharedFormula(maStrm, aScPos.Col(), nFormLen);
-        if (xGroup)
+        SCCOL nSharedCol;
+        SCROW nSharedRow;
+        if (pFormConv->ReadSharedFormulaPosition(maStrm, nSharedCol, nSharedRow))
         {
-            if (xGroup->mnStart == aScPos.Row())
-                // Generate code for the top cell only.
-                xGroup->compileCode(rDoc.getDoc(), aScPos, formula::FormulaGrammar::GRAM_DEFAULT);
-
-            ScFormulaCell* pCell = new ScFormulaCell(pD, aScPos, xGroup);
-            rDoc.getDoc().EnsureTable(aScPos.Tab());
-            rDoc.setFormulaCell(aScPos, pCell);
-            xGroup->mnLength = aScPos.Row() - xGroup->mnStart + 1;
-            pCell->SetNeedNumberFormat(false);
-            if (!rtl::math::isNan(fCurVal))
-                pCell->SetResultDouble(fCurVal);
-
-            GetXFRangeBuffer().SetXF(aScPos, nXF);
+            ScAddress aRefPos(aScPos.Col(), nSharedRow, GetCurrScTab());
+            ScFormulaCellGroupRef xGroup = pFormConv->GetSharedFormula(aRefPos);
+            if (xGroup)
+            {
+                // Make sure the this one follows immediately below another shared formula cell.
+                LastFormula* pLast = GetLastFormula(aScPos.Col());
+                if (pLast && pLast->mpCell && pLast->mnRow == (aScPos.Row()-1))
+                {
+                    ScFormulaCell* pCell = new ScFormulaCell(pD, aScPos, xGroup);
+                    rDoc.getDoc().EnsureTable(aScPos.Tab());
+                    rDoc.setFormulaCell(aScPos, pCell);
+                    xGroup->mnLength = aScPos.Row() - xGroup->mnStart + 1;
+                    pCell->SetNeedNumberFormat(false);
+                    if (!rtl::math::isNan(fCurVal))
+                        pCell->SetResultDouble(fCurVal);
+
+                    GetXFRangeBuffer().SetXF(aScPos, nXF);
+                    SetLastFormula(aScPos.Col(), aScPos.Row(), fCurVal, nXF, pCell);
+                }
+                else
+                    fprintf(stdout, "ImportExcel::Formula:   what!?\n");
+            }
+            else
+            {
+                // Shared formula not found even though it's clearly a shared formula.
+                // The cell will be created in the following shared formula
+                // record.
+                SetLastFormula(aScPos.Col(), aScPos.Row(), fCurVal, nXF, NULL);
+            }
             return;
         }
     }
@@ -158,6 +178,7 @@ void ImportExcel::Formula(
         pCell = new ScFormulaCell(&rDoc.getDoc(), aScPos, pResult);
         rDoc.getDoc().EnsureTable(aScPos.Tab());
         rDoc.setFormulaCell(aScPos, pCell);
+        SetLastFormula(aScPos.Col(), aScPos.Row(), fCurVal, nXF, pCell);
     }
     else
     {
@@ -1692,30 +1713,30 @@ const ScTokenArray* ExcelToSc::GetBoolErr( XclBoolError eType )
     return pErgebnis;
 }
 
-ScFormulaCellGroupRef ExcelToSc::GetSharedFormula( XclImpStream& aIn, SCCOL nCol, sal_Size nFormulaLen )
+bool ExcelToSc::ReadSharedFormulaPosition( XclImpStream& rStrm, SCCOL& rCol, SCROW& rRow )
 {
-    if (!nFormulaLen)
-        return ScFormulaCellGroupRef();
-
-    aIn.PushPosition();
+    rStrm.PushPosition();
 
     sal_uInt8 nOp;
-    aIn >> nOp;
+    rStrm >> nOp;
 
     if (nOp != 0x01)   // must be PtgExp token.
     {
-        aIn.PopPosition();
-        return ScFormulaCellGroupRef();
+        rStrm.PopPosition();
+        return false;
     }
 
-    sal_uInt16 nLeftCol, nRow;
-    aIn >> nRow >> nLeftCol;
-
-    ScAddress aRefPos(nCol, nRow, GetCurrScTab());
-    ScFormulaCellGroupRef xGroup = GetOldRoot().pShrfmlaBuff->Find(aRefPos);
+    sal_uInt16 nRow, nCol;
+    rStrm >> nRow >> nCol;
+    rStrm.PopPosition();
+    rCol = nCol;
+    rRow = nRow;
+    return true;
+}
 
-    aIn.PopPosition();
-    aIn.Ignore(nFormulaLen);
+ScFormulaCellGroupRef ExcelToSc::GetSharedFormula( const ScAddress& rRefPos )
+{
+    ScFormulaCellGroupRef xGroup = GetOldRoot().pShrfmlaBuff->Find(rRefPos);
     return xGroup;
 }
 
diff --git a/sc/source/filter/excel/impop.cxx b/sc/source/filter/excel/impop.cxx
index 16c4ac52e71d..a06aac110735 100644
--- a/sc/source/filter/excel/impop.cxx
+++ b/sc/source/filter/excel/impop.cxx
@@ -114,8 +114,10 @@ ImportExcel::ImportExcel( XclImpRootData& rImpData, SvStream& rStrm ):
     maStrm( rStrm, GetRoot() ),
     aIn( maStrm ),
     maScOleSize( ScAddress::INITIALIZE_INVALID ),
+    mpLastFormula(NULL),
     mnLastRefIdx( 0 ),
     mnIxfeIndex( 0 ),
+    mnLastRecId(0),
     mbBiff2HasXfs(false),
     mbBiff2HasXfsValid(false),
     mbRunCLKernelThread(true)
@@ -165,6 +167,31 @@ ImportExcel::~ImportExcel( void )
     delete pFormConv;
 }
 
+void ImportExcel::SetLastFormula( SCCOL nCol, SCROW nRow, double fVal, sal_uInt16 nXF, ScFormulaCell* pCell )
+{
+    LastFormulaMapType::iterator it = maLastFormulaCells.find(nCol);
+    if (it == maLastFormulaCells.end())
+    {
+        std::pair<LastFormulaMapType::iterator, bool> r =
+            maLastFormulaCells.insert(
+                LastFormulaMapType::value_type(nCol, LastFormula()));
+        it = r.first;
+    }
+
+    it->second.mnCol = nCol;
+    it->second.mnRow = nRow;
+    it->second.mpCell = pCell;
+    it->second.mfValue = fVal;
+    it->second.mnXF = nXF;
+
+    mpLastFormula = &it->second;
+}
+
+ImportExcel::LastFormula* ImportExcel::GetLastFormula( SCCOL nCol )
+{
+    LastFormulaMapType::iterator it = maLastFormulaCells.find(nCol);
+    return it == maLastFormulaCells.end() ? NULL : &it->second;
+}
 
 void ImportExcel::ReadFileSharing()
 {
@@ -815,6 +842,21 @@ void ImportExcel::Standardwidth( void )
 
 void ImportExcel::Shrfmla( void )
 {
+    switch (mnLastRecId)
+    {
+        case EXC_ID2_FORMULA:
+        case EXC_ID3_FORMULA:
+        case EXC_ID4_FORMULA:
+            // This record MUST immediately follow a FORMULA record.
+        break;
+        default:
+            return;
+    }
+
+    if (!mpLastFormula)
+        // The last FORMULA record should have left this data.
+        return;
+
     sal_uInt16              nFirstRow, nLastRow, nLenExpr;
     sal_uInt8               nFirstCol, nLastCol;
 
@@ -829,13 +871,37 @@ void ImportExcel::Shrfmla( void )
     pFormConv->Reset();
     pFormConv->Convert( pErgebnis, maStrm, nLenExpr, true, FT_SharedFormula );
 
-
     OSL_ENSURE( pErgebnis, "+ImportExcel::Shrfmla(): ScTokenArray is NULL!" );
 
-    pExcRoot->pShrfmlaBuff->Store( ScRange( static_cast<SCCOL>(nFirstCol),
-                static_cast<SCROW>(nFirstRow), GetCurrScTab(),
-                static_cast<SCCOL>(nLastCol), static_cast<SCROW>(nLastRow),
-                GetCurrScTab()), *pErgebnis );
+    // The range in this record can be erroneous especially the row range.
+    // Use the row from the last FORMULA record as the start row.  The end row
+    // will be adjusted by the formula cells that follow.
+    SCCOL nCol1 = nFirstCol;
+    SCCOL nCol2 = nLastCol;
+    SCROW nRow1 = mpLastFormula->mnRow;
+
+    pExcRoot->pShrfmlaBuff->Store(
+        ScRange(nCol1, nRow1, GetCurrScTab(), nCol2, nRow1, GetCurrScTab()), *pErgebnis);
+
+    // Create formula cell for the last formula record.
+
+    ScAddress aPos(nCol1, nRow1, GetCurrScTab());
+    ScFormulaCellGroupRef xGroup = pExcRoot->pShrfmlaBuff->Find(aPos);
+    if (xGroup)
+    {
+        ScDocumentImport& rDoc = GetDocImport();
+        xGroup->compileCode(rDoc.getDoc(), aPos, formula::FormulaGrammar::GRAM_DEFAULT);
+
+        ScFormulaCell* pCell = new ScFormulaCell(pD, aPos, xGroup);
+        rDoc.getDoc().EnsureTable(aPos.Tab());
+        rDoc.setFormulaCell(aPos, pCell);
+        pCell->SetNeedNumberFormat(false);
+        if (!rtl::math::isNan(mpLastFormula->mfValue))
+            pCell->SetResultDouble(mpLastFormula->mfValue);
+
+        GetXFRangeBuffer().SetXF(aPos, mpLastFormula->mnXF);
+        mpLastFormula->mpCell = pCell;
+    }
 }
 
 
@@ -1181,6 +1247,8 @@ void ImportExcel::NeueTabelle( void )
     }
 
     pExcRoot->pShrfmlaBuff->Clear();
+    maLastFormulaCells.clear();
+    mpLastFormula = NULL;
 
     InitializeTable( nTab );
 
diff --git a/sc/source/filter/excel/read.cxx b/sc/source/filter/excel/read.cxx
index 14396f617eb2..610db5513b67 100644
--- a/sc/source/filter/excel/read.cxx
+++ b/sc/source/filter/excel/read.cxx
@@ -630,7 +630,6 @@ FltError ImportExcel::Read( void )
                         case 0x0223: Externname34(); break; // EXTERNNAME   [  34 ]
                         case 0x0225: Defrowheight345();break;//DEFAULTROWHEI[  345]
                         case 0x023E: rTabViewSett.ReadWindow2( maStrm, false );break;
-                        case 0x04BC: Shrfmla(); break;      // SHRFMLA      [    5]
                     }
                 }
             }
@@ -654,6 +653,7 @@ FltError ImportExcel::Read( void )
                     case EXC_ID2_FORMULA:
                     case EXC_ID3_FORMULA:
                     case EXC_ID4_FORMULA:       Formula25(); break;
+                    case EXC_ID_SHRFMLA: Shrfmla(); break;
                     case 0x0A:  Eof(); eAkt = Z_Biff5E;                 break;
                     case 0x14:
                     case 0x15:  rPageSett.ReadHeaderFooter( maStrm );   break;
@@ -833,7 +833,9 @@ FltError ImportExcel8::Read( void )
     std::vector<OUString> aCodeNames;
     std::vector < SCTAB > nTabsWithNoCodeName;
 
-    while( eAkt != EXC_STATE_END )
+    sal_uInt16 nRecId = 0;
+
+    for (; eAkt != EXC_STATE_END; mnLastRecId = nRecId)
     {
         if( eAkt == EXC_STATE_BEFORE_SHEET )
         {
@@ -937,7 +939,7 @@ FltError ImportExcel8::Read( void )
         if( eAkt != EXC_STATE_SHEET_PRE && eAkt != EXC_STATE_GLOBALS_PRE )
             pProgress->ProgressAbs( nProgressBaseSize + aIn.GetSvStreamPos() - nProgressBasePos );
 
-        sal_uInt16 nRecId = aIn.GetRecId();
+        nRecId = aIn.GetRecId();
 
         /*  #i39464# Ignore records between USERSVIEWBEGIN and USERSVIEWEND
             completely (user specific view settings). Otherwise view settings
@@ -1145,7 +1147,6 @@ FltError ImportExcel8::Read( void )
                     case EXC_ID2_ARRAY:
                     case EXC_ID3_ARRAY: Array34(); break;      // ARRAY        [  34    ]
                     case 0x0225: Defrowheight345();break;//DEFAULTROWHEI[  345   ]
-                    case EXC_ID_SHRFMLA: Shrfmla(); break;      // SHRFMLA      [    5   ]
                     case 0x0867: SheetProtection(); break; // SHEETPROTECTION
                 }
             }
@@ -1179,6 +1180,7 @@ FltError ImportExcel8::Read( void )
                     case EXC_ID2_FORMULA:
                     case EXC_ID3_FORMULA:
                     case EXC_ID4_FORMULA:       Formula25();            break;
+                    case EXC_ID_SHRFMLA:        Shrfmla();              break;
                     case 0x000C:    Calccount();            break;  // CALCCOUNT
                     case 0x0010:    Delta();                break;  // DELTA
                     case 0x0011:    Iteration();            break;  // ITERATION
diff --git a/sc/source/filter/inc/excform.hxx b/sc/source/filter/inc/excform.hxx
index 0e2004c9d9e3..11254a0914e0 100644
--- a/sc/source/filter/inc/excform.hxx
+++ b/sc/source/filter/inc/excform.hxx
@@ -63,7 +63,9 @@ public:
 
     void                GetDummy( const ScTokenArray*& );
     const ScTokenArray* GetBoolErr( XclBoolError );
-    ScFormulaCellGroupRef GetSharedFormula( XclImpStream& rStrm, SCCOL nCol, sal_Size nFormulaLen );
+
+    bool ReadSharedFormulaPosition( XclImpStream& rStrm, SCCOL& rCol, SCROW& rRow );
+    ScFormulaCellGroupRef GetSharedFormula( const ScAddress& rRefPos );
 
     static void         SetError( ScFormulaCell& rCell, const ConvErr eErr );
 
diff --git a/sc/source/filter/inc/imp_op.hxx b/sc/source/filter/inc/imp_op.hxx
index 6e44f31a310e..7c583959625a 100644
--- a/sc/source/filter/inc/imp_op.hxx
+++ b/sc/source/filter/inc/imp_op.hxx
@@ -35,7 +35,7 @@
 
 #include <boost/shared_ptr.hpp>
 #include <boost/ptr_container/ptr_vector.hpp>
-
+#include <boost/unordered_map.hpp>
 
 class SvStream;
 
@@ -82,6 +82,16 @@ private:
 class ImportExcel : public ImportTyp, protected XclImpRoot
 {
 protected:
+    struct LastFormula
+    {
+        SCCOL mnCol;
+        SCROW mnRow;
+        double mfValue;
+        sal_uInt16 mnXF;
+        ScFormulaCell* mpCell;
+    };
+    typedef boost::unordered_map<SCCOL, LastFormula> LastFormulaMapType;
+
     rtl::Reference<sc::CLBuildKernelThread> mxCLKernelThread;
 
     static const double     fExcToTwips;        // Umrechnung 1/256 Zeichen -> Twips
@@ -104,11 +114,14 @@ protected:
     typedef boost::ptr_vector< XclImpOutlineDataBuffer > XclImpOutlineListBuffer;
     XclImpOutlineListBuffer* pOutlineListBuffer;
 
+    LastFormulaMapType maLastFormulaCells; // Keep track of last formula cells in each column.
+    LastFormula* mpLastFormula;
+
     sal_Int16               mnLastRefIdx;
     sal_uInt16              mnIxfeIndex;        /// Current XF identifier from IXFE record.
+    sal_uInt16 mnLastRecId;
 
     SCTAB                   nBdshtTab;          // Counter fuer Boundsheet
-    ScFormulaCell*          pLastFormCell;      // fuer String-Records
 
     sal_Bool                    bTabTruncated;      // wenn Bereichsueberschreitung zum
                                                 //  Abschneiden von Zellen fuehrt
@@ -117,6 +130,9 @@ protected:
     bool mbBiff2HasXfsValid:1; /// False = mbBiff2HasXfs is undetermined yet.
     bool mbRunCLKernelThread:1;
 
+    void SetLastFormula( SCCOL nCol, SCROW nRow, double fVal, sal_uInt16 nXF, ScFormulaCell* pCell );
+    LastFormula* GetLastFormula( SCCOL nCol );
+
     // Record-Funktionen
     void                    ReadFileSharing();