你是一个在C#开发领域有着丰富经验的开发工程师,现在请围绕vue,net,controller,service,采用以下结构导出excel或者csv,将以下代码进行优化,能导出excel,并且能支持高性能,还能异步处理:
前端代码:
const exportExcel = async () => {
const params = {
StartTime: datas.dateRange[0],
EndTime: datas.dateRange[1],
ReportType: datas.reportType,
ReportStatus: datas.reportStatus,
Keyword: datas.keyword,
Format: "xlsx" ,// 支持 csv 或 xlsx
};
// 显示加载动画
if (proxy && proxy.$loading) proxy.$loading.show();
datas.loading = true;
try {
// 1. 添加请求日志
console.log("导出请求参数:", JSON.stringify(params, null, 2));
const response = await proxy.$api.request({
url: "/vehicleReport/ExportExcel",
method: "POST",
data: params,
responseType: 'blob',
headers: {
'Content-Type': 'application/json; charset=UTF-8',
'Authorization': `Bearer ${localStorage.getItem('token')}` // 确保权限
},
timeout: 30000 // 增加超时时间
});
// 2. 检查HTTP状态码
if (response.status !== 200) {
// 尝试解析错误信息
let errorMsg = `服务器错误 (${response.status})`;
try {
const text = await new Response(response.data).text();
if (text) {
try {
const json = JSON.parse(text);
errorMsg = json.message || json.error || text;
} catch {
errorMsg = text;
}
}
} catch {}
throw new Error(errorMsg);
}
// 3. 文件处理
const contentType = response.headers['content-type'] ||
(params.Format === 'csv' ?
'text/csv' :
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
const blob = new Blob([response.data], {
type: contentType
});
// 4. 验证Blob
if (blob.size === 0) {
throw new Error("接收到的文件内容为空");
}
// 5. 创建下载链接
const url = window.URL.createObjectURL(blob);
const link = document.createElement("a");
link.href = url;
link.download = `车辆报告_${new Date().toISOString().slice(0, 10)}.${params.Format}`;
// 6. 添加超时处理
const downloadTimeout = setTimeout(() => {
proxy.$message.warning("下载处理时间较长,请稍候...");
}, 5000);
link.onclick = () => clearTimeout(downloadTimeout);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
// 7. 释放资源
setTimeout(() => {
window.URL.revokeObjectURL(url);
}, 1000);
} catch (error) {
console.error("导出失败详情:", error);
// 8. 显示更友好的错误信息
let userMessage = "导出失败";
if (error.message.includes("401")) {
userMessage = "登录已过期,请重新登录";
} else if (error.message.includes("500")) {
userMessage = "服务器处理失败";
} else if (error.message.includes("空")) {
userMessage = "未获取到有效数据";
} else if (error.message.includes("超时")) {
userMessage = "请求超时,请稍后再试";
}
proxy.$message.error(`${userMessage}: ${error.message}`);
} finally {
// 统一隐藏 loading
if (proxy && proxy.$loading) proxy.$loading.hide();
datas.loading = false;
}
};
后端代码:
#region =========导出报告============
[HttpPost("/vehicleReport/ExportExcel")]
[Authorize]
public async Task<IActionResult> ExportExcel([FromBody] VehicleReportExportToExcelParameter searchParam)
{
try
{
// 参数验证
if (!searchParam.StartTime.HasValue || !searchParam.EndTime.HasValue)
{
return BadRequest(ResponseMessage.Error("时间范围不能为空"));
}
// 构建查询条件
Expression<Func<VehicleReports, bool>> filter = x =>
x.AddTime >= searchParam.StartTime.Value &&
x.AddTime <= searchParam.EndTime.Value;
if (!string.IsNullOrEmpty(searchParam.Keyword))
{
filter = filter.And(x => x.Vin.Contains(searchParam.Keyword) || x.UserName.Contains(searchParam.Keyword));
}
// 验证排序字段合法性
var allowedOrderFields = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
"AddTime", "Id"
};
string orderBy = searchParam.OrderBy ?? "AddTime,Id";
if (!allowedOrderFields.Contains(orderBy.Split(',')[0].Trim()))
{
return BadRequest(ResponseMessage.Error("不允许的排序字段"));
}
// 调用服务层方法获取数据
var list = await _vehicleReportService.ExportExcelAsync(
0, // 获取所有数据
filter,
orderBy);
if (list == null || !list.Any())
{
return NotFound(ResponseMessage.Error("暂无数据"));
}
// 生成 Excel 文件流
using var stream = new MemoryStream();
string format = searchParam.Format?.ToLower() ?? "xlsx";
if (format == "csv")
{
await GenerateCsvAsync(stream, list);
}
else
{
await GenerateXlsxAsync(stream, list);
}
// 重置流位置
stream.Position = 0;
string fileName = $"车辆报告_{DateTime.Now:yyyyMMddHHmmss}_{Guid.NewGuid():N}.{format}";
// 返回文件
return File(
stream.ToArray(),
format == "csv" ? "text/csv; charset=utf-8" : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileName
);
}
catch (Exception ex) when (ex is ArgumentException or InvalidOperationException)
{
logger.Trace(ex, "参数或操作异常");
return BadRequest(ResponseMessage.Error($"请求参数错误:{ex.Message}"));
}
catch (Exception ex)
{
logger.Trace(ex, "导出Excel失败");
return StatusCode(500, ResponseMessage.Error($"服务器内部错误:{ex.Message}"));
}
}
private async Task GenerateCsvAsync(MemoryStream stream, IEnumerable<VehicleReports> list)
{
// 设置 leaveOpen: true 确保流不被关闭
using (var writer = new StreamWriter(stream, Encoding.UTF8, leaveOpen: true))
{
// 写入表头
await writer.WriteLineAsync("报告编号,VIN码,用户名,报告类型,查询状态,创建日期,生效日期");
foreach (var item in list)
{
string EscapeCsv(string value)
{
if (string.IsNullOrEmpty(value)) return string.Empty;
if (value.StartsWith("=") || value.StartsWith("+") || value.StartsWith("-") || value.StartsWith("@"))
return " " + value; // 加空格防止公式注入
return value.Replace("\"", "\"\""); // 转义双引号
}
await writer.WriteLineAsync(
$"\"{EscapeCsv(item.ReportNo)}\"," +
$"\"{EscapeCsv(item.Vin)}\"," +
$"\"{EscapeCsv(item.UserName)}\"," +
$"\"{EscapeCsv(item.ReportType.ToString())}\"," +
$"\"{item.ReportStatus}\"," +
$"\"{item.AddTime:yyyy-MM-dd HH:mm:ss}\"," +
$"\"{item.ValidTime:yyyy-MM-dd HH:mm:ss}\"");
}
}
}
private void GenerateXlsx(XLWorkbook workbook, IEnumerable<VehicleReports> list)
{
var worksheet = workbook.Worksheets.Add("Reports");
// 添加表头
worksheet.Cell(1, 1).Value = "报告编号";
worksheet.Cell(1, 2).Value = "VIN码";
worksheet.Cell(1, 3).Value = "用户名";
worksheet.Cell(1, 4).Value = "报告类型";
worksheet.Cell(1, 5).Value = "查询状态";
worksheet.Cell(1, 6).Value = "创建日期";
worksheet.Cell(1, 7).Value = "生效日期";
int row = 2;
foreach (var item in list)
{
worksheet.Cell(row, 1).Value = item.ReportNo ?? string.Empty;
worksheet.Cell(row, 2).Value = item.Vin ?? string.Empty;
worksheet.Cell(row, 3).Value = item.UserName ?? string.Empty;
worksheet.Cell(row, 4).Value = item.ReportType.ToString() ?? string.Empty;
worksheet.Cell(row, 5).Value = item.ReportStatus;
worksheet.Cell(row, 6).Value = item.AddTime;
worksheet.Cell(row, 7).Value = item.ValidTime;
row++;
}
}
private async Task GenerateXlsxAsync(MemoryStream stream, IEnumerable<VehicleReports> list)
{
using (var workbook = new XLWorkbook())
{
GenerateXlsx(workbook, list);
workbook.SaveAs(stream);
}
}
#endregion
服务层代码:
#region ======== 导出报告Excel ================
/// <summary>
/// 查询指定数量列表
/// </summary>
public async Task<IEnumerable<VehicleReports>> ExportExcelAsync(int top, Expression<Func<VehicleReports, bool>> funcWhere,
string orderBy, WriteRoRead writeAndRead = WriteRoRead.Read)
{
// 参数校验
if (funcWhere == null) throw new ArgumentNullException(nameof(funcWhere));
if (string.IsNullOrWhiteSpace(orderBy)) throw new ArgumentException("排序字段不能为空", nameof(orderBy));
// 防止非法排序字段(简单白名单示例)
var allowedOrderFields = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
nameof(VehicleReports.Id),
nameof(VehicleReports.AddTime),
// 添加其他允许排序的字段
};
var orderFields = orderBy.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(f => f.Trim().Split(' ')[0].Trim());
foreach (var field in orderFields)
{
if (!allowedOrderFields.Contains(field))
{
throw new ArgumentException($"不允许按字段 [{field}] 排序");
}
}
using var context = _contextFactory.CreateContext(writeAndRead);
var result = context.Set<VehicleReports>()
.Where(funcWhere)
.OrderByBatch(orderBy); // 确保OrderByBatch支持EF Core翻译
if (top > 0)
{
result = result.Take(top);
}
return await result.ToListAsync();
}
#endregion
最新发布