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