詳解免費(fèi)高效實(shí)用的.NET操作Excel組件NPOI（.NET組件介紹之六）

更新時(shí)間：2016年12月12日 11:24:06 作者：彭澤0902

這篇文章主要介紹了詳解免費(fèi)高效實(shí)用的.NET操作Excel組件NPOI（.NET組件介紹之六）,具有一定的參考價(jià)值，有興趣的可以了解一下。

很多的軟件項(xiàng)目幾乎都包含著對(duì)文檔的操作，前面已經(jīng)介紹過兩款操作文檔的組件，現(xiàn)在介紹一款文檔操作的組件NPOI。

NPOI可以生成沒有安裝在您的服務(wù)器上的Microsoft Office套件的Excel報(bào)表，并且在后臺(tái)調(diào)用Microsoft Excel ActiveX更有效率;從Office文檔中提取文本，以幫助您實(shí)現(xiàn)全文索引功能（大多數(shù)時(shí)候，此功能用于創(chuàng)建搜索引擎）；從Office文檔提取圖像；生成包含公式的Excel工作表。

一.NPOI組件概述：

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

以上是NPOI的優(yōu)點(diǎn)，其他一些優(yōu)點(diǎn)可以不用太在意，估計(jì)很多人對(duì)“支持xls，xlsx，docx”這一特點(diǎn)感覺有些驚訝，因?yàn)樵诤芏嗳说挠∠罄锩鍺POI就是對(duì)Excel進(jìn)行相關(guān)的操作，但是在這里突然看到了對(duì)docx也可以操作，這一特點(diǎn)可能讓很多人感到欣喜，因?yàn)镹POI的的確確是一個(gè)很不錯(cuò)的組件，用過的人都說好，我也不例外。

NPOI的運(yùn)行要求：VS2010與.NET 4.0運(yùn)行時(shí)；VS2005或VS2008與.NET 2.0運(yùn)行時(shí)（SP1）；vs2003與.NET 1.1；Mono；ASP.NET中的中等信任環(huán)境。

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

以上是對(duì)NPOI的相關(guān)背景和使用環(huán)境做了一個(gè)簡單的介紹，接下來我具體的看一下NPOI的一些核心類和方法，由于下載的是DLL文件，還是采用.NET Reflector對(duì)DLL文件進(jìn)行反編譯，以此查看源代碼。

如果需要具體的了解NPOI可以直接訪問：http://npoi.codeplex.com/SourceControl/latest，提供了NPOI的源碼和一些demo，由于本溪介紹的重點(diǎn)是NPOI對(duì)Excel的操作，所以下面的類和實(shí)例主要是對(duì)操作Excel的介紹，如果需要對(duì)docx的操作，可以具體查看相應(yīng)的類demo。

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

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()：創(chuàng)建行。

 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);
  }
}

以上只是對(duì)部分的類和方法做了簡單的說明，需要了解更多的內(nèi)容，可以進(jìn)入官網(wǎng)下載源碼，或者使用軟件查看DLL文件。

三.NPOI操作實(shí)例：

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

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

