C# - Snippets

Message_WorksheetAlreadyExists

public static void Message_WorksheetAlreadyExists(string sWshName)
{
    System.Windows.Forms.MessageBox.Show(
        "The worksheet '" + sWshName + "'" +
        " already exists in this workbook.",
        gsDIALOG_PREFIX_EXCEL,
        System.Windows.Forms.MessageBoxButtons.OK,
        System.Windows.Forms.MessageBoxIcon.Information
    );
}

Wsh_AddNew

public static void Wsh_AddNew(
    string sWshName,
    string sAfterWshName = "",
    string sBeforeWshName = "",
    Excel.XlSheetVisibility objSheetHidden = Excel.XlSheetVisibility.xlSheetVisible)
{
    Excel.Workbook objworkbook = null;
    Excel.Worksheet objworksheet = null;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        string sactivewsh;

        if (clsWsh.Exists(sWshName) == -1)
        {
            objworkbook = gApplicationExcel.ActiveWorkbook;

            sactivewsh = ((Excel.Worksheet)objworkbook.ActiveSheet).Name;

            gApplicationExcel.ScreenUpdating = false;

            objworkbook.Sheets.Add();

            objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
            objworksheet.Name = sWshName;

            if (sBeforeWshName.Length > 0)
            {
                objworksheet.Move(
                    Before: gApplicationExcel.Sheets[sBeforeWshName]
                );
            }

            if (sAfterWshName.Length > 0)
            {
                objworksheet.Move(
                    After: gApplicationExcel.Sheets[sAfterWshName]
                );
            }

            // inserts the new worksheet at the end of all existing worksheets
            if (sBeforeWshName.Length == 0 && sAfterWshName.Length == 0)
            {
                objworksheet.Move(
                    After: gApplicationExcel.Sheets[objworkbook.Worksheets.Count]
                );
            }

            objworksheet.Visible = objSheetHidden;

            ((Excel.Worksheet)objworkbook.Worksheets[sactivewsh]).Select();

            gApplicationExcel.ScreenUpdating = true;
        }
        else
        {
            clszMessagesExcel.WorksheetAlreadyExistsInformation(sWshName);
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        gApplicationExcel.ScreenUpdating = true;

        objworkbook = null;

        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "AddNew",
                msCLASSNAME,
                "add the new worksheet '" + sWshName + "' " +
                "after the worksheet '" + sAfterWshName + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wsh_Delete

public static void Wsh_Delete(
    string sWshName, 
    bool bDisplayAlerts = true)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Workbook objworkbook;
        Excel.Worksheet objworksheet;

        gApplicationExcel.DisplayAlerts = bDisplayAlerts;

        objworkbook = gApplicationExcel.ActiveWorkbook;
        objworksheet = (Excel.Worksheet)objworkbook.ActiveSheet;

        objworksheet.Delete();

        gApplicationExcel.DisplayAlerts = true;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Delete", msCLASSNAME, "delete the worksheet '" + sWshName + "'.", mobjCOMException, mobjException);
    }
}

Wsh_DeleteAllExcept

