DataTable ReturnDt = new DataTable("Tab_Result");
ReturnDt.Columns.AddRange(new DataColumn[]
{
new DataColumn("序号", typeof(int)),
new DataColumn("分公司名称", typeof(string)),
new DataColumn("销售件数", typeof(int)),
new DataColumn("销售均价", typeof(decimal)),
new DataColumn("销售总额", typeof(decimal)),
new DataColumn("售前件数", typeof(int)),
new DataColumn("售后件数", typeof(int)),
new DataColumn("签约店数", typeof(int)),
new DataColumn("有效店数", typeof(int)),
new DataColumn("销售总件数", typeof(int)),
new DataColumn("总店数", typeof(int)),
});
int Count = 20;
for (int i = 1; i <= Count; i++)
{
System.Data.DataRow myRow = ReturnDt.NewRow();
myRow["序号"] = i;
myRow["分公司名称"] = "北京" + i;
myRow["销售件数"] = 1 + i;
myRow["销售均价"] = Convert.ToDecimal(i.ToString()).ToString("0.00");
myRow["销售总额"] = Convert.ToDecimal(i.ToString()).ToString("0.00");
myRow["售前件数"] = i + 2;
myRow["售后件数"] = i + 3;
myRow["签约店数"] = i + 4;
myRow["有效店数"] = i + 5;
myRow["销售总件数"] = int.Parse(myRow["售前件数"].ToString()) + int.Parse(myRow["售后件数"].ToString());
ReturnDt.Rows.Add(myRow);
//需放在后面，不然赋值错误,可做为汇总列，没初始值
myRow["总店数"] = ReturnDt.Compute("Sum(签约店数)+Sum(有效店数)", "序号=" + i.ToString());
if (i == Count)
{
//添加汇总行
System.Data.DataRow myRow1 = ReturnDt.NewRow();
myRow1["序号"] = 0;
myRow1["分公司名称"] = "汇总行";
myRow1["销售件数"] = ReturnDt.Compute("SUM(销售件数)", "");
myRow1["销售均价"] = ReturnDt.Compute("SUM(销售均价)", "");
myRow1["销售总额"] = ReturnDt.Compute("SUM(销售总额)", "");
myRow1["售前件数"] = ReturnDt.Compute("SUM(售前件数)", "");
myRow1["售后件数"] = ReturnDt.Compute("SUM(售后件数)", "");
myRow1["签约店数"] = ReturnDt.Compute("SUM(签约店数)", "");
myRow1["有效店数"] = ReturnDt.Compute("SUM(有效店数)", "");
myRow1["销售总件数"] = ReturnDt.Compute("SUM(销售总件数)", "");
myRow1["总店数"] = ReturnDt.Compute("SUM(总店数)", "");
//ReturnDt.Rows.Add(myRow1);//插入到最后一行
ReturnDt.Rows.InsertAt(myRow1, 0);//插入到第一行
}
}
//删除列
// ReturnDt.Columns.Remove("销售总件数");
//调整列顺序 ，列排序从0开始
ReturnDt.Columns["分公司名称"].SetOrdinal(0);
//修改列标题名称
ReturnDt.Columns["分公司名称"].ColumnName = "名称";
// ReturnDt.Rows.Remove(row);//根据row行信息删除
//ReturnDt.Rows.RemoveAt(1);//根据index索引删除
//查询序号大于等于5，并且按销售件数排序
EnumerableRowCollection<DataRow> query =
from order in ReturnDt.AsEnumerable()
where order.Field<int>("序号") <= 15
orderby order.Field<int>("销售总件数")
select order;
//复制表
DataTable boundTable = query.CopyToDataTable<DataRow>();
DataView view1 = query.AsDataView();
//view1.Sort = "序号 DESC";//降序
view1.Sort = "序号 ASC";//升序
view1.RowFilter = "序号 < 15";//筛选
//泛型转DataTable
List<People> list = new List<People>()
{
new People{ID=1,Name="A",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区1",Work="工程师",Income=8000.09,Birthday=System.DateTime.Now},
new People{ID=2,Name="B",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区2",Work="教师",Income=8010.09,Birthday=System.DateTime.Now},
new People{ID=3,Name="S",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区3",Work="博士",Income=8200.09,Birthday=System.DateTime.Now},
new People{ID=4,Name="AD",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区4",Work="工程师",Income=840.09,Birthday=System.DateTime.Now},
new People{ID=5,Name="AA",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区5",Work="工程师",Income=8500.09,Birthday=System.DateTime.Now},
new People{ID=6,Name="AR",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区6",Work="建筑师",Income=850.09,Birthday=System.DateTime.Now},
new People{ID=7,Name="AW",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区7",Work="工程师",Income=8000.09,Birthday=System.DateTime.Now},
new People{ID=8,Name="AR",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区8",Work="培训师",Income=860.069,Birthday=System.DateTime.Now},
new People{ID=9,Name="AQ",Sex="男",Age=18,Tel="0871-8989898",Addres="四川南通市区9",Work="工程师",Income=8900.09,Birthday=System.DateTime.Now},
};
System.Data.DataTable dt = ConvertToDataTable(list);
System.String str = ConvertToJson(dt);
System.Data.DataTable dt1 = JsonToDataTable(str);
}
public static DataTable JsonToDataTable(string strJson)
{
//取出表名
//var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
var rg = new Regex(@"([^:])+(?=:\{)", RegexOptions.IgnoreCase);
string strName = rg.Match(strJson).Value;
DataTable tb = null;
//去除表名
//strJson = strJson.Substring(strJson.IndexOf("{") + 1);
//strJson = strJson.Substring(0, strJson.IndexOf("}"));
//获取数据
//rg = new Regex(@"(?<={)[^}]+(?=})");
rg = new Regex(@"(?<={)[^}]+(?=})");
System.Text.RegularExpressions.MatchCollection mc = rg.Matches(strJson);
for (int i = 0; i < mc.Count; i++)
{
string strRow = mc[i].Value;
string[] strRows = strRow.Split(',');
//创建表
if (tb == null)
{
tb = new DataTable();
tb.TableName = strName;
foreach (string str in strRows)
{
var dc = new DataColumn();
string[] strCell = str.Split(':');
dc.ColumnName = strCell[0];
tb.Columns.Add(dc);
}
tb.AcceptChanges();
}
//增加内容
DataRow dr = tb.NewRow();
for (int r = 0; r < strRows.Length; r++)
{
//dr[r] = strRows[r].Split(':')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
dr[r] = strRows[r];
}
tb.Rows.Add(dr);
tb.AcceptChanges();
}
return tb;
}
//datatable转json
public static string ConvertToJson(DataTable dt)
{
StringBuilder jsonBuilder = new StringBuilder();
int count = dt.Rows.Count;
if (count != 0)
{
jsonBuilder.Append("[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append(":\'");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\',");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
return jsonBuilder.ToString();
}
else
{
return null;
}
}
//泛型转Datatble
public static DataTable ConvertToDataTable<T>(IList<T> list)
{
if (list == null || list.Count <= 0)
{
return null;
}
DataTable dataTable = new DataTable(typeof(T).Name);
DataColumn column;
DataRow row;
System.Reflection.PropertyInfo[] PropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (T t in list)
{
if (t == null)
{
continue;
}
row = dataTable.NewRow();
for (int i = 0, j = PropertyInfo.Length; i < j; i++)
{
System.Reflection.PropertyInfo pi = PropertyInfo[i];
string name = pi.Name;
if (dataTable.Columns[name] == null)
{
column = new DataColumn(name, pi.PropertyType);
dataTable.Columns.Add(column);
}
row[name] = pi.GetValue(t, null);
}
dataTable.Rows.Add(row);
}
return dataTable;
}
public class People
{
public string Name { get; set; }
public int Age { get; set; }
public string Tel { get; set; }
public string Addres { get; set; }
public int ID { get; set; }
public string Sex { get; set; }
public double Income { get; set; }
public string Work { get; set; }
public System.DateTime Birthday { get; set; }
}
