C#与Vue2上传下载Excel文件

1、上传文件流程：先上传文件，上传成功，返回文件名与url，然后再次发起请求保存文件名和url到数据库

        前端Vue2代码：

        使用element的el-upload组件，action值为后端接收文件接口，headers携带session信息，成功后调用onsuccess对应的方法保存文件信息

 <el-upload :action="uploadUrl" :headers="uploadHeaders" accept=".xlsx" :show-file-list="false"
                        :limit="1" ref="upload" :on-success="handleAvatarSuccess" :before-upload="beforeAvatarUpload">
                        <el-button type="primary">导入</el-button>
                    </el-upload>

beforeAvatarUpload (res) {
            const isExcel = /\.(xlsx)$/.test(res.name);
            if (!isExcel) {
                toast.error('只能读取.xlsx文件,请另存为.xlsx文件!');
                return false
            }
            const fileSuffix = res.name.substring(res.name.lastIndexOf(".") + 1);
            const list = ["exe"]
            if (list.indexOf(fileSuffix) >= 0) {
                toast.error(list.toString() + '文件类型限制传入!')
                return false
            }
            if (res.size / 1024 / 1024 > 50) {
                toast.error('文件大小不能超过50MB!')
                return false
            }
            return true
        },
        //上传成功开始保存本条数据
        handleAvatarSuccess (res, file) {
            if (res.code == apiCode.Success) {
                let instance = {
                    stationId: this.stationId,
                    originalFileName: res.data.attachmentName,
                    newFileName: res.data.fileName,
                    Url: res.data.url,
                }
                this.$axios.post(ZlCustomeTimeFile.Save, instance)
                    .then(response => {
                        let res = response.data;
                        if (res.code == apiCode.Success) {
                            this.getData()
                            toast.success()
                        } else {
                            toast.error(res.msg)
                        }

                    })
            } else {
                toast.error(res.msg)
            }
        },

          后端代码C#，aspnet core，接收文件并保存到服务器，然后返回文件信息：

        [HttpPost]
        public async Task<ApiResult<ImageUploadResponse>> UploadFileAsync(List<IFormFile> file)
        {
            if (file?.Count > 0)
            {

            }
            else
            {
                return base.DoFail<ImageUploadResponse>(ApiCode.Fail, msg: $"上传文件失败,请选择文件");
            }

            var exMsg = "";
            var formFile = file[0];
            try
            {
                var filePath = $"/upload/";
                System.IO.Directory.CreateDirectory(_hostingEnvironment.WebRootPath + filePath);
                Random rd = new Random();                
                string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + rd.Next(100, 1000) + formFile.FileName.Substring(formFile.FileName.LastIndexOf(".") == -1 ? 0 : formFile.FileName.LastIndexOf("."));
                using (var stream = System.IO.File.Create(_hostingEnvironment.WebRootPath + filePath + FileName))
                {
                    await formFile.CopyToAsync(stream);
                }
                var request = HttpContext.Request;

                return base.DoSuccess<ImageUploadResponse>(new ImageUploadResponse
                {
                    Url = $"{request.Scheme}://{request.Host.Host}{(request.Host.Port == 80 ? "" : ":" + request.Host.Port)}" + filePath + FileName,
                    FileName = FileName,
                    AttachmentName = formFile.FileName,
                });
            }
            catch (Exception ex)
            {
                exMsg = ex.Message;
            }

            return base.DoFail<ImageUploadResponse>(ApiCode.Fail, msg: $"上传文件失败,{exMsg}");
        }

2、静态a标签下载文件

<a target="_blank" :href="row.url">{{ row.originalFileName }}
<el-link :href="row.url">{{ row.originalFileName }}</el-link>

3、动态a标签下载文件，使用二进制流blob直接下载，可以修改文件名

