Vue實作Excel模闆檔案的上傳與下載下傳

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

到此，檔案上傳和下載下傳的後端就處理完畢了，通過以上處理後，我們就可以在前端實作檔案的上傳了，若上傳失敗則會傳回失敗結果，該結果是根據自己的業務邏輯來處理的，大家可以根據自己的業務邏輯來進行對應的處理



5.結語

通過記錄自己在項目中使用到的技術點，或者遇到的一些問題，來避免後續再碰到類似問題時無從下手。記錄點滴開發日常，和大家分享開發經曆和生活感悟。
————————————————
版權聲明：本文為CSDN部落客「程式猿貝塔」的原創文章，遵循CC 4.0 BY-SA版權協定，轉載請附上原文出處連結及本聲明。
原文連結：https://blog.csdn.net/Lvc8023/article/details/106231621