diff options
Diffstat (limited to '')
18 files changed, 4754 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: */ diff --git a/sc/source/ui/StatisticsDialogs/ChiSquareTestDialog.cxx b/sc/source/ui/StatisticsDialogs/ChiSquareTestDialog.cxx new file mode 100644 index 000000000..cfcf53699 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/ChiSquareTestDialog.cxx @@ -0,0 +1,91 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <ChiSquareTestDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScChiSquareTestDialog::ScChiSquareTestDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScStatisticsInputOutputDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/chisquaretestdialog.ui", "ChiSquareTestDialog") +{ + m_xDialog->set_title(ScResId(STR_CHI_SQUARE_TEST)); +} + +ScChiSquareTestDialog::~ScChiSquareTestDialog() +{} + +void ScChiSquareTestDialog::Close() +{ + DoClose(ScChiSquareTestDialogWrapper::GetChildWindowId()); +} + +TranslateId ScChiSquareTestDialog::GetUndoNameId() +{ + return STR_CHI_SQUARE_TEST; +} + +ScRange ScChiSquareTestDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + aTemplate.autoReplaceRange("%RANGE%", mInputRange); + + aOutput.writeBoldString(ScResId(STR_CHI_SQUARE_TEST)); + aOutput.newLine(); + + // Alpha + aOutput.writeString(ScResId(STR_LABEL_ALPHA)); + aOutput.nextColumn(); + aOutput.writeValue(0.05); + aTemplate.autoReplaceAddress("%ALPHA%", aOutput.current()); + aOutput.newLine(); + + // DF + aOutput.writeString(ScResId(STR_DEGREES_OF_FREEDOM_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=(COLUMNS(%RANGE%) - 1) * (ROWS(%RANGE%) - 1)"); + aTemplate.autoReplaceAddress("%DEGREES_OF_FREEDOM%", aOutput.current()); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // p Value + aOutput.writeString(ScResId(STR_P_VALUE_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=CHITEST(%RANGE%; MMULT(MMULT(%RANGE%;TRANSPOSE(IF(COLUMN(%RANGE%))));MMULT(TRANSPOSE(IF(ROW(%RANGE%)));%RANGE%)) / SUM(%RANGE%))"); + aTemplate.autoReplaceAddress("%P_VALUE%", aOutput.current()); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Test Statistic + aOutput.writeString(ScResId(STR_TEST_STATISTIC_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=CHIINV(%P_VALUE%; %DEGREES_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Critical value + aOutput.writeString(ScResId(STR_CRITICAL_VALUE_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=CHIINV(%ALPHA%; %DEGREES_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/CorrelationDialog.cxx b/sc/source/ui/StatisticsDialogs/CorrelationDialog.cxx new file mode 100644 index 000000000..7e9a23372 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/CorrelationDialog.cxx @@ -0,0 +1,39 @@ +/* -*- 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 <reffact.hxx> +#include <CorrelationDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScCorrelationDialog::ScCorrelationDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScMatrixComparisonGenerator( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/correlationdialog.ui", "CorrelationDialog") +{} + +void ScCorrelationDialog::Close() +{ + DoClose(ScCorrelationDialogWrapper::GetChildWindowId()); +} + +OUString ScCorrelationDialog::getLabel() +{ + return ScResId(STR_CORRELATION_LABEL); +} + +OUString ScCorrelationDialog::getTemplate() +{ + return "=CORREL(%VAR1%; %VAR2%)"; +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/CovarianceDialog.cxx b/sc/source/ui/StatisticsDialogs/CovarianceDialog.cxx new file mode 100644 index 000000000..b2849d316 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/CovarianceDialog.cxx @@ -0,0 +1,44 @@ +/* -*- 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 <reffact.hxx> +#include <CovarianceDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScCovarianceDialog::ScCovarianceDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScMatrixComparisonGenerator( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/covariancedialog.ui", "CovarianceDialog") +{} + +TranslateId ScCovarianceDialog::GetUndoNameId() +{ + return STR_COVARIANCE_UNDO_NAME; +} + +void ScCovarianceDialog::Close() +{ + DoClose( ScCovarianceDialogWrapper::GetChildWindowId() ); +} + +OUString ScCovarianceDialog::getLabel() +{ + return ScResId(STR_COVARIANCE_LABEL); +} + +OUString ScCovarianceDialog::getTemplate() +{ + return "=COVAR(%VAR1%; %VAR2%)"; +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/DescriptiveStatisticsDialog.cxx b/sc/source/ui/StatisticsDialogs/DescriptiveStatisticsDialog.cxx new file mode 100644 index 000000000..0924278c5 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/DescriptiveStatisticsDialog.cxx @@ -0,0 +1,141 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <DescriptiveStatisticsDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +namespace +{ + +struct StatisticCalculation { + TranslateId aCalculationNameId; + const char* aFormula; +}; + +const StatisticCalculation lclCalcDefinitions[] = +{ + { STRID_CALC_MEAN, "=AVERAGE(%RANGE%)" }, + { STRID_CALC_STD_ERROR, "=SQRT(VAR(%RANGE%)/COUNT(%RANGE%))"}, + { STRID_CALC_MODE, "=MODE(%RANGE%)"}, + { STRID_CALC_MEDIAN, "=MEDIAN(%RANGE%)"}, + { STRID_CALC_FIRST_QUARTILE, "=QUARTILE(%RANGE%; 1)" }, + { STRID_CALC_THIRD_QUARTILE, "=QUARTILE(%RANGE%; 3)" }, + { STRID_CALC_VARIANCE, "=VAR(%RANGE%)"}, + { STRID_CALC_STD_DEVIATION, "=STDEV(%RANGE%)"}, + { STRID_CALC_KURTOSIS, "=KURT(%RANGE%)"}, + { STRID_CALC_SKEWNESS, "=SKEW(%RANGE%)"}, + { STRID_CALC_RANGE, "=MAX(%RANGE%)-MIN(%RANGE%)"}, + { STRID_CALC_MIN, "=MIN(%RANGE%)"}, + { STRID_CALC_MAX, "=MAX(%RANGE%)"}, + { STRID_CALC_SUM, "=SUM(%RANGE%)"}, + { STRID_CALC_COUNT, "=COUNT(%RANGE%)" }, + { {}, nullptr } +}; + +} + +ScDescriptiveStatisticsDialog::ScDescriptiveStatisticsDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScStatisticsInputOutputDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/descriptivestatisticsdialog.ui", + "DescriptiveStatisticsDialog") +{} + +ScDescriptiveStatisticsDialog::~ScDescriptiveStatisticsDialog() +{} + +void ScDescriptiveStatisticsDialog::Close() +{ + DoClose( ScDescriptiveStatisticsDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScDescriptiveStatisticsDialog::GetUndoNameId() +{ + return STR_DESCRIPTIVE_STATISTICS_UNDO_NAME; +} + +ScRange ScDescriptiveStatisticsDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + std::unique_ptr<DataRangeIterator> pIterator; + if (mGroupedBy == BY_COLUMN) + pIterator.reset(new DataRangeByColumnIterator(mInputRange)); + else + pIterator.reset(new DataRangeByRowIterator(mInputRange)); + + aOutput.nextColumn(); + + // Use explicit sheet name in case the input and output are on different sheets. + bool b3DAddress = mInputRange.aStart.Tab() != mOutputAddress.Tab(); + + // Write column/row labels + for( ; pIterator->hasNext(); pIterator->next() ) + { + // tdf#128018 - add column/row labels to the output + OUString aColRowLabel = mDocument.GetString(pIterator->get().aStart); + if (aColRowLabel.isEmpty()) + { + if (mGroupedBy == BY_COLUMN) + aTemplate.setTemplate(ScResId(STR_COLUMN_LABEL_TEMPLATE)); + else + aTemplate.setTemplate(ScResId(STR_ROW_LABEL_TEMPLATE)); + + aTemplate.applyNumber(u"%NUMBER%", pIterator->index() + 1); + aOutput.writeBoldString(aTemplate.getTemplate()); + } + else + { + aOutput.writeBoldString(aColRowLabel); + } + aOutput.nextColumn(); + } + aOutput.nextRow(); + aOutput.resetColumn(); + aOutput.push(); + + // Write calculation labels + for(sal_Int32 i = 0; lclCalcDefinitions[i].aFormula != nullptr; i++) + { + OUString aLabel(ScResId(lclCalcDefinitions[i].aCalculationNameId)); + aOutput.writeString(aLabel); + aOutput.nextRow(); + } + aOutput.nextColumn(); + + pIterator->reset(); + + for( ; pIterator->hasNext(); pIterator->next() ) + { + aOutput.resetRow(); + + for(sal_Int32 i = 0; lclCalcDefinitions[i].aFormula != nullptr; i++) + { + aTemplate.setTemplate(lclCalcDefinitions[i].aFormula); + aTemplate.applyRange(u"%RANGE%", pIterator->get(), b3DAddress); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextRow(); + } + aOutput.nextColumn(); + } + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/ExponentialSmoothingDialog.cxx b/sc/source/ui/StatisticsDialogs/ExponentialSmoothingDialog.cxx new file mode 100644 index 000000000..1a87f5beb --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/ExponentialSmoothingDialog.cxx @@ -0,0 +1,120 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <ExponentialSmoothingDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScExponentialSmoothingDialog::ScExponentialSmoothingDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) + : ScStatisticsInputOutputDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/exponentialsmoothingdialog.ui", + "ExponentialSmoothingDialog") + , mxSmoothingFactor(m_xBuilder->weld_spin_button("smoothing-factor-spin")) +{ +} + +ScExponentialSmoothingDialog::~ScExponentialSmoothingDialog() +{ +} + +void ScExponentialSmoothingDialog::Close() +{ + DoClose( ScExponentialSmoothingDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScExponentialSmoothingDialog::GetUndoNameId() +{ + return STR_EXPONENTIAL_SMOOTHING_UNDO_NAME; +} + +ScRange ScExponentialSmoothingDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + // Smoothing factor + double aSmoothingFactor = mxSmoothingFactor->get_value() / 100.0; + + // Alpha + output.writeBoldString(ScResId(STR_LABEL_ALPHA)); + output.nextRow(); + + // Alpha Value + ScAddress aSmoothingFactorAddress = output.current(); + output.writeValue(aSmoothingFactor); + output.nextRow(); + + // Exponential Smoothing + output.push(); + + std::unique_ptr<DataRangeIterator> pIterator; + if (mGroupedBy == BY_COLUMN) + pIterator.reset(new DataRangeByColumnIterator(mInputRange)); + else + pIterator.reset(new DataRangeByRowIterator(mInputRange)); + + for( ; pIterator->hasNext(); pIterator->next() ) + { + output.resetRow(); + + ScRange aCurrentRange = pIterator->get(); + + // Write column label + if (mGroupedBy == BY_COLUMN) + aTemplate.setTemplate(ScResId(STR_COLUMN_LABEL_TEMPLATE)); + else + aTemplate.setTemplate(ScResId(STR_ROW_LABEL_TEMPLATE)); + aTemplate.applyNumber(u"%NUMBER%", pIterator->index() + 1); + output.writeBoldString(aTemplate.getTemplate()); + output.nextRow(); + + // Initial value + if ((false)) + { + aTemplate.setTemplate("=AVERAGE(%RANGE%)"); + aTemplate.applyRange(u"%RANGE%", aCurrentRange); + output.writeFormula(aTemplate.getTemplate()); + } + else + { + aTemplate.setTemplate("=%VAR%"); + aTemplate.applyAddress(u"%VAR%", aCurrentRange.aStart); + output.writeFormula(aTemplate.getTemplate()); + } + + output.nextRow(); + + DataCellIterator aDataCellIterator = pIterator->iterateCells(); + + for (; aDataCellIterator.hasNext(); aDataCellIterator.next()) + { + aTemplate.setTemplate("=%VALUE% * %PREVIOUS_INPUT% + (1 - %VALUE%) * %PREVIOUS_OUTPUT%"); + aTemplate.applyAddress(u"%PREVIOUS_INPUT%", aDataCellIterator.get()); + aTemplate.applyAddress(u"%PREVIOUS_OUTPUT%", output.current(0, -1)); + aTemplate.applyAddress(u"%VALUE%", aSmoothingFactorAddress); + + output.writeFormula(aTemplate.getTemplate()); + output.nextRow(); + } + output.nextColumn(); + } + + return ScRange (output.mMinimumAddress, output.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/FTestDialog.cxx b/sc/source/ui/StatisticsDialogs/FTestDialog.cxx new file mode 100644 index 000000000..76b2bade6 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/FTestDialog.cxx @@ -0,0 +1,171 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <FTestDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScFTestDialog::ScFTestDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScStatisticsTwoVariableDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/ttestdialog.ui", "TTestDialog" ) +{ + m_xDialog->set_title(ScResId(STR_FTEST)); +} + +ScFTestDialog::~ScFTestDialog() +{} + +void ScFTestDialog::Close() +{ + DoClose( ScFTestDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScFTestDialog::GetUndoNameId() +{ + return STR_FTEST_UNDO_NAME; +} + +ScRange ScFTestDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar(formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + std::unique_ptr<DataRangeIterator> pVariable1Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range)); + else + pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range)); + + std::unique_ptr<DataRangeIterator> pVariable2Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range)); + else + pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range)); + + aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get()); + aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get()); + + aOutput.writeBoldString(ScResId(STR_FTEST_UNDO_NAME)); + aOutput.newLine(); + + // Alpha + aOutput.writeString(ScResId(STR_LABEL_ALPHA)); + aOutput.nextColumn(); + aOutput.writeValue(0.05); + aTemplate.autoReplaceAddress("%ALPHA%", aOutput.current()); + aOutput.newLine(); + + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_1_LABEL)); + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_2_LABEL)); + aOutput.newLine(); + + aOutput.writeString(ScResId(STRID_CALC_MEAN)); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STRID_CALC_VARIANCE)); + aOutput.nextColumn(); + aTemplate.setTemplate("=VAR(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE1_VARIANCE%", aOutput.current()); + aOutput.nextColumn(); + aTemplate.setTemplate("=VAR(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE2_VARIANCE%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_OBSERVATIONS_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE1_OBSERVATIONS%", aOutput.current()); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE2_OBSERVATIONS%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_ANOVA_LABEL_DF)); + aOutput.nextColumn(); + aTemplate.setTemplate("=%VARIABLE1_OBSERVATIONS% - 1"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE1_DEGREE_OF_FREEDOM%", aOutput.current()); + aOutput.nextColumn(); + aTemplate.setTemplate("=%VARIABLE2_OBSERVATIONS% - 1"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIABLE2_DEGREE_OF_FREEDOM%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_ANOVA_LABEL_F)); + aOutput.nextColumn(); + aTemplate.setTemplate("=%VARIABLE1_VARIANCE% / %VARIABLE2_VARIANCE%"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%F_VALUE%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_P_RIGHT_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=FDIST(%F_VALUE%; %VARIABLE1_DEGREE_OF_FREEDOM%; %VARIABLE2_DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%P_RIGHT_TAIL_VALUE%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_F_CRITICAL_RIGHT_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=FINV(%ALPHA%; %VARIABLE1_DEGREE_OF_FREEDOM%; %VARIABLE2_DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_P_LEFT_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=1 - %P_RIGHT_TAIL_VALUE%"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%P_LEFT_TAIL_VALUE%", aOutput.current()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_F_CRITICAL_LEFT_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=FINV(1-%ALPHA%; %VARIABLE1_DEGREE_OF_FREEDOM%; %VARIABLE2_DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_P_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=2*MIN(%P_RIGHT_TAIL_VALUE%; %P_LEFT_TAIL_VALUE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STR_FTEST_F_CRITICAL_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=FINV(1-(%ALPHA%/2); %VARIABLE1_DEGREE_OF_FREEDOM%; %VARIABLE2_DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=FINV(%ALPHA%/2; %VARIABLE1_DEGREE_OF_FREEDOM%; %VARIABLE2_DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/FourierAnalysisDialog.cxx b/sc/source/ui/StatisticsDialogs/FourierAnalysisDialog.cxx new file mode 100644 index 000000000..c6cff45e8 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/FourierAnalysisDialog.cxx @@ -0,0 +1,231 @@ +/* -*- 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 <docsh.hxx> +#include <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <FourierAnalysisDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> +#include <o3tl/safeint.hxx> + +ScFourierAnalysisDialog::ScFourierAnalysisDialog(SfxBindings* pSfxBindings, + SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData) + : ScStatisticsInputOutputDialog(pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/fourieranalysisdialog.ui", + "FourierAnalysisDialog") + , maLabelAddr(ScAddress::INITIALIZE_INVALID) + , maActualInputRange(ScAddress::INITIALIZE_INVALID) + , mnLen(0) + , mfMinMag(0.0) + , mbUse3DAddresses(false) + , mbGroupedByColumn(true) + , mbWithLabels(false) + , mbInverse(false) + , mbPolar(false) + , mxWithLabelsCheckBox(m_xBuilder->weld_check_button("withlabels-check")) + , mxInverseCheckBox(m_xBuilder->weld_check_button("inverse-check")) + , mxPolarCheckBox(m_xBuilder->weld_check_button("polar-check")) + , mxMinMagnitudeField(m_xBuilder->weld_spin_button("minmagnitude-spin")) + , mxErrorMessage(m_xBuilder->weld_label("error-message")) +{ + m_xDialog->set_title(ScResId(STR_FOURIER_ANALYSIS)); + + mxWithLabelsCheckBox->connect_toggled(LINK(this, ScFourierAnalysisDialog, CheckBoxHdl)); +} + +ScFourierAnalysisDialog::~ScFourierAnalysisDialog() {} + +void ScFourierAnalysisDialog::Close() +{ + DoClose(ScFourierAnalysisDialogWrapper::GetChildWindowId()); +} + +TranslateId ScFourierAnalysisDialog::GetUndoNameId() { return STR_FOURIER_ANALYSIS_UNDO_NAME; } + +ScRange ScFourierAnalysisDialog::ApplyOutput(ScDocShell* pDocShell) +{ + getOptions(); + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( + formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + aTemplate.autoReplaceUses3D(mbUse3DAddresses); + + aOutput.writeBoldString(mbInverse ? ScResId(STR_INVERSE_FOURIER_TRANSFORM) + : ScResId(STR_FOURIER_TRANSFORM)); + aOutput.newLine(); + OUString aLabel; + getDataLabel(aLabel); + if (aLabel.startsWith("=")) + aOutput.writeFormula(aLabel); + else + aOutput.writeString(aLabel); + + aOutput.newLine(); + // Components header + if (!mbPolar) + { + aOutput.writeString(ScResId(STR_REAL_PART)); + aOutput.nextColumn(); + aOutput.writeString(ScResId(STR_IMAGINARY_PART)); + } + else + { + aOutput.writeString(ScResId(STR_MAGNITUDE_PART)); + aOutput.nextColumn(); + aOutput.writeString(ScResId(STR_PHASE_PART)); + } + + aOutput.newLine(); + aTemplate.autoReplaceRange("%INPUTRANGE%", maActualInputRange); + + OUString aFormula; + genFormula(aFormula); + + aTemplate.setTemplate(aFormula); + aOutput.writeMatrixFormula(aTemplate.getTemplate(), 2, mnLen); + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +bool ScFourierAnalysisDialog::InputRangesValid() +{ + if (!mInputRange.IsValid()) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_INPUT_RANGE)); + return false; + } + + if (!mOutputAddress.IsValid()) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_OUTPUT_ADDR)); + return false; + } + + mInputRange.PutInOrder(); + + mbGroupedByColumn = mGroupedBy == BY_COLUMN; + mbWithLabels = mxWithLabelsCheckBox->get_active(); + + mbUse3DAddresses = mInputRange.aStart.Tab() != mOutputAddress.Tab(); + + SCSIZE nRows = mInputRange.aEnd.Row() - mInputRange.aStart.Row() + 1; + SCSIZE nCols = mInputRange.aEnd.Col() - mInputRange.aStart.Col() + 1; + + SCSIZE nLen = mbGroupedByColumn ? nRows : nCols; + SCSIZE nComponents = mbGroupedByColumn ? nCols : nRows; + + if (nComponents > 2) + { + OUString aMsg = mbGroupedByColumn ? ScResId(STR_MESSAGE_INVALID_NUMCOLS) + : ScResId(STR_MESSAGE_INVALID_NUMROWS); + mxErrorMessage->set_label(aMsg); + return false; + } + + if (mbWithLabels && nLen < 2) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_NODATA_IN_RANGE)); + return false; + } + + // Include space for writing the title, label and Real/Imaginary/Magnitude/Phase heading. + SCSIZE nLastOutputRow = mOutputAddress.Row() + nLen + 2; + if (mbWithLabels) + --nLastOutputRow; + + if (nLastOutputRow > o3tl::make_unsigned(mDocument.MaxRow())) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_OUTPUT_TOO_LONG)); + return false; + } + + ScAddress aActualStart(mInputRange.aStart); + + if (mbWithLabels) + { + if (mbGroupedByColumn) + aActualStart.IncRow(); + else + aActualStart.IncCol(); + + if (nComponents == 1) + maLabelAddr = mInputRange.aStart; + else + mbWithLabels = false; + + mnLen = nLen - 1; + } + else + { + mnLen = nLen; + } + + maActualInputRange = ScRange(aActualStart, mInputRange.aEnd); + mxErrorMessage->set_label(""); + + return true; +} + +void ScFourierAnalysisDialog::getOptions() +{ + mbInverse = mxInverseCheckBox->get_active(); + mbPolar = mxPolarCheckBox->get_active(); + + sal_Int32 nDeciBels = static_cast<sal_Int32>(mxMinMagnitudeField->get_value()); + if (nDeciBels <= -150) + mfMinMag = 0.0; + else + mfMinMag = pow(10.0, static_cast<double>(nDeciBels) / 10.0); +} + +void ScFourierAnalysisDialog::getDataLabel(OUString& rLabel) +{ + if (mbWithLabels) + { + rLabel = "=" + + maLabelAddr.Format(mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D + : ScRefFlags::ADDR_ABS, + &mDocument, mAddressDetails); + + return; + } + + OUString aDataSrc(mInputRange.Format( + mDocument, mbUse3DAddresses ? ScRefFlags::RANGE_ABS_3D : ScRefFlags::RANGE_ABS, + mAddressDetails)); + + rLabel = ScResId(STR_INPUT_DATA_RANGE) + " : " + aDataSrc; + return; +} + +void ScFourierAnalysisDialog::genFormula(OUString& rFormula) +{ + static constexpr OUStringLiteral aSep(u";"); + + if (!mbPolar) + { + rFormula = "FOURIER(%INPUTRANGE%;" + OUString::boolean(mbGroupedByColumn) + aSep + + OUString::boolean(mbInverse) + ")"; + return; + } + + rFormula = "FOURIER(%INPUTRANGE%;" + OUString::boolean(mbGroupedByColumn) + aSep + + OUString::boolean(mbInverse) + ";true;" + OUString::number(mfMinMag) + ")"; +} + +IMPL_LINK_NOARG(ScFourierAnalysisDialog, CheckBoxHdl, weld::Toggleable&, void) +{ + ValidateDialogInput(); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/MatrixComparisonGenerator.cxx b/sc/source/ui/StatisticsDialogs/MatrixComparisonGenerator.cxx new file mode 100644 index 000000000..4345b816b --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/MatrixComparisonGenerator.cxx @@ -0,0 +1,113 @@ +/* -*- 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 <rangelst.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <MatrixComparisonGenerator.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +namespace +{ + void lclWriteCorrelationFormulas( + AddressWalkerWriter& aOutput, FormulaTemplate& aTemplate, + const ScRangeList& aRangeList, const OUString& aTemplateString) + { + for (size_t i = 0; i < aRangeList.size(); i++) + { + aOutput.resetRow(); + for (size_t j = 0; j < aRangeList.size(); j++) + { + if (j >= i) + { + aTemplate.setTemplate(aTemplateString); + aTemplate.applyRange(u"%VAR1%", aRangeList[i]); + aTemplate.applyRange(u"%VAR2%", aRangeList[j]); + aOutput.writeFormula(aTemplate.getTemplate()); + } + aOutput.nextRow(); + } + aOutput.nextColumn(); + } + } +} + +ScMatrixComparisonGenerator::ScMatrixComparisonGenerator( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData, + const OUString& rUiXmlDescription, + const OString& rID) + : ScStatisticsInputOutputDialog(pSfxBindings, pChildWindow, pParent, rViewData, rUiXmlDescription, rID) +{} + +ScMatrixComparisonGenerator::~ScMatrixComparisonGenerator() +{} + +TranslateId ScMatrixComparisonGenerator::GetUndoNameId() +{ + return STR_CORRELATION_UNDO_NAME; +} + +ScRange ScMatrixComparisonGenerator::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + SCTAB inTab = mInputRange.aStart.Tab(); + + ScRangeList aRangeList = (mGroupedBy == BY_COLUMN) ? + MakeColumnRangeList(inTab, mInputRange.aStart, mInputRange.aEnd) : + MakeRowRangeList(inTab, mInputRange.aStart, mInputRange.aEnd); + + // labels + output.writeString(getLabel()); + output.nextColumn(); + + static const OUStringLiteral strWildcardNumber(u"%NUMBER%"); + + // write labels to columns + for (size_t i = 0; i < aRangeList.size(); i++) + { + if (mGroupedBy == BY_COLUMN) + aTemplate.setTemplate(ScResId(STR_COLUMN_LABEL_TEMPLATE)); + else + aTemplate.setTemplate(ScResId(STR_ROW_LABEL_TEMPLATE)); + + aTemplate.applyNumber(strWildcardNumber, i + 1); + output.writeString(aTemplate.getTemplate()); + output.nextColumn(); + } + + // write labels to rows + output.resetColumn(); + output.nextRow(); + for (size_t i = 0; i < aRangeList.size(); i++) + { + if (mGroupedBy == BY_COLUMN) + aTemplate.setTemplate(ScResId(STR_COLUMN_LABEL_TEMPLATE)); + else + aTemplate.setTemplate(ScResId(STR_ROW_LABEL_TEMPLATE)); + + aTemplate.applyNumber(strWildcardNumber, i + 1); + output.writeString(aTemplate.getTemplate()); + output.nextRow(); + } + + // write correlation formulas + output.reset(); + output.push(1, 1); + + lclWriteCorrelationFormulas(output, aTemplate, aRangeList, getTemplate()); + + return ScRange(output.mMinimumAddress, output.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/MovingAverageDialog.cxx b/sc/source/ui/StatisticsDialogs/MovingAverageDialog.cxx new file mode 100644 index 000000000..9d990ed5a --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/MovingAverageDialog.cxx @@ -0,0 +1,116 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <MovingAverageDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScMovingAverageDialog::ScMovingAverageDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) + : ScStatisticsInputOutputDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/movingaveragedialog.ui", + "MovingAverageDialog") + , mxTrimRangeCheck(m_xBuilder->weld_check_button("trimrange-check")) + , mxIntervalSpin(m_xBuilder->weld_spin_button("interval-spin")) +{ +} + +ScMovingAverageDialog::~ScMovingAverageDialog() +{ +} + +void ScMovingAverageDialog::Close() +{ + DoClose( ScMovingAverageDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScMovingAverageDialog::GetUndoNameId() +{ + return STR_MOVING_AVERAGE_UNDO_NAME; +} + +ScRange ScMovingAverageDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + if (mxTrimRangeCheck->get_active()) + mDocument.GetDataAreaSubrange(mInputRange); + + std::unique_ptr<DataRangeIterator> pIterator; + if (mGroupedBy == BY_COLUMN) + pIterator.reset(new DataRangeByColumnIterator(mInputRange)); + else + pIterator.reset(new DataRangeByRowIterator(mInputRange)); + + sal_Int32 aIntervalSize = mxIntervalSpin->get_value(); + const bool aCentral = true; //to-do add support to change this to the dialog + + for( ; pIterator->hasNext(); pIterator->next() ) + { + output.resetRow(); + + // Write label + if (mGroupedBy == BY_COLUMN) + aTemplate.setTemplate(ScResId(STR_COLUMN_LABEL_TEMPLATE)); + else + aTemplate.setTemplate(ScResId(STR_ROW_LABEL_TEMPLATE)); + + aTemplate.applyNumber(u"%NUMBER%", pIterator->index() + 1); + output.writeBoldString(aTemplate.getTemplate()); + output.nextRow(); + + DataCellIterator aDataCellIterator = pIterator->iterateCells(); + std::vector<OUString> aFormulas; + + for (; aDataCellIterator.hasNext(); aDataCellIterator.next()) + { + ScAddress aIntervalStart; + ScAddress aIntervalEnd; + + if (aCentral) + { + sal_Int32 aHalf = aIntervalSize / 2; + sal_Int32 aHalfRemainder = aIntervalSize % 2; + aIntervalStart = aDataCellIterator.getRelative(-aHalf); + aIntervalEnd = aDataCellIterator.getRelative(aHalf - 1 + aHalfRemainder); + } + else + { + aIntervalStart = aDataCellIterator.getRelative(-aIntervalSize); + aIntervalEnd = aDataCellIterator.getRelative(0); + } + + if(aIntervalStart.IsValid() && aIntervalEnd.IsValid()) + { + aTemplate.setTemplate("=AVERAGE(%RANGE%)"); + aTemplate.applyRange(u"%RANGE%", ScRange(aIntervalStart, aIntervalEnd)); + aFormulas.push_back(aTemplate.getTemplate()); + } + else + { + aFormulas.push_back("=#N/A"); + } + } + + output.writeFormulas(aFormulas); + output.nextColumn(); + } + return ScRange(output.mMinimumAddress, output.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/RandomNumberGeneratorDialog.cxx b/sc/source/ui/StatisticsDialogs/RandomNumberGeneratorDialog.cxx new file mode 100644 index 000000000..91b43cbe0 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/RandomNumberGeneratorDialog.cxx @@ -0,0 +1,482 @@ +/* -*- 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 <svl/undo.hxx> +#include <rtl/math.hxx> +#include <osl/time.h> + +#include <rangelst.hxx> +#include <docsh.hxx> +#include <document.hxx> +#include <reffact.hxx> +#include <docfunc.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +#include <random> + +#include <RandomNumberGeneratorDialog.hxx> + +namespace +{ + +const sal_Int64 DIST_UNIFORM = 0; +const sal_Int64 DIST_NORMAL = 1; +const sal_Int64 DIST_CAUCHY = 2; +const sal_Int64 DIST_BERNOULLI = 3; +const sal_Int64 DIST_BINOMIAL = 4; +const sal_Int64 DIST_CHI_SQUARED = 5; +const sal_Int64 DIST_GEOMETRIC = 6; +const sal_Int64 DIST_NEGATIVE_BINOMIAL = 7; +const sal_Int64 DIST_UNIFORM_INTEGER = 8; + +const sal_Int64 PRECISION = 10000; +const sal_Int64 DIGITS = 4; + +} + +ScRandomNumberGeneratorDialog::ScRandomNumberGeneratorDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData) + : ScAnyRefDlgController(pSfxBindings, pChildWindow, pParent, + "modules/scalc/ui/randomnumbergenerator.ui", + "RandomNumberGeneratorDialog") + , mrViewData(rViewData) + , mrDoc(rViewData.GetDocument()) + , mbDialogLostFocus(false) + , mxInputRangeText(m_xBuilder->weld_label("cell-range-label")) + , mxInputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("cell-range-edit"))) + , mxInputRangeButton(new formula::RefButton(m_xBuilder->weld_button("cell-range-button"))) + , mxDistributionCombo(m_xBuilder->weld_combo_box("distribution-combo")) + , mxParameter1Text(m_xBuilder->weld_label("parameter1-label")) + , mxParameter1Value(m_xBuilder->weld_spin_button("parameter1-spin")) + , mxParameter2Text(m_xBuilder->weld_label("parameter2-label")) + , mxParameter2Value(m_xBuilder->weld_spin_button("parameter2-spin")) + , mxSeed(m_xBuilder->weld_spin_button("seed-spin")) + , mxEnableSeed(m_xBuilder->weld_check_button("enable-seed-check")) + , mxDecimalPlaces(m_xBuilder->weld_spin_button("decimal-places-spin")) + , mxEnableRounding(m_xBuilder->weld_check_button("enable-rounding-check")) + , mxButtonApply(m_xBuilder->weld_button("apply")) + , mxButtonOk(m_xBuilder->weld_button("ok")) + , mxButtonClose(m_xBuilder->weld_button("close")) +{ + mxInputRangeEdit->SetReferences(this, mxInputRangeText.get()); + mxInputRangeButton->SetReferences(this, mxInputRangeEdit.get()); + + Init(); + GetRangeFromSelection(); +} + +ScRandomNumberGeneratorDialog::~ScRandomNumberGeneratorDialog() +{ +} + +void ScRandomNumberGeneratorDialog::Init() +{ + mxButtonOk->connect_clicked( LINK( this, ScRandomNumberGeneratorDialog, OkClicked ) ); + mxButtonClose->connect_clicked( LINK( this, ScRandomNumberGeneratorDialog, CloseClicked ) ); + mxButtonApply->connect_clicked( LINK( this, ScRandomNumberGeneratorDialog, ApplyClicked ) ); + + mxInputRangeEdit->SetGetFocusHdl(LINK( this, ScRandomNumberGeneratorDialog, GetEditFocusHandler )); + mxInputRangeButton->SetGetFocusHdl(LINK( this, ScRandomNumberGeneratorDialog, GetButtonFocusHandler )); + + mxInputRangeEdit->SetLoseFocusHdl (LINK( this, ScRandomNumberGeneratorDialog, LoseEditFocusHandler )); + mxInputRangeButton->SetLoseFocusHdl (LINK( this, ScRandomNumberGeneratorDialog, LoseButtonFocusHandler )); + + mxInputRangeEdit->SetModifyHdl( LINK( this, ScRandomNumberGeneratorDialog, InputRangeModified )); + mxParameter1Value->connect_value_changed( LINK( this, ScRandomNumberGeneratorDialog, Parameter1ValueModified )); + mxParameter2Value->connect_value_changed( LINK( this, ScRandomNumberGeneratorDialog, Parameter2ValueModified )); + + mxDistributionCombo->connect_changed( LINK( this, ScRandomNumberGeneratorDialog, DistributionChanged )); + + mxEnableSeed->connect_toggled( LINK( this, ScRandomNumberGeneratorDialog, CheckChanged )); + mxEnableRounding->connect_toggled( LINK( this, ScRandomNumberGeneratorDialog, CheckChanged )); + + DistributionChanged(*mxDistributionCombo); + CheckChanged(*mxEnableSeed); +} + +void ScRandomNumberGeneratorDialog::GetRangeFromSelection() +{ + mrViewData.GetSimpleArea(maInputRange); + OUString aCurrentString(maInputRange.Format(mrDoc, ScRefFlags::RANGE_ABS_3D, mrDoc.GetAddressConvention())); + mxInputRangeEdit->SetText( aCurrentString ); +} + +void ScRandomNumberGeneratorDialog::SetActive() +{ + if ( mbDialogLostFocus ) + { + mbDialogLostFocus = false; + if( mxInputRangeEdit ) + mxInputRangeEdit->GrabFocus(); + } + else + { + m_xDialog->grab_focus(); + } + RefInputDone(); +} + +void ScRandomNumberGeneratorDialog::Close() +{ + DoClose( ScRandomNumberGeneratorDialogWrapper::GetChildWindowId() ); +} + +void ScRandomNumberGeneratorDialog::SetReference( const ScRange& rReferenceRange, ScDocument& rDoc ) +{ + if (!mxInputRangeEdit->GetWidget()->get_sensitive()) + return; + + if ( rReferenceRange.aStart != rReferenceRange.aEnd ) + RefInputStart(mxInputRangeEdit.get()); + + maInputRange = rReferenceRange; + + OUString aReferenceString(maInputRange.Format(rDoc, ScRefFlags::RANGE_ABS_3D, rDoc.GetAddressConvention())); + mxInputRangeEdit->SetRefString( aReferenceString ); + + mxButtonApply->set_sensitive(true); + mxButtonOk->set_sensitive(true); +} + +void ScRandomNumberGeneratorDialog::SelectGeneratorAndGenerateNumbers() +{ + if (!maInputRange.IsValid()) + return; + + sal_Int64 aSelectedId = mxDistributionCombo->get_active_id().toInt64(); + + sal_uInt32 seedValue; + + if( mxEnableSeed->get_active() ) + { + seedValue = mxSeed->get_value(); + } + else + { + TimeValue now; + osl_getSystemTime(&now); + seedValue = now.Nanosec; + } + + std::mt19937 seed(seedValue); + + sal_Int64 parameterInteger1 = mxParameter1Value->get_value(); + sal_Int64 parameterInteger2 = mxParameter2Value->get_value(); + + double parameter1 = parameterInteger1 / static_cast<double>(PRECISION); + double parameter2 = parameterInteger2 / static_cast<double>(PRECISION); + + std::optional<sal_Int8> aDecimalPlaces; + if (mxEnableRounding->get_active()) + { + aDecimalPlaces = static_cast<sal_Int8>(mxDecimalPlaces->get_value()); + } + + switch(aSelectedId) + { + case DIST_UNIFORM: + { + std::uniform_real_distribution<> distribution(parameter1, parameter2); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_UNIFORM_REAL, aDecimalPlaces); + break; + } + case DIST_UNIFORM_INTEGER: + { + std::uniform_int_distribution<sal_Int64> distribution(parameterInteger1, parameterInteger2); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_UNIFORM_INTEGER, aDecimalPlaces); + break; + } + case DIST_NORMAL: + { + std::normal_distribution<> distribution(parameter1, parameter2); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_NORMAL, aDecimalPlaces); + break; + } + case DIST_CAUCHY: + { + std::cauchy_distribution<> distribution(parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_CAUCHY, aDecimalPlaces); + break; + } + case DIST_BERNOULLI: + { + std::bernoulli_distribution distribution(parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_BERNOULLI, aDecimalPlaces); + break; + } + case DIST_BINOMIAL: + { + std::binomial_distribution<> distribution(parameterInteger2, parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_BINOMIAL, aDecimalPlaces); + break; + } + case DIST_NEGATIVE_BINOMIAL: + { + std::negative_binomial_distribution<> distribution(parameterInteger2, parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_NEGATIVE_BINOMIAL, aDecimalPlaces); + break; + } + case DIST_CHI_SQUARED: + { + std::chi_squared_distribution<> distribution(parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_CHI_SQUARED, aDecimalPlaces); + break; + } + case DIST_GEOMETRIC: + { + std::geometric_distribution<> distribution(parameter1); + auto rng = std::bind(distribution, seed); + GenerateNumbers(rng, STR_DISTRIBUTION_GEOMETRIC, aDecimalPlaces); + break; + } + } +} + +template<class RNG> +void ScRandomNumberGeneratorDialog::GenerateNumbers(RNG& randomGenerator, TranslateId pDistributionStringId, std::optional<sal_Int8> aDecimalPlaces) +{ + OUString aUndo = ScResId(STR_UNDO_DISTRIBUTION_TEMPLATE); + OUString aDistributionName = ScResId(pDistributionStringId); + aUndo = aUndo.replaceAll("$(DISTRIBUTION)", aDistributionName); + + ScDocShell* pDocShell = mrViewData.GetDocShell(); + SfxUndoManager* pUndoManager = pDocShell->GetUndoManager(); + pUndoManager->EnterListAction( aUndo, aUndo, 0, mrViewData.GetViewShell()->GetViewShellId() ); + + SCROW nRowStart = maInputRange.aStart.Row(); + SCROW nRowEnd = maInputRange.aEnd.Row(); + SCCOL nColStart = maInputRange.aStart.Col(); + SCCOL nColEnd = maInputRange.aEnd.Col(); + SCTAB nTabStart = maInputRange.aStart.Tab(); + SCTAB nTabEnd = maInputRange.aEnd.Tab(); + + std::vector<double> aVals; + aVals.reserve(nRowEnd - nRowStart + 1); + + for (SCROW nTab = nTabStart; nTab <= nTabEnd; ++nTab) + { + for (SCCOL nCol = nColStart; nCol <= nColEnd; ++nCol) + { + aVals.clear(); + + ScAddress aPos(nCol, nRowStart, nTab); + for (SCROW nRow = nRowStart; nRow <= nRowEnd; ++nRow) + { + + if (aDecimalPlaces) + aVals.push_back(rtl::math::round(randomGenerator(), *aDecimalPlaces)); + else + aVals.push_back(randomGenerator()); + } + + pDocShell->GetDocFunc().SetValueCells(aPos, aVals, true); + } + } + + pUndoManager->LeaveListAction(); + + pDocShell->PostPaint( maInputRange, PaintPartFlags::Grid ); +} + +IMPL_LINK_NOARG( ScRandomNumberGeneratorDialog, OkClicked, weld::Button&, void ) +{ + ApplyClicked(*mxButtonApply); + CloseClicked(*mxButtonClose); +} + +IMPL_LINK_NOARG( ScRandomNumberGeneratorDialog, ApplyClicked, weld::Button&, void ) +{ + SelectGeneratorAndGenerateNumbers(); +} + +IMPL_LINK_NOARG( ScRandomNumberGeneratorDialog, CloseClicked, weld::Button&, void ) +{ + response(RET_CLOSE); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, GetEditFocusHandler, formula::RefEdit&, void) +{ + mxInputRangeEdit->SelectAll(); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, GetButtonFocusHandler, formula::RefButton&, void) +{ + mxInputRangeEdit->SelectAll(); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, LoseEditFocusHandler, formula::RefEdit&, void) +{ + mbDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, LoseButtonFocusHandler, formula::RefButton&, void) +{ + mbDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, InputRangeModified, formula::RefEdit&, void) +{ + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxInputRangeEdit->GetText(), mrDoc); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + maInputRange = *pRange; + mxButtonApply->set_sensitive(true); + mxButtonOk->set_sensitive(true); + // Highlight the resulting range. + mxInputRangeEdit->StartUpdateData(); + } + else + { + maInputRange = ScRange( ScAddress::INITIALIZE_INVALID); + mxButtonApply->set_sensitive(false); + mxButtonOk->set_sensitive(false); + } +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, Parameter1ValueModified, weld::SpinButton&, void) +{ + sal_Int64 aSelectedId = mxDistributionCombo->get_active_id().toInt64(); + if (aSelectedId == DIST_UNIFORM || + aSelectedId == DIST_UNIFORM_INTEGER) + { + sal_Int64 min = mxParameter1Value->get_value(); + sal_Int64 max = mxParameter2Value->get_value(); + if(min > max) + { + mxParameter2Value->set_value(min); + } + } +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, Parameter2ValueModified, weld::SpinButton&, void) +{ + sal_Int64 aSelectedId = mxDistributionCombo->get_active_id().toInt64(); + if (aSelectedId == DIST_UNIFORM || + aSelectedId == DIST_UNIFORM_INTEGER) + { + sal_Int64 min = mxParameter1Value->get_value(); + sal_Int64 max = mxParameter2Value->get_value(); + if(min > max) + { + mxParameter1Value->set_value(max); + } + } +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, CheckChanged, weld::Toggleable&, void) +{ + mxSeed->set_sensitive(mxEnableSeed->get_active()); + mxDecimalPlaces->set_sensitive(mxEnableRounding->get_active()); +} + +IMPL_LINK_NOARG(ScRandomNumberGeneratorDialog, DistributionChanged, weld::ComboBox&, void) +{ + sal_Int64 aSelectedId = mxDistributionCombo->get_active_id().toInt64(); + + mxParameter1Value->set_range(SAL_MIN_INT32, SAL_MAX_INT32); + mxParameter2Value->set_range(SAL_MIN_INT32, SAL_MAX_INT32); + + mxParameter1Value->set_digits(DIGITS); + mxParameter1Value->set_increments(PRECISION, PRECISION * 10); + + mxParameter2Value->set_digits(DIGITS); + mxParameter2Value->set_increments(PRECISION, PRECISION * 10); + + switch(aSelectedId) + { + case DIST_UNIFORM: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_MINIMUM)); + mxParameter2Text->set_label(ScResId(STR_RNG_PARAMETER_MAXIMUM)); + mxParameter2Text->show(); + mxParameter2Value->show(); + break; + } + case DIST_UNIFORM_INTEGER: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_MINIMUM)); + mxParameter1Value->set_digits(0); + mxParameter1Value->set_increments(1, 10); + + mxParameter2Text->set_label(ScResId(STR_RNG_PARAMETER_MAXIMUM)); + mxParameter2Value->set_digits(0); + mxParameter2Value->set_increments(1, 10); + + mxParameter2Text->show(); + mxParameter2Value->show(); + break; + } + case DIST_NORMAL: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_MEAN)); + mxParameter2Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_DEVIATION)); + mxParameter2Text->show(); + mxParameter2Value->show(); + break; + } + case DIST_CAUCHY: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_MEDIAN)); + mxParameter2Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_SIGMA)); + mxParameter2Text->show(); + mxParameter2Value->show(); + break; + } + case DIST_BERNOULLI: + case DIST_GEOMETRIC: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_PROBABILITY)); + mxParameter1Value->set_range(0, PRECISION); + mxParameter1Value->set_increments(1000, 10000); + + mxParameter2Text->hide(); + mxParameter2Value->hide(); + break; + } + case DIST_BINOMIAL: + case DIST_NEGATIVE_BINOMIAL: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_PROBABILITY)); + mxParameter1Value->set_range(0, PRECISION); + mxParameter1Value->set_increments(1000, 10000); + + mxParameter2Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_NUMBER_OF_TRIALS)); + mxParameter2Value->set_digits(0); + mxParameter2Value->set_increments(1, 10); + mxParameter2Value->set_min(0); + + mxParameter2Text->show(); + mxParameter2Value->show(); + break; + } + case DIST_CHI_SQUARED: + { + mxParameter1Text->set_label(ScResId(STR_RNG_PARAMETER_STANDARD_NU_VALUE)); + + mxParameter2Text->hide(); + mxParameter2Value->hide(); + break; + } + } +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx new file mode 100644 index 000000000..ad4adb1fe --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx @@ -0,0 +1,696 @@ +/* -*- 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 <sal/config.h> + +#include <string_view> + +#include <document.hxx> +#include <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <RegressionDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +/* + Some regression basics + ---------------------- + + 1. Linear regression fits using data, a linear function between the dependent variable and the independent variable(s). + The basic form of this function is :- + + y = b + m_1*x_1 + m_2*x_2 + ... + m_k*x_k + + where y is the dependent variable + x_1, x_2, ..., x_k are the k independent variables + b is the intercept + m_1, m_2, ..., m_k are the slopes corresponding to the variables x_1, x_2, ..., x_k respectively. + + + This equation for n observations can be compactly written using matrices as :- + + y = X*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ 1 x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ] + + Calc formula LINEST(Y_array ; X_array) can be used to compute all entries in "A" along with many other statistics. + + + 2. Logarithmic regression is basically used to find a linear function between the dependent variable and + the natural logarithm of the independent variable(s). + So the basic form of this functions is :- + + y = b + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) + + This can be again written in a compact matrix form for n observations. + + y = ln(X)*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ] + + To estimate A, we use the formula =LINEST(Y_array ; LN(X_array)) + + + 3. Power regression is used to fit the following model :- + + y = b * (x_1 ^ m_1) * (x_2 ^ m_2) * ... * (x_k ^ m_k) + + To reduce this to a linear function(so that we can still use LINEST()), we take natural logarithm on both sides + + ln(y) = c + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) ; where c = ln(b) + + + This again can be written compactly in matrix form as :- + + ln(y) = ln(X)*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ c m_1 m_2 ... m_k ] + + To estimate A, we use the formula =LINEST(LN(Y_array) ; LN(X_array)) + + Once we get A, to get back y from x's we use the formula :- + + y = exp( ln(X)*A ) + + + + Some references for computing confidence interval for the regression coefficients :- + + [1] https://en.wikipedia.org/wiki/Student%27s_t-test#Slope_of_a_regression_line + [2] https://en.wikipedia.org/wiki/Simple_linear_regression#Normality_assumption + [3] https://onlinecourses.science.psu.edu/stat414/node/280 + + */ + +namespace +{ + enum class ScRegType { + LINEAR, + LOGARITHMIC, + POWER + }; + + const TranslateId constRegressionModel[] = + { + STR_LABEL_LINEAR, + STR_LABEL_LOGARITHMIC, + STR_LABEL_POWER + }; + + OUString constTemplateLINEST[] = + { + "=LINEST(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE% ; %CALC_INTERCEPT% ; TRUE)", + "=LINEST(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%) ; %CALC_INTERCEPT% ; TRUE)", + "=LINEST(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%) ; %CALC_INTERCEPT% ; TRUE)" + }; + + OUString constRegressionFormula[] = + { + "=MMULT(%XDATAMATRIX_RANGE% ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%", + "=MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%", + "=EXP(MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%)" + }; + +} // end anonymous namespace + +static size_t lcl_GetNumRowsColsInRange(const ScRange& rRange, bool bRows) +{ + if (bRows) + return rRange.aEnd.Row() - rRange.aStart.Row() + 1; + + return rRange.aEnd.Col() - rRange.aStart.Col() + 1; +} + +ScRegressionDialog::ScRegressionDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) + : ScStatisticsTwoVariableDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/regressiondialog.ui", "RegressionDialog") + , mbUnivariate(true) + , mnNumIndependentVars(1) + , mnNumObservations(0) + , mbUse3DAddresses(false) + , mbCalcIntercept(true) + , mxWithLabelsCheckBox(m_xBuilder->weld_check_button("withlabels-check")) + , mxLinearRadioButton(m_xBuilder->weld_radio_button("linear-radio")) + , mxLogarithmicRadioButton(m_xBuilder->weld_radio_button("logarithmic-radio")) + , mxPowerRadioButton(m_xBuilder->weld_radio_button("power-radio")) + , mxErrorMessage(m_xBuilder->weld_label("error-message")) + , mxConfidenceLevelField(m_xBuilder->weld_spin_button("confidencelevel-spin")) + , mxCalcResidualsCheckBox(m_xBuilder->weld_check_button("calcresiduals-check")) + , mxNoInterceptCheckBox(m_xBuilder->weld_check_button("nointercept-check")) +{ + mxWithLabelsCheckBox->connect_toggled(LINK(this, ScRegressionDialog, CheckBoxHdl)); + mxConfidenceLevelField->connect_value_changed(LINK(this, ScRegressionDialog, NumericFieldHdl)); +} + +ScRegressionDialog::~ScRegressionDialog() +{ +} + +void ScRegressionDialog::Close() +{ + DoClose(ScRegressionDialogWrapper::GetChildWindowId()); +} + +TranslateId ScRegressionDialog::GetUndoNameId() +{ + return STR_REGRESSION_UNDO_NAME; +} + +ScRange ScRegressionDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + aTemplate.autoReplaceUses3D(mbUse3DAddresses); + mbCalcIntercept = !mxNoInterceptCheckBox->get_active(); + + // max col of our output should account for + // 1. constant term column, + // 2. mnNumIndependentVars columns + // 3. Actual Y column + // 4. Predicted Y column + // 5. Residual Column + SCCOL nOutputMaxCol = mOutputAddress.Col() + mnNumIndependentVars + 3; + + ScRange aXDataRange(GetDataRange(mVariable1Range)); + ScRange aYDataRange(GetDataRange(mVariable2Range)); + + aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", aXDataRange); + aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", aYDataRange); + size_t nRegressionIndex = GetRegressionTypeIndex(); + ScRegType eRegType = static_cast<ScRegType>(nRegressionIndex); + bool bTakeLogX = eRegType == ScRegType::LOGARITHMIC || eRegType == ScRegType::POWER; + + WriteRawRegressionResults(aOutput, aTemplate, nRegressionIndex); + WriteRegressionStatistics(aOutput, aTemplate); + WriteRegressionANOVAResults(aOutput, aTemplate); + WriteRegressionEstimatesWithCI(aOutput, aTemplate, bTakeLogX); + if (mxCalcResidualsCheckBox->get_active()) + WritePredictionsWithResiduals(aOutput, aTemplate, nRegressionIndex); + + ScAddress aMaxAddress(aOutput.mMaximumAddress); + aMaxAddress.SetCol(std::max(aMaxAddress.Col(), nOutputMaxCol)); + return ScRange(aOutput.mMinimumAddress, aMaxAddress); +} + +bool ScRegressionDialog::InputRangesValid() +{ + if (!mVariable1Range.IsValid()) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_XINVALID_RANGE)); + return false; + } + + if (!mVariable2Range.IsValid()) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_YINVALID_RANGE)); + return false; + } + + if (!mOutputAddress.IsValid()) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_OUTPUT_ADDR)); + return false; + } + + { + double fConfidenceLevel = mxConfidenceLevelField->get_value(); + if ( fConfidenceLevel <= 0.0 || fConfidenceLevel >= 100.0 ) + { + mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_CONFIDENCE_LEVEL)); + return false; + } + } + + mVariable1Range.PutInOrder(); + mVariable2Range.PutInOrder(); + + bool bGroupedByColumn = mGroupedBy == BY_COLUMN; + + bool bYHasSingleDim = ( + (bGroupedByColumn && + mVariable2Range.aStart.Col() == mVariable2Range.aEnd.Col()) || + (!bGroupedByColumn && + mVariable2Range.aStart.Row() == mVariable2Range.aEnd.Row())); + + if (!bYHasSingleDim) + { + if (bGroupedByColumn) + mxErrorMessage->set_label(ScResId(STR_MESSAGE_YVARIABLE_MULTI_COLUMN)); + else + mxErrorMessage->set_label(ScResId(STR_MESSAGE_YVARIABLE_MULTI_ROW)); + return false; + } + + bool bWithLabels = mxWithLabelsCheckBox->get_active(); + + size_t nYObs = lcl_GetNumRowsColsInRange(mVariable2Range, bGroupedByColumn); + size_t nNumXVars = lcl_GetNumRowsColsInRange(mVariable1Range, !bGroupedByColumn); + mbUnivariate = nNumXVars == 1; + // Observation count mismatch check + if (lcl_GetNumRowsColsInRange(mVariable1Range, bGroupedByColumn) != nYObs) + { + if (mbUnivariate) + mxErrorMessage->set_label(ScResId(STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH)); + else + mxErrorMessage->set_label(ScResId(STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH)); + return false; + } + + mnNumIndependentVars = nNumXVars; + mnNumObservations = bWithLabels ? nYObs - 1 : nYObs; + + mbUse3DAddresses = mVariable1Range.aStart.Tab() != mOutputAddress.Tab() || + mVariable2Range.aStart.Tab() != mOutputAddress.Tab(); + + mxErrorMessage->set_label(""); + + return true; +} + +size_t ScRegressionDialog::GetRegressionTypeIndex() const +{ + if (mxLinearRadioButton->get_active()) + return 0; + if (mxLogarithmicRadioButton->get_active()) + return 1; + return 2; +} + +ScRange ScRegressionDialog::GetDataRange(const ScRange& rRange) +{ + if (!mxWithLabelsCheckBox->get_active()) + return rRange; + + ScRange aDataRange(rRange); + if (mGroupedBy == BY_COLUMN) + aDataRange.aStart.IncRow(1); + else + aDataRange.aStart.IncCol(1); + + return aDataRange; +} + +OUString ScRegressionDialog::GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog) +{ + if (bXVar && nIndex == 0) + return "=\"" + ScResId(STR_LABEL_INTERCEPT) + "\""; + + if (mxWithLabelsCheckBox->get_active()) + { + ScAddress aAddr(bXVar ? mVariable1Range.aStart : mVariable2Range.aStart); + if (mGroupedBy == BY_COLUMN) + aAddr.IncCol(nIndex - 1); + else + aAddr.IncRow(nIndex - 1); + + ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS; + return bWithLog ? OUString("=CONCAT(\"LN(\";" + + aAddr.Format(eAddrFlag, &mDocument, mDocument.GetAddressConvention()) + ";\")\")") : + OUString("=" + aAddr.Format(eAddrFlag, &mDocument, mDocument.GetAddressConvention())); + } + + OUString aDefaultVarName; + + if (bXVar) + aDefaultVarName = "X" + OUString::number(nIndex); + else + aDefaultVarName = "Y"; + + return bWithLog ? OUString("=\"LN(" + aDefaultVarName + ")\"") : + OUString("=\"" + aDefaultVarName + "\""); +} + +OUString ScRegressionDialog::GetXVariableNameFormula(size_t nIndex, bool bWithLog) +{ + assert(nIndex <= mnNumIndependentVars); + return GetVariableNameFormula(true, nIndex, bWithLog); +} + +OUString ScRegressionDialog::GetYVariableNameFormula(bool bWithLog) +{ + return GetVariableNameFormula(false, 1, bWithLog); +} + +void ScRegressionDialog::WriteRawRegressionResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + size_t nRegressionIndex) +{ + rOutput.writeBoldString(ScResId(STR_REGRESSION)); + rOutput.newLine(); + // REGRESSION MODEL + rOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL)); + rOutput.nextColumn(); + rOutput.writeString(ScResId(constRegressionModel[nRegressionIndex])); + rOutput.newLine(); + rOutput.newLine(); + + rOutput.writeString(ScResId(STR_LINEST_RAW_OUTPUT_TITLE)); + rOutput.newLine(); + rOutput.push(); + + rTemplate.setTemplate(constTemplateLINEST[nRegressionIndex]. + replaceFirst("%CALC_INTERCEPT%", + mbCalcIntercept ? std::u16string_view(u"TRUE") : std::u16string_view(u"FALSE"))); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1 + mnNumIndependentVars, 5); + // Add LINEST result components to template + // 1. Add ranges for coefficients and standard errors for indep. vars and the intercept. + // Note that these two are in the reverse order(m_n, m_n-1, ..., m_1, b) w.r.t what we expect. + rTemplate.autoReplaceRange("%COEFFICIENTS_REV_RANGE%", ScRange(rOutput.current(), rOutput.current(mnNumIndependentVars))); + rTemplate.autoReplaceRange("%SERRORSX_REV_RANGE%", ScRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars, 1))); + + // 2. Add R-squared and standard error for y estimate. + rTemplate.autoReplaceAddress("%RSQUARED_ADDR%", rOutput.current(0, 2)); + rTemplate.autoReplaceAddress("%SERRORY_ADDR%", rOutput.current(1, 2)); + + // 3. Add F statistic and degrees of freedom + rTemplate.autoReplaceAddress("%FSTATISTIC_ADDR%", rOutput.current(0, 3)); + rTemplate.autoReplaceAddress("%DoFRESID_ADDR%", rOutput.current(1, 3)); + + // 4. Add regression sum of squares and residual sum of squares + rTemplate.autoReplaceAddress("%SSREG_ADDR%", rOutput.current(0, 4)); + rTemplate.autoReplaceAddress("%SSRESID_ADDR%", rOutput.current(1, 4)); + + rOutput.push(0, 4); + rOutput.newLine(); +} + +void ScRegressionDialog::WriteRegressionStatistics(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate) +{ + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_REGRESSION_STATISTICS)); + rOutput.newLine(); + + const TranslateId aMeasureNames[] = + { + STR_LABEL_RSQUARED, + STRID_CALC_STD_ERROR, + STR_LABEL_XVARIABLES_COUNT, + STR_OBSERVATIONS_LABEL, + STR_LABEL_ADJUSTED_RSQUARED + }; + + OUString aMeasureFormulas[] = + { + "=%RSQUARED_ADDR%", + "=%SERRORY_ADDR%", + "=" + OUString::number(mnNumIndependentVars), + "=" + OUString::number(mnNumObservations), + OUString::Concat( + "=1 - (1 - %RSQUARED_ADDR%)*(%NUMOBS_ADDR% - 1)/(%NUMOBS_ADDR% - %NUMXVARS_ADDR%") + + (mbCalcIntercept ? std::u16string_view(u" - 1)") : std::u16string_view(u")")) + }; + + rTemplate.autoReplaceAddress("%NUMXVARS_ADDR%", rOutput.current(1, 2)); + rTemplate.autoReplaceAddress("%NUMOBS_ADDR%", rOutput.current(1, 3)); + + for (size_t nIdx = 0; nIdx < SAL_N_ELEMENTS(aMeasureNames); ++nIdx) + { + rOutput.writeString(ScResId(aMeasureNames[nIdx])); + rOutput.nextColumn(); + rTemplate.setTemplate(aMeasureFormulas[nIdx]); + rOutput.writeFormula(rTemplate.getTemplate()); + rOutput.newLine(); + } +} + +void ScRegressionDialog::WriteRegressionANOVAResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate) +{ + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_ANOVA)); + rOutput.newLine(); + + const size_t nColsInTable = 6; + const size_t nRowsInTable = 4; + OUString aTable[nRowsInTable][nColsInTable] = + { + { + "", + ScResId(STR_ANOVA_LABEL_DF), + ScResId(STR_ANOVA_LABEL_SS), + ScResId(STR_ANOVA_LABEL_MS), + ScResId(STR_ANOVA_LABEL_F), + ScResId(STR_ANOVA_LABEL_SIGNIFICANCE_F) + }, + { + ScResId(STR_REGRESSION), + "=%NUMXVARS_ADDR%", + "=%SSREG_ADDR%", + "=%SSREG_ADDR% / %DoFREG_ADDR%", + "=%FSTATISTIC_ADDR%", + "=FDIST(%FSTATISTIC_ADDR% ; %DoFREG_ADDR% ; %DoFRESID_ADDR%)" + }, + { + ScResId(STR_LABEL_RESIDUAL), + "=%DoFRESID_ADDR%", + "=%SSRESID_ADDR%", + "=%SSRESID_ADDR% / %DoFRESID_ADDR%", + "", + "" + }, + { + ScResId(STR_ANOVA_LABEL_TOTAL), + "=%DoFREG_ADDR% + %DoFRESID_ADDR%", + "=%SSREG_ADDR% + %SSRESID_ADDR%", + "", + "", + "" + } + }; + + rTemplate.autoReplaceAddress("%DoFREG_ADDR%", rOutput.current(1, 1)); + + // Cell getter lambda + std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString& + { + return aTable[nRowIdx][nColIdx]; + }; + + // Cell writer lambda + std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate] + (const OUString& rContent, size_t /*nRowIdx*/, size_t /*nColIdx*/) + { + if (!rContent.isEmpty()) + { + if (rContent.startsWith("=")) + { + rTemplate.setTemplate(rContent); + rOutput.writeFormula(rTemplate.getTemplate()); + } + else + rOutput.writeString(rContent); + } + }; + + WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc); + + // User given confidence level + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_CONFIDENCE_LEVEL)); + rOutput.nextColumn(); + rOutput.writeValue(mxConfidenceLevelField->get_value() / 100.0); + rTemplate.autoReplaceAddress("%CONFIDENCE_LEVEL_ADDR%", rOutput.current()); + rOutput.newLine(); +} + +// Write slopes, intercept, their standard errors, t-statistics, p-value, confidence intervals +void ScRegressionDialog::WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + bool bTakeLogX) +{ + rOutput.newLine(); + ScAddress aEnd( rOutput.current(0, 1 + mnNumIndependentVars)); + ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS; + aEnd.IncCol(); + const OUString aCoeffAddr( aEnd.Format( eAddrFlag, &mDocument, mDocument.GetAddressConvention())); + aEnd.IncCol(); + const OUString aStErrAddr( aEnd.Format( eAddrFlag, &mDocument, mDocument.GetAddressConvention())); + + // Coefficients & Std.Errors ranges (column vectors) in this table (yet to populate). + rTemplate.autoReplaceRange("%COEFFICIENTS_RANGE%", + ScRange(rOutput.current(1, 1), + rOutput.current(1, 1 + mnNumIndependentVars))); + rTemplate.autoReplaceRange("%SLOPES_RANGE%", // Excludes the intercept + ScRange(rOutput.current(1, 2), + rOutput.current(1, 1 + mnNumIndependentVars))); + rTemplate.autoReplaceAddress("%INTERCEPT_ADDR%", rOutput.current(1, 1)); + rTemplate.autoReplaceRange("%SERRORSX_RANGE%", + ScRange(rOutput.current(2, 1), + rOutput.current(2, 1 + mnNumIndependentVars))); + // t-Statistics range in this table (yet to populate) + rTemplate.autoReplaceRange("%TSTAT_RANGE%", + ScRange(rOutput.current(3, 1), + rOutput.current(3, 1 + mnNumIndependentVars))); + + const size_t nColsInTable = 7; + const size_t nRowsInTable = 2; + OUString aTable[nRowsInTable][nColsInTable] = + { + { + "", + ScResId(STR_LABEL_COEFFICIENTS), + ScResId(STRID_CALC_STD_ERROR), + ScResId(STR_LABEL_TSTATISTIC), + ScResId(STR_P_VALUE_LABEL), + + "=CONCAT(\"" + ScResId(STR_LABEL_LOWER) + + " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")", + + "=CONCAT(\"" + ScResId(STR_LABEL_UPPER) + + " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")", + }, + + // Following are matrix formulas of size numcols = 1, numrows = (mnNumIndependentVars + 1) + { + "", + // This puts the coefficients in the reverse order compared to that in LINEST output. + "=INDEX(%COEFFICIENTS_REV_RANGE%; 1 ; ROW(" + aCoeffAddr + ")+1 - ROW())", + // This puts the standard errors in the reverse order compared to that in LINEST output. + "=INDEX(%SERRORSX_REV_RANGE%; 1 ; ROW(" + aStErrAddr + ")+1 - ROW())", + // t-Statistic + "=%COEFFICIENTS_RANGE% / %SERRORSX_RANGE%", + // p-Value + "=TDIST(ABS(%TSTAT_RANGE%) ; %DoFRESID_ADDR% ; 2 )", + // Lower limit of confidence interval + "=%COEFFICIENTS_RANGE% - %SERRORSX_RANGE% * " + "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)", + // Upper limit of confidence interval + "=%COEFFICIENTS_RANGE% + %SERRORSX_RANGE% * " + "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)" + } + }; + + // Cell getter lambda + std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString& + { + return aTable[nRowIdx][nColIdx]; + }; + + // Cell writer lambda + size_t nNumIndependentVars = mnNumIndependentVars; + std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate, nNumIndependentVars] + (const OUString& rContent, size_t nRowIdx, size_t /*nColIdx*/) + { + if (!rContent.isEmpty()) + { + if (rContent.startsWith("=")) + { + rTemplate.setTemplate(rContent); + if (nRowIdx == 0) + rOutput.writeFormula(rTemplate.getTemplate()); + else + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, 1 + nNumIndependentVars); + } + else + rOutput.writeString(rContent); + } + }; + + WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc); + + // Go back to the second row and first column of the table to + // fill the names of variables + intercept + rOutput.push(0, -1); + + for (size_t nXvarIdx = 0; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx) + { + rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, bTakeLogX)); + rOutput.newLine(); + } + +} + +// Re-write all observations in group-by column mode with predictions and residuals +void ScRegressionDialog::WritePredictionsWithResiduals(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + size_t nRegressionIndex) +{ + bool bGroupedByColumn = mGroupedBy == BY_COLUMN; + rOutput.newLine(); + rOutput.push(); + + // Range of X variables with rows as observations and columns as variables. + ScRange aDataMatrixRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars - 1, mnNumObservations)); + rTemplate.autoReplaceRange("%XDATAMATRIX_RANGE%", aDataMatrixRange); + + // Write X variable names + for (size_t nXvarIdx = 1; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx) + { + // Here we write the X variables without any transformation(LN) + rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, false)); + rOutput.nextColumn(); + } + rOutput.reset(); + + // Write the X data matrix + rOutput.nextRow(); + OUString aDataMatrixFormula = bGroupedByColumn ? OUString("=%VARIABLE1_RANGE%") : OUString("=TRANSPOSE(%VARIABLE1_RANGE%)"); + rTemplate.setTemplate(aDataMatrixFormula); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), mnNumIndependentVars, mnNumObservations); + + // Write predicted values + rOutput.push(mnNumIndependentVars, -1); + rOutput.writeString(ScResId(STR_LABEL_PREDICTEDY)); + rOutput.nextRow(); + rTemplate.setTemplate(constRegressionFormula[nRegressionIndex]); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); + rTemplate.autoReplaceRange("%PREDICTEDY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1))); + + // Write actual Y + rOutput.push(1, -1); + rOutput.writeFormula(GetYVariableNameFormula(false)); + rOutput.nextRow(); + OUString aYVectorFormula = bGroupedByColumn ? OUString("=%VARIABLE2_RANGE%") : OUString("=TRANSPOSE(%VARIABLE2_RANGE%)"); + rTemplate.setTemplate(aYVectorFormula); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); + rTemplate.autoReplaceRange("%ACTUALY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1))); + + // Write residual + rOutput.push(1, -1); + rOutput.writeString(ScResId(STR_LABEL_RESIDUAL)); + rOutput.nextRow(); + rTemplate.setTemplate("=%ACTUALY_RANGE% - %PREDICTEDY_RANGE%"); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); +} + +// Generic table writer +void ScRegressionDialog::WriteTable(const std::function<CellValueGetter>& rCellGetter, + size_t nRowsInTable, size_t nColsInTable, + AddressWalkerWriter& rOutput, + const std::function<CellWriter>& rFunc) +{ + for (size_t nRowIdx = 0; nRowIdx < nRowsInTable; ++nRowIdx) + { + for (size_t nColIdx = 0; nColIdx < nColsInTable; ++nColIdx) + { + rFunc(rCellGetter(nRowIdx, nColIdx), nRowIdx, nColIdx); + rOutput.nextColumn(); + } + rOutput.newLine(); + } +} + +IMPL_LINK_NOARG(ScRegressionDialog, CheckBoxHdl, weld::Toggleable&, void) +{ + ValidateDialogInput(); +} + +IMPL_LINK_NOARG(ScRegressionDialog, NumericFieldHdl, weld::SpinButton&, void) +{ + ValidateDialogInput(); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/SamplingDialog.cxx b/sc/source/ui/StatisticsDialogs/SamplingDialog.cxx new file mode 100644 index 000000000..fad4ac6d0 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/SamplingDialog.cxx @@ -0,0 +1,563 @@ +/* -*- 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 <svl/undo.hxx> +#include <comphelper/random.hxx> +#include <rangelst.hxx> +#include <docsh.hxx> +#include <document.hxx> +#include <reffact.hxx> +#include <docfunc.hxx> +#include <SamplingDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScSamplingDialog::ScSamplingDialog(SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData) + : ScAnyRefDlgController(pSfxBindings, pChildWindow, pParent, + "modules/scalc/ui/samplingdialog.ui", "SamplingDialog") + , mpActiveEdit(nullptr) + , mViewData(rViewData) + , mDocument(rViewData.GetDocument()) + , mInputRange(ScAddress::INITIALIZE_INVALID) + , mAddressDetails(mDocument.GetAddressConvention(), 0, 0) + , mOutputAddress(ScAddress::INITIALIZE_INVALID) + , mCurrentAddress(rViewData.GetCurX(), rViewData.GetCurY(), rViewData.GetTabNo()) + , mnLastSampleSizeValue(1) + , mnLastPeriodValue(1) + , mDialogLostFocus(false) + , mxInputRangeLabel(m_xBuilder->weld_label("input-range-label")) + , mxInputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("input-range-edit"))) + , mxInputRangeButton(new formula::RefButton(m_xBuilder->weld_button("input-range-button"))) + , mxOutputRangeLabel(m_xBuilder->weld_label("output-range-label")) + , mxOutputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("output-range-edit"))) + , mxOutputRangeButton(new formula::RefButton(m_xBuilder->weld_button("output-range-button"))) + , mxSampleSize(m_xBuilder->weld_spin_button("sample-size-spin")) + , mxPeriod(m_xBuilder->weld_spin_button("period-spin")) + , mxRandomMethodRadio(m_xBuilder->weld_radio_button("random-method-radio")) + , mxWithReplacement(m_xBuilder->weld_check_button("with-replacement")) + , mxKeepOrder(m_xBuilder->weld_check_button("keep-order")) + , mxPeriodicMethodRadio(m_xBuilder->weld_radio_button("periodic-method-radio")) + , mxButtonOk(m_xBuilder->weld_button("ok")) + , mxButtonCancel(m_xBuilder->weld_button("cancel")) +{ + mxInputRangeEdit->SetReferences(this, mxInputRangeLabel.get()); + mxInputRangeButton->SetReferences(this, mxInputRangeEdit.get()); + + mxOutputRangeEdit->SetReferences(this, mxOutputRangeLabel.get()); + mxOutputRangeButton->SetReferences(this, mxOutputRangeEdit.get()); + + Init(); + GetRangeFromSelection(); +} + +ScSamplingDialog::~ScSamplingDialog() +{ +} + +void ScSamplingDialog::Init() +{ + mxButtonCancel->connect_clicked( LINK( this, ScSamplingDialog, ButtonClicked ) ); + mxButtonOk->connect_clicked( LINK( this, ScSamplingDialog, ButtonClicked ) ); + mxButtonOk->set_sensitive(false); + + Link<formula::RefEdit&,void> aEditLink = LINK( this, ScSamplingDialog, GetEditFocusHandler ); + mxInputRangeEdit->SetGetFocusHdl( aEditLink ); + mxOutputRangeEdit->SetGetFocusHdl( aEditLink ); + Link<formula::RefButton&,void> aButtonLink = LINK( this, ScSamplingDialog, GetButtonFocusHandler ); + mxInputRangeButton->SetGetFocusHdl( aButtonLink ); + mxOutputRangeButton->SetGetFocusHdl( aButtonLink ); + + aEditLink = LINK( this, ScSamplingDialog, LoseEditFocusHandler ); + mxInputRangeEdit->SetLoseFocusHdl( aEditLink ); + mxOutputRangeEdit->SetLoseFocusHdl( aEditLink ); + aButtonLink = LINK( this, ScSamplingDialog, LoseButtonFocusHandler ); + mxInputRangeButton->SetLoseFocusHdl( aButtonLink ); + mxOutputRangeButton->SetLoseFocusHdl( aButtonLink ); + + Link<formula::RefEdit&,void> aLink2 = LINK( this, ScSamplingDialog, RefInputModifyHandler); + mxInputRangeEdit->SetModifyHdl( aLink2); + mxOutputRangeEdit->SetModifyHdl( aLink2); + + mxSampleSize->connect_value_changed( LINK( this, ScSamplingDialog, SamplingSizeValueModified )); + mxSampleSize->set_range(1, SAL_MAX_INT32); + mxPeriod->connect_value_changed( LINK( this, ScSamplingDialog, PeriodValueModified )); + mxPeriod->set_range(1, SAL_MAX_INT32); + + mxPeriodicMethodRadio->connect_toggled( LINK( this, ScSamplingDialog, ToggleSamplingMethod ) ); + mxRandomMethodRadio->connect_toggled( LINK( this, ScSamplingDialog, ToggleSamplingMethod ) ); + + mxWithReplacement->connect_toggled( LINK( this, ScSamplingDialog, CheckHdl)); + mxKeepOrder->connect_toggled( LINK( this, ScSamplingDialog, CheckHdl)); + + mxOutputRangeEdit->GrabFocus(); + mxPeriodicMethodRadio->set_active(true); + + ToggleSamplingMethod(); +} + +void ScSamplingDialog::GetRangeFromSelection() +{ + mViewData.GetSimpleArea(mInputRange); + OUString aCurrentString(mInputRange.Format(mDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails)); + mxInputRangeEdit->SetText(aCurrentString); +} + +void ScSamplingDialog::SetActive() +{ + if ( mDialogLostFocus ) + { + mDialogLostFocus = false; + if( mpActiveEdit ) + mpActiveEdit->GrabFocus(); + } + else + { + m_xDialog->grab_focus(); + } + RefInputDone(); +} + +void ScSamplingDialog::Close() +{ + DoClose( ScSamplingDialogWrapper::GetChildWindowId() ); +} + +void ScSamplingDialog::SetReference( const ScRange& rReferenceRange, ScDocument& rDocument ) +{ + if ( mpActiveEdit ) + { + if ( rReferenceRange.aStart != rReferenceRange.aEnd ) + RefInputStart( mpActiveEdit ); + + OUString aReferenceString; + + if ( mpActiveEdit == mxInputRangeEdit.get() ) + { + mInputRange = rReferenceRange; + aReferenceString = mInputRange.Format(rDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxInputRangeEdit->SetRefString( aReferenceString ); + + LimitSampleSizeAndPeriod(); + } + else if ( mpActiveEdit == mxOutputRangeEdit.get() ) + { + mOutputAddress = rReferenceRange.aStart; + + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + aReferenceString = mOutputAddress.Format(nFormat, &rDocument, rDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + + // Change sampling size according to output range selection + sal_Int64 aSelectedSampleSize = rReferenceRange.aEnd.Row() - rReferenceRange.aStart.Row() + 1; + if (aSelectedSampleSize > 1) + mxSampleSize->set_value(aSelectedSampleSize); + SamplingSizeValueModified(*mxSampleSize); + } + } + + // Enable OK if both, input range and output address are set. + // Disable if at least one is invalid. + mxButtonOk->set_sensitive(mInputRange.IsValid() && mOutputAddress.IsValid()); +} + +ScRange ScSamplingDialog::PerformPeriodicSampling(ScDocShell* pDocShell) +{ + ScAddress aStart = mInputRange.aStart; + ScAddress aEnd = mInputRange.aEnd; + + SCTAB outTab = mOutputAddress.Tab(); + SCROW outRow = mOutputAddress.Row(); + + sal_Int64 aPeriod = mxPeriod->get_value(); + + for (SCROW inTab = aStart.Tab(); inTab <= aEnd.Tab(); inTab++) + { + SCCOL outCol = mOutputAddress.Col(); + for (SCCOL inCol = aStart.Col(); inCol <= aEnd.Col(); inCol++) + { + sal_Int64 i = 0; + outRow = mOutputAddress.Row(); + for (SCROW inRow = aStart.Row(); inRow <= aEnd.Row(); inRow++) + { + assert(aPeriod && "div-by-zero"); + if (i % aPeriod == aPeriod - 1 ) // Sample the last of period + { + double aValue = mDocument.GetValue(ScAddress(inCol, inRow, inTab)); + pDocShell->GetDocFunc().SetValueCell(ScAddress(outCol, outRow, outTab), aValue, true); + outRow++; + } + i++; + } + outCol++; + } + outTab++; + } + + return ScRange(mOutputAddress, ScAddress(outTab, outRow, outTab) ); +} + +ScRange ScSamplingDialog::PerformRandomSampling(ScDocShell* pDocShell) +{ + ScAddress aStart = mInputRange.aStart; + ScAddress aEnd = mInputRange.aEnd; + + SCTAB outTab = mOutputAddress.Tab(); + SCROW outRow = mOutputAddress.Row(); + + const sal_Int64 nSampleSize = mxSampleSize->get_value(); + + // This implementation groups by columns. Other options could be grouping + // by rows or area. + const sal_Int64 nPopulationSize = aEnd.Row() - aStart.Row() + 1; + + const bool bWithReplacement = mxWithReplacement->get_sensitive() && mxWithReplacement->get_active(); + + // WOR (WithOutReplacement) can't draw more than population. Catch that in + // the caller. + assert( bWithReplacement || nSampleSize <= nPopulationSize); + if (!bWithReplacement && nSampleSize > nPopulationSize) + // Would enter an endless loop below, bail out. + return ScRange( mOutputAddress); + + for (SCROW inTab = aStart.Tab(); inTab <= aEnd.Tab(); inTab++) + { + SCCOL outCol = mOutputAddress.Col(); + for (SCCOL inCol = aStart.Col(); inCol <= aEnd.Col(); inCol++) + { + outRow = mOutputAddress.Row(); + std::vector<bool> vUsed( nPopulationSize, false); + + while ((outRow - mOutputAddress.Row()) < nSampleSize) + { + // [a,b] *both* inclusive + SCROW nRandom = comphelper::rng::uniform_int_distribution( aStart.Row(), aEnd.Row()); + + if (!bWithReplacement) + { + nRandom -= aStart.Row(); + if (vUsed[nRandom]) + { + // Find a nearest one, preferring forwards. + // Again: it's essential that the loop is entered only + // if nSampleSize<=nPopulationSize, which is checked + // above. + SCROW nBack = nRandom; + SCROW nForw = nRandom; + do + { + if (nForw < nPopulationSize - 1 && !vUsed[++nForw]) + { + nRandom = nForw; + break; + } + if (nBack > 0 && !vUsed[--nBack]) + { + nRandom = nBack; + break; + } + } + while (true); + } + vUsed[nRandom] = true; + nRandom += aStart.Row(); + } + + const double fValue = mDocument.GetValue( ScAddress(inCol, nRandom, inTab) ); + pDocShell->GetDocFunc().SetValueCell(ScAddress(outCol, outRow, outTab), fValue, true); + outRow++; + } + outCol++; + } + outTab++; + } + + return ScRange(mOutputAddress, ScAddress(outTab, outRow, outTab) ); +} + +ScRange ScSamplingDialog::PerformRandomSamplingKeepOrder(ScDocShell* pDocShell) +{ + ScAddress aStart = mInputRange.aStart; + ScAddress aEnd = mInputRange.aEnd; + + SCTAB outTab = mOutputAddress.Tab(); + SCROW outRow = mOutputAddress.Row(); + + SCROW inRow; + + sal_Int64 aSampleSize = mxSampleSize->get_value(); + + for (SCROW inTab = aStart.Tab(); inTab <= aEnd.Tab(); inTab++) + { + SCCOL outCol = mOutputAddress.Col(); + for (SCCOL inCol = aStart.Col(); inCol <= aEnd.Col(); inCol++) + { + SCROW aPopulationSize = (aEnd.Row() - aStart.Row()) + 1; + + outRow = mOutputAddress.Row(); + inRow = aStart.Row(); + + while ((outRow - mOutputAddress.Row()) < aSampleSize) + { + double aRandomValue = comphelper::rng::uniform_real_distribution(); + + if ( (aPopulationSize - (inRow - aStart.Row())) * aRandomValue >= aSampleSize - (outRow - mOutputAddress.Row()) ) + { + inRow++; + } + else + { + double aValue = mDocument.GetValue( ScAddress(inCol, inRow, inTab) ); + pDocShell->GetDocFunc().SetValueCell(ScAddress(outCol, outRow, outTab), aValue, true); + inRow++; + outRow++; + } + } + outCol++; + } + outTab++; + } + + return ScRange(mOutputAddress, ScAddress(outTab, outRow, outTab) ); +} + +void ScSamplingDialog::PerformSampling() +{ + OUString aUndo(ScResId(STR_SAMPLING_UNDO_NAME)); + ScDocShell* pDocShell = mViewData.GetDocShell(); + SfxUndoManager* pUndoManager = pDocShell->GetUndoManager(); + + ScRange aModifiedRange; + + pUndoManager->EnterListAction( aUndo, aUndo, 0, mViewData.GetViewShell()->GetViewShellId() ); + + if (mxRandomMethodRadio->get_active()) + { + if (mxKeepOrder->get_sensitive() && mxKeepOrder->get_active()) + aModifiedRange = PerformRandomSamplingKeepOrder(pDocShell); + else + aModifiedRange = PerformRandomSampling(pDocShell); + } + else if (mxPeriodicMethodRadio->get_active()) + { + aModifiedRange = PerformPeriodicSampling(pDocShell); + } + + pUndoManager->LeaveListAction(); + pDocShell->PostPaint(aModifiedRange, PaintPartFlags::Grid); +} + +sal_Int64 ScSamplingDialog::GetPopulationSize() const +{ + return mInputRange.IsValid() ? mInputRange.aEnd.Row() - mInputRange.aStart.Row() + 1 : 0; +} + +void ScSamplingDialog::LimitSampleSizeAndPeriod() +{ + // Limit sample size (for WOR methods) and period if population is smaller + // than last known value. When enlargening the input population range the + // values will be adjusted up to the last known value again. + const sal_Int64 nPopulationSize = GetPopulationSize(); + if (nPopulationSize <= mnLastSampleSizeValue && !mxWithReplacement->get_active()) + mxSampleSize->set_value( nPopulationSize); + if (nPopulationSize <= mnLastPeriodValue) + mxPeriod->set_value( nPopulationSize); +} + +IMPL_LINK_NOARG(ScSamplingDialog, SamplingSizeValueModified, weld::SpinButton&, void) +{ + if (!mxWithReplacement->get_active()) + { + // For all WOR methods limit sample size to population size. + const sal_Int64 nPopulationSize = GetPopulationSize(); + if (mxSampleSize->get_value() > nPopulationSize) + mxSampleSize->set_value(nPopulationSize); + } + mnLastSampleSizeValue = mxSampleSize->get_value(); +} + +IMPL_LINK_NOARG(ScSamplingDialog, PeriodValueModified, weld::SpinButton&, void) +{ + // Limit period to population size. + const sal_Int64 nPopulationSize = GetPopulationSize(); + if (mxPeriod->get_value() > nPopulationSize) + mxPeriod->set_value(nPopulationSize); + mnLastPeriodValue = mxPeriod->get_value(); +} + +IMPL_LINK( ScSamplingDialog, GetEditFocusHandler, formula::RefEdit&, rCtrl, void ) +{ + if (&rCtrl == mxInputRangeEdit.get()) + mpActiveEdit = mxInputRangeEdit.get(); + else if (&rCtrl == mxOutputRangeEdit.get()) + mpActiveEdit = mxOutputRangeEdit.get(); + else + mpActiveEdit = nullptr; + + if (mpActiveEdit) + mpActiveEdit->SelectAll(); +} + +IMPL_LINK(ScSamplingDialog, GetButtonFocusHandler, formula::RefButton&, rCtrl, void) +{ + if (&rCtrl == mxInputRangeButton.get()) + mpActiveEdit = mxInputRangeEdit.get(); + else if (&rCtrl == mxOutputRangeButton.get()) + mpActiveEdit = mxOutputRangeEdit.get(); + else + mpActiveEdit = nullptr; + + if (mpActiveEdit) + mpActiveEdit->SelectAll(); +} + + +IMPL_LINK(ScSamplingDialog, ButtonClicked, weld::Button&, rButton, void) +{ + if (&rButton == mxButtonOk.get()) + { + PerformSampling(); + response(RET_OK); + } + else + response(RET_CANCEL); +} + +IMPL_LINK_NOARG(ScSamplingDialog, LoseEditFocusHandler, formula::RefEdit&, void) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScSamplingDialog, LoseButtonFocusHandler, formula::RefButton&, void) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScSamplingDialog, ToggleSamplingMethod, weld::Toggleable&, void) +{ + ToggleSamplingMethod(); +} + +void ScSamplingDialog::ToggleSamplingMethod() +{ + if (mxRandomMethodRadio->get_active()) + { + mxPeriod->set_sensitive(false); + mxSampleSize->set_sensitive(true); + mxWithReplacement->set_sensitive(true); + mxKeepOrder->set_sensitive(true); + } + else if (mxPeriodicMethodRadio->get_active()) + { + // WOR keeping order. + mxPeriod->set_sensitive(true); + mxSampleSize->set_sensitive(false); + mxWithReplacement->set_active(false); + mxWithReplacement->set_sensitive(false); + mxKeepOrder->set_active(true); + mxKeepOrder->set_sensitive(false); + } +} + +IMPL_LINK(ScSamplingDialog, CheckHdl, weld::Toggleable&, rBtn, void) +{ + // Keep both checkboxes enabled so user can easily switch between the three + // possible combinations (one or the other or none), just uncheck the other + // one if one is checked. Otherwise the other checkbox would had to be + // disabled until user unchecks the enabled one again, which would force + // user to two clicks to switch. + if (&rBtn == mxWithReplacement.get()) + { + if (mxWithReplacement->get_active()) + { + // For WR can't keep order. + mxKeepOrder->set_active(false); + } + else + { + // For WOR limit sample size to population size. + SamplingSizeValueModified(*mxSampleSize); + } + } + else if (&rBtn == mxKeepOrder.get()) + { + if (mxKeepOrder->get_active()) + { + // Keep order is always WOR. + mxWithReplacement->set_active(false); + SamplingSizeValueModified(*mxSampleSize); + } + } +} + +IMPL_LINK_NOARG(ScSamplingDialog, RefInputModifyHandler, formula::RefEdit&, void) +{ + if ( mpActiveEdit ) + { + if ( mpActiveEdit == mxInputRangeEdit.get() ) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxInputRangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mInputRange = *pRange; + // Highlight the resulting range. + mxInputRangeEdit->StartUpdateData(); + + LimitSampleSizeAndPeriod(); + } + else + { + mInputRange = ScRange( ScAddress::INITIALIZE_INVALID); + } + } + else if ( mpActiveEdit == mxOutputRangeEdit.get() ) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxOutputRangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mOutputAddress = pRange->aStart; + + // Crop output range to top left address for Edit field. + if (pRange->aStart != pRange->aEnd) + { + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + OUString aReferenceString = mOutputAddress.Format(nFormat, &mDocument, mDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + } + + // Change sampling size according to output range selection + sal_Int64 aSelectedSampleSize = pRange->aEnd.Row() - pRange->aStart.Row() + 1; + if (aSelectedSampleSize > 1) + mxSampleSize->set_value(aSelectedSampleSize); + SamplingSizeValueModified(*mxSampleSize); + + // Highlight the resulting range. + mxOutputRangeEdit->StartUpdateData(); + } + else + { + mOutputAddress = ScAddress( ScAddress::INITIALIZE_INVALID); + } + } + } + + // Enable OK if both, input range and output address are set. + mxButtonOk->set_sensitive(mInputRange.IsValid() && mOutputAddress.IsValid()); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/StatisticsInputOutputDialog.cxx b/sc/source/ui/StatisticsDialogs/StatisticsInputOutputDialog.cxx new file mode 100644 index 000000000..7447ebf9a --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/StatisticsInputOutputDialog.cxx @@ -0,0 +1,305 @@ +/* -*- 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 <svl/undo.hxx> + +#include <rangelst.hxx> +#include <docsh.hxx> +#include <document.hxx> +#include <scresid.hxx> +#include <tabvwsh.hxx> + +#include <StatisticsInputOutputDialog.hxx> + +ScRangeList ScStatisticsInputOutputDialog::MakeColumnRangeList(SCTAB aTab, ScAddress const & aStart, ScAddress const & aEnd) +{ + ScRangeList aRangeList; + for (SCCOL inCol = aStart.Col(); inCol <= aEnd.Col(); inCol++) + { + ScRange aColumnRange ( + ScAddress(inCol, aStart.Row(), aTab), + ScAddress(inCol, aEnd.Row(), aTab) ); + + aRangeList.push_back(aColumnRange); + } + return aRangeList; +} + +ScRangeList ScStatisticsInputOutputDialog::MakeRowRangeList(SCTAB aTab, ScAddress const & aStart, ScAddress const & aEnd) +{ + ScRangeList aRangeList; + for (SCROW inRow = aStart.Row(); inRow <= aEnd.Row(); inRow++) + { + ScRange aRowRange ( + ScAddress(aStart.Col(), inRow, aTab), + ScAddress(aEnd.Col(), inRow, aTab) ); + + aRangeList.push_back(aRowRange); + } + return aRangeList; +} + +ScStatisticsInputOutputDialog::ScStatisticsInputOutputDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData, const OUString& rUIXMLDescription, const OString& rID) + : ScAnyRefDlgController(pSfxBindings, pChildWindow, pParent, rUIXMLDescription, rID) + , mxInputRangeLabel(m_xBuilder->weld_label("input-range-label")) + , mxInputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("input-range-edit"))) + , mxInputRangeButton(new formula::RefButton(m_xBuilder->weld_button("input-range-button"))) + , mxOutputRangeLabel(m_xBuilder->weld_label("output-range-label")) + , mxOutputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("output-range-edit"))) + , mxOutputRangeButton(new formula::RefButton(m_xBuilder->weld_button("output-range-button"))) + , mxGroupByColumnsRadio(m_xBuilder->weld_radio_button("groupedby-columns-radio")) + , mxGroupByRowsRadio(m_xBuilder->weld_radio_button("groupedby-rows-radio")) + , mViewData(rViewData) + , mDocument(rViewData.GetDocument()) + , mInputRange(ScAddress::INITIALIZE_INVALID) + , mAddressDetails(mDocument.GetAddressConvention(), 0, 0) + , mOutputAddress(ScAddress::INITIALIZE_INVALID) + , mGroupedBy(BY_COLUMN) + , mxButtonOk(m_xBuilder->weld_button("ok")) + , mxButtonCancel(m_xBuilder->weld_button("cancel")) + , mpActiveEdit(nullptr) + , mCurrentAddress(rViewData.GetCurX(), rViewData.GetCurY(), rViewData.GetTabNo()) + , mDialogLostFocus(false) +{ + mxInputRangeEdit->SetReferences(this, mxInputRangeLabel.get()); + mxInputRangeButton->SetReferences(this, mxInputRangeEdit.get()); + + mxOutputRangeEdit->SetReferences(this, mxOutputRangeLabel.get()); + mxOutputRangeButton->SetReferences(this, mxOutputRangeEdit.get()); + + Init(); + GetRangeFromSelection(); +} + +ScStatisticsInputOutputDialog::~ScStatisticsInputOutputDialog() +{ +} + +void ScStatisticsInputOutputDialog::Init() +{ + mxButtonCancel->connect_clicked( LINK( this, ScStatisticsInputOutputDialog, ButtonClicked ) ); + mxButtonOk->connect_clicked( LINK( this, ScStatisticsInputOutputDialog, ButtonClicked ) ); + mxButtonOk->set_sensitive(false); + + Link<formula::RefEdit&,void> aEditLink = LINK( this, ScStatisticsInputOutputDialog, GetEditFocusHandler ); + mxInputRangeEdit->SetGetFocusHdl( aEditLink ); + mxOutputRangeEdit->SetGetFocusHdl( aEditLink ); + Link<formula::RefButton&,void> aButtonLink = LINK( this, ScStatisticsInputOutputDialog, GetButtonFocusHandler ); + mxInputRangeButton->SetGetFocusHdl( aButtonLink ); + mxOutputRangeButton->SetGetFocusHdl( aButtonLink ); + + aEditLink = LINK( this, ScStatisticsInputOutputDialog, LoseEditFocusHandler ); + mxInputRangeEdit->SetLoseFocusHdl( aEditLink ); + mxOutputRangeEdit->SetLoseFocusHdl( aEditLink ); + aButtonLink = LINK( this, ScStatisticsInputOutputDialog, LoseButtonFocusHandler ); + mxInputRangeButton->SetLoseFocusHdl( aButtonLink ); + mxOutputRangeButton->SetLoseFocusHdl( aButtonLink ); + + Link<formula::RefEdit&,void> aLink2 = LINK( this, ScStatisticsInputOutputDialog, RefInputModifyHandler); + mxInputRangeEdit->SetModifyHdl( aLink2); + mxOutputRangeEdit->SetModifyHdl( aLink2); + + mxOutputRangeEdit->GrabFocus(); + + mxGroupByColumnsRadio->connect_toggled( LINK( this, ScStatisticsInputOutputDialog, GroupByChanged ) ); + mxGroupByRowsRadio->connect_toggled( LINK( this, ScStatisticsInputOutputDialog, GroupByChanged ) ); + + mxGroupByColumnsRadio->set_active(true); + mxGroupByRowsRadio->set_active(false); +} + +void ScStatisticsInputOutputDialog::GetRangeFromSelection() +{ + mViewData.GetSimpleArea(mInputRange); + OUString aCurrentString(mInputRange.Format(mDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails)); + mxInputRangeEdit->SetText(aCurrentString); +} + +void ScStatisticsInputOutputDialog::SetActive() +{ + if ( mDialogLostFocus ) + { + mDialogLostFocus = false; + if( mpActiveEdit ) + mpActiveEdit->GrabFocus(); + } + else + { + m_xDialog->grab_focus(); + } + RefInputDone(); +} + +void ScStatisticsInputOutputDialog::SetReference( const ScRange& rReferenceRange, ScDocument& rDocument ) +{ + if ( mpActiveEdit ) + { + if ( rReferenceRange.aStart != rReferenceRange.aEnd ) + RefInputStart( mpActiveEdit ); + + OUString aReferenceString; + + if (mpActiveEdit == mxInputRangeEdit.get()) + { + mInputRange = rReferenceRange; + aReferenceString = mInputRange.Format(rDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxInputRangeEdit->SetRefString( aReferenceString ); + } + else if (mpActiveEdit == mxOutputRangeEdit.get()) + { + mOutputAddress = rReferenceRange.aStart; + + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + aReferenceString = mOutputAddress.Format(nFormat, &rDocument, rDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + } + } + + ValidateDialogInput(); +} + +IMPL_LINK( ScStatisticsInputOutputDialog, ButtonClicked, weld::Button&, rButton, void ) +{ + if (&rButton == mxButtonOk.get()) + { + CalculateInputAndWriteToOutput(); + response(RET_OK); + } + else + response(RET_CANCEL); +} + +IMPL_LINK(ScStatisticsInputOutputDialog, GetEditFocusHandler, formula::RefEdit&, rCtrl, void) +{ + mpActiveEdit = nullptr; + + if (&rCtrl == mxInputRangeEdit.get()) + mpActiveEdit = mxInputRangeEdit.get(); + if (&rCtrl == mxOutputRangeEdit.get()) + mpActiveEdit = mxOutputRangeEdit.get(); + + if (mpActiveEdit) + mpActiveEdit->SelectAll(); +} + +IMPL_LINK(ScStatisticsInputOutputDialog, GetButtonFocusHandler, formula::RefButton&, rCtrl, void) +{ + mpActiveEdit = nullptr; + + if (&rCtrl == mxInputRangeButton.get()) + mpActiveEdit = mxInputRangeEdit.get(); + else if (&rCtrl == mxOutputRangeButton.get()) + mpActiveEdit = mxOutputRangeEdit.get(); + + if (mpActiveEdit) + mpActiveEdit->SelectAll(); +} + +IMPL_LINK_NOARG(ScStatisticsInputOutputDialog, LoseEditFocusHandler, formula::RefEdit&, void) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScStatisticsInputOutputDialog, LoseButtonFocusHandler, formula::RefButton&, void) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG( ScStatisticsInputOutputDialog, GroupByChanged, weld::Toggleable&, void ) +{ + if (mxGroupByColumnsRadio->get_active()) + mGroupedBy = BY_COLUMN; + else if (mxGroupByRowsRadio->get_active()) + mGroupedBy = BY_ROW; + + ValidateDialogInput(); +} + +IMPL_LINK_NOARG( ScStatisticsInputOutputDialog, RefInputModifyHandler, formula::RefEdit&, void ) +{ + if ( mpActiveEdit ) + { + if (mpActiveEdit == mxInputRangeEdit.get()) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxInputRangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mInputRange = *pRange; + // Highlight the resulting range. + mxInputRangeEdit->StartUpdateData(); + } + else + { + mInputRange = ScRange( ScAddress::INITIALIZE_INVALID); + } + } + else if (mpActiveEdit == mxOutputRangeEdit.get()) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxOutputRangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mOutputAddress = pRange->aStart; + + // Crop output range to top left address for Edit field. + if (pRange->aStart != pRange->aEnd) + { + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + OUString aReferenceString = mOutputAddress.Format(nFormat, &mDocument, mDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + } + + // Highlight the resulting range. + mxOutputRangeEdit->StartUpdateData(); + } + else + { + mOutputAddress = ScAddress( ScAddress::INITIALIZE_INVALID); + } + } + } + + ValidateDialogInput(); +} + +void ScStatisticsInputOutputDialog::CalculateInputAndWriteToOutput() +{ + OUString aUndo(ScResId(GetUndoNameId())); + ScDocShell* pDocShell = mViewData.GetDocShell(); + SfxUndoManager* pUndoManager = pDocShell->GetUndoManager(); + pUndoManager->EnterListAction( aUndo, aUndo, 0, mViewData.GetViewShell()->GetViewShellId() ); + + ScRange aOutputRange = ApplyOutput(pDocShell); + + pUndoManager->LeaveListAction(); + pDocShell->PostPaint( aOutputRange, PaintPartFlags::Grid ); +} + +bool ScStatisticsInputOutputDialog::InputRangesValid() +{ + return mInputRange.IsValid() && mOutputAddress.IsValid(); +} + +void ScStatisticsInputOutputDialog::ValidateDialogInput() +{ + // Enable OK button if all inputs are ok. + mxButtonOk->set_sensitive(InputRangesValid()); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx new file mode 100644 index 000000000..3c0f7ce98 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx @@ -0,0 +1,347 @@ +/* -*- 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 <svl/undo.hxx> + +#include <rangelst.hxx> +#include <docsh.hxx> +#include <document.hxx> +#include <scresid.hxx> +#include <tabvwsh.hxx> + +#include <StatisticsTwoVariableDialog.hxx> + +ScStatisticsTwoVariableDialog::ScStatisticsTwoVariableDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData, const OUString& rUIXMLDescription, const OString& rID) + : ScAnyRefDlgController(pSfxBindings, pChildWindow, pParent, rUIXMLDescription, rID) + , mxVariable1RangeLabel(m_xBuilder->weld_label("variable1-range-label")) + , mxVariable1RangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("variable1-range-edit"))) + , mxVariable1RangeButton(new formula::RefButton(m_xBuilder->weld_button("variable1-range-button"))) + , mxVariable2RangeLabel(m_xBuilder->weld_label("variable2-range-label")) + , mxVariable2RangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("variable2-range-edit"))) + , mxVariable2RangeButton(new formula::RefButton(m_xBuilder->weld_button("variable2-range-button"))) + , mxOutputRangeLabel(m_xBuilder->weld_label("output-range-label")) + , mxOutputRangeEdit(new formula::RefEdit(m_xBuilder->weld_entry("output-range-edit"))) + , mxOutputRangeButton(new formula::RefButton(m_xBuilder->weld_button("output-range-button"))) + , mViewData(rViewData) + , mDocument(rViewData.GetDocument()) + , mVariable1Range(ScAddress::INITIALIZE_INVALID) + , mVariable2Range(ScAddress::INITIALIZE_INVALID) + , mAddressDetails(mDocument.GetAddressConvention(), 0, 0 ) + , mOutputAddress(ScAddress::INITIALIZE_INVALID) + , mGroupedBy(BY_COLUMN) + , mxButtonOk(m_xBuilder->weld_button("ok")) + , mxButtonCancel(m_xBuilder->weld_button("cancel")) + , mxGroupByColumnsRadio(m_xBuilder->weld_radio_button("groupedby-columns-radio")) + , mxGroupByRowsRadio(m_xBuilder->weld_radio_button("groupedby-rows-radio")) + , mpActiveEdit(nullptr) + , mCurrentAddress(rViewData.GetCurX(), rViewData.GetCurY(), rViewData.GetTabNo() ) + , mDialogLostFocus(false) +{ + mxVariable1RangeEdit->SetReferences(this, mxVariable1RangeLabel.get()); + mxVariable1RangeButton->SetReferences(this, mxVariable1RangeEdit.get()); + + mxVariable2RangeEdit->SetReferences(this, mxVariable2RangeLabel.get()); + mxVariable2RangeButton->SetReferences(this, mxVariable2RangeEdit.get()); + + mxOutputRangeEdit->SetReferences(this, mxOutputRangeLabel.get()); + mxOutputRangeButton->SetReferences(this, mxOutputRangeEdit.get()); + + Init(); + GetRangeFromSelection(); +} + +ScStatisticsTwoVariableDialog::~ScStatisticsTwoVariableDialog() +{ +} + +void ScStatisticsTwoVariableDialog::Init() +{ + mxButtonCancel->connect_clicked( LINK( this, ScStatisticsTwoVariableDialog, ButtonClicked ) ); + mxButtonOk->connect_clicked( LINK( this, ScStatisticsTwoVariableDialog, ButtonClicked ) ); + mxButtonOk->set_sensitive(false); + + Link<formula::RefEdit&,void> aEditLink = LINK( this, ScStatisticsTwoVariableDialog, GetEditFocusHandler ); + mxVariable1RangeEdit->SetGetFocusHdl( aEditLink ); + mxVariable2RangeEdit->SetGetFocusHdl( aEditLink ); + mxOutputRangeEdit->SetGetFocusHdl( aEditLink ); + + Link<formula::RefButton&,void> aButtonLink = LINK( this, ScStatisticsTwoVariableDialog, GetButtonFocusHandler ); + mxVariable1RangeButton->SetGetFocusHdl( aButtonLink ); + mxVariable2RangeButton->SetGetFocusHdl( aButtonLink ); + mxOutputRangeButton->SetGetFocusHdl( aButtonLink ); + + aEditLink = LINK( this, ScStatisticsTwoVariableDialog, LoseEditFocusHandler ); + mxVariable1RangeEdit->SetLoseFocusHdl( aEditLink ); + mxVariable2RangeEdit->SetLoseFocusHdl( aEditLink ); + mxOutputRangeEdit->SetLoseFocusHdl( aEditLink ); + + aButtonLink = LINK( this, ScStatisticsTwoVariableDialog, LoseButtonFocusHandler ); + mxVariable1RangeButton->SetLoseFocusHdl( aButtonLink ); + mxVariable2RangeButton->SetLoseFocusHdl( aButtonLink ); + mxOutputRangeButton->SetLoseFocusHdl( aButtonLink ); + + Link<formula::RefEdit&,void> aLink2 = LINK( this, ScStatisticsTwoVariableDialog, RefInputModifyHandler); + mxVariable1RangeEdit->SetModifyHdl( aLink2); + mxVariable2RangeEdit->SetModifyHdl( aLink2); + mxOutputRangeEdit->SetModifyHdl( aLink2); + + mxOutputRangeEdit->GrabFocus(); + + mxGroupByColumnsRadio->connect_toggled( LINK( this, ScStatisticsTwoVariableDialog, GroupByChanged ) ); + mxGroupByRowsRadio->connect_toggled( LINK( this, ScStatisticsTwoVariableDialog, GroupByChanged ) ); + + mxGroupByColumnsRadio->set_active(true); + mxGroupByRowsRadio->set_active(false); +} + +void ScStatisticsTwoVariableDialog::GetRangeFromSelection() +{ + OUString aCurrentString; + + ScRange aCurrentRange; + mViewData.GetSimpleArea(aCurrentRange); + + if (aCurrentRange.aEnd.Col() - aCurrentRange.aStart.Col() == 1) + { + mVariable1Range = aCurrentRange; + mVariable1Range.aEnd.SetCol(mVariable1Range.aStart.Col()); + aCurrentString = mVariable1Range.Format(mDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxVariable1RangeEdit->SetText(aCurrentString); + + mVariable2Range = aCurrentRange; + mVariable2Range.aStart.SetCol(mVariable2Range.aEnd.Col()); + aCurrentString = mVariable2Range.Format(mDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxVariable2RangeEdit->SetText(aCurrentString); + } + else + { + mVariable1Range = aCurrentRange; + aCurrentString = mVariable1Range.Format(mDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxVariable1RangeEdit->SetText(aCurrentString); + } +} + +void ScStatisticsTwoVariableDialog::SetActive() +{ + if ( mDialogLostFocus ) + { + mDialogLostFocus = false; + if( mpActiveEdit ) + mpActiveEdit->GrabFocus(); + } + else + { + m_xDialog->grab_focus(); + } + RefInputDone(); +} + +void ScStatisticsTwoVariableDialog::SetReference( const ScRange& rReferenceRange, ScDocument& rDocument ) +{ + if ( mpActiveEdit != nullptr ) + { + if ( rReferenceRange.aStart != rReferenceRange.aEnd ) + RefInputStart( mpActiveEdit ); + + OUString aReferenceString; + + if ( mpActiveEdit == mxVariable1RangeEdit.get() ) + { + mVariable1Range = rReferenceRange; + aReferenceString = mVariable1Range.Format(rDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxVariable1RangeEdit->SetRefString(aReferenceString); + } + else if ( mpActiveEdit == mxVariable2RangeEdit.get() ) + { + mVariable2Range = rReferenceRange; + aReferenceString = mVariable2Range.Format(rDocument, ScRefFlags::RANGE_ABS_3D, mAddressDetails); + mxVariable2RangeEdit->SetRefString(aReferenceString); + } + else if ( mpActiveEdit == mxOutputRangeEdit.get() ) + { + mOutputAddress = rReferenceRange.aStart; + + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + aReferenceString = mOutputAddress.Format(nFormat, &rDocument, rDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + } + } + + ValidateDialogInput(); +} + +IMPL_LINK( ScStatisticsTwoVariableDialog, ButtonClicked, weld::Button&, rButton, void ) +{ + if (&rButton == mxButtonOk.get()) + { + CalculateInputAndWriteToOutput(); + response(RET_OK); + } + else + response(RET_CANCEL); +} + +IMPL_LINK(ScStatisticsTwoVariableDialog, GetEditFocusHandler, formula::RefEdit&, rCtrl, void) +{ + mpActiveEdit = nullptr; + if (&rCtrl == mxVariable1RangeEdit.get()) + { + mpActiveEdit = mxVariable1RangeEdit.get(); + } + else if (&rCtrl == mxVariable2RangeEdit.get()) + { + mpActiveEdit = mxVariable2RangeEdit.get(); + } + else if (&rCtrl == mxOutputRangeEdit.get()) + { + mpActiveEdit = mxOutputRangeEdit.get(); + } + + if( mpActiveEdit ) + mpActiveEdit->SelectAll(); +} + +IMPL_LINK( ScStatisticsTwoVariableDialog, GetButtonFocusHandler, formula::RefButton&, rCtrl, void ) +{ + mpActiveEdit = nullptr; + if (&rCtrl == mxVariable1RangeButton.get()) + { + mpActiveEdit = mxVariable1RangeEdit.get(); + } + else if (&rCtrl == mxVariable2RangeButton.get()) + { + mpActiveEdit = mxVariable2RangeEdit.get(); + } + else if (&rCtrl == mxOutputRangeButton.get()) + { + mpActiveEdit = mxOutputRangeEdit.get(); + } + + if( mpActiveEdit ) + mpActiveEdit->SelectAll(); +} + +IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, LoseEditFocusHandler, formula::RefEdit&, void ) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, LoseButtonFocusHandler, formula::RefButton&, void ) +{ + mDialogLostFocus = !m_xDialog->has_toplevel_focus(); +} + +IMPL_LINK_NOARG(ScStatisticsTwoVariableDialog, GroupByChanged, weld::Toggleable&, void) +{ + if (mxGroupByColumnsRadio->get_active()) + mGroupedBy = BY_COLUMN; + else if (mxGroupByRowsRadio->get_active()) + mGroupedBy = BY_ROW; + + ValidateDialogInput(); +} + +IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, RefInputModifyHandler, formula::RefEdit&, void ) +{ + if ( mpActiveEdit ) + { + if (mpActiveEdit == mxVariable1RangeEdit.get()) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxVariable1RangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mVariable1Range = *pRange; + // Highlight the resulting range. + mxVariable1RangeEdit->StartUpdateData(); + } + else + { + mVariable1Range = ScRange( ScAddress::INITIALIZE_INVALID); + } + } + else if ( mpActiveEdit == mxVariable2RangeEdit.get() ) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxVariable2RangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mVariable2Range = *pRange; + // Highlight the resulting range. + mxVariable2RangeEdit->StartUpdateData(); + } + else + { + mVariable2Range = ScRange( ScAddress::INITIALIZE_INVALID); + } + } + else if ( mpActiveEdit == mxOutputRangeEdit.get() ) + { + ScRangeList aRangeList; + bool bValid = ParseWithNames( aRangeList, mxOutputRangeEdit->GetText(), mDocument); + const ScRange* pRange = (bValid && aRangeList.size() == 1) ? &aRangeList[0] : nullptr; + if (pRange) + { + mOutputAddress = pRange->aStart; + + // Crop output range to top left address for Edit field. + if (pRange->aStart != pRange->aEnd) + { + ScRefFlags nFormat = ( mOutputAddress.Tab() == mCurrentAddress.Tab() ) ? + ScRefFlags::ADDR_ABS : + ScRefFlags::ADDR_ABS_3D; + OUString aReferenceString = mOutputAddress.Format(nFormat, &mDocument, mDocument.GetAddressConvention()); + mxOutputRangeEdit->SetRefString( aReferenceString ); + } + + // Highlight the resulting range. + mxOutputRangeEdit->StartUpdateData(); + } + else + { + mOutputAddress = ScAddress( ScAddress::INITIALIZE_INVALID); + } + } + } + + ValidateDialogInput(); +} + +void ScStatisticsTwoVariableDialog::CalculateInputAndWriteToOutput() +{ + OUString aUndo(ScResId(GetUndoNameId())); + ScDocShell* pDocShell = mViewData.GetDocShell(); + SfxUndoManager* pUndoManager = pDocShell->GetUndoManager(); + pUndoManager->EnterListAction( aUndo, aUndo, 0, mViewData.GetViewShell()->GetViewShellId() ); + + ScRange aOutputRange = ApplyOutput(pDocShell); + + pUndoManager->LeaveListAction(); + pDocShell->PostPaint( aOutputRange, PaintPartFlags::Grid ); +} + +bool ScStatisticsTwoVariableDialog::InputRangesValid() +{ + return mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid(); +} + +void ScStatisticsTwoVariableDialog::ValidateDialogInput() +{ + // Enable OK button if all inputs are ok. + mxButtonOk->set_sensitive(InputRangesValid()); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/TTestDialog.cxx b/sc/source/ui/StatisticsDialogs/TTestDialog.cxx new file mode 100644 index 000000000..864d4ac4f --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/TTestDialog.cxx @@ -0,0 +1,183 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <TTestDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScTTestDialog::ScTTestDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScStatisticsTwoVariableDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/ttestdialog.ui", "TTestDialog") +{ + m_xDialog->set_title(ScResId(STR_TTEST)); +} + +ScTTestDialog::~ScTTestDialog() +{} + +void ScTTestDialog::Close() +{ + DoClose( ScTTestDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScTTestDialog::GetUndoNameId() +{ + return STR_TTEST_UNDO_NAME; +} + +ScRange ScTTestDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + std::unique_ptr<DataRangeIterator> pVariable1Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range)); + else + pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range)); + + std::unique_ptr<DataRangeIterator> pVariable2Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range)); + else + pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range)); + + aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get()); + aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get()); + + aOutput.writeBoldString(ScResId(STR_TTEST_UNDO_NAME)); + aOutput.newLine(); + + // Alpha + aOutput.writeString(ScResId(STR_LABEL_ALPHA)); + aOutput.nextColumn(); + aOutput.writeValue(0.05); + aTemplate.autoReplaceAddress("%ALPHA%", aOutput.current()); + aOutput.newLine(); + + // Hypothesized mean difference + aOutput.writeString(ScResId(STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL)); + aOutput.nextColumn(); + aOutput.writeValue(0); + aTemplate.autoReplaceAddress("%HYPOTHESIZED_MEAN_DIFFERENCE%", aOutput.current()); + aOutput.newLine(); + + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_1_LABEL)); + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_2_LABEL)); + aOutput.newLine(); + + aOutput.writeString(ScResId(STRID_CALC_MEAN)); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + aOutput.writeString(ScResId(STRID_CALC_VARIANCE)); + aOutput.nextColumn(); + aTemplate.setTemplate("=VAR(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=VAR(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Observations + aOutput.writeString(ScResId(STR_OBSERVATIONS_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Pearson Correlation + aOutput.writeString(ScResId(STR_TTEST_PEARSON_CORRELATION)); + aOutput.nextColumn(); + aTemplate.setTemplate("=CORREL(%VARIABLE1_RANGE%;%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Observed mean difference + aOutput.writeString(ScResId(STR_OBSERVED_MEAN_DIFFERENCE_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(IF(ISODD(IF(ISNUMBER(%VARIABLE1_RANGE%); 1; 0) * IF(ISNUMBER(%VARIABLE2_RANGE%); 1; 0)); %VARIABLE1_RANGE% - %VARIABLE2_RANGE%; \"NA\"))"); + aOutput.writeMatrixFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%OBSERVED_MEAN_DIFFERENCE%", aOutput.current()); + aOutput.newLine(); + + // Variance of the Differences + aOutput.writeString(ScResId(STR_TTEST_VARIANCE_OF_THE_DIFFERENCES)); + aOutput.nextColumn(); + aTemplate.setTemplate("=VAR(IF(ISODD(IF(ISNUMBER(%VARIABLE1_RANGE%); 1; 0) * IF(ISNUMBER(%VARIABLE2_RANGE%); 1; 0)); %VARIABLE1_RANGE% - %VARIABLE2_RANGE%; \"NA\"))"); + aOutput.writeMatrixFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%VARIANCE_OF_DIFFERENCES%", aOutput.current()); + aOutput.newLine(); + + // df + aOutput.writeString(ScResId(STR_ANOVA_LABEL_DF)); + aOutput.nextColumn(); + aTemplate.setTemplate("=SUM(IF(ISNUMBER(%VARIABLE1_RANGE%); 1; 0) * IF(ISNUMBER(%VARIABLE2_RANGE%); 1; 0)) - 1"); + aOutput.writeMatrixFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%DEGREE_OF_FREEDOM%", aOutput.current()); + aOutput.newLine(); + + // t stat + aOutput.writeString(ScResId(STR_TTEST_T_STAT)); + aOutput.nextColumn(); + aTemplate.setTemplate("=(%OBSERVED_MEAN_DIFFERENCE% - %HYPOTHESIZED_MEAN_DIFFERENCE%) / (%VARIANCE_OF_DIFFERENCES% / ( %DEGREE_OF_FREEDOM% + 1)) ^ 0.5"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%T_STAT%", aOutput.current()); + aOutput.newLine(); + + // P one-tail + aOutput.writeString(ScResId(STR_TTEST_P_ONE_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=TDIST(ABS(%T_STAT%); %DEGREE_OF_FREEDOM%; 1)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // T critical one-tail + aOutput.writeString(ScResId(STR_TTEST_T_CRITICAL_ONE_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=TINV(2*%ALPHA%; %DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // P two-tail + aOutput.writeString(ScResId(STR_TTEST_P_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=TDIST(ABS(%T_STAT%); %DEGREE_OF_FREEDOM%; 2)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // T critical two-tail + aOutput.writeString(ScResId(STR_TTEST_T_CRITICAL_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=TINV(%ALPHA%; %DEGREE_OF_FREEDOM%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx new file mode 100644 index 000000000..be8431128 --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx @@ -0,0 +1,386 @@ +/* -*- 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 <editeng/editobj.hxx> +#include <editeng/wghtitem.hxx> +#include <editeng/eeitem.hxx> + +#include <editutil.hxx> + +#include <TableFillingAndNavigationTools.hxx> +#include <formulacell.hxx> +#include <docfunc.hxx> +#include <docsh.hxx> + +FormulaTemplate::FormulaTemplate(ScDocument* pDoc) + : mpDoc(pDoc) + , mbUse3D(true) +{} + +void FormulaTemplate::setTemplate(const OUString& aTemplate) +{ + mTemplate = aTemplate; +} + +void FormulaTemplate::setTemplate(const char* aTemplate) +{ + mTemplate = OUString::createFromAscii(aTemplate); +} + +const OUString& FormulaTemplate::getTemplate() +{ + for (const auto& [rVariable, rRange] : mRangeReplacementMap) + { + applyRange(rVariable, rRange, mbUse3D); + } + for (const auto& [rVariable, rAddress] : mAddressReplacementMap) + { + applyAddress(rVariable, rAddress, mbUse3D); + } + return mTemplate; +} + +void FormulaTemplate::autoReplaceRange(const OUString& aVariable, const ScRange& rRange) +{ + mRangeReplacementMap[aVariable] = rRange; +} + +void FormulaTemplate::autoReplaceAddress(const OUString& aVariable, ScAddress const & aAddress) +{ + + mAddressReplacementMap[aVariable] = aAddress; +} + +void FormulaTemplate::applyRange(std::u16string_view aVariable, const ScRange& aRange, bool b3D) +{ + ScRefFlags nFlag = b3D ? ScRefFlags::RANGE_ABS_3D : ScRefFlags::RANGE_ABS; + OUString aString = aRange.Format(*mpDoc, nFlag, mpDoc->GetAddressConvention()); + mTemplate = mTemplate.replaceAll(aVariable, aString); +} + +void FormulaTemplate::applyRangeList(std::u16string_view aVariable, const ScRangeList& aRangeList, sal_Unicode cDelimiter) +{ + OUString aString; + aRangeList.Format(aString, ScRefFlags::RANGE_ABS_3D, *mpDoc, mpDoc->GetAddressConvention(), cDelimiter); + mTemplate = mTemplate.replaceAll(aVariable, aString); +} + +void FormulaTemplate::applyAddress(std::u16string_view aVariable, const ScAddress& aAddress, bool b3D) +{ + ScRefFlags nFlag = b3D ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS; + OUString aString = aAddress.Format(nFlag, mpDoc, mpDoc->GetAddressConvention()); + mTemplate = mTemplate.replaceAll(aVariable, aString); +} + +void FormulaTemplate::applyString(std::u16string_view aVariable, std::u16string_view aValue) +{ + mTemplate = mTemplate.replaceAll(aVariable, aValue); +} + +void FormulaTemplate::applyNumber(std::u16string_view aVariable, sal_Int32 aValue) +{ + mTemplate = mTemplate.replaceAll(aVariable, OUString::number(aValue)); +} + +AddressWalker::AddressWalker(const ScAddress& aInitialAddress) : + mCurrentAddress(aInitialAddress), + mMinimumAddress(aInitialAddress), + mMaximumAddress(aInitialAddress) +{ + mAddressStack.push_back(mCurrentAddress); +} + +void AddressWalker::resetColumn() +{ + mCurrentAddress.SetCol(mAddressStack.back().Col()); +} + +void AddressWalker::resetRow() +{ + mCurrentAddress.SetRow(mAddressStack.back().Row()); +} + +void AddressWalker::reset() +{ + mCurrentAddress = mAddressStack.back(); +} + +void AddressWalker::newLine() +{ + resetColumn(); + nextRow(); +} + +ScAddress AddressWalker::current(SCCOL aRelCol, SCROW aRelRow, SCTAB aRelTab) +{ + return ScAddress( + mCurrentAddress.Col() + aRelCol, + mCurrentAddress.Row() + aRelRow, + mCurrentAddress.Tab() + aRelTab); +} + +void AddressWalker::nextColumn() +{ + mCurrentAddress.IncCol(); + + if(mMaximumAddress.Col() < mCurrentAddress.Col()) + mMaximumAddress.SetCol(mCurrentAddress.Col()); +} + +void AddressWalker::nextRow() +{ + mCurrentAddress.IncRow(); + if(mMaximumAddress.Row() < mCurrentAddress.Row()) + mMaximumAddress.SetRow(mCurrentAddress.Row()); +} + +void AddressWalker::push(SCCOL aRelativeCol, SCROW aRelativeRow, SCTAB aRelativeTab) +{ + mCurrentAddress = current(aRelativeCol, aRelativeRow, aRelativeTab); + mAddressStack.push_back(mCurrentAddress); +} + +AddressWalkerWriter::AddressWalkerWriter(const ScAddress& aInitialAddress, ScDocShell* pDocShell, ScDocument& rDocument, + formula::FormulaGrammar::Grammar eGrammar ) : + AddressWalker(aInitialAddress), + mpDocShell(pDocShell), + mrDocument(rDocument), + meGrammar(eGrammar) +{} + +void AddressWalkerWriter::writeFormula(const OUString& aFormula) +{ + mpDocShell->GetDocFunc().SetFormulaCell(mCurrentAddress, + new ScFormulaCell(mrDocument, mCurrentAddress, aFormula, meGrammar), true); +} + +void AddressWalkerWriter::writeFormulas(const std::vector<OUString>& rFormulas) +{ + size_t nLength = rFormulas.size(); + if (!nLength) + return; + + const size_t nMaxLen = mpDocShell->GetDocument().MaxRow() - mCurrentAddress.Row() + 1; + // If not done already, trim the length to fit. + if (nLength > nMaxLen) + nLength = nMaxLen; + + std::vector<ScFormulaCell*> aFormulaCells(nLength); + ScAddress aAddr(mCurrentAddress); + for (size_t nIdx = 0; nIdx < nLength; ++nIdx) + { + aFormulaCells[nIdx] = new ScFormulaCell(mrDocument, aAddr, rFormulas[nIdx], meGrammar); + aAddr.IncRow(1); + } + + mpDocShell->GetDocFunc().SetFormulaCells(mCurrentAddress, aFormulaCells, true); +} + +void AddressWalkerWriter::writeMatrixFormula(const OUString& aFormula, SCCOL nCols, SCROW nRows) +{ + ScRange aRange; + aRange.aStart = mCurrentAddress; + aRange.aEnd = mCurrentAddress; + if (nCols > 1) + aRange.aEnd.IncCol(nCols - 1); + if (nRows > 1) + aRange.aEnd.IncRow(nRows - 1); + mpDocShell->GetDocFunc().EnterMatrix(aRange, nullptr, nullptr, aFormula, false, false, OUString(), meGrammar ); +} + +void AddressWalkerWriter::writeString(const OUString& aString) +{ + mpDocShell->GetDocFunc().SetStringCell(mCurrentAddress, aString, true); +} + +void AddressWalkerWriter::writeString(const char* aCharArray) +{ + writeString(OUString::createFromAscii(aCharArray)); +} + +void AddressWalkerWriter::writeBoldString(const OUString& aString) +{ + ScFieldEditEngine& rEngine = mrDocument.GetEditEngine(); + rEngine.SetTextCurrentDefaults(aString); + SfxItemSet aItemSet = rEngine.GetEmptyItemSet(); + SvxWeightItem aWeight(WEIGHT_BOLD, EE_CHAR_WEIGHT); + aItemSet.Put(aWeight); + rEngine.QuickSetAttribs(aItemSet, ESelection(0, 0, 0, aString.getLength()) ); + std::unique_ptr<EditTextObject> pEditText(rEngine.CreateTextObject()); + mpDocShell->GetDocFunc().SetEditCell(mCurrentAddress, *pEditText, true); +} + +void AddressWalkerWriter::writeValue(double aValue) +{ + mpDocShell->GetDocFunc().SetValueCell(mCurrentAddress, aValue, true); +} + +// DataCellIterator + +DataCellIterator::DataCellIterator(const ScRange& aInputRange, bool aByColumn) + : mInputRange(aInputRange) + , mByColumn(aByColumn) + , mCol(0) + , mRow(0) +{ + if(aByColumn) + mCol = aInputRange.aStart.Col(); + else + mRow = aInputRange.aStart.Row(); +} + +bool DataCellIterator::hasNext() const +{ + if(mByColumn) + return mCol <= mInputRange.aEnd.Col(); + else + return mRow <= mInputRange.aEnd.Row(); +} + +void DataCellIterator::next() +{ + if(mByColumn) + mCol++; + else + mRow++; +} + +ScAddress DataCellIterator::get() +{ + return getRelative(0); +} + +ScAddress DataCellIterator::getRelative(int aDelta) +{ + if(mByColumn) + { + SCCOL aNewColumn = mCol + aDelta; + if(aNewColumn < mInputRange.aStart.Col() || aNewColumn > mInputRange.aEnd.Col()) + { + ScAddress aResult; + aResult.SetInvalid(); + return aResult; + } + return ScAddress(aNewColumn, mInputRange.aStart.Row(), mInputRange.aStart.Tab()); + } + else + { + SCROW aNewRow = mRow + aDelta; + if(aNewRow < mInputRange.aStart.Row() || aNewRow > mInputRange.aEnd.Row()) + { + ScAddress aResult; + aResult.SetInvalid(); + return aResult; + } + return ScAddress(mInputRange.aStart.Col(), aNewRow, mInputRange.aStart.Tab()); + } +} + +// DataRangeIterator + +DataRangeIterator::DataRangeIterator(const ScRange& aInputRange) : + mInputRange(aInputRange), + mIndex(0) +{} + +DataRangeIterator::~DataRangeIterator() +{} + +sal_Int32 DataRangeIterator::index() +{ + return mIndex; +} + +// DataRangeByColumnIterator + +DataRangeByColumnIterator::DataRangeByColumnIterator(const ScRange& aInputRange) + : DataRangeIterator(aInputRange) + , mCol(aInputRange.aStart.Col()) +{} + +bool DataRangeByColumnIterator::hasNext() +{ + return mCol <= mInputRange.aEnd.Col(); +} + +void DataRangeByColumnIterator::next() +{ + mCol++; + mIndex++; +} + +ScRange DataRangeByColumnIterator::get() +{ + return ScRange( + ScAddress(mCol, mInputRange.aStart.Row(), mInputRange.aStart.Tab()), + ScAddress(mCol, mInputRange.aEnd.Row(), mInputRange.aEnd.Tab()) + ); +} + +size_t DataRangeByColumnIterator::size() +{ + return mInputRange.aEnd.Row() - mInputRange.aStart.Row() + 1; +} + +void DataRangeByColumnIterator::reset() +{ + mCol = mInputRange.aStart.Col(); +} + +DataCellIterator DataRangeByColumnIterator::iterateCells() +{ + return DataCellIterator(get(), false); +} + +// DataRangeByRowIterator + +DataRangeByRowIterator::DataRangeByRowIterator(const ScRange& aInputRange) + : DataRangeIterator(aInputRange) + , mRow(aInputRange.aStart.Row()) +{} + +bool DataRangeByRowIterator::hasNext() +{ + return mRow <= mInputRange.aEnd.Row(); +} + +void DataRangeByRowIterator::next() +{ + mRow++; + mIndex++; +} + +ScRange DataRangeByRowIterator::get() +{ + return ScRange( + ScAddress(mInputRange.aStart.Col(), mRow, mInputRange.aStart.Tab()), + ScAddress(mInputRange.aEnd.Col(), mRow, mInputRange.aEnd.Tab()) + ); +} + +size_t DataRangeByRowIterator::size() +{ + return mInputRange.aEnd.Col() - mInputRange.aStart.Col() + 1; +} + +void DataRangeByRowIterator::reset() +{ + mRow = mInputRange.aStart.Row(); +} + +DataCellIterator DataRangeByRowIterator::iterateCells() +{ + return DataCellIterator(get(), true); +} + + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/ZTestDialog.cxx b/sc/source/ui/StatisticsDialogs/ZTestDialog.cxx new file mode 100644 index 000000000..a1731fa8f --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/ZTestDialog.cxx @@ -0,0 +1,167 @@ +/* -*- 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 <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <ZTestDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +ScZTestDialog::ScZTestDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) : + ScStatisticsTwoVariableDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/ztestdialog.ui", "ZTestDialog") +{ + m_xDialog->set_title(ScResId(STR_ZTEST)); +} + +ScZTestDialog::~ScZTestDialog() +{} + +void ScZTestDialog::Close() +{ + DoClose( ScZTestDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScZTestDialog::GetUndoNameId() +{ + return STR_ZTEST_UNDO_NAME; +} + +ScRange ScZTestDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + std::unique_ptr<DataRangeIterator> pVariable1Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range)); + else + pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range)); + + std::unique_ptr<DataRangeIterator> pVariable2Iterator; + if (mGroupedBy == BY_COLUMN) + pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range)); + else + pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range)); + + aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get()); + aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get()); + + aOutput.writeBoldString(ScResId(STR_ZTEST)); + aOutput.newLine(); + + // Alpha + aOutput.writeString(ScResId(STR_LABEL_ALPHA)); + aOutput.nextColumn(); + aOutput.writeValue(0.05); + aTemplate.autoReplaceAddress("%ALPHA%", aOutput.current()); + aOutput.newLine(); + + // Hypothesized mean difference + aOutput.writeString(ScResId(STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL)); + aOutput.nextColumn(); + aOutput.writeValue(0); + aTemplate.autoReplaceAddress("%HYPOTHESIZED_MEAN_DIFFERENCE%", aOutput.current()); + aOutput.newLine(); + + // Variable Label + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_1_LABEL)); + aOutput.nextColumn(); + aOutput.writeBoldString(ScResId(STR_VARIABLE_2_LABEL)); + aOutput.newLine(); + + // Known Variance + aOutput.writeString(ScResId(STR_ZTEST_KNOWN_VARIANCE)); + aOutput.nextColumn(); + aOutput.writeValue(0); + aTemplate.autoReplaceAddress("%KNOWN_VARIANCE_VARIABLE1%", aOutput.current()); + aOutput.nextColumn(); + aOutput.writeValue(0); + aTemplate.autoReplaceAddress("%KNOWN_VARIANCE_VARIABLE2%", aOutput.current()); + aOutput.newLine(); + + // Mean + aOutput.writeString(ScResId(STRID_CALC_MEAN)); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE1_RANGE%)"); + aTemplate.autoReplaceAddress("%MEAN_VARIABLE1%", aOutput.current()); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.nextColumn(); + aTemplate.setTemplate("=AVERAGE(%VARIABLE2_RANGE%)"); + aTemplate.autoReplaceAddress("%MEAN_VARIABLE2%", aOutput.current()); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // Observations + aOutput.writeString(ScResId(STR_OBSERVATIONS_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE1_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%OBSERVATION_VARIABLE1%", aOutput.current()); + aOutput.nextColumn(); + aTemplate.setTemplate("=COUNT(%VARIABLE2_RANGE%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%OBSERVATION_VARIABLE2%", aOutput.current()); + aOutput.newLine(); + + // Observed mean difference + aOutput.writeString(ScResId(STR_OBSERVED_MEAN_DIFFERENCE_LABEL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=%MEAN_VARIABLE1% - %MEAN_VARIABLE2%"); + aOutput.writeMatrixFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%OBSERVED_MEAN_DIFFERENCE%", aOutput.current()); + aOutput.newLine(); + + // z + aOutput.writeString(ScResId(STR_ZTEST_Z_VALUE)); + aOutput.nextColumn(); + aTemplate.setTemplate("=(%OBSERVED_MEAN_DIFFERENCE% - %HYPOTHESIZED_MEAN_DIFFERENCE%) / SQRT( %KNOWN_VARIANCE_VARIABLE1% / %OBSERVATION_VARIABLE1% + %KNOWN_VARIANCE_VARIABLE2% / %OBSERVATION_VARIABLE2% )"); + aOutput.writeFormula(aTemplate.getTemplate()); + aTemplate.autoReplaceAddress("%Z_STAT%", aOutput.current()); + aOutput.newLine(); + + // P one-tail + aOutput.writeString(ScResId(STR_ZTEST_P_ONE_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=1 - NORMSDIST(ABS(%Z_STAT%))"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // z critical one-tail + aOutput.writeString(ScResId(STR_ZTEST_Z_CRITICAL_ONE_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=-NORMSINV(%ALPHA%)"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // P two-tail + aOutput.writeString(ScResId(STR_ZTEST_P_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=2 * NORMSDIST(-ABS(%Z_STAT%))"); + aOutput.writeFormula(aTemplate.getTemplate()); + aOutput.newLine(); + + // z critical two-tail + aOutput.writeString(ScResId(STR_ZTEST_Z_CRITICAL_TWO_TAIL)); + aOutput.nextColumn(); + aTemplate.setTemplate("=-NORMSINV(%ALPHA%/2)"); + aOutput.writeFormula(aTemplate.getTemplate()); + + return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ |