Wednesday, April 27, 2011

Reading Excel file in C#

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

namespace code
{
    class excelReader
    {
        public void run()
        {

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

            string str = null;
            int rCnt = 1;
            int cCnt = 0;
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(@"D:\shrikant\abc.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(2);

            StreamWriter sWrite = new StreamWriter(@"D:\shrikant\outfile.csv");
            range = xlWorkSheet.UsedRange;


            object rowIndex = 1; int index = 0;
            string temp = null;
            while (((Excel.Range)range.Cells[rowIndex, 1]).Value2 != null)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    try
                    {

                        if ((range.Cells[rCnt, cCnt] as Excel.Range).Value2 == null)
                            temp = "";
                        else
                            temp = (range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();

                        str += temp + ",";
                        Console.Write(cCnt + " ");
                    }
                    catch (Exception exp)
                    {
                        Console.WriteLine(exp.Message);
                    }
                }
                str = str.Substring(0, str.Length - 1);

                cCnt = 1;
                rowIndex = 1 + index++;
                rCnt++;
                sWrite.WriteLine(str); str = "";
                Console.WriteLine("");
            }
            sWrite.Close();

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        public 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();
            }
        }

    }
}

No comments:

Post a Comment