Excel开发学习笔记：查找与创建worksheet

开发环境基于VSTO，具体配置：visual studio 2010，VB .Net，excel 2007，文档级别的定制程序。

如题，我在ThisWorkbook.vb中添加了一个public函数来完成查找功能。

入参：待查找的sheet名称

返回：如果存在则返回worksheet对象，如果不存在则返回nothing

    Public Function WorksheetExist(name As String) As Excel.Worksheet  
        Try  
            Dim existSheet As Excel.Worksheet = Globals.ThisWorkbook.Sheets(name)  
            Return existSheet  
        Catch ex As Exception  
            Return Nothing  
        End Try  
      
    End Function 

 

 

创建worksheet函数，要求在现有sheet的尾部添加新的sheet。

入参：Workbook，新sheet名

返回：成功则返回新的sheet对象，失败返回nothing

    Private Function CreatWorksheet(ByRef book As Excel.Workbook, ByRef name As String) As Excel.Worksheet  
        Dim newSheet As Excel.Worksheet = book.Sheets.Add(After:=book.Worksheets(book.Sheets.Count))  
        Try  
      
            newSheet.Name = name  
            Return newSheet  
        Catch ex As Exception  
            MsgBox("""" + name + """" + "不能被用作excel工作表(sheet)名称")  
            newSheet.Delete()  
            Return Nothing  
        End Try  
      
    End Function 

 

 查询与创建连起来使用，未查找到则创建的代码片段：

    Dim algoSheet As Excel.Worksheet = Globals.ThisWorkbook.WorksheetExist(name)  
    If algoSheet Is Nothing Then  
        algoSheet = CreatWorksheet(Globals.ThisWorkbook.Application.Workbooks(Globals.ThisWorkbook.Name), name)  
        If (algoSheet Is Nothing) Then  
            Continue For  
        End If  
    End If