Can you dynamically create variables based on rows in an Excel file in C#?

Question

I am trying to make a C# program that takes coordinates from an excel file in the sense that each column is x,y,z and r, respectively, and each row is a different point. I would like to be able to create variables in the format point0, point1, etc. depending on how many rows there are.

As of right now I am reading each cell into an Array, then manually creating points from that array. In this case there are 4 rows and 4 points (points 0 to 3). This works for now but I have to imagine there is a much easier way of doing this or at least something more dynamic. 4 points is not a big deal but there could be many more.

        int rows = 4;

        for(int i = 0; i < 4; i++)
        {
           for(int j = 0; j < rows; j++)
           {
               points[i,j] = excel.ReadCell(i, j);
           }
        }
      
        for(int i = 0; i < 4; i++)
        {
            point0[0, i] = points[0, i];
        }

        for (int i = 0; i < 4; i++)
        {
            point1[1, i] = points[1, i];
        }

        for (int i = 0; i < 4; i++)
        {
            point2[2, i] = points[2, i];
        }

        for (int i = 0; i < 4; i++)
        {
            point3[3, i] = points[3, i];
        }

Even condensing the set of loops where the points are manually created would save time, I am just not sure if there is a way to say something such as

       for(int i = 0; i < rows; i++)
       {
           for(int j = 0; j < cols; j++)
           {
               point+"i"[i,j] = points[i,j]
           }
       }

Where the ith iteration is concatenated to the variable name.

Any help would be greatly appreciated, and I am open to all recommendations (I am pretty new to C# if you can't tell)

Rather than store these in variables whose names get larger numbers added, you can just store them in a list. — Tobias Kildetoft, Aug 05 '20 at 18:19
If you find yourself naming variables with numbers (e.g. `point0`, `point1`, `point2`) you're probably doing it wrong. Use an array instead. In this case you might need an array of arrays. — John Wu, Aug 05 '20 at 23:30

score 0 · Accepted Answer · answered Aug 05 '20 at 19:42

I would define a class of what the combination of the four points mean. Not based on the row or column that they're stored on but what they actually represent.

public class Shape
{
    public int Start { get; set; }
    public int End { get; set; }
    public int Mean { get; set; }
    public int Median { get; set; }
}

Then as you loop through each row you can Create and add all four points at the same time. Some thing like this.

public List GetShapesFromExcel() { var list = new List();

        int StartColumn = 'x';
        int EndColumn = 'y';
        int MeanColumn = 'z';
        int MedianColumn = 'r';

        foreach (var row in workSheet)
        {
            var shape = new Shape()
            {
                Start = excel.ReadCell(row, StartColumn);
                End = excel.ReadCell(row, EndColumn);
                Mean = excel.ReadCell(row, MeanColumn);
                Median = excel.ReadCell(row, MedianColumn);
            };
        
        }
    return list;
    }

I'm taking a wild stab in the dark on what you're data actually represents but I would take the time to go ahead and spin that up into a real object so that it's easier to reason about as you're writing the code.

variables like ‘I’ & ‘J’ Don't save enough time in this case to be useful.

last suggestion is to go ahead and check out the package EPPlus. that package has the ability to turn rows into structured classes

check this out to get started. How to parse excel rows back to types using EPPlus

Can you dynamically create variables based on rows in an Excel file in C#?

1 Answers1