免費高效實用的.NET操作Excel組件NPOI（.NET組件介紹之六）

很多的軟體項目幾乎都包含著對文檔的操作，前面已經介紹過兩款操作文檔的組件，現在介紹一款文檔操作的組件NPOI。 NPOI可以生成沒有安裝在您的伺服器上的Microsoft Office套件的Excel報表，並且在後臺調用Microsoft Excel ActiveX更有效率;從Office文檔中提取 ...

很多的軟體項目幾乎都包含著對文檔的操作，前面已經介紹過兩款操作文檔的組件，現在介紹一款文檔操作的組件NPOI。

NPOI可以生成沒有安裝在您的伺服器上的Microsoft Office套件的Excel報表，並且在後臺調用Microsoft Excel ActiveX更有效率;從Office文檔中提取文本，以幫助您實現全文索引功能（大多數時候，此功能用於創建搜索引擎）；從Office文檔提取圖像；生成包含公式的Excel工作表。

一.NPOI組件概述：

NPOI是完全免費使用；涵蓋Excel的大多數功能（單元格樣式，數據格式，公式等）；支持xls，xlsx，docx；設計為面向介面（看看NPOI.SS命名空間）；支持不僅導出而且導入； .Net 2.0甚至為xlsx和docx（雖然我們也支持.NET 4.0）；來自世界各地的成功案例；巨大的基本例子；對隔離存儲沒有依賴。

以上是NPOI的優點，其他一些優點可以不用太在意，估計很多人對“支持xls，xlsx，docx”這一特點感覺有些驚訝，因為在很多人的印象裡面NPOI就是對Excel進行相關的操作，但是在這裡突然看到了對docx也可以操作，這一特點可能讓很多人感到欣喜，因為NPOI的的確確是一個很不錯的組件，用過的人都說好，我也不例外。

NPOI的運行要求：VS2010與.NET 4.0運行時；VS2005或VS2008與.NET 2.0運行時（SP1）；vs2003與.NET 1.1；Mono；ASP.NET中的中等信任環境。

二.NPOI核心類和方法解析：

以上是對NPOI的相關背景和使用環境做了一個簡單的介紹，接下來我具體的看一下NPOI的一些核心類和方法，由於下載的是DLL文件，還是採用.NET Reflector對DLL文件進行反編譯，以此查看源代碼。

如果需要具體的瞭解NPOI可以直接訪問：http://npoi.codeplex.com/SourceControl/latest，提供了NPOI的源碼和一些demo，由於本溪介紹的重點是NPOI對Excel的操作，所以下麵的類和實例主要是對操作Excel的介紹，如果需要對docx的操作，可以具體查看相應的類demo。

1.XSSFWorkbook類CreateSheet():創建表。

public ISheet CreateSheet(string sheetname)
{
    if (sheetname == null)
    {
        throw new ArgumentException("sheetName must not be null");
    }
    if (this.ContainsSheet(sheetname, this.sheets.Count))
    {
        throw new ArgumentException("The workbook already contains a sheet of this name");
    }
    if (sheetname.Length > 0x1f)
    {
        sheetname = sheetname.Substring(0, 0x1f);
    }
    WorkbookUtil.ValidateSheetName(sheetname);
    CT_Sheet sheet = this.AddSheet(sheetname);
    int index = 1;
    foreach (XSSFSheet sheet2 in this.sheets)
    {
        index = (int) Math.Max((long) (sheet2.sheet.sheetId + 1), (long) index);
    }
Label_0099:
    foreach (XSSFSheet sheet3 in this.sheets)
    {
        index = (int) Math.Max((long) (sheet3.sheet.sheetId + 1), (long) index);
    }
    string fileName = XSSFRelation.WORKSHEET.GetFileName(index);
    foreach (POIXMLDocumentPart part in base.GetRelations())
    {
        if ((part.GetPackagePart() != null) && fileName.Equals(part.GetPackagePart().PartName.Name))
        {
            index++;
            goto Label_0099;
        }
    }
    XSSFSheet item = (XSSFSheet) base.CreateRelationship(XSSFRelation.WORKSHEET, XSSFFactory.GetInstance(), index);
    item.sheet = sheet;
    sheet.id = item.GetPackageRelationship().Id;
    sheet.sheetId = (uint) index;
    if (this.sheets.Count == 0)
    {
        item.IsSelected = true;
    }
    this.sheets.Add(item);
    return item;
}

