使用vb.net创建包含多个工作表的excel文件

Question

这是我用来创建多个工作表的excel文件的代码。它创建成功了。然而，我希望能够与每个工作表的工作。我必须申报每一张表格吗?还是有别的办法?如果是,如何声明表吗?

Dim fileTest As String = "C:TempExcelTesttest.xlsx"
If File.Exists(fileTest) Then
File.Delete(fileTest)
End If
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
oBook = oExcel.Workbooks.Add
If oExcel.Application.Sheets.Count() < 1 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(1)
End If
oSheet.Name = "Requisition_Vendors"
oSheet.Range("A1").Value = "RQNHSEQ"
oSheet.Range("B1").Value = "VDCODE"
oSheet.Range("C1").Value = "CURRENCY"
oSheet.Range("D1").Value = "RATE"
oSheet.Range("E1").Value = "SPREAD"
oSheet.Range("F1").Value = "RATETYPE"
oSheet.Range("G1").Value = "RATEMATCH"
oSheet.Range("H1").Value = "RATEDATE"
oSheet.Range("I1").Value = "RATEOPER"
If oExcel.Application.Sheets.Count() < 2 Then
oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet = oExcel.Worksheets(2)
End If
oSheet.Name = "Requisition_Detail_Opt__Fields"
oSheet.Range("A1").Value = "RQNHSEQ"
oSheet.Range("B1").Value = "RQNLREV"
oSheet.Range("C1").Value = "OPTFIELD"
oSheet.Range("D1").Value = "VALUE"
oSheet.Range("E1").Value = "TYPE"
oSheet.Range("F1").Value = "LENGTH"
oSheet.Range("G1").Value = "DECIMALS"
oSheet.Range("H1").Value = "ALLOWNULL"
oSheet.Range("I1").Value = "VALIDATE"
oSheet.Range("J1").Value = "SWSET"
oSheet.Range("K1").Value = "VALINDEX"
oSheet.Range("L1").Value = "VALIFTEXT"
oSheet.Range("M1").Value = "VALIFMONEY"
oSheet.Range("N1").Value = "VALIFNUM"
oSheet.Range("O1").Value = "VALIFLONG"
oSheet.Range("P1").Value = "VALIFBOOL"
oSheet.Range("Q1").Value = "VALIFDATE"
oSheet.Range("R1").Value = "VALIFTIME"
oSheet.Range("S1").Value = "FDESC"
oSheet.Range("T1").Value = "VDESC"

Answer 1

是的，最好声明你的表单，因为它会使你的代码更容易阅读和使用。

Dim fileTest As String = "C:TempExcelTesttest.xlsx"
If File.Exists(fileTest) Then
File.Delete(fileTest)
End If
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
Dim oBook As Excel.Workbook
oBook = oExcel.Workbooks.Add
Dim oSheet1Name As String = "Requisition_Vendors"
Dim oSheet2Name As String = "Requisition_Detail_Opt__Fields"
Dim oSheet1 As Excel.Worksheet = oBook.Worksheets(oSheet1Name)
Dim oSheet2 As Excel.Worksheet = oBook.Worksheets(oSheet2Name)
If oExcel.Application.Sheets.Count() < 1 Then
oSheet1 = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet1 = oExcel.Worksheets(1)
End If
With oSheet1
.Name = oSheet1Name
.Range("A1").Value = "RQNHSEQ"
.Range("B1").Value = "VDCODE"
.Range("C1").Value = "CURRENCY"
.Range("D1").Value = "RATE"
.Range("E1").Value = "SPREAD"
.Range("F1").Value = "RATETYPE"
.Range("G1").Value = "RATEMATCH"
.Range("H1").Value = "RATEDATE"
.Range("I1").Value = "RATEOPER"
End With

If oExcel.Application.Sheets.Count() < 2 Then
oSheet2 = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
oSheet2 = oExcel.Worksheets(2)
End If
With oSheet2
.Name = oSheet2Name
.Range("A1").Value = "RQNHSEQ"
.Range("B1").Value = "RQNLREV"
.Range("C1").Value = "OPTFIELD"
.Range("D1").Value = "VALUE"
.Range("E1").Value = "TYPE"
.Range("F1").Value = "LENGTH"
.Range("G1").Value = "DECIMALS"
.Range("H1").Value = "ALLOWNULL"
.Range("I1").Value = "VALIDATE"
.Range("J1").Value = "SWSET"
.Range("K1").Value = "VALINDEX"
.Range("L1").Value = "VALIFTEXT"
.Range("M1").Value = "VALIFMONEY"
.Range("N1").Value = "VALIFNUM"
.Range("O1").Value = "VALIFLONG"
.Range("P1").Value = "VALIFBOOL"
.Range("Q1").Value = "VALIFDATE"
.Range("R1").Value = "VALIFTIME"
.Range("S1").Value = "FDESC"
.Range("T1").Value = "VDESC"
End With