Blazor Using Workbooks

El motor de Excel Infragistics Blazor le permite guardar datos y cargarlos desde Microsoft® Excel®. Puede crear libros y hojas de cálculo, ingresar datos y exportar los datos a Excel utilizando las diversas clases de la biblioteca. El motor de Excel Infragistics Blazor facilita la exportación de los datos de su aplicación como una hoja de cálculo de Excel, así como la importación de datos de Excel a su aplicación.

Blazor Using Workbooks Example

EXAMPLE

BlazorFastDownload.cs

MODULES

SharedExcelData.cs

RAZOR

JS

CSS

using System.Runtime.InteropServices.JavaScript;

namespace Infragistics.Samples
{
    public partial class BlazorFastDownload
    {
        [JSImport("BlazorDownloadFileFast", "BlazorFastDownload")]
        internal static partial void DownloadFile(string name, string contentType, byte[] content);

    }
}csコピー

using System;
using System.Net.Http;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Text;
using Microsoft.AspNetCore.Components.WebAssembly.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using IgniteUI.Blazor.Controls;

namespace Infragistics.Samples
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            var builder = WebAssemblyHostBuilder.CreateDefault(args);
            builder.RootComponents.Add<App>("app");
            builder.Services.AddScoped(sp => new HttpClient { BaseAddress = new Uri(builder.HostEnvironment.BaseAddress) });
            // registering Ignite UI modules
            builder.Services.AddIgniteUIBlazor(
                typeof(IgbDataGridModule)
            );
            await builder.Build().RunAsync();
        }
    }
}csコピー

using System;
using System.Collections.Generic;

namespace Infragistics.Samples
{
    public class SalesEmployee
    {
        public double ID { get; set; }
        public string ContactName { get; set; }
        public string CompanyName { get; set; }
        public string ContactTitle { get; set; }
        public int Age { get; set; }
        public string Country { get; set; }
        public string City { get; set; }
        public double Salary { get; set; }
        public string Fax { get; set; }
        public string Phone { get; set; }
        public string Address { get; set; }
        public string PostalCode { get; set; }
        public string Region { get; set; }
        public List<Order> Order { get; set; }
    }

    public class Expense
    {
        public int Year { get; set; }
        public double ComputerExpense { get; set; }
        public double ResearchExpense { get; set; }
        public double TravelExpense { get; set; }
        public double SalaryExpense { get; set; }
        public double SoftwareExpense { get; set; }
    }

    public class Income
    {
        public int Year { get; set; }
        public double PhoneIncome { get; set; }
        public double ComputerIncome { get; set; }
        public double SoftwareIncome { get; set; }
        public double ServiceIncome { get; set; }
        public double RoyaltyIncome { get; set; }
    }

    public class Order : SalesEmployee
    {
        public string CustomerName { get; set; }
        public string CustomerID { get; set; }
        public double Freight { get; set; }
        public string OrderDate { get; set; }
        public double OrderID { get; set; }
        public string RequiredDate { get; set; }
        public string ShipAddress { get; set; }
        public string ShipCity { get; set; }
        public string ShipCountry { get; set; }
        public string ShipName { get; set; }
        public string ShipPostalCode { get; set; }
        public string ShipRegion { get; set; }
        public double ShipVia { get; set; }
        public string ShippedDate { get; set; }
        public double ShipperID { get; set; }
        public string ShipperName { get; set; }
        public double TotalItems { get; set; }
        public double TotalPrice { get; set; }
    }
}csコピー

@using Microsoft.AspNetCore.Components
@using Microsoft.AspNetCore.Components.Rendering
@using Microsoft.AspNetCore.Components.Forms
@using Microsoft.AspNetCore.Components.RenderTree
@using Microsoft.AspNetCore.Components.Web
@using System.Text.RegularExpressions
@using System.Net.Http
@using System.Net.Http.Json
@using Microsoft.AspNetCore.Components.Routing
@using Microsoft.AspNetCore.Components.WebAssembly.Http
@using Microsoft.JSInterop
@using Microsoft.JSInterop.WebAssembly
@using Infragistics.Documents.Excel
@using IgniteUI.Blazor.Controls
@using System.Runtime.InteropServices.JavaScript

