Closed GoogleCodeExporter closed 8 years ago
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
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:
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
thank you very much
Original comment by deshmukh...@gmail.com
on 13 Jun 2011 at 5:31
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
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
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
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
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
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:
Original issue reported on code.google.com by
deshmukh...@gmail.com
on 6 Jun 2011 at 11:55