New feature to allow converting formula cells to static values.

You used to have to convert formula cells to static values by copying
them and pasting them as values onto the original place.  Why not just
allow converting them in place?

This is something I've always wanted to implement.

24 files changed, 692 insertions, 3 deletions

diff --git a/officecfg/registry/data/org/openoffice/Office/UI/CalcCommands.xcu b/officecfg/registry/data/org/openoffice/Office/UI/CalcCommands.xcu
index a0f7e86bb6d9..d81f35b32901 100644
--- a/officecfg/registry/data/org/openoffice/Office/UI/CalcCommands.xcu
+++ b/officecfg/registry/data/org/openoffice/Office/UI/CalcCommands.xcu
@@ -1709,6 +1709,14 @@
           <value>1</value>
         </prop>
       </node>
+      <node oor:name=".uno:ConvertFormulaToValue" oor:op="replace">
+        <prop oor:name="Label" oor:type="xs:string">
+          <value xml:lang="en-US">Convert Formula to Value</value>
+        </prop>
+        <prop oor:name="Properties" oor:type="xs:int">
+          <value>1</value>
+        </prop>
+      </node>
     </node>
     <node oor:name="Popups">
       <node oor:name=".uno:AuditMenu" oor:op="replace">
diff --git a/sc/Library_sc.mk b/sc/Library_sc.mk
index 639946a9b786..55988a153df5 100644
--- a/sc/Library_sc.mk
+++ b/sc/Library_sc.mk
@@ -514,6 +514,7 @@ $(eval $(call gb_Library_add_exception_objects,sc,\
     sc/source/ui/undo/undoblk3 \
     sc/source/ui/undo/undocell \
     sc/source/ui/undo/undocell2 \
+    sc/source/ui/undo/undoconvert \
     sc/source/ui/undo/undodat \
     sc/source/ui/undo/undodraw \
     sc/source/ui/undo/undoolk \
diff --git a/sc/inc/cellvalues.hxx b/sc/inc/cellvalues.hxx
index 4f4c50fab4fd..b545fcdd9115 100644
--- a/sc/inc/cellvalues.hxx
+++ b/sc/inc/cellvalues.hxx
@@ -11,13 +11,29 @@
 #define INCLUDED_SC_INC_CELLVALUES_HXX
 
 #include "address.hxx"
+#include <global.hxx>
 
 class ScColumn;
 
+namespace svl {
+
+class SharedString;
+
+}
+
 namespace sc {
 
 struct CellValuesImpl;
 
+struct CellValueSpan
+{
+    SCROW mnRow1;
+    SCROW mnRow2;
+    CellType meType;
+
+    CellValueSpan( SCROW nRow1, SCROW nRow2, CellType eType );
+};
+
 /**
  * Think of this as a mini-ScColumn like storage that only stores cell
  * values in a column.
@@ -45,16 +61,60 @@ public:
 
     void transferTo( ScColumn& rCol, SCROW nRow );
     void copyTo( ScColumn& rCol, SCROW nRow ) const;
+    void swapNonEmpty( ScColumn& rCol );
 
     void assign( const std::vector<double>& rVals );
 
     size_t size() const;
 
+    void reset( size_t nSize );
+    void setValue( size_t nRow, double fVal );
+    void setValue( size_t nRow, const svl::SharedString& rStr );
+
+    void swap( CellValues& r );
+
+    std::vector<CellValueSpan> getNonEmptySpans() const;
+
 private:
     void copyCellsTo( ScColumn& rCol, SCROW nRow ) const;
     void copyCellTextAttrsTo( ScColumn& rCol, SCROW nRow ) const;
 };
 
+/**
+ * Stores cell values for multiple tables.
+ */
+class TableValues
+{
+    struct Impl;
+
+    Impl* mpImpl;
+
+    TableValues( const TableValues& ); // disabled
+    TableValues& operator= ( const TableValues& ); // disabled
+
+public:
+
+    TableValues();
+    TableValues( const ScRange& rRange );
+    ~TableValues();
+
+    const ScRange& getRange() const;
+
+    /**
+     * Swap the entire column.
+     */
+    void swap( SCTAB nTab, SCCOL nCol, CellValues& rColValue );
+
+    /**
+     * Swap non-empty blocks with the column storage.
+     */
+    void swapNonEmpty( SCTAB nTab, SCCOL nCol, ScColumn& rCol );
+
+    std::vector<CellValueSpan> getNonEmptySpans( SCTAB nTab, SCCOL nCol ) const;
+
+    void swap( TableValues& rOther );
+};
+
 }
 
 #endif
diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index 893ef1391e68..c694513ad491 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -60,6 +60,7 @@ class EditTextIterator;
 struct NoteEntry;
 class DocumentStreamAccess;
 class CellValues;
+class TableValues;
 struct RowSpan;
 class RowHeightContext;
 class CompileFormulaContext;
@@ -153,6 +154,7 @@ friend class sc::SingleColumnSpanSet;
 friend class sc::ColumnSpanSet;
 friend class sc::EditTextIterator;
 friend class sc::CellValues;
+friend class sc::TableValues;
 
     ScColumn(const ScColumn&); // disabled
     ScColumn& operator= (const ScColumn&); // disabled
@@ -601,6 +603,11 @@ public:
 
     void TransferCellValuesTo( SCROW nRow, size_t nLen, sc::CellValues& rDest );
     void CopyCellValuesFrom( SCROW nRow, const sc::CellValues& rSrc );
+    void ConvertFormulaToValue(
+        sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2, sc::TableValues* pUndo );
+
+    void SwapNonEmpty(
+        sc::TableValues& rValues, sc::StartListeningContext& rStartCxt, sc::EndListeningContext& rEndCxt );
 
 #if DEBUG_COLUMN_STORAGE
     void DumpFormulaGroups() const;
diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 1d14b9f0382b..41fcffbd5f27 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -75,6 +75,7 @@ struct FormulaGroupContext;
 class DocumentStreamAccess;
 class DocumentLinkManager;
 class CellValues;
+class TableValues;
 class RowHeightContext;
 struct SetFormulaDirtyContext;
 class RefMovedHint;
@@ -2110,6 +2111,10 @@ public:
     void SetCalcConfig( const ScCalcConfig& rConfig );
     const ScCalcConfig& GetCalcConfig() const { return maCalcConfig; }
 
+    void ConvertFormulaToValue( const ScRange& rRange, sc::TableValues* pUndo );
+
+    void SwapNonEmpty( sc::TableValues& rValues );
+
 private:
 
     /**
diff --git a/sc/inc/formulacell.hxx b/sc/inc/formulacell.hxx
index b765ff07b63f..475bea34c9a2 100644
--- a/sc/inc/formulacell.hxx
+++ b/sc/inc/formulacell.hxx
@@ -300,6 +300,7 @@ public:
     sal_uInt16      GetRawError();  // don't interpret, just return code or result error
     bool GetErrorOrValue( sal_uInt16& rErr, double& rVal );
     sc::FormulaResultValue GetResult();
+    sc::FormulaResultValue GetResult() const;
     sal_uInt8       GetMatrixFlag() const { return cMatrixFlag;}
     ScTokenArray* GetCode() { return pCode;}
     const ScTokenArray* GetCode() const { return pCode;}
diff --git a/sc/inc/globstr.hrc b/sc/inc/globstr.hrc
index 0b2a6606b2d7..eab98cc892bc 100644
--- a/sc/inc/globstr.hrc
+++ b/sc/inc/globstr.hrc
@@ -693,8 +693,9 @@
 #define STR_PRINT_PREVIEW_EMPTY_RANGE   530
 
 #define STR_UNDO_CONDFORMAT             531
+#define STR_UNDO_FORMULA_TO_VALUE       532
 
-#define SC_GLOBSTR_STR_COUNT            532     /**< the count of permanently resident strings */
+#define SC_GLOBSTR_STR_COUNT            533     /**< the count of permanently resident strings */
 
 #endif
 
diff --git a/sc/inc/sc.hrc b/sc/inc/sc.hrc
index 420d1492497a..2d7d228762e9 100644
--- a/sc/inc/sc.hrc
+++ b/sc/inc/sc.hrc
@@ -275,8 +275,9 @@
 
 // functions
 
-#define SID_OPEN_CALC           (SC_FUNCTION_START + 4)
-#define FILE_MENU_END           (SC_FUNCTION_START + 20)
+#define SID_OPEN_CALC                   (SC_FUNCTION_START + 4)
+#define SID_CONVERT_FORMULA_TO_VALUE    (SC_FUNCTION_START + 5)
+#define FILE_MENU_END                   (SC_FUNCTION_START + 20)
 
 #define EDIT_MENU_START         (FILE_MENU_END)
 #define FID_DELETE_CELL         (EDIT_MENU_START + 2)
diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx
index fb5d6f9dcada..e0c6e2170b52 100644
--- a/sc/inc/table.hxx
+++ b/sc/inc/table.hxx
@@ -69,6 +69,7 @@ struct RefUpdateMoveTabContext;
 struct NoteEntry;
 class DocumentStreamAccess;
 class CellValues;
+class TableValues;
 class RowHeightContext;
 class CompileFormulaContext;
 struct SetFormulaDirtyContext;
@@ -934,6 +935,13 @@ public:
     void TransferCellValuesTo( SCCOL nCol, SCROW nRow, size_t nLen, sc::CellValues& rDest );
     void CopyCellValuesFrom( SCCOL nCol, SCROW nRow, const sc::CellValues& rSrc );
 
+    void ConvertFormulaToValue(
+        sc::EndListeningContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
+        sc::TableValues* pUndo );
+
+    void SwapNonEmpty(
+        sc::TableValues& rValues, sc::StartListeningContext& rStartCxt, sc::EndListeningContext& rEndCxt );
+
 #if DEBUG_COLUMN_STORAGE
     void DumpFormulaGroups( SCCOL nCol ) const;
 #endif
diff --git a/sc/sdi/cellsh.sdi b/sc/sdi/cellsh.sdi
index 8447768121b2..9bc9444df90f 100644
--- a/sc/sdi/cellsh.sdi
+++ b/sc/sdi/cellsh.sdi
@@ -218,6 +218,8 @@ interface CellSelection
     SID_HANGUL_HANJA_CONVERSION [ ExecMethod = ExecuteEdit; StateMethod = GetState; ]
         SID_CHINESE_CONVERSION          [ ExecMethod = ExecuteEdit; StateMethod = GetState; ]
 
+    SID_CONVERT_FORMULA_TO_VALUE [ ExecMethod = ExecuteEdit; StateMethod = GetBlockState; ]
+
     SID_TRANSLITERATE_SENTENCE_CASE [ ExecMethod = ExecuteTrans; StateMethod = GetBlockState; ]
     SID_TRANSLITERATE_TITLE_CASE    [ ExecMethod = ExecuteTrans; StateMethod = GetBlockState; ]
     SID_TRANSLITERATE_TOGGLE_CASE   [ ExecMethod = ExecuteTrans; StateMethod = GetBlockState; ]
diff --git a/sc/sdi/scalc.sdi b/sc/sdi/scalc.sdi
index 1835459e540a..f9c6cc770d04 100644
--- a/sc/sdi/scalc.sdi
+++ b/sc/sdi/scalc.sdi
@@ -8753,6 +8753,33 @@ SfxVoidItem OpenFromCalc SID_OPEN_CALC
     GroupId = GID_OPTIONS;
 ]
 
+SfxVoidItem ConvertFormulaToValue SID_CONVERT_FORMULA_TO_VALUE
+()
+[
+    /* flags: */
+    AutoUpdate = FALSE,
+    Cachable = Cachable,
+    FastCall = FALSE,
+    HasCoreId = FALSE,
+    HasDialog = FALSE,
+    ReadOnlyDoc = TRUE,
+    Toggle = FALSE,
+    Container = FALSE,
+    RecordAbsolute = FALSE,
+    RecordPerSet;
+    Synchron;
+
+    /* status: */
+    SlotType = SfxStringItem
+
+    /* config: */
+    AccelConfig = TRUE,
+    MenuConfig = TRUE,
+    StatusBarConfig = FALSE,
+    ToolBoxConfig = FALSE,
+    GroupId = GID_OPTIONS;
+]
+
 SfxVoidItem ShowDetail SID_OUTLINE_SHOW
 ()
 [
diff --git a/sc/source/core/data/cellvalues.cxx b/sc/source/core/data/cellvalues.cxx
index 083b4d1ac7a8..fb38ed52cae8 100644
--- a/sc/source/core/data/cellvalues.cxx
+++ b/sc/source/core/data/cellvalues.cxx
@@ -16,10 +16,44 @@
 
 namespace sc {
 
+namespace {
+
+struct BlockPos
+{
+    size_t mnStart;
+    size_t mnEnd;
+};
+
+CellType toCellType( mdds::mtv::element_t eType )
+{
+    switch (eType)
+    {
+        case element_type_numeric:
+            return CELLTYPE_VALUE;
+        case element_type_string:
+            return CELLTYPE_STRING;
+        case element_type_edittext:
+            return CELLTYPE_EDIT;
+        case element_type_formula:
+            return CELLTYPE_FORMULA;
+        default:
+            ;
+    }
+
+    return CELLTYPE_NONE;
+}
+
+}
+
+CellValueSpan::CellValueSpan( SCROW nRow1, SCROW nRow2, CellType eType ) :
+    mnRow1(nRow1), mnRow2(nRow2), meType(eType) {}
+
 struct CellValuesImpl : boost::noncopyable
 {
     CellStoreType maCells;
     CellTextAttrStoreType maCellTextAttrs;
+    CellStoreType::iterator miCellPos;
+    CellTextAttrStoreType::iterator miAttrPos;
 };
 
 CellValues::CellValues() :
@@ -53,6 +87,34 @@ void CellValues::copyTo( ScColumn& rCol, SCROW nRow ) const
     copyCellTextAttrsTo(rCol, nRow);
 }
 
+void CellValues::swapNonEmpty( ScColumn& rCol )
+{
+    std::vector<BlockPos> aBlocksToSwap;
+
+    {
+        // Go through static value blocks and record their positions and sizes.
+        sc::CellStoreType::const_iterator it = mpImpl->maCells.begin(), itEnd = mpImpl->maCells.end();
+        for (; it != itEnd; ++it)
+        {
+            if (it->type == sc::element_type_empty)
+                continue;
+
+            BlockPos aPos;
+            aPos.mnStart = it->position;
+            aPos.mnEnd = aPos.mnStart + it->size - 1;
+            aBlocksToSwap.push_back(aPos);
+        }
+    }
+
+    // Do the swapping.  The undo storage will store the replaced formula cells after this.
+    std::vector<BlockPos>::const_iterator it = aBlocksToSwap.begin(), itEnd = aBlocksToSwap.end();
+    for (; it != itEnd; ++it)
+    {
+        rCol.maCells.swap(it->mnStart, it->mnEnd, mpImpl->maCells, it->mnStart);
+        rCol.maCellTextAttrs.swap(it->mnStart, it->mnEnd, mpImpl->maCellTextAttrs, it->mnStart);
+    }
+}
+
 void CellValues::assign( const std::vector<double>& rVals )
 {
     mpImpl->maCells.resize(rVals.size());
@@ -70,6 +132,51 @@ size_t CellValues::size() const
     return mpImpl->maCells.size();
 }
 
+void CellValues::reset( size_t nSize )
+{
+    mpImpl->maCells.clear();
+    mpImpl->maCells.resize(nSize);
+    mpImpl->maCellTextAttrs.clear();
+    mpImpl->maCellTextAttrs.resize(nSize);
+
+    mpImpl->miCellPos = mpImpl->maCells.begin();
+    mpImpl->miAttrPos = mpImpl->maCellTextAttrs.begin();
+}
+
+void CellValues::setValue( size_t nRow, double fVal )
+{
+    mpImpl->miCellPos = mpImpl->maCells.set(mpImpl->miCellPos, nRow, fVal);
+    mpImpl->miAttrPos = mpImpl->maCellTextAttrs.set(mpImpl->miAttrPos, nRow, sc::CellTextAttr());
+}
+
+void CellValues::setValue( size_t nRow, const svl::SharedString& rStr )
+{
+    mpImpl->miCellPos = mpImpl->maCells.set(mpImpl->miCellPos, nRow, rStr);
+    mpImpl->miAttrPos = mpImpl->maCellTextAttrs.set(mpImpl->miAttrPos, nRow, sc::CellTextAttr());
+}
+
+void CellValues::swap( CellValues& r )
+{
+    std::swap(mpImpl, r.mpImpl);
+}
+
+std::vector<CellValueSpan> CellValues::getNonEmptySpans() const
+{
+    std::vector<CellValueSpan> aRet;
+    CellStoreType::const_iterator it = mpImpl->maCells.begin(), itEnd = mpImpl->maCells.end();
+    for (; it != itEnd; ++it)
+    {
+        if (it->type != element_type_empty)
+        {
+            // Record this span.
+            size_t nRow1 = it->position;
+            size_t nRow2 = nRow1 + it->size - 1;
+            aRet.push_back(CellValueSpan(nRow1, nRow2, toCellType(it->type)));
+        }
+    }
+    return aRet;
+}
+
 void CellValues::copyCellsTo( ScColumn& rCol, SCROW nRow ) const
 {
     CellStoreType& rDest = rCol.maCells;
@@ -167,6 +274,101 @@ void CellValues::copyCellTextAttrsTo( ScColumn& rCol, SCROW nRow ) const
     }
 }
 
+typedef boost::ptr_vector<CellValues> TableType;
+typedef boost::ptr_vector<TableType> TablesType;
+
+struct TableValues::Impl
+{
+    ScRange maRange;
+    TablesType maTables;
+
+    Impl( const ScRange& rRange ) : maRange(rRange)
+    {
+        size_t nTabs = rRange.aEnd.Tab() - rRange.aStart.Tab() + 1;
+        size_t nCols = rRange.aEnd.Col() - rRange.aStart.Col() + 1;
+
+        for (size_t nTab = 0; nTab < nTabs; ++nTab)
+        {
+            maTables.push_back(new TableType);
+            TableType& rTab = maTables.back();
+            for (size_t nCol = 0; nCol < nCols; ++nCol)
+                rTab.push_back(new CellValues);
+        }
+    }
+
+    CellValues* getCellValues( SCTAB nTab, SCCOL nCol )
+    {
+        if (nTab < maRange.aStart.Tab() || maRange.aEnd.Tab() < nTab)
+            // sheet index out of bound.
+            return NULL;
+
+        if (nCol < maRange.aStart.Col() || maRange.aEnd.Col() < nCol)
+            // column index out of bound.
+            return NULL;
+
+        size_t nTabOffset = nTab - maRange.aStart.Tab();
+        if (nTabOffset >= maTables.size())
+            return NULL;
+
+        TableType& rTab = maTables[nTab-maRange.aStart.Tab()];
+
+        size_t nColOffset = nCol - maRange.aStart.Col();
+        if (nColOffset >= rTab.size())
+            return NULL;
+
+        return &rTab[nColOffset];
+    }
+};
+
+TableValues::TableValues() :
+    mpImpl(new Impl(ScRange(ScAddress::INITIALIZE_INVALID))) {}
+
+TableValues::TableValues( const ScRange& rRange ) :
+    mpImpl(new Impl(rRange)) {}
+
+TableValues::~TableValues()
+{
+    delete mpImpl;
+}
+
+const ScRange& TableValues::getRange() const
+{
+    return mpImpl->maRange;
+}
+
+void TableValues::swap( SCTAB nTab, SCCOL nCol, CellValues& rColValue )
+{
+    CellValues* pCol = mpImpl->getCellValues(nTab, nCol);
+    if (!pCol)
+        return;
+
+    pCol->swap(rColValue);
+}
+
+void TableValues::swapNonEmpty( SCTAB nTab, SCCOL nCol, ScColumn& rCol )
+{
+    CellValues* pCol = mpImpl->getCellValues(nTab, nCol);
+    if (!pCol)
+        return;
+
+    pCol->swapNonEmpty(rCol);
+}
+
+std::vector<CellValueSpan> TableValues::getNonEmptySpans( SCTAB nTab, SCCOL nCol ) const
+{
+    std::vector<CellValueSpan> aRet;
+    CellValues* pCol = mpImpl->getCellValues(nTab, nCol);
+    if (pCol)
+        aRet = pCol->getNonEmptySpans();
+
+    return aRet;
+}
+
+void TableValues::swap( TableValues& rOther )
+{
+    std::swap(mpImpl, rOther.mpImpl);
+}
+
 }
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/column4.cxx b/sc/source/core/data/column4.cxx
index 3be685704057..c0c1fde3cb3f 100644
--- a/sc/source/core/data/column4.cxx
+++ b/sc/source/core/data/column4.cxx
@@ -322,6 +322,155 @@ void ScColumn::CopyCellValuesFrom( SCROW nRow, const sc::CellValues& rSrc )
     BroadcastCells(aRows, SC_HINT_DATACHANGED);
 }
 
