使用NPOI访问、控制Excel

工作中经常使用Excel，很多重复的数据操作或者检查都丢给程序做是最好的。

最一开始选择使用Com组件来作业，水平有限，使用效果不太理想，全是动态类型真的用起来有点累，异常处理也要小心谨慎。

之后选择了OleDB进行excel操作，但是把Excel作为数据库操作，会有下面的问题：

  关系数据库第一范式规定了对某一列中的数据只能有一种属性，因此当excel中某列既有string型又有Numeric型数据时，只能读到其中的一种，导致数据读入不全。

最终选择了NPOI作为解决方案，目前效果比较理想，写了个助手方法，方便日常使用。

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

namespace Rainbow.Core
{
    /// <summary>
    /// Helper for NOPI to operate excel files.
    /// </summary>
    public static class NPOIHelper
    {
        #region public methods
        /// <summary>
        /// Get the IWorkbook of the dest excel file.
        /// </summary>
        /// <param name="filePath">The dest file path.</param>
        /// <exception cref="RainstormException"></exception>
        /// <returns></returns>
        static public IWorkbook GetDestWorkBook(string filePath)
        {
            CoreUtility.CheckExcelFile(filePath);

            IWorkbook workBook = null;
            FunctionExcuteHelper.ExcuteIOFunction(() =>
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (Path.GetExtension(filePath) == ".xls")
                        workBook = new HSSFWorkbook(fs);
                    else
                        workBook = new XSSFWorkbook(fs);
                }
            });
            return workBook;
        }
        /// <summary>
        /// Get the ISheet of the dest sheet.
        /// </summary>
        /// <param name="filePath">The dest file path.</param>
        /// <param name="sheetName">The dest sheet name.</param>
        /// <returns></returns>
        static public ISheet GetDestWorkSheet(string filePath, string sheetName)
        {
            IWorkbook workBook = GetDestWorkBook(filePath);

            ISheet workSheet = null;
            workSheet = GetDestWorkSheet(workBook, sheetName);

            return workSheet;
        }
        /// <summary>
        /// Get the ISheet of the dest sheet.
        /// </summary>
        /// <param name="workBook">The IWorkbook dest file.</param>
        /// <param name="sheetName">The dest sheet name.</param>
        /// <returns></returns>
        static public ISheet GetDestWorkSheet(IWorkbook workBook, string sheetName)
        {
            ISheet workSheet = null;
            workSheet = workBook.GetSheet(sheetName);

            if (workSheet == null)
                throw new Exception(string.Format("There is no sheet named <{0}> in the excel.", sheetName));

            return workSheet;
        }
        /// <summary>
        /// Read dest sheet as a DataTable.
        /// The column is named with 'F1 F2 ...'
        /// </summary>
        /// <param name="filePath">The dest file path.</param>
        /// <param name="sheetName">The dest sheet name.</param>
        /// <param name="startRow">Start line number.</param>
        /// <returns></returns>
        static public DataTable ReadSheetAsDataTable(string filePath, string sheetName, int startRow = 0)
        {
            ISheet sheet = GetDestWorkSheet(filePath, sheetName);
            return GetDataTableFromISheet(sheet, startRow);
        }
        /// <summary>
        /// Read dest excel as a DataTable.
        /// The tables are named with sheet name.
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        static public DataSet ReadExcelAsDataSet(string filePath)
        {
            string fileName = System.IO.Path.GetFileNameWithoutExtension(filePath);
            DataSet ds = new DataSet(fileName);

            IWorkbook workBook = GetDestWorkBook(filePath);
            for (int i = 0; i < workBook.NumberOfSheets; i++)
                ds.Tables.Add(GetDataTableFromISheet(workBook.GetSheetAt(i)));
            return ds;
        }
        /// <summary>
        /// Get the string value of the ICell.
        /// </summary>
        /// <param name="cell">Dest ICell.</param>
        /// <returns></returns>
        static public string GetDestCellStringValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            string result = string.Empty;
            switch (cell.CellType)
            {
                case CellType.String:
                    result = cell.StringCellValue;
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                        result = cell.DateCellValue.ToShortDateString();
                    else
                        result = cell.NumericCellValue.ToString();
                    break;                    
                default:
                    result = string.Empty;
                    break;
            }
            return result;
        }
        /// <summary>
        /// Set dest cell a string value.
        /// </summary>
        /// <typeparam name="TValue">string ,int or any Type overrided ToString().</typeparam>
        /// <param name="row"></param>
        /// <param name="cellPosition"></param>
        /// <param name="value"></param>
        static public void SetDestCellStringValue<TValue>(IRow row, int cellPosition, TValue value)
        {
            if (row == null)
                throw new Exception("The row is a NULL row.");

            if (row.GetCell(cellPosition) == null)
                row.CreateCell(cellPosition).SetCellValue(value.ToString());
            else
                row.GetCell(cellPosition).SetCellValue(value.ToString());
        }
        /// <summary>
        /// Set dest cell a string value and dest style.
        /// </summary>
        /// <typeparam name="TValue"></typeparam>
        /// <param name="row"></param>
        /// <param name="cellPosition"></param>
        /// <param name="style"></param>
        /// <param name="value"></param>
        static public void SetDestCellStringValueAndStyle<TValue>(IRow row, int cellPosition, ICellStyle style, TValue value)
        {
            SetDestCellStringValue<TValue>(row, cellPosition, value);
            ICell cell = row.GetCell(cellPosition);
            cell.CellStyle = style;
        }
        /// <summary>
        /// Get all sheet names as a list.
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        static public List<string> GetAllSheetNames(string filePath)
        {
            return GetAllSheetNames(GetDestWorkBook(filePath));
        }
        /// <summary>
        /// Get all sheet names as a list.
        /// </summary>
        /// <param name="workBook"></param>
        /// <returns></returns>
        static public List<string> GetAllSheetNames(IWorkbook workBook)
        {
            List<string> result = new List<string>();

            for (int i = 0; i < workBook.NumberOfSheets; i++)
                result.Add(workBook.GetSheetName(i));

            return result;
        }

        /// <summary>
        /// Save workBook to dest excel file.
        /// !!Exception for xlsx!!
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="workBook"></param>
/*         static public void SaveWorkBookToExcelFile(IWorkbook workBook, string filePath)
        {
            CoreUtility.CheckExcelFile(filePath);

            FunctionExcuteHelper.ExcuteIOFunction(() =>
                {
                    using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
                        workBook.Write(fs);
                });            
        } 
*/
        /// <summary>
        /// Convert a DataSet to a excel file.
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="filePath"></param>
        static public void DataSetToExcel(DataSet dataSet, string filePath)
        {
            CoreUtility.CheckExcelFile(filePath);
            var ext = Path.GetExtension(filePath);

            FunctionExcuteHelper.ExcuteIOFunction(() =>
                {
                    var workbook = DataSetToWorkBook(dataSet, ext);

                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                    }
                });
        }
        /// <summary>
        /// Read dest ISheet as a DataTable.
        /// The column is named with 'F1 F2 ...'
        /// </summary>
        /// <param name="workSheet"></param>
        /// <param name="startRow"></param>
        /// <returns></returns>
        static public DataTable GetDataTableFromISheet(ISheet workSheet, int startRow = 0)
        {
            if (workSheet == null)
                throw new Exception("Dest work sheet does not exists.");

            DataTable result = new DataTable(workSheet.SheetName);

            int columnNumber = GuessSheetColumnNumber(workSheet);

            for (int i = 0; i < columnNumber; i++)
                result.Columns.Add(string.Format("F{0}", i + 1), typeof(string));

            for (int i = startRow; i <= workSheet.LastRowNum; i++)
            {
                IRow row = workSheet.GetRow(i);
                if (row == null)
                    continue;
                result.Rows.Add(ConvertIRowToDataRow(row, result));
            }

            return result;
        }
        #endregion

        #region private methods

        static private int GuessSheetColumnNumber(ISheet workSheet)
        {
            IRow headerRow = workSheet.GetRow(workSheet.FirstRowNum);
            IRow middleRow = workSheet.GetRow(workSheet.LastRowNum / 2);
            IRow lastRow = workSheet.GetRow(workSheet.LastRowNum);

            int result = 0;
            result = Math.Max(headerRow.LastCellNum, middleRow.LastCellNum);
            result = Math.Max(result, lastRow.LastCellNum);
            return result;
        }

        static private DataRow ConvertIRowToDataRow(IRow row, DataTable dataTable)
        {
            DataRow dr = dataTable.NewRow();

            for (int i = 0; i < dataTable.Columns.Count; i++)
                dr[i] = GetDestCellStringValue(row.GetCell(i));

            return dr;
        }

        static private void DataTableToWorkSheet(DataTable dataTable, ISheet sheet)
        {
            IRow headerRow = sheet.CreateRow(0);
            //Set the header
            foreach (DataColumn dc in dataTable.Columns)
                headerRow.CreateCell(dc.Ordinal).SetCellValue(dc.ColumnName);

            //Set the content
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                    row.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
            }
        }

        static private IWorkbook DataSetToWorkBook(DataSet dataSet, string extension)
        {
            IWorkbook workBook = null;
            if (extension == ".xls")
                workBook = new HSSFWorkbook();
            else
                workBook = new XSSFWorkbook();

            try
            {
                foreach (DataTable dt in dataSet.Tables)
                {
                    ISheet sheet = null;
                    sheet = workBook.CreateSheet(dt.TableName);
                    DataTableToWorkSheet(dt, sheet);
                }
            }
            catch (Exception)
            {
                throw new Exception(
                    string.Format("Error while converting DataSet <{0}> to WorkBook.", dataSet.DataSetName));
            }

            return workBook;
        }
        #endregion
    }
}