1、整個(gè)Excel表格叫做工作表：WorkBook（工作薄），包含的叫頁（工作表）：Sheet；行：Row；單元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是從0開始

3、POI讀取Excel有兩種格式一個(gè)是HSSF，另一個(gè)是XSSF。 HSSF和XSSF的區(qū)別如下：?
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.?
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.?
即：HSSF適用2007以前的版本,XSSF適用2007版本及其以上的。

下面是用NPOI讀寫Excel的例子：ExcelHelper封裝的功能主要是把DataTable中數(shù)據(jù)寫入到Excel中，或者是從Excel讀取數(shù)據(jù)到一個(gè)DataTable中。

?ExcelHelper類：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using System.IO; using System.Data;namespace NetUtilityLib {public class ExcelHelper : IDisposable{private string fileName = null; //文件名private IWorkbook workbook = null;private FileStream fs = null;private bool disposed;public ExcelHelper(string fileName){this.fileName = fileName;disposed = false;}/// <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>/// <returns>導(dǎo)入數(shù)據(jù)行數(shù)(包含列名那一行)</returns>public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten){int i = 0;int j = 0;int count = 0;ISheet sheet = null;fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);if (fileName.IndexOf(".xlsx") > 0) // 2007版本workbook = new XSSFWorkbook();else if (fileName.IndexOf(".xls") > 0) // 2003版本workbook = new HSSFWorkbook();try{if (workbook != null){sheet = workbook.CreateSheet(sheetName);}else{return -1;}if (isColumnWritten == true) //寫入DataTable的列名 {IRow 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;}for (i = 0; i < data.Rows.Count; ++i){IRow row = sheet.CreateRow(count);for (j = 0; j < data.Columns.Count; ++j){row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());}++count;}workbook.Write(fs); //寫入到excelreturn count;}catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);return -1;}}/// <summary>/// 將excel中的數(shù)據(jù)導(dǎo)入到DataTable中/// </summary>/// <param name="sheetName">excel工作薄sheet的名稱</param>/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>/// <returns>返回的DataTable</returns>public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn){ISheet sheet = null;DataTable data = new DataTable();int startRow = 0;try{fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);if (fileName.IndexOf(".xlsx") > 0) // 2007版本workbook = new XSSFWorkbook(fs);else if (fileName.IndexOf(".xls") > 0) // 2003版本workbook = new HSSFWorkbook(fs);if (sheetName != null){sheet = workbook.GetSheet(sheetName);if (sheet == null) //如果沒有找到指定的sheetName對(duì)應(yīng)的sheet，則嘗試獲取第一個(gè)sheet {sheet = workbook.GetSheetAt(0);}}else{sheet = workbook.GetSheetAt(0);}if (sheet != null){IRow firstRow = sheet.GetRow(0);int cellCount = firstRow.LastCellNum; //一行最后一個(gè)cell的編號(hào) 即總的列數(shù)if (isFirstRowColumn){for (int i = firstRow.FirstCellNum; i < cellCount; ++i){ICell cell = firstRow.GetCell(i);if (cell != null){string cellValue = cell.StringCellValue;if (cellValue != null){DataColumn column = new DataColumn(cellValue);data.Columns.Add(column);}}}startRow = sheet.FirstRowNum + 1;}else{startRow = sheet.FirstRowNum;}//最后一列的標(biāo)號(hào)int rowCount = sheet.LastRowNum;for (int i = startRow; i <= rowCount; ++i){IRow row = sheet.GetRow(i);if (row == null) continue; //沒有數(shù)據(jù)的行默認(rèn)是null　　　　　　　 DataRow dataRow = data.NewRow();for (int j = row.FirstCellNum; j < cellCount; ++j){if (row.GetCell(j) != null) //同理，沒有數(shù)據(jù)的單元格都默認(rèn)是nulldataRow[j] = row.GetCell(j).ToString();}data.Rows.Add(dataRow);}}return data;}catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);return null;}}public void Dispose(){Dispose(true);GC.SuppressFinalize(this);}protected virtual void Dispose(bool disposing){if (!this.disposed){if (disposing){if (fs != null)fs.Close();}fs = null;disposed = true;}}} }

補(bǔ)充個(gè)方法

　　　　/// <summary>/// 獲取單元格的值（讀取異常的時(shí)候調(diào)用）/// </summary>/// <param name="Cell"></param>/// <returns></returns>private object GetCellValue(ICell Cell){object value = null;try{switch (Cell.CellType){case CellType.Blank:value = Cell.StringCellValue;break;case CellType.String:value = Cell.StringCellValue;break;case CellType.Numeric:value = Cell.NumericCellValue.ToString();break;case CellType.Boolean:value = Cell.BooleanCellValue;break;case CellType.Formula:value = Cell.RichStringCellValue;break;case CellType.Error:value = Cell.ErrorCellValue;break;case CellType.Unknown:value = Cell.ToString();break;}}catch (Exception){value = System.DBNull.Value;}return value;}

測(cè)試代碼：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data;namespace NPOIExcelExample {class Program{static DataTable GenerateData(){DataTable data = new DataTable();for (int i = 0; i < 5; ++i){data.Columns.Add("Columns_" + i.ToString(), typeof(string));}for (int i = 0; i < 10; ++i){DataRow row = data.NewRow();row["Columns_0"] = "item0_" + i.ToString();row["Columns_1"] = "item1_" + i.ToString();row["Columns_2"] = "item2_" + i.ToString();row["Columns_3"] = "item3_" + i.ToString();row["Columns_4"] = "item4_" + i.ToString();data.Rows.Add(row);}return data;}static void PrintData(DataTable data){if (data == null) return;for (int i = 0; i < data.Rows.Count; ++i){for (int j = 0; j < data.Columns.Count; ++j)Console.Write("{0} ", data.Rows[i][j]);Console.Write("\n");}}static void TestExcelWrite(string file){try{using (ExcelHelper excelHelper = new ExcelHelper(file)){DataTable data = GenerateData();int count = excelHelper.DataTableToExcel(data, "MySheet", true);if (count > 0)Console.WriteLine("Number of imported data is {0} ", count);}}catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}}static void TestExcelRead(string file){try{using (ExcelHelper excelHelper = new ExcelHelper(file)){DataTable dt = excelHelper.ExcelToDataTable("MySheet", true);PrintData(dt);}}catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);}}static void Main(string[] args){string file = "..\\..\\myTest.xlsx";TestExcelWrite(file);TestExcelRead(file);}} }

補(bǔ)充調(diào)用方法：

　　　　　　HttpPostedFileBase excelFile = Request.Files["fileName"]; //取到上傳域if (null != excelFile){string fileName = Path.GetFileName(excelFile.FileName); //取到文件的名稱 excelFile.SaveAs(Server.MapPath("~/Upload/") + System.IO.Path.GetFileName(excelFile.FileName));string a = Server.MapPath("~/Upload/" + fileName);ExcelHelper eh = new ExcelHelper(a);if (fileName.Equals("") || null == fileName){ //沒有選擇文件就上傳的話，則跳回到上傳頁面return false;}
? 　　　　　　ExcelHelper eh = new ExcelHelper(a);

?

?

?

參考：http://www.cnblogs.com/luxiaoxun/p/3374992.html

?

總結(jié)

以上是生活随笔為你收集整理的NPOI 导入Excel和读取Excel的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。