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