C# 使用NPOI读写Excel(Xls)

Office组件读写Excel 不同版本兼容问题一直以来是比较头疼的问题 

NPOI 提供Excel读写组件  NuGet    搜  NPOI 第一个 添加引用即可

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

namespace WindowsFormsApplication1
{
    public static class ExcelHelper
    {

        /// <summary>
        /// 导出表格数据到 xls
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        public static void Export(DataTable dt, string filePath)
        {
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
            {
                HSSFWorkbook book = new HSSFWorkbook();
                ISheet sheet = book.CreateSheet(dt.TableName);
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    fs.Seek(0, SeekOrigin.Begin);
                    book.Write(fs);
                }
                book = null;
            }
        }
        /// <summary>
        /// WEBAPI 下载Excel表格文件
        /// </summary>
        /// <param name="request"></param>
        /// <param name="dt"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        //public static HttpResponseMessage DownLoadExcel(this HttpRequestMessage request, DataTable dt, String filename)
        //{
        //    var response = request.CreateResponse(HttpStatusCode.OK);
        //    HSSFWorkbook book = new HSSFWorkbook();
        //    ISheet sheet = book.CreateSheet(dt.TableName);
        //    IRow row = sheet.CreateRow(0);
        //    for (int i = 0; i < dt.Columns.Count; i++)
        //    {
        //        row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
        //    }
        //    for (int i = 0; i < dt.Rows.Count; i++)
        //    {
        //        IRow row2 = sheet.CreateRow(i + 1);
        //        for (int j = 0; j < dt.Columns.Count; j++)
        //        {
        //            row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
        //        }
        //    }
        //    // 写入到客户端  
        //    System.IO.MemoryStream stream = new System.IO.MemoryStream(8192);
        //    book.Write(stream);
        //    stream.Seek(0, SeekOrigin.Begin);
        //    response.Content = new StreamContent(stream);
        //    response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
        //    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
        //    {
        //        FileName = filename
        //    };
        //    book = null;
        //    return response;
        //}



        /// <summary>
        /// 从 Xls文件导入数据到 DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public static DataTable Import(string filePath, String SheetName)
        {
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheet(SheetName);
            if (sheet == null)
            {
                return new DataTable();
            }

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable(SheetName);

            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                ICell cell = sheet.GetRow(0).Cells[j];
                dt.Columns.Add(cell.ToString());
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow Row = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    Row[i] = cell == null ? null : cell.ToString();
                }
                dt.Rows.Add(Row);
            }
            hssfworkbook.Close();
            hssfworkbook = null;
            return dt;
        }

        /// <summary>
        /// 从 Xls文件导入数据到 DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public static DataTable Import(string filePath, Int32 SheetIndex)
        {
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(SheetIndex);
            String SheetName = hssfworkbook.GetSheetName(SheetIndex);
            if (sheet == null)
            {
                return new DataTable();
            }
            
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable(SheetName);

            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                ICell cell = sheet.GetRow(0).Cells[j];
                dt.Columns.Add(cell.ToString());
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow Row = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    Row[i] = cell == null ? null : cell.ToString();
                }
                dt.Rows.Add(Row);
            }
            hssfworkbook.Close();
            hssfworkbook = null;
            return dt;
        }



    }
}