ASP.NET Core 导入导出Excel.xlsx文件实例

Execl报表处理 专栏收录该内容
1 篇文章 0 订阅

ASP.NET Core 使用EPPlus.Core导入导出Excel.xlsx 文件,EPPlus.Core支持Excel 2007/2010 .xlsx文件导入导出,可以运行在Windows, Linux和Mac。

EPPlus.Core 是基于EPPlus 更改而来,在Linux 下需要安装libgdiplus 。

EPPlus:http://epplus.codeplex.com/

EPPlus.Core:https://github.com/VahidN/EPPlus.Core

下面在ASP.NET Core 中导入导出Excel xlsx 文件。(参考:https://www.jb51.net/article/100509.htm)

1.新建项目=> WebAppTest

2.导入NuGet包:EPPlus.Core


3.新建控制器:XlsxController,代码如下:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common.Helper;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;

namespace WebAppTest.Controllers
{
    public class XlsxController : Controller
    {
        /// <summary>
        /// 通过依赖注入获取HostingEnvironment,对应可以获取程序的相关目录及属性。
        /// </summary>
        private IHostingEnvironment _hostingEnvironment;

        public XlsxController(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 公用路径
        /// </summary>
        /// <returns></returns>
        private Tuple<string, string> GetTuple()
        {
            //string sWebRootFolder = _hostingEnvironment.WebRootPath + @"\execl";
            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{Guid.NewGuid()}.xlsx";
            return Tuple.Create(sWebRootFolder, sFileName);
        }


        #region 封装前-导出、导入Excel.xlsx文件
        // 导出xlsx文件
        public IActionResult Export()
        {
            string sWebRootFolder = GetTuple().Item1;
            string sFileName = GetTuple().Item2;
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // 添加worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
                //添加头
                worksheet.Cells[1, 1].Value = "ID";
                worksheet.Cells[1, 2].Value = "Name";
                worksheet.Cells[1, 3].Value = "Url";
                //添加值
                worksheet.Cells["A2"].Value = 1000;
                worksheet.Cells["B2"].Value = "LineZero";
                worksheet.Cells["C2"].Value = "http://www.cnblogs.com/linezero/";

                worksheet.Cells["A3"].Value = 1001;
                worksheet.Cells["B3"].Value = "LineZero GitHub";
                worksheet.Cells["C3"].Value = "https://github.com/linezero";
                worksheet.Cells["C3"].Style.Font.Bold = true;

                package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }

        [HttpPost]
        public IActionResult Import(IFormFile excelfile)
        {
            string sWebRootFolder = GetTuple().Item1;
            string sFileName = GetTuple().Item2;
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            try
            {
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    excelfile.CopyTo(fs);
                    fs.Flush();
                }
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int ColCount = worksheet.Dimension.Columns;
                    bool bHeaderRow = true;
                    for (int row = 1; row <= rowCount; row++)
                    {
                        for (int col = 1; col <= ColCount; col++)
                        {
                            if (bHeaderRow)
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                            else
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                        }
                        sb.Append(Environment.NewLine);
                    }
                    return Content(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }
        #endregion

        #region 封装后-导出、导入Excel.xlsx文件
        public IActionResult Export2()
        {
            string dt1 = DateTime.Now.ToString("yyyy-MM-dd/HH:mm:ss.fff");
            string path = GetTuple().Item1;
            ExcelHelper.Export(GetDic(), path, out Tuple<string, string> t);
            string dt2 = DateTime.Now.ToString("yyyy-MM-dd/HH:mm:ss.fff");
            //return Json(new {
            //    msg = "ok",
            //    data = $"开始时间:{dt1},结束时间:{dt2}"
            //});

            return File(t.Item1, t.Item2);
        }

        private Dictionary<string, DataTable> GetDic()
        {
            DataTable dt = new DataTable("cart");
            DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("point", Type.GetType("System.Int16"));
            DataColumn dc3 = new DataColumn("number", Type.GetType("System.Int16"));
            DataColumn dc4 = new DataColumn("totalpoint", Type.GetType("System.Int64"));
            DataColumn dc5 = new DataColumn("prizeid", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            //以上代码完成了DataTable的构架,但是里面是没有任何数据的  
            for (int i = 0; i <= 100000; i++)
            {
                DataRow dr = dt.NewRow();
                dr["prizename"] = $"娃娃{i}";
                dr["point"] = 10;
                dr["number"] = 1;
                dr["totalpoint"] = 10;
                dr["prizeid"] = $"t00{i}";
                dt.Rows.Add(dr);
            }
            //填充了100000条相同的记录进去  

            var dic = new Dictionary<string, DataTable>();

            for (int i = 0; i <= 10; i++)
            {
                dic.Add($"{dt.TableName}-{i}", dt);
            }

            return dic;
        }

        [HttpPost]
        public IActionResult Import2(IFormFile excelFile)
        {
            string sWebRootFolder = GetTuple().Item1;
            try
            {
                ExcelHelper.Import(excelFile, sWebRootFolder, out string content);
                return Content(content);
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        } 
        #endregion
    }
}
4.添加页面视图:Index.cshtml,代码如下:
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ASP.NET Core 导入导出Excel.xlsx 文件</title>
</head>
<body>
    <h2>ASP.NET Core 导入导出Excel.xlsx 文件</h2>
    <fieldset>
        <figure>封装前</figure>
        <a asp-action="Export">导出Excel</a>
        <hr />
        <form enctype="multipart/form-data" method="post" asp-action="Import">
            <input type="file" name="excelfile" />
            <input type="submit" value="导入" />
        </form>
    </fieldset>

    <fieldset>
        <figure>封装后</figure>
        <a asp-action="Export2">导出Excel</a>
        <hr />
        <form enctype="multipart/form-data" method="post" asp-action="Import2">
            <input type="file" name="excelfile" />
            <input type="submit" value="导入" />
        </form>
    </fieldset>
</body>
</html>

5.封装后的ExcelHelper.cs代码如下:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Microsoft.AspNetCore.Http; //IFormFile
using OfficeOpenXml;

namespace Common.Helper
{
    /// <summary>
    /// Excel导入导出助手
    /// NuGet:EPPlus.Core
    /// </summary>
    public sealed class ExcelHelper
    {
        //参考:https://www.jb51.net/article/100509.htm

        private ExcelHelper() { }

        /// <summary>
        /// Excel文件 Content-Type
        /// </summary>
        private const string Excel = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        /// <summary>
        /// Excel导出
        /// </summary>
        /// <param name="keyValuePairs">字典表【名称,数据】</param>
        /// <param name="sWebRootFolder">网站根文件夹</param>
        /// <param name="tuple">item1:The virtual path of the file to be returned.|item2:The Content-Type of the file</param>
        public static void Export(Dictionary<string,DataTable> keyValuePairs,string sWebRootFolder,out Tuple<string,string> tuple)
        {
            if (string.IsNullOrWhiteSpace(sWebRootFolder))
                tuple = Tuple.Create("", Excel);

            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{FormatGuid.GetGuid(FormatGuid.GuidType.N)}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                foreach (var item in keyValuePairs)
                {
                    string worksheetTitle = item.Key; //表名称
                    var dt = item.Value; //数据表

                    // 添加worksheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetTitle);

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (i==0)
                            {
                                //添加表头
                                worksheet.Cells[1, j+1].Value = dt.Columns[j].ColumnName;
                                worksheet.Cells[1, j+1].Style.Font.Bold = true;
                            }
                            else
                            {
                                //添加值
                                worksheet.Cells[i+1,j+1].Value = dt.Rows[i][j].ToString();
                            }
                        }
                    }
                }
                package.Save();
            }
            tuple = Tuple.Create(sFileName, Excel);
        }

        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="excelFile">Excel文件</param>
        /// <param name="sWebRootFolder">文件存储路径</param>
        ///  <param name="content">显示内容</param>
        /// <param name="isShow">是否显示内容</param>
        public static void Import(IFormFile excelFile,string sWebRootFolder,out string content, bool isShow = false)
        {
            if (string.IsNullOrWhiteSpace(sWebRootFolder))
                content = string.Empty;

            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{FormatGuid.GetGuid(FormatGuid.GuidType.N)}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));

            using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
            {
                excelFile.CopyToAsync(fs);
                fs.Flush();
            }

            if (isShow)
            {
                //导出单个工作表sheet
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int colCount = worksheet.Dimension.Columns;
                    bool bHeaderRow = true;
                    for (int row = 1; row <= rowCount; row++)
                    {
                        for (int col = 1; col <= colCount; col++)
                        {
                            if (bHeaderRow)
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                            else
                                sb.Append(worksheet.Cells[row, col].Value.ToString() + "\t");
                        }
                        sb.Append(Environment.NewLine);
                    }
                    content = sb.ToString();
                }
            }
            else
            {
                content = string.Empty;
            }
        }

    }
}

注:同上NuGet包导入:EPPlus.Core;

视图界面显示如下图所示:


上面代码中模拟了10w行 X 10张表的数据,导出如下:




  • 2
    点赞
  • 3
    评论
  • 13
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值