@implements IDisposable

<div class="container vertical">

    <div class="options vertical">
        <button @onclick="GenerateData">Generate Data</button>
        <button @onclick="CreateXlsx">Save Workbook to XLSX</button>
        <button @onclick="CreateXls">Save Workbook to XLS</button>
        <span>Select Table to Export:</span>
        <select @onchange="OnTableChange">
            <option>Sales Employee - Table1</option>
            <option>Expense - Table2</option>
            <option>Income - Table3</option>
        </select>
    </div>

    <div class="container vertical">

        @if (Data != null)
        {
            <IgbDataGrid @ref="@grid" Height="100%" Width="100%"
            DataSource="Data"
            AutoGenerateColumns="true">
            </IgbDataGrid>
        }

    </div>
</div>

@code {

    [Inject]
    public IJSRuntime Runtime { get; set; }
    public bool canSave = false;
    public Random Rand = new Random();

    public IgbDataGrid grid;
    public object Data;
    public Workbook wb;

    public List<SalesEmployee> salesEmployeeData;
    public List<Expense> expenseData;
    public List<Income> incomeData;

    public string[] companies;
    public string[] firstNames;
    public string[] lastNames;
    public string[] countries;
    public string[] cities;
    public string[] titles;
    public string[] employeeColumns;
    public string[] streets;
    public string selected = "Employees - Table1";

    protected override void OnInitialized()
    {

        Workbook.InProcessRuntime = this.Runtime as IJSInProcessRuntime;

        this.companies = new string[] { "Amazon", "Ford", "Jaguar", "Tesla", "IBM", "Microsoft" };
        this.firstNames = new string[] { "Andrew", "Mike", "Martin", "Ann", "Victoria", "John", "Brian", "Jason", "David" };
        this.lastNames = new string[] { "Smith", "Jordan", "Johnson", "Anderson", "Louis", "Phillips", "Williams" };
        this.countries = new string[] { "UK", "France", "USA", "Germany", "Poland", "Brazil" };
        this.cities = new string[] { "London", "Paris", "Boston", "Berlin" };
        this.titles = new string[] { "Sales Rep.", "Engineer", "Administrator", "Manager" };
        this.employeeColumns = new string[] { "Name", "Company", "Title", "Age", "Country" };
        this.streets = new string[] { "Main St", "Madison St", "Broad Way" };

        GenerateData();
        this.Data = this.salesEmployeeData;
    }

    public void GenerateData() {
        this.InitData();
        this.SwitchDataSource(this.selected);
    }

    public void InitData()
    {
        this.salesEmployeeData = new List<SalesEmployee>();
        this.expenseData = new List<Expense>();
        this.incomeData = new List<Income>();

        var startYear = 2011;

        for (var i = 1; i < 20; i++)
        {
            var year = startYear + i;

            // Employee Data
            string company = companies[Rand.Next(0, companies.Length)];
            string title = titles[Rand.Next(0, titles.Length)];
            string country = countries[Rand.Next(0, countries.Length)];
            string name = firstNames[Rand.Next(0, firstNames.Length)] + " " + firstNames[Rand.Next(0, firstNames.Length)];
            double salary = this.GetRandom(45000, 95000);
            double age = this.GetRandom(20, 65);
            string city = cities[Rand.Next(0, cities.Length)];
            string address = this.GetRandom(10, 60).ToString() + " " + streets[Rand.Next(0, streets.Length)];
            string postalCode = "CID-" + this.GetRandom(500, 900);
            string phone = this.GetRandom(500, 900) + "-" + this.GetRandom(200, 900) + "-" + this.GetRandom(2000, 9000);
            string fax = this.GetRandom(500, 900) + "-" + this.GetRandom(200, 900) + "-" + this.GetRandom(2000, 9000);

            // Expense Data
            double computerExpense = this.GetRandom(50000, 60000);
            double researchExpense = this.GetRandom(120000, 160000);
            double travelExpense = this.GetRandom(15000, 25000);
            double salaryExpense = this.GetRandom(1000000, 2000000);
            double softwareExpense = this.GetRandom(100000, 150000);

            // Income Data
            double phoneIncome = this.GetRandom(3500000, 6000000);
            double computerIncome = this.GetRandom(200000, 300000);
            double softwareIncome = this.GetRandom(700000, 800000);
            double serviceIncome = this.GetRandom(650000, 750000);
            double royaltyIncome = this.GetRandom(400000, 450000);

            this.salesEmployeeData.Add(new SalesEmployee()
            {
                ContactName = name,
                CompanyName = company,
                ID = this.GetRandom(1000, 8000),
                ContactTitle = title,
                Age = (int)age,
                Country = country,
                City = city,
                Salary = salary,
                Phone = phone,
                Fax = fax,
                Address = address,
                PostalCode = postalCode,
                Region = GetRandom(0,100).ToString()
            });

            this.expenseData.Add(new Expense()
            {
                Year = year,
                ComputerExpense = computerExpense,
                ResearchExpense = researchExpense,
                TravelExpense = travelExpense,
                SalaryExpense = salaryExpense,
                SoftwareExpense = softwareExpense
            });

            this.incomeData.Add(new Income()
            {
                Year = year,
                PhoneIncome = phoneIncome,
                ComputerIncome = computerIncome,
                SoftwareIncome = softwareIncome,
                ServiceIncome = serviceIncome,
                RoyaltyIncome = royaltyIncome
            });
        }

    }

    private void CreateXls()
    {
        ExportGridData(WorkbookFormat.Excel97To2003);
        this.SaveFile(this.wb, "ExcelWorkbook");
    }

    private void CreateXlsx()
    {
        ExportGridData(WorkbookFormat.Excel2007);
        this.SaveFile(this.wb, "ExcelWorkbook");
    }

    public void SwitchDataSource(string value)
    {
        if (value.Contains("Sales Employee"))
        {
            this.Data = this.salesEmployeeData;
        }
        else if (value.Contains("Expense"))
        {
            this.Data = this.expenseData;
        }
        else if (value.Contains("Income"))
        {
            this.Data = this.incomeData;
        }

        StateHasChanged();
    }

    public void ExportGridData (WorkbookFormat format)
    {
        this.wb = new Workbook(format);
        var ws = this.wb.Worksheets.Add("Sheet1");
        ws.DefaultColumnWidth = 300 * 20;
        if (this.Data is List<SalesEmployee>)
        {
            int worksheetRow = 0;
            foreach (SalesEmployee emp in this.salesEmployeeData)
            {
                for (int i = 0; i < this.grid.ActualColumns.Count; i++)
                {
                    IgbDataGridColumn c = this.grid.ActualColumns[i];
                    var value = typeof(SalesEmployee).GetProperty(c.Field).GetValue(emp);
                    ws.Rows[worksheetRow].Cells[i].Value = value;
                }

                worksheetRow++;
            }
        }
        else if (this.Data is List<Expense>)
        {
            int worksheetRow = 0;
            foreach (Expense emp in this.expenseData)
            {
                for (int i = 0; i < this.grid.ActualColumns.Count; i++)
                {
                    IgbDataGridColumn c = this.grid.ActualColumns[i];
                    var value = typeof(Expense).GetProperty(c.Field).GetValue(emp);
                    ws.Rows[worksheetRow].Cells[i].Value = value;
                }

                worksheetRow++;
            }
        }
        else if (this.Data is List<Income>)
        {
            int worksheetRow = 0;
            foreach (Income emp in this.incomeData)
            {
                for (int i = 0; i < this.grid.ActualColumns.Count; i++)
                {
                    IgbDataGridColumn c = this.grid.ActualColumns[i];
                    var value = typeof(Income).GetProperty(c.Field).GetValue(emp);
                    ws.Rows[worksheetRow].Cells[i].Value = value;
                }

                worksheetRow++;
            }
        }
    }

    public void SaveFile(Workbook wb, string fileNameWithoutExtension)
    {
        var ms = new System.IO.MemoryStream();

        if (wb != null)
        {
            wb.Save(ms);
            string extension;

            switch (wb.CurrentFormat)
            {
                default:
                case WorkbookFormat.StrictOpenXml:
                case WorkbookFormat.Excel2007:
                    extension = ".xlsx";
                    break;
                case WorkbookFormat.Excel2007MacroEnabled:
                    extension = ".xlsm";
                    break;
                case WorkbookFormat.Excel2007MacroEnabledTemplate:
                    extension = ".xltm";
                    break;
                case WorkbookFormat.Excel2007Template:
                    extension = ".xltx";
                    break;

                case WorkbookFormat.Excel97To2003:
                    extension = ".xls";
                    break;
                case WorkbookFormat.Excel97To2003Template:
                    extension = ".xlt";
                    break;
            }

            string fileName = fileNameWithoutExtension + extension;
            string mime;

            switch (wb.CurrentFormat)
            {
                default:
                case WorkbookFormat.Excel2007:
                case WorkbookFormat.Excel2007MacroEnabled:
                case WorkbookFormat.Excel2007MacroEnabledTemplate:
                case WorkbookFormat.Excel2007Template:
                case WorkbookFormat.StrictOpenXml:
                    mime = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    break;
                case WorkbookFormat.Excel97To2003:
                case WorkbookFormat.Excel97To2003Template:
                    mime = "application/vnd.ms-excel";
                    break;
            }

            ms.Position = 0;
            var bytes = ms.ToArray();
            SaveFile(bytes, fileName, mime);
        }

    }

    JSObject module;
    bool moduleDownloaded = false;
    public async void SaveFile(byte[] bytes, string fileName, string mime)
    {
        if (Runtime is WebAssemblyJSRuntime wasmRuntime)
        {
            if (!moduleDownloaded)
            {
                module = await JSHost.ImportAsync("BlazorFastDownload", "../BlazorFastDownloadFile.js");
                moduleDownloaded = true;
            }
            BlazorFastDownload.DownloadFile(fileName, mime, bytes);
        }
        else if (Runtime is IJSInProcessRuntime inProc)
            inProc.InvokeVoid("BlazorDownloadFile", fileName, mime, bytes);

    }
    public void Dispose()
    {
        if (moduleDownloaded && module != null)
        {
            module.Dispose();
        }
    }

    public void OnTableChange(ChangeEventArgs args)
    {
        string newVal = args.Value.ToString();
        this.selected = newVal;
        this.SwitchDataSource(newVal);
    }

    public double GetRandom(double min, double max)
    {
        return Math.Round(min + (Rand.NextDouble() * (max - min)));
    }

}razorコピー

