使用NPOI导入导出Excel

使用NPOI导入导出Excel

简介

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目,使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的,它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

项目开源地址:https://github.com/tonyqus/npoi

使用NPOI的优势

  1. 您可以完全免费使用该框架
  2. 包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)
  3. 同时支持文件的导入和导出
  4. 来自全世界大量成功且真实的测试Cases
  5. 大量的实例代码
  6. 不需要在服务器上安装微软的Office
  7. 支持.net core

准备

  1. 新建.net core webapi项目
  2. 使用Nuget安装NPOI
    npoi_01
  3. 创建ExcelHelper辅助类

导出Excel为Stream

/// <summary>
/// 导出到Excel到MemoryStream
/// 列头使用[DisplayName("name")]标注 
/// </summary>      
/// <param name="data">源数据</param>      
/// <param name="strHeaderText">表头文本</param>      
/// <param name="strSheetName">工作表名称</param>
/// <returns></returns>
public static MemoryStream Export<T>(List<T> data, string strHeaderText, string strSheetName = "sheet") where T : class
{
    var workbook = new HSSFWorkbook();
    var sheet = workbook.CreateSheet(strSheetName);

    #region 右击文件 属性信息
    {
        var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        workbook.DocumentSummaryInformation = dsi;

        var si = PropertySetFactory.CreateSummaryInformation();
        si.Title = strHeaderText;               //填加xls文件标题信息      
        si.Subject = strHeaderText;              //填加文件主题信息      
        si.CreateDateTime = DateTime.Now;
        workbook.SummaryInformation = si;
    }
    #endregion

    var dateStyle = workbook.CreateCellStyle();
    var format = workbook.CreateDataFormat();
    dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

    PropertyInfo[] props = null;

    var rowIndex = 0;
    foreach (var item in data)
    {
        if (props == null)
        {
            props = item.GetType().GetProperties();  //获取对象中的所有属性
        }
        #region 新建表,填充表头,填充列头,样式
        if (rowIndex == 65535 || rowIndex == 0)  //单个sheet最大65535行,超过新建sheet
        {
            if (rowIndex != 0)
            {
                sheet = workbook.CreateSheet(strSheetName + rowIndex / 65535);
            }

            #region 表头及样式
            {
                var headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                var headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                var font = workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, props.Length - 1));
            }
            #endregion

            #region 列头及样式
            {
                var headerRow = sheet.CreateRow(1);
                var headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                var font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                for (var i = 0; i < props.Length; i++)
                {
                    //获取列的显示名称,如果没有则取属性名
                    var displayName = ((DisplayNameAttribute)Attribute.GetCustomAttribute(props[i], typeof(DisplayNameAttribute)))?.DisplayName;
                    displayName = string.IsNullOrEmpty(displayName) ? props[i].Name : displayName;
                    headerRow.CreateCell(i).SetCellValue(displayName);
                    headerRow.GetCell(i).CellStyle = headStyle;
                    //设置列宽   
                    //sheet.SetColumnWidth(i, (int)((15 + 0.72) * 256));
		    		//为解决单元格内容超过255设置列宽
		    		int colWidth = (arrColWidth[i] + 1) * 256;
                    if (colWidth < 255 * 256)
                    {
                        sheet.SetColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
                    }
                    else
                    {
                        sheet.SetColumnWidth(i, 6000);
                    }
                }
            }
            #endregion

            rowIndex = 2;
        }
        #endregion

        #region 填充内容
        var dataRow = sheet.CreateRow(rowIndex);
        for (var i = 0; i < props.Length; i++)
        {
            var newCell = dataRow.CreateCell(i);
            var drValue = props[i].GetValue(item,null)?.ToString();
            newCell.SetCellValue(drValue);
        }
        #endregion

        rowIndex++;
    }

    workbook.Write(ms);
    ms.Flush();
    ms.Position = 0;

    workbook.Dispose();
    return ms;
}

导出Excel到本地

/// <summary>      
/// 导出到Excel文件并存储在本地
/// 列头使用[DisplayName("name")]标注 
/// </summary>      
/// <param name="data">源数据</param>      
/// <param name="strHeaderText">表头文本</param>      
/// <param name="strFileName">保存位置</param>   
/// <param name="strSheetName">工作表名称</param>   
/// <returns></returns>
public static void Export<T>(List<T> data, string strHeaderText, string strFileName, string strSheetName) where T : class
{
    using (var ms = Export(data, strHeaderText, strSheetName))
    {
        using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
        {
            var bytes = ms.ToArray();
            fs.Write(bytes, 0, bytes.Length);
            fs.Flush();
        }
    }
}

导入

