/// <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 void AbsoluteDayYearTooLow()
{
DateTime calendar = new DateTime(1899, 1, 1);
try
{
HSSFDateUtil.AbsoluteDay(calendar, false);
Assert.Fail("Should fail here");
}
catch (ArgumentException)
{
// expected here
}
try
{
calendar = new DateTime(1903, 1, 1);
HSSFDateUtil.AbsoluteDay(calendar, true);
Assert.Fail("Should fail here");
}
catch (ArgumentException)
{
// expected here
}
}
//更具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 void TestBug54557()
{
string format = ".0000";
bool isDateFormat = HSSFDateUtil.IsADateFormat(165, format);
Assert.AreEqual(false, isDateFormat);
}
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>
/// 导入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);
}
/// <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);
}
//格式转换
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>
/// 获取列的值
/// </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>
/// 从工作表中生成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>
/// 根据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());
}
}
}
public void Bug56269()
{
double excelFraction = 41642.45833321759d;
DateTime calNoRound = HSSFDateUtil.GetJavaCalendar(excelFraction, false);
Assert.AreEqual(10, calNoRound.Hour);
Assert.AreEqual(59, calNoRound.Minute);
Assert.AreEqual(59, calNoRound.Second);
DateTime calRound = HSSFDateUtil.GetJavaCalendar(excelFraction, false, null, true);
Assert.AreEqual(11, calRound.Hour);
Assert.AreEqual(0, calRound.Minute);
Assert.AreEqual(0, calRound.Second);
}
/// <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="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());
}
}
}
/// <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);
}
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());
}
}
}
/// <summary>
/// 获取Cell字符串
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static string GetCellString(ICell cell)
{
string cellValue = "";
if (null != cell)
{
// 以下是判断数据的类型
switch (cell.CellType)
{
case CellType.Numeric: // 数字
if (HSSFDateUtil.IsCellDateFormatted(cell))
{ // 判断是否为日期类型
cellValue = cell.DateCellValue.ToString("yyyyMMdd HH:mm:ss");
}
else
{
// 有些数字过大，直接输出使用的是科学计数法： 2.67458622E8 要进行处理
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.Format(cell.NumericCellValue);
// cellValue = cell.getNumericCellValue() + "";
}
break;
case CellType.String: // 字符串
cellValue = cell.StringCellValue;
break;
case CellType.Boolean: // Boolean
cellValue = cell.BooleanCellValue ? "1" : "0";
break;
default:
cellValue = "wuwuyaoyao";
break;
}
}
return(cellValue);
}
private string _ReadCellNodeData(ICell cell)
{
string cellDataStr;
CellType cellType = cell.CellType == CellType.Formula ? cell.CachedFormulaResultType : cell.CellType;
if (cellType == CellType.Blank)
{
cellDataStr = null;
}
else if (cellType == CellType.Numeric && !HSSFDateUtil.IsCellDateFormatted(cell))
{
cellDataStr = "" + cell.NumericCellValue;
}
else if (cellType == CellType.Boolean)
{
cellDataStr = cell.BooleanCellValue ? "true" : "false";
}
else
{
cellDataStr = cell.StringCellValue;
}
return(cellDataStr);
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
NpoiDataType[] ColumnDataType = null;
try
{
using (FileStream stream = new FileStream(@fileName, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(stream);//使用接口，自动识别excel2003/2007格式
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
if (ColumnDataType == null)
{
ColumnDataType = new NpoiDataType[cellCount];
for (int j = 0; j < cellCount; j++)
{
ICell hs = row.GetCell(j);
if (hs != null)
{
ColumnDataType[j] = GetCellDataType(hs);
}
}
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell hs = row.GetCell(j);
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
if (hs.CellType == CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(hs))
{
dataRow[j] = HSSFDateUtil.GetJavaDate(hs.NumericCellValue).ToString("HH:mm:ss");
}
else
{
dataRow[j] = hs;
}
//dataRow[j] = row.GetCell(j).ToString();
}
//dataRow[j] = GetCellData(ColumnDataType[j], row, j);
}
data.Rows.Add(dataRow);
}
}
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="headno">要跳过的行</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, int headno)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
// int i = workbook.NumberOfSheets;////获取个数
// string sheetname = workbook.GetSheetName(0);//获取名字
//sheet = workbook.GetSheetAt(0); //获取指定的那一个
// MessageBox.Show(i.ToString());
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (headno != 0)
{
startRow = sheet.FirstRowNum + headno;//第一行
}
else
{
startRow = sheet.FirstRowNum;
}
if (sheet != null)
{
int rowCount = sheet.LastRowNum; //获得行数
int maxcellCount = 0; //最大列数量
int maxcellno = 0; //最大列的行号
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null continue 结束单次循环
}
int cellCount = row.LastCellNum; //一行最后一个cell的编号 这一行的总的列数
if (cellCount > maxcellCount)
{
maxcellCount = cellCount;//最大列数
maxcellno = i;
}
}
IRow firstRow = sheet.GetRow(maxcellno);
int cellNo = 0;
for (int i = firstRow.FirstCellNum; i < maxcellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
// string cellValue = "";
/**********
* //单元格的类型为公式，返回公式的值
* if (cell.CellType == CellType.Numeric)
* {
* //是日期型
* if (HSSFDateUtil.IsCellDateFormatted(cell))
* {
* cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
* }
* //不是日期型
* else
* {
* cellValue = Convert.ToString(cell);
* }
* }
* else
* {
* cellValue = Convert.ToString(cell);
* }
*
*****/
cellNo = cellNo + 1;
string cellValue = Convert.ToString(cell) + cellNo;
// string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
//最后一列的标号
//int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.Cells.Count == 0)
{
continue; //没有数据的行默认是null continue 结束单次循环
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < maxcellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
string datastr = "";
/**********/
//单元格的类型为公式，返回公式的值 CellType.Numeric
if (row.GetCell(j).CellType == CellType.Numeric)
{
//是日期型
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
datastr = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
dataRow[j] = datastr;
}
else
{
dataRow[j] = row.GetCell(j).NumericCellValue.ToString();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
/**********/
string va = dataRow[j].ToString();
bool r = IsDateTime(va);
if (r)
{
va = va.Replace(".", "-");
va = va.Replace("/", "-");
va = va.Replace("年", "-");
va = va.Replace("月", "-");
va = va.Replace("日", "");
va = va.Replace("\\", "-");
}
if (va.Length > 0 && va.Substring(va.Length - 1, 1) == "-")
{
va = va.Substring(0, va.Length - 1);
}
dataRow[j] = va;
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
public DataTable ToDataTable(Stream stream, int minRow = 0, int maxRow = 0)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
//var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
try
{
workbook = new XSSFWorkbook(stream);//2007
}
catch (Exception)
{
try
{
workbook = new HSSFWorkbook(stream);//2003
}
catch
{
throw new Exception("读取Excel文件失败！");
}
}
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
if (minRow != 0) startRow = minRow - 1;//真实序号
IRow firstRow = sheet.GetRow(startRow);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int i = 0; i < cellCount; ++i)
{
DataColumn column = new DataColumn(i.ToString());
data.Columns.Add(column);
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
//if (maxRow != 0 && rowCount > maxRow) rowCount = maxRow - 1;
if (maxRow != 0) rowCount = maxRow - 1;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Formula)
{
//公式类型
sheet.ForceFormulaRecalculation = true;
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[j] = eva.EvaluateInCell(row.GetCell(j));
}
else if (row.GetCell(j).CellType == CellType.Numeric)
{
if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
{
//日期类型
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
//其他数字类型
dataRow[j] = row.GetCell(j).NumericCellValue;
}
}
else if (row.GetCell(j).CellType == CellType.Blank)
{
//空数据类型
dataRow[j] = "";
}
else
{
dataRow[j] = row.GetCell(j).StringCellValue;
}
}
}
data.Rows.Add(dataRow);
}
//fs.Close();
}
return data;
}
