ASP.NET PB is a practical learning hub for ASP.NET Core, C#, .NET Web API, SQL Server, Entity Framework Core, and AI development. Explore step-by-step tutorials, real-world coding examples, debugging solutions, performance optimization, and deployment guides. Learn to build secure, scalable, modern applications with clear explanations, working code, and developer-friendly best practices.
How to Build an Excel Export and Import API in ASP.NET Core 10 Using ClosedXML (2026)
An ASP.NET Core Excel import and export API should give users a predictable workbook and reject bad data before it reaches the database. In this walkthrough, ClosedXML generates a two-column inventory template. The import checks that the upload is a bounded XLSX archive, validates each SKU and quantity, and saves all accepted rows in one SQL Server transaction.
The API bounds workbook size and validates cells before opening a SQL transaction for accepted rows.
This is an insert-only example. An existing SKU fails the batch instead of changing stock silently. That makes the sample suitable for onboarding a small inventory catalog; a replenishment workflow needs a different business rule, such as replacing an absolute quantity or recording an adjustment.
Set up the project and database
Install the .NET 10 SDK and create an empty ASP.NET Core project. Start a SQL Server instance you can reach from the app. Use the schema below in a database named InventoryDb.
BASH
dotnet new web -f net10.0 -n InventoryWorkbook
cd InventoryWorkbook
dotnet add package ClosedXML --version '0.*'
dotnet add package Microsoft.Data.SqlClient --version '6.1.*'
dotnet user-secrets init
dotnet user-secrets set ConnectionStrings:Inventory 'Server=localhost;Database=InventoryDb;Integrated Security=true;Encrypt=true;TrustServerCertificate=false'
The example connection uses integrated authentication. Adjust it to your environment and install a trusted certificate on the server. Store a production connection string in the deployment secret store as ConnectionStrings__Inventory. Use a separate migration identity for DDL and grant the API identity only the database operations it needs.
schema.sql
CREATE TABLE dbo.StockWorkbook (
Sku varchar(40) NOT NULL CONSTRAINT PK_StockWorkbook PRIMARY KEY,
Quantity int NOT NULL CHECK (Quantity BETWEEN 0 AND 1000000)
);
Generate the template and validate the import
The XLSX format wraps worksheet XML and other parts in a ZIP archive. A 2 MB request limit controls the bytes sent over the network; it does not bound how large those parts become when expanded. The sample also checks the ZIP entry count and declared uncompressed size, then limits data rows to 500. ZIP metadata is attacker-controlled, so these checks are an early filter, not a replacement for sandboxing or resource controls when accepting untrusted workbooks.
The importer accepts one named sheet, verifies literal headers, normalizes SKUs to uppercase and rejects repeated keys. Quantity must be a numeric cell containing a whole number from zero to one million. Formula cells in the import columns are rejected even when Excel displays a valid cached value. This keeps the imported value independent of Excel’s calculation state. Common macro and external-link parts are blocked before opening the workbook; this is not a complete malicious-file detector.
Program.cs
using System.Data;
using System.IO.Compression;
using ClosedXML.Excel;
using Microsoft.Data.SqlClient;
var b = WebApplication.CreateBuilder(args);
b.WebHost.ConfigureKestrel(o => o.Limits.MaxRequestBodySize = 2_000_000);
var app = b.Build();
app.MapGet("/inventory/template", () =>
{
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Inventory");
ws.Cell("A1").Value = "SKU";
ws.Cell("B1").Value = "Quantity";
ws.Cell("A2").Value = "MUG-1";
ws.Cell("B2").Value = 12;
ws.Range("A1:B2").CreateTable("ImportRows");
ws.Column(1).Width = 24;
ws.Column(2).Width = 14;
using var output = new MemoryStream();
wb.SaveAs(output);
return Results.File(output.ToArray(),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "inventory.xlsx");
});
app.MapPost("/inventory/import", async (HttpRequest request, IConfiguration config, CancellationToken ct) =>
{
const string mime = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (!string.Equals(request.ContentType?.Split(';')[0].Trim(), mime, StringComparison.OrdinalIgnoreCase))
return Results.StatusCode(415);
using var input = new MemoryStream();
await request.Body.CopyToAsync(input, ct);
var result = ReadRows(input);
if (result.Error is not null) return Results.BadRequest(new { error = result.Error });
await using var db = new SqlConnection(config.GetConnectionString("Inventory")
?? throw new InvalidOperationException("Set ConnectionStrings__Inventory."));
await db.OpenAsync(ct);
await using var tx = (SqlTransaction)await db.BeginTransactionAsync(ct);
using var cmd = db.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = "INSERT dbo.StockWorkbook(Sku, Quantity) VALUES (@sku,@qty);";
var sku = cmd.Parameters.Add("@sku", SqlDbType.VarChar, 40);
var qty = cmd.Parameters.Add("@qty", SqlDbType.Int);
try
{
foreach (var row in result.Rows)
{
sku.Value = row.Sku;
qty.Value = row.Quantity;
await cmd.ExecuteNonQueryAsync(ct);
}
await tx.CommitAsync(ct);
return Results.Ok(new { inserted = result.Rows.Count });
}
catch (SqlException e) when (e.Number is 2601 or 2627)
{
return Results.Conflict(new { error = "A SKU already exists. The import was rolled back." });
}
});
app.Run();
static (List<StockRow> Rows, string? Error) ReadRows(MemoryStream input)
{
var rows = new List<StockRow>();
try
{
input.Position = 0;
using (var zip = new ZipArchive(input, ZipArchiveMode.Read, leaveOpen: true))
{
if (zip.Entries.Count > 150 || zip.Entries.Sum(e => e.Length) > 15_000_000)
return (rows, "Workbook exceeds expansion limits.");
if (zip.Entries.Any(e => e.FullName.StartsWith("xl/externalLinks/",
StringComparison.OrdinalIgnoreCase) ||
e.FullName.EndsWith("vbaProject.bin", StringComparison.OrdinalIgnoreCase)))
return (rows, "External links and macros are not supported.");
}
input.Position = 0;
using var wb = new XLWorkbook(input);
if (!wb.Worksheets.TryGetWorksheet("Inventory", out var ws))
return (rows, "Expected Inventory worksheet.");
var last = ws.LastRowUsed()?.RowNumber() ?? 0;
if (last is < 2 or > 501 || ws.Cell("A1").HasFormula || ws.Cell("B1").HasFormula ||
ws.Cell("A1").GetString() != "SKU" || ws.Cell("B1").GetString() != "Quantity")
return (rows, "Expected two headers and 1 to 500 rows.");
var seen = new HashSet<string>(StringComparer.Ordinal);
for (var i = 2; i <= last; i++)
{
var a = ws.Cell(i, 1);
var b = ws.Cell(i, 2);
if (a.HasFormula || b.HasFormula) return (rows, $"Row {i}: formulas are not accepted.");
var key = a.GetString().Trim().ToUpperInvariant();
if (a.DataType != XLDataType.Text || key.Length is < 1 or > 40 ||
key.Any(c => !(char.IsAsciiLetterOrDigit(c) || c == '-')) || !seen.Add(key) ||
b.DataType != XLDataType.Number || !b.TryGetValue<double>(out var n) ||
n < 0 || n > 1_000_000 || n != Math.Truncate(n))
return (rows, $"Row {i}: invalid or duplicate SKU or quantity.");
rows.Add(new StockRow(key, (int)n));
}
return (rows, null);
}
catch (Exception e) when (e is InvalidDataException or ArgumentException or FormatException
or IOException or System.Xml.XmlException or KeyNotFoundException or DocumentFormat.OpenXml.Packaging.OpenXmlPackageException)
{
return (rows, "Invalid or unsupported workbook.");
}
}
record StockRow(string Sku, int Quantity);
The export builds a small workbook in memory and returns the correct spreadsheet media type. Add worksheet columns deliberately; do not assign untrusted input to a formula property. Import uses the same shape as the template and accepts a raw XLSX body, not multipart form data. The Content-Type check rejects unrelated requests early; it does not prove that the content is a valid spreadsheet.
Test the round trip
Start the app at http://localhost:5085. Download the template, then upload it unchanged against an empty table.
The expected first response is 200 with an inserted count of one. Uploading the same workbook again should return 409. To check rollback, submit a workbook with a fresh SKU on row 2 and an existing one on row 3, then verify that neither new write was committed. Try a formula in the quantity column, the string “12” instead of a number, a duplicate SKU, or a renamed sheet; each should return 400 without reaching SQL.
In Postman, choose Body → binary, select the workbook, and set the spreadsheet Content-Type header. An HTML error response or 413 can originate at the reverse proxy before ASP.NET Core sees the upload. Configure request limits at both layers.
Troubleshooting and production safeguards
415: A multipart upload is being sent to a raw-body endpoint, or the media type is wrong.
400 on a valid-looking worksheet: Check the sheet name, header case, formula flags and actual cell types. Excel’s display formatting can make text look like a number.
409: A SKU already exists. Correct the file or design an explicit update mode with audit and conflict rules.
Slow imports: The workbook is parsed in memory and each row creates a SQL command round trip. For larger batches, stage validated data and benchmark a bulk-copy or set-based commit.
Large concurrent uploads: Require authentication and import authorization, apply rate and concurrency limits, use quotas and move workbook parsing to a constrained worker. Avoid logging workbook data or sending raw parser errors to the client.
Frequently asked questions
Is ClosedXML suitable for large files?
It is a good fit for modest workbooks with formatting and typed cells. ClosedXML constructs an in-memory workbook. For very large datasets, consider streaming CSV or an Open XML reader/writer designed to process rows without loading the whole workbook.
Does this endpoint update existing stock?
No. The transaction inserts new stock records only. A database unique constraint enforces that rule even if simultaneous requests contain the same SKU.
Can the export contain real inventory?
Yes. Query a tenant-scoped, authorized projection and cap the number of rows before generating a workbook. The public template route here contains sample data only; the sample import route needs authorization before deployment.
Comments
Post a Comment