C# - How to read Excel Cell Value in C#

Scenario: Download Script

You are working as C# developer, You need to write a program that should be able to read Excel Cell value and save into variable. Once saved into a variable then you can use for different purposes in your program.


How to read Excel Cell Value in C# from Excel Sheet


Below Script can be used to read any cell value. You simple have to provide the value to CellToRead variable. Let's say if I would like to read E7 Cell. I will use below C# script to read E7 Excel Cell. you can change to any cell you like to read.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.OleDb;
using System.Data;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                string CellToRead = "E7"; //Provide the Excel Cell you want to read
                string CellName = "";
                Int32 CellNumber;
                string fileFullPath = @"C:\Source\TechBrothersit.xlsx"; //Provide Excel File Path
                string sheetname="Sheet1$"; //Provide the Sheet Name
                string CellValue="";

                var alphanumericstring = new System.Text.RegularExpressions.Regex("(?<Alpha>[a-zA-Z]*)(?<Numeric>[0-9]*)");
                var match = alphanumericstring.Match(CellToRead);

                CellName = match.Groups["Alpha"].Value;
                int.TryParse(match.Groups["Numeric"].Value, out CellNumber);

                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR = "YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                          fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" +
                          HDR + ";IMEX=0\"";

                OleDbConnection cnn = new OleDbConnection(ConStr);        
                cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


                        //Read the Excel Cell value from Excel Sheet
                        OleDbCommand oconn = new OleDbCommand("select  * from ["
                                            sheetname + CellName + 
                                            (CellNumber - 1).ToString() 
                                            + ":" + CellToRead + "]", cnn);
                        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                        DataTable dt = new DataTable();
                        adp.Fill(dt);
                        cnn.Close();
                        
                        foreach (DataRow row in dt.Rows)
                        {
                            object[] array = row.ItemArray;
                            CellValue = array[0].ToString();
                        }

                    //Print Excel Cell Value
                    Console.WriteLine("CelltoRead :" + CellToRead + "   Cell Value:" + CellValue);
                    Console.ReadLine();               
                
            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}


I executed above script and it print the Excel cell value for me. The value is saved in CellValue variable and you are free to use anywhere you like in your program.
How to read Excel Cell Value in C# Program

No comments:

Post a Comment