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
App_Code/LoadGetDataTableDemo.vb
Imports System.Data
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Moves data between worksheets and .NET DataTable objects: reads the used range of an existing workbook into a
''' DataTable (the column names come from the first row) and loads a DataTable into a new worksheet.
''' </summary>
Public Module LoadGetDataTableDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "LoadGetDataTable"
''' <summary>
''' Reads the employees table of Data\awemployees.xls into a DataTable.
''' </summary>
''' <param name="inputFolder">The folder that contains the Data folder of the sample.</param>
Public Function GetEmployees(inputFolder As String) As DataTable
' a workbook can be opened from a file or from a stream
Using stream As New FileStream(Path.Combine(inputFolder, "Data", "awemployees.xls"), FileMode.Open, FileAccess.Read)
Dim sourceWorkbook As New ExcelWorkbook(stream)
Try
Dim sourceWorksheet As ExcelWorksheet = sourceWorkbook.Worksheets(0)
' True: the first row of the range gives the names of the DataTable columns
Return sourceWorksheet.GetDataTable(sourceWorksheet.UsedRange, True)
Finally
sourceWorkbook.Close()
End Try
End Using
End Function
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Data and Images folders of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
' 1. GET: an existing worksheet to a DataTable
Dim employees As DataTable = GetEmployees(inputFolder)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Load data from a DataTable and get data to a DataTable"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Employees"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(worksheet, "A2:N3", "Data Loaded from a .NET DataTable", titleStyle)
' 2. LOAD: the DataTable to the worksheet, starting with row 5, column 1; True writes the column
' names in the first row
worksheet.LoadDataTable(employees, 5, 1, True)
' style the header row, keep it visible when scrolling and fit the columns to the data
Dim headerRange As ExcelRange = worksheet(5, 1, 5, employees.Columns.Count)
headerRange.RowHeightInPoints = 20
headerRange.Style.Font.Bold = True
headerRange.Style.Fill.FillType = ExcelCellFillType.SolidFill
headerRange.Style.Fill.SolidFillOptions.BackColor = Color.LightBlue
headerRange.Style.Borders.LineStyle = ExcelCellLineStyle.Thin
worksheet("A6").FreezePanes()
worksheet.AutofitColumns()
' a summary below the data
Dim summaryRow As Integer = 5 + employees.Rows.Count + 2
worksheet(summaryRow, 1).Value = String.Format("{0} rows and {1} columns loaded from the DataTable",
employees.Rows.Count, employees.Columns.Count)
worksheet(summaryRow, 1).Style.Font.Bold = True
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module