表格组件导出 Excel/Word/Html/Pdf #310

densen2014 · 2023-01-11T11:44:15Z

densen2014
Jan 11, 2023
Collaborator

上篇: 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

[可选]BootstrapBlazor官方BootstrapBlazor.DataAcces.FreeSql包替换Densen.FreeSql.Extensions.BootstrapBlazor

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">X</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

AlexChow

今日头条 | 博客园 | 知乎 | Gitee | GitHub

Answered by densen2014

Jan 16, 2023

CV一条龙系列教程

View full answer

densen2014 · 2023-01-16T18:41:37Z

densen2014
Jan 16, 2023
Collaborator Author

CV一条龙系列教程

0 replies

Navigation Menu

Uh oh!

Uh oh!

表格组件导出 Excel/Word/Html/Pdf #310

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

Uh oh!

表格组件导出 Excel/Word/Html/Pdf #310

Uh oh!

densen2014 Jan 11, 2023 Collaborator

1.新建工程

2.样式表和Javascript 引用

3.添加增加命名空间引用到 _Imports.razor 文件中

4.增加 BootstrapBlazorRoot 组件到 App.razor 文件中

5.添加 BootstrapBlazor服务/ FreeSql 数据服务/ ImportExportsService 导出服务 到 Program.cs 文件中

6. 导出服务 Service/ImportExportsService.cs

7. 数据实体类 Data/WeatherForecast.cs

8. 界面和代码 Pages/Index.razor

9. 界面和代码 Pages/ImpExpII.razor

项目源码

AlexChow

Replies: 1 comment

Uh oh!

densen2014 Jan 16, 2023 Collaborator Author

densen2014
Jan 11, 2023
Collaborator

3.添加增加命名空间引用到 `_Imports.razor` 文件中

5.添加 BootstrapBlazor服务/ FreeSql 数据服务/ ImportExportsService 导出服务到 `Program.cs` 文件中

densen2014
Jan 16, 2023
Collaborator Author