ASP.NET Core使用EPPlus操作Excel

1.前言

  本篇文章通過ASP.NET Core的EPPlus包去操作Excel(導入導出),其使用原理與NPOI類似,導出Excel的時候不需要電腦上安裝office,非常好用

2.使用

  新建一個ASP.NET Core Web應用程式(模型視圖控制器),還有一個類庫,SDK2.1版本,解決方案如下

3.在EPPlusCommon類庫中創建一個EPPlusHelper類,包括兩個方法,導入和讀取數據

  1 using OfficeOpenXml;
  2 using OfficeOpenXml.Style;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Data;
  6 using System.Drawing;
  7 using System.IO;
  8 using System.Text;
  9 namespace EPPlusCommon
 10 {
 11     public class EPPlusHelper
 12     {
 13         private static int i;
 14 
 15         /// <summary>
 16         /// 導入數據到Excel中
 17         /// </summary>
 18         /// <param name="fileName"></param>
 19         /// <param name="ds"></param>
 20         public static bool ImportExcel(string fileName, DataSet ds)
 21         {
 22             if (ds == null || ds.Tables.Count == 0)
 23             {
 24                 return false;
 25             }
 26             FileInfo file = new FileInfo(fileName);
 27             if (file.Exists)
 28             {
 29                 file.Delete();
 30                 file = new FileInfo(fileName);
 31             }
 32             //在using語句裡面我們可以創建多個worksheet，ExcelPackage後面可以傳入路徑參數
 33             //命名空間是using OfficeOpenXml
 34             using (ExcelPackage package = new ExcelPackage(file))
 35             {
 36                 foreach (DataTable dt in ds.Tables)
 37                 {
 38                     //創建工作表worksheet
 39                     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName);
 40                     //給單元格賦值有兩種方式
 41                     //worksheet.Cells[1, 1].Value = "單元格的值";直接指定行列數進行賦值
 42                     //worksheet.Cells["A1"].Value = "單元格的值";直接指定單元格進行賦值
 43                     worksheet.Cells.Style.Font.Name = "微軟雅黑";
 44                     worksheet.Cells.Style.Font.Size = 12;
 45                     worksheet.Cells.Style.ShrinkToFit = true;//單元格自動適應大小
 46                     for (int i = 0; i < dt.Rows.Count; i++)
 47                     {
 48                         for (int j = 0; j < dt.Columns.Count; j++)
 49                         {
 50                             worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString();
 51                         }
 52                     }
 53                     using (var cell = worksheet.Cells[1, 1, 1, dt.Columns.Count])
 54                     {
 55                         //設置樣式:首行居中加粗背景色
 56                         cell.Style.Font.Bold = true; //加粗
 57                         cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
 58                         cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;     //垂直居中
 59                         cell.Style.Font.Size = 14;
 60                         cell.Style.Fill.PatternType = ExcelFillStyle.Solid;  //背景顏色
 61                         cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//設置單元格背景色
 62                     }
 63                 }
 64                 //保存
 65                 package.Save();
 66             }
 67             return true;
 68         }
 69 
 70         /// <summary>
 71         /// 讀取Excel數據
 72         /// </summary>
 73         /// <param name="fileName"></param>
 74         public static string ReadExcel(string fileName)
 75         {
 76             StringBuilder sb = new StringBuilder();
 77             FileInfo file = new FileInfo(fileName);
 78             try
 79             {
 80                 using (ExcelPackage package = new ExcelPackage(file))
 81                 {
 82                     var count = package.Workbook.Worksheets.Count;
 83                     for (int k = 1; k <= count; k++)  //worksheet是從1開始的
 84                     {
 85                         var workSheet = package.Workbook.Worksheets[k];
 86                         sb.Append(workSheet.Name);
 87                         sb.Append(Environment.NewLine);
 88                         int row = workSheet.Dimension.Rows;
 89                         int col = workSheet.Dimension.Columns;
 90                         for (int i = 1; i <= row; i++)
 91                         {
 92                             for (int j = 1; j <= col; j++)
 93                             {
 94                                 sb.Append(workSheet.Cells[i, j].Value.ToString() + "\t");
 95                             }
 96                             sb.Append(Environment.NewLine);
 97                         }
 98                         sb.Append(Environment.NewLine);
 99                         sb.Append(Environment.NewLine);
100                     }
101                 }
102             }
103             catch (Exception ex)
104             {
105                 return "An error had Happen";
106             }
107             return sb.ToString();
108         }
109     }
110 }