/// <summary>
/// 导入数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath">文件路径</param>
/// <param name="contentType">文件类型</param>
/// <returns></returns>
public static ExcelResult<T> Import<T>(string filePath, string contentType) where T : ImportExcelBase, new()
{
    using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    {
        return Import<T>(stream, contentType);
    }
}

/// <summary>
/// 导入数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="stream">文件流</param>
/// <param name="contentType">文件类型</param>
/// <returns></returns>
public static ExcelResult<T> Import<T>(Stream stream, string contentType) where T : ImportExcelBase, new()
{
    IWorkbook workbook;
    if (contentType == "application/vnd.ms-excel")
        workbook = new HSSFWorkbook(stream);
    else if (contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        workbook = new XSSFWorkbook(stream);
    else throw new ArgumentException();
    return ReadExcelToList<T>(workbook);
}

/// <summary>
/// 导入数据
/// </summary>
/// <typeparam name="T">ImportExcelBase</typeparam>
/// <param name="workbook"></param>
/// <returns></returns>
private static ExcelResult<T> ReadExcelToList<T>(IWorkbook workbook) where T : ImportExcelBase, new()
{
    if (workbook == null) throw new ArgumentException();

    var sheet = workbook.GetSheetAt(0);     //第一个sheet
    var headerRow = sheet.GetRow(0);        //表头(是否验证表头)
    var cellCount = headerRow.LastCellNum;  //获取列数

    var result = new ExcelResult<T>();
    var list = new List<T>();
    var type = typeof(T);
    var fields = type.GetProperties();
    var method = type.GetMethod("Valid");

    if (cellCount > fields.Length)
    {
        result.Message = "模板不匹配";
        return result;
    }

    for (var i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
    {
        var excelData = Activator.CreateInstance(type);
        var row = sheet.GetRow(i);
        for (int j = row.FirstCellNum; j < cellCount; j++)
        {
            fields[j].SetValue(excelData, row.GetCell(j)?.ToString());
        }

        var msg = method?.Invoke(excelData, new object[] { }).ToString();
        if (!string.IsNullOrEmpty(msg))
            result.Message += $"第{i}行,{msg};";

        list.Add((T)excelData);
    }
    result.Data = list;
    result.Success = string.IsNullOrEmpty(result.Message);
    return result;
}

/// <summary>
/// 数据导入的结果
/// </summary>
public class ExcelResult<T>
{
    public bool Success { get; set; }
    public string Message { get; set; }
    public List<T> Data { get; set; }
}

/// <summary>
/// 导入数据的基类
/// </summary>
public abstract class ImportExcelBase
{
    /// <summary>
    /// 用于验证导入列的合法性,为空则通过
    /// </summary>
    /// <returns></returns>
    public abstract string Valid();
}

测试

新建导入导出数据类

public class ImportUser : ImportExcelBase
{
    public string UserName { get; set; }
    public string Age { get; set; }
    public string Gender { get; set; }
    public override string Valid()
    {
        if (string.IsNullOrEmpty(UserName) || string.IsNullOrEmpty(Age) || string.IsNullOrEmpty(Gender))
            return $"{nameof(UserName)}、{nameof(Age)}、{nameof(Gender)}不能为空";
        return string.Empty;
    }
}

public class ExportUser
{
    [DisplayName("姓名")]
    public string UserName { get; set; }
    [DisplayName("年龄")]
    public int Age { get; set; }
    [DisplayName("性别")]
    public string Gender { get; set; }
}

新建导入导出api

using System.Collections.Generic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace WebApplication1.Controllers
{
    [Route("api/excel")]
    [ApiController]
    public class ExcelController : ControllerBase
    {
        [HttpPost("import")]
        [Consumes("multipart/form-data")]
        public IActionResult Import(IFormFile file)
        {
            var stream = file.OpenReadStream();
            var data = ExcelHelper.Import<ImportUser>(stream, file.ContentType);
            return Ok(data);
        }

        [HttpGet("export")]
        public IActionResult Export()
        {
            var data = new List<ExportUser>
            {
                new ExportUser(){Age = 10,Gender = "男",UserName = "张三"},
                new ExportUser(){Age = 12,Gender = "女",UserName = "张四"},
                new ExportUser(){Age = 14,Gender = "男",UserName = "张舞"},
            };

            var stream = ExcelHelper.Export(data, "TestHeaderText", "UserData");
            var bytes = stream.ToArray();
            return File(bytes, "application/vnd.ms-excel", "download.xls");
        }
    }
}

新建Excel,写入以下内容

npoi_04

使用postman调用接口 /api/excel/import,并上传上一步创建的Excel,结果如下

npoi_02

浏览器中访问 /api/excel/export,将直接下载导出的Excel文件,内容如下

npoi_03

结语

导入导出Excel的库有很多,我这里只是做了简单的封装,并没有涉及到很复杂的单元格操作