NPOI导入表格

标签

<input type="file" name="file" id="fileExcel" onchange="CheckFile(this)" / > 

MVC后端处理

/// <summary>
/// 文件异步上传到Server，NPOI解析后存入数据库，2020-05-25
/// </summary>
/// <param name="file">用户上传的Excel文件</param>
/// <returns></returns>
public ActionResult ImportExcelStu(HttpPostedFileBase file) 
{
	//是否有文件
	if (file == null) 
	{
		return Content("请选择文件！");
	}
	//文件大小限制
	double fileLength = file.ContentLength / (1024.0 * 1024.0);
	if (fileLength > 2.0) 
	{
		return Content("文件不能超过2M！");
	}
	//文件扩展名位置
	int extIndex = file.FileName.LastIndexOf(".");
	if (extIndex == -1) 
	{
		extIndex = file.FileName.Length;
	}
	//文件类型限制
	string[] ext = { ".xls", ".xlsx" };
	string fileNameExt = file.FileName.Substring(extIndex);
	if (!ext.Contains(fileNameExt)) 
	{
		return Content("仅支持xls、xlsx格式！");
	}
	//读取文件流，写入数据库，2020-03-19
	try 
	{
		DataTable dtExcel = ExcelHelper.StreamToTable(file.InputStream, fileNameExt);
		/* DO SOMETHING HERE */
		//可以用SqlBulkCopy批量复制到数据库
	}
	catch (Exception) 
	{
		throw;
	}
}

NPOI处理

/// <summary>
/// Excel导入成DataTble，表头在第二行，读取表头批注
/// </summary>
/// <param name="stream">流数据</param>
/// <param name="fileNameExt">文件扩展名</param>
/// <returns></returns>
public static DataTable StreamToTable(Stream stream, string fileNameExt) 
{
	DataTable dt = new DataTable();
	IWorkbook workbook;
	//string fileExt = stream.
	if (stream.Length != 0) 
	{
		if (fileNameExt== ".xlsx") 
		{
			workbook = new XSSFWorkbook(stream);
		} else if (fileNameExt== ".xls") 
		{
			workbook = new HSSFWorkbook(stream);
		} else 
		{
			workbook = null;
		}
		if (workbook == null) 
		{
			return null;
		}
		//当前活动表单，2020-03-17
		ISheet sheet = workbook.GetSheetAt(workbook.ActiveSheetIndex);
		//表头，读取表头批注
		IRow header = sheet.GetRow(sheet.FirstRowNum);
		List<int> columns = new List<int>();
		for (int i = 0; i < header.LastCellNum; i++) 
		{
			var str = header.GetCell(i).CellComment.String.String;
			if (str == null || str == string.Empty) 
			{
				dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
			} else
			                        dt.Columns.Add(new DataColumn(str.ToString()));
			columns.Add(i);
		}
		//数据
		for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) 
		{
			DataRow dr = dt.NewRow();
			bool hasValue = false;
			foreach (int j in columns) 
			{
				dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
				if (dr[j] != null && dr[j].ToString() != string.Empty) 
				{
					hasValue = true;
				}
			}
			if (hasValue) 
			{
				dt.Rows.Add(dr);
			}
		}
	}
	return dt;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell">目标单元格</param>
/// <returns></returns>
private static object GetValueType(ICell cell) 
{
	if (cell == null)
	                return null;
	switch (cell.CellType) 
	{
		case CellType.Blank:
		                    return null;
		case CellType.boolean:
		                    return cell.BooleanCellValue;
		case CellType.Numeric:
		                    short format = cell.CellStyle.DataFormat;
		//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
		if (format == 14 || format == 31 || format == 57 || format == 58)
		                        return cell.DateCellValue; else
		                        return cell.NumericCellValue;
		case CellType.String:
		                    return cell.StringCellValue;
		case CellType.Error:
		                    return cell.ErrorCellValue;
		case CellType.Formula:
		                default:
		                    return "=" + cell.CellFormula;
	}
}