// these methods are from:
// https://www.meziantou.net/generating-and-downloading-a-file-in-a-blazor-webassembly-application.htm

function BlazorDownloadFile(filename, contentType, content) {
    // Blazor marshall byte[] to a base64 string, so we first need to convert the string (content) to a Uint8Array to create the File
    var data = base64DecToArr(content);
    // Create the URL
    var file = new File([data], filename, { type: contentType });
    var exportUrl = URL.createObjectURL(file);
    // Create the <a> element and click on it
    var a = document.createElement("a");
    document.body.appendChild(a);
    a.href = exportUrl;
    a.download = filename;
    a.target = "_self";
    a.click();
    // We don't need to keep the url, let's release the memory
    URL.revokeObjectURL(exportUrl);
}
// Convert a base64 string to a Uint8Array. This is needed to create a blob object from the base64 string.
// The code comes from: https://developer.mozilla.org/fr/docs/Web/API/WindowBase64/D%C3%A9coder_encoder_en_base64
function b64ToUint6(nChr) {
    return nChr > 64 && nChr < 91 ? nChr - 65 : nChr > 96 && nChr < 123 ? nChr - 71 : nChr > 47 && nChr < 58 ? nChr + 4 : nChr === 43 ? 62 : nChr === 47 ? 63 : 0;
}
function base64DecToArr(sBase64, nBlocksSize) {
    var sB64Enc = sBase64.replace(/[^A-Za-z0-9\+\/]/g, ""), nInLen = sB64Enc.length, nOutLen = nBlocksSize ? Math.ceil((nInLen * 3 + 1 >> 2) / nBlocksSize) * nBlocksSize : nInLen * 3 + 1 >> 2, taBytes = new Uint8Array(nOutLen);
    for (var nMod3, nMod4, nUint24 = 0, nOutIdx = 0, nInIdx = 0; nInIdx < nInLen; nInIdx++) {
        nMod4 = nInIdx & 3;
        nUint24 |= b64ToUint6(sB64Enc.charCodeAt(nInIdx)) << 18 - 6 * nMod4;
        if (nMod4 === 3 || nInLen - nInIdx === 1) {
            for (nMod3 = 0; nMod3 < 3 && nOutIdx < nOutLen; nMod3++, nOutIdx++) {
                taBytes[nOutIdx] = nUint24 >>> (16 >>> nMod3 & 24) & 255;
            }
            nUint24 = 0;
        }
    }
    return taBytes;
}
//# sourceMappingURL=BlazorDownloadFile.js.mapjsコピー

