得到cellset代码:
public static CellSet ExecuteCellSet(string connectionString, string queryString)
{
string connString = connectionString;
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = connString;
conn.Open();
AdomdCommand command = conn.CreateCommand();
command.CommandText = queryString;
CellSet cellSet = command.ExecuteCellSet();
conn.Close();
return cellSet;
}
//把cellset转换为datatable 数据列以Count_开头 行头以File_开头
public static DataTable ToDataTable(CellSet cs)
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn();
DataRow dr = null;
//总列数
int columnsCount = cs.Axes[0].Positions.Count;
//总行数
int rowsCount = cs.Axes[1].Positions.Count;
//处理行头列
if (rowsCount > 0)
{
Position py = cs.Axes[1].Positions[0];
for (int i = 0; i < py.Members.Count; i++)
{
dt.Columns.Add("File_" + i.ToString(), System.Type.GetType("System.String"));
}
}
//生成数据列对象
string name = "";
foreach (Position p in cs.Axes[0].Positions)
{
dc = new DataColumn();
name = "";
foreach (Member m in p.Members)
{
name = name + m.Caption + "-";
}
string[] c = name.TrimEnd('-').Split('-');
dc.ColumnName = "Count_";
for (int i = 0; i < c.Length; i++)
{
dc.ColumnName += c+"_";
}
dc.ColumnName = dc.ColumnName.TrimEnd('_');
//我不知道怎么获得数据的原始类型 我把它全部定为了字符串型 希望高手指点
dc.DataType = System.Type.GetType("System.String");
dt.Columns.Add(dc);
}
//添加行数据
int pos = 0;
foreach (Position py in cs.Axes[1].Positions)
{
dr = dt.NewRow();
//维度描述列数据(行头)
for (int i = 0; i < py.Members.Count; i++)
{
dr["File_" + i.ToString()] = py.Members.Caption;
}
//数据列
for (int x = 0; x < cs.Axes[0].Positions.Count; x++)
{
string a = cs[x, pos].FormattedValue.ToString();
int c_index = py.Members.Count + x;
if (string.IsNullOrEmpty(a))
{
dr[c_index] = "0";
}
else
{
//这里过滤了那些错误数据if (a.IndexOf("IN")>0)
{
dr[c_index] = "0";
}
else
{
dr[c_index] = a;
}
}
}
pos = pos + 1;
dt.Rows.Add(dr);
}
return dt;
}