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