C# Excel文件与DataTable之间的转换

.net环境通过NPOI插件实现Excel与DataTable之间的转换

需要应用相应的dll，链接：下载插件.
也可以直接通过nuget导入，更方便
详细使用查看注释

using System;
using System.Data;
using System.IO;
using System.Collections.Generic;

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace ExcelMVC.Class
{
    public class ExcelTool
    {

        /// <summary>
        /// 将Excel解析为List<DataTable>，每一个sheet为一个DataTable
        /// </summary>
        /// <param name="stream">文件流</param>
        /// <returns>DataTable类型列表</returns>
        /// 使用方法：
        /// List<DataTable> list = null;
        /// using (System.IO.Stream stream = 文件流)
        /// {
        ///     list = ExcelTool.ExcelToDataTables(stream);
        ///     stream.Close();
        ///     stream.Dispose();
        /// }
        public static List<DataTable> ExcelToDataTables(Stream stream, bool isXlsx)
        {
            List<DataTable> list = new List<DataTable>();
            IWorkbook workbook = null;
            if (isXlsx)
                workbook = new XSSFWorkbook(stream);
            else
                workbook = new HSSFWorkbook(stream);
            stream.Close();
            stream.Dispose();
            ISheet sheet = null;
            int index = 0;
            //循环获取每个表
            do
            {
                sheet = workbook.GetSheetAt(index);
                index++;
                DataTable dt = new DataTable(sheet.SheetName);
                //获取标题
                IRow Colunm = sheet.GetRow(0);
                foreach (ICell item in Colunm.Cells)
                {
                    dt.Columns.Add(new DataColumn(item.StringCellValue));
                }
                //获取数据
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i + 1);
                    if (row == null)
                        continue;
                    DataRow dr = dt.NewRow();
                    bool HasValue = false;
                    for (int j = 0; j < Colunm.LastCellNum; ++j)
                    {
                        if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                        {
                            row.GetCell(j).SetCellType(CellType.String);
                            dr[j] = row.GetCell(j).StringCellValue;
                            HasValue = true;
                        }
                    }
                    if (HasValue)
                        dt.Rows.Add(dr);
                }
                list.Add(dt);
            } while (index < workbook.NumberOfSheets);
            return list;
        }

        /// <summary>
        /// 数据导出至excel文件,默认第一行为列名
        /// ms.Close();
        /// ms.Dispose();
        /// </summary>
        /// <param name="dt">数据源，DataTable类型</param>
        /// <returns>数据流</returns>
        /// WebFrom使用方式：
        /// using(MemoryStream ms = ExcelTool.DataTableToExcel(DataTable类型数据源))
        /// {
        ///     Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(文件名称, System.Text.Encoding.UTF8)));
        ///     Response.BinaryWrite(ms.ToArray());
        ///     Response.Flush();
        ///     Response.End();
        /// }
        /// MVC使用方式：
        /// 控制器返回类型FileResult
        /// MemoryStream ms = ExcelTool.DataTableToExcel(DataTable类型数据源);
        /// return File(ms, "application/vnd.ms-excel", 文件名称带后缀);
        public static MemoryStream DataTableToExcel(DataTable dt)
        {
            if (dt == null)
                throw new Exception("数据源无数据");
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet(dt.TableName);
            //写入标题
            IRow Column = sheet1.CreateRow(0);
            foreach (DataColumn item in dt.Columns)
            {
                Column.CreateCell(dt.Columns.IndexOf(item)).SetCellValue(item.ColumnName);
            }
            //将数据逐步写入sheet1各个行
            foreach (DataRow dr in dt.Rows)
            {
                IRow rowtemp = sheet1.CreateRow(dt.Rows.IndexOf(dr) + 1);
                for (int j = 0; j < dr.ItemArray.Length; j++)
                {
                    rowtemp.CreateCell(j).SetCellValue((string)dr.ItemArray[j]);
                }
            }
            // 写入到文件流
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }

    }
}