10分鐘做好 Bootstrap Blazor 的表格元件匯出 Excel/Word/Html/Pdf

AlexChow發表於2023-01-11

上篇: Bootstrap Blazor 實戰通用匯入匯出服務(Table元件)

1.新建工程

新建工程b14table

dotnet new blazorserver -o b14table

將專案新增到解決方案中：

dotnet sln add b14table/b14table.csproj

使用 nuget.org 進行 BootstrapBlazor 元件安裝, FreeSql sqlite庫,字型 ..

dotnet add b03sqlite package BootstrapBlazor
dotnet add b03sqlite package BootstrapBlazor.FontAwesome
dotnet add b03sqlite package FreeSql.Provider.Sqlite
dotnet add b03sqlite Densen.Extensions.BootstrapBlazor
dotnet add b03sqlite package Densen.FreeSql.Extensions.BootstrapBlazor
dotnet add b03sqlite package Magicodes.IE.Core
dotnet add b03sqlite package Magicodes.IE.Excel
dotnet add b03sqlite package Magicodes.IE.Html
dotnet add b03sqlite package Magicodes.IE.Pdf
dotnet add b03sqlite package Magicodes.IE.Word
dotnet add b03sqlite package HtmlToOpenXml.dll
dotnet add b03sqlite package Haukcode.WkHtmlToPdfDotNet

2.樣式表和Javascript 引用

增加主題樣式表到 Pages/_Host.cshtml 檔案中

刪除 <link rel="stylesheet" href="css/bootstrap/bootstrap.min.css" />

並在下面新增兩行

<link href="_content/BootstrapBlazor.FontAwesome/css/font-awesome.min.css" rel="stylesheet">
<link href="_content/BootstrapBlazor/css/bootstrap.blazor.bundle.min.css" rel="stylesheet">

新增 Javascript 引用到 Pages/_Layout.cshtml 檔案中

在 <script src="_framework/blazor.server.js"></script> 之前新增

<script src="_content/BootstrapBlazor/js/bootstrap.blazor.bundle.min.js" asp-append-version="true"></script>

完整檔案

@page "/"
@using Microsoft.AspNetCore.Components.Web
@namespace b14table.Pages
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <base href="~/" />

    <!-- 刪掉這行 <link rel="stylesheet" href="css/bootstrap/bootstrap.min.css" /> !-->
    <link href="css/site.css" rel="stylesheet" />
    
    <!-- 新增下面兩行 !-->
    <link href="_content/BootstrapBlazor.FontAwesome/css/font-awesome.min.css" rel="stylesheet">
    <link href="_content/BootstrapBlazor/css/bootstrap.blazor.bundle.min.css" rel="stylesheet">
    <!-- 新增上面兩行 !-->

    <link href="b14table.styles.css" rel="stylesheet" />
    <link rel="icon" type="image/png" href="favicon.png" />
    <component type="typeof(HeadOutlet)" render-mode="ServerPrerendered" />
</head>
<body>
    <component type="typeof(App)" render-mode="ServerPrerendered" />

    <div id="blazor-error-ui">
        <environment include="Staging,Production">
            An error has occurred. This application may no longer respond until reloaded.
        </environment>
        <environment include="Development">
            An unhandled exception has occurred. See browser dev tools for details.
        </environment>
        <a href="" class="reload">Reload</a>
        <a class="dismiss">?</a>
    </div>

    <!-- 增加這行 !-->
    <script src="_content/BootstrapBlazor/js/bootstrap.blazor.bundle.min.js"></script>

    <script src="_framework/blazor.server.js"></script>
</body>
</html>

3.新增增加名稱空間引用到 `_Imports.razor` 檔案中

其中 AME.Services 和 Blazor100.Service 名稱空間稍後檔案裡用到

@using BootstrapBlazor.Components 
@using AME.Services
@using Blazor100.Service
@using System.Diagnostics.CodeAnalysis

