Blazor Excel 라이브러리 개요
Infragistics Blazor Excel Library를 사용하면 Workbook, Worksheet, Cell, Formula 등과 같은 익숙한 Microsoft® Excel® 스프레드시트 개체를 사용하여 스프레드시트 데이터로 작업할 수 있습니다. Infragistics Blazor Excel Library를 사용하면 Excel 스프레드시트에서 애플리케이션의 데이터를 쉽게 표현하고 Excel에서 애플리케이션으로 데이터를 전송할 수 있습니다.
Blazor Excel 라이브러리 예제
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();
}
}
}csusing 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
요구사항
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지원되는 Microsoft Excel 버전
다음은 지원되는 Excel 버전 목록입니다.**
마이크로소프트 엑셀 97
마이크로소프트 엑셀 2000
마이크로소프트 엑셀 2002
마이크로소프트 엑셀 2003
마이크로소프트 엑셀 2007
마이크로소프트 엑셀 2010
마이크로소프트 엑셀 2013
마이크로소프트 엑셀 2016
통합 문서 로드 및 저장
이제 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);
}
razorAPI 참조
LoadWorkbookInProcessRuntimeWorksheetWorkbook