快捷導(dǎo)航

vue實(shí)現(xiàn)Excel文件的上傳與下載功能的兩種方式

更新時(shí)間：2019年06月28日 13:39:08 作者：wnxyz8023

這篇文章主要介紹了vue實(shí)現(xiàn)Excel文件的上傳與下載功能，本文通過兩種方式給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

一.前言項(xiàng)目中使用到比較多的關(guān)于Excel的前端上傳與下載，整理出來，以便后續(xù)使用或分析他人。

1.前端vue:模板下載與導(dǎo)入Excel

導(dǎo)入Excel封裝了子組件，點(diǎn)擊導(dǎo)入按鈕可調(diào)用子組件，打開文件上傳的對話框，上傳成功后返回結(jié)果

<el-col style="padding: 10px 0 20px;">
    <el-button
     class="pull-right"
     icon="el-icon-upload"
     type="primary"
     size="mini"
     @click="importFile()"
    >批量導(dǎo)入</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>
　　
 <!-- 批量導(dǎo)入Dialog開始 -->
  <uploadTemp
   :apiURL="fileUploadUrl"
   ref="refFileUpload"
   :Refresh="Refresh"
   :OtherParams="{brandId: QueryParams.BrandID}"
  ></uploadTemp>
  <!-- 批量導(dǎo)入Dialog結(jié)束 -->
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: `導(dǎo)入成功,成功${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: `上傳文件中數(shù)據(jù)為空`,
        type: "error"
       });
      }
     }
    })
    .catch(function(error) {
     console.log(error);
    });
  },

這是上傳文件的調(diào)用方法。

2.模板下載

關(guān)于模板下載，之前沒有考慮到IE10瀏覽器的兼容問題，導(dǎo)致在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);
    }
   });
  },

　　前端的處理就結(jié)束了。

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);//獲取上傳文件的擴(kuò)展名
        string NoFileName = Path.GetFileNameWithoutExtension(filename);//獲取無擴(kuò)展名的文件名
        string FileType = ".xls,.xlsx";//定義上傳文件的類型字符串
        if (!FileType.Contains(fileEx))
        {
          throw new UserFriendlyException(501, "無效的文件類型，只支持.xls和.xlsx文件");
        }
        //源數(shù)據(jù)
        MemoryStream msSource = new MemoryStream();
        file.CopyTo(msSource);
        msSource.Seek(0, SeekOrigin.Begin);
        DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true);

        //模板數(shù)據(jù)
        string dataDir = _hosting.WebRootPath;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄 
        dataDir = Path.Combine(dataDir, "ExcelTemplate");
        var path = dataDir + "http://檔案模版.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);
        //驗(yàn)證是否同模板相同 
        string columnName = templateExcel.Columns[0].ColumnName;
        if (columnName != sourceExcel.Columns[0].ColumnName)
        {
          throw new UserFriendlyException(501, "上傳的模板文件不正確");
        }
        int sucessCount = 0;
        int errorCount = 0;
        // 處理后臺邏輯 執(zhí)行 插入操作

        uploadResult.SuccessCount = sucessCount;
        uploadResult.ErrorCount = errorCount;
        uploadResult.uploadErrors = errorList;
        return uploadResult;
      }
      catch (Exception ex)
      {
        throw new UserFriendlyException(501, "上傳的模板文件不正確");
      }
    }

將文件流轉(zhuǎn)化為Datable

 public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
    {
      //定義要返回的datatable對象
      DataTable data = new DataTable();
      //excel工作表
      ISheet sheet = null;
      //數(shù)據(jù)開始行(排除標(biāo)題行)
      int startRow = 0;
      try
      {
        //根據(jù)文件流創(chuàng)建excel數(shù)據(jù)結(jié)構(gòu),NPOI的工廠類WorkbookFactory會自動(dòng)識別excel版本，創(chuàng)建出不同的excel數(shù)據(jù)結(jié)構(gòu)
        IWorkbook workbook = WorkbookFactory.Create(fileStream);
        //如果有指定工作表名稱
        if (!string.IsNullOrEmpty(sheetName))
        {
          sheet = workbook.GetSheet(sheetName);
          //如果沒有找到指定的sheetName對應(yīng)的sheet，則嘗試獲取第一個(gè)sheet
          if (sheet == null)
          {
            sheet = workbook.GetSheetAt(0);
          }
        }
        else
        {
          //如果沒有指定的sheetName，則嘗試獲取第一個(gè)sheet
          sheet = workbook.GetSheetAt(0);
        }
        if (sheet != null)
        {
          IRow firstRow = sheet.GetRow(0);
          //一行最后一個(gè)cell的編號 即總的列數(shù)
          int cellCount = firstRow.LastCellNum;
          //如果第一行是標(biāo)題列名
          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;
          }
          //最后一列的標(biāo)號
          int rowCount = sheet.LastRowNum;
          for (int i = startRow; i <= rowCount; ++i)
          {
            IRow row = sheet.GetRow(i);
            if (row == null || row.FirstCellNum < 0) continue; //沒有數(shù)據(jù)的行默認(rèn)是null　　　　　　　

            DataRow dataRow = data.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; ++j)
            {
              //同理，沒有數(shù)據(jù)的單元格都默認(rèn)是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;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄 
      dataDir = Path.Combine(dataDir, "ExcelTemplate");
      var path = dataDir + "http://檔案模版.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");//文件流方式，指定文件流對應(yīng)的ContenType。
    }

文件下載比較簡單

 public async Task<FileStreamResult> DownloadFiles()
    {
      string dataDir = _hosting.WebRootPath;//獲得當(dāng)前服務(wù)器程序的運(yùn)行目錄 
      dataDir = Path.Combine(dataDir, "ExcelTemplate");
      var path = dataDir + "http://檔案模版.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");//文件流方式，指定文件流對應(yīng)的ContenType。
    }

文件上傳結(jié)果通知類

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; }
  }

　通過以上處理后，我們就可以在前端實(shí)現(xiàn)文件的上傳了，若上傳失敗則會返回失敗結(jié)果

以上就是整個(gè)前后端關(guān)于文件上傳與下載的實(shí)現(xiàn)，想通過日常記錄這種方式，來幫助自己更好的掌握基礎(chǔ)，穩(wěn)固自己的技能

總結(jié)

以上所述是小編給大家介紹的vue實(shí)現(xiàn)Excel文件的上傳與下載功能的兩種方式，希望對大家有所幫助，如果大家有任何疑問歡迎給我留言，小編會及時(shí)回復(fù)大家的！

您可能感興趣的文章: