Reading data from excel using C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;

namespace ExcelReader
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.Application();
            object missing = System.Reflection.Missing.Value;
            string myPath = @"C:\Users\saikatd\Test.xlsx";
            xlWorkBook = xlApp.Workbooks.Open(myPath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
            range = xlWorkSheet.UsedRange;
            double sum = 0;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    object data = (range.Cells[rCnt, cCnt] as Excel.Range).Cells.get_Value();
                    if (rCnt != 1 && cCnt == 3 && data != null)
                    {
                        string str = (string)data;
                        Regex reg = new Regex(" ");
                        string[] strArray = reg.Split(str);
                        if (strArray[2] == "Dr.")
                        {
                            sum = sum - float.Parse(strArray[1]);
                        }
                        else
                        {
                            sum = sum + float.Parse(strArray[1]);
                        }
                        Console.Write(strArray[1]);
                        Console.WriteLine();
                    }
                }
            }

            Console.WriteLine("Sum = " + sum);

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }
        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}
Advertisements
This entry was posted in Information Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s