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.

visit this post for detailed usage of Interop libraries for reading excel files — Sagar
– Sagar, Commented Sep 20, 2016 at 6:47
There are several posts already on stackoverflow - see: stackoverflow.com/questions/36844037 — rudolf_franek
– rudolf_franek, Commented Sep 20, 2016 at 6:48

akhila · Accepted Answer · 2016-09-20 12:01:34Z

2

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

edited Sep 20, 2016 at 12:01

answered Sep 20, 2016 at 7:14

akhila

7502 gold badges7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Manick9 Over a year ago

Hi @akhila How do u do a search to find the row and column of 'C1'? I managed to write up to selecting the specific sheet.

akhila Over a year ago

hey @Manick9 this should help you here . Also this link here shows how to pick the column Id you could pick the row and cloumn in your case. Hope it helps..

Manick9 Over a year ago

Thanks I was looking at it earlier. However, I got an error that currentFind is not found or not declared.

akhila Over a year ago

could you please share the code and error details.It will be much easier to understand the situation

akhila Over a year ago

@Manick9 please find the updated code above to get the row and column vaules

|

Innat3 · Accepted Answer · 2016-09-20 09:27:34Z

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
        {

        }
    }

edited Sep 20, 2016 at 9:27

answered Sep 20, 2016 at 7:44

Innat3

3,5762 gold badges15 silver badges30 bronze badges

1 Comment

Niyazi Babayev Over a year ago

It is better to use .CreateReader() since it automatically handles both .xls and .xlsx under the hood.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related