项目需要，重拾NPOI.不会组织语言，直接分享代码。
public enum CellDataType
{
Date,
Time,
DateTime,
String
}
public class ExcelHelper
{
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表</param>
/// <param name="sheetIndex">Excel工作表</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
private static DataTable ExcelToDataTable(Stream excelStream,string sheetName, int sheetIndex, int headerRowIndex)
{
IWorkbook workbook = WorkbookFactory.Create(excelStream);
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(sheetIndex);
}
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
dataRow[j] = GetText(cell);
}
table.Rows.Add(dataRow);
}
#region 处理合并单元格
int ij = sheet.NumMergedRegions;
for (int i = 0; i < ij; i++)
{
CellRangeAddress r = sheet.GetMergedRegion(i);
if (r.FirstRow <= headerRowIndex)
continue;
object o = table.Rows[r.FirstRow - (headerRowIndex + 1)][r.FirstColumn];
for (int jr = r.FirstRow; jr <= r.LastRow; jr++)
{
if ((jr - (headerRowIndex + 1)) < table.Rows.Count)//处理合并列大于数据总列时这种不规范的EXCEL文本，
{
for (int jc = r.FirstColumn; jc <= r.LastColumn; jc++)
{
table.Rows[jr - (headerRowIndex + 1)][jc] = o;
}
}
}
}
#endregion
excelStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ExcelToDataTable(stream, sheetName,-1, headerRowIndex);
}
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表索引</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string excelFilePath, int sheetIndex, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ExcelToDataTable(stream,null, sheetIndex, headerRowIndex);
}
}
private static CellDataType GetCellDataType(ICell cell)
{
string dataFormatString = cell.CellStyle.GetDataFormatString();
if (dataFormatString.ToLower().IndexOf("y") != -1 && dataFormatString.ToLower().IndexOf("h") != -1)
{
return CellDataType.DateTime;
}
else if (dataFormatString.ToLower().IndexOf("y") != -1 && dataFormatString.ToLower().IndexOf("h") == -1)
{
return CellDataType.Date;
}
else if (dataFormatString.ToLower().IndexOf("y") == -1 && dataFormatString.ToLower().IndexOf("h") != -1)
{
return CellDataType.Time;
}
else
{
return CellDataType.String;
}
}
private static string GetText(ICell cell)
{
if (cell == null)
{
return "";
}
string value = null;
switch (cell.CellType)
{
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
case CellType.FORMULA:
value = GetFormulaText(cell);
break;
case CellType.NUMERIC:
CellDataType cdt = GetCellDataType(cell);
if (cdt == CellDataType.DateTime)
{
value = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else if (cdt == CellDataType.Date)
{
value = cell.DateCellValue.ToString("yyyy-MM-dd");
}
else if (cdt == CellDataType.Time)
{
value = cell.DateCellValue.ToString("HH:mm:ss");
}
else
{
value = cell.NumericCellValue.ToString();
}
break;
case CellType.STRING:
value = cell.StringCellValue;
break;
default:
value = cell.ToString();
break;
}
return value;
}
private static string GetFormulaText(ICell cell)
{
string value = null;
switch (cell.CachedFormulaResultType)
{
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
case CellType.NUMERIC:
value = cell.NumericCellValue.ToString();
break;
case CellType.STRING:
value = cell.StringCellValue;
break;
default:
value = cell.ToString();
break;
}
return value;
}