欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ASP.NET Core使用EPPlus操作Excel

程序员文章站 2024-01-11 09:25:46
1.前言 本篇文章通过ASP.NET Core的EPPlus包去操作Excel(导入导出),其使用原理与NPOI类似,导出Excel的时候不需要电脑上安装office,非常好用 2.使用 新建一个ASP.NET Core Web应用程序(模型视图控制器),还有一个类库,SDK2.1版本,解决方案如下 ......

1.前言

  本篇文章通过asp.net core的epplus包去操作excel(导入导出),其使用原理与npoi类似,导出excel的时候不需要电脑上安装office,非常好用

2.使用

  新建一个asp.net core web应用程序(模型视图控制器),还有一个类库,sdk2.1版本,解决方案如下

ASP.NET Core使用EPPlus操作Excel

 

ASP.NET Core使用EPPlus操作Excel

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.相关结果如下

ASP.NET Core使用EPPlus操作Excel

 

 

ASP.NET Core使用EPPlus操作Excel

 本文章代码已经放在github:https://github.com/xs0910/.net-core-epplus