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

How to read excel file in c# windows application

Create a new Windows Forms application, then drag and drop the ListView, Button controls from your Visual Studio into your form designer.

Design your Windows Form with a ListView control to display the data from Excel. You can modify your layout as shown below.

You need to add a reference to the Microsoft Excel Interop library. Right-click on your project in Visual Studio, select "Add" -> "Reference", then browse for "Microsoft.Office.Interop.Excel" and add it.

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 c# file interop
            _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);
        }
    }
}

In this example:

btnOpen_Click is the event handler for the button click to load the Excel file. The OpenFileDialog is used to allow the user to select the Excel file.

We use the Excel Interop library to open the Excel file, read its data, and populate the ListView control.

We iterate through each row and column of the Excel sheet, adding its values to the ListView, then 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. Finally, 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.