Don't Break The Build Software development articles and tutorials

30Jan/1126

Excel And C# – Interop With .Net 4 – How To Read Data From Excel


With the release of .Net 4, Microsoft has made Excel Interop incredibly simple and more intuitive. While this makes using Excel files incredibly easy, it does require that the computer that is running this code has Excel installed (which may be a problem for some people). Here is what you will need to get started:

  1. Visual Studio 2010 (Express Edition works great, that is what I use)
  2. Excel installed on your computer
  3. .Net 4.0 installed

To get started, create a new project in Visual Studio (this tutorial assumes you are using WinForm project). After you do that you will need to add a reference to the Excel Object Library in the COM tab (it should be called Microsoft Excel 12.0 Object Library if you have Excel 2007 installed, if you don't then the 12 may be lower). Once you have that reference set we are set to go.

We want this to be easy so we will need to add the following to the top of your file

using Excel = Microsoft.Office.Interop.Excel;

We add this because it now allows us to access the Excel libraries easily and it makes it clear what the code is for. Once we have that code, we want to create the OnLoad event and that is what we will use to handle the Excel data. Here is the code to create an Excel App in C#:
Excel.Application xlApp = new Excel.Application();


Now remember the line we appended to the top of our file let us access the Interop library by using Excel.xxx, this is where it comes in handy! Well we created an Excel App so what? What can we do with it? Nothing right now, but we with a few lines of code we will be able to start reading data!


Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:/C.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

We have now created an Excel application, a workbook, a worksheet and then selected the range that was used and also created two integers that contain the count of Columns and Rows. Using a for loop we can loop over the entire data set and get each value as follows:
for(int i=1; i <= rowCount; i++)
{
  for(int j=1; j<=colCount; j++)
  {
    MessageBox.Show(xlRange.Cells[i,j].Value2.ToString());
  }
}

It is important to make sure you use Value2 or else you will get a string that just says "Com Object" which is not what you want!!! Also, it is extremely important to make sure that the i and j start equal to 1 as there is no row/col 0 so your program will not run. Now, with the excel spreadsheet I included with this tutorial (and was taken from Yahoo Finance, thanks to them!) running this will take a very, very long time (there are about 8,000 rows in the file) and because we do not have it on any other thread, the UI will become non-responsive. The next tutorial in this series will show us how we can fix that to load large Excel files into our program while maintaining a responsive UI.

Here is the full code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTut
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:/C.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    MessageBox.Show(xlRange.Cells[i, j].Value2.ToString());
                }
            }

        }

    }
}

Tagged as: , , Leave a comment
Comments (26) Trackbacks (0)
  1. Tip top stuff. I’ll expcet more now.

  2. Thank you very much, This post was clean & helpful, what is the diff between (Excel._worksheet and Excel.worksheet ) ?

  3. Fantastic website. A lot of helpful info here. I?m sending it to several pals ans also sharing in delicious. And of course, thank you to your sweat!

  4. I don’t ordinarily comment but I gotta admit thanks for the post on this great one :D.

  5. Hello sir,
    i m using the same configuration for development.But the exception
    ” Exception from HRESULT: 0x800A03EC” .
    Plase help me now..

    Regards karthik.

  6. Hello Sabry,
    I am getting the warning/error saying : Warning 1 Cannot find wrapper assembly for type library “Microsoft.Office.Core”. ExcelTut
    I did add it though. I am not sure what’s wrong.
    Can you help?

    • Hi Grisha,

      The problem is that the path for the Office dll’s are wrong. You will have to go back to add a reference and use the COM tab to add them again. Once you do that, you should be good to go.

      Thanks,

    • Hey John,Great post, referring to Error using brliavae for worksheet name in SSIS Excel destination I wanted to put the sheet name as an expression in the sheet name brliavae rather then writing a script to hold the name but its not working. Would you be willing to share your sql task and script ? A screen shot of that flow also would be helpful.

  7. Its superb as your other posts : D, thanks for putting up. “For peace of mind, we need to resign as general manager of the universe.” by Larry Eisenberg.

  8. This is a marvelous post. Clean concise and extremely useful. I love the code structure too. Keep it up.

  9. Very nice example, a little bit strange lib where i starts with 1

  10. Very Nice Post…
    Thanks a lot !!

  11. Hi!

    This is a nice article. Thanks for sharing your knowledge. I have read some other links (Write data in EXCEL, CSV and XML file using C#) that’s also helpful for

    developers.

    http://www.codeproject.com/Articles/263106/Export-Tabular-Data-in-CSV-and-Excel-Formats-Throu

    http://www.mindstick.com/Blog/257/Write%20data%20in%20EXCEL%20CSV%20and

    http://www.mindstick.com/Articles/8abc2b55-713c-4552-9dca-b36a38c686a9/

  12. I am using this code on different Pc where i m getting “Exception from HRESULT: 0x800A03EC”. Excel 2007 is installed there. where as on my pc there is exl 2010 installed.

  13. Good blog! I truly love how it is easy on my eyes and the data are well written.

  14. I’ve searched for a hint like this for weeks.
    Great stuff!

  15. Don’t use xlworkbook = xlApp.Workbooks.Open(). There is a common no “two dot” rule with excel interop. In this case a com object for the workbooks is created but no reference is held to it to release the com object. Break this into two lines

    xlWorkbooks = xlApp.Workbooks;
    XlWorkbook = xlWorkbooks.Open(filePath);

    This way you can call releasecom on xlWorkbooks. Failing to release com objects may result in an excel app process continuing to run in the background after your program closes.

  16. You can read data from excel file without installing excel by using .NET Library for excel from Aspose. They offers many features like creating a new excel file and apply formatting option on the worksheet and many more features. Check out this Api here:

    http://www.aspose.com/.net/excel-component.aspx

  17. Excellent info, it was a big help!

    I would like to echo the note about the excel process lingering. I was able to call the “Close” method on the workbook and the process was ended. If you can find the time to update the sample code in this post it may save people headaches in the future (especially since most people won’t read the comments).

    Thanks again for this great information!

  18. Hi,

    I get compilation error on value2(xlRange.Cells[i, j].Value2.ToString())
    how can i fix this????

  19. Hi,
    i am developing an add-in application i am done with this things by the same way you did.Now i want to display the used cells as print preview on another form ,suppose i have data in my document from A1 to A7 then on clicking on preview btn that 7 records should be displayed on another form .Do you guys have any idea plz help …

    Excel.Range startcell = ExWorksheet.get_Range(A1, Type.Missing);
    Excel.Range endcell = ExWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
    Excel.Range worksheetCells = ExWorksheet.get_Range(startcell, endcell);
    PrintPageEventHandler(print_page);
    ExWorksheet.PrintPreview(worksheetCells);

    this code works fine but it doesnt open on a new form it simply opens the print prevw option of excel.Is there any way to use it with print preview dialouge or prnt preview contrl….Thanks in Advance





Leave a comment

No trackbacks yet.