2. 將DataTable數(shù)據(jù)導(dǎo)入到excel中：

 /// <summary>
    /// 將DataTable數(shù)據(jù)導(dǎo)入到excel中
    /// </summary>
    /// <param name="data">要導(dǎo)入的數(shù)據(jù)</param>
    /// <param name="isColumnWritten">DataTable的列名是否要導(dǎo)入</param>
    /// <param name="sheetName">要導(dǎo)入的excel的sheet的名稱</param>
    /// <param name="fileName">文件夾路徑</param>
    /// <returns>導(dǎo)入數(shù)據(jù)行數(shù)(包含列名那一行)</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;
        }
        //遍歷循環(huán)datatable具體數(shù)據(jù)項(xiàng)
        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中的數(shù)據(jù)導(dǎo)入到DataTable中：

  /// <summary>
    /// 將excel中的數(shù)據(jù)導(dǎo)入到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對(duì)應(yīng)的sheet，則嘗試獲取第一個(gè)sheet
          sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
        }
        if (sheet == null) return data;
        var firstRow = sheet.GetRow(0);
        //一行最后一個(gè)cell的編號(hào) 即總的列數(shù)
        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;
        }
        //最后一列的標(biāo)號(hào)
        var rowCount = sheet.LastRowNum;
        for (var i = startRow; i <= rowCount; ++i)
        {
          var row = sheet.GetRow(i);
          //沒有數(shù)據(jù)的行默認(rèn)是null
          if (row == null) continue;
          var dataRow = data.NewRow();
          for (int j = row.FirstCellNum; j < cellCount; ++j)
          {
            //同理，沒有數(shù)據(jù)的單元格都默認(rèn)是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文件內(nèi)容轉(zhuǎn)換為DataSet：

  /// <summary>
    /// 讀取Excel文件內(nèi)容轉(zhuǎn)換為DataSet,列名依次為 "c0"……c[columnlength-1]
    /// </summary>
    /// <param name="fileName">文件絕對(duì)路徑</param>
    /// <param name="startRow">數(shù)據(jù)開始行數(shù)(1為第一行)</param>
    /// <param name="columnDataType">每列的數(shù)據(jù)類型</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))
      {
        //使用接口，自動(dòng)識(shí)別excel2003/2007格式
        var workbook = WorkbookFactory.Create(stream);
        //得到里面第一個(gè)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}行出錯(cuò)：{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導(dǎo)出到2003：

  /// <summary>
    /// 從DataSet導(dǎo)出到MemoryStream流2003
    /// </summary>
    /// <param name="saveFileName">文件保存路徑</param>
    /// <param name="sheetName">Excel文件中的Sheet名稱</param>
    /// <param name="ds">存儲(chǔ)數(shù)據(jù)的DataSet</param>
    /// <param name="startRow">從哪一行開始寫入，從0開始</param>
    /// <param name="datatypes">DataSet中的各列對(duì)應(yīng)的數(shù)據(jù)類型</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;
            }
          }
        }

        // 創(chuàng)建表頭
        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);// 設(shè)置圖案色
        //GetXLColour(wb, LevelOneColor);// 設(shè)置背景色
        style1.FillPattern = FillPattern.SolidForeground;
        //樣式里的字體設(shè)置具體的字體樣式
        style1.SetFont(font1);
        //文字水平對(duì)齊方式
        style1.Alignment = HorizontalAlignment.Center;
        //文字垂直對(duì)齊方式
        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
          {
            //創(chuàng)建第0行的第j列
            cell = row.CreateCell(j);
            //單元格式設(shè)置樣式
            cell.CellStyle = style1;

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

          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message);
          }
        }
        // 創(chuàng)建每一行
        for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
        {
          var dr = ds.Tables[0].Rows[i];
          //創(chuàng)建第i行
          row = sheet.CreateRow(i + 1);
          for (j = 0; j < dt.Columns.Count; j++)
          {
            columnValue = dr[j];
            curLength = Encoding.Default.GetByteCount(columnValue.ToString());
            maxLength = (maxLength < curLength ? curLength : maxLength);
            var colounwidth = 256 * maxLength;
            sheet.SetColumnWidth(j, colounwidth);
            try
            {
              //創(chuàng)建第i行的第j列
              cell = row.CreateCell(j);
              // 插入第j列的數(shù)據(jù)
              try
              {
                var dtype = datatypes[j];
                switch (dtype)
                {
                  case NpoiDataType.String:
                    {
                      cell.SetCellType(CellType.Numeric);
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                  case NpoiDataType.Datetime:
                    {
                      cell.SetCellType(CellType.Numeric);
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                  case NpoiDataType.Numeric:
                    {
                      cell.SetCellType(CellType.Numeric);
                      cell.SetCellValue(Convert.ToDouble(columnValue));
                    }
                    break;
                  case NpoiDataType.Bool:
                    {
                      cell.SetCellType(CellType.Numeric);
                      cell.SetCellValue(Convert.ToBoolean(columnValue));
                    }
                    break;
                  case NpoiDataType.Richtext:
                    {
                      cell.SetCellType(CellType.Numeric);
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                }
              }
              catch (Exception ex)
              {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(columnValue.ToString());
                throw new Exception(ex.Message);
              }
            }
            catch (Exception ex)
            {
              throw new Exception(ex.Message);
            }
          }
        }
        //生成文件在服務(wù)器上
        using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
        {
          wb.Write(fs);
        }

        return true;
      }
      catch (Exception er)
      {
        throw new Exception(er.Message);
      }

    }

6.從DataSet導(dǎo)出到MemoryStream流2007：

/// <summary>
    /// 從DataSet導(dǎo)出到MemoryStream流2007
    /// </summary>
    /// <param name="saveFileName">文件保存路徑</param>
    /// <param name="sheetName">Excel文件中的Sheet名稱</param>
    /// <param name="ds">存儲(chǔ)數(shù)據(jù)的DataSet</param>
    /// <param name="startRow">從哪一行開始寫入，從0開始</param>
    /// <param name="datatypes">DataSet中的各列對(duì)應(yīng)的數(shù)據(jù)類型</param>
    public static bool CreateExcel2007(string saveFileName, string sheetName, DataSet ds, int startRow, params NpoiDataType[] datatypes)
    {
      try
      {
        if (startRow < 0) startRow = 0;
        var wb = new XSSFWorkbook();
        var sheet = wb.CreateSheet(sheetName);
        ICell cell;
        int j;
        var maxLength = 0;
        int curLength;
        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;
            }
          }
        }
        //創(chuàng)建表頭
        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;
        //GetXLColour(wb, LevelOneColor);
        // 設(shè)置圖案色
        style1.FillForegroundColor = HSSFColor.Green.Index;
        //GetXLColour(wb, LevelOneColor);// 設(shè)置背景色
        style1.FillPattern = FillPattern.SolidForeground;
        //樣式里的字體設(shè)置具體的字體樣式
        style1.SetFont(font1);
        //文字水平對(duì)齊方式
        style1.Alignment = HorizontalAlignment.Center;
        //文字垂直對(duì)齊方式
        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
          {
            //創(chuàng)建第0行的第j列
            cell = row.CreateCell(j);
            //單元格式設(shè)置樣式
            cell.CellStyle = style1;

            try
            {
              cell.SetCellValue(columnValue.ToString());
            }
            catch (Exception ex)
            {
              throw new Exception(ex.Message);
            }

          }
          catch (Exception ex)
          {
            throw new Exception(ex.Message);
          }
        }
        // 創(chuàng)建每一行
        for (var i = startRow; i < ds.Tables[0].Rows.Count; i++)
        {
          var dr = ds.Tables[0].Rows[i];
          //創(chuàng)建第i行
          row = sheet.CreateRow(i + 1);
          for (j = 0; j < dt.Columns.Count; j++)
          {
            columnValue = dr[j];
            curLength = Encoding.Default.GetByteCount(columnValue.ToString());
            maxLength = (maxLength < curLength ? curLength : maxLength);
            var colounwidth = 256 * maxLength;
            sheet.SetColumnWidth(j, colounwidth);
            try
            {
              //創(chuàng)建第i行的第j列
              cell = row.CreateCell(j);
              // 插入第j列的數(shù)據(jù)
              try
              {
                var dtype = datatypes[j];
                switch (dtype)
                {
                  case NpoiDataType.String:
                    {
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                  case NpoiDataType.Datetime:
                    {
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                  case NpoiDataType.Numeric:
                    {
                      cell.SetCellValue(Convert.ToDouble(columnValue));
                    }
                    break;
                  case NpoiDataType.Bool:
                    {
                      cell.SetCellValue(Convert.ToBoolean(columnValue));
                    }
                    break;
                  case NpoiDataType.Richtext:
                    {
                      cell.SetCellValue(columnValue.ToString());
                    }
                    break;
                }
              }
              catch (Exception ex)
              {
                cell.SetCellValue(columnValue.ToString());
                throw new Exception(ex.Message);
              }
            }
            catch (Exception ex)
            {
              throw new Exception(ex.Message);
            }
          }
        }
        //生成文件在服務(wù)器上
        using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate, FileAccess.Write))
        {
          wb.Write(fs);
        }
        return true;
      }
      catch (Exception er)
      {
        throw new Exception(er.Message);
      }

    }

