How to read data of an Excel file using C#?

Question

How to read an Excel file using C#? I open an Excel file for reading and copy it to clipboard to search email format, but I don't know how to do it.

FileInfo finfo; Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass(); ExcelObj.Visible = false;  Excel.Workbook theWorkbook; Excel.Worksheet worksheet;  if (listView1.Items.Count > 0) {     foreach (ListViewItem s in listView1.Items)     {         finfo = new FileInfo(s.Text);         if (finfo.Extension == ".xls" || finfo.Extension == ".xlsx" || finfo.Extension == ".xlt" || finfo.Extension == ".xlsm" || finfo.Extension == ".csv")         {             theWorkbook = ExcelObj.Workbooks.Open(s.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false);              for (int count = 1; count <= theWorkbook.Sheets.Count; count++)             {                 worksheet = (Excel.Worksheet)theWorkbook.Worksheets.get_Item(count);                 worksheet.Activate();                 worksheet.Visible = false;                 worksheet.UsedRange.Cells.Select();             }         }     } } 

Answer 1

OK,

One of the more difficult concepts to grasp about Excel VSTO programming is that you don't refer to cells like an array, Worksheet[0][0] won't give you cell A1, it will error out on you. Even when you type into A1 when Excel is open, you are actually entering data into Range A1. Therefore you refer to cells as Named Ranges. Here's an example:

Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet;  Excel.Range range = sheet.get_Range("A1", Missing.Value) 

You can now literally type:

range.Text // this will give you the text the user sees range.Value2 // this will give you the actual value stored by Excel (without rounding) 

If you want to do something like this:

Excel.Range range = sheet.get_Range("A1:A5", Missing.Value)  if (range1 != null)      foreach (Excel.Range r in range1)      {          string user = r.Text          string value = r.Value2       } 

There might be a better way, but this has worked for me.

The reason you need to use Value2 and not Value is because the Value property is a parametrized and C# doesn't support them yet.

As for the cleanup code, i will post that when i get to work tomorrow, i don't have the code with me, but it's very boilerplate. You just close and release the objects in the reverse order you created them. You can't use a Using() block because the Excel.Application or Excel.Workbook doesn't implement IDisposable, and if you don't clean-up, you will be left with a hanging Excel objects in memory.

Note:

• If you don't set the Visibility property Excel doesn't display, which can be disconcerting to your users, but if you want to just rip the data out, that is probably good enough
• You could OleDb, that will work too.

I hope that gets you started, let me know if you need further clarification. I'll post a complete

here is a complete sample:

using System; using System.IO; using System.Reflection; using NUnit.Framework; using ExcelTools = Ms.Office; using Excel = Microsoft.Office.Interop.Excel;  namespace Tests {     [TestFixture]     public class ExcelSingle     {         [Test]         public void ProcessWorkbook()         {             string file = @"C:\Users\Chris\Desktop\TestSheet.xls";             Console.WriteLine(file);              Excel.Application excel = null;             Excel.Workbook wkb = null;              try             {                 excel = new Excel.Application();                  wkb = ExcelTools.OfficeUtil.OpenBook(excel, file);                  Excel.Worksheet sheet = wkb.Sheets["Data"] as Excel.Worksheet;                  Excel.Range range = null;                  if (sheet != null)                     range = sheet.get_Range("A1", Missing.Value);                  string A1 = String.Empty;                  if( range != null )                     A1 = range.Text.ToString();                  Console.WriteLine("A1 value: {0}", A1);              }             catch(Exception ex)             {                 //if you need to handle stuff                 Console.WriteLine(ex.Message);             }             finally             {                 if (wkb != null)                     ExcelTools.OfficeUtil.ReleaseRCM(wkb);                  if (excel != null)                     ExcelTools.OfficeUtil.ReleaseRCM(excel);             }         }     } } 

I'll post the functions from ExcelTools tomorrow, I don't have that code with me either.

Edit: As promised, here are the Functions from ExcelTools you might need.

public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable,         bool updateLinks) {         Excel.Workbook book = excelInstance.Workbooks.Open(             fileName, updateLinks, readOnly,             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,             Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,             Type.Missing, Type.Missing);         return book;     }  public static void ReleaseRCM(object o) {         try {             System.Runtime.InteropServices.Marshal.ReleaseComObject(o);         } catch {         } finally {             o = null;         }     } 

To be frank, this stuff is much easier if you use VB.NET. It's in C# because I didn't write it. VB.NET does option parameters well, C# does not, hence the Type.Missing. Once you typed Type.Missing twice in a row, you run screaming from the room!

As for you question, you can try to following:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(VS.80).aspx

I will post an example when I get back from my meeting... cheers

Edit: Here is an example

range = sheet.Cells.Find("Value to Find",                                                  Type.Missing,                                                  Type.Missing,                                                  Type.Missing,                                                  Type.Missing,                                                  Excel.XlSearchDirection.xlNext,                                                  Type.Missing,                                                  Type.Missing, Type.Missing);  range.Text; //give you the value found 

Here is another example inspired by this site:

 range = sheet.Cells.Find("Value to find", Type.Missing, Type.Missing,Excel.XlLookAt.xlWhole,Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlNext,false, false, Type.Missing); 

It helps to understand the parameters.

P.S. I'm one of those weird people who enjoys learning COM automation. All this code steamed from a tool I wrote for work which required me to process over 1000+ spreadsheets from the lab each Monday.

Answer 2

You can use Microsoft.Office.Interop.Excel assembly to process excel files.

1. Right click on your project and go to Add reference. Add the Microsoft.Office.Interop.Excel assembly.
2. Include using Microsoft.Office.Interop.Excel; to make use of assembly.

Here is the sample code:

    using Microsoft.Office.Interop.Excel;      //create the Application object we can use in the member functions.     Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application();     _excelApp.Visible = true;      string fileName = "C:\\sampleExcelFile.xlsx";      //open the workbook     Workbook workbook = _excelApp.Workbooks.Open(fileName,         Type.Missing, Type.Missing, Type.Missing, Type.Missing,         Type.Missing, Type.Missing, Type.Missing, Type.Missing,         Type.Missing, Type.Missing, Type.Missing, Type.Missing,         Type.Missing, Type.Missing);      //select the first sheet             Worksheet worksheet = (Worksheet)workbook.Worksheets[1];      //find the used range in worksheet     Range excelRange = worksheet.UsedRange;      //get an object array of all of the cells in the worksheet (their values)     object[,] valueArray = (object[,])excelRange.get_Value(                 XlRangeValueDataType.xlRangeValueDefault);      //access the cells     for (int row = 1;  row <= worksheet.UsedRange.Rows.Count; ++row)     {         for (int col = 1; col <= worksheet.UsedRange.Columns.Count; ++col)         {             //access each cell             Debug.Print(valueArray[row, col].ToString());         }     }      //clean up stuffs     workbook.Close(false, Type.Missing, Type.Missing);     Marshal.ReleaseComObject(workbook);      _excelApp.Quit();     Marshal.FinalReleaseComObject(_excelApp);