.NET CORE 2.1 匯出excel檔案的兩種方法

最近在做 MVC 專案的時候遇到專案的匯出，下面總結下兩種匯出到excel 的方法

第一種方法： 將檔案寫到本地，然後返回這個File 或者返回這個 File 的絕對地址

　　 其中  _hostingEnvironment  可以在全域性讀取的配置檔案的工具類 Globals 中配置

 　　直接上程式碼：

private IHostingEnvironment _hostingEnvironment;
        public XlsxController(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }
        public IActionResult ExportData(int programmeId)
        {
            //獲取方案名稱
            var programmeName = ProgrammeService.GetProgrammeNameById(programmeId);
            //獲取列表內容
            var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId);
            var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel
            {
                UserName = a.UserName,
                Phone = a.Phone,
                EnrolTime = a.EnrolTimeStr
            }).ToList();

            var sWebRootFolder = _hostingEnvironment.WebRootPath;

            var sFileName = $"XXX-{programmeName}.xlsx";

            var file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (var package = new ExcelPackage(file))
            {

                //如果之前有同名的檔案先刪除然後重新建立 
                var count = package.Workbook.Worksheets.Count; if (count > 0)
                {
                    for (var i = 0; i < count; i++)
                    {
                        package.Workbook.Worksheets.Delete(i + 1);
                    }
                    package.File.Delete();
                }

                // 新增worksheet 
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

                //新增頭 
                worksheet.Cells[1, 1].Value = "姓名";
                worksheet.Cells[1, 2].Value = "手機號";
                worksheet.Cells[1, 3].Value = "報名時間";

                //新增值 
                for (var i = 0; i < data.Count; i++)
                {
                    worksheet.Cells[$"A{i + 2}"].Value = data[i].UserName;
                    worksheet.Cells[$"B{i + 2}"].Value = data[i].Phone;
                    worksheet.Cells[$"C{i + 2}"].Value = data[i].EnrolTime;
                }
                package.Save();
            }
            var returnFile = File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            returnFile.FileDownloadName = sFileName;
            return returnFile;
        }

 

第二種方法： 用寫流檔案的方法 將檔案寫入瀏覽器

 

　　這裡首先我們需要一個匯出的工具類 如下：

    /// <summary>
    /// 公共工具類
    /// </summary>
    public class CommonHelper
    {

        //獲取列名委託方法
        public delegate string GetColumnName(string columnName);

        #region 匯入匯出Excel相關

        /// <summary>
        /// 將泛型別集合List類轉換成DataTable
        /// </summary>
        /// <param name="list">泛型別集合</param>
        /// <returns>返回轉換後的DataTable</returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //生成DataTable的structure
            var dt = new DataTable();
            try
            {
                //檢查泛型實體是否為空
                if (entitys == null || entitys.Count < 1)
                {
                    return dt;
                }
                //取出第一個實體的所有Propertie
                var entityType = entitys[0].GetType();
                var entityProperties = entityType.GetProperties();
                for (var i = 0; i < entityProperties.Length; i++)
                {
                    dt.Columns.Add(entityProperties[i].Name);
                }
                //將所有entity新增到DataTable中
                foreach (object entity in entitys)
                {
                    //檢查所有的的實體都為同一型別
                    if (entity.GetType() != entityType)
                    {
                        throw new Exception("要轉換的集合元素型別不一致");
                    }
                    var entityValues = new object[entityProperties.Length];
                    for (var i = 0; i < entityProperties.Length; i++)
                    {
                        entityValues[i] = entityProperties[i].GetValue(entity, null);
                    }
                    dt.Rows.Add(entityValues);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            return dt;
        }

        /// <summary>
        /// 將dataTable轉換為Excel位元組流
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="getColumnName"></param>
        /// <returns></returns>
        public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表頭
            var row = sheet.CreateRow(0);

            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名稱,資料庫中欄位
                var columnName = dt.Columns[i].ColumnName;
                var convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);
            }

            //資料
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //轉為位元組陣列
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }

       #endregion
    }

　　

　　然後我們就可以匯出了

 

 　　　　/// <summary>
        /// 表格列名稱
        /// </summary>      
        private static string GetColumnNameForDetail(string columnName)
        {
            switch (columnName)
            {

                case "UserName":
                    return CommonConst.Column_Programme_Detail_UserName;
                case "Phone":
                    return CommonConst.Column_Programme_Detail_Phone;
                case "EnrolTime":
                    return CommonConst.Column_Programme_Detail_EnrolTime;
                default:
                    return String.Empty;
            }
        }

        /// <summary>
        /// 匯出excel
        /// </summary>
        [HttpGet]
        public void ExportData(int programmeId)
        {
            //獲取方案名稱
            var programmeName = ProgrammeService.GetProgrammeNameById(programmeId);
            //獲取列表內容
            var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId);
            var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel
            {
                UserName = a.UserName,
                Phone = a.Phone,
                EnrolTime = a.EnrolTimeStr
            }).ToList();

            var sFileName = string.Format(CommonConst.Export_Programme_Detail_Excel_Name, programmeName);
            var dataTable = CommonHelper.ListToDataTable(data);
            var result = CommonHelper.GetExcelForXLSX(dataTable, GetColumnNameForDetail);
            Response.ContentType = ResponseConfigure.ContentTypeExcel;
            SetResponseHeaderForDetail(sFileName);
            Response.Body.Write(result);
            Response.Body.Flush();
            Response.Body.Close();
        }

        /// <summary>
        /// 設定匯出的標頭內容
        /// </summary>
        /// <param name="fileName">匯出的檔名</param>
        private void SetResponseHeaderForDetail(string fileName)
        {
            Response.Headers.Add(ResponseConfigure.ContentDisposition,
                ResponseConfigure.Attachment + HttpUtility.UrlEncode(fileName));
        }