Tuesday, 21 August 2018

C#: Read from a spreadsheet/Excel

In my previous post i have mentioned about reading from CSV. In this i'm going to share the method i use to read from excel.

Reference:

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

Method:

        public void ReadExcel(string excelPath)
        {
        private static Microsoft.Office.Interop.Excel.Application appExcel;
        Microsoft.Office.Interop.Excel.Workbook theWorkbook;
        Microsoft.Office.Interop.Excel.Sheets objSheets;
        Microsoft.Office.Interop.Excel.Worksheet objWorkSheet;
        Microsoft.Office.Interop.Excel.Range range;
        string[] strArray;

            try
            {
                appExcel = new Microsoft.Office.Interop.Excel.Application();
                if (appExcel != null)
                {
                    theWorkbook = appExcel.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    objSheets = theWorkbook.Worksheets;
                    objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(1);
                    for (int i = 2; i <= 11; i++)
                    {
                        range = objWorkSheet.get_Range("A" + i.ToString(), "I" + i.ToString());
                        System.Array myValues = (System.Array)range.Cells.get_Value(Type.Missing);
                        strArray = myValues.OfType<object>().Select(o => o.ToString()).ToArray();

                        StartSearching(strArray);
                    }
                }
                else
                {
                    //clean up stuffs
                    theWorkbook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(theWorkbook);

                    appExcel.Quit();
                    Marshal.FinalReleaseComObject(appExcel);
                }
            }
            catch (Exception ex)
            {
                _log.ErrorFormat("There was an error reported Read Excel! {0} \n\n", ex.Message, ex.StackTrace);
            }
            _log.DebugFormat("ReadExcel Completed");
        }

No comments:

Post a Comment