Asp .Net Core Excel导入和导出

小世界的野孩子

发布于 2019-09-11 14:54:38

3.3K0

发布于 2019-09-11 14:54:38

文章被收录于专栏：.Net、.Net Core 、Docker.Net、.Net Core 、Docker

ASP .Net Core使用EPPlus实现Api导入导出，这里使用是EPPlus 4.5.2.1版本，.Net Core 2.2。在linux上运行的时候需要安装libgdiplus 。

下面我们看下如何实现导出导入的功能。

新建项目ASP.NET Core Web Api 、添加Test类

public class Test
{
   public int Id { get; set; }
   public string Name { get; set; }
   public int Age { get; set; }
}

导出Excel文件

新建控制器ExecelController

using System;

using System.Collections.Generic;

using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;

namespace WebApplication5.Controllers
{
[ApiController]
    public class ExecelController : ControllerBase
    {

        [Route("api/Execel/TOExecel")]
        public IActionResult TOExecel()
        {
            var list = new List<Test>();

            list.Add(new Test()
            {
                Id = 1,
                Name = "Test",
                Age = 22,

            });

            byte[] fileContents;
            using (ExcelPackage package = new ExcelPackage())
            {

                 
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(".Net Core 导出");

                 
                worksheet.Cells[1, 1].Value = "序号";
                worksheet.Cells[1, 2].Value = "Id";
                worksheet.Cells[1, 3].Value = "名称";
                worksheet.Cells[1, 4].Value = "年龄";

                int i = 2;

                foreach (var item in list)
                {
                    worksheet.Cells["A" + i].Value = i - 1;
                   worksheet.Cells["B" + i].Value = item.Id;
                    worksheet.Cells["C" + i].Value = item.Name;
                    worksheet.Cells["D" + i].Value = item.Age;
                    i = i + 1;
                }

                fileContents = package.GetAsByteArray();
                if (fileContents == null || fileContents.Length == 0)
                {
                    return NotFound();
                }
            }
            return File(fileContents, "application/ms-excel", $"{Guid.NewGuid().ToString()}.xlsx");
        }
}
}

导入Excel文件

[Route("api/Execel/Import")]
        public async Task<IActionResult> Import(IFormFile excelFile)
        {

            var msg = "";
            if (excelFile == null || excelFile.Length <= 0)
            {
                msg = "请选择导入文件!";
                return Ok(msg);
            }

 
            if (!Path.GetExtension(excelFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                msg = "请选择导入文件为.xlsx的后缀名!";
                return Ok(msg);
            }

            try
            {
                using (var stream = new MemoryStream())
                {
                    await excelFile.CopyToAsync(stream);

                     using (var package = new ExcelPackage(stream))
                    {

                        StringBuilder sb = new StringBuilder();
                        ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                        int rowCount = worksheet.Dimension.Rows;
                        int ColCount = worksheet.Dimension.Columns;
                        bool bHeaderRow = true;

 
                        for (int row = 2; row <= rowCount; row++)
                        {
                            Test model = new Test();
                            for (int col = 1; col <= ColCount; col++)
                            {
                                if (bHeaderRow)
                                {
                                    switch (col)
                                    {
                                        case 1:
                                            model.Id = int.Parse(worksheet.Cells[row, col].Value.ToString());
                                            break;
                                        case 2:
                                            model.Name = worksheet.Cells[row, col].Value.ToString();
                                            break;
                                         case 3:
                                            model.Age = int.Parse(worksheet.Cells[row, col].Value.ToString());
                                            break;
                                    }
                                }

                                else
                                {
                                    switch (col)
                                    {
                                        case 1:
                                            model.Id = int.Parse(worksheet.Cells[row, col].Value.ToString());
                                            break;
                                        case 2:
                                            model.Name = worksheet.Cells[row, col].Value.ToString();
                                            break;

                                         case 3:
                                            model.Age = int.Parse(worksheet.Cells[row, col].Value.ToString());
                                           break;
                                    }
                                }
                            }

                            //插入model即可
                        }
                    }
                }
                msg = "导入成功!";
                return Ok(msg);
            }

            catch (Exception ex)
            {
                msg = ex.Message;
                return Ok(msg);
            }
        }

　　按照格式导入即可完成导入功能。

　　这里导出是通过流的方式进行导出的，一般来说也可以先把Excel保存到服务器，然后直接通过Url访问服务器的文件的地址就可以实现在线下载。通过流导出的话就不需要去访问服务器文件了，访问接口查询数据就直接导出了。

在导入的时候目前测试仅仅支持.xlsx格式的，.xls格式的Excel在基于流创建ExcelPackage类的新实例的时候会报为空的错误。var package = new ExcelPackage(stream)还有待修改。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-05-09 ，如有侵权请联系 cloudcommunity@tencent.com 删除

.net