I would store the values in a Dictionary<string, List<string>>, and I would store only the Cell data for the number of row values per column that were actually used.
You could use the 'UsedRange function in Excel to determine (for the entire worksheet) what the last row used was. Note that 'UsedRange will return a Range that includes every Cell that at any point had a value, independent of whether that Cell has a value now.
And, with some programming you could (in theory ... I have not done this myself), calculate for each column what the first, and last, rows were in that column that had "real" data; see: [
^].
However, that approach would probably mean you'd want to write something like this:
public Dictionary<string, List<dctColNameToColData>>;
public class XLColumnData
{
public string Name { set; get; }
public int StartRow { set; get; }
public int EndRow { set; get; }
public List<string> ColumnData { set; get; }
public XLColumnData(string name, int srow, int erow, List<string> cdata)
{
}
}
I don't have Excel on this machine, so can't be more specific than this.