4.增加 BootstrapBlazorRoot 元件到 App.razor 檔案中

<BootstrapBlazorRoot>
    <Router AppAssembly="@typeof(App).Assembly">
        ...
    </Router>
</BootstrapBlazorRoot>

5.新增 BootstrapBlazor服務/ FreeSql 資料服務/ ImportExportsService 匯出服務到 `Program.cs` 檔案中

在 builder.Services.AddSingleton<WeatherForecastService>(); 後加入


builder.Services.AddFreeSql(option =>
{
    option.UseConnectionString(FreeSql.DataType.Sqlite, "Data Source=test.db;")  //也可以寫到配置檔案中
#if DEBUG
         //開發環境:自動同步實體
         .UseAutoSyncStructure(true)
         .UseNoneCommandParameter(true)
         //除錯sql語句輸出
         .UseMonitorCommand(cmd => System.Console.WriteLine(cmd.CommandText))
#endif
    ;
});
builder.Services.AddTransient<ImportExportsService>();
//不寫這句因為下面一句擴充套件包含了 //builder.Services.AddBootstrapBlazor();
builder.Services.AddDensenExtensions();

6. 匯出服務 Service/ImportExportsService.cs

using BootstrapBlazor.Components;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using Magicodes.ExporterAndImporter.Html;
using Magicodes.ExporterAndImporter.Pdf;
using Magicodes.ExporterAndImporter.Word;

namespace Blazor100.Service
{
    /// <summary>
    /// 通用匯入匯出服務類
    /// </summary>
    public class ImportExportsService
    {
        public enum ExportType
        {
            Excel,
            Pdf,
            Word,
            Html
        }

        public async Task<string> Export<T>(string filePath, List<T>? items = null, ExportType exportType = ExportType.Excel) where T : class, new()
        {
            switch (exportType)
            {
                case ExportType.Pdf:
                    var exporterPdf = new PdfExporter();
                    items = items ?? new List<T>();
                    var resultPdf = await exporterPdf.ExportListByTemplate(filePath + ".pdf", items);
                    return resultPdf.FileName;
                case ExportType.Word:
                    var exporterWord = new WordExporter();
                    items = items ?? new List<T>();
                    var resultWord = await exporterWord.ExportListByTemplate(filePath + ".docx", items);
                    return resultWord.FileName;
                case ExportType.Html:
                    var exporterHtml = new HtmlExporter();
                    items = items ?? new List<T>();
                    var resultHtml = await exporterHtml.ExportListByTemplate(filePath + ".html", items);
                    return resultHtml.FileName;
                default:
                    IExporter exporter = new ExcelExporter();
                    items = items ?? new List<T>();
                    var result = await exporter.Export(filePath + ".xlsx", items);
                    return result.FileName;
            }
        }

        public async Task<(IEnumerable<T>? items,string error)> ImportFormExcel<T>(string filePath) where T : class, new()
        {
            IExcelImporter Importer = new ExcelImporter();
            var import = await Importer.Import<T>(filePath);
            if (import.Data == null ) 
            {
                return (null, import.Exception.Message);
            }
            return (import.Data!.ToList(),""); 
        }
 
    }
}

7. 資料實體類 Data/WeatherForecast.cs

using BootstrapBlazor.Components;
using FreeSql.DataAnnotations;
using Magicodes.ExporterAndImporter.Excel;
using OfficeOpenXml.Table;
using System.ComponentModel;

namespace b14table.Data;

[ExcelImporter(IsLabelingError = true)]
[ExcelExporter(Name = "匯入商品中間表", TableStyle = TableStyles.Light10, AutoFitAllColumn = true)]
[AutoGenerateClass(Searchable = true, Filterable = true, Sortable = true)]
public class WeatherForecast
{
    [Column(IsIdentity = true)]
    [DisplayName("序號")]
    public int ID { get; set; }

    [DisplayName("日期")]
    public DateTime Date { get; set; }

