NPOI操作Excel-Toy模板网

这篇具有很好参考价值的文章主要介绍了NPOI操作Excel。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

NPOI操作Excel

一、NPOI背景

二、Excel名词介绍+C#常规操作Excel

名词介绍：

整个Excel：工作薄

Sheet页：页签，一个工作簿中可以包含多个Sheet页

表格：对应一个Sheet

行、列、单元格

C#中的常规操作：

导出一个Excel:其实就是要生成一个Excel文件，Excel文件对应的文件流

导入一个Excel:读取一个文件，读取文件流，需要从文件流中读取我们需要的各种数据，解析Excel的数据

三、ASP.NET Core5.0中开始使用NPOI

1.Nuget引入程序包

DotNetCore.NPOI

2.生成一个Excel文件出来

//Main方法                
IWorkbook workBook = ExcelOperationHelper.CreateExcelWorkbook();                
{                   
    using (FileStream file = new FileStream("D:\\Test\\TestExcel\\StudentInfo.xlsx", FileMode.Create))                  
    {                        
        workBook.Write(file);                        
        file.Close();                    
    }                
}

        /// <summary>
        /// 测试一下如何生成Excel
        /// </summary>
        /// <returns></returns>
        public static IWorkbook CreateExcelWorkbook()
        {
            HSSFWorkbook _workbook = new HSSFWorkbook();
            ISheet sheet1 = _workbook.CreateSheet("Sheet1");

            //创建第一行
            {
                IRow head = sheet1.CreateRow(0);

                ICell cell = head.CreateCell(0);
                cell.SetCellValue("学生姓名");

                ICell cell1 = head.CreateCell(1);
                cell1.SetCellValue("数学成绩");

                ICell cell2 = head.CreateCell(2);
                cell2.SetCellValue("语文成绩");
            }

            //创建第二行
            {
                IRow head = sheet1.CreateRow(1);

                ICell cell = head.CreateCell(0);
                cell.SetCellValue("Richard");

                ICell cell1 = head.CreateCell(1);
                cell1.SetCellValue("100");

                ICell cell2 = head.CreateCell(2);
                cell2.SetCellValue("95");
            }
            return _workbook;
        }

四、封装NPOI，方便使用

1.导出Excel —必然要生成Excel

2.对于系统来说–可以提供的是数据

需要考虑的问题：

数据写到工作簿中的那个Sheet页中
生成的Excel–考虑表头放在那个位置
直接集合中的某一个对象来直接生成—如果对象是一个实体—实体中有多少个属性，就表示多少个列

最终的目标：

做到业务系统不需要考虑其他，只需要按照规则来给定数据即可，就可以生成Excel出来；

设计生成Excel的数据格式：

五、封装NPOI，定义生成Excel方法

封装一个固定的数据结构
封装一个方法，给定固定的数据结构，然后调用方法就可以生成Excel
对于其他开发者来说，就很方便，不用关系如何生成Excel，只需要按照要求把数据准备好就可以生成Excel了

using Advanced.Common;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;