+namespace {
+
+class ConvertFormulaToValueHandler
+{
+    SCTAB mnTab;
+    SCCOL mnCol;
+    sc::CellValues maResValues;
+    bool mbModified;
+
+public:
+    ConvertFormulaToValueHandler( SCTAB nTab, SCCOL nCol ) :
+        mnTab(nTab),
+        mnCol(nCol),
+        mbModified(false)
+    {
+        maResValues.reset(MAXROWCOUNT);
+    }
+
+    void operator() ( size_t nRow, const ScFormulaCell* pCell )
+    {
+        sc::FormulaResultValue aRes = pCell->GetResult();
+        switch (aRes.meType)
+        {
+            case sc::FormulaResultValue::Value:
+                maResValues.setValue(nRow, aRes.mfValue);
+            break;
+            case sc::FormulaResultValue::String:
+                maResValues.setValue(nRow, aRes.maString);
+            break;
+            case sc::FormulaResultValue::Error:
+            case sc::FormulaResultValue::Invalid:
+            default:
+                maResValues.setValue(nRow, svl::SharedString::getEmptyString());
+        }
+
+        mbModified = true;
+    }
+
+    bool isModified() const { return mbModified; }
+
+    sc::CellValues& getResValues() { return maResValues; }
+};
+
+}
+
+void ScColumn::ConvertFormulaToValue(
+    sc::EndListeningContext& rCxt, SCROW nRow1, SCROW nRow2, sc::TableValues* pUndo )
+{
+    if (!ValidRow(nRow1) || !ValidRow(nRow2) || nRow1 > nRow2)
+        return;
+
+    std::vector<SCROW> aBounds;
+    aBounds.push_back(nRow1);
+    if (nRow2 < MAXROW-1)
+        aBounds.push_back(nRow2+1);
+
+    // Split formula cell groups at top and bottom boundaries (if applicable).
+    sc::SharedFormulaUtil::splitFormulaCellGroups(maCells, aBounds);
+
+    // Parse all formulas within the range and store their results into temporary storage.
+    ConvertFormulaToValueHandler aFunc(nTab, nCol);
+    sc::ParseFormula(maCells.begin(), maCells, nRow1, nRow2, aFunc);
+    if (!aFunc.isModified())
+        // No formula cells encountered.
+        return;
+
+    DetachFormulaCells(rCxt, nRow1, nRow2);
+
+    // Undo storage to hold static values which will get swapped to the cell storage later.
+    sc::CellValues aUndoCells;
+    aFunc.getResValues().swap(aUndoCells);
+    aUndoCells.swapNonEmpty(*this);
+    if (pUndo)
+        pUndo->swap(nTab, nCol, aUndoCells);
+}
+
+namespace {
+
+class StartListeningHandler
+{
+    sc::StartListeningContext& mrCxt;
+
+public:
+    StartListeningHandler( sc::StartListeningContext& rCxt ) :
+        mrCxt(rCxt) {}
+
+    void operator() (size_t /*nRow*/, ScFormulaCell* pCell)
+    {
+        pCell->StartListeningTo(mrCxt);
+    }
+};
+
+class EndListeningHandler
+{
+    sc::EndListeningContext& mrCxt;
+
+public:
+    EndListeningHandler( sc::EndListeningContext& rCxt ) :
+        mrCxt(rCxt) {}
+
+    void operator() (size_t /*nRow*/, ScFormulaCell* pCell)
+    {
+        pCell->EndListeningTo(mrCxt);
+    }
+};
+
+}
+
+void ScColumn::SwapNonEmpty(
+    sc::TableValues& rValues, sc::StartListeningContext& rStartCxt, sc::EndListeningContext& rEndCxt )
+{
+    const ScRange& rRange = rValues.getRange();
+    std::vector<SCROW> aBounds;
+    aBounds.push_back(rRange.aStart.Row());
+    if (rRange.aEnd.Row() < MAXROW-1)
+        aBounds.push_back(rRange.aEnd.Row()+1);
+
+    // Split formula cell groups at top and bottom boundaries (if applicable).
+    sc::SharedFormulaUtil::splitFormulaCellGroups(maCells, aBounds);
+    std::vector<sc::CellValueSpan> aSpans = rValues.getNonEmptySpans(nTab, nCol);
+
+    // Detach formula cells within the spans (if any).
+    EndListeningHandler aEndLisFunc(rEndCxt);
+    std::vector<sc::CellValueSpan>::const_iterator it = aSpans.begin(), itEnd = aSpans.end();
+    sc::CellStoreType::iterator itPos = maCells.begin();
+    for (; it != itEnd; ++it)
+    {
+        SCROW nRow1 = it->mnRow1;
+        SCROW nRow2 = it->mnRow2;
+        itPos = sc::ProcessFormula(itPos, maCells, nRow1, nRow2, aEndLisFunc);
+    }
+
+    rValues.swapNonEmpty(nTab, nCol, *this);
+    RegroupFormulaCells();
+
+    // Attach formula cells within the spans (if any).
+    StartListeningHandler aStartLisFunc(rStartCxt);
+    it = aSpans.begin();
+    itPos = maCells.begin();
+    for (; it != itEnd; ++it)
+    {
+        SCROW nRow1 = it->mnRow1;
+        SCROW nRow2 = it->mnRow2;
+        itPos = sc::ProcessFormula(itPos, maCells, nRow1, nRow2, aStartLisFunc);
+    }
+
+    CellStorageModified();
+}
+
 void ScColumn::DeleteRanges( const std::vector<sc::RowSpan>& rRanges, InsertDeleteFlags nDelFlag, bool bBroadcast )
 {
     std::vector<sc::RowSpan>::const_iterator itSpan = rRanges.begin(), itSpanEnd = rRanges.end();
diff --git a/sc/source/core/data/document10.cxx b/sc/source/core/data/document10.cxx
index 51b85ec0cfc4..183e8e5d02e8 100644
--- a/sc/source/core/data/document10.cxx
+++ b/sc/source/core/data/document10.cxx
@@ -18,6 +18,7 @@
 #include <tokenstringcontext.hxx>
 #include <poolhelp.hxx>
 #include <bcaslot.hxx>
+#include <cellvalues.hxx>
 
 #include "dociter.hxx"
 #include "patattr.hxx"
@@ -302,6 +303,46 @@ void ScDocument::SetCalcConfig( const ScCalcConfig& rConfig )
     maCalcConfig = rConfig;
 }
 
