Blazor ワークブックの使用

Infragistics Blazor Excel Engine は、データを Microsoft® Excel® に保存、また Microsoft® Excel® からの読み込みを可能にします。ライブラリのさまざまなクラスを使用してワークブックやワークシートを作成、データを入力、データを Excel にエクスポートできます。Infragistics Blazor Excel Engine は、Excel スプレッドシートでアプリケーションのデータの表示や Excel からアプリケーションへのデータのインポートが簡単にできます。

Blazor ワークブックの使用の例

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コピー

このサンプルが気に入りましたか? 完全な Ignite UI for Blazorツールキットにアクセスして、すばやく独自のアプリの作成を開始します。無料でダウンロードできます。

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

無料で試してみる

既定のフォントを変更

IWorkbookFont の新しいインスタンスを作成します。Workbook の Styles コレクションに新しいフォントを追加します。このスタイルにはワークブックのすべてのセルのデフォルトのプロパティが含まれています。ただし、行、列またはセルで指定されている場合はその限りではありません。スタイルのプロパティを変更すると、ワークブックのデフォルトのセル書式プロパティが変更します。

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

ワークブック プロパティの設定

Microsoft Excel® ドキュメント プロパティは、ドキュメントの整理やトラッキングを改善するための情報を提供します。Workbook オブジェクトの DocumentProperties プロパティを使用してこれらのプロパティを設定するために、Infragistics Blazor Excel Engine を使用できます。使用可能なプロパティは以下のとおりです。

• Author

• Title

• Subject

• Keywords

• Category

• Status

• Comments

• Company

• Manager

以下のコードは、ブックを作成し、title および status ドキュメント プロパティを設定する方法を示します。

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

ブックの保護

ブック保護機能は、ブックの構造を保護できます。つまり、ユーザーがそのブック内のワークシートを追加、名前変更、削除、非表示、およびソートができます。

Infragistics Excel Engine のオブジェクト モデルから保護が強制されることはありません。これらの保護設定を履行し、対応する操作の実行をユーザーに許可または制限することは、このオブジェクト モデルを表示する UI の役割です。

保護は、protect メソッドを呼び出すことによってブックに適用されます。

Workbook がパスワードを使用せずに保護される場合、エンドユーザーは Excel で Workbook の保護をパスワードを入力せずに解除できます。Workbook の保護をコードで解除するには、unprotect メソッドを使用できます。

Workbook が保護される場合、この Workbook の protection プロパティの WorkbookProtection インスタンスのプロパティの値は無効な操作を示します。

IsProtected が既に true の場合、protect メソッドは無視されます。

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

ブックが保護されているかどうかの確認この読み取り専用プロパティは、ワークブックに Protect メソッドのオーバーロードを使用して設定された保護がある場合、true を返します。

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

この読み取り専用プロパティは、保護の各設定を個別に取得するためにプロパティを含む WorkbookProtection 型のオブジェクトを返します。

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

API リファレンス

• DocumentProperties
• WorkbookProtection
• Workbook
• Workbook