缺点：需要下载相应的插件并添加到系统引用当中。
- /// <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); }
- 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) {
- DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue); 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
