【转】分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility

原文链接 http://www.cnblogs.com/zuowj/p/5113812.html

 

1. ExcelUtility功能：
　　 1.将数据导出到EXCEL(支持XLS,XLSX，支持多种类型模板，支持列宽自适应)
　　　　 类名：ExcelUtility. Export

　　 2.将EXCEL数据导入到数据对象中（DataTable、Dataset，支持XLS,XLSX）
　　　　 类名：ExcelUtility. Import

　　类库项目文件结构如下图示：

　　

 2. ExcelUtility依赖组件：
　　 1.NPOI 操作EXCEL核心类库
　　 2.NPOI.Extend NPOI扩展功能
　　 3. ExcelReport 基于NPOI的二次扩展，实现模板化导出功能
　　 4. System.Windows.Forms 导出或导入时，弹出文件选择对话框（如果用在WEB中可以不需要，但我这里以CS端为主）

3.使用环境准备：

　　1.通过NUGet引用NPOI包、ExcelReport 包；（ExcelReport 存在BUG,可能需要用我项目中修复过后的DLL）

　　2.引用ExcelUtility类库；

4.具体使用方法介绍（示例代码，全部为测试方法）：

导出方法测试：

/// <summary>
        /// 测试方法：测试将DataTable导出到EXCEL，无模板
        /// </summary>
        [TestMethod]
        public void TestExportToExcelByDataTable()
        {
            DataTable dt = GetDataTable();
            string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果");
            Assert.IsTrue(File.Exists(excelPath));
        }

结果如下图示：

 

/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名
/// </summary>
[TestMethod]
public void TestExportToExcelByDataTable2()
{
    DataTable dt = GetDataTable();
    string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" };
    string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames);
    Assert.IsTrue(File.Exists(excelPath));
}

结果如下图示：

 

/// <summary>
      /// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名，以及导出列名的重命名
      /// </summary>
      [TestMethod]
      public void TestExportToExcelByDataTable3()
      {
          DataTable dt = GetDataTable();
          string[] expColNames = { "Col1", "Col2", "Col3", "Col4", "Col5" };
          Dictionary<string, string> expColAsNames = new Dictionary<string, string>() {
              {"Col1","列一"},
              {"Col2","列二"},
              {"Col3","列三"},
              {"Col4","列四"},
              {"Col5","列五"}
          };
          string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, expColNames,expColAsNames);
          Assert.IsTrue(File.Exists(excelPath));
      }

结果如下图示：

 

/// <summary>
      /// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出列名的重命名
      /// </summary>
      [TestMethod]
      public void TestExportToExcelByDataTable4()
      {
          DataTable dt = GetDataTable();
          Dictionary<string, string> expColAsNames = new Dictionary<string, string>() {
              {"Col1","列一"},
              {"Col5","列五"}
          };
          string excelPath = ExcelUtility.Export.ToExcel(dt, "导出结果", null, null, expColAsNames);
          Assert.IsTrue(File.Exists(excelPath));
      }

结果如下图示：

 

/// <summary>
/// 测试方法：测试依据模板+DataTable来生成EXCEL
/// </summary>
[TestMethod]
public void TestExportToExcelWithTemplateByDataTable()
{
    DataTable dt = GetDataTable();//获取数据
    string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
    SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器
 
    PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器
    partFormatterBuilder.AddFormatter("Title", "IT学员");//将模板表格中Title的值设置为跨越IT学员
    formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器
    cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
    formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    //实例化一个表格格式化器，dt.Select()是将DataTable转换成DataRow[]，name表示的模板表格中第一行第一个单元格要填充的数据参数名
    TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), "name");
    tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
        {"name",r=>r["Col1"]},//将模板表格中name对应DataTable中的列Col1
        {"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2
        {"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3
        {"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col
        {"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5
    });
    formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "table", formatterContainers);
    Assert.IsTrue(File.Exists(excelPath));
}

模板如下图示：

结果如下图示：

 

