FindReplace.aspx.cs
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using ExpertXls.ExcelLib;
using ExcelLibraryDemos;
/// <summary>
/// Find and Replace: the workbook code is in App_Code\FindReplaceDemo.cs.
/// </summary>
public partial class FindReplacePage : Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void buttonCreate_Click(object sender, EventArgs e)
{
ExcelWorkbookFormat format = radioXlsx.Checked ? ExcelWorkbookFormat.Xlsx_2007 : ExcelWorkbookFormat.Xls_2003;
byte[] workbookBytes;
try
{
// create the workbook in memory; the input files are in the folders of the web site
using (MemoryStream stream = new MemoryStream())
{
FindReplaceDemo.CreateWorkbook(format, Server.MapPath("~"), stream);
workbookBytes = stream.ToArray();
}
}
catch (Exception ex)
{
labelError.Text = "The workbook could not be created: " + Server.HtmlEncode(ex.Message);
return;
}
WorkbookDownload.Send(Response, workbookBytes, FindReplaceDemo.OutputName + DemoLayout.GetExtension(format), format);
}
}
App_Code/FindReplaceDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class FindReplaceDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "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 static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Find and replace";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
// the style of the cells that were found
ExcelCellStyle foundStyle = workbook.Styles.AddStyle("FoundCellStyle");
foundStyle.Fill.FillType = ExcelCellFillType.SolidFill;
foundStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 235, 156);
foundStyle.Font.Bold = true;
ExcelWorksheet summary = 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;
ExcelWorksheet sales = workbook.Worksheets.AddWorksheet("Sales");
DemoLayout.SetupPage(sales, logo, ExcelPageOrientation.Landscape);
DemoLayout.WriteTitle(sales, "A2:G3", "Sales by Product", titleStyle);
DemoLayout.WriteSalesTable(sales, 6, 1);
ExcelWorksheet notes = 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
ExcelRange[] pdfCells = sales.Find("pdf");
foreach (ExcelRange cell in pdfCells)
cell.Style = foundStyle;
// find in the whole workbook, matching the case
ExcelRange[] excelCells = 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
int regions = workbook.Replace("North", "North America", ExcelFindOptions.MatchEntireCellContent);
// replace a text in all the worksheets, in any part of the cells
int products = 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);
int row = 12;
foreach (ExcelRange cell in excelCells)
{
summary[row, 1].Text = cell.Worksheet.Name + "!" + CellName(cell);
summary[row, 2].Text = cell.Text;
row++;
}
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
// the A1 name of a cell, for example C7
private static string CellName(ExcelRange cell)
{
string letters = "";
for (int column = cell.LeftColumnIndex; column > 0; column = (column - 1) / 26)
letters = (char)('A' + (column - 1) % 26) + letters;
return letters + cell.TopRowIndex;
}
}
}