C# Snippets

Message_WorksheetAlreadyExists

Public Shared Sub Message_WorksheetAlreadyExists(ByVal sWshName As String)

   Call System.Windows.Forms.MessageBox.Show( _
        "The worksheet '" & sWshName & "'" & _
         " already exists in this workbook.", _
        gsDIALOG_PREFIX_EXCEL, _
        Windows.Forms.MessageBoxButtons.OK, _
        Windows.Forms.MessageBoxIcon.Information)

End Sub

Wsh_ActiveName

Public Shared Function ActiveName() As String

   Dim objworkbook As Excel.Workbook
   Dim objworksheet As Excel.Worksheet

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      objworkbook = gApplicationExcel.ActiveWorkbook

      objworksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)

      ActiveName = objworksheet.Name

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      objworkbook = Nothing

      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("ActiveName", "clsWsh", _
              "returns the name of the currently active worksheet.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

Wsh_AddNew

Public Shared Sub AddNew(ByVal sWshName As String, _
                Optional ByVal sAfterWshName As String = "", _
                Optional ByVal sBeforeWshName As String = "", _
                Optional ByVal objSheetHidden As Excel.XlSheetVisibility = _
                               Excel.XlSheetVisibility.xlSheetVisible)

   Dim objworkbook As Excel.Workbook
   Dim objworksheet As Excel.Worksheet

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim sactivewsh As String

      If clsWsh.Exists(sWshName) = -1 Then
         objworkbook = gApplicationExcel.ActiveWorkbook

         sactivewsh = CType(objworkbook.ActiveSheet, Excel.Worksheet).Name

         gApplicationExcel.ScreenUpdating = False

         objworkbook.Sheets.Add()

         objworksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
         objworksheet.Name = sWshName

         If (sBeforeWshName.Length > 0) Then
            objworksheet.Move(before:=gApplicationExcel.Sheets(sBeforeWshName))
         End If

         If (sAfterWshName.Length > 0) Then
            objworksheet.Move(After:=gApplicationExcel.Sheets(sAfterWshName))
         End If

         'then inserts the new worksheet at the end of all the existing worksheets
         If (sBeforeWshName.Length = 0) And (sAfterWshName.Length = 0) Then
            objworksheet.Move(After:=gApplicationExcel.Sheets(objworkbook.Worksheets.Count))
         End If

         objworksheet.Visible = objSheetHidden

         CType(objworkbook.Worksheets(sactivewsh), Excel.Worksheet).Select()

         gApplicationExcel.ScreenUpdating = True

      Else
         Call clszMessagesExcel.WorksheetAlreadyExistsInformation(sWshName)
      End If

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      gApplicationExcel.ScreenUpdating = True

      objworkbook = Nothing

      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("AddNew", msCLASSNAME, _
              "add the new worksheet '" & sWshName & "' " & _
              "after the worksheet '" & sAfterWshName & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

Wsh_Delete

Public Shared Sub Delete(ByVal sWshName As String, _
                Optional ByVal bDisplayAlerts As Boolean = True)

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objworkbook As Excel.Workbook
      Dim objworksheet As Excel.Worksheet

      gApplicationExcel.DisplayAlerts = bDisplayAlerts

      objworkbook = gApplicationExcel.ActiveWorkbook
      objworksheet = CType(objworkbook.ActiveSheet, Excel.Worksheet)

      objworksheet.Delete()

      gApplicationExcel.DisplayAlerts = True

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("Delete", msCLASSNAME, _
              "delete the worksheet '" & sWshName & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

Wsh_DeleteAllExcept

Public Shared Sub DeleteAllExcept(ByVal sWshNamesToKeep As String, _
                         Optional ByVal sSeperateChar As String = ";", _
                         Optional ByVal bDisplayAlerts As Boolean = True, _
                         Optional ByVal sWbkName As String = "")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objworkbook As Excel.Workbook
      Dim objworkbookreturn As Excel.Workbook
      Dim objworksheet As Excel.Worksheet
      Dim objworksheetreturn As Excel.Worksheet

      Dim swshnametokeep As String

      If sSeperateChar = "" Then sSeperateChar = Microsoft.VisualBasic.Chr(10)

      If sWbkName.Length > 0 Then
         objworkbookreturn = gApplicationExcel.ActiveWorkbook
         CType(objworkbookreturn, Excel._Workbook).Activate()
      End If

      objworksheetreturn = CType(objworkbook.ActiveSheet, Excel.Worksheet)

      gApplicationExcel.DisplayAlerts = False

      For Each objworksheet In objworkbook.Worksheets
         If clsWsh.NameIsInList(objworksheet.Name, sWshNamesToKeep) = False Then
            objworksheet.Delete()
         End If
      Next objworksheet

      gApplicationExcel.DisplayAlerts = bDisplayAlerts

      objworksheetreturn.Select()

      If sWbkName.Length > 0 Then
         CType(objworkbookreturn, Excel._Workbook).Activate()
      End If

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("DeleteAllExcept", msCLASSNAME, _
              "delete all the workbooks except the ones in the list.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

Wsh_Exists

public static bool Wsh_Exists(
     Excel.Workbook workbook,
     string sheetName)
 {
     try
     {
         if (workbook == null || sheetName == null)
         {
             return false;
         }
         foreach (Excel.Worksheet sheet in workbook.Sheets)
         {
             //  sheet = workbook.Sheets[i];

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

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

Public Shared Function Wsh_Exists(ByVal sWshName As String, _
                     Optional ByVal bInformUser As Boolean = False) _
                              As Integer

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objworkbook As Excel.Workbook
      Dim objWorksheet As Excel.Worksheet

      Dim bexists As Boolean
      Dim iwshcount As Integer
      Dim iwshcounter As Integer

      objworkbook = gApplicationExcel.ActiveWorkbook

      bexists = False
      iwshcounter = -1
      For iwshcount = 1 To objworkbook.Worksheets.Count
         objWorksheet = CType(objworkbook.Worksheets(iwshcount), Excel.Worksheet)

         If sWshName = objWorksheet.Name Then bexists = True
         If sWshName = objWorksheet.Name Then iwshcounter = 0
      Next iwshcount

      If bexists = False Then
         '      For iwshcount = 1 To ActiveWorkbook.Worksheets.Count
         '         If InStr(Worksheets(iwshcount).Name, sWshName) > 0 Then
         '            iwshcounter = iwshcounter + 1
         '         Else
         '            iwshcounter = -1
         '         End If
         '      Next iwshcount
      Else
         If bInformUser = True Then
            Call clszMessagesExcel.WorksheetAlreadyExistsInformation(sWshName)
         End If
      End If

      Exists = iwshcounter

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("Exists", "clsWsh", _
              "determine if the worksheet name '" & sWshName & "' " & _
              "already exists in the active workbook.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

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 Sub HideWsh(ByVal sWshName As String, _
                   ByVal bShowIt As Boolean)

   Dim objworkbook As Excel.Workbook
   Dim objworksheet As Excel.Worksheet

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

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

      If bShowIt = True And _
         objworksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden Then

         objworksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible

      ElseIf bShowIt = False And _
          objworksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible Then
         objworksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden

      Else                                 'don't do anything
      End If

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Dim serrortext As String
         If bShowIt = True Then serrortext = "Show "
         If bShowIt = False Then serrortext = "Hide "

         Call clsError.Handle("HideWsh", "clsWsh", _
              serrortext & "the worksheet called '" & sWshName & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

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 Shared Function NameIsInList(ByVal sWshName As String, _
                                    ByVal sWshConcatenation As String, _
                           Optional ByVal sSeperateChar As String = ";", _
                           Optional ByVal sWbkName As String = "") _
                                    As Boolean

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim swshnametokeep As String
      Dim stemporary As String

      NameIsInList = False

      stemporary = sWshConcatenation
      Do While stemporary.Length > 0
         If stemporary.IndexOf(sSeperateChar) > -1 Then
            swshnametokeep = stemporary.Substring(0, stemporary.IndexOf(sSeperateChar))
         Else
            swshnametokeep = stemporary
            stemporary = ""
         End If

         If sWshName = swshnametokeep Then
            NameIsInList = True
            Exit Function
         End If
         stemporary = stemporary.Substring(stemporary.IndexOf(sSeperateChar) + 1)
      Loop

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("NameIsInList", msCLASSNAME, _
              "determine if the worksheet name '" & sWshName & "' " & _
              "is in the following concatenated list:" & _
              gsCRLF & gsCRLF & sWshConcatenation, _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

Wsh_Paste

Public Shared Sub WorksheetPaste(ByVal objWorksheet As Excel.Worksheet)

   Try
      objWorksheet.Paste()

   Catch objCOMException As System.Runtime.InteropServices.COMException

   End Try
End Sub

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 Shared Sub SelectWsh(ByVal sWshName As String)

   Dim objworkbook As Excel.Workbook
   Dim objworksheet As Excel.Worksheet

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      objworkbook = gApplicationExcel.ActiveWorkbook

      objworksheet = CType(objworkbook.Worksheets(sWshName), Excel.Worksheet)

      objworksheet.Select()

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      objworkbook = Nothing

      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("SelectWsh", "clsWsh", _
              "select the worksheet called '" & sWshName & "' in the active workbook.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

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