2.XSSFSheet類Write():將文件流寫入到excel。

internal virtual void Write(Stream stream)
{
    bool flag = false;
    if (this.worksheet.sizeOfColsArray() == 1)
    {
        CT_Cols colsArray = this.worksheet.GetColsArray(0);
        if (colsArray.sizeOfColArray() == 0)
        {
            flag = true;
            this.worksheet.SetColsArray(null);
        }
        else
        {
            this.SetColWidthAttribute(colsArray);
        }
    }
    if (this.hyperlinks.Count > 0)
    {
        if (this.worksheet.hyperlinks == null)
        {
            this.worksheet.AddNewHyperlinks();
        }
        CT_Hyperlink[] array = new CT_Hyperlink[this.hyperlinks.Count];
        for (int i = 0; i < array.Length; i++)
        {
            XSSFHyperlink hyperlink = this.hyperlinks[i];
            hyperlink.GenerateRelationIfNeeded(base.GetPackagePart());
            array[i] = hyperlink.GetCTHyperlink();
        }
        this.worksheet.hyperlinks.SetHyperlinkArray(array);
    }
    foreach (XSSFRow row in this._rows.Values)
    {
        row.OnDocumentWrite();
    }
    Dictionary<string, string> dictionary = new Dictionary<string, string>();
    dictionary[ST_RelationshipId.NamespaceURI] = "r";
    new WorksheetDocument(this.worksheet).Save(stream);
    if (flag)
    {
        this.worksheet.AddNewCols();
    }
}

3.XSSFSheet類CreateRow()：創建行。

public virtual IRow CreateRow(int rownum)
{
    CT_Row cTRow;
    XSSFRow row2 = this._rows.ContainsKey(rownum) ? this._rows[rownum] : null;
    if (row2 != null)
    {
        cTRow = row2.GetCTRow();
        cTRow.Set(new CT_Row());
    }
    else if ((this._rows.Count == 0) || (rownum > this.GetLastKey(this._rows.Keys)))
    {
        cTRow = this.worksheet.sheetData.AddNewRow();
    }
    else
    {
        int count = this.HeadMap(this._rows, rownum).Count;
        cTRow = this.worksheet.sheetData.InsertNewRow(count);
    }
    XSSFRow row3 = new XSSFRow(cTRow, this) {
        RowNum = rownum
    };
    this._rows[rownum] = row3;
    return row3;
}

4.XSSFWorkbook類GetSheet：獲取表。

public ISheet GetSheet(string name)
{
    foreach (XSSFSheet sheet in this.sheets)
    {
        if (name.Equals(sheet.SheetName, StringComparison.InvariantCultureIgnoreCase))
        {
            return sheet;
        }
    }
    return null;
}

5.WorkbookFactory類：

  public class PropertySetFactory
  {
    public static PropertySet Create(DirectoryEntry dir, string name);
    public static PropertySet Create(Stream stream);
    public static SummaryInformation CreateSummaryInformation();
    public static DocumentSummaryInformation CreateDocumentSummaryInformation();
  }

6.DocumentSummaryInformation：

[Serializable]
public class DocumentSummaryInformation : SpecialPropertySet
{
    // Fields
    public const string DEFAULT_STREAM_NAME = "\x0005DocumentSummaryInformation";

    // Methods
    public DocumentSummaryInformation(PropertySet ps);
    private void EnsureSection2();
    public void RemoveByteCount();
    public void RemoveCategory();
    public void RemoveCompany();
    public void RemoveCustomProperties();
    public void RemoveDocparts();
    public void RemoveHeadingPair();
    public void RemoveHiddenCount();
    public void RemoveLineCount();
    public void RemoveLinksDirty();
    public void RemoveManager();
    public void RemoveMMClipCount();
    public void RemoveNoteCount();
    public void RemoveParCount();
    public void RemovePresentationFormat();
    public void RemoveScale();
    public void RemoveSlideCount();