/// <summary>
/// 测试方法：测试依据模板+List来生成EXCEL
/// </summary>
[TestMethod]
public void TestExportToExcelWithTemplateByList()
{
    List<Student> studentList = GetStudentList();//获取数据
    string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
    SheetFormatterContainer<Student> formatterContainers = new SheetFormatterContainer<Student>(); //实例化一个模板数据格式化容器
 
    PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//实例化一个局部元素格式化器
    partFormatterBuilder.AddFormatter("Title", "IT学员");//将模板表格中Title的值设置为跨越IT学员
    formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//实例化一个单元格格式化器
    cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
    formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    //实例化一个表格格式化器，studentList本身就是可枚举的无需转换，name表示的模板表格中第一行第一个单元格要填充的数据参数名
    TableFormatterBuilder<Student> tableFormatterBuilder = new TableFormatterBuilder<Student>(studentList, "name");
    tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<Student, object>>{
        {"name",r=>r.Name},//将模板表格中name对应Student对象中的属性Name
        {"sex",r=>r.Sex},//将模板表格中sex对应Student对象中的属性Sex
        {"km",r=>r.KM},//将模板表格中km对应Student对象中的属性KM
        {"score",r=>r.Score},//将模板表格中score对应Student对象中的属性Score
        {"result",r=>r.Result}//将模板表格中result对应Student对象中的属性Result
    });
    formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
 
    string excelPath = ExcelUtility.Export.ToExcelWithTemplate<Student>(templateFilePath, "table", formatterContainers);
    Assert.IsTrue(File.Exists(excelPath));
 
}

结果如下图示：（模板与上面相同）

 

/// <summary>
/// 测试方法：测试依据模板+DataTable来生成多表格EXCEL（注意：由于NPOI框架限制，目前仅支持模板文件格式为：xls）
/// </summary>
[TestMethod]
public void TestExportToRepeaterExcelWithTemplateByDataTable()
{
    DataTable dt = GetDataTable();//获取数据
    string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel2.xls"; //获得EXCEL模板路径
    SheetFormatterContainer<DataRow> formatterContainers = new SheetFormatterContainer<DataRow>(); //实例化一个模板数据格式化容器
 
    //实例化一个可重复表格格式化器，dt.Select()是将DataTable转换成DataRow[]，rpt_begin表示的模板表格开始位置参数名，rpt_end表示的模板表格结束位置参数名
    RepeaterFormatterBuilder<DataRow> tableFormatterBuilder = new RepeaterFormatterBuilder<DataRow>(dt.Select(), "rpt_begin", "rpt_end");
    tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
        {"sex",r=>r["Col2"]},//将模板表格中sex对应DataTable中的列Col2
        {"km",r=>r["Col3"]},//将模板表格中km对应DataTable中的列Col3
        {"score",r=>r["Col4"]},//将模板表格中score对应DataTable中的列Col
        {"result",r=>r["Col5"]}//将模板表格中result对应DataTable中的列Co5
    });
 
    PartFormatterBuilder<DataRow> partFormatterBuilder2 = new PartFormatterBuilder<DataRow>();//实例化一个可嵌套的局部元素格式化器
    partFormatterBuilder2.AddFormatter("name", r => r["Col1"]);//将模板表格中name对应DataTable中的列Col1
    tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2);//添加到可重复表格格式化器中，作为其子格式化器
 
 
    CellFormatterBuilder<DataRow> cellFormatterBuilder = new CellFormatterBuilder<DataRow>();//实例化一个可嵌套的单元格格式化器
    cellFormatterBuilder.AddFormatter("rptdate", r => DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//将模板表格中rptdate的值设置为当前日期
    tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder);//添加到可重复表格格式化器中，作为其子格式化器
 
    formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
 
    string excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath, "multtable", formatterContainers);
    Assert.IsTrue(File.Exists(excelPath));
}

模板如下图示：（注意：该模板仅支持XLS格式文件，XLSX下存在问题）

结果如下图示：

