美文网首页
Vue实现Excel模板文件的上传与下载

Vue实现Excel模板文件的上传与下载

作者: 程序猿贝塔 | 来源:发表于2020-05-20 11:25 被阅读0次

    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.结语

    通过记录自己在项目中使用到的技术点,或者遇到的一些问题,来避免后续再碰到类似问题时无从下手。记录点滴开发日常,和大家分享开发经历和生活感悟。

    相关文章

      网友评论

          本文标题:Vue实现Excel模板文件的上传与下载

          本文链接:https://www.haomeiwen.com/subject/bmygohtx.html