使用NPOI操作DataTable写入Excel和从Excel读入到DataTable(C#)

对Excel的读写可以利用COM组件来操作，但前提是用户的机子必须安装有Office办公软件，而且由于Office的版本不同，所对应的代码也略有不同，如果你不想装Office，你可以使用NPOI

 

步骤：

1  利用NuGut下载NPOI组件，在vs的菜单栏  工具/NuGet程序包管理器/管理解决方案中的NuGet程序包中搜索NPOI,NuGut工具不会用可以参考：https://blog.csdn.net/zxy13826134783/article/details/85336968，当然你也可以下载NPOI的程序集，链接：

https://npoi.codeplex.com/releases

2 看一篇入门的教程，这位仁兄写得还不错，刚好可以用来入门：https://blog.csdn.net/jmh1996/article/details/78225718

 

3 进入主题：

3.1 编写ExcelHelper类，有两个静态方法，一个是把DataTable中的内容写入Excel文件中，一个是从Excel文件中读取内容到DataTable中，代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace OperateExcel
{
    /// <summary>
    /// Excel文件和DataTable之间转换帮助类
    /// </summary>
    public class ExcelHelper:IDisposable
    {

       
       

        /// <summary>
        /// 把DataTable的数据写入到指定的excel文件中
        /// </summary>
        /// <param name="TargetFileNamePath">目标文件excel的路径</param>
        /// <param name="sourceData">要写入的数据</param>
        /// <param name="sheetName">excel表中的sheet的名称，可以根据情况自己起</param>
        /// <param name="IsWriteColumnName">是否写入DataTable的列名称</param>
        /// <returns>返回写入的行数</returns>
        public static int DataTableToExcel(string TargetFileNamePath,DataTable sourceData,string sheetName,bool IsWriteColumnName) { 
            
            //数据验证
            if(!File.Exists(TargetFileNamePath)){
                //excel文件的路径不存在
                throw new ArgumentException("excel文件的路径不存在或者excel文件没有创建好");
            }
            if(sourceData==null){
                throw new ArgumentException("要写入的DataTable不能为空");
            }

            if (sheetName== null&&sheetName.Length==0)
            {
                throw new ArgumentException("excel中的sheet名称不能为空或者不能为空字符串");
            } 



            //根据Excel文件的后缀名创建对应的workbook
            IWorkbook workbook = null;
            if(TargetFileNamePath.IndexOf(".xlsx")> 0){  //2007版本的excel
                workbook = new XSSFWorkbook();
            }
            else if (TargetFileNamePath.IndexOf(".xls") > 0) //2003版本的excel
            {
                workbook = new HSSFWorkbook();
            }
            else {
                return -1;    //都不匹配或者传入的文件根本就不是excel文件，直接返回
            }
            


            //excel表的sheet名
            ISheet sheet = workbook.CreateSheet(sheetName);
            if (sheet == null) return -1;   //无法创建sheet，则直接返回


            //写入Excel的行数
            int WriteRowCount = 0;

            
            
            //指明需要写入列名，则写入DataTable的列名,第一行写入列名
            if(IsWriteColumnName){
                //sheet表创建新的一行,即第一行
                IRow ColumnNameRow=sheet.CreateRow(0); //0下标代表第一行
                //进行写入DataTable的列名
                for (int colunmNameIndex = 0; colunmNameIndex < sourceData.Columns.Count;colunmNameIndex++ )
                {
                    ColumnNameRow.CreateCell(colunmNameIndex).SetCellValue(sourceData.Columns[colunmNameIndex].ColumnName);
                }
                WriteRowCount++;
            }


            //写入数据
            for (int row=0; row < sourceData.Rows.Count;row++)
            {
                //sheet表创建新的一行
                IRow newRow = sheet.CreateRow(WriteRowCount);
                for (int column=0; column < sourceData.Columns.Count;column++)
                {
                    
                    newRow.CreateCell(column).SetCellValue(sourceData.Rows[row][column].ToString());
                    
                }

                WriteRowCount++;  //写入下一行
            }


            //写入到excel中
            FileStream fs = new FileStream(TargetFileNamePath,FileMode.Open,FileAccess.Write);
            workbook.Write(fs);
         
            fs.Flush();
            fs.Close();
               
            workbook.Close();
            return WriteRowCount;
        }



        
        
        /// <summary>
        /// 从Excel中读入数据到DataTable中
        /// </summary>
        /// <param name="sourceFileNamePath">Excel文件的路径</param>
        /// <param name="sheetName">excel文件中工作表名称</param>
        /// <param name="IsHasColumnName">文件是否有列名</param>
        /// <returns>从Excel读取到数据的DataTable结果集</returns>
        public static DataTable ExcelToDataTable(string sourceFileNamePath,string sheetName,bool IsHasColumnName) {
            
            if(!File.Exists(sourceFileNamePath)){
                throw new ArgumentException("excel文件的路径不存在或者excel文件没有创建好");
            }

            if(sheetName==null||sheetName.Length==0){
                throw new ArgumentException("工作表sheet的名称不能为空");
            }

            //根据Excel文件的后缀名创建对应的workbook
            IWorkbook workbook = null;
            //打开文件
            FileStream fs = new FileStream(sourceFileNamePath, FileMode.Open, FileAccess.Read);
            if (sourceFileNamePath.IndexOf(".xlsx") > 0)
            {  //2007版本的excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (sourceFileNamePath.IndexOf(".xls") > 0) //2003版本的excel
            {
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                return null;    //都不匹配或者传入的文件根本就不是excel文件，直接返回
            }


            

            //获取工作表sheet
            ISheet sheet = workbook.GetSheet(sheetName);
            //获取不到，直接返回
            if (sheet == null) return null;



            //开始读取的行号
            int StartReadRow = 0;
            DataTable targetTable = new DataTable();

            
            
            //表中有列名,则为DataTable添加列名
            if(IsHasColumnName){
                //获取要读取的工作表的第一行
               IRow columnNameRow=sheet.GetRow(0);   //0代表第一行
               //获取该行的列数(即该行的长度)
               int CellLength = columnNameRow.LastCellNum;
               
               //遍历读取
               for (int columnNameIndex = 0; columnNameIndex <CellLength; columnNameIndex++)
               {
                   //不为空，则读入
                   if(columnNameRow.GetCell(columnNameIndex)!=null){
                       //获取该单元格的值
                       string cellValue=columnNameRow.GetCell(columnNameIndex).StringCellValue;
                        if(cellValue!=null){
                            //为DataTable添加列名
                            targetTable.Columns.Add(new DataColumn(cellValue));
                        }
                   }
               }

               StartReadRow++;             
            }



            ///开始读取sheet表中的数据

             //获取sheet文件中的行数
            int RowLength = sheet.LastRowNum;
            //遍历一行一行地读入
            for (int RowIndex = StartReadRow; RowIndex < RowLength; RowIndex++)
            {
                //获取sheet表中对应下标的一行数据
                IRow currentRow = sheet.GetRow(RowIndex);   //RowIndex代表第RowIndex+1行

                if (currentRow == null) continue;  //表示当前行没有数据，则继续
                //获取第Row行中的列数，即Row行中的长度
                int currentColumnLength = currentRow.LastCellNum;

                //创建DataTable的数据行
                DataRow dataRow = targetTable.NewRow();
                //遍历读取数据
                for (int columnIndex = 0; columnIndex < currentColumnLength; columnIndex++)
                {
                    //没有数据的单元格默认为空
                    if(currentRow.GetCell(columnIndex)!=null){
                        dataRow[columnIndex] = currentRow.GetCell(columnIndex);
                    }
                }
                //把DataTable的数据行添加到DataTable中
                targetTable.Rows.Add(dataRow);
            }


            //释放资源
            fs.Close();
            workbook.Close();

            return targetTable;
        }
        
        
        #region IDisposable 成员

        public void Dispose()
        {
            
        }

        #endregion
    }
}

3.2  测试

3.2.1 测试DataTable到写入Excel的过程

测试代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;

namespace OperateExcel
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dataTable = CreateDataTable();
            string filePath = "test.xls";
            if (!File.Exists(filePath))
            {
                FileStream fs = File.Create(filePath);
                fs.Close();
                Console.WriteLine("创建文件完毕");
            }
            //把DataTable写入到excel文件中
            int writeCount = ExcelHelper.DataTableToExcel(filePath, dataTable, "MySheet", true);
            Console.WriteLine("成功写入" + writeCount + "行");

   
            Console.ReadKey();
        }



        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <returns></returns>
        static DataTable CreateDataTable() {
            //创建列明行,五列(Column1-Column5)
            DataTable dataTable = new DataTable();
            for (int i =1; i <=5; i++)
            {
                dataTable.Columns.Add("Column"+i);
            }
            
            //创建测试数据,5行，即所创建的DataTable是5行5列
            for (int i = 1; i <= 5; i++)
            {
               //创建新的一行
               DataRow newRow=dataTable.NewRow(); 
               //为新的一行填充数据
               newRow["Column1"] = "第" + i + "行，第1列";
               newRow["Column2"] = "第" + i + "行，第2列";
               newRow["Column3"] = "第" + i + "行，第3列";
               newRow["Column4"] = "第" + i + "行，第4列";
               newRow["Column5"] = "第" + i + "行，第5列";

               //把刚创建好得一行数据添加到DataTable中
               dataTable.Rows.Add(newRow);
            }
            return dataTable;
        }


       
    }
}

运行结果如下图：

你可以在bin/Debug目录下，看到生成一个名为test.xls的文件，打开如下图：

 

3.2.2 测试从excel中读取数据到DataTable中的过程，excel文件则利用刚才生成的test.xls

代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;

namespace OperateExcel
{
    class Program
    {
        static void Main(string[] args)
        {

            string filePath = "test.xls";
            DataTable dataTable = ExcelHelper.ExcelToDataTable(filePath, "MySheet", true);
            PrintDataTable(dataTable);
            Console.ReadKey();
        }




        /// <summary>
        /// 打印输出DataTable的值
        /// </summary>
        /// <param name="dataTable"></param>
        static void PrintDataTable(DataTable dataTable)
        {
            if (dataTable == null) return;

            for (int row = 0; row < dataTable.Rows.Count; row++) //行长度
            {
                for (int column = 0; column < dataTable.Columns.Count; column++) //列长度
                {
                    Console.WriteLine(dataTable.Rows[row][column] + "\t");
                }

                Console.Write("\n");
            }
        }
    }
}

运行结果如下图：