.NET Core使用NPOI导出多语言Excel-Base64加密-解密下载-带源码

最新推荐文章于 2024-05-12 14:21:59 发布

猫猫.net

最新推荐文章于 2024-05-12 14:21:59 发布

阅读量303

点赞数 8

分类专栏： aps.net core 文章标签： .netcore oracle 后端

本文链接：https://blog.csdn.net/qq_58734875/article/details/137274258

版权

aps.net core 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

实现了前端通过调取导出接口，返回Base64加密地址，再去解密，暂停下载的效果

头部中文繁体日语英语

效果图：

接下来是实现代码

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="bs">id</param>
        /// <returns></returns>
        [HttpPost]
        [Route("ExportList")]
        public async Task<ApiResult> ExportList(BaseQuery<BasicSettingsQuery> query)
        {
            try
            {
                //判斷當前頁
                if (query.oData.pageIndex <= 0)
                {
                    query.oData.pageIndex = 1;
                }

                if (query.oData.pageSize <= 0)
                {
                    query.oData.pageSize = GlobalConstants.MIN_PAGE_NUM;
                }

                if (query.oData.pageSize > GlobalConstants.MAX_PAGE_NUM)
                {
                    query.oData.pageSize = GlobalConstants.MAX_PAGE_NUM;
                }
                query.oData.company_id = GetCompany();

                //查出DataTable格式的数据
                var data = await _overtimeService.QueryExportData(query.oData);
                DataTable table = data;

                //需要加根据语言返回对应的字段(语言名称)
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("ST_PREFIX_NO", MultilingualCodeEnum.ST_PREFIX_NO.GetMessage());
                dic.Add("DT_ACTION_DATE", MultilingualCodeEnum.DT_ACTION_DATE.GetMessage());
                dic.Add("ST_STAFF_NAME", MultilingualCodeEnum.ST_STAFF_NAME.GetMessage());
                dic.Add("ST_DEPARTMENT_ID", MultilingualCodeEnum.ST_DEPARTMENT_ID.GetMessage());
                dic.Add("ST_STOCK_ID", MultilingualCodeEnum.ST_STOCK_ID.GetMessage());
                dic.Add("NO_TOT_HOUR", MultilingualCodeEnum.NO_TOT_HOUR.GetMessage());
                dic.Add("NO_MARK", MultilingualCodeEnum.NO_MARK.GetMessage());

                if (table == null)
                {
                    // 導出出錯，請聯繫管理員
                    return ApiResult.sysError(ApplicationErrorCodeEnum.FAILURE.GetMessage());

                }
                if (table.Rows.Count == 0)
                {
                    //  暫無數據可導出
                    return ApiResult.sysError(ApplicationErrorCodeEnum.FAILURE.GetMessage());
                }


                NPOIHelper npoi = new NPOIHelper();

                //导出文件存放地址
                string path = npoi.SaveFromDataTable(RemoveEnterCharFromTable(table), "OT", "/impexp/export/Overtime/", "sheet", null, dic);
                if (path == null)
                {
                    //为空
                    return ApiResult.sysError(ApplicationErrorCodeEnum.FAILURE.GetMessage());

                }
                //加密
                var a = EncryptionHelper.DouBase64Encode(path);
                if (a != null)
                {
                    // 获取 "Constants" 下的 "ApiUrl" 值
                    string apiUrl = _options.CurrentValue.ApiUrl;

                    //返回域名+下载文件的控制器/方法名传?加密数据
                    string url = apiUrl + "api/download/ddown?id=" + a;
                    //成功
                    return ApiResult.success(new { url = url });
                }
                else
                {
                    //失败
                    return ApiResult.sysError(ApplicationErrorCodeEnum.FAILURE.GetMessage());

                }

            }
            catch (Exception ex)
            {
                //導出出錯，請聯繫管理員
                _loggerContext.Error(ex.Message, "加班申请 導出出錯");

                return ApiResult.sysError(ApplicationErrorCodeEnum.FAILURE.GetMessage());

            }
        }


        /// <summary> 
        /// 刪除table的換行符 *** 
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private DataTable RemoveEnterCharFromTable(DataTable table)
        {
            if (table == null)
            {
                return null;
            }

            int rowLen = table.Rows.Count;
            int colLen = table.Columns.Count;

            for (int i = 0; i < rowLen; i++)
            {
                DataRow row = table.Rows[i];
                for (int j = 0; j < colLen; j++)
                {
                    string val = row[j] == null ? string.Empty : row[j].ToString();
                    if (!string.IsNullOrWhiteSpace(val))
                    {
                        string newVal = val.Replace("\r", " ").Replace("\n", " ").Trim();
                        if (val != newVal)
                        {
                            row[j] = newVal;
                        }
                    }
                }
            }
            return table;
        }

