vue实现Excel文件的上传与下载功能的两种方式
(编辑:jimmy 日期: 2024/11/28 浏览:3 次 )
一.前言项目中使用到比较多的关于Excel的前端上传与下载,整理出来,以便后续使用或分析他人。
1.前端vue:模板下载与导入Excel
导入Excel封装了子组件,点击导入按钮可调用子组件,打开文件上传的对话框,上传成功后返回结果
<el-col style="padding: 10px 0 20px;"> <el-button class="pull-right" icon="el-icon-upload" type="primary" size="mini" @click="importFile()" >批量导入</el-button> <el-button class="pull-right right-10" icon="el-icon-download" type="primary" size="mini" @click="downloadFile('档案模板')" >模板下载</el-button> <el-button size="mini" type="primary" icon="el-icon-plus" class="pull-right" @click="addRow" >新增</el-button> <div class="pull-right"> <el-input placeholder="请输入编码,名称" prefix-icon="el-icon-search" v-model="FinQueryParams.archiveFilter" size="mini" ></el-input> </div> </el-col> <!-- 批量导入Dialog开始 --> <uploadTemp :apiURL="fileUploadUrl" ref="refFileUpload" :Refresh="Refresh" :OtherParams="{brandId: QueryParams.BrandID}" ></uploadTemp> <!-- 批量导入Dialog结束 --> importFile() { this.$refs.refFileUpload.open(); }
向后台提交文件的方法
submitFile() { const _this = this; if (!_this.files.name) { _this.$message.warning("请选择要上传的文件!"); return false; } let fileFormData = new FormData(); //filename是键,file是值,就是要传的文件 fileFormData.append("file", _this.files, _this.files.name); if(_this.OtherParams){ const keys=Object.keys(_this.OtherParams); keys.forEach(e=>{ fileFormData.append(e, _this.OtherParams[e]); }) } let requestConfig = { headers: { "Content-Type": "multipart/form-data" } }; AjaxHelper.post(_this.apiURL, fileFormData, requestConfig) .then(res => { console.log(res); if (res.success) { const result = res.result; if (result.errorCount == 0 && result.successCount > 0) { _this.$message({ message: `导入成功,成功${result.successCount}条`, type: "success" }); _this.closeFileUpload(); _this.Refresh(); } else if (result.errorCount > 0 && result.successCount >= 0) { _this.Refresh(); _this.tableData = result.uploadErrors; _this.successCount = result.successCount; _this.innerVisible = true; } else if (result.errorCount == 0 && result.successCount == 0) { _this.$message({ message: `上传文件中数据为空`, type: "error" }); } } }) .catch(function(error) { console.log(error); }); },
这是上传文件的调用方法。
2.模板下载
关于模板下载,之前没有考虑到IE10浏览器的兼容问题,导致在IE10下文件没法下载,后来百度后找到了解决办法。
downloadFile(name) { let requestConfig = { headers: { "Content-Type": "application/json;application/octet-stream" } }; AjaxHelper.post(this.downLoadUrl, requestConfig, { responseType: "blob" }).then(res => { // 处理返回的文件流 const content = res.data; const blob = new Blob([content]); var date = new Date().getFullYear() + "" + (new Date().getMonth() + 1) + "" + new Date().getDate(); const fileName = date + name + ".xlsx"; if ("download" in document.createElement("a")) { // 非IE下载 const elink = document.createElement("a"); elink.download = fileName; elink.style.display = "none"; elink.href = URL.createObjectURL(blob); document.body.appendChild(elink); elink.click(); URL.revokeObjectURL(elink.href); // 释放URL 对象 document.body.removeChild(elink); } else { // IE10+下载 navigator.msSaveBlob(blob, fileName); } }); },
前端的处理就结束了。
3.后端对于文件上传和下载的处理
文件上传
public UploadResult UploadFiles(IFormFile file, Guid brandId) { try { UploadResult uploadResult = new UploadResult(); if (file == null) { throw new UserFriendlyException(501, "上传的文件为空,请重新上传"); } string filename = Path.GetFileName(file.FileName); string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名 string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 if (!FileType.Contains(fileEx)) { throw new UserFriendlyException(501, "无效的文件类型,只支持.xls和.xlsx文件"); } //源数据 MemoryStream msSource = new MemoryStream(); file.CopyTo(msSource); msSource.Seek(0, SeekOrigin.Begin); DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true); //模板数据 string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "//档案模版.xlsx"; MemoryStream msModel = new MemoryStream(); FileStream stream = new FileStream(path, FileMode.Open); stream.CopyTo(msModel); msModel.Seek(0, SeekOrigin.Begin); DataTable templateExcel = ReadStreamToDataTable(stream, "", true); //验证是否同模板相同 string columnName = templateExcel.Columns[0].ColumnName; if (columnName != sourceExcel.Columns[0].ColumnName) { throw new UserFriendlyException(501, "上传的模板文件不正确"); } int sucessCount = 0; int errorCount = 0; // 处理后台逻辑 执行 插入操作 uploadResult.SuccessCount = sucessCount; uploadResult.ErrorCount = errorCount; uploadResult.uploadErrors = errorList; return uploadResult; } catch (Exception ex) { throw new UserFriendlyException(501, "上传的模板文件不正确"); } }
将文件流转化为Datable
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true) { //定义要返回的datatable对象 DataTable data = new DataTable(); //excel工作表 ISheet sheet = null; //数据开始行(排除标题行) int startRow = 0; try { //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本,创建出不同的excel数据结构 IWorkbook workbook = WorkbookFactory.Create(fileStream); //如果有指定工作表名称 if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { //如果没有指定的sheetName,则尝试获取第一个sheet sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); //一行最后一个cell的编号 即总的列数 int cellCount = firstRow.LastCellNum; //如果第一行是标题列名 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理,没有数据的单元格都默认是null ICell cell = row.GetCell(j); if (cell != null) { if (cell.CellType == CellType.Numeric) { //判断是否日期类型 if (DateUtil.IsCellDateFormatted(cell)) { dataRow[j] = row.GetCell(j).DateCellValue; } else { dataRow[j] = row.GetCell(j).ToString().Trim(); } } else { dataRow[j] = row.GetCell(j).ToString().Trim(); } } } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { throw ex; } } 文件下载比较简单 public async Task<FileStreamResult> DownloadFiles() { string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "//档案模版.xlsx"; var memoryStream = new MemoryStream(); using (var stream = new FileStream(path, FileMode.Open)) { await stream.CopyToAsync(memoryStream); } memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式,指定文件流对应的ContenType。 }
文件下载比较简单
public async Task<FileStreamResult> DownloadFiles() { string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录 dataDir = Path.Combine(dataDir, "ExcelTemplate"); var path = dataDir + "//档案模版.xlsx"; var memoryStream = new MemoryStream(); using (var stream = new FileStream(path, FileMode.Open)) { await stream.CopyToAsync(memoryStream); } memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, "application/octet-stream");//文件流方式,指定文件流对应的ContenType。 }
文件上传结果通知类
public class UploadResult { public int RepeatCount { get; set; } public int SuccessCount { get; set; } public int FileRepeatCount { get; set; } public int ErrorCount { get; set; } public List<UploadErrorDto> uploadErrors { get; set; } } public class UploadErrorDto { public string RowIndex { get; set; } public string ErrorCol { get; set; } public string ErrorData { get; set; } }
通过以上处理后,我们就可以在前端实现文件的上传了,若上传失败则会返回失败结果
以上就是整个前后端关于文件上传与下载的实现,想通过日常记录这种方式,来帮助自己更好的掌握基础,稳固自己的技能
总结
以上所述是小编给大家介绍的vue实现Excel文件的上传与下载功能的两种方式,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!
下一篇:开发中常用的25个JavaScript单行代码(小结)