如何在C# DataTable中将单列数据拆分至多列并对齐填充？

问题原因 #

你当前的代码逻辑是每读取一条原始数据就新建一行添加到myTableTagModify：

• 当ValueID=1时，创建新行并填充DateTime和kPa列
• 当ValueID=2时，又创建新行并填充Nm3/h列
  这就导致两类数据分别占用不同行，无法对齐到同一时间戳的行里。

解决方案 #

按**Timestamp（时间戳）**分组，把同一时间点的不同ValueID对应值填充到同一行的对应列中。具体修改如下：

修改后的核心代码 #

替换原TLG_Connect方法中的foreach循环部分：

// 按Timestamp分组，确保同一时间的数据被归为一组
var groupedRows = myTableTag.AsEnumerable()
    .GroupBy(row => row["Timestamp"].ToString());

foreach (var group in groupedRows)
{
    DataRow newRow = myTableTagModify.NewRow();
    // 获取当前组的时间戳并转换为本地时间
    var timestamp = System.DateTime.Parse(group.Key);
    timestamp = timestamp.ToLocalTime();
    newRow["DateTime"] = timestamp.ToString();

    // 遍历组内的每一行，根据ValueID填充对应列
    foreach (DataRow row in group)
    {
        int valueId = Convert.ToInt16(row["ValueID"]);
        if (valueId == 1)
        {
            newRow["kPa"] = String.Format("{0:F3}", row["RealValue"]).PadLeft(20);
        }
        else if (valueId == 2)
        {
            newRow["Nm3/h"] = String.Format("{0:F3}", row["RealValue"]).PadLeft(20);
        }
    }
    // 将填充好的行添加到目标表
    myTableTagModify.Rows.Add(newRow);
}

完整修改后的TLG_Connect方法 #

public int TLG_Connect(string mySeclectQuery)
{
    string myConnectionString = "Provider = WinCCOLEDBProvider.1; Data Source = ADMIN\\WINCC; Catalog = CC_92_CS11__24_05_16_16_24_12R; Jet OLEDB:Database Password= ";
    myGrid.Name = "TagLogging";
    myTableTag = new DataTable();
    myTableTagModify = new DataTable();
    System.DateTime localDateTime;

    DataColumn newColumn = new DataColumn("DateTime", System.Type.GetType("System.String"));
    newColumn.Caption = "Date Time";
    newColumn.DefaultValue = string.Empty;
    myTableTagModify.Columns.Add(newColumn);
   
    newColumn = new DataColumn("kPa", System.Type.GetType("System.String"));
    newColumn.Caption = "kPa";
    newColumn.DefaultValue = string.Empty;
    myTableTagModify.Columns.Add(newColumn);
   
    newColumn = new DataColumn("Nm3/h", System.Type.GetType("System.String"));
    newColumn.Caption = "Nm3/h";
    newColumn.DefaultValue = string.Empty;
    myTableTagModify.Columns.Add(newColumn);
    
    using (OleDbCommand myComand = new OleDbCommand(mySeclectQuery)) 
    {
        myConnection = new OleDbConnection(myConnectionString);
        myComand.Connection = myConnection;
        myDataAdapter = new OleDbDataAdapter(myComand);
        myTableTag.TableName = "myTableTag";
        myTableTagModify.TableName = "myTableTagModify";
        myDataAdapter.Fill(myTableTag);
        myTableTagModify.Clear();

        // 核心修改：按时间戳分组处理数据
        var groupedRows = myTableTag.AsEnumerable()
            .GroupBy(row => row["Timestamp"].ToString());

        foreach (var group in groupedRows)
        {
            DataRow newRow = myTableTagModify.NewRow();
            var timestamp = System.DateTime.Parse(group.Key);
            timestamp = timestamp.ToLocalTime();
            newRow["DateTime"] = timestamp.ToString();

            foreach (DataRow row in group)
            {
                int valueId = Convert.ToInt16(row["ValueID"]);
                if (valueId == 1)
                {
                    newRow["kPa"] = String.Format("{0:F3}", row["RealValue"]).PadLeft(20);
                }
                else if (valueId == 2)
                {
                    newRow["Nm3/h"] = String.Format("{0:F3}", row["RealValue"]).PadLeft(20);
                }
            }
            myTableTagModify.Rows.Add(newRow);
        }
    }
    myGrid.DataSource = myTableTagModify;
    
    myConnection.Close();

    return (myGrid.RowCount);
}

额外优化建议 #

• 建议将DateTime列的类型改为System.DateTime而非String，避免字符串解析带来的精度和匹配问题，分组时直接用DateTime类型更可靠：

  DataColumn newColumn = new DataColumn("DateTime", typeof(System.DateTime));
  // 分组时直接用DateTime类型
  var groupedRows = myTableTag.AsEnumerable()
      .GroupBy(row => System.DateTime.Parse(row["Timestamp"].ToString()).ToLocalTime());
  

• 添加空值判断，避免原始数据中Timestamp或RealValue为DBNull时引发异常。

内容的提问来源于stack exchange，提问作者Văn Trưởng Nguyễn