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");
        }

C#: Read from CSV

I'm writing this post thinking how easy it is to read and write to a file for a developer, however if you happen to write a piece of code to read from CSV i bet you take little longer. This is what happened to me. So i thought it's a good move to add this to my blog so i can refer this method anytime. 

If you think this helps you, make use of it whenever you want it.

Reference:

using System.IO;

Method to read from CSV: 

 public string[,] readCSV(string filePath)

        {

            try

            {

                string fileData = System.IO.File.ReadAllText(filePath);

                // Split into lines.

                fileData = fileData.Replace('\n', '\r');

                string[] lines = fileData.Split(new Char[] { '\r' }, StringSplitOptions.RemoveEmptyEntries);

                // See how many rows and columns there are.

                int totalRows = lines.Length;

                int totalCols = lines[0].Split(',').Length;

                // Allocate the data array.

                string[,] resultVals = new string[totalRows, totalCols];

                //populate the array with data

                for (int row = 0; row < totalRows; row++)

                {

                    string[] line_r = lines[row].Split(',');

                    for (int col = 0; col < totalCols; col++)

                    {

                        resultVals[row, col] = line_r[col];

                    }

                }

                return resultVals;

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

                return null;

            }

        }

Method that use the string array:

 private void StartSearching(string[,] strArray)

        {

            try

            {

                for (int i = 1; i < strArray.GetLength(0); i++)

                {

 

                        Narnum = strArray[i,0].ToString().TrimEnd().ToLower();

                        EmpName = strArray[i,1].ToString().Trim();

                        PreferredName = strArray[i,2].ToString().TrimEnd();

                        Firstname = strArray[i,3].ToString().TrimEnd();

                        Surname = strArray[i,4].ToString().TrimEnd();

                        Position = strArray[i,5].ToString().TrimEnd();

                        Department = strArray[i,6].ToString().TrimEnd();

                        Manager = strArray[i,7].ToString().TrimEnd();

                        Division = strArray[i,8].ToString().TrimEnd();

     Validate(Narnum, EmpName, Position, PreferredName, Surname, Firstname, Manager, Division);

                }

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

            }

        }

C#: Convert names to proper case

I have come across situations where some names or surnames have a character and managing casing is the important task. So i developed a simple method to convert the names to proper case in exceptional cases.

Ex: O'Brien , Tucky-Knight, Van Van, Nra (Nanette)

My method below identify the special character and make the next letter to upper case.


public string ConvertToProperNameCase(string input)
{
    char[] chars = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(input.ToLower()).ToCharArray();

             for (int i = 0; i + 1 < chars.Length; i++)
             {
                 if ((chars[i].Equals('\'')) || (chars[i].Equals('-')) || (chars[i].Equals('(')) || (chars[i].Equals(' ')))
                 {
                     chars[i + 1] = Char.ToUpper(chars[i + 1]);
                 }
             }
    return new string(chars);
}