+void ScDocument::ConvertFormulaToValue( const ScRange& rRange, sc::TableValues* pUndo )
+{
+    sc::EndListeningContext aCxt(*this);
+
+    for (SCTAB nTab = rRange.aStart.Tab(); nTab <= rRange.aEnd.Tab(); ++nTab)
+    {
+        ScTable* pTab = FetchTable(nTab);
+        if (!pTab)
+            continue;
+
+        pTab->ConvertFormulaToValue(
+            aCxt, rRange.aStart.Col(), rRange.aStart.Row(), rRange.aEnd.Col(), rRange.aEnd.Row(),
+            pUndo);
+    }
+
+    aCxt.purgeEmptyBroadcasters();
+}
+
+void ScDocument::SwapNonEmpty( sc::TableValues& rValues )
+{
+    const ScRange& rRange = rValues.getRange();
+    if (!rRange.IsValid())
+        return;
+
+    boost::shared_ptr<sc::ColumnBlockPositionSet> pPosSet(new sc::ColumnBlockPositionSet(*this));
+    sc::StartListeningContext aStartCxt(*this, pPosSet);
+    sc::EndListeningContext aEndCxt(*this, pPosSet);
+
+    for (SCTAB nTab = rRange.aStart.Tab(); nTab <= rRange.aEnd.Tab(); ++nTab)
+    {
+        ScTable* pTab = FetchTable(nTab);
+        if (!pTab)
+            continue;
+
+        pTab->SwapNonEmpty(rValues, aStartCxt, aEndCxt);
+    }
+
+    aEndCxt.purgeEmptyBroadcasters();
+}
+
 void ScDocument::PreprocessRangeNameUpdate()
 {
     sc::EndListeningContext aEndListenCxt(*this);
diff --git a/sc/source/core/data/formulacell.cxx b/sc/source/core/data/formulacell.cxx
index d10b9d4ff763..8cd95986c73c 100644
--- a/sc/source/core/data/formulacell.cxx
+++ b/sc/source/core/data/formulacell.cxx
@@ -2513,6 +2513,15 @@ sc::FormulaResultValue ScFormulaCell::GetResult()
     return aResult.GetResult();
 }
 
