public classExcelHelper : IDisposable

{private string fileName = null; //文件名

private IWorkbook workbook = null;private FileStream fs = null;private booldisposed;public ExcelHelper(string fileName)//构造函数，读入文件名

{this.fileName =fileName;

disposed= false;

}///将excel中的数据导入到DataTable中/// excel工作薄sheet的名称

/// 第一行是否是DataTable的列名

/// 返回的DataTable

public DataTable ExcelToDataTable(string sheetName, boolisFirstRowColumn)

{

ISheet sheet= null;

DataTable data= newDataTable();int startRow = 0;try{

fs= newFileStream(fileName, FileMode.Open, FileAccess.Read);

workbook=WorkbookFactory.Create(fs);if (sheetName != null)

{

sheet=workbook.GetSheet(sheetName);//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet

if (sheet == null)

{

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= newDataColumn(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) //同理，没有数据的单元格都默认是null

dataRow[j] =row.GetCell(j).ToString();

}

data.Rows.Add(dataRow);

}

}returndata;

}catch (Exception ex)//打印错误信息

{

MessageBox.Show("Exception:" +ex.Message);return null;

}

}//将DataTable数据导入到excel中//要导入的数据//要导入的excel的sheet的名称//DataTable的列名是否要导入//导入数据行数(包含列名那一行)

public int DataTableToExcel(DataTable data, string sheetName, boolisColumnWritten)

{int i = 0;int j = 0;int count = 0;

ISheet sheet= null;

fs= newFileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);if (fileName.IndexOf(".xlsx") > 0) //2007版本

workbook = newXSSFWorkbook();else if (fileName.IndexOf(".xls") > 0) //2003版本

workbook = newHSSFWorkbook();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);//写入到excel

returncount;

}catch(Exception ex)

{

Console.WriteLine("Exception:" +ex.Message);return -1;

}

}public void Dispose()//IDisposable为垃圾回收相关的东西，用来显式释放非托管资源,这部分目前还不是非常了解

{

Dispose(true);

GC.SuppressFinalize(this);

}protected virtual void Dispose(booldisposing)

{if (!this.disposed)

{if(disposing)

{if (fs != null)

fs.Close();

}

fs= null;

disposed= true;

}

}

}