Vue实现Excel模板文件的上传与下载
一.前言
越来越多的项目开始使用前后端分离的方式来进行开发了，前端使用的vue来进行页面处理的，所以正常的文件下载和上传肯定是少不了的，因为这也是自己第一次使用vue来进行项目开发，所以抱着学习的态度来记录自己遇到的问题。
1.excel导入
先看代码，导入操作通过弹框来处理，选择要上传的文件后台对上传的文件进行处理。importFile()方法来触发导入文件的对话框
<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();
}
uploadTemp.vue 示例代码如下
<template>
<el-dialog
:before-close="uploadHandleClose"
:close-on-click-modal="false"
:close-on-press-escape="false"
:visible.sync="uploadVisible"
width="35%"
title="批量导入"
v-el-drag-dialog
ref="refDialogUpload"
v-loading="loading"
>
<el-dialog
append-to-body
:before-close="uploadHandleClose2"
:close-on-click-modal="false"
:close-on-press-escape="false"
:visible.sync="innerVisible"
width="35%"
title="上传结果"
v-el-drag-dialog
>
成功条数:{{successCount}}
<el-table :data="tableData" size="mini" style="width: 100%; margin-top: 10px;" border>
<el-table-column prop="rowIndex" label="行数"></el-table-column>
<el-table-column prop="errorData" label="错误原因"></el-table-column>
</el-table>
</el-dialog>
<el-upload
ref="upload"
class="upload-demo"
:action="apiURL"
:limit="1"
:file-list="fileList"
:before-upload="beforeUpload"
style="padding-bottom:20px;"
>
<el-button icon="el-icon-upload" class="right-20">点击上传模板</el-button>
<div slot="tip" class="el-upload__tip">请先下载模板，再编辑上传，仅支持excel格式文件</div>
</el-upload>
<span slot="footer">
<el-button type="info" size="medium" @click="closeFileUpload" icon="el-icon-close">关闭</el-button>
<el-button type="primary" size="medium" @click="submitFile" icon="el-icon-check">上传</el-button>
</span>
</el-dialog>
</template>
<script>
import AjaxHelper from "@/common/js/AjaxHelper";
export default {
props: {
apiURL: {
type: String,
required: true
},
// afterUploadFunc: {
// type: Function,
// required: true,
// default: function() {}
// },
Refresh: {
type: Function,
required: true,
default: function() {}
},
OtherParams: {
type: Object,
required: false,
default: null
}
},
data() {
return {
uploadVisible: false,
fileList: [],
files: {},
innerVisible: false,
tableData: [],
successCount: 0,
loading: false
};
},
methods: {
open() {
this.uploadVisible = true;
},
uploadHandleClose(d) {
this.closeFileUpload();
d();
},
uploadHandleClose2(d) {
d();
},
closeFileUpload() {
this.fileList = [];
this.uploadVisible = false;
this.files = {};
},
beforeUpload(file) {
const _this = this;
_this.fileList = [];
_this.files = file;
const extension = file.name.split(".")[1] === "xls";
const extension2 = file.name.split(".")[1] === "xlsx";
const isLt2M = file.size / 1024 / 1024 < 5;
if (!extension && !extension2) {
_this.$message.warning("上传模板只能是 xls、xlsx格式!");
}
if (!isLt2M) {
_this.$message.warning("上传模板大小不能超过 5MB!");
}
var isSubmit = (extension || extension2) && isLt2M;
if (isSubmit) {
_this.fileList.push(file);
}
return false;
},
submitFile() {
const _this = this;
_this.loading = true;
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 => {
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"
});
}
_this.loading = false;
}
})
.catch(function(error) {
console.log(error);
});
},
beforeUpload(file) {
//验证选中文件的正确性
const _this = this;
_this.fileList = [];
_this.files = file;
const extension = file.name.split(".")[1] === "xls";
const extension2 = file.name.split(".")[1] === "xlsx";
const isLt2M = file.size / 1024 / 1024 < 5;
if (!extension && !extension2) {
_this.$message.warning("上传模板只能是 xls、xlsx格式!");
}
if (!isLt2M) {
_this.$message.warning("上传模板大小不能超过 5MB!");
}
var isSubmit = (extension || extension2) && isLt2M;
if (isSubmit) {
_this.fileList.push(file);
}
return false;
}
}
};
</script>
<style>
</style>
接着向后台提交文件的方法
submitFile() {
const _this = this;
_this.loading = true;
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 => {
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"
});
}
_this.loading = false;
}
})
.catch(function(error) {
console.log(error);
});
},
这是上传文件的调用方法。
2.excel下载
下载主要是通过后台返回文件流的形式在前端浏览器进行下载，不过关于模板下载，之前没有考虑到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);
}
});
},
到此前端vue对于Excel文件的上传和下载的处理就结束了。
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;
}
}
4.文件下载
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; }
}
到此，文件上传和下载的后端就处理完毕了，通过以上处理后，我们就可以在前端实现文件的上传了，若上传失败则会返回失败结果，该结果是根据自己的业务逻辑来处理的，大家可以根据自己的业务逻辑来进行对应的处理
img
5.结语
通过记录自己在项目中使用到的技术点，或者遇到的一些问题，来避免后续再碰到类似问题时无从下手。记录点滴开发日常，和大家分享开发经历和生活感悟。
网友评论