以下是模拟数据来源所定义的方法（配合测试）：

private DataTable GetDataTable()
{
    DataTable dt = new DataTable();
    for (int i = 1; i <= 6; i++)
    {
        if (i == 4)
        {
            dt.Columns.Add("Col" + i.ToString(), typeof(double));
        }
        else
        {
            dt.Columns.Add("Col" + i.ToString(), typeof(string));
        }
    }
 
    for (int i = 1; i <= 10; i++)
    {
        dt.Rows.Add("Name" + i.ToString(), (i % 2) > 0 ? "男" : "女", "科目" + i.ToString(), i * new Random().Next(1, 5), "待定", Guid.NewGuid().ToString("N"));
    }
 
    return dt;
}
 
private List<Student> GetStudentList()
{
    List<Student> studentList = new List<Student>();
    for (int i = 1; i <= 10; i++)
    {
        studentList.Add(new Student
        {
            Name = "Name" + i.ToString(),
            Sex = (i % 2) > 0 ? "男" : "女",
            KM = "科目" + i.ToString(),
            Score = i * new Random().Next(1, 5),
            Result = "待定"
        });
    }
    return studentList;
}
 
class Student
{
    public string Name { get; set; }
 
    public string Sex { get; set; }
 
    public string KM { get; set; }
 
    public double Score { get; set; }
 
    public string Result { get; set; }
}

导入方法测试：

/// <summary>
   /// 测试方法：测试将指定的EXCEL数据导入到DataTable
   /// </summary>
   [TestMethod]
   public void TestImportToDataTableFromExcel()
   {
      //null表示由用户选择EXCEL文件路径，data表示要导入的sheet名,0表示数据标题行
      DataTable dt=  ExcelUtility.Import.ToDataTable(null, "data", 0);
      Assert.AreNotEqual(0, dt.Rows.Count);
   }

数据源文件内容如下图示：

 

下面贴出该类库主要源代码：

ExcelUtility.Export类：

using ExcelReport;
using ExcelUtility.Base;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 
namespace ExcelUtility
{
    public sealed class Export
    {
        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <returns></returns>
        public static string ToExcel(DataSet sourceDs, string filePath = null)
        {
 
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Common.GetSaveFilePath();
            }
 
            if (string.IsNullOrEmpty(filePath)) return null;
 
            bool isCompatible = Common.GetIsCompatible(filePath);
 
            IWorkbook workbook = Common.CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = Common.GetCellStyle(workbook,true);
            ICellStyle cellStyle = Common.GetCellStyle(workbook);
 
            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = string.IsNullOrEmpty(table.TableName) ? "result" + i.ToString() : table.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    ICell headerCell = headerRow.CreateCell(column.Ordinal);
                    headerCell.SetCellValue(column.ColumnName);
                    headerCell.CellStyle = headerCellStyle;
                    sheet.AutoSizeColumn(headerCell.ColumnIndex);
                }
 
                // handling value.
                int rowIndex = 1;
                 
                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
 
                    foreach (DataColumn column in table.Columns)
                    {
                        ICell cell = dataRow.CreateCell(column.Ordinal);
                        cell.SetCellValue((row[column] ?? "").ToString());
                        cell.CellStyle = cellStyle;
                        Common.ReSizeColumnWidth(sheet, cell);
                    }
 
