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版本,解决方案如下
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