An Excel export can open without an error and still be wrong. An identifier loses its leading zeros. A date sorts as text. A long reference number comes back with different digits at the end. The download succeeded, but the person using it has a problem.
I wrote about exporting MySafeInfo data to Excel in C# back in 2016. That example used ASP.NET Web Forms and EPPlus. For this one, I wanted a complete, current example with explicit decisions about what goes into each cell.
The source is the States dataset from MySafeInfo, the reference-data service I built. It is available in full without an account or API key, so you can run this example as written. The workbook uses ClosedXML, an MIT-licensed library that creates real .xlsx files without requiring Excel to be installed.
The important choices are small enough to explain before the code:
- Identifiers are text. The source ID identifies a record; nobody needs to add the IDs together. Names, abbreviations, and capitals are text too.
- The source's
Statehood value is a numeric year. It stays a number. Turning 1819 into January 1, 1819 would invent information the API did not provide.
- The retrieval timestamp is an Excel date value with a display format. Its label explicitly says UTC, because the spreadsheet cell does not retain a time-zone offset.
- An empty capital becomes a blank cell. A zero in the source year remains zero. Those are different values, and the exporter should not quietly decide they mean the same thing.
At the time of testing, the States endpoint returned 56 records, including territories and Washington, D.C. The code exports the records it receives; it does not assume this is a list of exactly 50 states or reinterpret the source's year values.
This is a .NET 10 console application using ClosedXML 0.105.1, the version tested here. Create the project and add the package:
dotnet new console --name MySafeInfoExcel --framework net10.0
cd MySafeInfoExcel
dotnet add package ClosedXML --version 0.105.1
Replace Program.cs with the following. It fetches the data, validates the fields, and writes two worksheets: the records themselves and a short record of where and when the data was retrieved.
using System.Globalization;
using System.Net.Http.Headers;
using System.Text.Json;
using System.Text.Json.Serialization;
using ClosedXML.Excel;
try
{
if (args.Length > 1)
throw new ArgumentException("Usage: dotnet run -- [output.xlsx]");
// One client for this one-shot console application.
using var http = new HttpClient(new HttpClientHandler
{
AllowAutoRedirect = false
})
{
Timeout = TimeSpan.FromSeconds(30),
MaxResponseContentBufferSize = 1_048_576
};
http.DefaultRequestHeaders.Accept.ParseAdd("application/json");
http.DefaultRequestHeaders.UserAgent.ParseAdd("MySafeInfoExcelExample/1.0");
// Optional: States is available in full without a key.
string? key = Environment.GetEnvironmentVariable("MYSAFEINFO_API_KEY");
if (!string.IsNullOrWhiteSpace(key))
http.DefaultRequestHeaders.Authorization =
new AuthenticationHeaderValue("Bearer", key.Trim());
string path = Path.GetFullPath(args.Length == 1 ? args[0] : "states.xlsx");
int count = await StateExport.RunAsync(http, path);
Console.WriteLine($"Wrote {count} records to {path}");
return 0;
}
catch (Exception ex) when (ex is HttpRequestException or JsonException
or IOException or UnauthorizedAccessException or ArgumentException
or InvalidDataException or FormatException or OperationCanceledException)
{
Console.Error.WriteLine($"Export failed: {ex.Message}");
return 1;
}
public static class StateExport
{
public const string SourceUrl =
"https://mysafeinfo.com/api/data/states?format=json&full=true";
public const int MaxRows = 10_000;
public static async Task<int> RunAsync(HttpClient http, string outputPath,
CancellationToken cancellationToken = default)
{
string path = Path.GetFullPath(outputPath);
if (!string.Equals(Path.GetExtension(path), ".xlsx",
StringComparison.OrdinalIgnoreCase))
throw new ArgumentException("The output file must end in .xlsx.");
if (File.Exists(path))
throw new IOException("The output file already exists. Choose a new name.");
// Buffering keeps the client's response-size limit in effect.
using var response = await http.GetAsync(SourceUrl,
HttpCompletionOption.ResponseContentRead, cancellationToken);
response.EnsureSuccessStatusCode();
await using var json = await response.Content.ReadAsStreamAsync(cancellationToken);
var rows = await JsonSerializer.DeserializeAsync<List<StateRow>>(
json, new JsonSerializerOptions { RespectNullableAnnotations = true },
cancellationToken) ?? throw new InvalidDataException("The API returned null.");
Validate(rows);
DateTimeOffset retrievedUtc = DateTimeOffset.UtcNow;
// Finish the workbook before giving it its final name.
string temporary = Path.Combine(Path.GetDirectoryName(path)!,
$".{Guid.NewGuid():N}.tmp");
try
{
using (var file = new FileStream(temporary, FileMode.CreateNew,
FileAccess.ReadWrite, FileShare.None))
{
WriteWorkbook(rows, file, retrievedUtc);
}
cancellationToken.ThrowIfCancellationRequested();
File.Move(temporary, path); // Does not overwrite an existing file.
}
finally
{
if (File.Exists(temporary))
File.Delete(temporary);
}
return rows.Count;
}
public static void WriteWorkbook(IReadOnlyList<StateRow> rows,
Stream output, DateTimeOffset retrievedUtc)
{
Validate(rows);
using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("States");
string[] headers = ["Source ID", "State / territory", "Abbreviation",
"Capital", "Statehood (source year)"];
for (int column = 0; column < headers.Length; column++)
sheet.Cell(1, column + 1).Value = headers[column];
for (int index = 0; index < rows.Count; index++)
{
var item = rows[index];
int row = index + 2;
sheet.Cell(row, 1).Value = item.Id.ToString(CultureInfo.InvariantCulture);
// Rich text preserves literal leading apostrophes in source strings.
sheet.Cell(row, 2).GetRichText().AddText(item.StateName);
sheet.Cell(row, 3).GetRichText().AddText(item.Abbreviation);
if (item.Capital.Length > 0)
sheet.Cell(row, 4).GetRichText().AddText(item.Capital);
sheet.Cell(row, 5).Value = item.Statehood;
}
sheet.Range(2, 1, rows.Count + 1, 1).Style.NumberFormat.Format = "@";
sheet.Range(2, 5, rows.Count + 1, 5).Style.NumberFormat.Format = "0";
sheet.Range(1, 1, rows.Count + 1, 5).CreateTable("StatesTable");
sheet.SheetView.FreezeRows(1);
sheet.Column(1).Width = 12;
sheet.Column(2).Width = 30;
sheet.Column(3).Width = 16;
sheet.Column(4).Width = 26;
sheet.Column(5).Width = 26;
var info = workbook.Worksheets.Add("Export info");
info.Cell("A1").Value = "Source";
info.Cell("B1").Value = SourceUrl;
info.Cell("A2").Value = "Retrieved (UTC)";
info.Cell("B2").Value = retrievedUtc.UtcDateTime;
info.Cell("B2").Style.NumberFormat.Format = "yyyy-mm-dd hh:mm:ss";
info.Cell("A3").Value = "Records";
info.Cell("B3").Value = rows.Count;
info.Cell("A4").Value = "Scope";
info.Cell("B4").Value = "Includes territories and Washington, D.C.";
info.Cell("A5").Value = "Year values";
info.Cell("B5").Value = "Copied from the source, including zero; not full dates.";
info.Column(1).Width = 22;
info.Column(2).Width = 80;
info.Range("A1:A5").Style.Font.Bold = true;
workbook.SaveAs(output);
}
private static void Validate(IReadOnlyList<StateRow> rows)
{
if (rows.Count is 0 or > MaxRows)
throw new InvalidDataException($"Expected 1 to {MaxRows} records.");
var ids = new HashSet<int>();
foreach (var item in rows)
{
if (item is null || item.Id <= 0 || !ids.Add(item.Id)
|| string.IsNullOrWhiteSpace(item.StateName)
|| string.IsNullOrWhiteSpace(item.Abbreviation)
|| item.Capital is null || item.Statehood is < 0 or > 9999
|| item.StateName.Length > 32_767
|| item.Abbreviation.Length > 32_767
|| item.Capital.Length > 32_767)
throw new InvalidDataException("Unexpected States data; no export was saved.");
}
}
}
public sealed record StateRow
{
[JsonPropertyName("ID")]
public required int Id { get; init; }
public required string StateName { get; init; }
public required string Abbreviation { get; init; }
public required string Capital { get; init; }
public required int Statehood { get; init; }
}
Run it with an output filename:
dotnet run -- states.xlsx
The destination directory must already exist. If the file exists, the program refuses to overwrite it; use another filename. A failed request or invalid response produces an error and a nonzero exit code. The final filename is created only after the workbook has been saved successfully.
No key is needed for this dataset. If you have a pass, the optional MYSAFEINFO_API_KEY environment variable sends your key in the authorization header. Keep it out of source code and URLs. The request also uses full=true, which asks MySafeInfo to fail rather than silently return a sample when full access requires a key. Other datasets have different fields and need their own model and column mapping; changing the URL alone is not enough. The API documentation covers access and request limits.
The States IDs do not contain leading zeros. To see why the text decision matters elsewhere, this small, separate workbook uses a postal code and a long reference number:
using ClosedXML.Excel;
using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Text examples");
sheet.Cell("A1").Value = "Postal code";
sheet.Cell("A2").Value = "00501";
sheet.Cell("B1").Value = "Reference";
sheet.Cell("B2").Value = "123456789012345678";
sheet.Range("A2:B2").Style.NumberFormat.Format = "@";
workbook.SaveAs("text-examples.xlsx");
Run that separately, with an unused filename. Both values are assigned as strings. The text format makes the intent explicit, but formatting is not what preserves the original characters. If the application already converted 00501 to the number 501, changing the cell's format cannot recover what was lost. Excel also has a 15-digit limit on numeric precision, so long identifiers belong in text cells.
Dates work the other way around. The export assigns a DateTime to the timestamp cell, then gives it the format yyyy-mm-dd hh:mm:ss. It does not call ToString() and hand Excel a date-shaped piece of text. As the ClosedXML formatting documentation explains, a number format changes presentation, not the underlying value.
Testing caught another detail worth keeping: assigning a string through Value can treat a leading apostrophe as Excel's text prefix. The main export uses GetRichText().AddText() for the API's text fields so a literal apostrophe survives. The tests also check that strings beginning with =, +, -, and @ remain text. Imported text never gets assigned to FormulaA1.
The verification went beyond checking that a file appeared. The final example was run against the live API without a key, the saved workbook was reopened, and its structure was checked with the Open XML SDK validator. Automated cases covered:
- Text and numeric cell types, leading zeros, an 18-digit reference, Unicode, literal apostrophes, and formula-like text.
- UTC timestamp conversion and results under U.S., German, and French culture settings.
- Missing fields, nulls, duplicate IDs, invalid JSON, oversized responses, and too many records.
- HTTP errors, including 429, cancellation, timeouts, and protecting an existing destination file, including one created during the request.
This is deliberately a bounded export. The HTTP response is capped at one MiB, the request times out after 30 seconds, and validation rejects more than 10,000 rows. Those are example limits, not promises about how much memory a workbook will use. ClosedXML builds the workbook in memory. For a large export or a web application serving concurrent downloads, measure the workload and choose an appropriate background-job or streaming design. Reuse or manage HttpClient through the application's normal lifetime management. The single client here lives for the duration of this console run.
The example also stops on a rate-limit response instead of repeatedly calling the API. A scheduled integration needs a retry policy that respects Retry-After, along with whatever monitoring the job requires.
The next time you check an export, try doing something with it. Sort the dates. Filter a numeric column. Read an identifier back and compare it with the source. Those checks tell you much more than whether Excel managed to open the file.