C × datatable row to column and column to row conversion at the same time

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;
        }

Tags: C# Excel Database

Posted on Fri, 22 May 2020 06:37:07 -0400 by czs