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("保存失败");}