Convert solver excel in C#

Question

0.00/5 (No votes)

See more:

Excel-2016

Hello,

Excuse me for my limited English. If you have trouble understanding what I'm saying, don't hesitate to ask me, I really need help.

I need to convert an Excel file to C# software.

The current operator uses a large Excel file to perform complex calculations. But to get those results, he uses solvers in Excel.

I have to create software that automates all the measurement part then the calculations and therefore the solvers.

I admit it's really vague to me how solvers work, whether in Excel or C#.

What I have tried:

I saw that in C# there is a "Microsoft Solver foundation" library. I found a tutorial on this site: https://www.codeproject.com/Articles/1183168/Solving-optimization-problems-with-Microsoft-Solve

But it remains complex and I find it difficult to make the link with my situation.

What I have the most trouble understanding is how to make the interaction between the variables and the result.

In excel I visualize the solver on each tab concerned, example:

objective to be defined : R18
At: Min
Variable cells: L18;Q18
Constraint:
L18 >= 0
L18 >= 0
J18 <= K18
N18 >= C54
L18 <= 4
L18 <= 4
J18 >= I18
J18 <= K18
J18 >= I18

Checked: make unconstrained variables non-negative.

Select a resolution = GRG Nonlinear

(For information, I removed the $, I was unable to put an image.)

How to transcribe this to C#.
in the excel solver i tried to do some tests and if i change the "goal" cell to a blank cell the solver tells me that the goal must contain a formula. Which is normally the case, but how to give it a formula in C#. Also knowing that the formula will look for data in multiple tabs which are also formulas etc.

Another potential solution:
Have the excel file in the background. The C# software updates the data before running a macro that would launch the solvers (a macro that already exists). Then I retrieve the data in Excel in order to display it.
It's not super clean, but maybe faster for development, although I never found a quick fix to use Excel from Dotnet, before I was in VB and it was long enough to access read and write cells.

And then, the excel solver indicates whether the resolution of the problem has been found or not. In the case of the execution of the macro in C#, can we recover these messages?

In macro I have this:

VB

...
Sheets("SheetX").Select
Range("R18").Select
SolverOk SetCell:="$R$18", MaxMinVal:=2, ValueOf:=0, ByChange:="$L$18,$Q$18", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$R$18", MaxMinVal:=2, ValueOf:=0, ByChange:="$L$18,$Q$18", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve

I find some data, but not the constraints, is this normal?

Thank you for your precious help.

Posted 27-Jun-22 4:31am

Megamarioo

Updated 27-Jun-22 5:53am

Add a Solution

Comments

[no name] 27-Jun-22 11:14am

Since you admit to not understanding the components you are working with, most suggestions probably won't make any sense. Accept the fact that you will probably need to spend a few days to a week studying (and understanding) the "current" environment before being able to design a "new" environment.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-06-27T05:53:00

Gerry Schmitz:
Since you admit to not understanding the components you are working with, most suggestions probably won't make any sense. Accept the fact that you will probably need to spend a few days to a week studying (and understanding) the "current" environment before being able to design a "new" environment.

Gerry is absolutely right: unless you have a pretty good understanding of what solvers do, how they do it, and what the "command language" to control the solver in Excel is in good detail, no amount of research into solver libraries your code can use is going to help you much. That comes later when you understand what you have to produce.

Start here: Excel Solver Tutorial - Step by Step Easy to use guide for Excel's Solver | solver[^] And probably the links at the end as well, then here: Define and solve a problem by using Solver[^] and be prepared to google when you have read and understood (as well as practiced with) that lot. Then look at the solvers you need to implement, and make sure you understand them before you even start thinking about implementation detail!