用NPOI读.xls文件中的表格

前面我们通过一系列文章介绍了Office PIA操作Excel文档的方法，但这并不是操作Excel文档的唯一方法。本文提供的代码示例通过NPOI来读Excel，这样的实现方法有三个好处：

1. 运行环境不依赖于Office Application；
2. 由于不需要打开Office Application进程，直接分析文件，所以执行速度快；
3. 可并发。

但是本文的例子也有局限，这个局限并非没有办法解决，只是我暂时还没有需求来解决这个局限，所以就先这样了：

1. 本文的例子只支持读Excel 2003格式的工作簿（.xls）。这个格式也称作BIFF8格式，相对于Open XML格式来说；
2. 本文读Excel Worksheet的表格的方法是有局限的。具体是啥局限，看懂源代码就知道了。

本示例基于NPOI v1.2.4。NPOI是大名鼎鼎的Java开源Office文档操作库POI的.Net porting。该类库基于Apache 2.0开源协议，在遵守此协议的前提下可自由且免费地应用于商业、非商业、开源或闭源项目中。

好了，下面我们贴代码：

// -----------------------------------------------------------------------
// <copyright file="NpoiExcelAccessor.cs" Author="Yaping Xin">
//
//  File Name           : NpoiExcelAccessor.cs
//  Description         : Excel document accessor implemented using NPOI.
//
// </copyright>
// -----------------------------------------------------------------------

namespace ******.Common.Reporting.Excel
{
    using System;
    using System.Data;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;

    /// <summary>
    /// Excel document accessor implemented using NPOI.
    /// Dependency on NPOI.dll of NPOI v1.2.4 .Net 4.0
    /// </summary>
    public class NpoiExcelAccessor : IDisposable
    {
        #region Private variables for IDisposable
        /// <summary>Indicates disposing status.</summary>
        private bool disposed = false;
        #endregion

        #region Private variables
        /// <summary>Excel document file path.</summary>
        private string pathName = string.Empty;

        /// <summary>Excel document file name.</summary>
        private string fileName = string.Empty;

        /// <summary>Excel document full path.</summary>
        private string fullPath = string.Empty;

        /// <summary>File Stream for operating the Excel document.</summary>
        private FileStream fileStream = null;
        #endregion

        #region Initializations and Finalizations
        /// <summary>
        /// Finalizes an instance of the NpoiExcelAccessor class.
        /// </summary>
        ~NpoiExcelAccessor()
        {
            this.Dispose(false);
        }
        #endregion

        #region Properties for IDisposable
        /// <summary>
        /// Gets a value indicating whether this instance is disposed.
        /// </summary>
        public bool Disposed
        {
            get { return this.disposed; }
        }
        #endregion

        #region Properties for Excel document
        /// <summary>
        /// Gets current workbook instance.
        /// </summary>
        public HSSFWorkbook CurrentWorkbook { get; private set; }

        /// <summary>
        /// Gets latest Worksheet instance in last operating.
        /// </summary>
        public ISheet LatestWorksheet { get; private set; }

        /// <summary>
        /// Gets current Worksheet instance.
        /// </summary>
        public ISheet CurrentWorksheet { get; private set; }

        /// <summary>
        /// Gets ActiveSheet in current workbook
        /// </summary>
        public ISheet ActiveWorksheet
        {
            get { return this.CurrentWorkbook.GetSheetAt(this.CurrentWorkbook.ActiveSheetIndex); }
        }

        /// <summary>
        /// Gets excel document path name
        /// </summary>
        public string PathName
        {
            get { return this.pathName; }
        }

        /// <summary>
        /// Gets excel document file name
        /// </summary>
        public string FileName
        {
            get { return this.fileName; }
        }

        /// <summary>
        /// Gets excel document full path
        /// </summary>
        public string FullPath
        {
            get { return this.fullPath; }
        }

        /// <summary>Gets latest error code.</summary>
        public uint ErrorCode { get; private set; }

        /// <summary>Gets latest error description.</summary>
        public string ErrorDescription { get; private set; }

        /// <summary>Gets latest exception.</summary>
        public Exception Exception { get; private set; }
        #endregion

        #region IDisposable members
        /// <summary>
        /// Close method.
        /// </summary>
        public void Close()
        {
            if (this.CurrentWorkbook != null)
            {
                this.CurrentWorkbook.Dispose();
            }

            if (this.fileStream != null)
            {
                this.fileStream.Close();
                this.fileStream.Dispose();
                this.fileStream = null;
            }
        }

        /// <summary>
        /// Implement Dispose method in IDisposable
        /// </summary>
        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion

