C# Read data using using Microsoft.Office.Interop.Excel

Question

I have a table of data which i need to read from excel based on user input and store it in VS as an array.

If the user enters C1, search and get the associated data:

array[0]: X1E1M101

array[1]: F2G1M202

If the user enters C2:

array[0]: X1E1M105

array[1]: F1G2M304

---

my data:

     A      B      C     D     E
1   C1
2
3   X1     E1     M1     01 
4   F2     G1     M2     02
5
6   C2
7
8   X1     E1     M1     05 
9   F1     G2     M3     04
10

---

my code:

//I declared the Interop
using Excel = Microsoft.Office.Interop.Excel;


        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(ManufacturingFile);
        //xlWorkSheet = ("Default Value");  // i get an error here when trying to define the worksheet name i want to select. "Cannot impicitly convert type string to ... excel.worksheet'

        xlWorkSheet = ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[1]).Select(); // This method also I get an error.
        xlWorkSheet.Activate(); 

I am stuck after this part as I am new to using Interop. Hope someone can help me, I am a beginner in C# and your help is much appreciated.

Answer 1

You will have to open your woorkbook and worksheet , there are a number of example explaining how to do that , here is a sample

      Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        string str;
        int rCnt = 0;
        int cCnt = 0;

        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open("testone.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


        range = xlWorkSheet.UsedRange;

        Microsoft.Office.Interop.Excel.Range xlFound =range.EntireRow.Find("C2",misValue, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,true, misValue, misValue);

        if (!(xlFound == null))
        {
            int ID_Number = xlFound.Column;
            int rownum = xlFound.Row;
            Console.WriteLine(ID_Number);
            Console.WriteLine(rownum);
            Console.Read();
        }

You could 1st get the range value of the search for example if 'C1' is at a1 the you will have to read the whole row from a(n+2) and stop when it finds an empty row.

Above code is not compiled its takenfrom here

Answer 2

If you're going to just read data from excel files I recommend you use the ExcelDataReader library instead, it offers better performance and doesn't leave ghost processes as opposed to the Interop one. Here's some sample code to set you up:

    IExcelDataReader reader = null;

    string FilePath = "PathToExcelFile";

    //Load file into a stream
    FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);

    //Must check file extension to adjust the reader to the excel file type
    if (System.IO.Path.GetExtension(FilePath).Equals(".xls"))
    {
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    }
    else if (System.IO.Path.GetExtension(FilePath).Equals(".xlsx"))
    {
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    }

    if (reader != null)
    {
        //Fill DataSet
        System.Data.DataSet result = reader.AsDataSet();
        try
        {
            //Loop through rows for the desired worksheet
            //In this case I use the table index "0" to pick the first worksheet in the workbook
            foreach (DataRow row in result.Tables[0].Rows)
            {
                string FirstColumn = row[0].ToString();
            }
        }
        catch
        {

        }
    }