RangesAndCells.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Cells and Ranges: the workbook code is in App_Code\RangesAndCellsDemo.vb.
''' </summary>
Partial Class RangesAndCellsPage
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()
RangesAndCellsDemo.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, RangesAndCellsDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/RangesAndCellsDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Shows the ways to address cells and ranges: the A1 notation, one based row and column indexes, named ranges
''' defined at worksheet and at workbook level, and the used range of a worksheet.
''' </summary>
Public Module RangesAndCellsDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "RangesAndCells"
''' <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 = "Access cells and ranges"
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)
' the style of the cells accessed by the demo
Dim accessedStyle As ExcelCellStyle = workbook.Styles.AddStyle("AccessedRangeStyle")
accessedStyle.Font.Bold = True
accessedStyle.Fill.FillType = ExcelCellFillType.SolidFill
accessedStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204)
accessedStyle.Borders.LineStyle = ExcelCellLineStyle.Thin
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Ranges and Cells"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait)
DemoLayout.WriteTitle(worksheet, "A2:G3", "Accessing Cells and Ranges of Cells", titleStyle)
' setting the width of a range sets the width of all its columns
worksheet("A1:G1").ColumnWidthInChars = 14
' a single cell in the A1 notation
DemoLayout.WriteSection(worksheet, "A5:G5", "A single cell in the A1 notation: worksheet(""A7"")", sectionStyle)
worksheet("A7").Value = "worksheet(""A7"")"
worksheet("A7").Style = accessedStyle
' a range in the A1 notation: setting a value sets every cell of the range
DemoLayout.WriteSection(worksheet, "A9:G9", "A range in the A1 notation: worksheet(""A11:G12"")", sectionStyle)
worksheet("A11:G12").Value = "A11:G12"
worksheet("A11:G12").Style = accessedStyle
' a single cell by its one based row and column index
DemoLayout.WriteSection(worksheet, "A14:G14", "A single cell by row and column index: worksheet(16, 1)", sectionStyle)
worksheet(16, 1).Value = "worksheet(16, 1)"
worksheet(16, 1).Style = accessedStyle
' a range by its top row, left column, bottom row and right column indexes
DemoLayout.WriteSection(worksheet, "A18:G18", "A range by row and column indexes: worksheet(20, 1, 21, 7)", sectionStyle)
worksheet(20, 1, 21, 7).Value = "20, 1, 21, 7"
worksheet(20, 1, 21, 7).Style = accessedStyle
' named ranges: a name defined in a worksheet is visible only in that worksheet, a name defined in the
' workbook is visible in all the worksheets
DemoLayout.WriteSection(worksheet, "A23:G23", "Named ranges defined at worksheet and at workbook level", sectionStyle)
worksheet.NamedRanges.AddNamedRange(worksheet("A25:C25"), "WorksheetName")
worksheet("WorksheetName").Merge()
worksheet("A25").Value = "Named range defined in the worksheet"
worksheet("WorksheetName").Style.Font.Color = Color.Green
worksheet("WorksheetName").Style.Font.Bold = True
workbook.NamedRanges.AddNamedRange(worksheet("A26:C26"), "WorkbookName")
worksheet("WorkbookName").Merge()
worksheet("A26").Value = "Named range defined in the workbook"
worksheet("WorkbookName").Style.Font.Color = Color.Blue
worksheet("WorkbookName").Style.Font.Bold = True
' the used range spans all the cells that have a value or a style
DemoLayout.WriteSection(worksheet, "A28:G28", "The used range of the worksheet", sectionStyle)
worksheet(30, 1).Value = "Used range: " & worksheet.UsedRange.Address
worksheet(30, 1).Style.Font.Color = Color.Green
worksheet(30, 1).Style.Font.Bold = True
' the cells of a range can be enumerated, for example to read the values back
Dim nonEmptyCells As Integer = 0
For Each cell As ExcelRange In worksheet("A11:G12").Cells
If Not cell.IsEmpty Then nonEmptyCells += 1
Next
worksheet(31, 1).Value = String.Format("The range A11:G12 has {0} cells with values", nonEmptyCells)
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module