                    rowIndex++;
                }
            }
 
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            workbook = null;
 
            return filePath;
 
        }
 
        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="colAliasNames">导出的列名重命名数组</param>
        /// <param name="sheetName">工作薄名称，可选</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <returns></returns>
        public static string ToExcel(DataTable sourceTable, string[] colAliasNames, string sheetName = "result", string filePath = null)
        {
            if (sourceTable.Rows.Count <= 0) return null;
 
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Common.GetSaveFilePath();
            }
 
            if (string.IsNullOrEmpty(filePath)) return null;
 
            if (colAliasNames == null || sourceTable.Columns.Count != colAliasNames.Length)
            {
                throw new ArgumentException("列名重命名数组与DataTable列集合不匹配。", "colAliasNames");
            }
 
            bool isCompatible = Common.GetIsCompatible(filePath);
 
            IWorkbook workbook = Common.CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
            ICellStyle cellStyle = Common.GetCellStyle(workbook);
 
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(colAliasNames[column.Ordinal]);
                headerCell.CellStyle = headerCellStyle;
                sheet.AutoSizeColumn(headerCell.ColumnIndex);
            }
 
            // handling value.
            int rowIndex = 1;
 
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
 
                foreach (DataColumn column in sourceTable.Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);
                    cell.SetCellValue((row[column] ?? "").ToString());
                    cell.CellStyle = cellStyle;
                    Common.ReSizeColumnWidth(sheet, cell);
                }
 
                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
 
            sheet = null;
            headerRow = null;
            workbook = null;
 
            return filePath;
        }
 
        /// <summary>
        /// 由DataGridView导出
        /// </summary>
        /// <param name="grid">要导出的DataGridView对象</param>
        /// <param name="sheetName">工作薄名称，可选</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <returns></returns>
        public static string ToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
        {
            if (grid.Rows.Count <= 0) return null;
 
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Common.GetSaveFilePath();
            }
 
            if (string.IsNullOrEmpty(filePath)) return null;
 
            bool isCompatible = Common.GetIsCompatible(filePath);
 
            IWorkbook workbook = Common.CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
            ICellStyle cellStyle = Common.GetCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);
 
            IRow headerRow = sheet.CreateRow(0);
 
            for (int i = 0; i < grid.Columns.Count; i++)
            {
                ICell headerCell = headerRow.CreateCell(i);
                headerCell.SetCellValue(grid.Columns[i].HeaderText);
                headerCell.CellStyle = headerCellStyle;
                sheet.AutoSizeColumn(headerCell.ColumnIndex);
            }
 
            int rowIndex = 1;
            foreach (DataGridViewRow row in grid.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < grid.Columns.Count; n++)
                {
                    ICell cell = dataRow.CreateCell(n);
                    cell.SetCellValue((row.Cells[n].Value ?? "").ToString());
                    cell.CellStyle = cellStyle;
                    Common.ReSizeColumnWidth(sheet, cell);
                }
                rowIndex++;
            }
 
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
 
            sheet = null;
            headerRow = null;
            workbook = null;
 
            return filePath;
        }
 
        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="sheetName">工作薄名称，可选</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <param name="colNames">需要导出的列名,可选</param>
        /// <param name="colAliasNames">导出的列名重命名，可选</param>
        /// <returns></returns>
        public static string ToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null, string[] colNames = null, IDictionary<string, string> colAliasNames = null)
        {
            if (sourceTable.Rows.Count <= 0) return null;
 
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Common.GetSaveFilePath();
            }
 
            if (string.IsNullOrEmpty(filePath)) return null;
 
            bool isCompatible = Common.GetIsCompatible(filePath);
 
            IWorkbook workbook = Common.CreateWorkbook(isCompatible);
            ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
            ICellStyle cellStyle = Common.GetCellStyle(workbook);
 
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
 
            if (colNames == null || colNames.Length <= 0)
            {
                colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();
            }
 
            // handling header.
            for (int i = 0; i < colNames.Length; i++)
            {
                ICell headerCell = headerRow.CreateCell(i);
                if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i]))
                {
                    headerCell.SetCellValue(colAliasNames[colNames[i]]);
                }
                else
                {
                    headerCell.SetCellValue(colNames[i]);
                }
                headerCell.CellStyle = headerCellStyle;
                sheet.AutoSizeColumn(headerCell.ColumnIndex);
            }
 
            // handling value.
            int rowIndex = 1;
 
            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
 
                for (int i = 0; i < colNames.Length; i++)
                {
                    ICell cell = dataRow.CreateCell(i);
                    cell.SetCellValue((row[colNames[i]] ?? "").ToString());
                    cell.CellStyle = cellStyle;
                    Common.ReSizeColumnWidth(sheet, cell);
                }
 
                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
 
            sheet = null;
            headerRow = null;
            workbook = null;
 
            return filePath;
        }
 
 
        /// <summary>
        ///由SheetFormatterContainer导出基于EXCEL模板的文件
        /// </summary>
        /// <param name="templatePath">模板路径</param>
        /// <param name="sheetName">模板中使用的工作薄名称</param>
        /// <param name="formatterContainer">模板数据格式化容器</param>
        /// <param name="filePath">导出路径，可选</param>
        /// <returns></returns>
 
        public static string ToExcelWithTemplate<T>(string templatePath, string sheetName, SheetFormatterContainer<T> formatterContainer, string filePath = null)
        {
 
            if (!File.Exists(templatePath))
            {
                throw new FileNotFoundException(templatePath + "文件不存在！");
            }
 
            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Common.GetSaveFilePath();
            }
 
            if (string.IsNullOrEmpty(filePath)) return null;
 
            string templateConfigFilePath = Common.GetTemplateConfigFilePath(templatePath, false);
 
            var workbookParameterContainer = new WorkbookParameterContainer();
            workbookParameterContainer.Load(templateConfigFilePath);
            SheetParameterContainer sheetParameterContainer = workbookParameterContainer[sheetName];
            ExportHelper.ExportToLocal(templatePath, filePath, new SheetFormatter(sheetName, formatterContainer.GetFormatters(sheetParameterContainer)));
 
            return filePath;
        }
 
 
    }
}

 