+sc::FormulaResultValue ScFormulaCell::GetResult() const
+{
+    sal_uInt16 nErr = pCode->GetCodeError();
+    if (nErr)
+        return sc::FormulaResultValue(nErr);
+
+    return aResult.GetResult();
+}
+
 bool ScFormulaCell::HasOneReference( ScRange& r ) const
 {
     pCode->Reset();
diff --git a/sc/source/core/data/table7.cxx b/sc/source/core/data/table7.cxx
index 407a8ba0d155..2104b6f983bc 100644
--- a/sc/source/core/data/table7.cxx
+++ b/sc/source/core/data/table7.cxx
@@ -14,6 +14,7 @@
 #include <bcaslot.hxx>
 #include <segmenttree.hxx>
 #include <sharedformula.hxx>
+#include <cellvalues.hxx>
 
 bool ScTable::IsMerged( SCCOL nCol, SCROW nRow ) const
 {
@@ -82,6 +83,26 @@ void ScTable::CopyCellValuesFrom( SCCOL nCol, SCROW nRow, const sc::CellValues&
     aCol[nCol].CopyCellValuesFrom(nRow, rSrc);
 }
 
+void ScTable::ConvertFormulaToValue(
+    sc::EndListeningContext& rCxt, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
+    sc::TableValues* pUndo )
+{
+    if (!ValidCol(nCol1) || !ValidCol(nCol2) || nCol1 > nCol2)
+        return;
+
+    for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+        aCol[nCol].ConvertFormulaToValue(rCxt, nRow1, nRow2, pUndo);
+}
+
+void ScTable::SwapNonEmpty(
+    sc::TableValues& rValues, sc::StartListeningContext& rStartCxt, sc::EndListeningContext& rEndCxt )
+{
+    const ScRange& rRange = rValues.getRange();
+    assert(rRange.IsValid());
+    for (SCCOL nCol = rRange.aStart.Col(); nCol <= rRange.aEnd.Col(); ++nCol)
+        aCol[nCol].SwapNonEmpty(rValues, rStartCxt, rEndCxt);
+}
+
 void ScTable::PreprocessRangeNameUpdate(
     sc::EndListeningContext& rEndListenCxt, sc::CompileFormulaContext& rCompileCxt )
 {
diff --git a/sc/source/ui/docshell/docfunc.cxx b/sc/source/ui/docshell/docfunc.cxx
index c20992abf876..f3a6245ca7d4 100644
--- a/sc/source/ui/docshell/docfunc.cxx
+++ b/sc/source/ui/docshell/docfunc.cxx
@@ -84,6 +84,8 @@
 #include "cellvalue.hxx"
 #include "tokenarray.hxx"
 #include <rowheightcontext.hxx>
+#include <cellvalues.hxx>
+#include <undoconvert.hxx>
 
 #include <memory>
 #include <utility>
@@ -5384,6 +5386,38 @@ void ScDocFunc::SetConditionalFormatList( ScConditionalFormatList* pList, SCTAB
     SfxGetpApp()->Broadcast(SfxSimpleHint(SC_HINT_AREAS_CHANGED));
 }
 
+void ScDocFunc::ConvertFormulaToValue( const ScRange& rRange, bool bRecord, bool bInteraction )
+{
+    ScDocShellModificator aModificator(rDocShell);
+    ScDocument& rDoc = rDocShell.GetDocument();
+    if (!rDoc.IsUndoEnabled())
+        bRecord = false;
+
+    ScEditableTester aTester(&rDoc, rRange);
+    if (!aTester.IsEditable())
+    {
+        if (bInteraction)
+            rDocShell.ErrorMessage(aTester.GetMessageId());
+        return;
+    }
+
+    sc::TableValues aUndoVals(rRange);
+    sc::TableValues* pUndoVals = bRecord ? &aUndoVals : NULL;
+
+    rDoc.ConvertFormulaToValue(rRange, pUndoVals);
+
+    if (bRecord && pUndoVals)
+    {
+        rDocShell.GetUndoManager()->AddUndoAction(
+            new sc::UndoFormulaToValue(&rDocShell, *pUndoVals));
+    }
+
+    rDocShell.PostPaint(rRange, PAINT_GRID);
+    rDocShell.PostDataChanged();
+    rDoc.BroadcastCells(rRange, SC_HINT_DATACHANGED);
+    aModificator.SetDocumentModified();
+}
+
 void ScDocFunc::EnterListAction( sal_uInt16 nNameResId )
 {
     OUString aUndo( ScGlobal::GetRscString( nNameResId ) );
diff --git a/sc/source/ui/inc/docfunc.hxx b/sc/source/ui/inc/docfunc.hxx
index 37bf4aac12b7..ebd7ac371aeb 100644
--- a/sc/source/ui/inc/docfunc.hxx
+++ b/sc/source/ui/inc/docfunc.hxx
@@ -222,6 +222,8 @@ public:
      * @param nTab the tab to which the conditional format list belongs
      */
     void SetConditionalFormatList( ScConditionalFormatList* pList, SCTAB nTab );
+
+    void ConvertFormulaToValue( const ScRange& rRange, bool bRecord, bool bInteraction );
 };
 
 class ScDocFuncDirect : public ScDocFunc
diff --git a/sc/source/ui/inc/undoconvert.hxx b/sc/source/ui/inc/undoconvert.hxx
new file mode 100644
index 000000000000..99ce97f51e7c
--- /dev/null
+++ b/sc/source/ui/inc/undoconvert.hxx
@@ -0,0 +1,37 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#ifndef INCLUDED_SC_UNDOCONVERT_HXX
+#define INCLUDED_SC_UNDOCONVERT_HXX
+
+#include <undobase.hxx>
+#include <cellvalues.hxx>
+
+namespace sc {
+
+class UndoFormulaToValue : public ScSimpleUndo
+{
+    TableValues maUndoValues;
+
+public:
+    UndoFormulaToValue( ScDocShell* pDocSh, TableValues& rUndoValues );
+
+    virtual OUString GetComment() const SAL_OVERRIDE;
+    virtual void Undo() SAL_OVERRIDE;
+    virtual void Redo() SAL_OVERRIDE;
+
+private:
+    void Execute();
+};
+
+}
+
+#endif
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/inc/viewfunc.hxx b/sc/source/ui/inc/viewfunc.hxx
index cd664cd5b45d..70c7e0f8df4b 100644
--- a/sc/source/ui/inc/viewfunc.hxx
+++ b/sc/source/ui/inc/viewfunc.hxx
@@ -240,6 +240,7 @@ public:
     void            FillAuto( FillDir eDir, SCCOL nStartCol, SCROW nStartRow,
                                 SCCOL nEndCol, SCROW nEndRow, sal_uLong nCount, bool bRecord = true );
     void            FillCrossDblClick();
+    void            ConvertFormulaToValue();
 
     void            TransliterateText( sal_Int32 nType );
 
diff --git a/sc/source/ui/src/globstr.src b/sc/source/ui/src/globstr.src
index 3d167fdef221..985644afec1f 100644
--- a/sc/source/ui/src/globstr.src
+++ b/sc/source/ui/src/globstr.src
@@ -2076,6 +2076,11 @@ Resource RID_GLOBSTR
     {
         Text [ en-US ] = "Conditional Format";
     };
+
+    String STR_UNDO_FORMULA_TO_VALUE
+    {
+        Text [ en-US ] = "Convert Formula To Value";
+    };
 };
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/undo/undoconvert.cxx b/sc/source/ui/undo/undoconvert.cxx
new file mode 100644
index 000000000000..995ab8f3e2fd
--- /dev/null
+++ b/sc/source/ui/undo/undoconvert.cxx
@@ -0,0 +1,51 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#include <undoconvert.hxx>
+#include <globstr.hrc>
+#include <undoutil.hxx>
+
+namespace sc {
+
+UndoFormulaToValue::UndoFormulaToValue( ScDocShell* pDocSh, TableValues& rUndoValues ) :
+    ScSimpleUndo(pDocSh)
+{
+    maUndoValues.swap(rUndoValues);
+}
+
+OUString UndoFormulaToValue::GetComment() const
+{
+    return ScGlobal::GetRscString(STR_UNDO_FORMULA_TO_VALUE);
+}
+
+void UndoFormulaToValue::Undo()
+{
+    Execute();
+}
+
+void UndoFormulaToValue::Redo()
+{
+    Execute();
+}
+
+void UndoFormulaToValue::Execute()
+{
+    ScDocument& rDoc = pDocShell->GetDocument();
+    rDoc.SwapNonEmpty(maUndoValues);
+
+    ScUndoUtil::MarkSimpleBlock(pDocShell, maUndoValues.getRange());
+
+    pDocShell->PostPaint(maUndoValues.getRange(), PAINT_GRID);
+    pDocShell->PostDataChanged();
+    rDoc.BroadcastCells(maUndoValues.getRange(), SC_HINT_DATACHANGED);
+}
+
+}
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/view/cellsh1.cxx b/sc/source/ui/view/cellsh1.cxx
index bb9ce1ad1cae..511d4b7e2e9d 100644
--- a/sc/source/ui/view/cellsh1.cxx
+++ b/sc/source/ui/view/cellsh1.cxx
@@ -1754,6 +1754,11 @@ void ScCellShell::ExecuteEdit( SfxRequest& rReq )
             }
             break;
 
+        case SID_CONVERT_FORMULA_TO_VALUE:
+        {
+            pTabViewShell->ConvertFormulaToValue();
+        }
+        break;
         case SID_THESAURUS:
             pTabViewShell->DoThesaurus();
             break;
diff --git a/sc/source/ui/view/viewfun2.cxx b/sc/source/ui/view/viewfun2.cxx
index df5fb973cb6e..1f395fc4aad6 100644
--- a/sc/source/ui/view/viewfun2.cxx
+++ b/sc/source/ui/view/viewfun2.cxx
@@ -1414,6 +1414,17 @@ void ScViewFunc::FillCrossDblClick()
     }
 }
 
+void ScViewFunc::ConvertFormulaToValue()
+{
+    ScRange aRange;
+    GetViewData().GetSimpleArea(aRange);
+    aRange.Justify();
+
+    ScDocShell* pDocSh = GetViewData().GetDocShell();
+    pDocSh->GetDocFunc().ConvertFormulaToValue(aRange, true, true);
+    pDocSh->PostPaint(aRange, PAINT_GRID);
+}
+
 void ScViewFunc::TransliterateText( sal_Int32 nType )
 {
     ScMarkData aFuncMark = GetViewData().GetMarkData();