/// <summary>
/// 读取sheet数据
/// </summary>
/// <param name="dt"></param>
/// <param name="sheet"></param>
/// <param name="NullOrEmptyIgnore"></param>
private void ReadSheetData(DataTable dt, ISheet sheet, int curIndex, int sheetCount, Action <int> reportProcessValue = null)
{
#region//读取数据
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i); //读取当前行数据
if (row == null)
{
continue;
}
DataRow newrow = dt.NewRow();
bool IsNullOrEmpty = true;
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j); //当前表格
string cellvalue = "";
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank: //空数据类型处理
{
cellvalue = "";
break;
}
case CellType.Numeric:
{
//特殊处理吧，木办法
string DataFormat = cell.CellStyle.GetDataFormatString();
if (DataFormat == "yyyymm") //
{
//DateTime date = cell.DateCellValue;
//if (string.IsNullOrEmpty(DataFormat))
//{
// cellvalue = date.ToString("yyyy-MM-dd HH:mm:ss");
//}
//else
//{
// cellvalue = date.ToString(DataFormat);
//}
//cellvalue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
if (DataFormat == "yyyymm")
{
DataFormat = "yyyyMM";
}
cellvalue = cell.DateCellValue.ToString(DataFormat);
}
else if (HSSFDateUtil.IsCellInternalDateFormatted(cell))
{
cellvalue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
cellvalue = cell.NumericCellValue.ToString();
}
break;
}
case CellType.String:
{
cellvalue = cell.StringCellValue;
break;
}
case CellType.Formula:
{
if (cell.CachedFormulaResultType == CellType.Numeric)
{
cellvalue = cell.NumericCellValue.ToString();
}
else
{
cellvalue = cell.StringCellValue;
}
break;
}
default:
{
cellvalue = cell.ToString();
break;
}
}
}
if (!string.IsNullOrEmpty(cellvalue))
{
IsNullOrEmpty = false;
}
newrow[j] = cellvalue;
}
if (reportProcessValue != null)
{
double dblHaveReadSheetPercent = curIndex * 1.0 / sheetCount;
double dblCurSheetPercent = 1 / sheetCount;
double dblCurReadRowCountPercent = (i + 1) * 1.0 / sheet.LastRowNum;
if (i >= sheet.LastRowNum / 2)
{
}
double dblCurProcess = dblHaveReadSheetPercent + dblCurReadRowCountPercent / dblCurSheetPercent;
reportProcessValue((int)(dblCurProcess * 100));
}
if (IsNullOrEmpty)//如果是空行
{
continue;
}
dt.Rows.Add(newrow);
}
#endregion
}
/// <summary>
/// HSSFWorkbook To DataTable
/// </summary>
/// <returns></returns>
public DataTable GetDataTable(int?userId, string filePath, out string message)
{
message = string.Empty;
bool exists = IsExists(filePath, out message);
if (!exists)
{
return(null);
}
string fileName = System.IO.Path.GetFileName(filePath);
try
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
FileStream streamfile = new FileStream(filePath, FileMode.Open, FileAccess.Read);
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(streamfile);
}
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(streamfile);
}
sheet = workbook.GetSheetAt(0);
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0); //第一行为标题行
int cellCount = headerRow.LastCellNum; //总列
int rowCount = sheet.LastRowNum; //总行
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
DataColumn colAdder = new DataColumn("Adder");
DataColumn colAddTime = new DataColumn("AddTime");
DataColumn colFileName = new DataColumn("FileName");
table.Columns.Add(colAdder);
table.Columns.Add(colAddTime);
table.Columns.Add(colFileName);
DateTime addTime = DateTime.Now;
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
dataRow["Adder"] = userId;
dataRow["AddTime"] = addTime;
dataRow["FileName"] = fileName;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.Cells[j];
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = string.Empty;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
dataRow[j] = cell.DateCellValue;
}
else if (HSSFDateUtil.IsCellInternalDateFormatted(cell))
{
dataRow[j] = cell.DateCellValue;
}
else
{
dataRow[j] = cell.NumericCellValue;
}
break;
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Error:
dataRow[j] = cell.ErrorCellValue.ToString();
break;
case CellType.Formula:
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
dataRow[i] = eva.Evaluate(cell).StringValue;
break;
default:
dataRow[j] = cell.ToString();
break;
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
catch (Exception ex)
{
message += ex.Message;
return(null);
}
}