Tables.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Excel Tables: the workbook code is in App_Code\TablesDemo.vb.
''' </summary>
Partial Class TablesPage
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)
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()
TablesDemo.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, TablesDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/TablesDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Turns a sales table into an Excel table: a built-in table style with banded rows, filter buttons in the header
''' row and a totals row with a sum and an average calculated by Excel. Excel tables are available in the Excel
''' 2007+ format; the Excel 97-2003 workbook gets the same data with an AutoFilter instead.
''' </summary>
Public Module TablesDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "Tables"
''' <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 Images folder 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)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Excel tables"
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 sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Excel Table"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(worksheet, "A2:G3", "Creating an Excel Table", titleStyle)
Dim salesRange As ExcelRange = DemoLayout.WriteSalesTable(worksheet, 6, 1)
If format = ExcelWorkbookFormat.Xlsx_2007 Then
DemoLayout.WriteSection(worksheet, "A5:G5", "An Excel table with the Medium 9 style and a totals row. Its name, Sales, can be used in formulas.", sectionStyle)
' the first row of the range is the header row: its cells give the names of the table columns
Dim table As ExcelTable = worksheet.Tables.Add("Sales", salesRange)
table.Style = ExcelTableStyle.Medium9
table.ShowBandedRows = True
table.ShowFirstColumn = True
' the totals row is added below the data; Excel calculates the SUBTOTAL formulas
table.ShowTotalsRow = True
table.Columns("Product").TotalsRowLabel = "Total"
table.Columns("Region").TotalsCalculation = ExcelTableTotalsCalculation.Count
For i As Integer = 2 To table.Columns.Count - 2
table.Columns(i).TotalsCalculation = ExcelTableTotalsCalculation.Sum
Next
table.Columns("Total").TotalsCalculation = ExcelTableTotalsCalculation.Average
' a formula with a structured reference to the table
worksheet("A22").Text = "Largest total:"
worksheet("A22").Style.Font.Bold = True
worksheet("B22").Formula = "=MAX(Sales[Total])"
worksheet("B22").Style.Number.NumberFormatString = "#,##0"
Else
DemoLayout.WriteSection(worksheet, "A5:G5", "Excel tables need the Excel 2007+ (.xlsx) format. This workbook has an AutoFilter instead.", sectionStyle)
salesRange.AddAutoFilter()
End If
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module