Blazor Excel 라이브러리 개요

Infragistics Blazor Excel Library를 사용하면 Workbook, Worksheet, Cell, Formula 등과 같은 익숙한 Microsoft® Excel® 스프레드시트 개체를 사용하여 스프레드시트 데이터로 작업할 수 있습니다. Infragistics Blazor Excel Library를 사용하면 Excel 스프레드시트에서 애플리케이션의 데이터를 쉽게 표현하고 Excel에서 애플리케이션으로 데이터를 전송할 수 있습니다.

Blazor Excel Library Example

EXAMPLE

MODULES

DATA

RAZOR

JS

CSS

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;

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) });
            await builder.Build().RunAsync();
        }
    }
}csコピー

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 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 System.Runtime.InteropServices.JavaScript

@implements IDisposable

<div class="container vertical">

    <div class="options vertical">
        <button @onclick="CreateXlsx">Save Workbook to XLSX</button>
        <button @onclick="CreateXls">Save Workbook to XLS</button>
    </div>
</div>

@code {

    [Inject]
    public IJSRuntime Runtime { get; set; }
    public bool canSave = false;
    public Workbook wb;
    public Worksheet ws;
    public List<string> worksheetRegion = null;
    public string selectedRegion = null;
    private Random Rand = new Random();

    protected override void OnInitialized()
    {

        this.WorkbookCreate();
    }

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

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

    public void WorkbookCreate() {
        Workbook.InProcessRuntime = this.Runtime as IJSInProcessRuntime;

        var wb = new Workbook(WorkbookFormat.Excel2007);
        var employeeSheet = wb.Worksheets.Add("Employees");
        var employeeHeader = employeeSheet.Rows[0];
        var companies = new string[] { "Amazon", "Ford", "Jaguar", "Tesla", "IBM", "Microsoft" };
        var firstNames = new string[] { "Andrew", "Mike", "Martin", "Ann", "Victoria", "John", "Brian", "Jason", "David" };
        var lastNames = new string[] { "Smith", "Jordan", "Johnson", "Anderson", "Louis", "Phillips", "Williams" };
        var countries = new string[] { "UK", "France", "USA", "Germany", "Poland", "Brazil" };
        var titles = new string[] { "Sales Rep.", "Engineer", "Administrator", "Manager" };
        var employeeColumns = new string[] { "Name", "Company", "Title", "Age", "Country" };
        for (var col = 0; col < employeeColumns.Length; col++) {
            employeeSheet.Columns[col].Width = 5000;
            employeeHeader.SetCellValue(col, employeeColumns[col]);
        }
        for (var i = 1; i < 20; i++) {
            var company = this.GetItem(companies);
            var title = this.GetItem(titles);
            var country = this.GetItem(countries);
            var name = this.GetItem(firstNames) + " " + this.GetItem(lastNames);
            var salary = this.GetRandom(45000, 95000);
            var age = this.GetRandom(20, 65);
            var wr = employeeSheet.Rows[i] as WorksheetRow;
            wr.SetCellValue(0, name);
            wr.SetCellValue(1, company);
            wr.SetCellValue(2, title);
            wr.SetCellValue(3, age);
            wr.SetCellValue(4, country);
            wr.SetCellValue(5, salary);
        }
        var expanseSheet = wb.Worksheets.Add("Expanses");
        var expanseHeader = expanseSheet.Rows[0];
        var expanseNames = new string[] { "Year", "Computers", "Research", "Travel", "Salary", "Software" };
        var expanseCol = 0;
        foreach (var key in expanseNames) {
            expanseSheet.Columns[expanseCol].Width = 5000;
            expanseHeader.SetCellValue(expanseCol, key);
            for (var i = 1; i < 20; i++) {
                var wr = expanseSheet.Rows[i];
                if (key == "Year") {
                    wr.SetCellValue(expanseCol, 2010 + i);
                } else if (key == "Computers") {
                    wr.SetCellValue(expanseCol, this.GetRandom(50000, 65000));
                } else if (key == "Research") {
                    wr.SetCellValue(expanseCol, this.GetRandom(150000, 165000));
                } else if (key == "Travel") {
                    wr.SetCellValue(expanseCol, this.GetRandom(20000, 25000));
                } else if (key == "Salary") {
                    wr.SetCellValue(expanseCol, this.GetRandom(4000000, 450000));
                } else if (key == "Software") {
                    wr.SetCellValue(expanseCol, this.GetRandom(100000, 150000));
                }
            }
            expanseCol++;
        }
        var incomeSheet = wb.Worksheets.Add("Income");
        var incomeHeader = incomeSheet.Rows[0];
        var incomeNames = new string[] { "Year", "Phones", "Computers", "Software", "Services", "Royalties" };
        var incomeCol = 0;
        foreach (var key in incomeNames) {
            incomeSheet.Columns[incomeCol].Width = 5000;
            incomeHeader.SetCellValue(incomeCol, key);
            for (var i = 1; i < 20; i++) {
                var wr = incomeSheet.Rows[i];
                if (key == "Year") {
                    wr.SetCellValue(incomeCol, 2010 + i);
                } else if (key == "Software") {
                    wr.SetCellValue(incomeCol, this.GetRandom(700000, 850000));
                } else if (key == "Computers") {
                    wr.SetCellValue(incomeCol, this.GetRandom(250000, 265000));
                } else if (key == "Royalties") {
                    wr.SetCellValue(incomeCol, this.GetRandom(400000, 450000));
                } else if (key == "Phones") {
                    wr.SetCellValue(incomeCol, this.GetRandom(6000000, 650000));
                } else if (key == "Services") {
                    wr.SetCellValue(incomeCol, this.GetRandom(700000, 750000));
                }
            }
            incomeCol++;
        }
        this.WorkbookParse(wb);
    }

    public void WorkbookParse(Workbook wb)
    {
        if (wb == null)
        {
            this.worksheetRegion = null;
            this.selectedRegion = null;
        }
        else
        {
            var names = new List<string>();
            var worksheets = wb.Worksheets;
            var wsCount = worksheets.Count;
            for (var i = 0; i < wsCount; i++)
            {
                var tables = worksheets[i].Tables;
                var tCount = tables.Count;
                for (var j = 0; j < tCount; j++)
                {
                    names.Add(worksheets[i].Name + " - " + tables[j].Name);
                }
            }
            this.worksheetRegion = names;
            this.selectedRegion = names.Count > 0 ? names[0] : null;
        }
        this.wb = wb;
        this.canSave = wb != null;
    }

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

    public string GetItem(string[] array)
    {
        var index = (int)Math.Round(GetRandom(0, array.Length - 1));
        return array[index];
    }

    private void SaveFile(Workbook workbook, string fileNameWithoutExtension, WorkbookFormat format)
    {
        var ms = new System.IO.MemoryStream();
        workbook.SetCurrentFormat(format);
        workbook.Save(ms);

        string extension;

        switch (workbook.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 (workbook.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();
        this.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();
        }
    }
}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 エクスペリエンスを構築します。

無料で試してみる

Requirements

Blazor Excel 라이브러리를 사용하려면 다음 using 문을 추가해야 합니다.

@using Infragistics.Documents.Excel
razor

웹 어셈블리(WASM) Blazor 프로젝트를 사용하는 경우 몇 가지 추가 단계가 있습니다.

• wwwroot/index.html 파일에 다음 스크립트에 대한 참조를 추가합니다.

<script src="_content/IgniteUI.Blazor.Documents.Excel/excel.js"></script>
razor

• 정적 Workbook.InProcessRuntime 현재 런타임으로 설정합니다. 이는 다음 코드를 사용하여 수행할 수 있습니다.

@using Microsoft.JSInterop

@code {

    [Inject]
    public IJSRuntime Runtime { get; set; }

    protected override void OnInitialized()
    {
        base.OnInitialized();
        Workbook.InProcessRuntime = (IJSInProcessRuntime)this.Runtime;
    }
}
razor

Supported Versions of Microsoft Excel

다음은 지원되는 Excel 버전 목록입니다.**

• 마이크로소프트 엑셀 97

• 마이크로소프트 엑셀 2000

• 마이크로소프트 엑셀 2002

• 마이크로소프트 엑셀 2003

• 마이크로소프트 엑셀 2007

• 마이크로소프트 엑셀 2010

• 마이크로소프트 엑셀 2013

• 마이크로소프트 엑셀 2016

Load and Save Workbooks

이제 Excel 라이브러리 모듈을 가져왔으므로 다음 단계는 통합 문서를 로드하는 것입니다.

​Workbook 개체를 로드하고 저장하려면 실제 Workbook 개체의 save 메서드와 정적 Load 메서드를 활용할 수 있습니다.

protected override void OnInitialized()
{
    var memoryStream = new System.IO.MemoryStream();
    workbook.Save(memoryStream);

    memoryStream.Position = 0;
    var bytes = memoryStream.ToArray();
    this.SaveFile(bytes, "fileName.xlsx", string.Empty);
}

private void SaveFile(byte[] bytes, string fileName, string mime)
{
    if (this.Runtime is WebAssemblyJSRuntime wasmRuntime)
      wasmRuntime.InvokeUnmarshalled<string, string, byte[], bool>("BlazorDownloadFileFast", fileName, mime, bytes);
    else if (this.Runtime is IJSInProcessRuntime inProc)
      inProc.InvokeVoid("BlazorDownloadFile", fileName, mime, bytes);
}
razor

API References

• Load
• WorkbookInProcessRuntime
• Worksheet
• Workbook