Windows Forms: How To Open and Read an Excel file into a ListView in C#

Creating a new Windows Forms Application, then drag ListView, Button controls from your Visual Studio into your form designer. You can modify your layout as shown below.

How to read excel file in c# windows application

Right-clicking on your References, then select Add References. We will use COM to read the excel file, so you need to add a reference to the Microsoft.Office.Interop.Excel.dll to your project.

You can find the Microsoft.Office.Interop.Excel.dll in locate C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel

Clicking on your ListView control, then change View to Details type.

Next, Set the GridLines property of ListView control to True.

Clicking Edit Column, then add the columns you want to read from excel file.

Creating a ConvertToStringArrary method allows you to convert array to string array.

string[] ConvertToStringArray(Array values)
{
    //create a new string array  
    string[] arrays = new string[values.Length];
    for (int i = 1; i <= values.Length; i++)
    {
        if (values.GetValue(1, i) == null)
            arrays[i - 1] = "";
        else
            arrays[i - 1] = (string)values.GetValue(1, i).ToString();
    }
    return arrays;
}

Declaring an excel application variable.

Microsoft.Office.Interop.Excel.Application _excel = null;

How to read data from excel sheet using c#

Adding a click event handler to the Open button allows you to open an excel file, then start excel application to read data from excel sheet and add data to the ListView control.

//c# read excel file using interop
private void btnOpen_Click(object sender, EventArgs e)
{
    using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx" })
    {
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            _excel = new Microsoft.Office.Interop.Excel.Application();
            //Make the Application invisible  
            _excel.Visible = false;
            var workbook = _excel.Workbooks.Open(ofd.FileName);
            //get the collection of sheets in the workbook  
            var sheets = workbook.Worksheets;
            // get the first and only worksheet from the collection of worksheets  
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
            //loop through total rows of the spreadsheet and place each row in the list view
            //Interop Excel UsedRange Rows Count
            //how to get the count of rows in excel sheet using c#
            for (int i = 2; i <= worksheet.UsedRange.Rows.Count; i++)
            {
                Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "J" + i.ToString());
                System.Array values = (System.Array)range.Cells.Value;
                string[] strArray = ConvertToStringArray(values);
                listView.Items.Add(new ListViewItem(strArray));
            }
            workbook.Close(false);
            _excel.Quit();
            Marshal.ReleaseComObject(worksheet);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(_excel);
        }
    }
}

We need to count the number of existing lines in the excel sheet, to remove the title you should start the loop from line 2.

To get how many row in excel sheet you can use workshet.UsedRang.Rows.Count property.

And don't forget to close your excel application and release your memory.

Through this c# example, i showed you how to read excel files using Microsoft Office Interop assemblies in c# windows forms application.