代碼片段已經給出了一些註釋,對於Excel的更多樣式設置可以參考博客

4.新建一個ExcelController(用於讀取和導入Excel),代碼如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using EPPlusCommon;
using Microsoft.AspNetCore.Hosting;
using System.IO;
using EPPlusWeb.Models;

namespace EPPlusWeb.Controllers
{
    public class ExcelController : Controller
    {
        private readonly IHostingEnvironment _hosting;
        public ExcelController(IHostingEnvironment hosting)
        {
            _hosting = hosting;
        }
        public IActionResult Import()
        {
            string folder = _hosting.WebRootPath;
            string fileName = Path.Combine(folder, "Excel", "Test.xlsx");
            bool result = EPPlusHelper.ImportExcel(fileName, ExcelData.GetExcelData());
            string str = result ? "導入Excel成功:" + fileName : "導入失敗";
            return Content(str);
        }
        public IActionResult Read()
        {
            string folder = _hosting.WebRootPath;
            string fileName = Path.Combine(folder, "Excel", "Test.xlsx");
            string result = EPPlusHelper.ReadExcel(fileName);
            return Content(result);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace EPPlusWeb.Models
{
    public class ExcelData
    {
        public static DataSet GetExcelData()
        {
            DataSet ds = new DataSet();
            string[,] infos =
            {
                { "151100310001","劉備","男","電腦科學與工程學院","電腦科學與技術"},
                { "151100310002","關羽","男","電腦科學與工程學院","通信工程"},
                { "151100310003","張飛","男","數學與統計學院","信息與計算科學"},
                { "151100310004","小喬","女","文學院","漢語言文學"}
            };
            string[,] scores =
            {
                { "151100310001","劉備","88","90","80"},
                { "151100310002","關羽","86","70","75"},
                { "151100310003","張飛","67","75","81"},
                { "151100310004","小喬","99","89","92"}
            };
            DataTable stuInfoTable = new DataTable
            {
                TableName = "學生信息表"
            };
            stuInfoTable.Columns.Add("學號", typeof(string));
            stuInfoTable.Columns.Add("姓名", typeof(string));
            stuInfoTable.Columns.Add("性別", typeof(string));
            stuInfoTable.Columns.Add("學院", typeof(string));
            stuInfoTable.Columns.Add("專業", typeof(string));
            stuInfoTable.Rows.Add("學號", "姓名", "性別", "學院", "專業");
            for (int i = 0; i < infos.GetLength(0); i++)
            {
                DataRow row = stuInfoTable.NewRow();
                for (int j = 0; j < infos.GetLength(1); j++)
                {
                    row[j] = infos[i, j];
                }
                stuInfoTable.Rows.Add(row);
            }
            ds.Tables.Add(stuInfoTable);

            DataTable stuScoreTable = new DataTable
            {
                TableName = "學生成績表"
            };
            stuScoreTable.Columns.Add("學號", typeof(string));
            stuScoreTable.Columns.Add("姓名", typeof(string));
            stuScoreTable.Columns.Add("語文", typeof(string));
            stuScoreTable.Columns.Add("數學", typeof(string));
            stuScoreTable.Columns.Add("英語", typeof(string));
            stuScoreTable.Rows.Add("學號", "姓名", "語文", "數學", "英語");
            for (int i = 0; i < scores.GetLength(0); i++)
            {
                DataRow row = stuScoreTable.NewRow();
                for (int j = 0; j < scores.GetLength(1); j++)
                {
                    row[j] = scores[i, j];
                }
                stuScoreTable.Rows.Add(row);
            }
            ds.Tables.Add(stuScoreTable);
            return ds;
        }
    }
}

5.相關結果如下

 本文章代碼已經放在github:https://github.com/xs0910/.NET-Core-EPPlus