    public int TemperatureC { get; set; }

    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

    public string? Summary { get; set; }
}

8. 介面和程式碼 Pages/Index.razor

@page "/"
@using b14table.Data
<PageTitle>Index</PageTitle>

<Tab>
    <TabItem Text="匯入匯出">
        <ImpExpII />
    </TabItem> 
    <TabItem Text="資料維護">
        <Table TItem="WeatherForecast"
               IsPagination="true"
               IsStriped="true"
               IsBordered="true"
               AutoGenerateColumns="true"
               ShowSearch="true"
               ShowToolbar="true"
               ShowExtendButtons="true"
               DoubleClickToEdit=true
               ShowColumnList=true
               ShowCardView=true>
        </Table>

        @code{
            // 由於使用了FreeSql ORM 資料服務,可以直接取物件
            [Inject] IFreeSql? fsql { get; set; }
            [Inject] ToastService? toastService { get; set; }

            //用演示服務的資料初始化資料庫
            [Inject] WeatherForecastService? ForecastService { get; set; }
            protected override async Task OnAfterRenderAsync(bool firstRender)
            {
                if (firstRender)
                {
                    //懶的人,直接初始化一些資料用用
                    var res = fsql!.Select<WeatherForecast>().Count();
                    if (res == 0)
                    {
                        var forecasts = (await ForecastService!.GetForecastAsync(DateTime.Now)).ToList();
                        fsql.Insert<WeatherForecast>().AppendData(forecasts).ExecuteAffrows();
                    }
                }
            }


        }

    </TabItem>
</Tab>

9. 介面和程式碼 Pages/ImpExpII.razor

@page "/impexpii"
@using b14table.Data
@using static Blazor100.Service.ImportExportsService

<PageTitle>匯入匯出II</PageTitle>

<InputFile OnChange="OnChange" style="max-width:400px" class="form-control"  />
<br/>

<Table @ref="list1"
       TItem="WeatherForecast"
       IsPagination="true"
       IsStriped="true"
       IsBordered="true"
       AutoGenerateColumns="true"
       ShowSearch="true"
       ShowToolbar="true"
       ShowExtendButtons="true"
       DataService="LazyHeroDataService"
       OnSaveAsync="LazyHeroDataService!.SaveAsync"
       OnDeleteAsync="LazyHeroDataService.DeleteAsync"
       DoubleClickToEdit="@DoubleClickToEdit"
       IsExcel="@IsExcel"
       ScrollingDialogContent="true"
       EditDialogIsDraggable="true"
       EditDialogSize="Size.ExtraLarge"
       EditDialogShowMaximizeButton="true"
       ShowExportButton
       OnExportAsync="ExportAsync"
       PageItemsSource="new int[] {5, 20, 50, 100, 200, 500, 1000 }">

    <TableToolbarTemplate>
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Primary" Text="自由編輯" OnClick="@IsExcelToggle" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Warning" Text="隨機資料" IsAsync OnClick="@GetDatasAsync" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Secondary" Text="匯入" IsAsync OnClick="@ImportExcel" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Danger" Text="清空" IsAsync OnClick="EmptyAll" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Success" Text="模板" IsAsync OnClick="Export模板Async" />
    </TableToolbarTemplate>

    <ExportButtonDropdownTemplate>
        <h6 class="dropdown-header">當前頁資料</h6>
        <div class="dropdown-item" @onclick="_=>ExportExcelAsync(list1.Rows)">
            <i class="fas fa-file-excel"></i>
            <span>Excel</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportWordAsync(list1.Rows)">
            <i class="fas fa-file-word"></i>
            <span>Word</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportHtmlAsync(list1.Rows)">
            <i class="fa-brands fa-html5"></i>
            <span>Html</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportPDFAsync(list1.Rows)">
            <i class="fas fa-file-pdf"></i>
            <span>PDF</span>
        </div>
        <div class="dropdown-divider"></div>
        <h6 class="dropdown-header">全部資料</h6>
        <div class="dropdown-item" @onclick="_=>ExportExcelAsync(LazyHeroDataService!.Items)">
            <i class="fas fa-file-excel"></i>
            <span>Excel</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportWordAsync(LazyHeroDataService!.Items)">
            <i class="fas fa-file-word"></i>
            <span>Word</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportHtmlAsync(LazyHeroDataService!.Items)">
            <i class="fa-brands fa-html5"></i>
            <span>Html</span>
        </div>
        <div class="dropdown-item" @onclick="_=>ExportPDFAsync(LazyHeroDataService!.Items)">
            <i class="fas fa-file-pdf"></i>
            <span>PDF</span>
        </div>
    </ExportButtonDropdownTemplate>
