C# code of the RangesAndCells demo
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class RangesAndCellsDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "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 static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = 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";
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 accessed by the demo
ExcelCellStyle accessedStyle = 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;
ExcelWorksheet worksheet = 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
int nonEmptyCells = 0;
foreach (ExcelRange cell in worksheet["A11:G12"].Cells)
{
if (!cell.IsEmpty)
nonEmptyCells++;
}
worksheet[31, 1].Value = String.Format("The range A11:G12 has {0} cells with values", nonEmptyCells);
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
}
}
Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.