Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

How to read an Excel file using C#



The following program illustrates how to open an existing Excel spreadsheet in C# using .NET Framework COM interop capability. Also, you can see how to find Named Ranges in Excel and get the range of occupied cells (Used area) in an excel sheet.

Excel Library

To access the object model from Visual C# .NET, you have to add the Microsoft Excel 15.0 Object Library to your project.

Create a new project in your Visual Studio and add a Command Button to your C# Form.

How to use COM Interop to Create an Excel Spreadsheet

Form the following pictures you can find how to add an Excel reference library in your project.

Select Add Reference dialogue from Project menu of your Visual Studio.

Select Microsoft Excel 15.0 Object Library of COM left-side menu and click OK button.

After import the reference library, we have to initialize the Excel application Object.

Excel.Application xlApp = new
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
Excel.Range range ;

Next step is to open the Excel file and get the specified worksheet.

 
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(@"d:\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

After getting the selected worksheet, next step is to specify the used range in the worksheet

How to specify a range in an Excel sheet?

If you want to select a specific cell in Excel sheet, you can code like this.

Excel.Worksheet excelSheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range)excelSheet.Cells[10, 10];

Reading Named Ranges in Excel

Worksheet.get_Range Method

If you want to select multiple cell value from Excel sheet, you can code like this.

Excel.Worksheet excelSheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) excelSheet.get_Range(excelSheet.Cells[1, 1],
excelSheet.Cells[3,3]);

How to get the range of occupied cells in the excel sheet

For the reading entire content of an Excel file in C#, we have to know how many cells used in the Excel file. To find the used range we use “UsedRange” property of xlWorkSheet. A used range includes any cell that has ever been used. It will return the last cell of the used area.

 
Excel.Range range ;
range = xlWorkSheet.UsedRange;

How to properly clean up Excel interop objects

Interop marshalling governs how data is passed in method arguments and return values between managed and unmanaged memory during calls. Most data types have common representations in both managed and unmanaged memory. The interop marshaler handles these types for you. Other types can be ambiguous or not represented at all in managed memory.

Marshal.ReleaseComObject (excelWB);
Marshal.ReleaseComObject (excelApp);

It is important to note that every reference to an Excel COM object had to be set to null when you have finished with it, including Cells, Sheets, everything.

The Marshal class is in the System.Runtime.InteropServices namespace, so you should import the following namespace.

 using System.Runtime.InteropServices;

Open and Read an Excel Spreadsheet Programmatically

Copy and paste the following source code in your C# project file

using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel; 

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

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            Excel.Range range ;

            string str;
            int rCnt ;
            int cCnt ;
            int rw = 0;
            int cl = 0;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(@"d:\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            rw = range.Rows.Count;
            cl = range.Columns.Count;


            for (rCnt = 1; rCnt  < = rw; rCnt++)
            {
                for (cCnt = 1; cCnt  < = cl; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                    MessageBox.Show(str);
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);

        }

    }
}

When you execute this C# source code the program read all used cells from Excel file.


Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More