ExpertXls Excel Library Blazor demo · .NET 10 and .NET 11

← All demos

C# code of the RangesAndCells demo

Features/RangesAndCellsDemo.cs
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.