<span style="cursor: pointer;" @click="down(row)">{{ row.originalFileName }}</span>

        //路径转为相对路径，然后使用二进制流blob直接下载，可以修改文件名
        down (data) {
            var newurl = data.url.substring(data.url.indexOf("//") + 2)
            console.log(newurl)
            newurl = newurl.substring(newurl.indexOf("/"))
            console.log(newurl)
            const x = new XMLHttpRequest()
            x.open('GET', newurl, true)
            x.responseType = 'blob'
            x.onload = () => {
                const url = window.URL.createObjectURL(x.response)
                // 创建隐藏的可下载链接
                var eleLink = document.createElement('a');
                eleLink.download = data.originalFileName;
                eleLink.style.display = 'none';
                // 下载内容转变成blob地址
                eleLink.href = url;
                // 触发点击
                document.body.appendChild(eleLink);
                eleLink.click();
                // 然后移除
                document.body.removeChild(eleLink);
            }
            x.send()
        },

4、动态下载Excel文件，使用二进制流blob，前端vue2下载按钮事件，后端C#接口代码：

     import { saveAs } from 'file-saver';
   
     downloadControllerAction (row) {
            var that = this;
            this.$axios.get(CustomeTimeFile.Download, {
                params: { id: row.id },
                responseType: 'blob'
            })
                .then(response => {
                    console.log(response)
                    if (response.status !== 200) {
                        toast.error('下载失败');
                        return;
                    }
                    const blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
                    saveAs(blob, row.originalFileName); // 直接调用saveAs函数
                }).catch((error) => {
                    console.log(error);
                    let errorMessage = '';
                    if (error instanceof Blob) {
                        const reader = new FileReader();
                        reader.onload = function (e) {
                            try {
                                const errorJson = JSON.parse(e.target.result);
                                errorMessage = errorJson.message || '出现错误';
                            } catch (err) {
                                errorMessage = '下载错误，请重新下载.';
                            }
                            toast.error(errorMessage);
                        };
                        reader.readAsText(error);
                    } else {
                        errorMessage = error.message;
                        console.error('出现错误:', errorMessage);
                        toast.error(errorMessage);
                    }

                });
        },

public async Task<IActionResult> Download([FromQuery] long id)
        {
            var fileInfo = DapperContext.GetDatabase().Get<ZustomeTimeFile>(id);
            if (fileInfo == null || fileInfo.Url.IsNullOrEmpty()) return StatusCode(500, new { Message = "找不到文件，请重新上传统计" });

            var energylist = CustomeTimeEnergyRepository.GetListByFile(fileInfo.StationId, fileInfo.Id);
            var energydict = energylist.GroupBy(t => t.RowNumber).ToDictionary(t => t.Key, t => t.First());
            try
            {               
                byte[] fileBytes = null;
                var filePath = $"/upload/";
                var fileName = fileInfo.NewFileName;
                try
                {
                    fileBytes = System.IO.File.ReadAllBytes(_hostingEnvironment.WebRootPath + filePath + fileName);
                }
                catch (Exception ex1)
                {
                    LogHelper.WriteInfo("读取文件异常，" + ex1);
                    return StatusCode(500, new { Message = "找不到已上传文件，请重新上传" });
                }

                // 从URL下载Excel文件到字节数组，内部网络权限设置，不允许从外网访问，改为直接从服务器下载
                //try
                //{
                //    fileBytes = await HttpClientHelper.DownloadFileAsync(fileInfo.Url);
                //}
                //catch (Exception ex)
                //{
                //    return StatusCode(500, new { Message = "找不到已上传文件，请重新上传" });
                //}
                // 使用NPOI解析Excel文件
                using (MemoryStream memoryStream = new MemoryStream(fileBytes, true))
                {
                    IWorkbook workbook = null;
                    // 确定文件格式并创建相应的工作簿对象
                    if (fileInfo.Url.EndsWith(".xlsx")) workbook = new XSSFWorkbook(memoryStream);
                    else if (fileInfo.Url.EndsWith(".xls")) workbook = new HSSFWorkbook(memoryStream);

                    if (workbook != null)
                    {
                        ISheet sheet = workbook.GetSheetAt(0);
                        foreach (IRow row in sheet)
                        {
                            if (energydict.ContainsKey(row.RowNum))
                            {
                                ICell cell = row.GetCell(5) ?? row.CreateCell(5);
                                if (energydict[row.RowNum].ElectricityValue.HasValue)
                                {
                                    cell.SetCellValue((double)energydict[row.RowNum].ElectricityValue);
                                }

                                cell = row.GetCell(6) ?? row.CreateCell(6);
                                if (energydict[row.RowNum].NaturalGasValue.HasValue)
                                {
                                    cell.SetCellValue((double)energydict[row.RowNum].NaturalGasValue);
                                }

                                cell = row.GetCell(7) ?? row.CreateCell(7);
                                if (!energydict[row.RowNum].StatisticMessage.IsNullOrEmpty())
                                {
                                    cell.SetCellValue(energydict[row.RowNum].StatisticMessage);
                                }
                            }
                        }
                    }

                    using (var stream = new MemoryStream())
                    {
                        workbook.Write(stream);
                        workbook.Close();
                        stream.Flush();

                        // 设置响应头以提示浏览器下载文件
                        return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileInfo.OriginalFileName);
                    }


                }
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { Message = "出现异常:" + ex.Message });
            }
        }
    }