</Table>

@code{
    [Inject]
    Microsoft.AspNetCore.Hosting.IWebHostEnvironment? HostEnvironment { get; set; }

    [Inject]
    [NotNull]
    NavigationManager? NavigationManager { get; set; }

    [Inject]
    [NotNull]
    ImportExportsService? ImportExportsService { get; set; }

    [Inject]
    [NotNull]
    ToastService? ToastService { get; set; }

    [Inject]
    WeatherForecastService? ForecastService { get; set; }

    [Inject]
    [NotNull]
    LazyHeroDataService<WeatherForecast>? LazyHeroDataService { get; set; }

    [NotNull]
    Table<WeatherForecast>? list1 { get; set; }

    public bool IsExcel { get; set; }
    public bool DoubleClickToEdit { get; set; } = true;
    protected string UploadPath = "";
    protected string? uploadstatus;
    long maxFileSize = 1024 * 1024 * 15;
    string? tempfilename;


    protected async Task GetDatasAsync()
    {
        LazyHeroDataService!.Items = (await ForecastService!.GetForecastAsync(DateTime.Now)).ToList();
        await list1!.QueryAsync();
    }

    protected override async void OnAfterRender(bool firstRender)
    {
        if (firstRender)
        {
            UploadPath = Path.Combine(HostEnvironment!.WebRootPath, "uploads");
            if (!Directory.Exists(UploadPath)) Directory.CreateDirectory(UploadPath);
            await GetDatasAsync();
        }
    }

    private Task IsExcelToggle()
    {
        IsExcel = !IsExcel;
        DoubleClickToEdit = !IsExcel;
        StateHasChanged();
        return Task.CompletedTask;
    }

    public async Task<bool> Export模板Async()
    {
        await Export();
        return true;
    } 

    private async Task<bool> ExportExcelAsync(IEnumerable<WeatherForecast> items) => await ExportAutoAsync(items, ExportType.Excel);
    private async Task<bool> ExportPDFAsync(IEnumerable<WeatherForecast> items) => await ExportAutoAsync(items, ExportType.Pdf);
    private async Task<bool> ExportWordAsync(IEnumerable<WeatherForecast> items) => await ExportAutoAsync(items, ExportType.Word);
    private async Task<bool> ExportHtmlAsync(IEnumerable<WeatherForecast> items) => await ExportAutoAsync(items, ExportType.Html);

    private async Task<bool> ExportAutoAsync(IEnumerable<WeatherForecast> items, ExportType exportType = ExportType.Excel)
    {
        if ((items == null || !items.Any()) && (LazyHeroDataService!.Items == null || !LazyHeroDataService!.Items.Any()))
        {
            await ToastService.Error("提示", "無資料可匯出");
            return false;
        }
        var option = new ToastOption()
        {
            Category = ToastCategory.Information,
            Title = "提示",
            Content = $"匯出正在執行,請稍等片刻...",
            IsAutoHide = false
        };
        // 彈出 Toast
        await ToastService.Show(option);
        await Task.Delay(100);


        // 開啟後臺程式進行資料處理
        await Export(items?.ToList(), exportType);

        // 關閉 option 相關聯的彈窗
        option.Close();

        // 彈窗告知下載完畢
        await ToastService.Show(new ToastOption()
        {
            Category = ToastCategory.Success,
            Title = "提示",
            Content = $"匯出成功,請檢查資料",
            IsAutoHide = false
        });
        return true;

    }

    private async Task Export(List<WeatherForecast>? items=null, ExportType exportType = ExportType.Excel)
    {
        try
        {
            if (items==null || !items.Any()) items = LazyHeroDataService!.Items;
            var fileName = items == null ? "模板" : typeof(WeatherForecast).Name;
            var fullName = Path.Combine(UploadPath, fileName);
            fullName = await ImportExportsService.Export(fullName, items, exportType);
            fileName = (new System.IO.FileInfo(fullName)).Name;
            ToastService?.Success("提示", fileName + "已生成");

            //下載後清除檔案
            NavigationManager.NavigateTo($"uploads/{fileName}", true);
            _ = Task.Run(() =>
            {
                Thread.Sleep(50000);
                System.IO.File.Delete(fullName);
            });

        }
        catch (Exception e)
        {
            ToastService?.Error($"匯出", $"{exportType}出錯,請檢查. {e.Message}");
        }
    }

    public async Task<bool> EmptyAll()
    {
        LazyHeroDataService!.Items = new List<WeatherForecast>();
        await ToastService!.Show(new ToastOption()
        {
            Category = ToastCategory.Success,
            Title = "提示",
            Content = "已清空資料",
        });

        await list1!.QueryAsync();
        return true;
    }
    private async Task ImportExcel()
    {
        if (string.IsNullOrEmpty(tempfilename))
        {
            ToastService?.Error("提示", "請正確選擇檔案上傳");
            return;
        }
        var option = new ToastOption()
        {
            Category = ToastCategory.Information,
            Title = "提示",
            Content = "匯入檔案中,請稍等片刻...",
            IsAutoHide = false
        };
        // 彈出 Toast
        await ToastService!.Show(option);
        await Task.Delay(100);


        // 開啟後臺程式進行資料處理
        var isSuccess= await MockImportExcel();

        // 關閉 option 相關聯的彈窗
        option.Close();

        // 彈窗告知下載完畢
        await ToastService.Show(new ToastOption()
        {
            Category = isSuccess? ToastCategory.Success : ToastCategory.Error,
            Title = "提示",
            Content = isSuccess ? "操作成功,請檢查資料":"出現錯誤,請重試匯入或者上傳",
            IsAutoHide = false
        });

        await list1!.QueryAsync();
    }
    private async Task<bool> MockImportExcel()
    {
        var items_temp = await ImportExportsService!.ImportFormExcel<WeatherForecast>(tempfilename!);
        if (items_temp.items == null)
        {
            ToastService?.Error("提示", "檔案匯入失敗: "+ items_temp.error);
            return false;
        }
        //items = SmartCombine(items_temp, items).ToList(); 新資料和老資料合併處理,略100字
        LazyHeroDataService!.Items = items_temp!.items.ToList();
        return true;
    }

    protected async Task OnChange(InputFileChangeEventArgs e)
    {
        if (e.File == null) return;
        tempfilename = Path.Combine(UploadPath, e.File.Name);
        await using FileStream fs = new(tempfilename, FileMode.Create);
        using var stream = e.File.OpenReadStream(maxFileSize);
        await stream.CopyToAsync(fs);

        //正式工程此處是回撥,簡化版必須InvokeAsync一下,自由發揮
        _ = Task.Run(async () => await InvokeAsync(async () => await ImportExcel()));

    }

    /// <summary>
    /// 匯出資料方法
    /// </summary>
    /// <param name="Items"></param>
    /// <param name="opt"></param>
    /// <returns></returns>
    protected async Task<bool> ExportAsync(IEnumerable<WeatherForecast> Items, QueryPageOptions opt)
    {
        var ret = false;
        ret = await ExportExcelAsync(Items);
        return ret;
    }


}

