Retrieve last column has value in excel

Question

I have a sheet which have many columns. suppose I have below 6 columns. Number of real columns are more

x    temp 1 temp 2  temp 3  temp 4  temp 5  temp 6
101   22                    
102   23     34             
103   56                    
104   34     56      78       98        
105   11                    
106   12     32      43         
107   2       5      89       99     101     123

in this example I want to sum last column has value which is 22+34+56+98+11+43+123

I use LinqToExcel but don't know how to do that.

var ff = database.Where(w => BetweenTwoDates(txt_fromDate.Text, w["Time Column"].ToString(), txt_toDate.Text) == true);
            var grouped = ff.GroupBy(row => row["Sample column"].ToString());
            foreach (var g in grouped)
            {
                X.Add(g.Key);
                var temp = g.Where(w => w["Temp "].ToString() != "");
                Y.Add(g.Sum(a => (double.Parse(a["Temp"]))));
            }

Where does this values come from `22+34+56+98+11+43+123` last column only have `123` ?? — Mahesh, Mar 13 '15 at 07:51
@CoderofCode I want to sum the last column for each row. for example the 101 last column is 22 and 102 row last column is 34 — user3073682, Mar 13 '15 at 07:59
Is that `Generic List`? Can you change your List to DataTable ? — zey, Mar 13 '15 at 08:25
@zey It possible to change it to DataTable. acutually database is IEnumerable database; — user3073682, Mar 13 '15 at 08:50

zey · Accepted Answer · 2015-03-13T09:16:34.380

Convert your IEnumerable to DataTable and try this code ,

 int _total = 0;
                foreach (DataRow _dr in dt.Rows) // Looping Rows
                {
                    int _value = 0;
                    int _colCount = 1;
                    foreach (var _column in _dr.ItemArray) // Looping Columns
                    {
                        if (_column.ToString() != "")
                        {
                          //if column value is not equal to blank , keep assign it to _value 
                           _value = Int32.Parse(_column.ToString());

                        }
                        else
                        {
                         //if column value is equal to blank , sum-up the _total with _value and break column looping and go to next row
                            _total += _value;
                            break;
                        }

                      if (_colCount == _dr.ItemArray.Length)
                        {
                            _total += _value;
                            break;
                        }
                     _colCount++;
                    }
                }

Here , dt is your converted DataTable and _total value will be the result what you need.
Hope it's helpful for you :)

Edit

This is how to convert to DataTable ,

   public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        
}

Thanks for reply but the code has some problems, first if the last columns has value it will not go to else section and the value is not considered for rows with completed columns. — user3073682, Mar 13 '15 at 09:47

Retrieve last column has value in excel

1 Answers1