Adding upstream version 4:7.4.7.upstream/4%7.4.7upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 559 insertions, 0 deletions

diff --git a/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx b/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx
new file mode 100644
index 000000000..f6871ccff
--- /dev/null
+++ b/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx
@@ -0,0 +1,559 @@
+/* -*- 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 <memory>
+#include <string_view>
+
+#include <rangelst.hxx>
+#include <reffact.hxx>
+#include <TableFillingAndNavigationTools.hxx>
+#include <AnalysisOfVarianceDialog.hxx>
+#include <scresid.hxx>
+#include <strings.hrc>
+
+namespace
+{
+
+struct StatisticCalculation {
+    TranslateId aLabelId;
+    const char* aFormula;
+    const char* aResultRangeName;
+};
+
+StatisticCalculation const lclBasicStatistics[] =
+{
+    { STR_ANOVA_LABEL_GROUPS, nullptr,             nullptr       },
+    { STRID_CALC_COUNT,       "=COUNT(%RANGE%)",   "COUNT_RANGE" },
+    { STRID_CALC_SUM,         "=SUM(%RANGE%)",     "SUM_RANGE"   },
+    { STRID_CALC_MEAN,        "=AVERAGE(%RANGE%)", "MEAN_RANGE"  },
+    { STRID_CALC_VARIANCE,    "=VAR(%RANGE%)",     "VAR_RANGE"   },
+    { {},                     nullptr,             nullptr       }
+};
+
+const TranslateId lclAnovaLabels[] =
+{
+    STR_ANOVA_LABEL_SOURCE_OF_VARIATION,
+    STR_ANOVA_LABEL_SS,
+    STR_ANOVA_LABEL_DF,
+    STR_ANOVA_LABEL_MS,
+    STR_ANOVA_LABEL_F,
+    STR_ANOVA_LABEL_P_VALUE,
+    STR_ANOVA_LABEL_F_CRITICAL,
+    {}
+};
+
+constexpr OUStringLiteral strWildcardRange = u"%RANGE%";
+
+OUString lclCreateMultiParameterFormula(
+            ScRangeList&        aRangeList, const OUString& aFormulaTemplate,
+            std::u16string_view aWildcard,  const ScDocument& rDocument,
+            const ScAddress::Details& aAddressDetails)
+{
+    OUStringBuffer aResult;
+    for (size_t i = 0; i < aRangeList.size(); i++)
+    {
+        OUString aRangeString(aRangeList[i].Format(rDocument, ScRefFlags::RANGE_ABS_3D, aAddressDetails));
+        OUString aFormulaString = aFormulaTemplate.replaceAll(aWildcard, aRangeString);
+        aResult.append(aFormulaString);
+        if(i != aRangeList.size() - 1) // Not Last
+            aResult.append(";");
+    }
+    return aResult.makeStringAndClear();
+}
+
+void lclMakeSubRangesList(ScRangeList& rRangeList, const ScRange& rInputRange, ScStatisticsInputOutputDialog::GroupedBy aGroupedBy)
+{
+    std::unique_ptr<DataRangeIterator> pIterator;
+    if (aGroupedBy == ScStatisticsInputOutputDialog::BY_COLUMN)
+        pIterator.reset(new DataRangeByColumnIterator(rInputRange));
+    else
+        pIterator.reset(new DataRangeByRowIterator(rInputRange));
+
+    for( ; pIterator->hasNext(); pIterator->next() )
+    {
+        ScRange aRange = pIterator->get();
+        rRangeList.push_back(aRange);
+    }
+}
+
+}
+
+ScAnalysisOfVarianceDialog::ScAnalysisOfVarianceDialog(
+                    SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
+                    weld::Window* pParent, ScViewData& rViewData )
+    : ScStatisticsInputOutputDialog(
+            pSfxBindings, pChildWindow, pParent, rViewData,
+            "modules/scalc/ui/analysisofvariancedialog.ui",
+            "AnalysisOfVarianceDialog")
+    , meFactor(SINGLE_FACTOR)
+    , mxAlphaField(m_xBuilder->weld_spin_button("alpha-spin"))
+    , mxSingleFactorRadio(m_xBuilder->weld_radio_button("radio-single-factor"))
+    , mxTwoFactorRadio(m_xBuilder->weld_radio_button("radio-two-factor"))
+    , mxRowsPerSampleField(m_xBuilder->weld_spin_button("rows-per-sample-spin"))
+{
+    mxSingleFactorRadio->connect_toggled( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) );
+    mxTwoFactorRadio->connect_toggled( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) );
+
+    mxSingleFactorRadio->set_active(true);
+    mxTwoFactorRadio->set_active(false);
+
+    FactorChanged();
+}
+
+ScAnalysisOfVarianceDialog::~ScAnalysisOfVarianceDialog()
+{
+}
+
+void ScAnalysisOfVarianceDialog::Close()
+{
+    DoClose( ScAnalysisOfVarianceDialogWrapper::GetChildWindowId() );
+}
+
+TranslateId ScAnalysisOfVarianceDialog::GetUndoNameId()
+{
+    return STR_ANALYSIS_OF_VARIANCE_UNDO_NAME;
+}
+
+IMPL_LINK_NOARG( ScAnalysisOfVarianceDialog, FactorChanged, weld::Toggleable&, void )
+{
+    FactorChanged();
+}
+
+void ScAnalysisOfVarianceDialog::FactorChanged()
+{
+    if (mxSingleFactorRadio->get_active())
+    {
+        mxGroupByRowsRadio->set_sensitive(true);
+        mxGroupByColumnsRadio->set_sensitive(true);
+        mxRowsPerSampleField->set_sensitive(false);
+        meFactor = SINGLE_FACTOR;
+    }
+    else if (mxTwoFactorRadio->get_active())
+    {
+        mxGroupByRowsRadio->set_sensitive(false);
+        mxGroupByColumnsRadio->set_sensitive(false);
+        mxRowsPerSampleField->set_sensitive(false); // Rows per sample not yet implemented
+        meFactor = TWO_FACTOR;
+    }
+}
+
+void ScAnalysisOfVarianceDialog::RowColumn(ScRangeList& rRangeList, AddressWalkerWriter& aOutput, FormulaTemplate& aTemplate,
+                                           const OUString& sFormula, GroupedBy aGroupedBy, ScRange* pResultRange)
+{
+    if (pResultRange != nullptr)
+        pResultRange->aStart = aOutput.current();
+    if (!sFormula.isEmpty())
+    {
+        for (size_t i = 0; i < rRangeList.size(); i++)
+        {
+            ScRange const & rRange = rRangeList[i];
+            aTemplate.setTemplate(sFormula);
+            aTemplate.applyRange(strWildcardRange, rRange);
+            aOutput.writeFormula(aTemplate.getTemplate());
+            if (pResultRange != nullptr)
+                pResultRange->aEnd = aOutput.current();
+            aOutput.nextRow();
+        }
+    }
+    else
+    {
+        TranslateId pLabelId = (aGroupedBy == BY_COLUMN) ? STR_COLUMN_LABEL_TEMPLATE : STR_ROW_LABEL_TEMPLATE;
+        OUString aLabelTemplate(ScResId(pLabelId));
+
+        for (size_t i = 0; i < rRangeList.size(); i++)
+        {
+            aTemplate.setTemplate(aLabelTemplate);
+            aTemplate.applyNumber(u"%NUMBER%", i + 1);
+            aOutput.writeString(aTemplate.getTemplate());
+            if (pResultRange != nullptr)
+                pResultRange->aEnd = aOutput.current();
+            aOutput.nextRow();
+        }
+    }
+}
+
+void ScAnalysisOfVarianceDialog::AnovaSingleFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
+{
+    output.writeBoldString(ScResId(STR_ANOVA_SINGLE_FACTOR_LABEL));
+    output.newLine();
+
+    double aAlphaValue = mxAlphaField->get_value() / 100.0;
+    output.writeString(ScResId(STR_LABEL_ALPHA));
+    output.nextColumn();
+    output.writeValue(aAlphaValue);
+    aTemplate.autoReplaceAddress("%ALPHA%", output.current());
+    output.newLine();
+    output.newLine();
+
+    // Write labels
+    for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++)
+    {
+        output.writeString(ScResId(lclBasicStatistics[i].aLabelId));
+        output.nextColumn();
+    }
+    output.newLine();
+
+    // Collect aRangeList
+    ScRangeList aRangeList;
+    lclMakeSubRangesList(aRangeList, mInputRange, mGroupedBy);
+
+    output.push();
+
+    // Write values
+    for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++)
+    {
+        output.resetRow();
+        ScRange aResultRange;
+        OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
+        RowColumn(aRangeList, output, aTemplate, sFormula, mGroupedBy, &aResultRange);
+        output.nextColumn();
+        if (lclBasicStatistics[i].aResultRangeName != nullptr)
+        {
+            OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
+            aTemplate.autoReplaceRange("%" + sResultRangeName + "%", aResultRange);
+        }
+    }
+
+    output.nextRow(); // Blank row
+
+    // Write ANOVA labels
+    output.resetColumn();
+    for(sal_Int32 i = 0; lclAnovaLabels[i]; i++)
+    {
+        output.writeString(ScResId(lclAnovaLabels[i]));
+        output.nextColumn();
+    }
+    output.nextRow();
+
+    aTemplate.autoReplaceRange("%FIRST_COLUMN%", aRangeList[0]);
+
+    // Between Groups
+    {
+        // Label
+        output.resetColumn();
+        output.writeString(ScResId(STR_ANOVA_LABEL_BETWEEN_GROUPS));
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE%;%MEAN_RANGE%)-SUM(%SUM_RANGE%)^2/SUM(%COUNT_RANGE%)");
+        aTemplate.autoReplaceAddress("%BETWEEN_SS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=COUNT(%SUM_RANGE%)-1");
+        aTemplate.autoReplaceAddress("%BETWEEN_DF%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // MS
+        aTemplate.setTemplate("=%BETWEEN_SS% / %BETWEEN_DF%");
+        aTemplate.autoReplaceAddress("%BETWEEN_MS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F
+        aTemplate.setTemplate("=%BETWEEN_MS% / %WITHIN_MS%");
+        aTemplate.applyAddress(u"%WITHIN_MS%",  output.current(-1, 1));
+        aTemplate.autoReplaceAddress("%F_VAL%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // P-value
+        aTemplate.setTemplate("=FDIST(%F_VAL%; %BETWEEN_DF%; %WITHIN_DF%");
+        aTemplate.applyAddress(u"%WITHIN_DF%",   output.current(-3, 1));
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F critical
+        aTemplate.setTemplate("=FINV(%ALPHA%; %BETWEEN_DF%; %WITHIN_DF%");
+        aTemplate.applyAddress(u"%WITHIN_DF%",  output.current(-4, 1));
+        output.writeFormula(aTemplate.getTemplate());
+    }
+    output.nextRow();
+
+    // Within Groups
+    {
+        // Label
+        output.resetColumn();
+        output.writeString(ScResId(STR_ANOVA_LABEL_WITHIN_GROUPS));
+        output.nextColumn();
+
+        // Sum of Squares
+        OUString aSSPart = lclCreateMultiParameterFormula(aRangeList, "DEVSQ(%RANGE%)", strWildcardRange, mDocument, mAddressDetails);
+        aTemplate.setTemplate("=SUM(%RANGE%)");
+        aTemplate.applyString(strWildcardRange, aSSPart);
+        aTemplate.autoReplaceAddress("%WITHIN_SS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=SUM(%COUNT_RANGE%)-COUNT(%COUNT_RANGE%)");
+        aTemplate.autoReplaceAddress("%WITHIN_DF%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // MS
+        aTemplate.setTemplate("=%WITHIN_SS% / %WITHIN_DF%");
+        output.writeFormula(aTemplate.getTemplate());
+    }
+    output.nextRow();
+
+    // Total
+    {
+        // Label
+        output.resetColumn();
+        output.writeString(ScResId(STR_ANOVA_LABEL_TOTAL));
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=DEVSQ(%RANGE_LIST%)");
+        aTemplate.applyRangeList(u"%RANGE_LIST%", aRangeList, ';');
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=SUM(%COUNT_RANGE%) - 1");
+        output.writeFormula(aTemplate.getTemplate());
+    }
+    output.nextRow();
+}
+
+void ScAnalysisOfVarianceDialog::AnovaTwoFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
+{
+    output.writeBoldString(ScResId(STR_ANOVA_TWO_FACTOR_LABEL));
+    output.newLine();
+
+    double aAlphaValue = mxAlphaField->get_value() / 100.0;
+    output.writeString("Alpha");
+    output.nextColumn();
+    output.writeValue(aAlphaValue);
+    aTemplate.autoReplaceAddress("%ALPHA%", output.current());
+    output.newLine();
+    output.newLine();
+
+    // Write labels
+    for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++)
+    {
+        output.writeString(ScResId(lclBasicStatistics[i].aLabelId));
+        output.nextColumn();
+    }
+    output.newLine();
+
+    ScRangeList aColumnRangeList;
+    ScRangeList aRowRangeList;
+
+    lclMakeSubRangesList(aColumnRangeList, mInputRange, BY_COLUMN);
+    lclMakeSubRangesList(aRowRangeList, mInputRange, BY_ROW);
+
+    // Write ColumnX values
+    output.push();
+    for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++)
+    {
+        output.resetRow();
+        ScRange aResultRange;
+        OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
+        RowColumn(aColumnRangeList, output, aTemplate, sFormula, BY_COLUMN, &aResultRange);
+        if (lclBasicStatistics[i].aResultRangeName != nullptr)
+        {
+            OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
+            aTemplate.autoReplaceRange("%" + sResultRangeName + "_COLUMN%", aResultRange);
+        }
+        output.nextColumn();
+    }
+    output.newLine();
+
+    // Write RowX values
+    output.push();
+    for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++)
+    {
+        output.resetRow();
+        ScRange aResultRange;
+        OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
+        RowColumn(aRowRangeList, output, aTemplate, sFormula, BY_ROW, &aResultRange);
+
+        if (lclBasicStatistics[i].aResultRangeName != nullptr)
+        {
+            OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
+            aTemplate.autoReplaceRange("%" + sResultRangeName + "_ROW%", aResultRange);
+        }
+        output.nextColumn();
+    }
+    output.newLine();
+
+    // Write ANOVA labels
+    for(sal_Int32 i = 0; lclAnovaLabels[i]; i++)
+    {
+        output.writeString(ScResId(lclAnovaLabels[i]));
+        output.nextColumn();
+    }
+    output.nextRow();
+
+    // Setup auto-replace strings
+    aTemplate.autoReplaceRange(strWildcardRange, mInputRange);
+    aTemplate.autoReplaceRange("%FIRST_COLUMN%", aColumnRangeList[0]);
+    aTemplate.autoReplaceRange("%FIRST_ROW%",    aRowRangeList[0]);
+
+    // Rows
+    {
+        // Label
+        output.resetColumn();
+        output.writeString("Rows");
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
+        aTemplate.autoReplaceAddress("%ROW_SS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=MAX(%COUNT_RANGE_COLUMN%) - 1");
+        aTemplate.autoReplaceAddress("%ROW_DF%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // MS
+        aTemplate.setTemplate("=%ROW_SS% / %ROW_DF%");
+        aTemplate.autoReplaceAddress("%MS_ROW%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F
+        aTemplate.setTemplate("=%MS_ROW% / %MS_ERROR%");
+        aTemplate.applyAddress(u"%MS_ERROR%", output.current(-1, 2));
+        aTemplate.autoReplaceAddress("%F_ROW%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // P-value
+        aTemplate.setTemplate("=FDIST(%F_ROW%; %ROW_DF%; %ERROR_DF%");
+        aTemplate.applyAddress(u"%ERROR_DF%",   output.current(-3, 2));
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F critical
+        aTemplate.setTemplate("=FINV(%ALPHA%; %ROW_DF%; %ERROR_DF%");
+        aTemplate.applyAddress(u"%ERROR_DF%",  output.current(-4, 2));
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+    }
+    output.nextRow();
+
+    // Columns
+    {
+        // Label
+        output.resetColumn();
+        output.writeString("Columns");
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
+        aTemplate.autoReplaceAddress("%COLUMN_SS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=MAX(%COUNT_RANGE_ROW%) - 1");
+        aTemplate.autoReplaceAddress("%COLUMN_DF%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // MS
+        aTemplate.setTemplate("=%COLUMN_SS% / %COLUMN_DF%");
+        aTemplate.autoReplaceAddress("%MS_COLUMN%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F
+        aTemplate.setTemplate("=%MS_COLUMN% / %MS_ERROR%");
+        aTemplate.applyAddress(u"%MS_ERROR%", output.current(-1, 1));
+        aTemplate.autoReplaceAddress("%F_COLUMN%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+         // P-value
+        aTemplate.setTemplate("=FDIST(%F_COLUMN%; %COLUMN_DF%; %ERROR_DF%");
+        aTemplate.applyAddress(u"%ERROR_DF%",   output.current(-3, 1));
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // F critical
+        aTemplate.setTemplate("=FINV(%ALPHA%; %COLUMN_DF%; %ERROR_DF%");
+        aTemplate.applyAddress(u"%ERROR_DF%",  output.current(-4, 1));
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+    }
+    output.nextRow();
+
+    // Error
+    {
+        // Label
+        output.resetColumn();
+        output.writeString("Error");
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=SUMSQ(%RANGE%)+SUM(%RANGE%)^2/COUNT(%RANGE%) - (SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) + SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%))");
+        aTemplate.autoReplaceAddress("%ERROR_SS%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=%TOTAL_DF% - %ROW_DF% - %COLUMN_DF%");
+        aTemplate.applyAddress(u"%TOTAL_DF%", output.current(0,1));
+        aTemplate.autoReplaceAddress("%ERROR_DF%", output.current());
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // MS
+        aTemplate.setTemplate("=%ERROR_SS% / %ERROR_DF%");
+        output.writeFormula(aTemplate.getTemplate());
+    }
+    output.nextRow();
+
+     // Total
+    {
+        // Label
+        output.resetColumn();
+        output.writeString("Total");
+        output.nextColumn();
+
+        // Sum of Squares
+        aTemplate.setTemplate("=SUM(%ROW_SS%;%COLUMN_SS%;%ERROR_SS%)");
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+
+        // Degree of freedom
+        aTemplate.setTemplate("=COUNT(%RANGE%)-1");
+        output.writeFormula(aTemplate.getTemplate());
+        output.nextColumn();
+    }
+}
+
+ScRange ScAnalysisOfVarianceDialog::ApplyOutput(ScDocShell* pDocShell)
+{
+    AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument,
+        formula::FormulaGrammar::mergeToGrammar(formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
+    FormulaTemplate aTemplate(&mDocument);
+
+    if (meFactor == SINGLE_FACTOR)
+    {
+        AnovaSingleFactor(output, aTemplate);
+    }
+    else if (meFactor == TWO_FACTOR)
+    {
+        AnovaTwoFactor(output, aTemplate);
+    }
+
+    return ScRange(output.mMinimumAddress, output.mMaximumAddress);
+}
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */