SpreadSheetLightHelpers.vb

Imports SpreadsheetLight
''' <summary>
''' Commonly used methods for SpreadSheetLight library.
''' Anytime there are methods that are used over and over again
''' consider placing those methods in this module.
''' 
''' To use them simply call the method as this module has 
''' public scope in the project it resides or in a class project
''' another main project references.
''' </summary>
Public Module SpreadSheetLightHelpers
    ''' <summary>
    ''' Determine if a sheet exists in the specified excel file
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <returns>True if WorkSheet currently exists, false if not exists</returns>
    Public Function SheetExists(FileName As String, ByVal WorkSheetName As String) As Boolean
        Using doc As New SLDocument(FileName)
            Return doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = WorkSheetName.ToLower())
        End Using
    End Function
    ''' <summary>
    ''' Add a new sheet if it does not currently exists.
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <returns>True if WorkSheet added, False if not added</returns>
    Public Function AddNewSheet(FileName As String, WorkSheetName As String) As Boolean
        Using doc As New SLDocument(FileName)
            If Not (doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = WorkSheetName.ToLower())) Then
                doc.AddWorksheet(WorkSheetName)
                doc.Save()
                Return True
            Else
                Return False
            End If
        End Using
    End Function
    ''' <summary>
    ''' Remove a sheet if it exists.
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="WorkSheetName"></param>
    ''' <returns>True if WorkSheet remove</returns>
    ''' <remarks>
    ''' If there is only one WorkSheet it can not be removed, consider
    ''' renaming it instead.
    ''' </remarks>
    Public Function RemoveWorkSheet(FileName As String, WorkSheetName As String) As Boolean
        Using doc As New SLDocument(FileName)
            Dim workSheets = doc.GetSheetNames(False)
            If workSheets.Any(Function(sheetName) sheetName.ToLower() = WorkSheetName.ToLower()) Then
                If workSheets.Count > 1 Then
                    Dim sheet = doc.GetSheetNames().FirstOrDefault(Function(sName) sName.ToLower() <> WorkSheetName.ToLower())
                    doc.SelectWorksheet(doc.GetSheetNames().FirstOrDefault(Function(sName) sName.ToLower() <> WorkSheetName.ToLower()))
                ElseIf workSheets.Count = 1 Then
                    'Throw New Exception("Can not delete the sole worksheet")
                    Return False
                End If

                doc.DeleteWorksheet(WorkSheetName)
                doc.Save()

                Return True

            Else
                Return False
            End If
        End Using
    End Function
    ''' <summary>
    ''' Get sheet names in an Excel file
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <returns>List of WorkSheets in ordinal position they appear</returns>
    ''' <remarks>
    ''' Using some other libraries will sort A-Z
    ''' </remarks>
    Public Function SheetNames(FileName As String) As List(Of String)
        Using doc As New SLDocument(FileName)
            Return doc.GetSheetNames(False)
        End Using
    End Function

End Module