EditWorkbook.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Edit a Workbook: the workbook code is in App_Code\EditWorkbookDemo.vb.
''' </summary>
Partial Class EditWorkbookPage
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()
EditWorkbookDemo.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, EditWorkbookDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/EditWorkbookDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Opens an existing workbook (TestDocs\GettingStarted.xls or .xlsx), renames its worksheets, writes new cells,
''' inserts a row, adds a chart and saves the result as a new workbook.
''' </summary>
Public Module EditWorkbookDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "EditedWorkbook"
''' <summary>
''' Edits the test document of the given format and saves the result to the output stream.
''' </summary>
''' <param name="format">The format of the document to edit, which is also the format of the result.</param>
''' <param name="inputFolder">The folder that contains the TestDocs 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 testDocument As String = Path.Combine(inputFolder, "TestDocs",
"GettingStarted" & DemoLayout.GetExtension(format))
' the format of an opened workbook is the format of its file
Dim workbook As New ExcelWorkbook(testDocument)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Edited workbook"
workbook.DocumentProperties.Comments = "Edited with ExpertXls Excel Library for .NET"
Try
Dim firstWorksheet As ExcelWorksheet = workbook.Worksheets(0)
Dim secondWorksheet As ExcelWorksheet = workbook.Worksheets(1)
firstWorksheet.Name = "Edited " & firstWorksheet.Name
secondWorksheet.Name = "Edited " & secondWorksheet.Name
' new named styles can be added to an existing workbook
Dim labelStyle As ExcelCellStyle = workbook.Styles.AddStyle("EditLabelStyle")
labelStyle.Font.Size = 12
labelStyle.Font.Bold = True
labelStyle.Font.Color = Color.Green
labelStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
Dim dateStyle As ExcelCellStyle = workbook.Styles.AddStyle("EditDateStyle")
dateStyle.Font.Size = 12
dateStyle.Font.Bold = True
dateStyle.Font.Color = Color.DarkBlue
dateStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right
dateStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
dateStyle.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss"
For Each worksheet As ExcelWorksheet In New ExcelWorksheet() {firstWorksheet, secondWorksheet}
' insert a row at the top: the existing cells, merged ranges, pictures and charts move down
worksheet.InsertRow(1)
worksheet("A1:D1").Merge()
worksheet("A1").Text = "Edited on:"
worksheet("A1:D1").Style = labelStyle
worksheet("A1:D1").RowHeightInPoints = 30
worksheet("E1:G1").Merge()
worksheet("E1").Value = DateTime.Now
worksheet("E1:G1").Style = dateStyle
Next
' read a value of the opened document: the title of the second worksheet, now in row 3
Dim chartSheetTitle As String = secondWorksheet("A3").Text
' add a chart to the existing data of the second worksheet (B8:G13 after the inserted row)
secondWorksheet("A15:G15").Merge()
secondWorksheet("A15").Text = "The chart below was added to the existing worksheet """ & chartSheetTitle & """"
secondWorksheet("A15:G15").Style = labelStyle
secondWorksheet("A15:G15").RowHeightInPoints = 30
Dim chart As ExcelChart = secondWorksheet.Charts.AddChart(ExcelChartType.LineMarkers, secondWorksheet("B8:G12"),
False, 2, 16, 8, 33)
chart.Title.Text = "Units Sold per Product (added chart)"
DemoLayout.FormatChart(chart, workbook.Format, "Units sold", "Quarters")
workbook.Save(output)
Finally
workbook.Close()
End Try
End Sub
End Module