Requirement: the database data is vertical. When presenting, it is required to see the row header (row to column) at the same time (column to row).
Analysis: a lot of report rendering requires such display, which can be realized by class, but there is a lot of code, and a lot of dto needs to be built. Excel has the function of transpose, but you don't want to involve excel. Just use DataTable to implement, and there is not much code.
Let's first look at the sample data 3 columns and 10 rows:
We need to convert it into 3 rows and 10 columns, and then convert the columns into rows and columns at the same time
Baidu has a lot of conversion schemes, which are not satisfactory. It's basically a transformation of one direction. Either row to column or column to row. I just wrote one myself.
Because row to column conversion and column to row conversion should be carried out at the same time, we need to carefully observe the data and make a subscript to observe.
First, classify the data as a two-dimensional array and push it back from the converted table. It is found that:
The result table [1,1] is the original table [1,1]
[1,2] of the result table is [2,1] of the original table, and [1,3] of the result table is [3,1] of the original table
Rule: the conversion table can be obtained by benchmarking
Code implementation:
1. Convert the source data (including column headers) into a two-dimensional array, and then index the alignment. This method is the most convenient. To calculate the subscript position, you should add the column head
private DataTable PivotDatatable(DataTable dtSource, string columnFilter) { var columns = columnFilter.Split(','); DataTable dtFilter = dtSource.DefaultView.ToTable(false, columns); DataTable dtResult = new DataTable(); var rowCount = dtFilter.Rows.Count; var columnCount = columns.Length; // The number of rows in the source array is + 1, plus a row header object[,] arrSource = new object[rowCount + 1, columnCount]; // The number of rows in the target array is equal to the number of selected columns, the number of columns is equal to the number of rows in the source data + 1, plus a column property name object[,] arrResult = new object[columnCount, rowCount + 1]; // Write header in the first row of the original array for (int i = 0; i < columnCount; i++) { arrSource[0, i] = dtFilter.Columns[i].ColumnName; } // Write data for each line of source data for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { arrSource[i + 1, j] = dtFilter.Rows[i][j]; } } // Original number transposed to target array for (int i = 0; i < rowCount + 1; i++) { for (int j = 0; j < columnCount; j++) { arrResult[j, i] = arrSource[i, j]; } } // Create the structure of Datatable for (int i = 0; i < rowCount + 1; i++) { dtResult.Columns.Add(arrResult[0, i].ToString()); } List<string> valueList = new List<string>(); for (int i = 1; i < columnCount; i++) { for (int j = 0; j < rowCount + 1; j++) { valueList.Add(arrResult[i, j].ToString()); } dtResult.Rows.Add(valueList.ToArray()); valueList.Clear(); } return dtResult; }
2. The idea is to convert the data into two-dimensional code (excluding the column header). After the conversion, the column header will be added to the new table without too many subscripts
private DataTable SwapTable(DataTable tableData) { int intRows = tableData.Rows.Count; int intColumns = tableData.Columns.Count; //Convert to 2D array string[,] arrayData = new string[intRows, intColumns]; for (int i = 0; i < intRows; i++) { for (int j = 0; j < intColumns; j++) { arrayData[i, j] = tableData.Rows[i][j].ToString(); } } //Superscript commutation string[,] arrSwap = new string[intColumns, intRows]; for (int m = 0; m < intColumns; m++) { for (int n = 0; n < intRows; n++) { arrSwap[m, n] = arrayData[n, m]; } } DataTable dt = new DataTable(); //Add column for (int k = 0; k < intRows; k++) { dt.Columns.Add( new DataColumn(arrSwap[0, k]) ); } //add rows for (int r = 1; r < intColumns; r++) { DataRow dr = dt.NewRow(); for (int c = 0; c < intRows; c++) { dr[c] = arrSwap[r, c].ToString(); } dt.Rows.Add(dr); } //Add line header DataColumn ColRowHead = new DataColumn(tableData.Columns[0].ColumnName); dt.Columns.Add(ColRowHead); dt.Columns[ColRowHead.ColumnName].SetOrdinal(0); for (int i = 0; i < intColumns - 1; i++) { dt.Rows[i][ColRowHead.ColumnName] = tableData.Columns[i + 1].ColumnName; } return dt; }