/// <summary>
/// Excel转table 导入 Michaux 20160531
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null)
{
var datatable = new DataTable();
if (file.FileName.IndexOf("xlsx") > -1)
{
NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
//if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue))
//{
//}
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
#region 清除最后的空行
for (int i = datatable.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (datatable.Rows[i][j] != null)
{
if (datatable.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
datatable.Rows[i].Delete();
}
}
#endregion
return datatable;
}