namespace NPOI操作Excel导入导出
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //IWorkbook workBook = ExcelOperationHelper.CreateExcelWorkbook();
                //{
                //    using (FileStream file = new FileStream("D:\\Test\\TestExcel\\StudentInfo.xlsx", FileMode.Create))
                //    {
                //        workBook.Write(file);
                //        file.Close();
                //    }
                //}
                {
                    List<ExcelDataResource> dataResourceslist = GetExcelDataList();
                    ExcelOperationHelper.DataToHSSFWorkbook(dataResourceslist);

                    IWorkbook workBook = ExcelOperationHelper.DataToHSSFWorkbook(dataResourceslist);
                    {
                        using (FileStream file = new FileStream("D:\\Test\\TestExcel\\StudentInfo.xlsx", FileMode.Create))
                        {
                            workBook.Write(file);
                            //file.Close();
                        }
                    }

                }
                //{
                //    using (FileStream fileStream = new FileStream("D:\\Test\\TestExcel\\StudentInfo.xlsx", FileMode.Open))
                //    {
                //        List<DataTable> dataTables = ExcelOperationHelper.ExcelStreamToDateTable(fileStream);
                //    }
                //}

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw;
            }
        }

        public static List<ExcelDataResource> GetExcelDataList()
        {
            List<object> objlist = new List<object>();
            for (int i = 0; i < 100; i++)
            {
                objlist.Add(new UserInfo
                {
                    UserId = i + 1,
                    UserName = $"名称-{i}",
                    Age = i + i + 1,
                    UserType = i + 1,
                    Description = $"描述_描述_描述_描述_描述_描述_描述_描述_{i}"
                }); 
            }

            List<object> Classobjlist = new List<object>();
            for (int i = 0; i < 200; i++)
            {
                Classobjlist.Add(new ClassInfo 
                {
                    UserId = i + 1,
                    UserName = $"名称-{i}",
                    Age = i + i + 1,
                    UserType = i + 1,
                    Description1 = "描述_描述_描述_描述_描述_描述_描述_描述_{i}",
                    Description2 = "描述_描述_描述_描述_描述_描述_描述_描述_{i}"
                });
            }
            return new List<ExcelDataResource>()
            {
                new ExcelDataResource()
                {
                    SheetName = "页签1",
                    TitleIndex = 1,
                    SheetDataResource = objlist
                },
                new ExcelDataResource() 
                {
                    SheetName = "页签2",
                    TitleIndex = 1,
                    SheetDataResource = Classobjlist
                }
            };
        }
    }
}

        /// <summary>
        /// 给定固定格式的数据，可以生成Excel
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource> dataResources)
        {
            HSSFWorkbook _workbook = new HSSFWorkbook();
            if (dataResources == null || dataResources.Count == 0)
            {
                return _workbook;
            }
            //每循环一次就生成一个Sheet页出来
            foreach (var sheetResource in dataResources) 
            {
                if (sheetResource.SheetDataResource != null && sheetResource.SheetDataResource.Count == 0)
                {
                    break;
                }
                //创建一个页签
                ISheet sheet = _workbook.CreateSheet(sheetResource.SheetName);
                //确定当前这一页有多少列---取决保存当前Sheet页数据的实体属性中的标记的特性
                object obj = sheetResource.SheetDataResource[0];
                
                //获取需要导出的所有的列
                Type type = obj.GetType();
                List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(TitleAttribute),true)).ToList();

                //确定表头在哪一行生成
                int titleIndex = 0;
                if (sheetResource.TitleIndex >= 0)
                {
                    titleIndex = sheetResource.TitleIndex - 1;
                }

                //基于当前的这个Sheet创建表头
                IRow titleRow = sheet.CreateRow(titleIndex);

                ICellStyle style = _workbook.CreateCellStyle();
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                style.FillPattern = FillPattern.SolidForeground;
                style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;

                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                titleRow.Height = 100 * 4;
                //给表头行，分别创建单元格，并赋值字段的名称
                for (int i = 0; i < propList.Count(); i++)
                {
                    TitleAttribute propertyAttribute = propList[i].GetCustomAttribute<TitleAttribute>();

                    ICell cell = titleRow.CreateCell(i);
                    cell.SetCellValue(propertyAttribute.Titile);
                    cell.CellStyle = style;
                }

                //去生成数据
                for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++)
                {
                    IRow row = sheet.CreateRow(i + titleIndex + 1);
                    object objInstance = sheetResource.SheetDataResource[i];
                    for (int j = 0; j < propList.Count(); j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue((propList[j].GetValue(objInstance).ToString()));
                    }
                }
            }

            return _workbook;
        }

        /// <summary>
        /// 传入固定格式的数据，生成Excel workbook再写入到基于内存的流里边去
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static MemoryStream ToExcelMemoryStream(List<ExcelDataResource> dataResources)
        {
            IWorkbook _workbook = DataToHSSFWorkbook(dataResources);
            using (MemoryStream stream = new MemoryStream()) 
            {
                _workbook.Write(stream);
                return stream;
            }
        }

六、开发中的各种应用场景

写入Response二进制流
调用框架的file方法
扩展IActionResult 方法 ExcelResult方法

        /// <summary>
        /// 通过数据生成Excel 然后转换成byte[]
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static byte[] ToExcelByteArray(List<ExcelDataResource> dataResources)
        {
            IWorkbook _workbook = DataToHSSFWorkbook(dataResources);

            using(MemoryStream stream = new MemoryStream()) 
            {
                _workbook.Write(stream);
                byte[] bt = stream.ToArray();
                stream.Write(bt,0,(int)stream.Length);
                return bt;
            }
        }

七、Excel导入

本质：目的是把Excel文件提交到服务器，然后把Excel文件中的数据信息读取出来，然后要处理的就是数据信息；

Excel文件的解析文章来源地址https://www.toymoban.com/news/detail-530974.html

Excel文件----文件流 fileStream MemoryStream Byte[]-------IWorkbook,如果得到了一个IWorkbook 就可以使用Npoi来解析内部的数据
就是解析一个IWorkbook–读取内部数据

using (FileStream fileStream = new FileStream("D:\\Test\\TestExcel\\StudentInfo.xlsx", FileMode.Open))
{
     List<DataTable> dataTables = ExcelOperationHelper.ExcelStreamToDateTable(fileStream);
}

        /// <summary>
        /// Excel转换成DataTable
        /// </summary>
        /// <param name="hSSFWorkbook"></param>
        /// <returns></returns>
        public static List<DataTable> ToExcelDataTable(IWorkbook hSSFWorkbook)
        {
            List<DataTable> datablelist = new List<DataTable>();
            for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);

                //获取表头FirstRowNum 第一行索引 0
                IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行

                if (header == null)
                {
                    break;
                }
                int startRow = 0; //数据的第一行索引

                DataTable dtNpoi = new DataTable();
                startRow = sheet.FirstRowNum + 1;
                for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue = $"Column{i + 1}_{cell.ToString()}";
                        if (cellValue != null)
                        {
                            DataColumn column = new DataColumn(cellValue);
                            dtNpoi.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn();
                            dtNpoi.Columns.Add(column);
                        }
                    }
                }

                //数据    LastRowNum最后一行的索引   如第九行----索引8
                for (int i = startRow; i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);//获取第i行
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dr = dtNpoi.NewRow();

                    //遍历每行的单元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    dtNpoi.Rows.Add(dr);
                }
                datablelist.Add(dtNpoi);
            }
            return datablelist;
        }

八、Excel导入常用场景

ASP.NET Core中的上传Excel—表单提交+JS文件上传
Winform文件上传

using Advanced.Common;
using System.Data;
namespace Advanced.WinFormProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string fileName = Path.GetExtension(openFileDialog.FileName);
                if (fileName != ".xls" && fileName != ".xlsx")
                {
                    MessageBox.Show("请选择Excel文件！");
                }
                else
                {
                    List<DataTable> dataTables = null;
                    using (FileStream fileStream = new FileStream(openFileDialog.FileName, FileMode.Open))
                    {
                        dataTables = ExcelOperationHelper.ExcelStreamToDateTable(fileStream);
                    }
                    this.dataGridView1.DataSource = dataTables[0];
                }
            }
        }
    }
}

using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Reflection;

namespace Advanced.Common
{

    /// <summary>
    /// Excel帮助类库
    /// </summary>
    public class ExcelOperationHelper
    {
        /// <summary>
        /// 测试一下如何生成Excel
        /// </summary>
        /// <returns></returns>
        public static IWorkbook CreateExcelWorkbook()
        {
            HSSFWorkbook _workbook = new HSSFWorkbook();
            ISheet sheet1 = _workbook.CreateSheet("Sheet1");

            //创建第一行
            {
                IRow head = sheet1.CreateRow(0);

                ICell cell = head.CreateCell(0);
                cell.SetCellValue("学生姓名");

                ICell cell1 = head.CreateCell(1);
                cell1.SetCellValue("数学成绩");

                ICell cell2 = head.CreateCell(2);
                cell2.SetCellValue("语文成绩");
            }

            //创建第二行
            {
                IRow head = sheet1.CreateRow(1);

                ICell cell = head.CreateCell(0);
                cell.SetCellValue("Richard");

                ICell cell1 = head.CreateCell(1);
                cell1.SetCellValue("100");

                ICell cell2 = head.CreateCell(2);
                cell2.SetCellValue("95");
            }
            return _workbook;
        }

