Tuesday, September 11, 2012

Methods to read excel files in C#

Useful C# methods in order to read cell values in excel.
Remember to add a .NET reference for Microsoft.Office.Interop.Excel and use it in your program via the statement using Microsoft.Office.Interop.Excel;

private static Microsoft.Office.Interop.Excel.ApplicationClass appExcel;
private static Workbook newWorkbook = null;
private static _Worksheet objsheet = null;

Method to initialize excel connection:
        static void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
                System.Windows.Forms.Application.Exit();
            }
           
        }

Method to read a cell's value:
        static string excel_getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }

            return value;
        }

Method to safely close excel connection:
        static void excel_close()
        {
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }

To use it in your program, you need to initialize the excel connection first via this method:
excel_init(Path_to_your_excel_file);
make sure that your slash \ are double slashes \\. E.g. C:\\excel_file.xls

To read a cell value:
excel_getValue("A1"); this will return the value of the cell.

Safely close the connection with
excel_close(); to prevent memory problems.

Thanks for reading! I hope you gain additional knowledge.

If you have questions and or problems using it, please comment on this article.

Thanks,
Administrator

No comments:

Post a Comment