專案原始碼

Github | Gitee

關聯專案

FreeSql QQ群：4336577(已滿)、8578575(已滿)、52508226(線上)

BA & Blazor QQ群：795206915、675147445

知識共享許可協議

本作品採用知識共享署名-非商業性使用-相同方式共享 4.0 國際許可協議進行許可。歡迎轉載、使用、重新發布，但務必保留文章署名AlexChow（包含連結： https://github.com/densen2014 ），不得用於商業目的，基於本文修改後的作品務必以相同的許可釋出。如有任何疑問，請與我聯絡。

AlexChow

今日頭條 | 部落格園 | 知乎 | Gitee | GitHub

NPOI匯出和匯入Excel,Word和PDF
2018-07-23
Excel
Element-ui元件庫Table表格匯出Excel表格
2019-04-03
UI元件Excel
excel表格匯入word方法彙總
2016-06-27
Excel
C# word或者excel儲存或匯出為PDF
2016-11-07
C#Excel
js匯出Excel表格
2018-12-29
JSExcel
vue匯出Excel表格
2020-12-28
VueExcel
如何將excel表格匯入word並保持格式不變如何把excel的表格弄到word文件
2022-03-26
Excel
匯出Excel或word文件
2012-07-06
Excel
模板匯出word和 EXcel
2010-10-15
Excel
vue + element + 匯入、匯出excel表格
2018-09-10
VueExcel
vue+elementUI表格匯出excel
2018-11-16
VueUIExcel
vue將表格匯出為excel
2018-03-18
VueExcel
Vue實現匯出excel表格
2018-03-28
VueExcel
資料匯出為excel表格
2016-04-24
Excel
騰訊文件怎樣匯出excel表格騰訊文件如何匯出excel
2022-03-22
Excel
Blazor元件自做十二 : Blazor Pdf Reader PDF閱讀器元件
2022-12-06
Blazor元件
word放不下excel表格怎麼辦 word放不下excel表格的方法
2022-05-09
Excel
資料庫文件編寫,如何通過Navicat把表導成表格?資料庫快速匯出為excel表格資訊,excel匯出到word表格
2021-06-28
資料庫Excel
element-UI庫Table表格匯出Excel表格
2020-02-19
UIExcel
Vue框架下實現匯入匯出Excel、匯出PDF
2021-09-09
Vue框架Excel
vue匯出excel資料表格功能
2018-11-17
VueExcel
vue實現前端匯出excel表格
2018-08-01
Vue前端Excel
springboot poi匯出excel表格
2018-07-12
Spring BootExcel
前端常用方法之匯出excel表格
2020-11-12
前端Excel
PHP匯出大量資料到excel表格
2016-10-13
PHPExcel
將SAP CRM WebClient UI的表格匯出成PDF
2020-08-02
WebclientUI
如何將SAP WebClient UI的表格匯出成PDF
2020-08-26
WebclientUI
Blazor Bootstrap 元件庫語音元件介紹
2022-04-13
Blazorboot元件
Vue匯出資料到Excel電子表格
2020-03-13
VueExcel
vue2.0 匯出Excel表格資料
2018-03-22
VueExcel
excel匯出、mysql分頁
2018-09-03
ExcelMySql
html轉Excel表格
2018-12-21
HTMLExcel
word與excel轉pdf方法
2020-11-06
Excel
Blazor Pdf Reader PDF閱讀器元件更新
2023-01-25
Blazor元件
dcat Laravel-Excel(Maatwebsite\Excel)的分塊匯出
2021-11-18
LaravelExcelWeb
php生成匯出Word、Excel、PowerPoint外掛
2017-11-17
PHPExcel
頁面資料匯出為word或者excel
2016-01-12
Excel
html table 匯出excel,親測有效
2018-11-28
HTMLExcel