NPOI 入门--上传excel文件并解析

 ?/// <summary> ???????/// 上传文件，上传实体文件 ???????/// </summary> ???????/// <param name="httpfile">与前端post的参数名一致</param> ???????[HttpPost] ???????public ActionResult UploadExcel(HttpPostedFileBase httpfile) ???????{ ???????????ResultModel rm = new ResultModel(); ???????????try ???????????{ ???????????????DataTable dt = null; ???????????????var fileName = httpfile.FileName; ???????????????var hz = fileName.Split(‘.‘); ???????????????if (hz.Count() > 0 & (hz[1].ToString() == "xlsx" || hz[1].ToString() == "xls")) ???????????????{ ???????????????????//NPOI操作excel ???????????????????using (Stream stream = httpfile.InputStream) ???????????????????{ ???????????????????????Stopwatch sw = new Stopwatch(); ???????????????????????sw.Start(); ???????????????????????var workbook1 = new XSSFWorkbook(stream); ???????????????????????var sheet = workbook1.GetSheetAt(0);//默认获取第1个sheet ???????????????????????sw.Stop(); ???????????????????????TimeSpan t1 = sw.Elapsed;//文件读取耗时 ???????????????????????/*excel流文件转为datatable*/ ???????????????????????sw.Restart(); ???????????????????????dt = RenderFromExcel(sheet, 0); ???????????????????????sw.Stop(); ???????????????????????TimeSpan t2 = sw.Elapsed;//转换datatable耗时 ???????????????????????LogHelper.Info("条数："+dt.Rows.Count+",文件读取耗时：" + t1 + ",转换datatable耗时：" + t2); ???????????????????????rm.Status = statuslist.成功; ???????????????????????rm.ResultMessage = "文件识别成功！"; ???????????????????} ???????????????} ???????????????else ???????????????{ ???????????????????rm.Status = statuslist.失败; ???????????????????rm.ResultMessage = "未识别文件类型，请上传正确文件格式！"; ???????????????????//错误 ???????????????} ???????????} ???????????catch (Exception ex) ???????????{ ???????????????rm.Status = statuslist.失败; ???????????????rm.ResultMessage = ex.Message; ???????????????????????} ???????????JsonResult jr = new JsonResult(); ???????????jr.Data = Json(rm); ???????????return jr; ???????}

 ?/// <summary> ???????/// excel转为datatable ???????/// </summary> ???????/// <param name="sheet">需要转换的isheet对象</param> ???????/// <param name="headerRowIndex">表头所在行</param> ???????/// <returns></returns> ???????private static DataTable RenderFromExcel(ISheet sheet, int headerRowIndex) ???????{ ???????????DataTable table = new DataTable(); ???????????IRow headerRow = sheet.GetRow(headerRowIndex); ???????????int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells ???????????int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 ???????????//handling header. ???????????for (int i = headerRow.FirstCellNum; i < cellCount; i++) ???????????{ ???????????????DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); ???????????????table.Columns.Add(column); ???????????} ???????????for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) ???????????{ ???????????????IRow row = sheet.GetRow(i); ???????????????DataRow dataRow = table.NewRow(); ???????????????//第一列如果为空，则不添加至datatable中 ???????????????if (row != null & GetCellValue(row.GetCell(0)) != "") ???????????????{ ???????????????????for (int j = row.FirstCellNum; j < cellCount; j++) ???????????????????{ ???????????????????????if (row.GetCell(j) != null) ???????????????????????????dataRow[j] = GetCellValue(row.GetCell(j)); ???????????????????} ???????????????????table.Rows.Add(dataRow); ???????????????} ???????????} ???????????return table; ???????}

 ???????/// <summary> ???????/// 获取excel单元格类型以及值 ???????/// </summary> ???????/// <param name="cell"></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: ???????????????????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 ???????????????????{ ???????????????????????XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); ???????????????????????// HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); ???????????????????????e.EvaluateInCell(cell); ???????????????????????return cell.ToString(); ???????????????????} ???????????????????catch ???????????????????{ ???????????????????????return cell.NumericCellValue.ToString(); ???????????????????} ???????????} ???????}

