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

← All demos

C# code of the LoadGetDataTable demo

Features/LoadGetDataTableDemo.cs
using System;
using System.Data;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;

namespace ExcelLibraryDemos
{
    /// <summary>
    /// Moves data between worksheets and .NET DataTable objects: reads the used range of an existing workbook into a
    /// DataTable (the column names come from the first row) and loads a DataTable into a new worksheet.
    /// </summary>
    public static class LoadGetDataTableDemo
    {
        /// <summary>The name of the generated file, without extension.</summary>
        public const string OutputName = "LoadGetDataTable";

        /// <summary>
        /// Reads the employees table of Data\awemployees.xls into a DataTable.
        /// </summary>
        /// <param name="inputFolder">The folder that contains the Data folder of the sample.</param>
        public static DataTable GetEmployees(string inputFolder)
        {
            // a workbook can be opened from a file or from a stream
            using (FileStream stream = new FileStream(Path.Combine(inputFolder, "Data", "awemployees.xls"), FileMode.Open, FileAccess.Read))
            {
                ExcelWorkbook sourceWorkbook = new ExcelWorkbook(stream);
                try
                {
                    ExcelWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];
                    // true: the first row of the range gives the names of the DataTable columns
                    return sourceWorksheet.GetDataTable(sourceWorksheet.UsedRange, true);
                }
                finally
                {
                    sourceWorkbook.Close();
                }
            }
        }

        /// <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 Data and Images folders of the sample.</param>
        /// <param name="output">The stream that receives the workbook.</param>
        public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
        {
            // 1. GET: an existing worksheet to a DataTable
            DataTable employees = GetEmployees(inputFolder);

            ExcelWorkbook workbook = new ExcelWorkbook(format);
            // workbook.LicenseKey = "your license key";
            workbook.DocumentProperties.Subject = "Load data from a DataTable and get data to a DataTable";
            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);

                ExcelWorksheet worksheet = workbook.Worksheets[0];
                worksheet.Name = "Employees";
                DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape);
                DemoLayout.WriteTitle(worksheet, "A2:N3", "Data Loaded from a .NET DataTable", titleStyle);

                // 2. LOAD: the DataTable to the worksheet, starting with row 5, column 1; true writes the column
                // names in the first row
                worksheet.LoadDataTable(employees, 5, 1, true);

                // style the header row, keep it visible when scrolling and fit the columns to the data
                ExcelRange headerRange = worksheet[5, 1, 5, employees.Columns.Count];
                headerRange.RowHeightInPoints = 20;
                headerRange.Style.Font.Bold = true;
                headerRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
                headerRange.Style.Fill.SolidFillOptions.BackColor = Color.LightBlue;
                headerRange.Style.Borders.LineStyle = ExcelCellLineStyle.Thin;
                worksheet["A6"].FreezePanes();
                worksheet.AutofitColumns();

                // a summary below the data
                int summaryRow = 5 + employees.Rows.Count + 2;
                worksheet[summaryRow, 1].Value = String.Format("{0} rows and {1} columns loaded from the DataTable",
                    employees.Rows.Count, employees.Columns.Count);
                worksheet[summaryRow, 1].Style.Font.Bold = true;

                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.