NPOI读写Excel1、整个Excel表格叫做⼯作表：WorkBook（⼯作薄），包含的叫页（⼯作表）：Sheet；⾏：Row；单元格Cell。
2、NPOI是POI的C#版本，NPOI的⾏和列的index都是从0开始3、POI读取Excel有两种格式⼀个是HSSF，另⼀个是XSSF。
HSSF和XSSF的区别如下：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中数据写⼊到Excel中，或者是从Excel读取数据到⼀个DataTable中。
ExcelHelper类：using System;using System.Collections.Generic;using System.Linq;using System.Text;using erModel;using erModel;using erModel;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数据导⼊到excel中///</summary>///<param name="data">要导⼊的数据</param>///<param name="isColumnWritten">DataTable的列名是否要导⼊</param>///<param name="sheetName">要导⼊的excel的sheet的名称</param>///<returns>导⼊数据⾏数(包含列名那⼀⾏)</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;}elsecount = 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中的数据导⼊到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对应的sheet，则尝试获取第⼀个sheet {sheet = workbook.GetSheetAt(0);}}else{sheet = workbook.GetSheetAt(0);}if (sheet != null){IRow firstRow = sheet.GetRow(0);int cellCount = stCellNum; //⼀⾏最后⼀个cell的编号即总的列数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;}//最后⼀列的标号int rowCount = stRowNum;for (int i = startRow; i <= rowCount; ++i)IRow row = sheet.GetRow(i);if (row == null) continue; //没有数据的⾏默认是null DataRow dataRow = data.NewRow();for (int j = row.FirstCellNum; j < cellCount; ++j){if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[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;}}}}测试代码：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);}}}View Code签于这篇⽂章阅读量较⾼，更新⼀下我使⽤Aspose.Cells的另⼀个版本：PS：Aspose是要收费的using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using Aspose.Cells;namespace NetUtilityLib{public static class ExcelHelper{public static int DataTableToExcel(DataTable data, string fileName, string sheetName, bool isColumnNameWritten) {int num = -1;try{Workbook workBook;Worksheet worksheet = null;if (File.Exists(fileName))workBook = new Workbook(fileName);elseworkBook = new Workbook();if (sheetName == null){worksheet = workBook.Worksheets[0];}else{sheetName = "Sheet1";workBook.Worksheets.RemoveAt(sheetName);worksheet = workBook.Worksheets.Add(sheetName);}}if (worksheet != null){worksheet.Cells.Clear();num = worksheet.Cells.ImportDataTable(data, isColumnNameWritten, 0, 0, false);workBook.Save(fileName);}}catch (Exception ex){Console.WriteLine(ex.Message);}return num;}public static void AddOneRowToExcel(DataRow dataRow, string fileName, string sheetName){try{Workbook workBook;if (File.Exists(fileName))workBook = new Workbook(fileName);elseworkBook = new Workbook();Worksheet worksheet=null;if (sheetName == null){worksheet = workBook.Worksheets[0];}else{worksheet = workBook.Worksheets[sheetName];}if (worksheet != null){worksheet.Cells.ImportDataRow(dataRow, worksheet.Cells.MaxDataRow + 1,0);//worksheet.Cells.ImportArray(dataArray, worksheet.Cells.MaxDataRow+1, 0, false);workBook.Save(fileName);}}catch (Exception ex){Console.WriteLine(ex.Message);}}public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumnName) {DataTable data = new DataTable();try{Workbook workbook = null;FileInfo fileInfo = new FileInfo(fileName);if (fileInfo.Extension.ToLower().Equals(".xlsx"))workbook = new Workbook(fileName, new LoadOptions(LoadFormat.Xlsx));else if (fileInfo.Extension.ToLower().Equals(".xls"))workbook = new Workbook(fileName, new LoadOptions(LoadFormat.Excel97To2003));if (workbook != null){Worksheet worksheet = null;if (sheetName != null){worksheet = workbook.Worksheets[sheetName];}else{worksheet = workbook.Worksheets[0];}data = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow+1, worksheet.Cells.MaxColumn+1, isFirstRowColumnName);return data;}}else{return data;}}catch (Exception ex){Console.WriteLine(ex.Message);}return data;}}}Excel相关DLL下载：参考：。