    // Properties
    public int ByteCount { get; set; }
    public string Category { get; set; }
    public string Company { get; set; }
    public CustomProperties CustomProperties { get; set; }
    public byte[] Docparts { get; set; }
    public byte[] HeadingPair { get; set; }
    public int HiddenCount { get; set; }
    public int LineCount { get; set; }
    public bool LinksDirty { get; set; }
    public string Manager { get; set; }
    public int MMClipCount { get; set; }
    public int NoteCount { get; set; }
    public int ParCount { get; set; }
    public string PresentationFormat { get; set; }
    public override PropertyIDMap PropertySetIDMap { get; }
    public bool Scale { get; set; }
    public int SlideCount { get; set; }
}



具體方法：
private void EnsureSection2()
{
    if (this.SectionCount < 2)
    {
        MutableSection section = new MutableSection();
        section.SetFormatID(SectionIDMap.DOCUMENT_SUMMARY_INFORMATION_ID2);
        this.AddSection(section);
    }
}

以上只是對部分的類和方法做了簡單的說明，需要瞭解更多的內容，可以進入官網下載源碼，或者使用軟體查看DLL文件。

三.NPOI操作實例：

1.枚舉(Excel單元格數據類型)：

    /// <summary>
    /// 枚舉(Excel單元格數據類型)
    /// </summary>
    public enum NpoiDataType
    {
        /// <summary>
        /// 字元串類型-值為1
        /// </summary>
        String,
        /// <summary>
        /// 布爾類型-值為2
        /// </summary>
        Bool,
        /// <summary>
        /// 時間類型-值為3
        /// </summary>
        Datetime,
        /// <summary>
        /// 數字類型-值為4
        /// </summary>
        Numeric,
        /// <summary>
        /// 複雜文本類型-值為5
        /// </summary>
        Richtext,
        /// <summary>
        /// 空白
        /// </summary>
        Blank,
        /// <summary>
        /// 錯誤
        /// </summary>
        Error
    }

2. 將DataTable數據導入到excel中：

        /// <summary>
        /// 將DataTable數據導入到excel中
        /// </summary>
        /// <param name="data">要導入的數據</param>
        /// <param name="isColumnWritten">DataTable的列名是否要導入</param>
        /// <param name="sheetName">要導入的excel的sheet的名稱</param>
        /// <param name="fileName">文件夾路徑</param>
        /// <returns>導入數據行數(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }                
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                ISheet sheet;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                int j;
                int count;
                //寫入DataTable的列名，寫入單元格中
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //遍歷迴圈datatable具體數據項
                int i;
                for (i = 0; i < data.Rows.Count; ++i)
                {
                    var row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                //將文件流寫入到excel
                workbook.Write(fs);
                return count;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

3.將excel中的數據導入到DataTable中：

        /// <summary>
        /// 將excel中的數據導入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名稱</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="fileName">文件路徑</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            var data = new DataTable();
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果沒有找到指定的sheetName對應的sheet，則嘗試獲取第一個sheet
                    sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最後一個cell的編號 即總的列數
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最後一列的標號
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    //沒有數據的行預設是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理，沒有數據的單元格都預設是null
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

4.讀取Excel文件內容轉換為DataSet：

       /// <summary>
        /// 讀取Excel文件內容轉換為DataSet,列名依次為 "c0"……c[columnlength-1]
        /// </summary>
        /// <param name="fileName">文件絕對路徑</param>
        /// <param name="startRow">數據開始行數(1為第一行)</param>
        /// <param name="columnDataType">每列的數據類型</param>
        /// <returns></returns>
        public static DataSet ReadExcel(string fileName, int startRow, params NpoiDataType[] columnDataType)
        {
            var ds = new DataSet("ds");
            var dt = new DataTable("dt");
            var sb = new StringBuilder();
            using (var stream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                //使用介面，自動識別excel2003/2007格式
                var workbook = WorkbookFactory.Create(stream);
                //得到裡面第一個sheet
                var sheet = workbook.GetSheetAt(0);
                int j;
                IRow row;
                //ColumnDataType賦值
                if (columnDataType.Length <= 0)
                {
                    //得到第i行
                    row = sheet.GetRow(startRow - 1);
                    columnDataType = new NpoiDataType[row.LastCellNum];
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var hs = row.GetCell(i);
                        columnDataType[i] = GetCellDataType(hs);
                    }
                }
                for (j = 0; j < columnDataType.Length; j++)
                {
                    var tp = GetDataTableType(columnDataType[j]);
                    dt.Columns.Add("c" + j, tp);
                }
                for (var i = startRow - 1; i <= sheet.PhysicalNumberOfRows; i++)
                {
                    //得到第i行
                    row = sheet.GetRow(i);
                    if (row == null) continue;
                    try
                    {
                        var dr = dt.NewRow();

                        for (j = 0; j < columnDataType.Length; j++)
                        {
                            dr["c" + j] = GetCellData(columnDataType[j], row, j);
                        }
                        dt.Rows.Add(dr);
                    }
                    catch (Exception er)
                    {
                        sb.Append(string.Format("第{0}行出錯：{1}\r\n", i + 1, er.Message));
                    }
                }
                ds.Tables.Add(dt);
            }
            if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
            return ds;
        }

