展平数据透视表的数据透视表样式 [英] Flatten Table Pivot style for a Datagridview

问题描述

我想使用datagridview(vb.net)查看我的出勤表

I want to view my attendance table using a datagridview (vb.net)

这是我的样品表

Name   |    Date     |  Status  
-------+-------------+--------
John   | 08/08/2015  |   P
Mark   | 08/08/2015  |   A
James  | 08/08/2015  |   L
John   | 08/09/2015  |   A
Mark   | 08/09/2015  |   A
James  | 08/09/2015  |   A

这就是我希望我的datagridview看起来像的样子:

And this is what I want my datagridview to look like:

NAME  | 08/08/2015 | 08/09/2015
------+------------+------------
John  |     P      |     A
Mark  |     A      |     A
James |     L      |     A

推荐答案

根据您的工作，在某些情况下，您可以创建查询或准备好的语句来为您执行此操作.这些通常是总结一组固定的已知列.在这种情况下，我们不知道会有多少个日期列或它们是什么.因此，我们可以通过代码来实现.

Depending on what you are doing, in some cases you can create a query or prepared statement to do this for you. These are usually to summarize a fixed set of known columns. In this case we do not know how many date columns there will be or what they are. So, we can do it in code.

我使用Access代替mySQL，但是概念是相同的.通过每天不开会的班级上课记录，我也使事情变得更加复杂.起始数据:

I used Access instead of mySQL, but the concept is the same. I also made it more complex by logging attendance by class which do not meet everyday. The starting data:

我不会在结果中使用类名，这会使显示范围过大.

I wont be using the class name in the results, it makes the display too wide.

Dim sql = <sql>  
           ((Use your own SQL obviously))
           </sql>.Value

Dim dtTemp As New DataTable

' get the data
Using dbcon As OleDbConnection = GetACEConnection(),
    cmd As New OleDbCommand(sql, dbcon)

    dbcon.Open()
    Using da As New OleDbDataAdapter(cmd)
        da.Fill(dtTemp)
    End Using

End Using

' unique list of "date" columns in the result set
' ORDERBY Date is in the SQL
Dim colNames = dtTemp.AsEnumerable().
                Select(Function(s) DateTime.Parse(s.Item("Date").ToString).
                        ToString("MM/dd/yyyy")).
                Distinct.ToList()

' unique list of students
Dim Students = dtTemp.AsEnumerable().Select(Function(q) q.Item("Name")).
                Distinct.ToList()

' the final table to use with the DGV
Dim dt As New DataTable
Dim colName As String

' add the name and class code designation columns
dt.Columns.Add(New DataColumn(dtTemp.Columns(0).ColumnName, GetType(String)))
dt.Columns.Add(New DataColumn(dtTemp.Columns(1).ColumnName, GetType(String)))

' add a "MM/dd/yyyy" text column for each possible class day
For n As Int32 = 0 To colNames.ToArray.Count - 1
    colName = DateTime.Parse(colNames(n).ToString).ToString("MM/dd/yyyy")
    dt.Columns.Add(New DataColumn(colName, GetType(String)))
Next

Dim newRow As DataRow

' loop thru all students
For Each s In Students
    ' the student-class dataset
    Dim drs As DataRow() = dtTemp.Select(String.Format("Name = '{0}'", s.ToString)).
                            OrderBy(Function(o) o.Item("ClassCode")).ToArray

    ' create list of classes for this student
    Dim classes = drs.AsEnumerable.
            Select(Function(q) q.Item(1).ToString).Distinct.ToArray

    For Each classcode As String In classes
        ' filter the drs results to the current class
        Dim datestat As DataRow() = drs.AsEnumerable.
                Where(Function(q) q.Item(1).ToString = classcode).ToArray

        ' create new row, copy the data from drs.Rows to dt.columns
        newRow = dt.NewRow
        newRow.Item(0) = s
        newRow.Item(1) = classcode
        ' NOTE since not all students will have a class everyday, some
        ' "status" cells will be dbNull!
        For Each statRow In datestat
            Dim cname As String = DateTime.Parse(statRow.Item("Date").
                                                     ToString()).ToString("MM/dd/yyyy")
            newRow.Item(cname) = statRow.Item("Status")
        Next
        dt.Rows.Add(newRow)
    Next

Next

dgv.AutoGenerateColumns = True
dgv.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.ColumnHeader)
dgv.DataSource = dt

它看起来并不复杂.

1. 获取要使用的主数据集
2. 获取唯一的学生姓名列表
3. 要使用的列名称来自linq查询，以在数据表中提取唯一的类日期
4. 为结果创建一个新的DataTable.
   • 在StudentName和ClassCode之后，循环为 any 类遇到的每个日期添加一列.列名称/标题文本来自刚刚创建的ColNames列表/数组.

1. Get the master dataset to work on
2. Get a list of unique student names
3. The column names to use comes from a linq query to extract the unique class dates in the data table
4. Create a new DataTable for the results.
   • After the StudentName and ClassCode a loop adds one column for each date that any class meets. The column names/headert text comes from the ColNames list/array just created.

创建目标数据表后，就可以开始向其复制数据了.同样，您可以使用MySQL...对象代替OleDB...对象，但是它们的工作原理相同.

With the destination DataTable created, you can start copying data to it. Again, instead of OleDB... objects you would use MySQL... object, but they work the same.

1. 在学生列表中遍及所有学生
2. 对于每个人，从主数据集中提取他们参加的所有课程的列表
3. 遍历这些课程
4. 从学生班级数据集中提取当前班级的行
5. 使用前两列的Student和Class迭代变量创建新的DataRow.
6. 将当前学生"类数据集中的每个DateTime值转换为用于创建结果列的相同格式(cname).
   • 使用它来复制其状态:newRow.Item(cname) = statRow.Item("Status")到新行
   • 由于上课时间不是每天都开会，因此某些单元格将为空白(DbNull)

1. Loop thru all students in the student list
2. For each, extract a list of all classes they attended from the master data set
3. Loop thru those classes
4. Extract the rows for the current class from the Student-Class dataset
5. Create a new DataRow using the Student and Class iteration vars for the first 2 columns.
6. Convert each DateTime value in the current Student-Class dataset to the same format used to create the result columns (cname).
   • use it to copy their status: newRow.Item(cname) = statRow.Item("Status") to the new row
   • Since the classes dont meet everyday, some cells will be blank (DbNull)

如果没有按类别报告，而只是报告一整天的状态，那会更简单.结果:

It would be simpler without the By Class reporting, and just report the status for the entire day. The Result:

最令人困惑的部分是将一个数据表中的日期数据用作另一数据表中的列 name ，并去除时间部分.

The most confusing part is using the Date data in one datatable as a column name in another and stripping out the time portion.

那只是第一步，因此可以对其进行完善.某些处理可能可以用SQL完成.使用DateTime.Parse方法将DateTime数据转换为相同格式的字符串(删除时间等)可以是它自己的过程.我还将使用2个字符的年份格式来缩小标题的范围.

That is just a first pass, so it can likely be refined. Some of the processing might be able to be done in SQL; the DateTime.Parse method to convert the DateTime data to a string in the same format (remove the Time etc) could be its own procedure. I would also use a 2 character year format to make the headers a little narrower.

这篇关于展平数据透视表的数据透视表样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！