你是一个在C#开发领域有着丰富经验的开发工程师，采用以下结构导出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 {
const response = await proxy.$api.request({
url: "/vehicleReport/ExportExcel",
method: "POST",
data: params,
responseType: 'blob', // 确保设置为 blob
headers: {
'Content-Type': 'application/json; charset=UTF-8' // 明确指定编码
},
success(response) {
console.log("内请求参数:", response.data);
console.log("文件大小:", response.data.byteLength);
// 隐藏加载动画
if (proxy && proxy.$loading) {
proxy.$loading.hide();
}
// console.log("请求参数:", response.data);
// console.log("请求参数:", params);
// 检查 response 是否有效
if (!response || !response.data) {
throw new Error("服务器未返回有效数据");
}
// 处理 content-type（兼容数组情况）
const contentType = response.headers['content-type'] || "application/octet-stream";
const blob = new Blob([response.data], { type: contentType });
const url = window.URL.createObjectURL(blob);
// 获取本地日期
const today = new Date();
const dateStr = `${today.getFullYear()}-${String(today.getMonth() + 1).padStart(2, '0')}-${String(today.getDate()).padStart(2, '0')}`;
// 创建下载链接
const link = document.createElement("a");
link.href = url;
link.download = `车辆报告_${dateStr}.${params.Format}`;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
// 释放 URL 对象
window.URL.revokeObjectURL(url);
}
});
} catch (error) {
console.error("导出失败:", error.message || error);
proxy.$message.error("导出失败，请检查网络或联系管理员！");
} 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
本文介绍了如何使用Aspose.Cells库来实现Excel表格的冻结窗口功能及设置单元格背景颜色的方法。通过具体的代码示例，展示了如何冻结指定的行和列，并为特定行设置统一的背景色。
扫一扫
1730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