SaveFromDataTable()方法

   /// <summary>
   ///  從DataTable中讀取數據放入Excel中，并保存到服務器指定文件夾中去掉了info
   /// </summary>
   /// <param name="table">導入的table集</param>
   /// <param name="fileName">文件名</param>
   /// <param name="saveDir">保存目錄</param>
   /// <param name="info">返回錯誤信息</param> 
   /// <param name="sheetName">sheet名稱</param>
   /// <param name="hideCell">隱藏列</param>
   /// <param name="dic">
   /// 轉化表頭（中文轉英文）
   /// (string, string) （待轉化的字段，轉化為的字段）
   /// </param>
   /// <returns>返回路徑</returns>
   public string SaveFromDataTable(DataTable dataSet, string fileName, string saveDir, string sheetName = null, int[] hideCell = null, Dictionary<string, string> dic = null)
   {
       try
       {
           //初始化工作簿實例
           HSSFWorkbook workbook = new HSSFWorkbook();

           NPOICommonHelper.SetWorkbook(workbook, dataSet, sheetName, hideCell, dic);

       

           string result = NPOISaveHelper.Save(workbook, fileName, saveDir);

           return result;
       }
       catch (ThreadAbortException)
       {
           //終止線程，不做處理
           return null;
       }
       catch (Exception ex)
       {
           return null;
       }
   }

