C# Excel文件操作基础教程

c#开发过程中是否经常遇到office com组件抽风，版本不兼容等各种问题？NPOI可以帮你，NPOI是一个国人的OFFICE SDK，设计目的是用于处理word,excel，ppt中的文本，可以将程序数据导出、导入成excel文档，还可以提取图片文件中的图片等。

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

使用教程

DataTable导出

class Program
    {
        static void Main(string[] args)
        {
            //添加datatable测试数据
            DataTable _dataTable = new DataTable();
            _dataTable.Columns.Add(new DataColumn("userName", typeof(string)));
            _dataTable.Columns.Add(new DataColumn("userId", typeof(int)));
            _dataTable.Columns.Add(new DataColumn("birthday", typeof(string)));
            _dataTable.Columns.Add(new DataColumn("sex", typeof(string)));
            _dataTable.Rows.Add(new string[] {
                "张三",
                "100",
                "2020-08-01",
                "女"
            });
            _dataTable.Rows.Add(new string[] {
                "李四",
                "100",
                "2020-08-01",
                "男"
            });

            DataTabletoExcel(_dataTable,DateTime.Now.ToString("yyyyMMddHHmm"));
            Console.ReadKey();
        }

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="strFileName"></param>
        private static void DataTabletoExcel(DataTable dataTable, string strFileName)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            int index = 0;
            IRow titleRow = sheet.CreateRow(index++);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                titleRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
            }
            foreach (DataRow dr in dataTable.Rows)
            {
                IRow row = sheet.CreateRow(index++);
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dr[i].ToString());
                }
            }

            using (FileStream fileStream = new FileStream(strFileName, FileMode.Create))
            {
                workbook.Write(fileStream);
                fileStream.Close();
                workbook.Close();
            }
        }
    }

泛型集合导出

class Program
    {
        static void Main(string[] args)
        {
            //新增测试数据
            List<User> users = new List<User>()
            {
                new User()
                {
                 Age=100,
                 Id=1,
                 Name="张三",
                 Sex="男"
                },
                new User()
                {
                 Age=34,
                 Id=2,
                 Name="梁哥",
                 Sex="男"
                },
                new User()
                {
                 Age=25,
                 Id=3,
                 Name="阿坤",
                 Sex="男"
                }
            };
            Entity2Excel(users);
            Console.ReadKey();
        }

        /// <summary>
        /// 实体对象导出到Excel
        /// </summary>
        /// <param name="users"></param>
        public static void Entity2Excel(List<User> users)
        {
            //创建工作簿
            IWorkbook workbook = new XSSFWorkbook();
            //创建工作表
            ISheet sheet = workbook.CreateSheet("有码挺好测试");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("ID");
            row0.CreateCell(1).SetCellValue("姓名");
            row0.CreateCell(2).SetCellValue("年龄");
            row0.CreateCell(3).SetCellValue("性别");
            for (int r = 0; r < users.Count; r++)
            {
                //创建行row
                IRow row = sheet.CreateRow(r + 1);
                row.CreateCell(0).SetCellValue(users[r].Id);
                row.CreateCell(1).SetCellValue(users[r].Name);
                row.CreateCell(2).SetCellValue(users[r].Age);
                row.CreateCell(3).SetCellValue(users[r].Sex);
            }

            //创建流对象并设置存储Excel文件的路径
            using (FileStream file = File.OpenWrite(DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx"))
            {
                //导出Excel文件
                workbook.Write(file);
                //用完释放
                workbook.Close();
            }
        }

    }