how to use group by

[GoogleCodeExporter]

want the data of the columns in a group
e.g. in excel sheet there is education qualification column i want to know how 
many different types of educational qualification columns are available 

Original issue reported on code.google.com by deshmukh...@gmail.com on 6 Jun 2011 at 11:55

[GoogleCodeExporter]

If you can attach the spreadsheet you're working with, then I'll be able to 
help you write the Linq query.

Original comment by paulyo...@gmail.com on 6 Jun 2011 at 1:32

[GoogleCodeExporter]

this is testing file actual file can contain up to 6000 record
and many records are blank so please take care of blank records in where 
condition
thankx for the reply 

waiting for your reply

Original comment by deshmukh...@gmail.com on 7 Jun 2011 at 6:51

Attachments:

• [testing File.xls](https://storage.googleapis.com/google-code-attachments/linqtoexcel/issue-44/comment-2/testing File.xls)

[GoogleCodeExporter]

Although LinqToExcel doesn't support group by, you can still use Linq to group 
the results list and perform the group by. Below is a brief code example 
showing how to group by the Educational Qualification column.

class Program
    {
        static void Main(string[] args)
        {
            var excel = new ExcelQueryFactory("pathToExcelFile");
            excel.AddMapping<Person>(x => x.EducationalQualification, "Educational Qualification *");
            excel.AddMapping<Person>(x => x.Age, "Age *");
            excel.AddMapping<Person>(x => x.Community, "Community *");

            var rows = (from row in excel.Worksheet<Person>("Sheet2")
                        where row.EducationalQualification != ""
                        select row).ToList();

            var grouped = from row in rows
                          group row by row.EducationalQualification
                          into g
                          select new {EducationQualification = g.Key, Count = g.Count()};

            foreach (var group in grouped)
                Console.WriteLine("{0}: {1}", group.EducationQualification, group.Count);

            Console.ReadLine();
        }
    }

    public class Person
    {
        public int Age { get; set; }
        public string Community { get; set; }
        public string EducationalQualification { get; set; }
    }

Original comment by paulyo...@gmail.com on 10 Jun 2011 at 9:08

• Changed state: Fixed

[GoogleCodeExporter]

thank you very much 

Original comment by deshmukh...@gmail.com on 13 Jun 2011 at 5:31

[GoogleCodeExporter]

i had used same gruop method but it giving error as " group is not supported"
help

Original comment by rahul.si...@zeuslearning.com on 16 Nov 2011 at 10:34

[GoogleCodeExporter]

Rahul, you'll need to convert the results to a list first and then you can use 
group by.

Notice the linq query above and how I use the ToList() method.

Here's the code I'm talking about
              var rows = (from row in excel.Worksheet<Person>("Sheet2")
                        where row.EducationalQualification != ""
                        select row).ToList();

Original comment by paulyo...@gmail.com on 21 Nov 2011 at 4:09

[GoogleCodeExporter]

how would i add properties of the first object example

var rows = (from row in excel.Worksheet<Person>("Sheet2")
                        where row.EducationalQualification != ""
                        select row).ToList();

            var grouped = from row in rows
                          group row by row.EducationalQualification
                          into g
                          select new {EducationQualification = g.Key, Count = g.Count()};

            foreach (var group in grouped)
                Console.WriteLine("{0}: {1}: {2}: {3}", group.EducationQualification, group.Count, group.Age, group.Community);

            Console.ReadLine();

I want to be able to write out values such as Age & Community after grouping 
and getting the (Total = g.sum(r => r.amount)) of a column and write all the 
distinct values with the "Total" back to a .csv file.  while i posting do you 
have any easy methods used to write back to a csv??? i will be waiting for a 
response thanks

Original comment by Roarman...@gmail.com on 12 Apr 2012 at 12:16

[GoogleCodeExporter]

Hi, i have tried below code .. i wanted to group and sum the values but its not 
grouping at all. Pls find my code below 
var groupp=from k in excelFile.Worksheet(sheetName).ToList() select k;
                var grouped = from row in groupp group row by row["Issue Type"] into g select new { edd = g.Key, c = g.Sum(row => Convert.ToDouble(row["Time Spent"])) };
                foreach (var groupe in grouped)
                MessageBox.Show("key is" + groupe.edd + " :  count=" + groupe.c);

Original comment by surendar...@gmail.com on 2 Oct 2014 at 6:28

[GoogleCodeExporter]

Try something like this:

var grouped = groupp.GroupBy(row => row["Issue Type"]);
foreach (var g in grouped)
{
  sum = g.Sum(t => Convert.ToDouble(t["Time Spent"]));
}

Original comment by fwd...@gmail.com on 2 Oct 2014 at 11:46

[GoogleCodeExporter]

int sum = 0;
var groupp=from k in excelFile.Worksheet(sheetName).ToList() select k;
var grouped = groupp.GroupBy(row => row["Issue Type"]);
foreach (var g in grouped)
{
sum = g.Sum(t => Convert.ToInt32(t["Time Spent"]));
MessageBox.Show("key=" + g.Key + "sum=" + sum);
}

and it is displaying same value as it is in Excel column. its not displaying 
Summed value and not grouping.. provide me ur mail id so that i can send you 
excel and my requirements

Original comment by sure05pa...@gmail.com on 3 Oct 2014 at 8:42

Attachments:

• groupandsum.PNG