以前一直觉得泛型比较好用,一直没使用DataTable,最近发现泛型也不是万能的,比如将行转成列。
虽然这种在前台JS转比较方便,但可能也需要这样的接口,提供别人调用,还是记录一下。
/// <summary> /// 将行转成列 /// DataTable dtResult = Utility.ConvertDataTableToVertical(dt, "ID", new string[]{"Code","StationName"}, new string[] { "Value", "Name", "IsExcessive" }); /// </summary> /// <param name="dt">数据源</param> /// <param name="rowKeyArray">唯一标示列</param> /// <param name="columnKeyArray">要转成表头的列</param> /// <param name="valueKeyArray">数据行</param> /// <param name="preserveColumnArray">剩下的要显示的行</param> /// <returns></returns> public static DataTable ConvertDataTableToVertical(DataTable dt, string[] rowKeyArray, string[] columnKeyArray, string[] valueKeyArray, List<string> preserveColumnArray) { DataTable dtResult = new DataTable(); if (preserveColumnArray == null) { preserveColumnArray = new List<string>(); foreach (var column in dt.Columns) { if (columnKeyArray.Contains(column.ToString()) || valueKeyArray.Contains(column.ToString())) { continue; } preserveColumnArray.Add(column.ToString()); } } var columns = (from r in dt.AsEnumerable() select GetStringFromDataRow(r, columnKeyArray)).Where(r => !string.IsNullOrEmpty(r)).Distinct().OrderBy(r => r).ToArray(); var columnCount = columns.Length * valueKeyArray.Length + preserveColumnArray.Count; Dictionary<object, object[]> rows = new Dictionary<object, object[]>(); foreach (DataRow dr in dt.Rows) { var rowKey = GetStringFromDataRow(dr, rowKeyArray); if (rows.Keys.Contains(rowKey) == false) { rows.Add(rowKey, new object[columnCount]); for (var j = 0; j < preserveColumnArray.Count; j++) { rows[rowKey][j] = dr[preserveColumnArray[j]]; } } for (var i = 0; i < columns.Length; i++) { if (columns[i] == GetStringFromDataRow(dr, columnKeyArray)) { var j = i * valueKeyArray.Length + preserveColumnArray.Count; foreach (var valueKey in valueKeyArray) { rows[rowKey][j] = dr[valueKey]; j++; } break; } } } foreach (var column in preserveColumnArray) { dtResult.Columns.Add(column, dt.Columns[column].DataType); } foreach (var column in columns) { foreach (var valueKey in valueKeyArray) { if (column.IndexOf('_') == -1) { dtResult.Columns.Add(column + '_' + valueKey, dt.Columns[valueKey].DataType); } else { dtResult.Columns.Add(column + '_' + valueKey, dt.Columns[valueKey].DataType); //dtResult.Columns.Add(column.Substring(0, column.IndexOf('_')) + '_' + valueKey + column.Substring(column.IndexOf('_')), dt.Columns[valueKey].DataType); } } } foreach (var keyValuePair in rows) { dtResult.Rows.Add(keyValuePair.Value); } return dtResult; }
public static string GetStringFromDataRow(DataRow dr, string[] keys) { string strResult = string.Empty; foreach (string key in keys) { Type ObjectValueType = dr[key].GetType(); switch (ObjectValueType.FullName) { case "System.DateTime": strResult += "_" + DateTimeHelper.DateTimeToUTCTime((DateTime)dr[key]); break; default: strResult += "_" + dr[key]; break; } } if (strResult.Length > 0) { strResult = strResult.Remove(0, 1); } return strResult; }
经常得到的是List,发现不会转成行显示(后台),只能先转成DataTable了。。。
/// <summary> /// 将泛类型集合List类转换成DataTable /// </summary> /// <param name="list">泛类型集合</param> /// <returns></returns> public static DataTable ListToDataTable<T>(List<T> entitys) { //检查实体集合不能为空 if (entitys == null || entitys.Count < 1) { throw new Exception("需转换的集合为空"); } //取出第一个实体的所有Propertie Type entityType = entitys[0].GetType(); PropertyInfo[] entityProperties = entityType.GetProperties(); //生成DataTable的structure //生产代码中,应将生成的DataTable结构Cache起来,此处略 DataTable dt = new DataTable(); for (int i = 0; i < entityProperties.Length; i++) { //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType); dt.Columns.Add(entityProperties[i].Name); } //将所有entity添加到DataTable中 foreach (object entity in entitys) { //检查所有的的实体都为同一类型 if (entity.GetType() != entityType) { throw new Exception("要转换的集合元素类型不一致"); } object[] entityValues = new object[entityProperties.Length]; for (int i = 0; i < entityProperties.Length; i++) { entityValues[i] = entityProperties[i].GetValue(entity, null); } dt.Rows.Add(entityValues); } return dt; }