/* 
CSS styles are loaded from the shared CSS file located at:
https://static.infragistics.com/xplatform/css/samples/
*/cssコピー

Like this sample? Get access to our complete Ignite UI for Blazor toolkit and start building your own apps in minutes. Download it for free.

最速のデータ グリッド、高性能なチャート、すぐに使用できる機能のフルセットなどを含む 60 以上の再利用可能なコンポーネント を使用して、最新の Web エクスペリエンスを構築します。

無料で試してみる

Change Default Font

Primero cree una nueva instancia de IWorkbookFont. A continuación, agregue la nueva fuente a la colección Styles del Workbook. Este estilo contiene las propiedades predeterminadas para todas las celdas del libro, a menos que se especifique lo contrario en una fila, columna o celda. Cambiar las propiedades del estilo cambiará las propiedades de formato de celda predeterminadas en el libro.

var workbook = new Workbook();
var font = workbook.Styles.NormalStyle.StyleFormat.Font;
font.Name = "Times New Roman";
font.Height = 16 * 20;
razor

Setting Workbook Properties

Las propiedades de los documentos de Microsoft Excel® proporcionan información para ayudar a organizar y realizar un seguimiento de los documentos. Puede utilizar la biblioteca de Excel Infragistics Blazor para establecer estas propiedades mediante la propiedad del Workbook​ ​DocumentProperties objeto. Las propiedades disponibles son:

