public static DataSet excelToDataSet(string fileName, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fileStream);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; ++i)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = excelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
}
return(ds);
}
}
}
/// <summary>
/// 将Excel单元格转换为String
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static string CellValueToString(ICell cell)
{
string cellString = String.Empty;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
cellString = numericCellToString(cell);
break;
case CellType.String:
cellString = cell.StringCellValue;
break;
case CellType.Formula:
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(cell.Sheet.Workbook);
ICell formulaCell = e.EvaluateInCell(cell);
cellString = CellValueToString(formulaCell);
break;
case CellType.Boolean:
cellString = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
cellString = cell.ErrorCellValue.ToString();
break;
case CellType.Blank:
case CellType.Unknown:
default:
break;
}
}
return(cellString);
}
/// <summary>
/// 将单元格的值转换为类型t
/// </summary>
/// <param name="cell"></param>
/// <param name="t"></param>
/// <returns></returns>
private static object ConvertCell(ICell cell, Type t)
{
switch (cell.CellType)
{
case CellType.Unknown:
return(null);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return(Convert.ChangeType(cell.DateCellValue, t));
}
else
{
return(Convert.ChangeType(cell.NumericCellValue, t));
}
case CellType.String:
return(Convert.ChangeType(cell.StringCellValue, t));
case CellType.Formula:
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(cell.Sheet.Workbook);
ICell formulaCell = e.EvaluateInCell(cell);
return(ConvertCell(formulaCell, t));
case CellType.Blank:
return(null);
case CellType.Boolean:
return(Convert.ChangeType(cell.BooleanCellValue, t));
case CellType.Error:
return(null);
default:
return(null);
}
}
public static DataTable ExcelImportToDataTable(FileStream file)
{
DataTable dt = new DataTable();
//.xlsx //.xlsm
#region .xlsx文件处理:XSSFWorkbook
try
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet sheet = wb.GetSheetAt(0);//.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
{
//SET EVERY COLUMN NAME
ICell cell = headerRow.GetCell(j);
if (cell != null && cell.ToString() != "")
{
dt.Columns.Add(cell.ToString());
}
else
{
continue;
}
}
int colCount = dt.Columns.Count;
while (rows.MoveNext())
{
//IRow row = (XSSFRow)sheet.GetRow(j);
IRow row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
bool addDR = false;
if (row.RowNum == 0)
{
continue;//The firt row is title,no need import
}
for (int i = 0; i < colCount; i++)
{
//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
//cell count>column count,then break //每条记录的单元格数量不能大于DataTable的title
if (i >= colCount)
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell != null)
{
object o = cell;
//读取Excel格式，根据格式读取数据类型
switch (cell.CellType)
{
case CellType.Blank: //空数据类型处理
o = "";
break;
case CellType.String: //字符串类型
o = cell.StringCellValue;
break;
case CellType.Numeric: //数字类型
if (DateUtil.IsCellDateFormatted(cell))
{
o = cell.DateCellValue;
}
else
{
o = cell.ToString();
}
break;
case CellType.Formula:
//HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(wb);
o = e.Evaluate(cell).StringValue;
break;
default:
o = "";
break;
}
dr[i] = Convert.ToString(o);//row.GetCell(j).StringCellValue;
addDR = true;
}
}
if (addDR)
{
dt.Rows.Add(dr);
}
}
}
catch (Exception e)
{
throw e;
}
#endregion
return(dt);
}
/// <summary>
/// 将单元格的值转化为字符串
/// </summary>
/// <param name="tCell"></param>
/// <returns></returns>
private string getCellStringValueAllCase(ICell tCell)
{
string tempValue = "";
switch (tCell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = tCell.BooleanCellValue.ToString();
break;
case CellType.Error:
break;
case CellType.Formula:
IFormulaEvaluator fe = WorkbookFactory.CreateFormulaEvaluator(tCell.Sheet.Workbook);
var cellValue = fe.Evaluate(tCell);
switch (cellValue.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = cellValue.BooleanValue.ToString();
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
tempValue = cellValue.NumberValue.ToString();
break;
case CellType.String:
tempValue = cellValue.StringValue.ToString();
break;
case CellType.Unknown:
break;
default:
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(tCell))
{
tempValue = tCell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
tempValue = tCell.NumericCellValue.ToString();
}
break;
case CellType.String:
tempValue = tCell.StringCellValue.Trim();
break;
case CellType.Unknown:
break;
default:
break;
}
return(tempValue);
}
/// <summary>
/// 读取2007Excel[.xlsx]或读取2003Excel[.xls](返回DataTable)
/// </summary>
/// <param name="path">Excel路径</param>
/// <param name="sheetName">表名</param>
/// <param name="isheader">第一行是否列名，否表默认列名为column1，column2等</param>
/// <returns>表</returns>
public static DataTable ReadExcel(string path, string sheetName, bool isheader)
{
try
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//IWorkbook workbook = new XSSFWorkbook(fs);//2007
//IWorkbook workbook = new HSSFWorkbook(fs);//2003
IWorkbook workbook = WorkbookFactory.Create(fs);//工厂模式
ISheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0); //取第一个工作表
}
else
{
sheet = workbook.GetSheet(sheetName);
}
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
int rfirst = sheet.FirstRowNum; //工作表第一行
int rlast = sheet.LastRowNum; //工作表最后一行
IRow row = sheet.GetRow(rfirst);
int cfirst = row.FirstCellNum; //工作表第一列
int clast = row.LastCellNum; //工作表最后一列
//构建表列
if (isheader)
{
for (int i = cfirst; i < clast; i++)
{
if (row.GetCell(i) != null)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, System.Type.GetType("System.String"));
}
}
rfirst = rfirst + 1;
row = null;
}
else
{
for (int i = cfirst; i < clast; i++)
{
//DataColumn column = new DataColumn("column" + (i + 1));
dt.Columns.Add("column" + (i + 1), System.Type.GetType("System.String"));
}
}
for (int i = rfirst; i <= rlast; i++)
{
DataRow r = dt.NewRow();
IRow ir = sheet.GetRow(i);
for (int j = cfirst; j < clast; j++)
{
ICell cell = evaluator.EvaluateInCell(ir.GetCell(j));
if (cell != null)
{
//if (cell.CellType == CellType.Formula)
//{
// r[j] = cell.NumericCellValue.ToString();//cell.NumericCellValue取包括公式的值 IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook); cell = e.EvaluateInCell(cell);
// }
//else
r[j] = ir.GetCell(j).ToString();
}
}
dt.Rows.Add(r);
ir = null;
r = null;
}
sheet = null;
workbook = null;
}
return(dt);
}
catch (Exception ex)
{
throw (ex);//System.Windows.Forms.MessageBox.Show("Excel格式错误或者Excel正由另一进程在访问");
}
}