[C#/COMMON] List 클래스 : 피벗 데이터 동적 생성하기

■ List<T> 클래스를 사용해 피벗 데이터를 동적으로 생성하는 방법을 보여준다.

▶ MainForm.cs


using System.ComponentModel;
using System.Collections.Generic;
using System.Linq;

using DevExpress.Utils;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid;

namespace TestProject
{
    /// <summary>
    /// 메인 폼
    /// </summary>
    public partial class MainForm : XtraForm
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - MainForm()

        /// <summary>
        /// 생성자
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            #region 그리드 컨트롤을 설정한다.

            this.gridView.OptionsDetail.EnableMasterViewMode = false;
            this.gridView.OptionsSelection.MultiSelect       = true;
            this.gridView.OptionsSelection.MultiSelectMode   = GridMultiSelectMode.CellSelect;
            this.gridView.FocusRectStyle                     = DrawFocusRectStyle.CellFocus;

            this.gridView.OptionsView.ShowGroupPanel = false;

            this.gridView.IndicatorWidth                         = 30;
            this.gridView.OptionsView.ShowIndicator              = true;
            this.gridView.OptionsView.EnableAppearanceEvenRow    = false;
            this.gridView.OptionsView.EnableAppearanceOddRow     = false;
            this.gridView.OptionsBehavior.KeepFocusedRowOnUpdate = false;
            this.gridView.OptionsNavigation.AutoFocusNewRow      = true;

            this.gridView.OptionsBehavior.AutoPopulateColumns      = true;
            this.gridView.OptionsView.ShowColumnHeaders            = true;
            this.gridView.OptionsView.ColumnAutoWidth              = false;
            this.gridView.OptionsMenu.EnableColumnMenu             = false;
            this.gridView.OptionsNavigation.EnterMoveNextColumn    = true;
            this.gridView.OptionsCustomization.AllowColumnMoving   = false;
            this.gridView.OptionsCustomization.AllowColumnResizing = true;
            this.gridView.OptionsCustomization.AllowFilter         = false;
            this.gridView.OptionsCustomization.AllowSort           = false;

            this.gridView.OptionsView.ShowButtonMode                   = ShowButtonModeEnum.ShowForFocusedCell;
            this.gridView.OptionsView.AllowCellMerge                   = false;
            this.gridView.OptionsBehavior.EditorShowMode               = EditorShowMode.Default;
            this.gridView.OptionsBehavior.Editable                     = false;
            this.gridView.OptionsSelection.EnableAppearanceFocusedCell = false;
            this.gridView.OptionsSelection.InvertSelection             = false;
            this.gridView.OptionsHint.ShowCellHints                    = false;
            this.gridView.OptionsClipboard.CopyColumnHeaders           = DefaultBoolean.False;

            #endregion
            #region 피벗 데이터를 생성한다.

            List<Cost> sourceList = Cost.GetList();

            List<dynamic> pivotList = GetPivotList<Cost, string, string>(sourceList, "Weekday", "Category1", "Category2", "Amount");

            #endregion

            this.gridControl.DataSource = pivotList;

            this.gridView.BestFitColumns();
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region 피벗 리스트 구하기 - GetPivotList<TItem>(sourceList, xColumnName, y1ColumnName, y2ColumnName, valueColumnName)

        /// <summary>
        /// 피벗 리스트 구하기
        /// </summary>
        /// <typeparam name="TItem">항목 타입</typeparam>
        /// <param name="sourceList">소스 리스트</param>
        /// <param name="xColumnName">X축 컬럼명</param>
        /// <param name="y1ColumnName">Y1축 컬럼명</param>
        /// <param name="y2ColumnName">Y2축 컬럼명</param>
        /// <param name="valueColumnName">값 컬럼명</param>
        /// <returns>피벗 리스트</returns>
        /// <remarks>
        /// X축 컬럼 1개와 Y축 컬럼 2개, 값 컬럼 1개를 기준으로 피벗 데이터를 생성한다.
        /// X축 컬럼 타입은 string이다.
        /// 값 컬럼 타입은 decimal이다.
        /// 값 컬럼에 대해 누적 연산만 한다.
        /// </remarks>
        private List<dynamic> GetPivotList<TItem, TY1ColumnType, TY2ColumnType>
        (
            List<TItem> sourceList,
            string      xColumnName,
            string      y1ColumnName,
            string      y2ColumnName,
            string      valueColumnName
        )
        {
            if(sourceList == null && sourceList.Count == 0)
            {
                return null;
            }

            TItem firstCost = sourceList.First();

            PropertyDescriptorCollection pdc = TypeDescriptor.GetProperties(firstCost);

            PropertyDescriptor xDescriptor  = pdc[xColumnName    ];
            PropertyDescriptor yDescriptor1 = pdc[y1ColumnName   ];
            PropertyDescriptor yDescriptor2 = pdc[y2ColumnName   ];
            PropertyDescriptor vDescriptor  = pdc[valueColumnName];

            List<string> xValueList = sourceList.Select(s => xDescriptor.GetValue(s).ToString()).Distinct().OrderBy(s => s).ToList();

            DynamicClass dynamicClass = new DynamicClass();

            dynamicClass.AddProperty(y1ColumnName, typeof(TY1ColumnType));
            dynamicClass.AddProperty(y2ColumnName, typeof(TY2ColumnType));

            Dictionary<string, int> xColumnIndexDictionary = new Dictionary<string, int>();

            int xColumnIndex = 2;

            foreach(string xValue in xValueList)
            {
                dynamicClass.AddProperty(xValue, typeof(decimal));

                xColumnIndexDictionary.Add(xValue, xColumnIndex++);
            }

            dynamicClass.CreateType();

            sourceList = sourceList.OrderBy(s => GetPivotKey(yDescriptor1, yDescriptor2, s)).ToList();

            string currentKey = null;

            List<dynamic> resultList = new List<dynamic>();

            foreach(TItem source in sourceList)
            {
                string key = GetPivotKey(yDescriptor1, yDescriptor2, source);

                if(currentKey == null)
                {
                    dynamicClass.CreateInstance();

                    dynamicClass.SetValue(0, yDescriptor1.GetValue(source));
                    dynamicClass.SetValue(1, yDescriptor2.GetValue(source));

                    string xValue = xDescriptor.GetValue(source).ToString();

                    dynamicClass.SetValue(xColumnIndexDictionary[xValue], vDescriptor.GetValue(source));

                    currentKey = key;
                }
                else
                {
                    if(key == currentKey)
                    {
                        string x = xDescriptor.GetValue(source).ToString();
                        int    i = xColumnIndexDictionary[x];

                        decimal currentValue = (decimal)dynamicClass.GetValue(i);
                        decimal value        = (decimal)vDescriptor.GetValue(source);

                        dynamicClass.SetValue(i, currentValue + value);
                    }
                    else
                    {
                        resultList.Add(dynamicClass.Instance);

                        dynamicClass.CreateInstance();

                        dynamicClass.SetValue(0, yDescriptor1.GetValue(source));
                        dynamicClass.SetValue(1, yDescriptor2.GetValue(source));

                        string x = xDescriptor.GetValue(source).ToString();

                        dynamicClass.SetValue(xColumnIndexDictionary[x], vDescriptor.GetValue(source));

                        currentKey = key;
                    }
                }
            }

            resultList.Add(dynamicClass.Instance);

            return resultList;
        }

        #endregion
        #region 피벗 키 구하기 - GetPivotKey(y1Descriptor, y2Descriptor, source)

        /// <summary>
        /// 피벗 키 구하기
        /// </summary>
        /// <param name="y1Descriptor">Y1 컬럼 설명자</param>
        /// <param name="y2Descriptor">Y2 컬럼 설명자</param>
        /// <param name="source">소스 객체</param>
        /// <returns>피벗 키</returns>
        private string GetPivotKey(PropertyDescriptor y1Descriptor, PropertyDescriptor y2Descriptor, object source)
        {
            string key = $"{y1Descriptor.GetValue(source)}_{y2Descriptor.GetValue(source)}";

            return key;
        }

        #endregion
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

using System.ComponentModel;

using System.Collections.Generic;

using System.Linq;

using DevExpress.Utils;

using DevExpress.XtraEditors;

using DevExpress.XtraGrid.Views.Base;

using DevExpress.XtraGrid.Views.Grid;

namespace TestProject

{

/// <summary>

/// 메인 폼

/// </summary>

public partial class MainForm : XtraForm

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 생성자 - MainForm()

/// <summary>

/// 생성자

/// </summary>

public MainForm()

{

InitializeComponent();

#region 그리드 컨트롤을 설정한다.

this.gridView.OptionsDetail.EnableMasterViewMode = false;

this.gridView.OptionsSelection.MultiSelect = true;

this.gridView.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CellSelect;

this.gridView.FocusRectStyle = DrawFocusRectStyle.CellFocus;

this.gridView.OptionsView.ShowGroupPanel = false;

this.gridView.IndicatorWidth = 30;

this.gridView.OptionsView.ShowIndicator = true;

this.gridView.OptionsView.EnableAppearanceEvenRow = false;

this.gridView.OptionsView.EnableAppearanceOddRow = false;

this.gridView.OptionsBehavior.KeepFocusedRowOnUpdate = false;

this.gridView.OptionsNavigation.AutoFocusNewRow = true;

this.gridView.OptionsBehavior.AutoPopulateColumns = true;

this.gridView.OptionsView.ShowColumnHeaders = true;

this.gridView.OptionsView.ColumnAutoWidth = false;

this.gridView.OptionsMenu.EnableColumnMenu = false;

this.gridView.OptionsNavigation.EnterMoveNextColumn = true;

this.gridView.OptionsCustomization.AllowColumnMoving = false;

this.gridView.OptionsCustomization.AllowColumnResizing = true;

this.gridView.OptionsCustomization.AllowFilter = false;

this.gridView.OptionsCustomization.AllowSort = false;

this.gridView.OptionsView.ShowButtonMode = ShowButtonModeEnum.ShowForFocusedCell;

this.gridView.OptionsView.AllowCellMerge = false;

this.gridView.OptionsBehavior.EditorShowMode = EditorShowMode.Default;

this.gridView.OptionsBehavior.Editable = false;

this.gridView.OptionsSelection.EnableAppearanceFocusedCell = false;

this.gridView.OptionsSelection.InvertSelection = false;

this.gridView.OptionsHint.ShowCellHints = false;

this.gridView.OptionsClipboard.CopyColumnHeaders = DefaultBoolean.False;

#endregion

#region 피벗 데이터를 생성한다.

List<Cost> sourceList = Cost.GetList();

List<dynamic> pivotList = GetPivotList<Cost, string, string>(sourceList, "Weekday", "Category1", "Category2", "Amount");

#endregion

this.gridControl.DataSource = pivotList;

this.gridView.BestFitColumns();

}

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Private

#region 피벗 리스트 구하기 - GetPivotList<TItem>(sourceList, xColumnName, y1ColumnName, y2ColumnName, valueColumnName)

/// <summary>

/// 피벗 리스트 구하기

/// </summary>

/// <typeparam name="TItem">항목 타입</typeparam>

/// <param name="sourceList">소스 리스트</param>

/// <param name="xColumnName">X축 컬럼명</param>

/// <param name="y1ColumnName">Y1축 컬럼명</param>

/// <param name="y2ColumnName">Y2축 컬럼명</param>

/// <param name="valueColumnName">값 컬럼명</param>

/// <returns>피벗 리스트</returns>

/// <remarks>

/// X축 컬럼 1개와 Y축 컬럼 2개, 값 컬럼 1개를 기준으로 피벗 데이터를 생성한다.

/// X축 컬럼 타입은 string이다.

/// 값 컬럼 타입은 decimal이다.

/// 값 컬럼에 대해 누적 연산만 한다.

/// </remarks>

private List<dynamic> GetPivotList<TItem, TY1ColumnType, TY2ColumnType>

(

List<TItem> sourceList,

string xColumnName,

string y1ColumnName,

string y2ColumnName,

string valueColumnName

)

{

if(sourceList == null && sourceList.Count == 0)

{

return null;

}

TItem firstCost = sourceList.First();

PropertyDescriptorCollection pdc = TypeDescriptor.GetProperties(firstCost);

PropertyDescriptor xDescriptor = pdc[xColumnName ];

PropertyDescriptor yDescriptor1 = pdc[y1ColumnName ];

PropertyDescriptor yDescriptor2 = pdc[y2ColumnName ];

PropertyDescriptor vDescriptor = pdc[valueColumnName];

List<string> xValueList = sourceList.Select(s => xDescriptor.GetValue(s).ToString()).Distinct().OrderBy(s => s).ToList();

DynamicClass dynamicClass = new DynamicClass();

dynamicClass.AddProperty(y1ColumnName, typeof(TY1ColumnType));

dynamicClass.AddProperty(y2ColumnName, typeof(TY2ColumnType));

Dictionary<string, int> xColumnIndexDictionary = new Dictionary<string, int>();

int xColumnIndex = 2;

foreach(string xValue in xValueList)

{

dynamicClass.AddProperty(xValue, typeof(decimal));

xColumnIndexDictionary.Add(xValue, xColumnIndex++);

}

dynamicClass.CreateType();

sourceList = sourceList.OrderBy(s => GetPivotKey(yDescriptor1, yDescriptor2, s)).ToList();

string currentKey = null;

List<dynamic> resultList = new List<dynamic>();

foreach(TItem source in sourceList)

{

string key = GetPivotKey(yDescriptor1, yDescriptor2, source);

if(currentKey == null)

{

dynamicClass.CreateInstance();

dynamicClass.SetValue(0, yDescriptor1.GetValue(source));

dynamicClass.SetValue(1, yDescriptor2.GetValue(source));

string xValue = xDescriptor.GetValue(source).ToString();

dynamicClass.SetValue(xColumnIndexDictionary[xValue], vDescriptor.GetValue(source));

currentKey = key;

}

else

{

if(key == currentKey)

{

string x = xDescriptor.GetValue(source).ToString();

int i = xColumnIndexDictionary[x];

decimal currentValue = (decimal)dynamicClass.GetValue(i);

decimal value = (decimal)vDescriptor.GetValue(source);

dynamicClass.SetValue(i, currentValue + value);

}

else

{

resultList.Add(dynamicClass.Instance);

dynamicClass.CreateInstance();

dynamicClass.SetValue(0, yDescriptor1.GetValue(source));

dynamicClass.SetValue(1, yDescriptor2.GetValue(source));

string x = xDescriptor.GetValue(source).ToString();

dynamicClass.SetValue(xColumnIndexDictionary[x], vDescriptor.GetValue(source));

currentKey = key;

}

resultList.Add(dynamicClass.Instance);

return resultList;

}

#endregion

#region 피벗 키 구하기 - GetPivotKey(y1Descriptor, y2Descriptor, source)

/// <summary>

/// 피벗 키 구하기

/// </summary>

/// <param name="y1Descriptor">Y1 컬럼 설명자</param>

/// <param name="y2Descriptor">Y2 컬럼 설명자</param>

/// <param name="source">소스 객체</param>

/// <returns>피벗 키</returns>

private string GetPivotKey(PropertyDescriptor y1Descriptor, PropertyDescriptor y2Descriptor, object source)

{

string key = $"{y1Descriptor.GetValue(source)}_{y2Descriptor.GetValue(source)}";

return key;

}

#endregion

}

TestProject.zip