5.從DataSet導出到2003：

        /// <summary>
        /// 從DataSet導出到MemoryStream流2003
        /// </summary>
        /// <param name="saveFileName">文件保存路徑</param>
        /// <param name="sheetName">Excel文件中的Sheet名稱</param>
        /// <param name="ds">存儲數據的DataSet</param>
        /// <param name="startRow">從哪一行開始寫入，從0開始</param>
        /// <param name="datatypes">DataSet中的各列對應的數據類型</param>
        public static bool CreateExcel2003(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
        {
            try
            {
                if (startRow < 0) startRow = 0;
                var wb = new HSSFWorkbook();
                var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "pkm";
                var si = PropertySetFactory.CreateSummaryInformation();
                si.Title =
                si.Subject = "automatic genereted document";
                si.Author = "pkm";
                wb.DocumentSummaryInformation = dsi;
                wb.SummaryInformation = si;
                var sheet = wb.CreateSheet(sheetName);
                //sheet.SetColumnWidth(0, 50 * 256);
                //sheet.SetColumnWidth(1, 100 * 256);
                ICell cell;
                int j;
                var maxLength = 0;
                var curLength = 0;
                object columnValue;
                var dt = ds.Tables[0];
                if (datatypes.Length < dt.Columns.Count)
                {
                    datatypes = new NpoiDataType[dt.Columns.Count];
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        var dtcolumntype = dt.Columns[i].DataType.Name.ToLower();
                        switch (dtcolumntype)
                        {
                            case "string":
                                datatypes[i] = NpoiDataType.String;
                                break;
                            case "datetime":
                                datatypes[i] = NpoiDataType.Datetime;
                                break;
                            case "boolean":
                                datatypes[i] = NpoiDataType.Bool;
                                break;
                            case "double":
                                datatypes[i] = NpoiDataType.Numeric;
                                break;
                            default:
                                datatypes[i] = NpoiDataType.String;
                                break;
                        }
                    }
                }

                // 創建表頭
                var row = sheet.CreateRow(0);
                //樣式
                var style1 = wb.CreateCellStyle();
                //字體
                var font1 = wb.CreateFont();
                //字體顏色
                font1.Color = HSSFColor.White.Index;
                //字體加粗樣式
                font1.Boldweight = (short)FontBoldWeight.Bold;
                //style1.FillBackgroundColor = HSSFColor.WHITE.index;                                                            
                style1.FillForegroundColor = HSSFColor.Green.Index;
                //GetXLColour(wb, LevelOneColor);// 設置圖案色
                //GetXLColour(wb, LevelOneColor);// 設置背景色
                style1.FillPattern = FillPattern.SolidForeground;
                //樣式里的字體設置具體的字體樣式
                style1.SetFont(font1);
                //文字水平對齊方式
                style1.Alignment = HorizontalAlignment.Center;
                //文字垂直對齊方式
                style1.VerticalAlignment = VerticalAlignment.Center;
                row.HeightInPoints = 25;
                for (j = 0; j < dt.Columns.Count; j++)
                {
                    columnValue = dt.Columns[j].ColumnName;
                    curLength = Encoding.Default.GetByteCount(columnValue.ToString());
                    maxLength = (maxLength < curLength ? curLength : maxLength);
                    var colounwidth = 256 * maxLength;
                    sheet.SetColumnWidth(j, colounwidth);
                    try
                    {
                        //創建第0行的第j列
                        cell = row.CreateCell(j);
                        //單元格式設置樣式
                        cell.CellStyle = style1;

                        try
                        {
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(columnValue.ToString());
                        }
                        catch (Exception ex)
                        {