ExpertXls Excel Library ASP.NET Web Forms demo · VB.NET

DataTable Import and Export

Reads the worksheet of Data\awemployees.xls into a .NET DataTable (shown below) and loads the DataTable into a new workbook.

Select the format and press the button: the workbook is created on the server and downloaded by the browser.

Workbook format   

The DataTable read from Data\awemployees.xls (first 15 rows)

EmployeeIDNationalIDNumberContactIDLoginIDManagerIDTitleBirthDateMaritalStatusGenderHireDateSalariedFlagVacationHoursSickLeaveHoursModifiedDate
1144178071209adventure-works\guy116Production Technician - WC605/15/1972MM7/31/1996021307/31/2004
22530228761030adventure-works\kevin06Marketing Assistant6/3/1977SM2/26/1997042417/31/2004
35096471741002adventure-works\roberto012Engineering Manager12/13/1964MM12/12/1997-12217/31/2004
41124578911290adventure-works\rob03Senior Tool Designer1/23/1965SM1/5/1998048807/31/2004
54801685281009adventure-works\thierry0263Tool Designer8/29/1949MM1/11/199809247/31/2004
6247566241028adventure-works\david0109Marketing Manager4/19/1965SM1/20/1998-140407/31/2004
73097387521070adventure-works\jolynn021Production Supervisor - WC602/16/1946SF1/26/1998082617/31/2004
86906278181071adventure-works\ruth0185Production Technician - WC107/6/1946MF2/6/1998083617/31/2004
96952569081005adventure-works\gail03Design Engineer10/29/1942MF2/6/1998-15227/31/2004
109122658251076adventure-works\barry0185Production Technician - WC104/27/1946SM2/7/1998088647/31/2004
119983206921006adventure-works\jossef03Design Engineer4/11/1949MM2/24/1998-16237/31/2004
122457979671001adventure-works\terri0109Vice President of Engineering9/1/1961SF3/3/1998-11207/31/2004
138449736251072adventure-works\sidney0185Production Technician - WC1010/1/1946MM3/5/1998084627/31/2004
142330693021067adventure-works\taylor021Production Supervisor - WC505/3/1946MM3/11/1998079597/31/2004
151326748231073adventure-works\jeffrey0185Production Technician - WC108/12/1946SM3/23/1998085627/31/2004

Create, edit and read Excel workbooks in ASP.NET: the workbooks are created on the server and sent to the browser. The code of each workbook is in App_Code.

VB.NET code of this sample

LoadGetDataTable.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib

''' <summary>
''' DataTable Import and Export: the workbook code is in App_Code\LoadGetDataTableDemo.vb.
''' </summary>
Partial Class LoadGetDataTablePage
    Inherits Page

    ' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            ' GetDataTable reads a worksheet range into a DataTable that any data control can show
            Dim employees As DataTable = LoadGetDataTableDemo.GetEmployees(Server.MapPath("~"))
            Dim firstRows As DataTable = employees.Clone()
            For i As Integer = 0 To Math.Min(15, employees.Rows.Count) - 1
                firstRows.ImportRow(employees.Rows(i))
            Next
            gridEmployees.DataSource = firstRows
            gridEmployees.DataBind()
        End If
    End Sub

    Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
        Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
        Dim workbookBytes As Byte()
        Try
            ' create the workbook in memory; the input files are in the folders of the web site
            Using stream As New MemoryStream()
                LoadGetDataTableDemo.CreateWorkbook(format, Server.MapPath("~"), stream)
                workbookBytes = stream.ToArray()
            End Using
        Catch ex As Exception
            labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
            Return
        End Try
        WorkbookDownload.Send(Response, workbookBytes, LoadGetDataTableDemo.OutputName & DemoLayout.GetExtension(format), format)
    End Sub

End Class