5、读取Excel文件，NPOI方式

        public async Task<ApiResult> Save(CustomeTimeFile instance)
        {
            instance.UploadTime = DateTime.Now;
            instance.DateCode = instance.UploadTime.ToDateCode();
            instance.IsStatistic = 0;
            instance.UploaderName = CurrentUser.RealName;
            var id = DapperContext.GetDatabase().Insert(instance);
            if (id > 0)
            {
                var list = new List<CustomeTimeEnergy>();
                try
                {
                    // 从URL下载Excel文件到字节数组，旧版本从下载链接直接读取，但是网络权限网关限制不允许外网读取，修改为直接读取文件
                    //byte[] fileBytes = await HttpClientHelper.DownloadFileAsync(instance.Url);

                    var filePath = $"/upload/";
                    var fileName = instance.NewFileName;
                    byte[] fileBytes = new byte[0];
                    try
                    {
                        fileBytes = System.IO.File.ReadAllBytes(_hostingEnvironment.WebRootPath + filePath + fileName);
                    }
                    catch (Exception ex1)
                    {
                        LogHelper.WriteInfo("读取文件异常，" + ex1);
                        throw;
                    }


                    // 使用NPOI解析Excel文件
                    using (MemoryStream memoryStream = new MemoryStream(fileBytes))
                    {
                        IWorkbook workbook = null;
                        // 确定文件格式并创建相应的工作簿对象
                        try
                        {
                            if (instance.Url.EndsWith(".xlsx")) workbook = new XSSFWorkbook(memoryStream);
                            else if (instance.Url.EndsWith(".xls")) workbook = new HSSFWorkbook(memoryStream);
                        }
                        catch (Exception exx)
                        {
                            LogHelper.WriteInfo($"读取到workbook异常={exx}");
                        }
                        if (workbook != null)
                        {
                            ISheet sheet = workbook.GetSheetAt(0);
                            foreach (IRow row in sheet)
                            {
                                var rowNum = row.RowNum; // 使用 RowNum 属性获取行号
                                var deviceName = row.Cells[0].StringCellValue;
                                var startTime = row.Cells[1].DateCellValue;
                                var endTime = row.Cells[3].DateCellValue;
                                if (deviceName.IsNullOrEmpty() || startTime.HasValue == false || endTime.HasValue == false) continue;
                                list.Add(new CustomeTimeEnergy()
                                {
                                    StationId = instance.StationId,
                                    UploadFileId = (int)id,
                                    RowNumber = rowNum,
                                    ClassificationSubitemId = 0,
                                    ClassificationSubitemName = deviceName,
                                    StartTime = startTime,
                                    EndTime = endTime,
                                    CollectTime = startTime.Value.Date,
                                    IsStatistic = 0,
                                    DateCode = startTime.Value.ToDateCode(),
                                    RecordTime = instance.UploadTime,
                                });
                            }
                        }
                        workbook.Close();
                    }
                    CustomeTimeEnergyRepository.BatchInsert(list);
                }
                catch (Exception ex)
                {
                    DoFail($"保存失败:{ex.Message}");
                }

                return DoSuccess();
            }
            return DoFail("保存失败");
        }