1.前言
本篇文章通过ASP.NET Core的EPPlus包去操作Excel(导入导出),其使用原理与NPOI类似,导出Excel的时候不需要电脑上安装office,非常好用
2.使用
新建一个ASP.NET Core Web应用程序(模型视图控制器),还有一个类库,SDK2.1版本,解决方案如下
3.在EPPlusCommon类库中创建一个EPPlusHelper类,包括两个方法,导入和读取数据
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
namespace EPPlusCommon
{
public class EPPlusHelper
{
private static int i; /// <summary>
/// 导入数据到Excel中
/// </summary>
/// <param name="fileName"></param>
/// <param name="ds"></param>
public static bool ImportExcel(string fileName, DataSet ds)
{
if (ds == null || ds.Tables.Count == )
{
return false;
}
FileInfo file = new FileInfo(fileName);
if (file.Exists)
{
file.Delete();
file = new FileInfo(fileName);
}
//在using语句里面我们可以创建多个worksheet，ExcelPackage后面可以传入路径参数
//命名空间是using OfficeOpenXml
using (ExcelPackage package = new ExcelPackage(file))
{
foreach (DataTable dt in ds.Tables)
{
//创建工作表worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName);
//给单元格赋值有两种方式
//worksheet.Cells[1, 1].Value = "单元格的值";直接指定行列数进行赋值
//worksheet.Cells["A1"].Value = "单元格的值";直接指定单元格进行赋值
worksheet.Cells.Style.Font.Name = "微软雅黑";
worksheet.Cells.Style.Font.Size = ;
worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
for (int i = ; i < dt.Rows.Count; i++)
{
for (int j = ; j < dt.Columns.Count; j++)
{
worksheet.Cells[i + , j + ].Value = dt.Rows[i][j].ToString();
}
}
using (var cell = worksheet.Cells[, , , dt.Columns.Count])
{
//设置样式:首行居中加粗背景色
cell.Style.Font.Bold = true; //加粗
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中
cell.Style.Font.Size = ;
cell.Style.Fill.PatternType = ExcelFillStyle.Solid; //背景颜色
cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , ));//设置单元格背景色
}
}
//保存
package.Save();
}
return true;
} /// <summary>
/// 读取Excel数据
/// </summary>
/// <param name="fileName"></param>
public static string ReadExcel(string fileName)
{
StringBuilder sb = new StringBuilder();
FileInfo file = new FileInfo(fileName);
try
{
using (ExcelPackage package = new ExcelPackage(file))
{
var count = package.Workbook.Worksheets.Count;
for (int k = ; k <= count; k++) //worksheet是从1开始的
{
var workSheet = package.Workbook.Worksheets[k];
sb.Append(workSheet.Name);
sb.Append(Environment.NewLine);
int row = workSheet.Dimension.Rows;
int col = workSheet.Dimension.Columns;
for (int i = ; i <= row; i++)
{
for (int j = ; j <= col; j++)
{
sb.Append(workSheet.Cells[i, j].Value.ToString() + "\t");
}
sb.Append(Environment.NewLine);
}
sb.Append(Environment.NewLine);
sb.Append(Environment.NewLine);
}
}
}
catch (Exception ex)
{
return "An error had Happen";
}
return sb.ToString();
}
}
}
代码片段已经给出了一些注释,对于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 =
{
{ "","刘备","男","计算机科学与工程学院","计算机科学与技术"},
{ "","关羽","男","计算机科学与工程学院","通信工程"},
{ "","张飞","男","数学与统计学院","信息与计算科学"},
{ "","小乔","女","文学院","汉语言文学"}
};
string[,] scores =
{
{ "","刘备","","",""},
{ "","关羽","","",""},
{ "","张飞","","",""},
{ "","小乔","","",""}
};
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 = ; i < infos.GetLength(); i++)
{
DataRow row = stuInfoTable.NewRow();
for (int j = ; j < infos.GetLength(); 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 = ; i < scores.GetLength(); i++)
{
DataRow row = stuScoreTable.NewRow();
for (int j = ; j < scores.GetLength(); 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