vue 自己写上传excel组件_vue实现Excel文件的上传与下载

一.前言项目中使用到比较多的关于Excel的前端上传与下载，整理出来，以便后续使用或分析他人。

1.前端vue:模板下载与导入Excel

导入Excel封装了子组件，点击导入按钮可调用子组件，打开文件上传的对话框，上传成功后返回结果

class="pull-right"

icon="el-icon-upload"

type="primary"

size="mini"

@click="importFile()"

>批量导入

class="pull-right right-10"

icon="el-icon-download"

type="primary"

size="mini"

@click="downloadFile('档案模板')"

>模板下载

size="mini"

type="primary"

icon="el-icon-plus"

class="pull-right"

@click="addRow"

>新增

placeholder="请输入编码,名称"

prefix-icon="el-icon-search"

v-model="FinQueryParams.archiveFilter"

size="mini"

>

:apiURL="fileUploadUrl"

ref="refFileUpload":Refresh="Refresh":OtherParams="{brandId: QueryParams.BrandID}"

>

importFile() {

this.$refs.refFileUpload.open();

}

向后台提交文件的方法

submitFile() {const _this = this;if (!_this.files.name) {

_this.$message.warning("请选择要上传的文件！");return false;

}

let fileFormData= newFormData();//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);

});

},

View Code

这是上传文件的调用方法。

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.后端对于文件上传和下载的处理

文件上传

publicUploadResult UploadFiles(IFormFile file, Guid brandId)

{try{

UploadResult uploadResult= newUploadResult();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 = newMemoryStream();

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= newMemoryStream();

FileStream stream= newFileStream(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;returnuploadResult;

}catch(Exception ex)

{throw new UserFriendlyException(501, "上传的模板文件不正确");

}

}

View Code

将文件流转化为Datable

public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)

{//定义要返回的datatable对象

DataTable data = newDataTable();//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= newDataColumn(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);

}

}returndata;

}catch(Exception ex)

{throwex;

}

}

View Code

文件下载比较简单

public async TaskDownloadFiles()

{string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录

dataDir = Path.Combine(dataDir, "ExcelTemplate");var path = dataDir + "//档案模版.xlsx";var memoryStream = newMemoryStream();using (var stream = newFileStream(path, FileMode.Open))

{awaitstream.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 uploadErrors { get; set; }

}

public class UploadErrorDto

{

public string RowIndex { get; set; }

public string ErrorCol { get; set; }

public string ErrorData { get; set; }

}

通过以上处理后，我们就可以在前端实现文件的上传了，若上传失败则会返回失败结果

以上就是整个前后端关于文件上传与下载的实现，想通过日常记录这种方式，来帮助自己更好的掌握基础，稳固自己的技能