c# - 流对象列表作为来自 API 的文件
问题描述
API 端点是否可以从实体框架填充的 IQueryable 对象列表中流式传输数据,并链接到 SQL 数据库以创建和返回 csv 文件?
到目前为止,我遍历列表中的所有项目并创建一个临时文件。然后,我将该文件作为我的 GET API 调用的结果进行流式传输。
Directory.CreateDirectory($"{Environment.CurrentDirectory}/TmpData");
string tmpFileName = $"{Environment.CurrentDirectory}/TmpData/{Guid.NewGuid().ToString()}.csv";
using (FileStream file = new FileStream(tmpFileName, FileMode.CreateNew))
{
using (StreamWriter fileStream = new StreamWriter(file))
{
LookupItem liTmp = new();
await fileStream.WriteAsync(nameof(liTmp.LookupItemId));
await fileStream.WriteAsync(",");
await fileStream.WriteAsync(nameof(liTmp.Code));
await fileStream.WriteAsync(",");
await fileStream.WriteAsync(nameof(liTmp.Label));
await fileStream.WriteLineAsync();
foreach (var li in items)
{
await fileStream.WriteAsync(li.LookupItemId.ToString());
await fileStream.WriteAsync(",");
await fileStream.WriteAsync(li.Code?.ToString());
await fileStream.WriteAsync(",");
await fileStream.WriteAsync(li.Label?.ToString());
await fileStream.WriteLineAsync();
}
}
}
this.Response.StatusCode = 200;
this.Response.Headers.Add(HeaderNames.ContentDisposition, $"attachment; filename=\"{ request.LookupTableType.ToString() } Data { DateTime.Now.ToString("yyyy-mm-dd hh-MM-ss")}.csv\"");
this.Response.Headers.Add(HeaderNames.ContentType, "application/octet-stream");
var inputStream = new FileStream(tmpFileName, FileMode.Open, FileAccess.Read);
var outputStream = this.Response.Body;
const int bufferSize = 1 << 10;
var buffer = new byte[bufferSize];
while (true)
{
var bytesRead = await inputStream.ReadAsync(buffer, 0, bufferSize);
if (bytesRead == 0) break;
await outputStream.WriteAsync(buffer, 0, bytesRead);
}
await outputStream.FlushAsync();
System.IO.File.Delete(tmpFileName);
return new EmptyResult();
这工作正常,我得到了一个 csv 文件,但我在想是否可以将数据直接写入输出流而不是创建一个临时文件?我不想将所有项目加载到内存中,因为我想在大型数据集上使用此方法以允许用户下载表数据。
解决方案
您可以添加EPPlus
包。下面我测试过的代码,它适用于我。
using dotnetcoreMVC.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace dotnetcoreMVC.Controllers
{
public class ForTestController : Controller
{
public IActionResult ExportData()
{
//TODO read data from db
// Mock data
List<TestModel> li = new List<TestModel>();
for (int i = 0; i < 10; i++)
{
TestModel m = new TestModel();
m.id = 1+i;
m.name = "test name"+ i;
li.Add(m);
}
var data = li;
if (data?.Any() != true)
{
return new ContentResult() { Content = "no data" };
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage())
{
using (var worksheet = ep.Workbook.Worksheets.Add("export data for test"))
{
var x = 1;
var y = 1;
var columnTitles = new List<string>()
{
"id",
"alias"
};
foreach (var columnTitle in columnTitles)
{
var cell = worksheet.Cells[x, y++];
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cell.Value = columnTitle;
}
foreach (var item in data)
{
x++;
y = 1;
var cell = worksheet.Cells[x, y++];
cell.Value = item.id;
cell = worksheet.Cells[x, y++];
cell.Value = item.name;
}
using (var stream = new MemoryStream())
{
ep.SaveAs(stream);
return new FileContentResult(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = Guid.NewGuid()+"test.csv"
};
}
}
}
}
}
public class TestModel {
public int id { get; set; }
public string name { get; set; }
}
}
推荐阅读
- javascript - 如何强制用户按顺序点击 div?
- segmentation-fault - google-assistant-demo 和 googlesamples-assistant-hotword 分段错误
- wordpress - Wordpress 如何使过滤器在 Wordpress API 中返回 author_name
- php - 作曲家更新最后说 Trait 'Carbon\Traits\Mixin' not found
- docker - 在 Dockerfile 基础映像中公开端口是否会在最终映像中公开它们?
- python - [[]]*X 不创建唯一数组
- postgresql - \COPY TO Postgres 语句在 bash 中工作,但不适用于 python psycopg2
- python - Python - 根据其他变量自动创建新变量
- c - 将内存分配给结构中的数组
- javascript - 在资源时间轴中拖动事件时的 FullCalendar 工具提示错误