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.

Possible duplicate of [I'm getting the "missing a using directive or assembly reference" and no clue what's going wrong](https://stackoverflow.com/questions/17344295/im-getting-the-missing-a-using-directive-or-assembly-reference-and-no-clue-wh) — SᴇM, Oct 13 '17 at 07:11
You need not write `Excel.Sheets`. You have already used the directive `Excel`, so just write `Sheet` which is an interface. Or otherwise you can use the concrete class `Worksheet`. — praty, Oct 13 '17 at 07:13
Happy to help :). I have also included some code which can achieve what you intend. Check that out as well. — praty, Oct 13 '17 at 09:59

score 1 · Accepted Answer · answered Oct 13 '17 at 07:43

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

That one is really nice. Short and matching solution :) Thx again for your help! — Niclas, Oct 13 '17 at 11:14
So I tried your code today. I am getting an Exception as the program goes into the last if(){} before the catch(){}. I am not getting the problem. I think it has something to do with the condition. — Niclas, Oct 16 '17 at 07:36
What is the exception message @Niclas? Could that be because `valueArray` is empty? What do you see in `valueArray` on debug? — praty, Oct 16 '17 at 09:53
So I figured it out. The array started at 1 due to excel starting its index at 1. :) — Niclas, Oct 16 '17 at 13:08

score 0 · Answer 2 · answered Nov 07 '19 at 15:41

Working Copy for retrieving 2 rows and open excel with Pwd Protected

class Program
{
    static void Main(string[] args)
    {
        Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("YourPath", ReadOnly: true, Password: "PWD");
        Excel._Worksheet xlWorksheet = xlWorkBook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;

        int rCnt;
        int rw = 0;
        int cl = 0;

        //get the total column count
        cl = xlRange.Columns.Count;

        List<MyRow> myRows = new List<MyRow>();
        for (rCnt = 1; rCnt <= 1; rCnt++)
        {
            if (rCnt % 6 == 1)
            {//get rows which ABC or XYZ is in

                for (int col = 2; col <= cl; col++)
                {//traverse columns (the first column is not included)

                    for (int rowABCD = rCnt; rowABCD <= rCnt + 5; rowABCD++)
                    {//traverse the following four rows after ABC row or XYZ row
                        MyRow myRow = new MyRow();
                        //get ABC or XYZ
                        myRow.Col1 = (string)(xlRange.Cells[rowABCD, 1] as Range).Value2.ToString();
                        // get the  value of current column  in ABC row or XYZ row
                        myRow.Col2 = (string)(xlRange.Cells[rowABCD, col] as Range).Value2.ToString();
                        // add the newly created myRow to the list
                        myRows.Add(myRow);
                    }
                }
            }
        }
        xlApp.Quit();
    }

    public class MyRow
    {
        public string Col1 { get; set; }
        public string Col2 { get; set; }
    }
}

Import Excel Rows, Columns into List

2 Answers2

Linked