FindReplace.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Find and Replace: the workbook code is in App_Code\FindReplaceDemo.vb.
''' </summary>
Partial Class FindReplacePage
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()
FindReplaceDemo.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, FindReplaceDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/FindReplaceDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Finds and replaces text in a workbook: the cells that contain a text are found in a worksheet and in the whole
''' workbook and highlighted, a region name is replaced only where it is the entire cell content and a product
''' name is replaced in all the worksheets. A summary worksheet lists what was found and replaced.
''' </summary>
Public Module FindReplaceDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "FindReplace"
''' <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 = "Find and replace"
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 that were found
Dim foundStyle As ExcelCellStyle = workbook.Styles.AddStyle("FoundCellStyle")
foundStyle.Fill.FillType = ExcelCellFillType.SolidFill
foundStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 235, 156)
foundStyle.Font.Bold = True
Dim summary As ExcelWorksheet = workbook.Worksheets(0)
summary.Name = "Summary"
DemoLayout.SetupPage(summary, logo, ExcelPageOrientation.Portrait)
DemoLayout.WriteTitle(summary, "A2:G3", "Find and Replace", titleStyle)
summary("A1").ColumnWidthInChars = 40
summary("B1").ColumnWidthInChars = 50
Dim sales As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Sales")
DemoLayout.SetupPage(sales, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(sales, "A2:G3", "Sales by Product", titleStyle)
DemoLayout.WriteSalesTable(sales, 6, 1)
Dim notes As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Notes")
notes("A1").Text = "The PDF products are sold in all the regions."
notes("A2").Text = "The Excel Library creates .xls and .xlsx workbooks."
notes("A3").Text = "Contact the North office for volume licenses."
notes("A1").ColumnWidthInChars = 60
' find the cells of a worksheet that contain a text, in any part of the cell and without regard to case
Dim pdfCells As ExcelRange() = sales.Find("pdf")
For Each cell As ExcelRange In pdfCells
cell.Style = foundStyle
Next
' find in the whole workbook, matching the case
Dim excelCells As ExcelRange() = workbook.Find("Excel", ExcelFindOptions.MatchCase)
' replace a region name only where it is the entire content of the cell: the note that mentions
' "the North office" is not changed
Dim regions As Integer = workbook.Replace("North", "North America", ExcelFindOptions.MatchEntireCellContent)
' replace a text in all the worksheets, in any part of the cells
Dim products As Integer = workbook.Replace("Image Extractor", "PDF Image Extractor")
DemoLayout.WriteSection(summary, "A5:B5", "Results", sectionStyle)
summary("A6").Text = "Cells of the Sales worksheet that contain ""pdf"":"
summary("B6").Value = pdfCells.Length
summary("A7").Text = "Cells of the workbook that contain ""Excel"" (match case):"
summary("B7").Value = excelCells.Length
summary("A8").Text = "Cells where ""North"" was replaced by ""North America"":"
summary("B8").Value = regions
summary("A9").Text = "Cells where ""Image Extractor"" was replaced:"
summary("B9").Value = products
summary("B6:B9").Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left
DemoLayout.WriteSection(summary, "A11:B11", "The cells that contain ""Excel""", sectionStyle)
Dim row As Integer = 12
For Each cell As ExcelRange In excelCells
summary(row, 1).Text = cell.Worksheet.Name & "!" & CellName(cell)
summary(row, 2).Text = cell.Text
row += 1
Next
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
' the A1 name of a cell, for example C7
Private Function CellName(cell As ExcelRange) As String
Dim letters As String = ""
Dim column As Integer = cell.LeftColumnIndex
While column > 0
letters = ChrW(AscW("A"c) + (column - 1) Mod 26) & letters
column = (column - 1) \ 26
End While
Return letters & cell.TopRowIndex.ToString()
End Function
End Module