• Author

• Title

• Subject

• Keywords

• Category

• Status

• Comments

• Company

• Manager

El siguiente código demuestra cómo crear un libro y establecer sus propiedades title y documento status.

var workbook = new Workbook();
workbook.DocumentProperties.Title = "Expense Report";
workbook.DocumentProperties.Status = "Complete";
razor

Workbook Protection

La función de protección del libro le permite proteger la estructura del libro. Es decir, la capacidad de un usuario de agregar, cambiar el nombre, eliminar, ocultar y reordenar las hojas de trabajo de ese libro.

La protección no se aplica a través del modelo de objetos de Infragistics Excel Engine. Es responsabilidad de la interfaz de usuario que visualiza este modelo de objetos respetar estas configuraciones de protección y permitir o restringir que el usuario realice las operaciones correspondientes.

La protección se aplica a un libro invocando su método protect.

Cuando un Workbook está protegido sin contraseña, el usuario final puede desproteger el Workbook en Excel sin tener que proporcionar una contraseña. Para desproteger un Workbook mediante programación, se puede utilizar el método unprotect.

Cuando un Workbook está protegido, los valores de las propiedades de la instancia WorkbookProtection de la propiedad protection de este Workbook indican las operaciones deshabilitadas.

If IsProtected is already true, the protect method will be ignored.

var workbook = new Workbook();
workbook.Protect(false, false);
razor

Compruebe si un libro tiene protección. Esta propiedad de solo lectura devuelve verdadero si el libro tiene alguna protección establecida mediante las sobrecargas del método Protect.

var workbook = new Workbook();
var protect = workbook.IsProtected;
razor

Esta propiedad de solo lectura devuelve un objeto de tipo WorkbookProtection que contiene propiedades para obtener cada configuración de protección individualmente.

var workbook = new Workbook();
var protect = workbook.Protection;
razor

API References

• DocumentProperties
• WorkbookProtection
• Workbook
• Workbook