public static object GetCellValue(HSSFFormulaEvaluator evaluator, HSSFCell cell)
{
object value = null;
switch (cell.CellType)
{
case CellType.BLANK:
case CellType.Unknown:
value = Convert.ToString(cell.StringCellValue);
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue;
break;
case CellType.ERROR:
value = Convert.ToString(cell.ErrorCellValue);
break;
case CellType.FORMULA:
try
{
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
value = GetCellValue(evaluator, cell);
}
catch (Exception)
{
string err = String.Format("单元格{0}使用了不支持的表达式：{1}，请移除该表达式", YZExcelHelper.ColumnIndexToName(cell.ColumnIndex) + (cell.RowIndex + 1).ToString(), cell.CellFormula);
throw new Exception(err);
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.STRING:
value = Convert.ToString(cell.RichStringCellValue);
break;
default:
value = Convert.ToString(cell.StringCellValue);
break;
}
if (value is string && value != null)
{
value = (value as string).Trim();
}
return(value);
}
public static DataSet LoadExcel(Stream stream, string fileName, int titleRowIndex, int dataRowIndex, bool ignoreCellException)
{
//打开文件
IWorkbook book = null;
IFormulaEvaluator evaluator = null;
string ext = Path.GetExtension(fileName);
if (YZStringHelper.EquName(ext, ".xlsx"))
{
book = new XSSFWorkbook(stream);
evaluator = new XSSFFormulaEvaluator(book);
}
if (book == null)
{
book = new HSSFWorkbook(stream);
evaluator = new HSSFFormulaEvaluator(book);
}
DataSet dataset = new DataSet();
for (int i = 0; i < book.NumberOfSheets; i++)
{
ISheet sheet = (ISheet)book.GetSheetAt(i);
DataTable table = new DataTable(sheet.SheetName);
dataset.Tables.Add(table);
if (sheet.LastRowNum != 0)
{
for (int r = 0; r <= sheet.LastRowNum; r++)
{
IRow row = (IRow)sheet.GetRow(r);
if (row == null) //存在row为null的情况
{
break; // continue;
}
DataRow dataRow = null;
bool containsValue = false;
for (int c = 0; c < row.LastCellNum; c++)
{
ICell cell = (ICell)row.GetCell(c); //存在cell为null的情况
object value = cell == null ? null : YZExcelHelper.GetCellValue(evaluator, cell, ignoreCellException);
if (!String.IsNullOrEmpty(Convert.ToString(value)))
{
containsValue = true;
}
string columnName = YZExcelHelper.ColumnIndexToName(c);
if (r == titleRowIndex)
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
dataColumn.Caption = Convert.ToString(value);
}
else if (r >= dataRowIndex)
{
if (!table.Columns.Contains(columnName))
{
DataColumn dataColumn = new DataColumn(columnName, typeof(object));
table.Columns.Add(dataColumn);
}
if (dataRow == null)
{
dataRow = table.NewRow();
}
dataRow[columnName] = value;
if (c == row.LastCellNum - 1 && containsValue)
{
table.Rows.Add(dataRow);
}
}
}
}
}
}
return(dataset);
}
public static object GetCellValue(IFormulaEvaluator evaluator, ICell cell, bool ignoreCellException)
{
object value = null;
switch (cell.CellType)
{
case CellType.Blank:
case CellType.Unknown:
value = Convert.ToString(cell.StringCellValue);
break;
case CellType.Boolean:
value = cell.BooleanCellValue;
break;
case CellType.Error:
value = Convert.ToString(cell.ErrorCellValue);
break;
case CellType.Formula:
try
{
cell = evaluator.EvaluateInCell(cell) as ICell;
value = GetCellValue(evaluator, cell, ignoreCellException);
}
catch (Exception)
{
if (ignoreCellException)
{
value = "";
}
else
{
string formula = null;
try
{
formula = cell.CellFormula;
}
catch (Exception)
{
}
string err = String.Format(Resources.YZStrings.Aspx_Excel_Ext_Invalid_Express, YZExcelHelper.ColumnIndexToName(cell.ColumnIndex) + (cell.RowIndex + 1).ToString(), formula);
throw new Exception(err);
}
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.String:
value = Convert.ToString(cell.RichStringCellValue);
break;
default:
value = Convert.ToString(cell.StringCellValue);
break;
}
if (value is string && value != null)
{
value = (value as string).Trim();
}
return(value);
}