Create excel file with multiple sheets using vb.net

Question

This is the code that I used to create excel file with multiple sheets. Its created successfully. However, I want to be able to work with each worksheets. Do I have to declare every sheet or there is another way to do it. If yes, how to declare the sheets?

Dim fileTest As String = "C:\Temp\ExcelTest\test.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"

Not sure what you mean by Declare Every Sheet. Why not just use the oExcel.Worksheets() collection as you currently are? — Hursey
– Hursey, Commented Jul 5, 2022 at 4:20

Patrick · Accepted Answer · 2022-07-05 04:26:42Z

1

Yes it's much better to declare your sheets as it'll make your code easier to read and work with.

    Dim fileTest As String = "C:\Temp\ExcelTest\test.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

answered Jul 5, 2022 at 4:26

Patrick

2323 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jiaxinxin25 Over a year ago

Thank you for your suggestion, the whole project looks better now but my save file code has a error now. I had edited my question, would you mind to look at it.

Cal-cium Over a year ago

@Jiaxinxin25 if you have a new problem, please ask a new question rather than editing this question, as you already have accepted an answer for it

T. Zack Crawford · Accepted Answer · 2024-02-20 17:26:26Z

My team managed to do this by creating a shared function (CreateWorksheet) which appends worksheets to the Excel file by referencing the initial count of current worksheets.

In site.master.vb:

Public Function CreateWorksheet(ByVal Exclpackage As OfficeOpenXml.ExcelPackage, ByVal sheetName As String) As OfficeOpenXml.ExcelWorksheet
    Dim currentWorksheetCount As Integer = Exclpackage.Workbook.Worksheets.Count
    Exclpackage.Workbook.Worksheets.Add(sheetName)
    Dim ws As OfficeOpenXml.ExcelWorksheet = Exclpackage.Workbook.Worksheets(currentWorksheetCount+1)
    ws.Name = sheetName
    ...
    Return ws
End Function

Public Function PopulateWorksheet(ByRef myWorksheet As OfficeOpenXml.ExcelWorksheet, ByVal myDataTable As Data.DataTable, ByVal cellNum As String )
    myWorksheet.Cells(cellNum).LoadFromDataTable(myDataTable, True)
    myWorksheet.Cells.AutoFitColumns()
End Function

Public Function SaveExcelPkgToDisk(ByVal myWorkbook As OfficeOpenXml.ExcelPackage, ByVal filePath As String)
    If File.Exists(filePath) Then
        File.Delete(filePath)
    End If
    Dim myFileStream As FileStream = File.Create(filePath)
    myFileStream.Close()
    File.WriteAllBytes(filePath, myWorkbook.GetAsByteArray())
End Function

Public Function DownloadFile(ByVal fileName As String, ByVal filePath As String, ByVal fileType As String)
    Response.Clear()
    Response.ContentType = fileType
    Response.AddHeader("Content-Disposition", "attachment; filename=" & fileName)
    Response.WriteFile(filePath)
    Response.Flush()
    Response.End()
End Function

In my web app:

Dim newFile As FileInfo = New FileInfo(myFileName & ".xlsx")
Dim mypackage As OfficeOpenXml.ExcelPackage = New OfficeOpenXml.ExcelPackage(newFile)
Dim ws1 As OfficeOpenXml.ExcelWorksheet = Master.CreateWorksheet(mypackage, mySheetName1)
Dim dt As DataTable = mySQLDataSource.Select(DataSourceSelectArguments.Empty).Table
Master.PopulateWorksheet(ws1, dt, "A1")
Dim ws2 As OfficeOpenXml.ExcelWorksheet = Master.CreateWorksheet(mypackage, mySheetName2)
...
Master.SaveExcelPkgToDisk(mypackage, myFilePath)
Master.DownloadFile(myFileName & ".xlsx", myFilePath, "application/xlsx")

Collectives™ on Stack Overflow

Create excel file with multiple sheets using vb.net

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related