实战 通用导入导出服务(Table组件)

[densen2014]

1.本文主要内容为给Blazor工程构建通用导入导出服务类

基础工程构建取自 [BootstrapBlazor实战 10分钟编写数据库维护] 项目,使用到的orm为freesql,导入导出库为Magicodes.IE,还有封装了Table组件的内存数据服务LazyHeroDataService的Densen.FreeSql.Extensions.BootstrapBlazor库.

主要实现功能(Table组件):

• [编辑][添加][删除]数据
• [导出]现在表格数据
• [清空]数据
• [选择文件]上传并导入
• [清空]数据后再选择[导入]
• [自由编辑]类似Excel模式编辑表格

最终运行效果

2.使用 nuget.org 进行必须的组件安装

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 Densen.FreeSql.Extensions.BootstrapBlazor

Densen.FreeSql.Extensions.BootstrapBlazor 库封装了Table组件的内存数据服务LazyHeroDataService,因篇幅关系不展开介绍,需要了解的朋友请自行查看源码 https://github.com/densen2014/Densen.Extensions/blob/master/Blazor/Services/LazyHeroDataService.cs

3.项目添加 Service 文件夹,添加 ImportExportsService.cs 文件

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> ExportToExcel<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(),""); 
        }

    }
}

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

@using Blazor100.Service
@using AME.Services

5.添加ImportExportsService服务到 Program.cs 文件中

using Blazor100.Service;

builder.Services.AddTransient<ImportExportsService>();

添加封装了Table组件的内存数据服务LazyHeroDataService的DensenExtensions()
builder.Services.AddBootstrapBlazor(); 改为

builder.Services.AddDensenExtensions();

6.数据实体类

添加导入导出特性到 Data/WeatherForecast.cs 类文件中

完整文件

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

namespace b03sqlite.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; }
}

6.页面文件 Pages/ImpExp.razor

@page "/impexp"
@using b03sqlite.Data
<PageTitle>导入导出</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">

    <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.Info" Text="导出" IsAsync OnClickCallback="ExportAsync" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Danger" Text="清空" IsAsync OnClick="EmptyAll" />
        <TableToolbarButton TItem="WeatherForecast" Color="Color.Success" Text="模板" IsAsync OnClick="Export模板Async" />
    </TableToolbarTemplate>

</Table>

7.代码

@code{
    [Inject] Microsoft.AspNetCore.Hosting.IWebHostEnvironment? HostEnvironment { get; set; }
    [Inject] NavigationManager? navigationManager { get; set; }
    [Inject] ImportExportsService? importExportsService { get; set; }
    [Inject] ToastService? toastService { get; set; }
    [Inject] WeatherForecastService? ForecastService { get; set; }
    [Inject] LazyHeroDataService<WeatherForecast>? LazyHeroDataService { get; set; }

    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 override async Task OnInitializedAsync()
    //{
    //LazyHeroDataService!.Items = (await ForecastService!.GetForecastAsync(DateTime.Now)).ToList();
    //}
    protected async Task GetDatasAsync()
    {
        LazyHeroDataService!.Items = (await ForecastService!.GetForecastAsync(DateTime.Now)).ToList();
        await list1!.QueryAsync();
    }

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

    private Task IsExcelToggle()
    {
        IsExcel = !IsExcel;
        DoubleClickToEdit = !IsExcel;
        StateHasChanged();
        return Task.CompletedTask;
    }
    public async Task<bool> Export模板Async()
    {
        await 模板下载();
        return true;
    }

    private async Task 模板下载(List<WeatherForecast>? items = null)
    {
        var ufilename = LazyHeroDataService!.Items == null ? "模板" : "新数据";
        var sFileName = ufilename + ".xlsx";
        var filename = Path.Combine(UploadPath, ufilename);
        await importExportsService!.ExportToExcel(filename, items);
        toastService?.Success("提示", ufilename + "已生成");

        //* 流化到前端处理也可以,不一定要用文件形式下载.
        navigationManager?.NavigateTo($"Upload/{sFileName}", true);
        //下载后清除文件
        _ = Task.Run(() =>
        {
            Thread.Sleep(5000);
            System.IO.File.Delete(filename);
        });

    }

    public async Task<bool> ExportAsync(IEnumerable<WeatherForecast> Items)
    {
        await 模板下载(LazyHeroDataService!.Items);
        return true;
    }

    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 相关联的弹窗
        await 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()));

    }


}

8.试试Index.razor添加Tab组件看看效果

<Tab>
    <TabItem Text="导入导出">
        <ImpExp />
    </TabItem>
    <TabItem Text="数据维护">
       ... 原始页面内容
    </TabItem>
</Tab>

9.运行逐一测试功能

• 点击[随机数据] 生成数据
• [编辑][添加][删除]数据
• [导出]现在表格数据
• [清空]数据
• [选择文件]上传并导入
• [清空]数据后再选择[导入]
• [自由编辑]类似Excel模式编辑表格
• [导出PDF] 自行扩展 importExportsService.ExportToExcel(filename, items,ExportType.Pdf);
• [导出Word] 自行扩展 importExportsService.ExportToExcel(filename, items,ExportType.Word);
• [导出Html] 自行扩展 importExportsService.ExportToExcel(filename, items,ExportType.Html);

项目源码

Github

[m916423330]

CV一条龙系列教程