C#通过NPOI操作EXCEL

1、名词介绍

整个Excel：工作簿
Sheet页：页签，一个工作簿包含多个sheet页
表格：对应一个sheet页
行、列：单元格

2、导出Excel

根据实体类生成表格的表头，通过控制实体类的属性，保证哪些属性可以作为表头。

1、新建属性类，继承于Attribute

[AttributeUsage(AttributeTargets.Property)]
    public class TitleAttribute:Attribute
    {
        public string Title { get; set; }
    }

2、新建用户实体类

/// <summary>
    /// 用户实体类
    /// </summary>
    public class UserInfo
    {
        [Title(Title ="用户ID")]
        public int UserId { get; set; }
        [Title(Title = "用户姓名")]
        public string UserName { get; set; }
        [Title(Title = "用户年龄")]
        public int Age { get; set; }
        [Title(Title = "用户类型")]
        public int UserType { get; set; }
        [Title(Title = "用户描述")]
        public string Description { get; set; }
    }

3、新建表格实体类

将表格的名称和数据源绑定，可以批量导出。
数据源中的是用户实体类的集合

public class ExcelDataResource
    {
        public string SheetName { get; set; }//表格名称
        public int TitleIndex { get; set; }//起始行索引
        public List<object> SheetDataResource { get; set; }//数据源
    }

4、生成一个WorkBook

public static IWorkbook DataToHssfWorkbook(List<ExcelDataResource> list)
        {
            HSSFWorkbook hSSFWorkbook=new HSSFWorkbook();
            if (list == null) return hSSFWorkbook;
            foreach(var resourse in list)
            {
                if (resourse.SheetDataResource == null && resourse.SheetDataResource.Count == 0)
                    break;
                ISheet sheet=hSSFWorkbook.CreateSheet(resourse.SheetName);
                Type type =resourse.SheetDataResource[0].GetType();
                //根据实体类的属性筛选哪些属性（列）导出
                List<PropertyInfo> propList=type.GetProperties().Where(c=>c.IsDefined(typeof(TitleAttribute),true)).ToList();
                IRow header=sheet.CreateRow(resourse.TitleIndex);
                for(int i = 0; i < propList.Count; i++)
                {    
                    ICell cell = header.CreateCell(i);
                    //给表头赋值
                    cell.SetCellValue(propList[i].GetCustomAttribute<TitleAttribute>().Title);
                }

                for (int i = 0; i < resourse.SheetDataResource.Count; i++)
                {
                    IRow row = sheet.CreateRow(resourse.TitleIndex+1 + i);
                    object obSource = resourse.SheetDataResource[i];
                    for (int j = 0; j < propList.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.SetCellValue(propList[j].GetValue(obSource).ToString());
                    }
                }
            }
            return hSSFWorkbook;
        }

5、流导出WorkBook

 IWorkbook workbook= ExcelHelper.DataToHssfWorkbook(dataResources);
            using (FileStream fs = new FileStream("test.xls", FileMode.Create))
            {
                workbook.Write(fs);
            }

3、导入Excel

导入Excel主要是通过文件流生成WorkBook

1、加载Excel，生成IworkBook

using(FileStream fs=new FileStream("test2.xls",FileMode.Open))
            {
                IWorkbook workbook=WorkbookFactory.Create(fs);
            }

2、根据IworkBook，生成List< DataTable >。

public static List<DataTable> ToExcelDataTable(IWorkbook workbook)
        {
            List<DataTable> list = new List<DataTable>();
            for (int sheetIndex=0; sheetIndex< workbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet=workbook.GetSheetAt(sheetIndex);
                IRow header=sheet.GetRow(sheet.FirstRowNum);//获取第一行表头
                if (header == null) break;
                
                DataTable dataTable = new DataTable();
                //创建表头
                for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    if (cell != null)
                    {
                        string value = $"Column{i + 1}_{cell.ToString()}";
                        DataColumn column = new DataColumn(value);
                        dataTable.Columns.Add(column);
                    }
                }
                //生成表格数据
                for (int i=sheet.FirstRowNum+1; i < sheet.LastRowNum; i++)
                {
                    IRow row=sheet.GetRow(i);
                    if (row == null) continue;
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                }
                list.Add(dataTable);
            }
            return list;
        }

3、调用方法

 using(FileStream fs=new FileStream("test2.xls",FileMode.Open))
            {
                IWorkbook workbook=WorkbookFactory.Create(fs);
                List<DataTable> dataTables=ExcelHelper.ToExcelDataTable(workbook);
                dataGridView1.DataSource = dataTables[0];
            }