        #region Public Excel methods - Workbook
        /// <summary>
        /// Open Excel workbook in .xls file formatting.
        /// </summary>
        /// <param name="filePath">Excel file full path.</param>
        /// <returns>True indicates opened successfully. 
        /// False indicates error occured.</returns>
        public bool OpenXLS(string filePath)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    this.ErrorCode = 0xFFFF;
                    this.ErrorDescription = "File not found.";
                    return false;
                }

                 Check file extension is '.xls'
                 Set FileName, FilePath

                this.fileStream = new FileStream(filePath, FileMode.Open);
                if (this.fileStream == null)
                {
                    this.ErrorCode = 0xFFFF;
                    this.ErrorDescription = "Failed in open file.";
                    return false;
                }

                this.CurrentWorkbook = new HSSFWorkbook(this.fileStream);
                if (this.CurrentWorkbook == null)
                {
                    this.ErrorCode = 0xFFFF;
                    this.ErrorDescription = "Failed in open workbook.";
                    return false;
                }
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = ex.Message;

                return false;
            }

            return true;
        }
        #endregion

        #region Public Excel methods - Worksheet
        /// <summary>
        /// Set current worksheet by sheet index.
        /// </summary>
        /// <param name="sheetIndex">worksheet index</param>
        public bool SetCurrentWorksheet(int sheetIndex)
        {
            if (this.CurrentWorkbook == null)
            {
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = "Workbook not opened.";
                return false;
            }

            try
            {
                this.CurrentWorksheet = this.CurrentWorkbook.GetSheetAt(sheetIndex);
                if (this.CurrentWorksheet == null)
                {
                    this.ErrorCode = 0xFFFF;
                    this.ErrorDescription = "Sheet not found.";
                    return false;
                }
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = ex.Message;

                return false;
            }

            return true;
        }

        /// <summary>
        /// Set current worksheet by sheet name.
        /// </summary>
        /// <param name="sheetName">>worksheet name</param>
        /// <returns></returns>
        public bool SetCurrentWorksheet(string sheetName)
        {
            if (this.CurrentWorkbook == null)
            {
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = "Workbook not opened.";
                return false;
            }

            try
            {
                this.CurrentWorksheet = this.CurrentWorkbook.GetSheet(sheetName);
                if (this.CurrentWorksheet == null)
                {
                    this.ErrorCode = 0xFFFF;
                    this.ErrorDescription = "Sheet not found.";
                    return false;
                }
            }
            catch (Exception ex)
            {
                this.Exception = ex;
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = ex.Message;

                return false;
            }

            return true;
        }
        #endregion

        #region Public Excel methods - ReadTableWithHeader
        public DataTable ReadTableWithHeader(int headerRowBeginIndex)
        {
            IRow row = this.CurrentWorksheet.GetRow(headerRowBeginIndex);
            if (row != null)
            {
                HSSFRow headerRow = this.CurrentWorksheet.GetRow(headerRowBeginIndex) as HSSFRow;
                int columnBeginIndex = headerRow.FirstCellNum;
                int columnEndIndex = headerRow.LastCellNum - 1;

                int rowEndIndex = this.CurrentWorksheet.LastRowNum;

                return this.ReadTableWithHeader(
                    headerRowBeginIndex,
                    columnBeginIndex,
                    rowEndIndex,
                    columnEndIndex);
            }

            return null;
        }

        public DataTable ReadTableWithHeader(
            int headerRowBeginIndex,
            int columnBeginIndex)
        {
            HSSFRow headerRow = this.CurrentWorksheet.GetRow(headerRowBeginIndex) as HSSFRow;
            int columnEndIndex = headerRow.LastCellNum - 1;

            int rowEndIndex = this.CurrentWorksheet.LastRowNum;

            return this.ReadTableWithHeader(
                headerRowBeginIndex, 
                columnBeginIndex, 
                rowEndIndex, 
                columnEndIndex);
        }

        public DataTable ReadTableWithHeader(
            int headerRowBeginIndex, 
            int columnBeginIndex, 
            int rowEndIndex, 
            int columnEndIndex)
        {
            if (rowEndIndex < headerRowBeginIndex || columnEndIndex < columnBeginIndex)
            {
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = "ReadTableWithHeader() parameters validation failed.";

                return null;
            }

            DataTable table = new DataTable();

            HSSFRow headerRow = this.CurrentWorksheet.GetRow(headerRowBeginIndex) as HSSFRow;
            if (columnEndIndex >= headerRow.LastCellNum)
            {
                columnEndIndex = headerRow.LastCellNum - 1;
            }

            for (int i = columnBeginIndex; i <= columnEndIndex; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            if (rowEndIndex > headerRowBeginIndex)
            {
                for (int i = headerRowBeginIndex + 1; i <= rowEndIndex; i++)
                {
                    HSSFRow row = this.CurrentWorksheet.GetRow(i) as HSSFRow;
                    if (row != null)
                    {
                        DataRow dataRow = table.NewRow();

                        for (int j = columnBeginIndex; j <= columnEndIndex; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                CellType cellType = row.GetCell(j).CellType;

                                switch (cellType)
                                {
                                    case CellType.STRING:
                                        dataRow[j - columnBeginIndex] = row.GetCell(j).StringCellValue;
                                        break;
                                    case CellType.NUMERIC:
                                        dataRow[j - columnBeginIndex] = row.GetCell(j).NumericCellValue;
                                        break;
                                    default:
                                        dataRow[j - columnBeginIndex] = row.GetCell(j);
                                        break;
                                }
                            }
                        }

                        table.Rows.Add(dataRow);
                    }
                }
            }

            return table;
        }
        #endregion

        #region Public Excel methods - ReadTableWithoutHeader
        public DataTable ReadTableWithoutHeader(int rowBeginIndex)
        {
            HSSFRow headerRow = this.CurrentWorksheet.GetRow(rowBeginIndex) as HSSFRow;
            int columnBeginIndex = headerRow.FirstCellNum;
            int columnEndIndex = headerRow.LastCellNum - 1;

            int rowEndIndex = this.CurrentWorksheet.LastRowNum;

            return this.ReadTableWithoutHeader(
                rowBeginIndex,
                columnBeginIndex,
                rowEndIndex,
                columnEndIndex);
        }

        public DataTable ReadTableWithoutHeader(
            int rowBeginIndex,
            int columnBeginIndex)
        {
            HSSFRow headerRow = this.CurrentWorksheet.GetRow(rowBeginIndex) as HSSFRow;
            int columnEndIndex = headerRow.LastCellNum - 1;

            int rowEndIndex = this.CurrentWorksheet.LastRowNum;

            return this.ReadTableWithoutHeader(
                rowBeginIndex,
                columnBeginIndex,
                rowEndIndex,
                columnEndIndex);
        }

        public DataTable ReadTableWithoutHeader(
            int rowBeginIndex,
            int columnBeginIndex,
            int columnEndIndex)
        {
            int rowEndIndex = this.CurrentWorksheet.LastRowNum;

            return this.ReadTableWithoutHeader(
                rowBeginIndex,
                columnBeginIndex,
                rowEndIndex,
                columnEndIndex);
        }

        public DataTable ReadTableWithoutHeader(
            int rowBeginIndex,
            int columnBeginIndex,
            int rowEndIndex,
            int columnEndIndex)
        {
            if (rowEndIndex < rowBeginIndex || columnEndIndex < columnBeginIndex)
            {
                this.ErrorCode = 0xFFFF;
                this.ErrorDescription = "ReadTableWithHeader() parameters validation failed.";

                return null;
            }

            DataTable table = new DataTable();

            for (int i = columnBeginIndex; i <= columnEndIndex; i++)
            {
                DataColumn column = new DataColumn();
                table.Columns.Add(column);
            }

            for (int i = rowBeginIndex; i <= rowEndIndex; i++)
            {
                HSSFRow row = this.CurrentWorksheet.GetRow(i) as HSSFRow;
                DataRow dataRow = table.NewRow();

                for (int j = columnBeginIndex; j <= columnEndIndex; j++)
                {
                    CellType cellType = row.GetCell(j).CellType;

                    switch (cellType)
                    {
                        case CellType.STRING:
                            dataRow[j - columnBeginIndex] = row.GetCell(j).StringCellValue;
                            break;
                        case CellType.NUMERIC:
                            dataRow[j - columnBeginIndex] = row.GetCell(j).NumericCellValue;
                            break;
                        default:
                            dataRow[j - columnBeginIndex] = row.GetCell(j);
                            break;
                    }
                }

                table.Rows.Add(dataRow);
            }

            return table;
        }
        #endregion

        #region Private methods for IDisposable
        /// <summary>
        /// Dispose method
        /// </summary>
        /// <param name="disposing">Disposing status</param>
        protected void Dispose(bool disposing)
        {
            if (this.disposed)
            {
                return;
            }

            if (disposing)
            {
                this.Close();
            }

             Cleanup un-managed resources

            this.disposed = true;
        }
        #endregion

        #region Private methods
        /// <summary>
        /// Set actual full path of the Excel document.
        /// </summary>
        /// <param name="fullPath">inputed full path</param>
        private void SetActualFullPath(string fullPath)
        {
            this.pathName = Path.GetDirectoryName(fullPath);
            this.fileName = Path.GetFileName(fullPath);

            if (string.IsNullOrEmpty(this.fileName))
            {
                throw new ArgumentException("Excel reporting file name cannot be empty.", "fileFullPath");
            }

            if (string.IsNullOrEmpty(this.pathName))
            {
                this.pathName = Environment.CurrentDirectory;
            }

            this.fullPath = Path.Combine(this.pathName, this.fileName);
        }
        #endregion
    }
}