NPOI读写Excel

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数据导入到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;}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中的数据导入到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 = firstRow.LastCellNum; //一行最后一个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 = sheet.LastRowNum;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) //同理，没有数据的单元格都默认是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;}}}
}

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){if (workBook.Worksheets.Count > 0){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];}if (worksheet != null){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;}}
}