Query Excel where Rows and Columns are reversed

Question

How can I query an Excel file where the rows and columns are reversed / rotated 90 degrees?

Can it be done with a SELECT query, or do I need to recurse the cells programmatically?

It's for a .NET app, so linq or other suggestions are welcome.

I think you can fill a `DataTable` with those Excel data and then do "reverse transpose" - you can perform such like this reference but in reverse: http://www.nullskull.com/a/1045/transpose-a-datatable-in-net.aspx. — Tetsuya Yamamoto, Nov 14 '17 at 07:55

score 1 · Accepted Answer · answered Nov 14 '17 at 09:09

Transpose a Datatable with a code like this:

private DataTable GenerateTransposedTable(DataTable inputTable)
    {
        DataTable outputTable = new DataTable(inputTable.TableName);
        outputTable.Columns.Add(inputTable.Columns[0].ColumnName);


        foreach (DataRow inRow in inputTable.Rows)
        {
            string newColName = inRow[0].ToString();
            outputTable.Columns.Add(newColName);
        }


        for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
        {
            DataRow newRow = outputTable.NewRow();


            newRow[0] = inputTable.Columns[rCount].ColumnName;
            for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
            {
                string colValue = inputTable.Rows[cCount][rCount].ToString();
                newRow[cCount + 1] = colValue;
            }
            outputTable.Rows.Add(newRow);
        }

        return outputTable;
    }

score 1 · Answer 2 · answered Nov 14 '17 at 09:18

.NET does not include a method to transpose data tables. You have to make your own. This website Link has a tutorial on an example transpose method. I will copy and paste the code snippet below:

private DataTable Transpose(DataTable dt)
{
DataTable dtNew = new DataTable();

//adding columns    
for(int i=0; i<=dt.Rows.Count; i++)
{
   dtNew.Columns.Add(i.ToString());
}



//Changing Column Captions: 
dtNew.Columns[0].ColumnName = " ";

 for(int i=0; i<dt.Rows.Count; i++) 
 {
  //For dateTime columns use like below
   dtNew.Columns[i+1].ColumnName =Convert.ToDateTime(dt.Rows[i].ItemArray[0].ToString()).ToString("MM/dd/yyyy");
  //Else just assign the ItermArry[0] to the columnName prooperty
 }

//Adding Row Data
for(int k=1; k<dt.Columns.Count; k++)
{
    DataRow r = dtNew.NewRow();
    r[0] = dt.Columns[k].ToString(); 
    for(int j=1; j<=dt.Rows.Count; j++)
    r[j] = dt.Rows[j-1][k];  
    dtNew.Rows.Add(r);
}

return dtNew;
}

Query Excel where Rows and Columns are reversed

2 Answers2