public static void DeleteAllExcept(
    string sWshNamesToKeep,
    string sSeperateChar = ";",
    bool bDisplayAlerts = true,
    string sWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Workbook objworkbook = null;
        Excel.Workbook objworkbookreturn = null;
        Excel.Worksheet objworksheet = null;
        Excel.Worksheet objworksheetreturn = null;

        string swshnametokeep;

        if (sSeperateChar == "")
            sSeperateChar = ((char)10).ToString();

        if (sWbkName.Length > 0)
        {
            objworkbookreturn = gApplicationExcel.ActiveWorkbook;
            ((Excel._Workbook)objworkbookreturn).Activate();
        }

        objworksheetreturn = (Excel.Worksheet)objworkbook.ActiveSheet;

        gApplicationExcel.DisplayAlerts = false;

        foreach (Excel.Worksheet wsh in objworkbook.Worksheets)
        {
            if (clsWsh.NameIsInList(wsh.Name, sWshNamesToKeep) == false)
            {
                wsh.Delete();
            }
        }

        gApplicationExcel.DisplayAlerts = bDisplayAlerts;

        objworksheetreturn.Select();

        if (sWbkName.Length > 0)
        {
            ((Excel._Workbook)objworkbookreturn).Activate();
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "DeleteAllExcept",
                msCLASSNAME,
                "delete all the workbooks except the ones in the list.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wsh_Exists

public static int Wsh_Exists(
    string sWshName, 
    bool bInformUser = false)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return 0;

        Excel.Workbook objworkbook;
        Excel.Worksheet objWorksheet;

        bool bexists = false;
        int iwshcount;
        int iwshcounter = -1;

        objworkbook = gApplicationExcel.ActiveWorkbook;

        for (iwshcount = 1; iwshcount <= objworkbook.Worksheets.Count; iwshcount++)
        {
            objWorksheet = (Excel.Worksheet)objworkbook.Worksheets[iwshcount];

            if (sWshName == objWorksheet.Name)
            {
                bexists = true;
                iwshcounter = 0;
            }
        }

        if (bexists == false)
        {
            // VB code had commented-out partial-match logic here
        }
        else
        {
            if (bInformUser == true)
            {
                clszMessagesExcel.WorksheetAlreadyExistsInformation(sWshName);
            }
        }

        return iwshcounter;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "Exists",
                "clsWsh",
                "determine if the worksheet name '" + sWshName +
                "' already exists in the active workbook.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return 0;
}

Wsh_GetNameOfActive

public static string Wsh_GetNameOfActive()
{
    Excel.Workbook objworkbook = null;
    Excel.Worksheet objworksheet = null;

    try
    {
        if (clsError.ErrorFlag() == true)
            return null;

        objworkbook = gApplicationExcel.ActiveWorkbook;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        ActiveName = objworksheet.Name;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        objworkbook = null;

        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "ActiveName",
                "clsWsh",
                "returns the name of the currently active worksheet.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return ActiveName;
}

Wsh_GetSheetByName

public static Excel.Worksheet getSheetByName(Excel.Workbook workbook, string sheetName)
  {
      try
      {
          if (workbook == null || sheetName == null)
          {
              return null;
          }

          foreach (Excel.Worksheet sheet in workbook.Sheets)
          {
              //  sheet = workbook.Sheets[i];

              if (sheet == null || sheet.Name == null)
                  continue;

              if (sheet.Name.Equals(sheetName))
              {
                  return sheet;
              }
          }

          return null;

      }
      catch (System.Exception ex)
      {
          ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
          return null;
      }
  }

Wsh_Hide

public void Wsh_HideWsh(
    string sWshName, 
    bool bShowIt)
{
    Excel.Workbook objworkbook;
    Excel.Worksheet objworksheet;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        objworkbook = gApplicationExcel.ActiveWorkbook;
        objworksheet = (Excel.Worksheet)objworkbook.Worksheets(sWshName);

        if (bShowIt == true & objworksheet.Visible == Excel.XlSheetVisibility.xlSheetHidden)
            objworksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
        else if (bShowIt == false & objworksheet.Visible == Excel.XlSheetVisibility.xlSheetVisible)
            objworksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;
        else
        {
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
        {
            string serrortext;
            if (bShowIt == true)
                serrortext = "Show ";
            if (bShowIt == false)
                serrortext = "Hide ";

            clsError.Handle("HideWsh", "clsWsh", serrortext + "the worksheet called '" + sWshName + "'.", mobjCOMException, mobjException);
        }
    }
}

Wsh_Insert

public static Excel.Worksheet Wsh_Insert(
     Excel.Application excelApp,
     Excel.Workbook workbook,
     string sheetName = null,
     string beforeSheet = null,
     string afterSheet = null,
     Constants.OrientationStyle orientationStyle = Constants.OrientationStyle.NONE)
 {
     try
     {
         if (excelApp == null)
         {
             return null;
         }

         // Create a new empty workbook in a new workbook set.
         //Excel.Workbook workbook = excelApp.ActiveWorkbook;
         Excel.Worksheet worksheet = null;

         // Add a new worksheet
         if (workbook == null)
         {
             return null;
         }

         if (sheetName != null)
         {
             if (sheetExists(workbook, sheetName))
             {
                 worksheet = workbook.Worksheets[sheetName];
                 clearSheetContent(worksheet);
                 return workbook.Worksheets[sheetName];
             }
         }

         worksheet = workbook.Worksheets.Add();

         if (sheetName != null)
         {
             worksheet.Name = sheetName;
         }

         excelApp.DisplayAlerts = true;

         Wbk_SaveWorkbook(workbook);

         excelApp.DisplayAlerts = false;

         if (beforeSheet != null && sheetExists(workbook, beforeSheet))
         {
             worksheet.Move(workbook.Sheets[beforeSheet], System.Type.Missing);
         }

         if (afterSheet != null && sheetExists(workbook, afterSheet))
         {
             worksheet.Move(System.Type.Missing, workbook.Sheets[afterSheet]);
         }

         setOrientation(worksheet, orientationStyle);

         return worksheet;

     }
     catch (System.Exception ex)
     {
         ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
         return null;
     }
 }

Wsh_InsertWorksheetAtBeginning

public static Excel.Worksheet insertWorksheetAtBeginning(
      Excel.Application excelApp,
      string sheetName,
      Constants.OrientationStyle orientationStyle = Constants.OrientationStyle.NONE)
  {
      try
      {
          Excel.Workbook workbook = excelApp.ActiveWorkbook;
          Excel.Worksheet worksheet = null;

          // Add a new worksheet
          if (workbook == null || sheetName == null)
          {
              return null;
          }

          if (sheetName != null)
          {
              if (sheetExists(excelApp.ActiveWorkbook, sheetName))
              {
                  worksheet = excelApp.ActiveWorkbook.Worksheets[sheetName];
                  clearSheetContent(worksheet);
                  worksheet.Move(workbook.Sheets[1], System.Type.Missing);
                  return worksheet;
              }
          }

          worksheet = workbook.Worksheets.Add();

          setOrientation(worksheet, orientationStyle);

          if (sheetName != null)
          {
              worksheet.Name = sheetName;
          }

          worksheet.Move(workbook.Sheets[1], System.Type.Missing);
          return worksheet;

      }
      catch (System.Exception ex)
      {
          ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
          return null;
      }
  }

Wsh_InsertWorksheetAtEnd

public static Excel.Worksheet insertWorksheetAtEnd(
     Excel.Application excelApp,
     string sheetName,
     Constants.OrientationStyle orientationStyle = Constants.OrientationStyle.NONE)
 {
     try
     {
         Excel.Workbook workbook = excelApp.ActiveWorkbook;
         Excel.Worksheet worksheet = null;

         // Add a new worksheet
         if (workbook == null || sheetName == null)
         {
             return null;
         }

         if (sheetName != null)
         {
             if (sheetExists(excelApp.ActiveWorkbook, sheetName))
             {
                 worksheet = excelApp.ActiveWorkbook.Worksheets[sheetName];
                 CommonUtil.WorksheetUtil.unprotectWorksheet(excelApp.ActiveWorkbook, sheetName);
                 clearSheetContent(worksheet);
                 worksheet.Move(System.Type.Missing, workbook.Sheets[workbook.Sheets.Count]);
                 return worksheet;
             }
         }

         worksheet = workbook.Worksheets.Add();

         setOrientation(worksheet, orientationStyle);

         if (sheetName != null)
         {
             worksheet.Name = sheetName;
         }

         worksheet.Move(System.Type.Missing, workbook.Sheets[workbook.Sheets.Count]);

         return worksheet;

     }
     catch (System.Exception ex)
     {
         ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
         return null;
     }
 }

Wsh_IsSheetVisible

public static bool sheetVisible(
      Excel.Workbook workbook,
      string sheetName)
  {
      try
      {
          if (workbook == null || sheetName == null)
          {
              return false;
          }
          foreach (Excel.Worksheet sheet in workbook.Sheets)
          {
              if (sheet == null || sheet.Name == null)
                  continue;

              if (sheet.Name.ToLower().Equals(sheetName.ToLower()))
              {
                  return (sheet.Visible == Excel.XlSheetVisibility.xlSheetVisible);
              }
          }
          return false;
      }
      catch (System.Exception ex)
      {
          ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
          return false;
      }
  }

Wsh_NameIsInList

public static bool NameIsInList(
    string sWshName,
    string sWshConcatenation,
    string sSeperateChar = ";",
    string sWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return false;

        string swshnametokeep;
        string stemporary;

        bool NameIsInList = false;

        stemporary = sWshConcatenation;

        while (stemporary.Length > 0)
        {
            if (stemporary.IndexOf(sSeperateChar) > -1)
            {
                swshnametokeep = stemporary.Substring(0, stemporary.IndexOf(sSeperateChar));
            }
            else
            {
                swshnametokeep = stemporary;
                stemporary = "";
            }

            if (sWshName == swshnametokeep)
            {
                NameIsInList = true;
                return NameIsInList;
            }

            stemporary = stemporary.Substring(stemporary.IndexOf(sSeperateChar) + 1);
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NameIsInList",
                msCLASSNAME,
                "determine if the worksheet name '" + sWshName + "' " +
                "is in the following concatenated list:" +
                gsCRLF + gsCRLF + sWshConcatenation,
                mobjCOMException,
                mobjException
            );
        }
    }

    return false;
}

