111,098
社区成员




key item 2017/2/1 2017/3/1 2017/4/1
123 1q 123 45 34
234 123e 45 55 66
333 eee 444 555 5656
以上数据想变成 以下数据
key item date qty
123 1q 2017/2/1 123
124 1q 2017/3/1 45
125 1q 2017/4/1 34
234 123e 2017/2/1 45
234 123e 2017/3/1 55
234 123e 2017/4/1 66
333 eee 2017/2/1 444
334 eee 2017/3/1 555
335 eee 2017/4/1 666
其中 2017/2/1 这样的日期列不是固定的 比如这次是 3列下次可能就是4列
并且下次可能变成 2020/1/1
求助
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace T392551790
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DataTable dt1 = new DataTable();
dt1.Columns.Add("key");
dt1.Columns.Add("item");
dt1.Columns.Add("2017/2/1");
dt1.Columns.Add("2017/3/1");
dt1.Columns.Add("2017/4/1");
string data = @"123 1q 123 45 34
234 123e 45 55 66
333 eee 444 555 5656";
foreach (var line in data.Split(new string[] { "\r\n" }, StringSplitOptions.None))
{
var row = dt1.NewRow();
var rowdt = line.Split(' ');
row[0] = rowdt[0];
row[1] = rowdt[1];
row[2] = rowdt[2];
row[3] = rowdt[3];
row[4] = rowdt[4];
dt1.Rows.Add(row);
}
dataGridView1.DataSource = dt1;
dataGridView2.DataSource = ConvTable(dt1, new string[] { "2017/2/1", "2017/3/1", "2017/4/1" }, "data", "qty");
}
private DataTable ConvTable(DataTable dt, string[] aggfrom, string aggto, string val)
{
DataTable dt1 = new DataTable();
foreach (DataColumn col in dt.Columns)
{
if (!aggfrom.Contains(col.ColumnName))
dt1.Columns.Add(col.ColumnName);
}
dt1.Columns.Add(aggto);
dt1.Columns.Add(val);
var query = dt.Rows.Cast<DataRow>()
.SelectMany(x => aggfrom.Select(y =>
{
var row = dt1.NewRow();
foreach (DataColumn col in dt1.Columns)
{
if (col.ColumnName == aggto)
{
row[col.ColumnName] = y;
}
else if (col.ColumnName == val)
{
row[col.ColumnName] = x[y];
}
else
{
row[col.ColumnName] = x[col.ColumnName];
}
}
return row;
}));
foreach (var r in query)
dt1.Rows.Add(r);
return dt1;
}
}
}
DataTable dtNew = new DataTable();
dtNew.Columns.Add("KEY");
dtNew.Columns.Add("ITEM");
dtNew.Columns.Add("DateWeek");
dtNew.Columns.Add("qty");
foreach (DataRow item in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName != "KEY" && dc.ColumnName != "ITEM")
{
DataRow dr = dtNew.NewRow();
dr["KEY"] = item["KEY"];
dr["ITEM"] = item["ITEM"];
dr["DateWeek"] = dc.ColumnName;
dr["qty"] = item[dc.ColumnName];
dtNew.Rows.Add(dr);
}
}
}
还有比这种方法更简单的吗