7.讀Excel-根據(jù)NpoiDataType創(chuàng)建的DataTable列的數(shù)據(jù)類型：

 /// <summary>
    /// 讀Excel-根據(jù)NpoiDataType創(chuàng)建的DataTable列的數(shù)據(jù)類型
    /// </summary>
    /// <param name="datatype"></param>
    /// <returns></returns>
    private static Type GetDataTableType(NpoiDataType datatype)
    {
      var tp = typeof(string);
      switch (datatype)
      {
        case NpoiDataType.Bool:
          tp = typeof(bool);
          break;
        case NpoiDataType.Datetime:
          tp = typeof(DateTime);
          break;
        case NpoiDataType.Numeric:
          tp = typeof(double);
          break;
        case NpoiDataType.Error:
          tp = typeof(string);
          break;
        case NpoiDataType.Blank:
          tp = typeof(string);
          break;
      }
      return tp;
    }


    /// <summary>
    /// 讀Excel-得到不同數(shù)據(jù)類型單元格的數(shù)據(jù)
    /// </summary>
    /// <param name="datatype">數(shù)據(jù)類型</param>
    /// <param name="row">數(shù)據(jù)中的一行</param>
    /// <param name="column">哪列</param>
    /// <returns></returns>
    private static object GetCellData(NpoiDataType datatype, IRow row, int column)
    {
      switch (datatype)
      {
        case NpoiDataType.String:
          try
          {
            return row.GetCell(column).DateCellValue;
          }
          catch
          {
            try
            {
              return row.GetCell(column).StringCellValue;
            }
            catch
            {
              return row.GetCell(column).NumericCellValue;
            }
          }
        case NpoiDataType.Bool:
          try { return row.GetCell(column).BooleanCellValue; }
          catch { return row.GetCell(column).StringCellValue; }
        case NpoiDataType.Datetime:
          try { return row.GetCell(column).DateCellValue; }
          catch { return row.GetCell(column).StringCellValue; }
        case NpoiDataType.Numeric:
          try { return row.GetCell(column).NumericCellValue; }
          catch { return row.GetCell(column).StringCellValue; }
        case NpoiDataType.Richtext:
          try { return row.GetCell(column).RichStringCellValue; }
          catch { return row.GetCell(column).StringCellValue; }
        case NpoiDataType.Error:
          try { return row.GetCell(column).ErrorCellValue; }
          catch { return row.GetCell(column).StringCellValue; }
        case NpoiDataType.Blank:
          try { return row.GetCell(column).StringCellValue; }
          catch { return ""; }
        default: return "";
      }
    }

    /// <summary>
    /// 獲取單元格數(shù)據(jù)類型
    /// </summary>
    /// <param name="hs">單元格對(duì)象</param>
    /// <returns></returns>
    private static NpoiDataType GetCellDataType(ICell hs)
    {
      NpoiDataType dtype;
      DateTime t1;
      var cellvalue = "";

      switch (hs.CellType)
      {
        case CellType.Blank:
          dtype = NpoiDataType.String;
          cellvalue = hs.StringCellValue;
          break;
        case CellType.Boolean:
          dtype = NpoiDataType.Bool;
          break;
        case CellType.Numeric:
          dtype = NpoiDataType.Numeric;
          cellvalue = hs.NumericCellValue.ToString(CultureInfo.InvariantCulture);
          break;
        case CellType.String:
          dtype = NpoiDataType.String;
          cellvalue = hs.StringCellValue;
          break;
        case CellType.Error:
          dtype = NpoiDataType.Error;
          break;
        default:
          dtype = NpoiDataType.Datetime;
          break;
      }
      if (cellvalue != "" && DateTime.TryParse(cellvalue, out t1)) dtype = NpoiDataType.Datetime;
      return dtype;
    }

四.總結(jié)：

本文是接著上五篇介紹.NET組件，目的只是在于總結(jié)一些組件的用法，將文章作為一個(gè)引子，各位讀者可以根據(jù)文章的介紹更加深入的去了解相關(guān)組件。有些地方寫的有誤，還望多多包涵和指正，歡迎大家給我建議介紹一些你們在項(xiàng)目中經(jīng)常使用的組件，可以跟大家做一個(gè)分享。

您可能感興趣的文章: