C# Utility Class To Create and Access MS Excel

This class can be used to create a Excel workbook and setting values in a particular cell.

Many times we need to save some values in excel and do some calculations programatically.
We usually automate  performance calculation and calculating percentage of improvement using the following class.

This class helps us to save your time in excel automation and performance calculation.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

class CExcelHelper
{
    public int m_nCol;
    public int m_nRow;
    private Excel.Application m_oXL;
    private Excel._Workbook m_oWB;
    private Excel._Worksheet m_oSheet;
    private Excel.Range m_oRng;
    public void createExcel()
    {
        try
        {
            //Start Excel and get Application object.
            m_oXL = new Excel.Application();
            m_oXL.Visible = true;


            //Get a new workbook.
            m_oWB = (Excel._Workbook)(m_oXL.Workbooks.Add(Missing.Value));
            m_oSheet = (Excel._Worksheet)m_oWB.ActiveSheet;

            m_oXL.Visible = true;
            m_oXL.UserControl = true;
        }
        catch (Exception theException)
        {
            ErrMsg(ref theException);
        }
    }
    public bool SetValue(int nRow, int nCol, ref string strValue)
    {
        //Row and col should from 1S
        if (nRow < 1 || nCol < 1)
            return false;


        bool bRet = true;

        try
        {
            m_oSheet.Cells[nRow, nCol] = strValue;
        }
        catch (Exception e)
        {
            ErrMsg(ref e);
            return false;
        }
        return bRet;
    }

    public void CloseExcel()
    {
        m_oXL.Quit();
    }
    private void ErrMsg(ref Exception theException)
    {
        String errorMessage;
        errorMessage = "Error: ";
        errorMessage = String.Concat(errorMessage, theException.Message);
        errorMessage = String.Concat(errorMessage, " Line: ");
        errorMessage = String.Concat(errorMessage, theException.Source);
        MessageBox.Show(errorMessage, "Error");
    }
    public bool ApplyFormula(String strRangeStart, String strRangeEnd, string strFormula)
    {
        bool bRet = true;

        try
        {
            m_oRng = m_oSheet.get_Range(strRangeStart, strRangeEnd);
            m_oRng.Formula = strFormula;
        }
        catch (Exception e)
        {
            ErrMsg(ref e);
            return false;
        }
        return bRet;
    }
    private string RangeAddress(Excel.Range rng)
    {
        return rng.get_AddressLocal(false, false, Excel.XlReferenceStyle.xlA1);
    }
    public string CellAddress(int row, int col)
    {
        return RangeAddress(m_oSheet.Cells[row, col]);
    }
}

Usage:
//Excel Open
CExcelHelper objExcel = new CExcelHelper();
 objExcel.createExcel();
  string str="Sample";
 objExcel.SetValue(2, 1, ref str);

C# Code To Split String By Using Tabs As Delimmiter

We save some files as Tab delimited. In MS-Excel also have option to save the file as Tab delimited file. In that file there is a tab between each cells. We can read cell by cell using the following sample code.

Following Code snippet will split string using tab and store it in to a ArrayList.

public static void SplitTabs(string strText, ArrayList ArrTabs)
{

    int nStart = 0;
    int nPos = -1;
    int nLength = 0;
 //   strText = strText.Trim();
    nPos = strText.IndexOf("\t");
    if (nPos == -1 && strText.Length > 0)
    {
        ArrTabs.Add(strText);
    }
    while (nPos > -1)
    {
        nLength = nPos - nStart;
        string strLine = strText.Substring(nStart, nLength);
        strLine = strLine.Trim();

    //    if (strLine.Length > 0)
        {
            ArrTabs.Add(strLine);
        }
        nStart = nPos;
        nPos = strText.IndexOf("\t", nPos + 1);
        if (nPos == -1 && strText.Length > (nStart + 1)) //Last and remaining Text
        {
            strLine=strText.Substring(nStart );
            strLine = strLine.Trim();
            if (strLine.Length > 0)
            {
                ArrTabs.Add(strLine);
            }
        }
        nStart ++;

    }
}

Usage:
string strTxt = "'abc'\t'bcd'\t'xyz'";
ArrayList ArrLines= new ArrayList();
SplitTabs(strTxt, ArrLines);
 foreach (string str in ArrLines)
 {
      Console.WriteLine(str);
 }

Output:

C# Code to Split String Into Lines

In parsing some times we need to get the string line by line. We can split the line by line using the end of line character('\r\n'). We need to find its position and it can be splited suing sub-string functions and given below.

Following code snippet split the string in to lines and store it in a Array list.

public static void SplitLines(string strText, ArrayList ArrLines)
{
    int nStart = 0;
    int nPos = -1;
    int nLength = 0;
    char[] szTrim = { '\r', '\n' };
    strText = strText.Trim(szTrim);
    nPos = strText.IndexOf("\r\n");
    if (nPos == -1 && strText.Length > 0)
    {
        ArrLines.Add(strText);
    }
    while (nPos > -1)
    {
        nLength = nPos - nStart;
        string strLine = strText.Substring(nStart, nLength);
        strLine = strLine.Trim(szTrim);

        if (strLine.Length > 0)
        {
            ArrLines.Add(strLine);
        }
        nStart = nPos;
        nPos = strText.IndexOf("\r\n", nPos + 1);

        if (nPos == -1 && strText.Length > (nStart + 1)) //Last and remaining Text
        {
            strLine = strText.Substring(nStart);
            strLine = strLine.Trim(szTrim);

            if (strLine.Length > 0)
            {
                ArrLines.Add(strLine);
            }
        }

        nStart++;

    }
}

Usage:

 string strTxt = "abc \r\nbcd \r\nxyz";
 ArrayList ArrLines= new ArrayList();
 SplitLines(strTxt,ArrLines);

//To print result
 foreach (string str in ArrLines)
 {
    Console.WriteLine(str);
 } 

Output screenshot:

C# Code To Get The Folder Path Of A Executable

Following Code snippet will return the folder path of the current running application.

 public static string GetExePath()
 {
     string strPath = Application.ExecutablePath;
     string strBackSlash = @"\";
     int nPos = -1;
     if ((nPos = strPath.LastIndexOf(strBackSlash)) > -1)
     {
         strPath = strPath.Substring(0, nPos + strBackSlash.Length);
     }
     return strPath;
 }

Usage:
 string strpath = GetExePath();
 MessageBox.Show(strpath);

Screenshot:

 

C# Code To Extract Only Alphabets From A String

Sometimes we may need to remove all numbers and special characters from a string.
This can be done using ASCII vaues of characters.

Following code snippet will extract only alphabets  from a string using ASCII codes.

public static string GetAlphabets(string strText)
{
    string strAlphabets="";
    for(int i=0;i<strText.Length;i++)
    {
        char chr=strText[i];
        if(chr>=65 && chr<=90)
        {
            strAlphabets=strAlphabets+chr;
        }
        else if (chr >= 97 && chr <= 122)
        {
            strAlphabets = strAlphabets + chr;
        }
       
    }
    return strAlphabets;
}

Usage:

           string strTxt = "ABCD1234!@#$abcdefgh";
            Console.WriteLine("The string is " + strTxt);
            Console.WriteLine("Output: "+  GetAlphabets(strTxt));

Output:

C# Code To Execute a Command line Dos Exe and display the result

Some times we may need to execute a another command line executable file and display its result as string.
You can use the following code snippet for that.

public static string ReadOutputFromCmdExection(string strFileName, string strParameter)
{
    string StrResult = "";
    ProcessStartInfo start = new ProcessStartInfo();
    start.FileName = strFileName; // Specify Executable Name.
    start.UseShellExecute = false;
    start.Arguments = strParameter;
    start.RedirectStandardOutput = true;

    using (Process process = Process.Start(start))
    {
        //
        // Read in all the text from the process with the StreamReader.
        //
        using (StreamReader reader = process.StandardOutput)
        {
            StrResult = reader.ReadToEnd();
        }
    }
    return StrResult;
}

Usage:
 string strTxt = ReadOutputFromCmdExection(@"c:\windows\system32\ipconfig.exe","" );

Console.WriteLine(strTxt);





This helps to get the out put as string, so that we can parse it and use as we require.