/// <summary>
/// 获取单元格的值
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static object GetCellValue(ICell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return(null);
case CellType.Boolean: //BOOLEAN:
return(cell.BooleanCellValue);
case CellType.Numeric: //NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
//日期型会被判为Numeric,所以要做处理
return(cell.DateCellValue);
}
return(cell.NumericCellValue);
case CellType.String: //STRING:
return(cell.StringCellValue);
case CellType.Error: //ERROR:
return(cell.ErrorCellValue);
case CellType.Formula: //FORMULA:
default:
return("=" + cell.CellFormula);
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValue(ICell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return(null);
case CellType.Boolean: //BOOLEAN:
return(cell.BooleanCellValue);
case CellType.Numeric: //NUMERIC:
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue); //日期类型
}
return(cell.NumericCellValue);
case CellType.String: //STRING:
return(cell.StringCellValue);
case CellType.Error: //ERROR:
return(cell.ErrorCellValue);
case CellType.Formula: //FORMULA:
default:
return("=" + cell.CellFormula);
}
}
//セル値
protected static String ReadCell(ISheet sheet, int columnIndex, int rowIndex)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
var cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
String cellValue = null;
if (cell != null)
{
if (cell.CellType == NPOI.SS.UserModel.CellType.String)
{
cellValue = cell.StringCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
{
cellValue = cell.StringCellValue;
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
cellValue = cell.DateCellValue.ToString();
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
}
}
return(cellValue);
}
private object GetCellValue(ICell cell)
{
switch (cell.CellType)
{
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.String:
return(cell.StringCellValue.Trim());
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue);
}
else
{
if (cell.NumericCellValue == (int)cell.NumericCellValue)
{
return((int)cell.NumericCellValue);
}
else
{
return(cell.NumericCellValue);
}
}
default:
return(cell.StringCellValue.Trim());
}
}
public static string GetValueCell(this IRow row, int column, IFormulaEvaluator evaluator)
{
string result = string.Empty;
ICell cell = row.GetCell(column);
if (cell != null)
{
var cellValue = evaluator.Evaluate(cell);
switch (cellValue.CellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
result = new DateTime((long)cellValue.NumberValue).ToString(Enums.FormatType.FormatDateVN);
}
else
{
result = cellValue.NumberValue.ToString();
}
break;
case CellType.String:
result = cellValue.StringValue;
break;
}
}
return(result);
}
/// <summary>
/// 根据单元格类型读取值
/// </summary>
/// <param name="cell">单元格</param>
/// <returns></returns>
private object GetCellValue(ICell cell)
{
if (cell == null)
{
return(null);
}
if (cell.CellType == CellType.Boolean)
{
return(cell.BooleanCellValue);
}
else if (cell.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
return(cell.DateCellValue);
}
else if ((cell.CellType == CellType.Formula && cell.CachedFormulaResultType == CellType.Numeric) || cell.CellType == CellType.Numeric)
{
//检测是否是百分比 是的话，手动除以100，因为程序里通常存的是50，表示50%
string formatCode = _df.GetFormat(cell.CellStyle.DataFormat);
if (formatCode.EndsWith("%"))
{
return(cell.NumericCellValue * 100);
}
else
{
return(cell.NumericCellValue);
}
}
else
{
return(cell.ToString());
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return(null);
case CellType.Boolean: //BOOLEAN:
return(cell.BooleanCellValue);
case CellType.Numeric: //NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
{
return(cell.DateCellValue);
}
else
{
return(cell.NumericCellValue);
}
case CellType.String: //STRING:
return(cell.StringCellValue);
case CellType.Error: //ERROR:
return(cell.ErrorCellValue);
case CellType.Formula: //FORMULA:
default:
return("=" + cell.CellFormula);
}
}
public static string GetValueCell(this IRow row, int column)
{
string result = string.Empty;
ICell cell = row.GetCell(column);
if (cell != null)
{
CellType _cellType = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;
switch (_cellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
result = cell.DateCellValue.ToString(Enums.FormatType.FormatDateVN);
}
else
{
result = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
result = cell.StringCellValue;
break;
}
}
return(result);
}
//更具xml node节点生成对应的CellNode实例
public Data.CellNode getCellNode()
{
GlobalData.DATA_TYPE dataType;
double num = 0;
Boolean boolData = false;
string str = null;
//获取单元格的数据类型
CellType cellType = _cell.CellType == CellType.Formula ? _cell.CachedFormulaResultType : _cell.CellType;
if (cellType == CellType.Blank)
{
return(null);
}
else if (cellType == CellType.Numeric && !HSSFDateUtil.IsCellDateFormatted(_cell))
{
num = _cell.NumericCellValue;
dataType = GlobalData.DATA_TYPE.DOUBLE;
}
else if (cellType == CellType.Boolean)
{
boolData = _cell.BooleanCellValue;
dataType = GlobalData.DATA_TYPE.BOOLEAN;
}
else
{
str = _cell.StringCellValue;
dataType = GlobalData.DATA_TYPE.STRING;
}
return(new Data.CellNode(dataType, _key, num, boolData, str));
}
public string GetCellString(IRow row, int cellNum, bool AllowNull)
{
if (row.GetCell(cellNum) == null)
{
if (AllowNull)
{
return(string.Empty);
}
else
{
throw new ArgumentNullException((cellNum + 1).ToString() + 1);
}
}
if (string.IsNullOrEmpty(row.GetCell(cellNum).ToString().Trim()))
{
if (AllowNull)
{
return(string.Empty);
}
else
{
throw new ArgumentNullException((cellNum + 1).ToString());
}
}
if (row.GetCell(cellNum).CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(cellNum)))//日期类型
{
return(row.GetCell(cellNum).DateCellValue.ToString("yyyy-MM-dd"));
}
}
return(row.GetCell(cellNum).ToString().Trim());
}
public DataTable ReadDataTable()
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);
if (headerRow == null)
{
return(new DataTable());
}
int cellfirst = headerRow.FirstCellNum;
int celllast = headerRow.LastCellNum;
List <int> array = new List <int>();
for (int i = cellfirst; i < celllast; i++)
{
string str = headerRow.GetCell(i)._ToStrTrim();
if (str != "")
{
DataColumn column = new DataColumn(str);
table.Columns.Add(column);
array.Add(i);
}
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
DataRow dataRow = table.NewRow();
for (int j = 0; j < array.Count; j++)
{
if (row.GetCell(array[j]) != null)
{
if (row.GetCell(array[j]).CellType == CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(array[j])))
{
dataRow[j] = row.GetCell(array[j]).DateCellValue;
}
else
{
dataRow[j] = row.GetCell(array[j]).NumericCellValue;
}
}
else
{
dataRow[j] = row.GetCell(array[j]).ToString();
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
/// <summary>
/// 获取单元格的值
/// </summary>
/// <param name="rowIdx">行号</param>
/// <param name="colIdx">列号</param>
/// <returns></returns>
public object GetCellValue(int rowIdx, int colIdx)
{
object obj = null;
try
{
IRow row = currentSheet.GetRow(rowIdx);
ICell cell = currentSheet.GetRow(rowIdx).GetCell(colIdx);
if (cell == null)
{
return(obj = "");
}
switch (cell.CellType)
{
case CellType.Boolean:
obj = cell.BooleanCellValue;
break;
case CellType.Error:
obj = cell.ErrorCellValue;
break;
case CellType.Numeric:
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
{
DateTime dateTime = cell.DateCellValue;
obj = dateTime.ToShortDateString(); //转换为短日期类型
}
else //其他数字类型
{
obj = cell.NumericCellValue;
}
//obj = cell.NumericCellValue;
break;
case CellType.String:
obj = cell.StringCellValue;
break;
case CellType.Formula:
obj = cell.CellFormula;
break;
default:
obj = "";
break;
}
}
catch (Exception ex)
{
string message = string.Format("获取表格{0}行{1}列的出错，{2}", rowIdx, colIdx, ex.Message);
//MapSoftLog.LogError(message, ex);
}
return(obj);
}
/// <summary>
/// 导入Excel中的数据到DataSet
/// </summary>
/// <param name="filePath">路径</param>
/// <returns>DataSet</returns>
//public static DataSet ImportExcel(string filePath,string type=null)
//{
// //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
// string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 14.0'";
// using (OleDbConnection OleConn = new OleDbConnection(strConn))
// {
// OleConn.Open();
// string sql = "SELECT * FROM [Sheet1$]";
// OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
// DataSet OleDsExcle = new DataSet();
// OleDaExcel.Fill(OleDsExcle, "Sheet1");
// OleConn.Close();
// return OleDsExcle;
// }
//}
#region 原导入，已注释
///// <summary>
///// 导入Excel数据
///// </summary>
///// <param name="fileName"></param>
///// <returns></returns>
//public static DataSet ImportExcel(string fileName, string type)
//{
// if (string.IsNullOrEmpty(type))
// return null;
// //把EXCEL导入到DataSet
// DataSet ds = new DataSet();
// string connStr = "";
// //if (type == "2003")
// //{
// // connStr = " Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=Excel 8.0;";
// //}
// //if (type == "2007")
// //{
// connStr = " Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=Excel 12.0;";
// //}
// using (OleDbConnection conn = new OleDbConnection(connStr))
// {
// conn.Open();
// OleDbDataAdapter da;
// //for (int i = 1; i <= n; i++)
// //{
// string sql = "select * from [Sheet1$]";
// da = new OleDbDataAdapter(sql, conn);
// da.Fill(ds, "Sheet1");
// da.Dispose();
// //}
// conn.Close();
// conn.Dispose();
// }
// //删除空白行
// if (ds.Tables.Count > 0 && ds.Tables[0].Rows != null)
// {
// List<DataRow> NullRow = new List<DataRow>();
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// bool HasValue = false;
// foreach (var item in ds.Tables[0].Rows[i].ItemArray)
// {
// if (item != null && item.ToString().Trim() != "")
// {
// HasValue = true;
// continue;
// }
// }
// if (!HasValue)
// {
// NullRow.Add(ds.Tables[0].Rows[i]);
// }
// }
// if (NullRow.Count > 0)
// {
// NullRow.ForEach(x => ds.Tables[0].Rows.Remove(x));
// }
// }
// return ds;
//}
#endregion
#region 使用NPOI导入
/// <summary>
/// 使用NPOI导入Excel数据
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public static DataSet ImportExcel(Stream stream)
{
DataSet ds = new DataSet();
var wookBook = new HSSFWorkbook(stream);
for (int sheetIndex = 0; sheetIndex < wookBook.NumberOfSheets; sheetIndex++)
{
var sheet = wookBook.GetSheetAt(sheetIndex);
var table = GenrateNPOITable(sheet.GetRow(sheet.FirstRowNum));
for (int rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var tabelRow = table.NewRow();
var excelRow = sheet.GetRow(rowIndex);
if (excelRow != null)
{
for (int colIndex = 0; colIndex < table.Columns.Count; colIndex++)
{
var cell = excelRow.GetCell(colIndex);
if (cell != null)
{
if (cell.CellType == CellType.NUMERIC && HSSFDateUtil.IsCellDateFormatted(cell))
{
tabelRow[colIndex] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
tabelRow[colIndex] = cell.ToString();
}
}
}
}
table.Rows.Add(tabelRow);
}
//删除空行
for (int i = table.Rows.Count - 1; i >= 0; i--)
{
var row = table.Rows[i];
bool IsNullRow = true;
for (int j = 0; j < table.Columns.Count; j++)
{
if (!string.IsNullOrWhiteSpace(row[j].ToString()))
{
IsNullRow = false;
break;
}
}
if (IsNullRow)
{
table.Rows.Remove(row);
}
}
ds.Tables.Add(table);
}
return(ds);
}
//格式转换
private static String parseExcel(ICell cell)
{
string result = "";
switch (cell.CellType)
{
case CellType.Formula:
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
result = e.Evaluate(cell).StringValue;
break;
case CellType.Numeric: // 数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))
{ // 处理日期格式、时间格式
string sdf = "";
if (cell.CellStyle.DataFormat == HSSFDataFormat
.GetBuiltinFormat("h:mm"))
{
sdf = "HH:mm";
}
else
{ // 日期
sdf = "yyyy-MM-dd";
}
DateTime date = cell.DateCellValue;
result = date.ToString(sdf);
}
else if (cell.CellStyle.DataFormat == 58)
{
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
string sdf = "yyyy-MM-dd";
double value = cell.NumericCellValue;
DateTime date = new DateTime(1899, 12, 30); // 起始时间
date = date.AddDays(value);
result = date.ToString(sdf);
}
else
{
result = cell.NumericCellValue.ToString();
}
break;
case CellType.String: // String类型
result = cell.StringCellValue;
break;
case CellType.Blank:
result = "";
break;
default:
result = "";
break;
}
return(result);
}
/// <summary>
/// 复制单元格
/// </summary>
/// <param name="wb"></param>
/// <param name="srcCell"></param>
/// <param name="distCell"></param>
/// <param name="copyValueFlag"></param>
public static void CopyCell(IWorkbook wb, ICell srcCell, ICell distCell, bool copyValueFlag)
{
ICellStyle newstyle = wb.CreateCellStyle();
CopyCellStyle(wb, srcCell.CellStyle, newstyle);
//样式
distCell.CellStyle = newstyle;
//评论
if (srcCell.CellComment != null)
{
distCell.CellComment = srcCell.CellComment;
}
// 不同数据类型处理
CellType srcCellType = srcCell.CellType;
distCell.SetCellType(srcCellType);
if (copyValueFlag)
{
if (srcCellType == CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(srcCell))
{
distCell.SetCellValue(srcCell.DateCellValue);
}
else
{
distCell.SetCellValue(srcCell.NumericCellValue);
}
}
else if (srcCellType == CellType.String)
{
distCell.SetCellValue(srcCell.RichStringCellValue);
}
else if (srcCellType == CellType.Blank)
{
// nothing21
}
else if (srcCellType == CellType.Boolean)
{
distCell.SetCellValue(srcCell.BooleanCellValue);
}
else if (srcCellType == CellType.Error)
{
distCell.SetCellErrorValue(srcCell.ErrorCellValue);
}
else if (srcCellType == CellType.Formula)
{
distCell.SetCellFormula(srcCell.CellFormula);
}
else
{ // nothing29
}
}
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable ImportExcel(string strFileName)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j);
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
dataRow[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
}
else//其他数字类型
{
dataRow[j] = cell.NumericCellValue;
}
}
else
{
dataRow[j] = cell.ToString();
}
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
/// <summary>
/// 将excel中的cell的值做转换
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private object GetCellValue(ICell cell)
{
switch (cell.CellType)
{
case CellType.Formula: //公式
object result;
try
{
try
{
result = cell.StringCellValue;
}
catch
{
result = cell.NumericCellValue;
}
}
catch
{
result = null;
}
return(result);
case CellType.Blank:
return(cell.StringCellValue);
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.Error:
return(cell.ErrorCellValue);
case CellType.Numeric:
{
// 解决日期类型值读取问题 liufeng 2016年11月28日15:42:45
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue);
}
else
{
return(cell.NumericCellValue);
}
}
case CellType.String:
// 排除首尾空格 liufeng 2016年11月29日17:01:11
return(cell.StringCellValue.Trim());
default:
return(null);
}
}
/// <summary>
/// 从工作表中生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
//如果遇到某行的第一个单元格的值为空，则不再继续向下读取
if (row != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))
{
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j).CellType.Equals(CellType.Numeric))
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy/M/d");
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
}
return(table);
}
/// <summary>
/// 获取列的值
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static string GetCellValue(ICell cell)
{
//为空直接返回数据
if (cell == null)
{
return(string.Empty);
}
string value = string.Empty;
switch (cell.CellType)
{
case CellType.Unknown: //未知类型
value = cell.ToString();
break;
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue.ToShortDateString();
}
else
{
value = cell.ToString();
}
break;
case CellType.String: //string类型
value = cell.StringCellValue;
break;
case CellType.Formula: //计算公式
value = string.Empty;
break;
case CellType.Blank: //空文本
value = string.Empty;
break;
case CellType.Boolean: //逻辑类型
value = cell.ToString();
break;
case CellType.Error: //错误内容
value = string.Empty;
break;
default:
value = string.Empty;
break;
}
return(value);
}
/// <summary>
/// 根据Excel列类型获取列的值.
/// </summary>
/// <param name="cell">cell.</param>
/// <returns>值.</returns>
public static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Numeric:
return(HSSFDateUtil.IsCellDateFormatted(cell) ? $"{cell.DateCellValue:G}" : cell.NumericCellValue.ToString());
case CellType.Unknown:
default:
return(cell.ToString());
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
var da = cell.Sheet.Workbook.GetType().Name;
if (da == "HSSFWorkbook")
{
var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
else
{
var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private object GetValueType(ICell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.Blank:
return("");
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue);
}
else if (cell.CellStyle.DataFormat == 58)
{
return(cell.DateCellValue);
}
else
{
return(cell.NumericCellValue);
}
//short format = cell.CellStyle.DataFormat;
//switch (format)
//{
// case 14:
// case 177:
// case 178:
// case 179:
// return cell.DateCellValue;
// default:
// return cell.NumericCellValue;
//}
case CellType.String:
return(cell.StringCellValue);
case CellType.Error:
return(cell.ErrorCellValue);
case CellType.Formula:
default:
return("=" + cell.CellFormula);
}
}
//对单元格进行判断取值
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
return(string.Empty);
case CellType.Boolean: //bool类型
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
{
return(cell.DateCellValue.ToString());
}
else //其它数字
{
return(cell.NumericCellValue.ToString());
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return(cell.ToString()); //
case CellType.String: //string 类型
return(cell.StringCellValue);
case CellType.Formula: //带公式类型
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public DataTable Import(string strFileName, int index)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(index);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.BLANK:
return(string.Empty);
case CellType.BOOLEAN:
return(cell.BooleanCellValue.ToString());
case CellType.ERROR:
return(cell.ErrorCellValue.ToString());
case CellType.NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue.ToString());
}
else
{
return(cell.ToString());
}
case CellType.Unknown:
default:
return(cell.ToString());
case CellType.STRING:
return(cell.StringCellValue);
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
private String getStringCellValue(XSSFFormulaEvaluator eva, ICell cell)
{
// 获取单元格数据内容为字符串类型的数据
String strCell = "";
switch (cell.CellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
if (cell.DateCellValue != null)
{
//lynn:因为有些数据是有时分秒的，并且vourcher需要这个去判断时间
strCell = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
strCell = null;
}
}
else
{
//var kk = cell.ToString();
strCell = cell.NumericCellValue.ToString();
}
break;
case CellType.Formula:
var tempType = eva.Evaluate(cell);
if (tempType.CellType == CellType.Numeric)
{
strCell = tempType.NumberValue.ToString();
}
else
{
strCell = tempType.StringValue;
}
break;
default:
strCell = cell.ToString();
break;
}
return(strCell);
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Numeric:
case CellType.Unknown:
default:
if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型
{
return(cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"));
}
else //其他数字类型
{
return(cell.NumericCellValue.ToString());
}
//return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
/// <summary>
/// 根据Excel列类型获取列的值(一般用于读取)
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
public static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
if (cell.CellType.ToString() == "System.DBNull")
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Numeric:
//日期类型
return(HSSFDateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString() : cell.NumericCellValue.ToString());
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
default:
return(cell.ToString());
}
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="cell">cell对象</param>
/// <returns></returns>
private static object GetCellValue(ICell cell)
{
if (cell == null || (cell.CellType == CellType.String &&
string.IsNullOrWhiteSpace(cell.StringCellValue)))
{
return(null);
}
CellType cellType = cell.CellType;
switch (cellType)
{
case CellType.Blank:
return(null);
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.Error:
return(cell.ErrorCellValue);
case CellType.Formula:
return(cell.NumericCellValue);
case CellType.Numeric:
// 区分日期格式类型
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
DateTime date = cell.DateCellValue;
return(date.ToString(DATE_FORMATTER_PATTERN));
}
else
{
cell.SetCellType(CellType.String);
return(cell.StringCellValue);
}
case CellType.String:
return(cell.StringCellValue);
default:
return(null);
}
}
public static object GetCellValue(ICell cell)
{
if (cell == null)
{
return("");
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
return(cell.StringCellValue);
case CellType.Boolean:
return(cell.BooleanCellValue);
case CellType.Error:
return(cell.ErrorCellValue);
case CellType.Formula:
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue);
}
else
{
return(cell.NumericCellValue);
}
case CellType.String:
return(cell.StringCellValue);
case CellType.Unknown:
return(cell.RichStringCellValue);
default:
return(cell.StringCellValue);
}
}
}
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return(string.Empty);
}
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Numeric:
return(HSSFDateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd") : cell.NumericCellValue.ToString());
case CellType.Unknown:
default:
return(cell.ToString());
case CellType.String:
return(cell.StringCellValue);
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return(cell.ToString());
}
catch
{
return(cell.NumericCellValue.ToString());
}
}
}
