Excel转化成DataTable实现：NPOI和OLEDb

public interface IExcelAccess
{
DataSet Load(ExcelConfig config);
}

public class ExcelConfig
{
/// <summary>
/// 文件存储路径
/// </summary>
public string Path { get; set; } /// <summary>
/// 文件表头所在行索引
/// </summary>
public int HeaderIndex { get; set; }
}

public class NpoiExcelAccess : IExcelAccess
{
/// <summary>
/// 根据文件扩展名，获取workbook实例
/// </summary>
/// <param name="ext"></param>
/// <returns></returns>
private IWorkbook GetWorkBook(string ext, Stream stream)
{
IWorkbook workbook = null; switch (ext)
{
case ".xlsx":
workbook = new XSSFWorkbook(stream);
break;
case ".xls":
workbook = new HSSFWorkbook(stream);
break;
default:
break;
} return workbook;
} /// <summary>
/// 加载数据，可设置跳过前几行
/// </summary>
/// <param name="path"></param>
/// <param name="skipRows"></param>
/// <returns></returns>
public DataSet Load(ExcelConfig config)
{
using (var fileStream = new FileStream(config.Path, FileMode.Open, FileAccess.Read))
{
var ds = new DataSet();
var ext = Path.GetExtension(config.Path); // 新建IWorkbook对象
var workbook = this.GetWorkBook(ext, fileStream); for (int i = ; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = GetDataTable(sheet, config.HeaderIndex);
ds.Tables.Add(dt);
} return ds;
}
} private DataTable GetDataTable(ISheet sheet, int headerIndex)
{
var dt = new DataTable();
// 获取表头行
var headerRow = sheet.GetRow(headerIndex);
var cellCount = GetCellCount(sheet, headerIndex); // 设置表头
for (int i = ; i < cellCount; i++)
{
if (headerRow.GetCell(i) != null)
{
dt.Columns.Add(headerRow.GetCell(i).StringCellValue, typeof(string));
}
} for (int i = headerIndex + ; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
FillDataRow(row, ref dr);
dt.Rows.Add(dr);
} dt.TableName = sheet.SheetName;
return dt; } private void FillDataRow(IRow row, ref DataRow dr)
{
if (row != null)
{
for (int j = ; j < dr.Table.Columns.Count; j++)
{
ICell cell = row.GetCell(j); if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
dr[j] = DBNull.Value;
break;
case CellType.Boolean:
dr[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.String:
dr[j] = cell.StringCellValue;
break;
case CellType.Error:
dr[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
// cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
} /// <summary>
/// 获取表头列数
/// </summary>
/// <param name="sheet"></param>
/// <param name="skipRows"></param>
/// <returns></returns>
private int GetCellCount(ISheet sheet, int headerIndex)
{
var headerRow = sheet.GetRow(headerIndex); return headerRow.LastCellNum;
}
}

public class OleDbExcelAccess : IExcelAccess
{
private string strConn; public OleDbExcelAccess()
{
//TODO 需要根据扩展名，来决定 使用哪一种连接字符串
strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties='Excel 8.0;IMEX=1;HDR=NO'";
} public DataSet Load(ExcelConfig config)
{
var ds = new DataSet();
var sheets = GetSheetNames(config.Path);
var strConnTmp = string.Format(strConn, config.Path); foreach (string sheet in sheets)
{
using (var oleConn = new OleDbConnection(strConnTmp))
{
var strsql = "SELECT * FROM [" + sheet + "]";
var oleDaExcel = new OleDbDataAdapter(strsql, oleConn);
oleConn.Open();
oleDaExcel.Fill(ds, sheet);
}
} return ds;
} /// <summary>
/// 获取Excel的所有的sheet
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string[] GetSheetNames(string path)
{
DataTable dt = new DataTable();
if (File.Exists(path))
{
string strConnTmp = string.Format(strConn, path);
using (var conn = new OleDbConnection(strConnTmp))
{
conn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
}
//包含excel中表名的字符串数组
var sheetNames = new List<string>();
for (int k = ; k < dt.Rows.Count; k++)
{
string tableName = dt.Rows[k]["TABLE_NAME"].ToString();
//修正出现兼容性视图名称时过滤表名
if (!tableName.Substring(tableName.Length - ).Equals("_"))
{
sheetNames.Add(tableName);
}
}
return sheetNames.ToArray(); }
return null; }
}

private void TestExcelImport()
{
var excelFile = Path.Combine("C:\\Users\\hankk\\Desktop\\ICON", "2003版本.xls"); ExcelConfig config = new ExcelConfig()
{
Path = excelFile,
HeaderIndex =
}; IExcelAccess npoiAccess = new NpoiExcelAccess();
var npoiDs = npoiAccess.Load(config); IExcelAccess oleDbAccess = new OleDbExcelAccess();
var oleDs = oleDbAccess.Load(config);
}