Save()方法

   /// <summary>
   /// 保存服務端的默認路徑
   /// </summary>
   private static string directory = "/impexp/export/";

   /// <summary>
   /// 保存到服務器指定目錄
   /// </summary>
   /// <param name="workbook">數據</param>
   /// <param name="fileName">文件名</param>
   /// <param name="saveDir">保存目錄</param>
   /// <returns>返回目錄路徑。如果為null/空，則保存失敗，需看日誌文件瞭解情況</returns>
   public static string Save(HSSFWorkbook workbook, string fileName, string saveDir)
   {
       string result = string.Empty;
       //導出
       using (MemoryStream ms = new MemoryStream())
       {
           workbook.Write(ms);
           result = SaveByWeb(ms, DateTime.Now.ToString("yyyyMMddHHmmssFFF")+"-"+ fileName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssFFF") + ".xls", saveDir);
           workbook = null;
       }
       return result;
   }

api/download/ddown 是一个接口接下来是download控制器的代码

using System;
using System.IO;
using Microsoft.AspNetCore.Mvc;
using JWProject.Utils.NPOI.common;

namespace JWProject.Api.Controllers
{
    /// <summary>
    /// 下载
    /// </summary>
    [CtmApiExplorerSettings(GroupName = "特别功能接口")]
    [Route("api/[controller]")]
    [ApiController]
    public class DownloadController : Controller
    {
        private readonly IWebHostEnvironment _webHostEnvironment;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="webHostEnvironment"></param>
        public DownloadController(IWebHostEnvironment webHostEnvironment)
        {
         
            _webHostEnvironment = webHostEnvironment;
        }

        /// <summary>
        /// 下载数据
        /// </summary>
        /// <param name="bs">id</param>
        /// <returns></returns>
        [HttpGet]
        [Route("ddown")]
        public async Task<ActionResult> ddown(string id)
        {
            try
            {
                if (!string.IsNullOrEmpty(id))
                {
                    //将地址解密
                    string value = EncryptionHelper.DouBase64Decode(id);
                    return await downloadfile(value);
                }
                else
                {
                    Response.StatusCode = 404;
                }
            }
            catch
            {
                Response.StatusCode = 404;
            }
            return Content("ok");
        }

        /// <summary>
        /// 下载文件
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        private async Task< ActionResult> downloadfile(string path)
        {
            try
            {
                if (!string.IsNullOrEmpty(path))
                {
                    string[] fileInfo = path.Split('.');
                    //判断文件格式
                    if (!GetSuffix(fileInfo[fileInfo.Length - 1]))
                    {
                        Response.StatusCode = 404;
                        // return;
                    }
                    else
                    {
                        string allPath = _webHostEnvironment.ContentRootPath + path;
                        if (FileHelper.CheckFile(allPath))
                        {
  //下载帮助文件
                            bool succ = await DownloadHelper.DownloadFileAsync(Request, allPath, 1024 * 1024 * 12);//10M/s:12000000
                            if (!succ)
                            {
                                Response.StatusCode = 404;
                            }
                        }
                        else
                        {
                            Response.StatusCode = 404;
                        }
                    }
                }
                else
                {
                    Response.StatusCode = 404;
                }
            }
            catch
            {
                Response.StatusCode = 404;
            }
            return Content("ok");
        }

        /// <summary>
        /// 文件后缀名类型
        /// </summary>
        /// <param name="suffix"></param>
        /// <returns></returns>
        private bool GetSuffix(string suffix)
        {
            string value = suffix.ToLower();
            bool result = true;
            switch (value)
            {
                case "asax":
                case "ascx":  //Global.asax 
                case "ashx":
                case "asmx":  //WebService.asmx  
                case "aspx":  //index.aspx

                case "browser":

                case "cd":
                case "config":
                case "cshtml":
                case "cs":  //index.aspx.cs 
                case "css":  //

                case "dbml":
                case "dll":  //LinkOraDB.dll

                case "htm":  //FileNotFound.htm
                case "html":

                case "js":  //kindeditor.js

                case "master":  //mainpage.master

                case "xml":  //MenuCHI.xml
                case "xsd":  //dsLabel.xsd
                case "xslt":

                case "rdlc": //
                case "resx": //lang.en-us.resx
                case "rpt":  //printlbl.rpt

                case "skin":
                case "sitemap":
                case "swf":  //uploadify.swf
                case "svc":

                    //case "ttf":  //code128.ttf
                    result = false;
                    break;
                default:
                    result = true;
                    break;
            }
            return result;
        }

    }
}

写下来是 //下载帮助文件 DownloadHelper.cs

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Primitives;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace JWProject.Common.Helpers
{
    /// <summary>
    ///downloadHelper 的摘要说明
    /// </summary>
    public class DownloadHelper
    {
        public DownloadHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 下載數據到指定文件中
        /// </summary>
        /// <param name="context">当前请求的HttpContext</param>
        /// <param name="filePath">绝对路徑</param>
        /// <param name="fileName">客户端保存的文件名</param>
        public static async Task DownloadFileAsync(HttpRequest context, string filePath, string fileName)
        {
            try
            {
                HttpContext httpContext = context.HttpContext;
                FileInfo fileInfo = new FileInfo(filePath);
                FileStream myFile = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                byte[] buffur = new byte[myFile.Length];
                myFile.Read(buffur, 0, (int)myFile.Length);

                httpContext.Response.Clear();
                httpContext.Response.Headers.Clear();
                httpContext.Response.Headers["Content-Disposition"] = "attachment;filename=" + fileName;
                httpContext.Response.Headers["Content-Length"] = fileInfo.Length.ToString();
                httpContext.Response.Headers["Content-Transfer-Encoding"] = "binary";
                httpContext.Response.ContentType = "application/octet-stream";
                //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                await httpContext.Response.Body.WriteAsync(buffur, 0, (int)myFile.Length);
                httpContext.Response.Body.Flush();
                //HttpContext.Current.Response.End();
            }
            catch (Exception ex) { }
        }


        #region 下載服務
        /**/
        /// <summary>
        /// 下载文件，支持大文件、续传、速度限制。支持续传的响应头Accept-Ranges、ETag，请求头Range 。
        /// Accept-Ranges：响应头，向客户端指明，此进程支持可恢复下载.实现后台智能传输服务（BITS），值为：bytes；
        /// ETag：响应头，用于对客户端的初始（200）响应，以及来自客户端的恢复请求，
        /// 必须为每个文件提供一个唯一的ETag值（可由文件名和文件最后被修改的日期组成），这使客户端软件能够验证它们已经下载的字节块是否仍然是最新的。
        /// Range：续传的起始位置，即已经下载到客户端的字节数，值如：bytes=1474560- 。
        /// 另外：UrlEncode编码后会把文件名中的空格转换中+（+转换为%2b），但是浏览器是不能理解加号为空格的，所以在浏览器下载得到的文件，空格就变成了加号；
        /// 解决办法：UrlEncode 之后, 将 "+" 替换成 "%20"，因为浏览器将%20转换为空格
        /// </summary>
        /// <param name="context">当前请求的HttpContext</param>
        /// <param name="filePath">下载文件的物理路径，含路径、文件名</param>
        /// <param name="speed">下载速度：每秒允许下载的字节数</param>
        /// <returns>true下载成功，false下载失败</returns>
        public static async Task<bool> DownloadFileAsync(HttpRequest context, string filePath, long speed)
        {
            HttpContext httpContext = context.HttpContext;
            httpContext.Response.Clear();
            bool ret = true;
            try
            {
                #region --验证：HttpMethod，请求的文件是否存在#region
                switch (httpContext.Request.Method.ToUpper())
                { //目前只支持GET和HEAD方法
                    case "GET":
                    case "HEAD":
                        break;
                    default:
                        httpContext.Response.StatusCode = 501;
                        return false;
                }
                if (!File.Exists(filePath))
                {
                    httpContext.Response.StatusCode = 404;
                    return false;
                }
                #endregion

                #region 定义局部变量#region 定义局部变量
                long startBytes = 0;
                long stopBytes = 0;
                int packSize = 1024 * 10; //分块读取，每块10K bytes
                string fileName = Path.GetFileName(filePath);
                FileStream myFile = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                BinaryReader br = new BinaryReader(myFile);
                long fileLength = myFile.Length;

                int sleep = (int)Math.Ceiling(1000.0 * packSize / speed);//毫秒数：读取下一数据块的时间间隔
                string lastUpdateTiemStr = File.GetLastWriteTimeUtc(filePath).ToString("r");
                string eTag = HttpUtility.UrlEncode(fileName, Encoding.UTF8) + lastUpdateTiemStr;//便于恢复下载时提取请求头;
                #endregion

                #region --验证：文件是否太大，是否是续传，且在上次被请求的日期之后是否被修改过
                if (myFile.Length > long.MaxValue)
                {//-------文件太大了-------
                    httpContext.Response.StatusCode = 413;//请求实体太大
                    return false;
                }

                if (httpContext.Request.Headers.ContainsKey("If-Range"))//对应响应头ETag：文件名+文件最后修改时间
                {
                    //----------上次被请求的日期之后被修改过--------------
                    StringValues ifrange = string.Empty;
                    httpContext.Request.Headers.TryGetValue("If-Range", out ifrange);
                    if (!string.IsNullOrWhiteSpace(ifrange) && ifrange.ToString().Replace("\"", "") != eTag)
                    {//文件修改过
                        httpContext.Response.StatusCode = 412;//预处理失败
                        return false;
                    }
                }
                #endregion

                try
                {
                    #region -------添加重要响应头、解析请求头、相关验证
                    httpContext.Response.Clear();

                    if (httpContext.Request.Headers.ContainsKey("Range"))
                    {//------如果是续传请求，则获取续传的起始位置，即已经下载到客户端的字节数------
                        httpContext.Response.StatusCode = 206;//重要：续传必须，表示局部范围响应。初始下载时默认为200
                        StringValues rangeStr = string.Empty;
                        httpContext.Request.Headers.TryGetValue("Range", out rangeStr);
                        if (string.IsNullOrWhiteSpace(rangeStr))
                        {
                            return false;
                        }
                        string[] ranges = rangeStr.ToString().Split(new char[] { '=', '-' });//"bytes=1474560-"
                        startBytes = Convert.ToInt64(ranges[1]);//已经下载的字节数，即本次下载的开始位置  
                        if (startBytes < 0 || startBytes >= fileLength)
                        {//无效的起始位置
                            return false;
                        }
                    }

                    //httpContext.Response.Buffer = false;
                    httpContext.Response.Headers["Content-MD5"] = EncryptionHelper.Md532(filePath);//用于验证文件
                    httpContext.Response.Headers["Accept-Ranges"] = "bytes";//重要：续传必须
                    httpContext.Response.Headers["ETag"] = "\"" + eTag + "\"";//重要：续传必须
                    httpContext.Response.Headers["Last-Modified"] = lastUpdateTiemStr;//把最后修改日期写入响应                
                    httpContext.Response.ContentType = "application/octet-stream";//MIME类型：匹配任意文件类型
                    httpContext.Response.Headers["Content-Disposition"] = "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).Replace("+", "%20");
                    httpContext.Response.ContentLength = fileLength - startBytes;
                    httpContext.Response.Headers["Connection"] = "Keep-Alive";
                    //httpContext.Response.ContentEncoding = Encoding.UTF8;
                    if (startBytes > 0)
                    {//------如果是续传请求，告诉客户端本次的开始字节数，总长度，以便客户端将续传数据追加到startBytes位置后----------
                        httpContext.Response.Headers["Content-Range"] = string.Format("bytes {0}-{1}/{2}", startBytes, fileLength - 1, fileLength);
                    }
                    #endregion

                    #region -------向客户端发送数据块-------------------
                    br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                    int maxCount = (int)Math.Ceiling((fileLength - startBytes + 0.0) / packSize);//分块下载，剩余部分可分成的块数
                    for (int i = 0; i < maxCount && !httpContext.RequestAborted.IsCancellationRequested; i++)
                    {
                        //客户端中断连接，则暂停
                        if (maxCount - 1 == i)
                        {
                            packSize = (int)(fileLength - startBytes - i * packSize);
                        }
                        await httpContext.Response.Body.WriteAsync(br.ReadBytes(packSize), 0, packSize);
                        await httpContext.Response.Body.FlushAsync();
                        //httpContext.Response.Body.Flush();
                        if (sleep > 1) Thread.Sleep(sleep);
                    }
                    #endregion
                }
                catch (Exception ex)
                {
                    ret = false;
                }
                finally
                {
                    br.Close();
                    myFile.Close();
                }
            }
            catch (Exception ex)
            {
                ret = false;
            }
            return ret;
        }

        #endregion
    }
}

接下来设置多语言

上传到了资源自行下载（LangCodeEnum和CodeEnumeration）语言文件的引用文件

引用的LangCodeEnum在下方创建对应文件.cs


    /// <summary>
    /// 多语言码设置
    /// </summary>
    public abstract class LangCodeEnum : CodeEnumeration
    {
        public abstract string GetMessage();
    }

接下来就是设置多语言的MultilingualCodeEnum.cs代码

using GLProject.Common.Extensions;
using JWProject.Common.App_GlobalResources;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JWProject.Common.Enums.Exceptions
{
    /// <summary>
    /// 多语言
    /// </summary>
    public class MultilingualCodeEnum : LangCodeEnum
    {
        public static MultilingualCodeEnum ST_STAFF_NAME = new MultilingualCodeEnum("0", "职员", "Staff", "職員");
        public static MultilingualCodeEnum ST_PREFIX_NO = new MultilingualCodeEnum("1", "编号", "Code", "コード");
        public static MultilingualCodeEnum DT_ACTION_DATE = new MultilingualCodeEnum("2", "日期", "Date", "日期");
        public static MultilingualCodeEnum ST_DEPARTMENT_ID = new MultilingualCodeEnum("3", "部门", "Department", "部門");
        public static MultilingualCodeEnum ST_STOCK_ID = new MultilingualCodeEnum("4", "库存点", "Stock", "庫存點");
        public static MultilingualCodeEnum NO_TOT_HOUR = new MultilingualCodeEnum("5", "总时数", "Hours", "總時數");
        public static MultilingualCodeEnum NO_MARK = new MultilingualCodeEnum("6", "状态", "Status", "ステータス");




        public MultilingualCodeEnum(string code, string Cn, string En, string Ja)
        {
            this.Code = code;
            this.Cn = Cn;
            this.En = En;
            this.Ja = Ja;
        }

        /**
        * 编号
        */
        private string Code;

        /**
         * 中文
         */
        private string Cn;

        /**
         * 英文
         */
        private string En;

        /**
        * 日语
        */
        private string Ja;


        public override string GetMessage()
        {
            LangType langType = LangType.FromLanguageCodes<LangType>(lang.LangType);
            if (langType == LangType.English)
            {
                return En;
            }
            else if (langType == LangType.Japanese)
            {
                return Ja;
            }
            else if (langType == LangType.Chinese)
            {
                return Cn.ToSimplified();
            }
            else
            {
                return Cn.ToTraditional();
            }
        }

    }



}

测试导出文件返回给前端加密url: