private const string Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
public static DataTable ImportExcel()
{
var dt = new DataTable();
var path = GetOpenFile();
if (string.IsNullOrEmpty(path))
{
return null;
}
using (var stream = new FileStream(path, FileMode.Open))
{
var workbook = GetWorkbook(path, stream);
var sheet = workbook.GetSheetAt(0);
var headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
dt.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
return dt;
}
private static string GetOpenFile()
{
var openFileDialog = new System.Windows.Forms.OpenFileDialog()
{
Filter = Filter
};
string filePath = null;
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filePath = openFileDialog.FileName;
}
return filePath;
}
private static IWorkbook GetWorkbook(string path, FileStream stream)
{
if (Path.GetExtension(path).ToLower() == ".xls")
{
return stream != null ? new HSSFWorkbook(stream) : new HSSFWorkbook();
}
else if (Path.GetExtension(path).ToLower() == ".xlsx")
{
return stream != null ? new XSSFWorkbook(stream) : new XSSFWorkbook();
}
throw new Exception("异常的后缀");
}