.net core webapi 导出excel 功能

最新推荐文章于 2024-05-31 22:26:31 发布

Grant丶

最新推荐文章于 2024-05-31 22:26:31 发布

阅读量902

点赞数 1

分类专栏： c# .net core 文章标签： .net core vue

本文链接：https://blog.csdn.net/Granters/article/details/123348722

版权

c# .net core 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

.net core webapi 导出excel 功能

1.nuget安装 EPPlus.Core
2.创建ExcelHelper类：

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace StoveApi.Model
{
    public class ExcelHelper
    {
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList">数据</param>
        /// <param name="headers">表头</param>
        /// <returns></returns>
        public static string CreateExcelFromList<T>(List<T> dataList, List<string> headers)
        {
            string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
            if (!Directory.Exists(sWebRootFolder))
            {
                Directory.CreateDirectory(sWebRootFolder);
            }
            string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            var path = Path.Combine(sWebRootFolder, sFileName);
            FileInfo file = new FileInfo(path);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(path);
            }
            using (ExcelPackage package = new ExcelPackage(file))
            {
                //创建sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
                worksheet.Cells.LoadFromCollection(dataList, true);
                //表头字段
                for (int i = 0; i < headers.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = headers[i];
                }
                package.Save();
            }
            return path;
        }
    }
}

3.创建ExportExcelController：
1).get方式：

[Route("ExportExcel")]
[HttpGet]
public IActionResult ExportExcel(){
    var personList=new List<Person>(){
        new Person(){id=1,name="wufan",age=25},
        new Person(){id=2,name="you",age=26}
    }
    var heads=new List<string>() { "编号", "姓名", "年龄"};
    var excelFilePath = ExcelHelper.CreateExcelFromList(personList,heads)
    return File(
        new FileStream(excelFilePath, FileMode.Open),
        "application/octet-stream",
        "ExcelNameHere.xlsx"
    );
}

2).post方式：

[Route("ExportExcel")]
[HttpPost]
public IActionResult ExportExcel(List<Person> personList){
    var heads=new List<string>() { "编号", "姓名", "年龄"};
    var excelFilePath = ExcelHelper.CreateExcelFromList(personList,heads)

    return File(
        new FileStream(excelFilePath, FileMode.Open),
        "application/octet-stream",
        "ExcelNameHere.xlsx"
    );
}

4.vue axios调用：

this.$axios({
        method: 'post', //或者get
        responseType: 'blob',
        url: this.url.exportExcel,
        data: this.formData
      }).then(function (res) {
        if (!res) return
        let blob = new Blob([res.data], {type: 'application/vnd.ms-excel;charset=utf-8'})
        let url = window.URL.createObjectURL(blob);
        let aLink = document.createElement("a");
        aLink.style.display = "none";
        aLink.href = url;
        aLink.setAttribute("download", "excel.xlsx");
        document.body.appendChild(aLink);
        aLink.click();
        document.body.removeChild(aLink); 
        window.URL.revokeObjectURL(url); 
      });