Wsh_Paste

public static void WorksheetPaste(
    Excel.Worksheet objWorksheet)
{
    try
    {
        objWorksheet.Paste();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
    }
}

Wsh_Protect

public static bool protectWorksheet(
     Excel.Workbook workbook,
     string sheetName)
 {
     try
     {
         //Excel.Worksheet worksheet;
         //worksheet = getSheetByName(workbook, sheetName);

         //worksheet.Protect(Password: "mypassword");
         return true;
     }
     catch (System.Exception ex)
     {
         ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
         return false;
     }
 }

Wsh_Select

public static void Wsh_Select(
    string sWshName)
{
    Excel.Workbook objworkbook;
    Excel.Worksheet objworksheet;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        objworkbook = gApplicationExcel.ActiveWorkbook;

        objworksheet = (Excel.Worksheet)objworkbook.Worksheets(sWshName);

        objworksheet.Select();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        objworkbook = null;

        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
            clsError.Handle("Wsh_Select", "clsWsh", "select the worksheet called '" + sWshName + "' in the active workbook.", mobjCOMException, mobjException);
    }
}

Wsh_UnProtect

public static bool unprotectWorksheet(
      Excel.Workbook workbook,
      string sheetName)
  {
      try
      {
          //Excel.Worksheet worksheet;
          //worksheet = getSheetByName(workbook, sheetName);

          //worksheet.Unprotect(Password: "london");
          return true;
      }
      catch (System.Exception ex)
      {
          ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
          return false;
      }
  }