protected void SubtotalCalculate()
{
string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
MySqlConnection cn = new MySqlConnection(connectionString);
cn.Open();
string sqlstr = @"
select a.*,
stuff((select ',' + t.task from table2 as t where t.name = a.name for xml path('')),
1,1,'') as task
from table1 as a
";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = cn;
cmd.CommandText = sqlstr;
MySqlDataReader reader = cmd.ExecuteReader();
DataTable dt = ReaderToTable(reader);
dt = DealTable(dt);
string htmlStr = GetHtmlStrByDataTable(dt);
//lblTable.Text = htmlStr;
}
protected DataTable ReaderToTable(MySqlDataReader dr)
{
DataTable dt = new DataTable();
for (int i = 0; i < dr.FieldCount; i++)
{
dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
}
object[] objValues = new object[dr.FieldCount];
while (dr.Read())
{
dr.GetValues(objValues);
dt.LoadDataRow(objValues, true);
}
dr.Close();
return dt;
}
protected DataTable DealTable(DataTable dt)
{
//获取task列数据,存入list
List<string[]> list = new List<string[]>();
for (int i = 0; i < dt.Rows.Count; i++)
{
string[] strs = dt.Rows[i]["task"].ToString().Split(',');
list.Add(strs);
}
//取得strs中最长的长度
int maxlen = 0;
foreach (string[] strs in list)
{
if (strs.Length > maxlen)
{
maxlen = strs.Length;
}
}
//删除task列
dt.Columns.Remove("task");
//添加maxlen个列
for (int i = 0; i < maxlen; i++)
{
dt.Columns.Add("task" + (i + 1).ToString());
}
//给新添加的列赋值数据
int newColStart = dt.Columns.IndexOf(dt.Columns["name"]) + 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = newColStart; j < newColStart + maxlen; j++)
{
int newColNo = j - newColStart;
if (list[i].Length > newColNo)
{
dt.Rows[i][j] = list[i][newColNo];
}
}
}
return dt;
}
protected string GetHtmlStrByDataTable(DataTable dt)
{
StringBuilder sb = new StringBuilder();
//表头
sb.Append("<tr>");
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
}
sb.Append("</tr>");
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("<tr>");
for (int j = 0; j < dt.Columns.Count; j++)
{
sb.Append("<td>" + dt.Rows[i][j].ToString() + "</td>");
}
sb.Append("</tr>");
}
return sb.ToString();
}
.net行转列实现(动态列)
最新推荐文章于 2023-03-06 14:56:57 发布