sc/qa/unit/data/xlsx/tdf156814.xlsx |binary
sc/qa/unit/ucalc_solver.cxx | 48 +++++++++++++++++++++++--------
sc/source/core/data/SolverSettings.cxx | 51 +++++++++++++++++++++++++++++++++
3 files changed, 87 insertions(+), 12 deletions(-)
New commits:
commit d3049ab4786005c6bd17c66f8edcb98210bf511c
Author: Rafael Lima <rafael.palma.l...@gmail.com>
AuthorDate: Fri Mar 8 21:49:47 2024 +0100
Commit: Tomaž Vajngerl <qui...@gmail.com>
CommitDate: Tue Mar 12 15:36:28 2024 +0100
tdf#156814 Remove sheet name if range belongs to the same sheet (Solver)
When solver models are imported from XLSX, ranges come with sheet names
even when they belong to the same sheet, which clutter the solver dialog.
This patch checks when this happens and remove sheet names when the solver
model is imported.
Change-Id: I3994f1312667946aab330c734ff820e94673d018
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/164610
Tested-by: Jenkins
Reviewed-by: Tomaž Vajngerl <qui...@gmail.com>
diff --git a/sc/qa/unit/data/xlsx/tdf156814.xlsx
b/sc/qa/unit/data/xlsx/tdf156814.xlsx
new file mode 100644
index 000000000000..49e430b41554
Binary files /dev/null and b/sc/qa/unit/data/xlsx/tdf156814.xlsx differ
diff --git a/sc/qa/unit/ucalc_solver.cxx b/sc/qa/unit/ucalc_solver.cxx
index 7834597e9c07..f7db19cad59a 100644
--- a/sc/qa/unit/ucalc_solver.cxx
+++ b/sc/qa/unit/ucalc_solver.cxx
@@ -34,19 +34,19 @@ std::vector<ModelConstraint>
SolverTest::CreateConstraintsModelA()
std::vector<ModelConstraint> aConstraints;
ModelConstraint aConstr1;
- aConstr1.aLeftStr = "C1:C10";
+ aConstr1.aLeftStr = "$C$1:$C$10";
aConstr1.nOperator = CO_LESS_EQUAL;
aConstr1.aRightStr = "100";
aConstraints.push_back(aConstr1);
ModelConstraint aConstr2;
- aConstr2.aLeftStr = "F5";
+ aConstr2.aLeftStr = "$F$5";
aConstr2.nOperator = CO_EQUAL;
aConstr2.aRightStr = "500";
aConstraints.push_back(aConstr2);
ModelConstraint aConstr3;
- aConstr3.aLeftStr = "D1:D5";
+ aConstr3.aLeftStr = "$D$1:$D$5";
aConstr3.nOperator = CO_BINARY;
aConstr3.aRightStr = "";
aConstraints.push_back(aConstr3);
@@ -59,15 +59,15 @@ void SolverTest::TestConstraintsModelA(SolverSettings*
pSettings)
{
std::vector<ModelConstraint> aConstraints = pSettings->GetConstraints();
- CPPUNIT_ASSERT_EQUAL(OUString("C1:C10"), aConstraints[0].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$C$1:$C$10"), aConstraints[0].aLeftStr);
CPPUNIT_ASSERT_EQUAL(CO_LESS_EQUAL, aConstraints[0].nOperator);
CPPUNIT_ASSERT_EQUAL(OUString("100"), aConstraints[0].aRightStr);
- CPPUNIT_ASSERT_EQUAL(OUString("F5"), aConstraints[1].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$F$5"), aConstraints[1].aLeftStr);
CPPUNIT_ASSERT_EQUAL(CO_EQUAL, aConstraints[1].nOperator);
CPPUNIT_ASSERT_EQUAL(OUString("500"), aConstraints[1].aRightStr);
- CPPUNIT_ASSERT_EQUAL(OUString("D1:D5"), aConstraints[2].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$D$1:$D$5"), aConstraints[2].aLeftStr);
CPPUNIT_ASSERT_EQUAL(CO_BINARY, aConstraints[2].nOperator);
CPPUNIT_ASSERT_EQUAL(OUString(""), aConstraints[2].aRightStr);
}
@@ -93,19 +93,19 @@ CPPUNIT_TEST_FIXTURE(SolverTest, testSingleModel)
CPPUNIT_ASSERT_EQUAL(sal_Int32(0),
pSettings->GetParameter(SP_CONSTR_COUNT).toInt32());
// Create a simple model
- pSettings->SetParameter(SP_OBJ_CELL, OUString("A1"));
+ pSettings->SetParameter(SP_OBJ_CELL, OUString("$A$1"));
pSettings->SetParameter(SP_OBJ_TYPE, OUString::number(OT_MINIMIZE));
pSettings->SetParameter(SP_OBJ_VAL, OUString::number(0));
- pSettings->SetParameter(SP_VAR_CELLS, OUString("D1:D5"));
+ pSettings->SetParameter(SP_VAR_CELLS, OUString("$D$1:$D$5"));
std::vector<ModelConstraint> aConstraints = CreateConstraintsModelA();
pSettings->SetConstraints(aConstraints);
// Test if the model parameters were set
- CPPUNIT_ASSERT_EQUAL(OUString("A1"), pSettings->GetParameter(SP_OBJ_CELL));
+ CPPUNIT_ASSERT_EQUAL(OUString("$A$1"),
pSettings->GetParameter(SP_OBJ_CELL));
CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int32>(OT_MINIMIZE),
pSettings->GetParameter(SP_OBJ_TYPE).toInt32());
CPPUNIT_ASSERT_EQUAL(OUString("0"), pSettings->GetParameter(SP_OBJ_VAL));
- CPPUNIT_ASSERT_EQUAL(OUString("D1:D5"),
pSettings->GetParameter(SP_VAR_CELLS));
+ CPPUNIT_ASSERT_EQUAL(OUString("$D$1:$D$5"),
pSettings->GetParameter(SP_VAR_CELLS));
// Test if the constraints were correctly set before saving
CPPUNIT_ASSERT_EQUAL(sal_Int32(3),
pSettings->GetParameter(SP_CONSTR_COUNT).toInt32());
@@ -120,11 +120,11 @@ CPPUNIT_TEST_FIXTURE(SolverTest, testSingleModel)
CPPUNIT_ASSERT(pSettings);
// Test if the model parameters remain set in the file
- CPPUNIT_ASSERT_EQUAL(OUString("A1"), pSettings->GetParameter(SP_OBJ_CELL));
+ CPPUNIT_ASSERT_EQUAL(OUString("$A$1"),
pSettings->GetParameter(SP_OBJ_CELL));
CPPUNIT_ASSERT_EQUAL(static_cast<sal_Int32>(OT_MINIMIZE),
pSettings->GetParameter(SP_OBJ_TYPE).toInt32());
CPPUNIT_ASSERT_EQUAL(OUString("0"), pSettings->GetParameter(SP_OBJ_VAL));
- CPPUNIT_ASSERT_EQUAL(OUString("D1:D5"),
pSettings->GetParameter(SP_VAR_CELLS));
+ CPPUNIT_ASSERT_EQUAL(OUString("$D$1:$D$5"),
pSettings->GetParameter(SP_VAR_CELLS));
// Test if the constraints remain correct after saving
CPPUNIT_ASSERT_EQUAL(sal_Int32(3),
pSettings->GetParameter(SP_CONSTR_COUNT).toInt32());
@@ -192,4 +192,28 @@ CPPUNIT_TEST_FIXTURE(SolverTest, tdf158735)
CPPUNIT_ASSERT_EQUAL(OUString("80"),
pSettings->GetParameter(SP_STAGNATION_LIMIT));
}
+// Tests if range addresses from XLSX files that belong to the same sheet are
not imported
+// with the sheet name, since it is unnecessary and clutters the dialog
+CPPUNIT_TEST_FIXTURE(SolverTest, tdf156814)
+{
+ createScDoc("xlsx/tdf156814.xlsx");
+ ScDocument* pDoc = getScDoc();
+
+ ScTable* pTable = pDoc->FetchTable(0);
+ std::shared_ptr<sc::SolverSettings> pSettings =
pTable->GetSolverSettings();
+ CPPUNIT_ASSERT(pSettings);
+ // Ranges must not contain the sheet name
+ CPPUNIT_ASSERT_EQUAL(OUString("$F$2"),
pSettings->GetParameter(SP_OBJ_CELL));
+ CPPUNIT_ASSERT_EQUAL(OUString("$A$2:$A$11,$C$2:$D$11"),
pSettings->GetParameter(SP_VAR_CELLS));
+
+ // Check also the constraints (ranges must not contain sheet name either)
+ std::vector<ModelConstraint> aConstraints = pSettings->GetConstraints();
+ CPPUNIT_ASSERT_EQUAL(OUString("$H$2:$H$11"), aConstraints[0].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$I$2:$I$11"), aConstraints[0].aRightStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$H$2:$H$11"), aConstraints[1].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("10"), aConstraints[1].aRightStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("$I$2:$I$11"), aConstraints[2].aLeftStr);
+ CPPUNIT_ASSERT_EQUAL(OUString("0"), aConstraints[2].aRightStr);
+}
+
CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/core/data/SolverSettings.cxx
b/sc/source/core/data/SolverSettings.cxx
index 64badb8c2790..dd618d1e868b 100644
--- a/sc/source/core/data/SolverSettings.cxx
+++ b/sc/source/core/data/SolverSettings.cxx
@@ -9,9 +9,11 @@
*/
#include <global.hxx>
+#include <compiler.hxx>
#include <table.hxx>
#include <docsh.hxx>
#include <rtl/math.hxx>
+#include <o3tl/string_view.hxx>
#include <solverutil.hxx>
#include <unotools/charclass.hxx>
#include <SolverSettings.hxx>
@@ -412,6 +414,12 @@ bool SolverSettings::ReadConstraintPart(ConstraintPart
ePart, tools::Long nIndex
if (pRangeData)
{
rValue = pRangeData->GetSymbol();
+ // tdf#156814 Remove sheet name if it is a range that refers to the
same sheet
+ ScRange aRange;
+ ScRefFlags nFlags = aRange.ParseAny(rValue, m_rDoc);
+ bool bIsValidRange = (nFlags & ScRefFlags::VALID) == ScRefFlags::VALID;
+ if (bIsValidRange && m_rTable.GetTab() == aRange.aStart.Tab())
+ rValue = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
return true;
}
return false;
@@ -521,6 +529,49 @@ bool SolverSettings::ReadParamValue(SolverParameter
eParam, OUString& rValue, bo
rValue = pRangeData->GetSymbol();
if (bRemoveQuotes)
ScGlobal::EraseQuotes(rValue, '"');
+
+ // tdf#156814 Remove sheet name from the objective cell and value if
they refer to the same sheet
+ if (eParam == SP_OBJ_CELL || eParam == SP_OBJ_VAL)
+ {
+ ScRange aRange;
+ ScRefFlags nFlags = aRange.ParseAny(rValue, m_rDoc);
+ bool bIsValidRange = ((nFlags & ScRefFlags::VALID) ==
ScRefFlags::VALID);
+
+ if (bIsValidRange && m_rTable.GetTab() == aRange.aStart.Tab())
+ rValue = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
+ }
+ else if (eParam == SP_VAR_CELLS)
+ {
+ // Variable cells may contain multiple ranges separated by ';'
+ sal_Int32 nIdx = 0;
+ OUString sNewValue;
+ bool bFirst = true;
+ // Delimiter character to separate ranges
+ sal_Unicode cDelimiter =
ScCompiler::GetNativeSymbolChar(OpCode::ocSep);
+
+ do
+ {
+ OUString aRangeStr(o3tl::getToken(rValue, 0, cDelimiter,
nIdx));
+ ScRange aRange;
+ ScRefFlags nFlags = aRange.ParseAny(aRangeStr, m_rDoc);
+ bool bIsValidRange = (nFlags & ScRefFlags::VALID) ==
ScRefFlags::VALID;
+
+ if (bIsValidRange && m_rTable.GetTab() == aRange.aStart.Tab())
+ aRangeStr = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
+
+ if (bFirst)
+ {
+ sNewValue = aRangeStr;
+ bFirst = false;
+ }
+ else
+ {
+ sNewValue += OUStringChar(cDelimiter) + aRangeStr;
+ }
+ } while (nIdx > 0);
+
+ rValue = sNewValue;
+ }
return true;
}
return false;
