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.

Answer 1

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