Import Excel Rows, Columns into List

Question

I am looking for a way to import certain elements of an Excel sheet into a List. My goal is to be able to sort the attributes (first row) of the excel sheet (click on the attribute I want to see) and get the values of the rows below the first row.

Answer 1

I would implement what you want this way without using Sheet Interface but Worksheet class object.

One thing to note is I am closing the excel sheet after I get all the used range in 2-d array. This makes it faster otherwise the reading from range will be a lot slower. There could be a lot many ways to make it even faster.

Application xlApp = new Application();
Workbook xlWorkBook = null;
Worksheet dataSheet = null;
Range dataRange = null;
List<string> columnNames = new List<string>();
object[,] valueArray;

try
{
    // Open the excel file
    xlWorkBook = xlApp.Workbooks.Open(fileFullPath, 0, true);

    if (xlWorkBook.Worksheets != null
        && xlWorkBook.Worksheets.Count > 0)
    {
        // Get the first data sheet
        dataSheet = xlWorkBook.Worksheets[1];

        // Get range of data in the worksheet
        dataRange = dataSheet.UsedRange;

        // Read all data from data range in the worksheet
        valueArray = (object[,])dataRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

        if (xlWorkBook != null)
        {
            // Close the workbook after job is done
            xlWorkBook.Close();
            xlApp.Quit();
        }

        for (int colIndex = 0; colIndex < valueArray.GetLength(1); colIndex++)
        {
            if (valueArray[0, colIndex] != null
                && !string.IsNullOrEmpty(valueArray[0, colIndex].ToString()))
            {
                // Get name of all columns in the first sheet
                columnNames.Add(valueArray[0, colIndex].ToString());
            }
        }
    }

    // Now you have column names or to say first row values in this:
    // columnNames - list of strings
}
catch (System.Exception generalException)
{
    if (xlWorkBook != null)
    {
        // Close the workbook after job is done
        xlWorkBook.Close();
        xlApp.Quit();
    }
}