ExcelUtility.Import类：

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using ExcelUtility.Base;
 
namespace ExcelUtility
{
    public sealed class Import
    {
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        public static DataTable ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }
 
            DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
 
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
 
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径,可传空值</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = Common.GetOpenFilePath();
            }
 
            if (string.IsNullOrEmpty(excelFilePath))
            {
                return null;
            }
 
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = Common.GetIsCompatible(excelFilePath);
                return ToDataTable(stream, sheetName, headerRowIndex, isCompatible);
            }
        }
 
        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataSet</returns>
        public static DataSet ToDataSet(Stream excelFileStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }
 
            excelFileStream.Close();
            workbook = null;
 
            return ds;
        }
 
        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。可传空值</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ToDataSet(string excelFilePath, int headerRowIndex)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                excelFilePath = Common.GetOpenFilePath();
            }
 
            if (string.IsNullOrEmpty(excelFilePath))
            {
                return null;
            }
 
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = Common.GetIsCompatible(excelFilePath);
                return ToDataSet(stream, headerRowIndex, isCompatible);
            }
        }
 
    }
}

 

Common类根据单元格内容重新设置列宽ReSizeColumnWidth

/// <summary>
/// 根据单元格内容重新设置列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
public static void ReSizeColumnWidth(ISheet sheet, ICell cell)
{
    int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256;
    const int maxLength = 255 * 256;
    if (cellLength > maxLength)
    {
        cellLength = maxLength;
    }
    int colWidth = sheet.GetColumnWidth(cell.ColumnIndex);
    if (colWidth < cellLength)
    {
        sheet.SetColumnWidth(cell.ColumnIndex, cellLength);
    }
}

注意这个方法中，列宽自动设置最大宽度为255个字符宽度。

/// <summary>
/// 创建表格样式
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)
{
    ICellStyle style = workbook.CreateCellStyle();
 
    if (isHeaderRow)
    {
        style.FillPattern = FillPattern.SolidForeground;
        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
        IFont f = workbook.CreateFont();
        f.Boldweight = (short)FontBoldWeight.Bold;
        style.SetFont(f);
    }
 
    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
    return style;
}

发文时，部份方法代码已经更新了，所以实际效果以GIT项目中的为准。

 该类库源码已分享到该路径中：http://git.oschina.net/zuowj/ExcelUtility　　　　GIT Repository路径：git@git.oschina.net:zuowj/ExcelUtility.git