/*上传类型，Stream or File*/var _type;/*文件对象*/var _fileobj;/*posturl*/var _posturl;/*控制的对象*/var _element = null;function fileupload(type, fileobj, posturl, element) { ???try { ???????console.log("fileupload"); ???????if (fileobj != null) ???????????_fileobj = fileobj; ???????if (posturl != null) ???????????_posturl = posturl; ???????if (element != null) ???????????_element = element; ???????if (type != null) { ???????????switch (type) { ???????????????case "Stream": ???????????????????{ ???????????????????????globalmodal(_element, true); ???????????????????????_type = "Stream"; ???????????????????????var file = fileobj; ???????????????????????if (!!file) { ???????????????????????????var reader = new FileReader(); ???????????????????????????/*客户端读取文件，并发送*/ ???????????????????????????reader.readAsArrayBuffer(file); ???????????????????????????reader.onload = function (e) { ???????????????????????????????var binary = e.target.result; ???????????????????????????????upload(binary, _posturl); ???????????????????????????} ???????????????????????} else ???????????????????????????modaldisplay("3", "提示", "文件不能为：" + file); ???????????????????????break; ???????????????????} ???????????????case "File": ???????????????????{ ???????????????????????_type = "File"; ???????????????????????if (_element != null) ???????????????????????????globalmodal(_element, true); ???????????????????????var formData = new FormData(); ???????????????????????formData.append("httpfile", fileobj); ???????????????????????$.ajax({ ???????????????????????????type: ‘post‘, ???????????????????????????url: _posturl, ???????????????????????????// 告诉jQuery不要去处理发送的数据 ???????????????????????????processData: false, ???????????????????????????// 告诉jQuery不要去设置Content-Type请求头 ???????????????????????????contentType: false, ???????????????????????????data: formData, ???????????????????????????success: function (reponse) { ???????????????????????????????globalmodal(_element, false); ???????????????????????????????if (reponse.Data.Status != 1) { ???????????????????????????????????modaldisplay("2", "提示", reponse.Data.ResultMessage); ???????????????????????????????} ???????????????????????????????else { ???????????????????????????????????modaldisplay("1", "提示", reponse.Data.ResultMessage); ???????????????????????????????} ???????????????????????????} ???????????????????????}); ???????????????????????break; ???????????????????} ???????????????default: { ???????????????????globalmodal(_element, true); ???????????????????modaldisplay("3", "文件类型错误提示", "fileupload方法错误！"); ???????????????} ???????????} ???????} ???????else { ???????????modaldisplay("3", "文件类型错误提示", "请填写type！"); ???????} ???} catch (e) { ???????modaldisplay("3", "异常提示", e.message); ???} ???finally { ???}}/*发送数据*/function upload(binary, posturl) { ???try { ???????var xhr = new XMLHttpRequest(); ???????xhr.open("POST", posturl); ???????xhr.overrideMimeType("application/octet-stream"); ???????if (xhr.sendAsBinary) { ???????????xhr.sendAsBinary(binary); ???????} else { ???????????xhr.send(binary); ???????} ???????/*回调*/ ???????xhr.onload = function (e) { ???????????globalmodal(_element, false); ???????????if (this.status == 200) { ???????????????var responsedata = JSON.parse(e.target.response); ???????????????modaldisplay("1", "提示", responsedata.Data.ResultMessage); ???????????????/*表示成功*/ ???????????} ???????????else { ???????????????/*失败*/ ???????????????modaldisplay("3", "提示", "文件处理失败！"); ???????????} ???????} ???} catch (e) { ???????modaldisplay("3", "错误提示", e.message); ???}}

<div class="row" id="fileuploadmodal"> ???<input type="file" id="file2" /> ???<input type="button" id="btnupload2" value="上传文件" /> ???<input type="button" id="btnuploadstream" value="上传流" /></div> ?<script> /*文件上传*/ ???$("#btnupload2").on("click", function () { ???????var filetype = "File"; ???????var file = $("#file2")[0].files[0]; ???????var posturl = "sdm/UploadExcel"; ???????var element = $("#fileuploadmodal"); ???????fileupload(filetype, file, posturl, element); ???}); ?/*流形式*/ ???$("#btnuploadstream").on("click", function () { ???????var filetype = "Stream"; ???????var file = $("#file2")[0].files[0]; ???????var posturl = "sdm/UploadExcelStrem"; ???????var element = $("#fileuploadmodal"); ???????fileupload(filetype, file, posturl, element); ???});</script>