许多业务场景下需要处理和分析大量的数据，而 Excel 是业务人员常用的数据表格工具，因此，将 Excel 表格中内容上传并读取到网站，是一个很常见的功能，目前有许多成熟的开源或者商业的第三方库，比如 NPOI，EPPlus，Spire.Office for .NET 等等，今天分享一个使用 Magicodes.IE.Excel 上传和读取 Excel的方案，这是近年来一个比较受欢迎的开源的第三方库，下面我们用一个 Step By Step 例子来感受它的魅力。
- 安装 nuget 包
Magicodes.IE.Excel
Magicodes.IE.Core
2.创建一个 DTO 类
using Magicodes.ExporterAndImporter.Core; namespace ExcelSample.BusinessEntities.Dtos { public partial class ImportDto { /// <summary> /// ID /// </summary> [ImporterHeader(Name ="ID")] public string ItemGuid { get; set; } /// <summary> /// 巡检编号 /// </summary> [ImporterHeader(Name = "巡检编号")] public string InspectionNumber { get; set; } /// <summary> /// 详细地址 /// </summary> [ImporterHeader(Name = "详细位置")] public string FormattedAddress { get; set; } /// <summary> /// 开始日期 /// </summary> [ImporterHeader(Name = "开始日期")] public string BeginDate { get; set; } /// <summary> /// 截止日期 /// </summary> [ImporterHeader(Name = "结束日期")] public string EndDate { get; set; } /// <summary> /// 故障描述 /// </summary> [ImporterHeader(Name = "故障描述")] public string FaultInfo { get; set; } /// <summary> /// 单位名称 /// </summary> [ImporterHeader(Name = "单位")] public string CustomerName { get; set; } /// <summary> /// 维修说明 /// </summary> [ImporterHeader(Name = "维修说明")] public string HandleMeasuresOther { get; set; } } }
3.写公共读取 Export 文件内容方法
using Magicodes.ExporterAndImporter.Core; using Magicodes.ExporterAndImporter.Core.Extension; using Magicodes.ExporterAndImporter.Core.Models; using Magicodes.ExporterAndImporter.Excel; using System.Collections.Generic; using System.IO; using System.Threading.Tasks; namespace ExcelSample.Common { /// <summary> /// excel 工具类 /// </summary> public static class ExportHelper { /// <summary> /// 通用导入 excel 文件 /// </summary> /// <param name="filePath">Excel 文件路径</param> public static async Task<ImportResult<T>> ImportExcel<T>(string filePath) where T : class, new() { IImporter importer = new ExcelImporter(); var result = await importer.Import<T>(filePath); return result; } } }
4.写上传 Excel 文件的业务方法
public string UploadFile() { HttpFileCollection files = HttpContext.Current.Request.Files; if (files == null || files.Count == 0) { throw new Exception("没有上传文件"); } HttpPostedFile file = files[0]; string fileExt = Path.GetExtension(file.FileName); if (fileExt != ".xlsx" && fileExt != ".xls") { throw new Exception("不是Excel文件"); } string dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExcelImport"); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } string fileName = Path.GetFileNameWithoutExtension(file.FileName); string fileSaveName = string.Format("{0}{1}.xlsx", fileName, DateTime.Now.ToFlowWaterDate()); string fileSavePath = Path.Combine(dir, fileSaveName); _logger.Value.Info($"上传文件：[{fileSavePath}]"); file.SaveAs(fileSavePath); return fileSavePath; }
5.写具体的读取 Excel 文件内容的业务方法
public List<ImportDto> ReadExcel(string filePath) { var importData = ExportHelper.ImportExcel<ImportDto>(filePath).Result; var list = importData.Data.ToList(); if (list.HasData()) { return list; } return null; }
6.在控制器中写 API 向外提供上传和读取 Excel 的接口
using System; using System.Collections.Generic; using System.Linq; using System.Net; using System.Text; using System.Threading.Tasks; using System.Web.Http; using ExcelSample.Contracts.IService; using ExcelSample.BusinessEntities.Dtos; namespace ExcelSample.WebAPI.Controllers.V1 { [Authorize] [RoutePrefix("api/v1/excelSample")] public partial class ExcelSampleController : BaseController { // ...... [HttpPost] [Route("uploadExcel")] public IHttpActionResult UploadExcel() { // 1. 上传文件 string fileUpload = ""; try { fileUpload = UploadFile(); } catch (Exception ex) { _log.Value.Error(ex, "上传文件失败！"); return BadRequest(ex.Message); } // 2. 读取数据 var list = ReadExcel(fileUpload); if (list== null || list.Count == 0) { return BadRequest("文件没有数据或者数据格式不正确！"); } // 3. 更新数据 // 存储数据到数据库中 return Ok(Success(result)); } // ...... } }
7.运行项目并在 Postman 中进行测试
