NPOI导出Excel帮助类

工具类

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Trumgu_BI_PF.Util
{
    public static class NpoiHelper
    {
        /// <summary>
        /// dataset导出Excel
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="fileFullPath"></param>
        public static void ExportToFile(DataSet dataSet, string fileFullPath)
        {
            var dts = dataSet.Tables.Cast<DataTable>().ToList();
            ExportToFile(dts, fileFullPath);
        }

        /// <summary>
        /// datatable导出Excel
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileFullPath"></param>
        public static void ExportToFile(DataTable dataTable, string fileFullPath)
        {
            var dts = new List<DataTable> { dataTable };
            ExportToFile(dts, fileFullPath);
        }

        /// <summary>
        /// 多个datatable导出Excel
        /// </summary>
        /// <param name="dataTables"></param>
        /// <param name="fileFullPath"></param>
        public static void ExportToFile(IEnumerable<DataTable> dataTables, string fileFullPath)
        {
            IWorkbook workbook = new XSSFWorkbook();
            var i = 0;
            foreach (var dt in dataTables)
            {
                var sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i) : dt.TableName;
                var sheet = workbook.CreateSheet(sheetName);
                var headerRow = sheet.CreateRow(0);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
                        ? "Column " + j
                        : dt.Columns[j].ColumnName;
                    headerRow.CreateCell(j).SetCellValue(columnName);
                }

                for (var a = 0; a < dt.Rows.Count; a++)
                {
                    var dr = dt.Rows[a];
                    var row = sheet.CreateRow(a + 1);
                    for (var b = 0; b < dt.Columns.Count; b++)
                    {
                        row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value 
                            ? dr[b].ToString() 
                            : string.Empty);
                    }
                }
            }

            using (var fs = File.Create(fileFullPath))
            {
                workbook.Write(fs);
            }
        }

        /// <summary>
        /// excel导入datatable
        /// </summary>
        /// <param name="xlsxFile"></param>
        /// <returns></returns>
        public static List<DataTable> GetDataTablesFrom(string xlsxFile)
        {
            if (!File.Exists(xlsxFile))
                throw new FileNotFoundException("文件不存在");

            var result = new List<DataTable>();
            Stream stream = new MemoryStream(File.ReadAllBytes(xlsxFile));
            IWorkbook workbook = new XSSFWorkbook(stream);
            for (var i = 0; i < workbook.NumberOfSheets; i++)
            {
                var dt = new DataTable();
                var sheet = workbook.GetSheetAt(i);
                var headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                {
                    var column = new DataColumn(headerRow.GetCell(j).StringCellValue);
                    dt.Columns.Add(column);
                }
                var rowCount = sheet.LastRowNum;
                for (var a = (sheet.FirstRowNum + 1); a < rowCount; a++)
                {
                    var row = sheet.GetRow(a);
                    if (row == null) continue;

                    var dr = dt.NewRow();
                    for (int b = row.FirstCellNum; b < cellCount; b++)
                    {
                        if (row.GetCell(b) == null) continue;
                        dr[b] = row.GetCell(b).ToString();
                    }

                    dt.Rows.Add(dr);
                }
                result.Add(dt);
            }
            stream.Close();

            return result;
        }
    }
}

使用方法

NpoiHelper.ExportToFile(dt, path);