NPOI 2.4.1
首先去设置下webconfig里面上传文件大小的设置，
<httpRuntime targetFramework="4.5" maxRequestLength="102400" executionTimeout="3600" />,我设置了允许上传最大100M。
话不多说，上MVC 后台代码：
/// <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>
上传7m左右的文件，耗时25S左右，其中23S在读取文件上，想办法优化下。