流对象列表作为来自 API 的文件 [英] Stream Object list as file from API
问题描述
API 端点是否可以从实体框架填充的 IQueryable 对象列表中流式传输数据,并链接到 SQL 数据库以创建和返回 csv 文件?
Is it possible for a API endpoint to stream data from an IQueryable object list populated from entity framework, and linked to SQL database to create and return a csv file?
到目前为止,我循环遍历列表中的所有项目并创建一个临时文件.然后,作为 GET API 调用的结果,我将该文件流式传输.
What I have so far is I loop through all the items in my list and create a temporary file. I then stream that file as the result of my GET API call.
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 文件,但我在想是否可以将数据直接写入输出流而不是创建临时文件?我不想将所有项目都加载到内存中,因为我想在大型数据集上使用此方法以允许用户下载表数据.
This works fine and I get a csv file back, but I was thinking is it possible to write the data straight to the output stream rather than creating a temp file? I don't want to load all the items in to memory as I would like to use this method on large data sets to allow users to download the table data.
推荐答案
您可以添加 EPPlus
包.下面的代码我已经测试过了,它对我有用.
You can add EPPlus
package. below code I have tested, it works for me.
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; }
}
}
这篇关于流对象列表作为来自 API 的文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!