Get count of records in excel by groupings with count?

Question

I have a excel with the following data:

Week---Parts

32--------part1

32--------part1

33--------part2

33--------part2

33--------part3

34--------part1

34--------part4

34--------part5

From this, I would like to get somethin glike this in datagridview

Week---Part1---PArt2---Part3----Part4---Part5

32-----2------0--------0-------0--------0

33-----0------2--------1-------0--------0

34-----1------0--------0-------1--------1

This program is importing excel files with this data and it is also not connected to a database.

Using VB.NET code, how can I make the desired results?

Tried this (from Hari Prasad) but no output. NEW in VB.NET

Dim filePath As String = OpenFileDialog1.FileName
    Dim extension As String = Path.GetExtension(filePath)
    conStr = String.Empty
    Select Case extension

        Case ".xls"
            'Excel 97-03
            conStr = String.Format(Excel03ConString, filePath, "YES")
            Exit Select

        Case ".xlsx"
            'Excel 07
            conStr = String.Format(Excel07ConString, filePath, "YES")
            Exit Select
    End Select

    'Get the name of the First Sheet.

    Using con As New OleDbConnection(conStr)
        Using cmd As New OleDbCommand()
            cmd.Connection = con
            con.Open()
            Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
            con.Close()
        End Using
    End Using

    'Read Data from the First Sheet.
    Using con As New OleDbConnection(conStr)
        Dim cmd As OleDbCommand = New OleDbCommand((Convert.ToString("SELECT parts, week From [") & sheetName) + "] ", con)
        Dim adapter As New OleDbDataAdapter(cmd)
        Dim ds As New DataSet()
        adapter.Fill(ds)



        Dim contents = ds.Tables(0).AsEnumerable().Select(Function(x) New With { _
        Key .week = x.Field(Of Double)("week"), _
        Key .parts = x.Field(Of String)("parts") _
    })

        Dim result = contents.GroupBy(Function(g) g.week).[Select](Function(g) New With { _
    Key .week = g.Key, _
    Key .groups = g.GroupBy(Function(s) s.parts) _
}).[Select](Function(n) New With { _
    n.week, _
    Key .PartAndCount = n.groups.[Select](Function(s) New With { _
        Key .part = s.Key, _
        Key .count = s.Count() _
    }) _
})



        Dim gview = New DataGridView()
        gview.Columns.Add("week", "Week")

        For Each c As String In contents.[Select](Function(c) c.parts).Distinct()
            gview.Columns.Add(c, c)
        Next

        For Each r As String In result 'Error: cannot converted to string
            Dim rowIndex As Integer = gview.Rows.Add()

            Dim row As DataGridViewRow = gview.Rows(rowIndex)
            For Each s As String In r.PartAndCount 'Error: is not a member of string
                row.Cells(s.part).Value = s.Count
            Next
        Next

    End Using

Answer 1

The DataGridView will just need to have a data source (e.g. DataTable) with rows and columns as following; 1. each row will have unique week column order by week ascending 2. each row will also have columns that each of which contains number of the part appearing in the data (as per the data in the question above)

What Data Source would be used depends on the preference and requirements. For example, if the source data is coming from the database, grid view will generally be using a query to populate the grid, or is either hard coded or cannot be converted to Collection then you could just use DataTable and populate rows/columns

Answer 2

For example you have this class for storage your excel data (you didn't specified this in your question, so I used my imagination :)

 struct ExcelResults
 {
     public string Week;
     public string Part;
 }

To solve your task, you need dictionary with two keys. Here is samle code, that you can use. It's not a better variant, you can find here more elegant solutions for multi-dimensional key, but this variant is usable too.

 var excelResult = new List<ExcelResults>();   // your code for getting results from excel;
 var dict = new Dictionary<string, Dictionary<string, int>>();
 foreach (var line in excelResult)
 {
    if (!dict.ContainsKey(line.Week))
       dict.Add(line.Week, new Dictionary<string, int>());

    if (!dict[line.Week].ContainsKey(line.Part))
        dict[line.Week].Add(line.Part, 0);

     ++dict[line.Week][line.Part];
  }

Answer 3

This code will help reading contents from excel and grouping to meet your need.

string filename = @"c:\myexcel.xlsx"; // your input file.
string connectionstring = @Provider = Microsoft.ACE.OLEDB.12.0; 
    Data Source = " + filename + @"; Extended properties = 'Excel 12.0; HDR= Yes; ';";

using(var conn = new OleDbConnection(connectionstring)) 
{
    conn.Open();
    var comm = new OleDbCommand("SELECT week, parts FROM [Sheet1$]", conn);
    var adapter = new OleDbDataAdapter(comm);
    var ds = new DataSet();
    adapter.Fill(ds);

    var contents = ds.Tables[0].AsEnumerable().Select(x = > new {
        week = x.Field < double > ("week"),
        parts = x.Field < string > ("parts")
    });

    var result = contents.GroupBy(g = > g.week)
        .Select(g = > new {week = g.Key, groups = g.GroupBy(s = > s.parts)
    }).Select(n = > new {
        n.week, PartAndCount = n.groups.Select(s = > 
        new 
        {
            part = s.Key, count = s.Count()
        })
    });
}

Converting this results to DataGridView is fairly simple and can be achieved through below lines.

 var gview = new DataGridView();
 gview.Columns.Add("week", "Week");

 foreach(string c in contents.Select(c = > c.parts).Distinct()) 
 {
    gview.Columns.Add(c, c);
 }

 foreach(var r in result) 
 {
    int rowIndex = gview.Rows.Add();

    DataGridViewRow row = gview.Rows[rowIndex];
    foreach(var s in r.PartAndCount) 
    {
        row.Cells[s.part].Value = s.count;
    }
 }

Hope this helps.