        /// <summary>
        /// 给定固定格式的数据，可以生成Excel
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource> dataResources)
        {
            HSSFWorkbook _workbook = new HSSFWorkbook();
            if (dataResources == null || dataResources.Count == 0)
            {
                return _workbook;
            }
            //每循环一次就生成一个Sheet页出来
            foreach (var sheetResource in dataResources) 
            {
                if (sheetResource.SheetDataResource != null && sheetResource.SheetDataResource.Count == 0)
                {
                    break;
                }
                //创建一个页签
                ISheet sheet = _workbook.CreateSheet(sheetResource.SheetName);
                //确定当前这一页有多少列---取决保存当前Sheet页数据的实体属性中的标记的特性
                object obj = sheetResource.SheetDataResource[0];
                
                //获取需要导出的所有的列
                Type type = obj.GetType();
                List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(TitleAttribute),true)).ToList();

                //确定表头在哪一行生成
                int titleIndex = 0;
                if (sheetResource.TitleIndex >= 0)
                {
                    titleIndex = sheetResource.TitleIndex - 1;
                }

                //基于当前的这个Sheet创建表头
                IRow titleRow = sheet.CreateRow(titleIndex);

                ICellStyle style = _workbook.CreateCellStyle();
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                style.FillPattern = FillPattern.SolidForeground;
                style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;

                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                titleRow.Height = 100 * 4;
                //给表头行，分别创建单元格，并赋值字段的名称
                for (int i = 0; i < propList.Count(); i++)
                {
                    TitleAttribute propertyAttribute = propList[i].GetCustomAttribute<TitleAttribute>();

                    ICell cell = titleRow.CreateCell(i);
                    cell.SetCellValue(propertyAttribute.Titile);
                    cell.CellStyle = style;
                }

                //去生成数据
                for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++)
                {
                    IRow row = sheet.CreateRow(i + titleIndex + 1);
                    object objInstance = sheetResource.SheetDataResource[i];
                    for (int j = 0; j < propList.Count(); j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue((propList[j].GetValue(objInstance).ToString()));
                    }
                }
            }

            return _workbook;
        }

        /// <summary>
        /// 传入固定格式的数据，生成Excel workbook再写入到基于内存的流里边去
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static MemoryStream ToExcelMemoryStream(List<ExcelDataResource> dataResources)
        {
            IWorkbook _workbook = DataToHSSFWorkbook(dataResources);
            using (MemoryStream stream = new MemoryStream()) 
            {
                _workbook.Write(stream);
                return stream;
            }
        }

        /// <summary>
        /// 通过数据生成Excel 然后转换成byte[]
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static byte[] ToExcelByteArray(List<ExcelDataResource> dataResources)
        {
            IWorkbook _workbook = DataToHSSFWorkbook(dataResources);

            using(MemoryStream stream = new MemoryStream()) 
            {
                _workbook.Write(stream);
                byte[] bt = stream.ToArray();
                stream.Write(bt,0,(int)stream.Length);
                return bt;
            }
        }


        /// <summary>
        /// Excel转换成DataTable
        /// </summary>
        /// <param name="hSSFWorkbook"></param>
        /// <returns></returns>
        public static List<DataTable> ToExcelDataTable(IWorkbook hSSFWorkbook)
        {
            List<DataTable> datablelist = new List<DataTable>();
            for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);

                //获取表头FirstRowNum 第一行索引 0
                IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行

                if (header == null)
                {
                    break;
                }
                int startRow = 0; //数据的第一行索引

                DataTable dtNpoi = new DataTable();
                startRow = sheet.FirstRowNum + 1;
                for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue = $"Column{i + 1}_{cell.ToString()}";
                        if (cellValue != null)
                        {
                            DataColumn column = new DataColumn(cellValue);
                            dtNpoi.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn();
                            dtNpoi.Columns.Add(column);
                        }
                    }
                }

                //数据    LastRowNum最后一行的索引   如第九行----索引8
                for (int i = startRow; i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);//获取第i行
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dr = dtNpoi.NewRow();

                    //遍历每行的单元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    dtNpoi.Rows.Add(dr);
                }
                datablelist.Add(dtNpoi);
            }
            return datablelist;
        }


        public static List<DataTable> ExcelStreamToDateTable(Stream stream)
        {
            IWorkbook hSSFWorkbook = WorkbookFactory.Create(stream);
            return  ToExcelDataTable(hSSFWorkbook);
        }
    }
}

到了这里，关于NPOI操作Excel的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！