How To Open and Read an Excel Spreadsheet into a ListView in .NET

Introduction 

 

The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel.  You can also refer to my previous article on the topic: Real-time Stock Quotes in Excel using .NET for more information on accessing Excel via .NET.  This article was written in response to a question asking How do I open an excel file and read it using .NET?

 

 

 

Figure 1 - Excel Spreadsheet read into a ListView

 

The first step is to create a reference in your project to Excel 9.0 Objects Library.  This is done by right mouse clicking on the References folder in the Solution Explorer and choose Add Reference. This brings up the Tab Dialog below. Choose the COM Tab and pick Microsoft Excel 9.0 Objects Library.

 

 

 

Figure 2 - Adding an Excel Reference

 

This action puts an Interop.Excel.dll and Interop.Office.dll into your bin directory so you can manipulate excel.

 

Now we can declare our Excel Application Object and the compiler will recognize it:

private Excel.Application ExcelObj = null;

Excel is launched and an Application reference is obtained in the constructor of our form.  First an Excel Application object is constructed.  Then we check to make sure Excel was actually started. If it was, we have a valid application object and we can now use it to open a file:

public Form1()
{
    // Initialize the Windows Components
    InitializeComponent();
    ExcelObj = new Excel.Application();
    // See if the Excel Application Object was successfully constructed
    if (ExcelObj == null)
    {
        MessageBox.Show("ERROR: EXCEL couldn't be started!");
        System.Windows.Forms.Application.Exit();
    }
    // Make the Application Visible
    ExcelObj.Visible = true;
}

The code for opening the Excel file is shown below. The code uses the OpenFileDialog component to get the path name for the Excel file.  The Excel file is opened using the WorkBooks collections' Open method.  This method takes 15 parameters with the following definition.

 

Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMenuRecentlyUsed]) As Workbook

 

We really are only interested in the FileName, but have added the other default parameters for your reference.  There is also an OpenText method in Workbooks for opening tab or comma delimited text files.

private void menuItem2_Click(object sender, System.EventArgs e)
{
    // prepare open file dialog to only search for excel files (had trouble setting this in design view)
    this.openFileDialog1.FileName = "*.xls"; if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        // Here is the call to Open a Workbook in Excel
        // It uses most of the default values (except for the read-only which we set to true)
        Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(openFileDialog1.FileName, 0, true, 5,
        "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
        // get the collection of sheets in the workbook
        Excel.Sheets sheets = theWorkbook.Worksheets;
        // get the first and only worksheet from the collection of worksheets
        Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
        // loop through 10 rows of the spreadsheet and place each row in the list view
        for (int i = 1; i <= 10; i++)
        {
            Excel.Range range = worksheet.get_Range("A" + i.ToString(), "J" + i.ToString());
            System.Array myvalues = (System.Array)range.Cells.Value;
            string[] strArray = ConvertToStringArray(myvalues);
            listView1.Items.Add(new ListViewItem(strArray));
        }
    }
}

You also may want to note the ConvertToStringArray method which is used to convert a System.Array into a string array.  If someone knows an easier way to do this, please let me know and I'll alter the article.  The problem is that an Excel Array comes back as two dimensional even if you are selecting a range of a single row, so you need to change the 2-d array into something the listview can accept. Also the listview array is 0 based and the Excel range array is 1 based.

string[] ConvertToStringArray(System.Array values)
{
    // create a new string array
    string[] theArray = new string[values.Length];
    // loop through the 2-D System.Array and populate the 1-D String Array
    for (int i = 1; i <= values.Length; i++)
    {
        if (values.GetValue(1, i) == null)
            theArray[i - 1] = "";
        else
            theArray[i - 1] = (string)values.GetValue(1, i).ToString();
    }
    return theArray;
}

That's all there is to it!  Now you can read in all your old Excel Databases. Perhaps you can even use ADO.NET to place them into a database! (Could be the topic for the next article).

 

VBA Resources for Excel:  Excel VBA Made Easy

 

Affiliation Links:  Learn MS Office the Easy Way  Passing Microsoft Certification  Basic Programming