Question Array Class and Output in Excel

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
Hello Everyone,

i'm a newbie in this Community. I have some Problems in C#. First of all what i want to do ist the Following.

I want to make calculation in C# and the Output should be written in an Excel-Sheet. The First Colume should have Main-Values of a Contract and the Second Colume should have the additional Values. I have defined every cell in Excel, for example A1 has the name ZB and B1 ZB_add. Overall i have 40 Values, so 50 Values for the Main Part and 50 for the additional part. The only differ in the name, as you can see the additional one have at the end "_add".

What i have do in c#
1. write an Constructor where i declare only the Main Values. It looks likes this

public class Output
{
//declare Parameters
public double ZB;
public double HV;
....
}
public Output(double? ZB,double HV,...)
{
this.ZB=ZB;
this.HV=HV;
...
}
2. I write an Array class with:
Output out= new Output[2];
out[0]= new Output(value1,value2,...); //this are the main-values
out[1]= new Output(value1_add,value2_add,...); // this are the additional values


So far so good. How can i make the output in Excel as efficienc as possible. As you can see the cells only differ in the präfix "_add".

Thanks for helping :)
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
The first question I would ask is if Excel spreadsheet is absolutely necessary, or is a .CSV file that can be loaded into Excel sufficient?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Next, when you find yourself naming variabes with number suffixes, or suffixes of increasing values, that is usually a big hint that you should be using arrays, not individual variables.

And finally, when you find yourself naming two variables almost the same name because you want to relate one to the other, that is usually a hint that the two variables should be put into a struct or class.
 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
The first question I would ask is if Excel spreadsheet is absolutely necessary, or is a .CSV file that can be loaded into Excel sufficient?
I forget to say that first the calculated values are written in an XML-documenta. And it is necessary to write it in an Excel sheet.
 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
Wenn Sie Variablen mit Zahlensuffixen oder Suffixen mit steigenden Werten benennen, ist dies normalerweise ein großer Hinweis darauf, dass Sie Arrays und keine einzelnen Variablen verwenden sollten.

Und schließlich, wenn Sie feststellen, dass Sie zwei Variablen fast gleich benennen, weil Sie sie miteinander in Beziehung setzen möchten, ist dies normalerweise ein Hinweis darauf, dass die beiden Variablen in eine Struktur oder Klasse eingefügt werden sollten
I dont want relate them. I just want have a seperat output. First colume Main-values and second additional values.

Thanks for your hints
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Also another aside, if you have a method or constructor that has more 8 parameters, it's usually a sign that you are doing something wrong -- perhaps not following SRP, perhaps wrong abstraction, perhaps wrong data structure design, etc.) It looks like your Output class will have 50 parameters. Definitely a sign that something maybe wrong.
 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
Also another aside, if you have a method or constructor that has more 8 parameters, it's usually a sign that you are doing something wrong -- perhaps not following SRP, perhaps wrong abstraction, perhaps wrong data structure design, etc.) It looks like your Output class will have 50 parameters. Definitely a sign that something maybe wrong.
The number of parameters are correct. If you have an contract like an insurance contract it is normaly that you have many parameters.
To sum up what i have
1.Excel sheet with 2 Columns with Main Value and addition values. Cells are named
2.Cstr for only the Main Values
3.Array Class
4. Put all variables in an array
5. Method that takes the array and write it in a xml document.
6.VBA-Makro that writes the elements of the xml document in excel

The main Problem is the point 5.

I really thanks for your help.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
If all you need to do is to write out an array into an XML document, look at XML serialization:


Now if it's a matter of putting values into an Excel document, there's a few approaches:
1) If you are running on a client side machine and Excel is installed on the machine, then use the Excel Object Model to manipulate a spreadsheet.
2) If you are running server side, use the OpenXML APIs built into the .NET Framework, or use a 3rd party library (ex. Aspose) to create a .xlsx file.
 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
If all you need to do is to write out an array into an XML document, look at XML serialization:


Now if it's a matter of putting values into an Excel document, there's a few approaches:
1) If you are running on a client side machine and Excel is installed on the machine, then use the Excel Object Model to manipulate a spreadsheet.
2) If you are running server side, use the OpenXML APIs built into the .NET Framework, or use a 3rd party library (ex. Aspose) to create a .xlsx file.
Thanks thats looks good for me. Do you know if i can control the output like

if (only main values are needed)
{
write xml with the nodes of main-values
}
else if (only additional values are needed)
{
write xml with the nodes of additional-values
}
else
{
give me all values (main and additional values)
}

This will very good, if i have an controlled output.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
This is exactly why I was saying that you had two related values. You have the base value and the additional value. But you said that they are not related at all, so I guess no class or struct for you.
 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
This is exactly why I was saying that you had two related values. You have the base value and the additional value. But you said that they are not related at all, so I guess no class or struct for you.
Oh sorry i understand the "relation" wrong. Sorry for that. I think you mean with relation that i compare every value.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
Have an array of classes. Each class has a Base value and an Add value member. Implement IXmlSerializable on the class so that you can control what gets put out into the XML for each class.

 

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner

Cauchy

Member
Joined
May 4, 2021
Messages
9
Programming Experience
Beginner
When i have
double [] jb = New double [2];
double [] xy = New double [2];
double [] zw = New double [2];
....

Can i declare many Array variables in an compact way?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,756
Location
Sydney, Australia
Programming Experience
10+
When i have
double [] jb = New double [2];
double [] xy = New double [2];
double [] zw = New double [2];
....

Can i declare many Array variables in an compact way?
Variables are variables. Why should array variables be any different to any other variables?

Also, you're not just declaring variables there. You are declaring variables, creating objects and assigning those objects to those variables.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
2,994
Location
Chesapeake, VA
Programming Experience
10+
The number of parameters are correct. If you have an contract like an insurance contract it is normaly that you have many parameters.
Yes. But at some point you have to decide you are not going to explicitly declare and name 50+ parameters, and use a dictionary or some other data structure instead to keep track of all those parameters.

Also consider calling a constructor or method with 50+ parameters. The potential for accidentally swapping the order of two parameters feels to be astronomically high, specially if all the parameters are the same type and the compiler won't warn you that you accidentally swapped two parameters around. Yes, C# 4.0 (?) started support for named parameters to help alleviate this problem, but now you go from just typing in:
C#:
var output = new Output(1.0, 2.5, ...);
to typing in:
C#:
var output = new Output(ZB: 1.0, HV: 2.5, ...);
(The same problem applies even if magic numbers are not used, and variables are passed into the constructor instead.)

Or are you not concerned about typing all those values in because you are the "software architect" and you have a bunch of "code monkeys" to actually do all real work and long term maintenance after you've created your proof of concept?
 
Top Bottom