Crear una API de Excel para .NET Core: Crear, leer y exportar archivos XLSX

using IronXL;
using Microsoft.AspNetCore.Mvc;
[ApiController]
[Route("api/[controller]")]
public class ExcelController : ControllerBase
{
    [HttpGet("create-report")]
    public IActionResult CreateSalesReport()
    {
        // Create a new Excel workbook
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        WorkSheet worksheet = workbook.CreateWorkSheet("Sales Data");
        // Set headers with cell styles
        worksheet["A1"].Value = "Product";
        worksheet["B1"].Value = "Q1 Sales";
        worksheet["C1"].Value = "Q2 Sales";
        worksheet["D1"].Value = "Total";
        // Apply bold formatting to header row
        worksheet["A1:D1"].Style.Font.Bold = true;
        worksheet["A1:D1"].Style.SetBackgroundColor("#4472C4");
        worksheet["A1:D1"].Style.Font.SetColor("#FFFFFF");
        // Add Excel data with number formats
        worksheet["A2"].Value = "Widget Pro";
        worksheet["B2"].Value = 15000;
        worksheet["C2"].Value = 18500;
        worksheet["D2"].Formula = "=B2+C2";
        worksheet["A3"].Value = "Gadget Plus";
        worksheet["B3"].Value = 22000;
        worksheet["C3"].Value = 24000;
        worksheet["D3"].Formula = "=B3+C3";
        // Apply currency number formats to sales columns
        worksheet["B2:D3"].Style.Format = "$#,##0";
        // Save and return the XLSX file
        var stream = workbook.ToStream();
        return File(stream,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "SalesReport.xlsx");
    }
}

Imports IronXL
Imports Microsoft.AspNetCore.Mvc

<ApiController>
<Route("api/[controller]")>
Public Class ExcelController
    Inherits ControllerBase

    <HttpGet("create-report")>
    Public Function CreateSalesReport() As IActionResult
        ' Create a new Excel workbook
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Sales Data")
        ' Set headers with cell styles
        worksheet("A1").Value = "Product"
        worksheet("B1").Value = "Q1 Sales"
        worksheet("C1").Value = "Q2 Sales"
        worksheet("D1").Value = "Total"
        ' Apply bold formatting to header row
        worksheet("A1:D1").Style.Font.Bold = True
        worksheet("A1:D1").Style.SetBackgroundColor("#4472C4")
        worksheet("A1:D1").Style.Font.SetColor("#FFFFFF")
        ' Add Excel data with number formats
        worksheet("A2").Value = "Widget Pro"
        worksheet("B2").Value = 15000
        worksheet("C2").Value = 18500
        worksheet("D2").Formula = "=B2+C2"
        worksheet("A3").Value = "Gadget Plus"
        worksheet("B3").Value = 22000
        worksheet("C3").Value = 24000
        worksheet("D3").Formula = "=B3+C3"
        ' Apply currency number formats to sales columns
        worksheet("B2:D3").Style.Format = "$#,##0"
        ' Save and return the XLSX file
        Dim stream = workbook.ToStream()
        Return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "SalesReport.xlsx")
    End Function
End Class

[HttpPost("import")]
public IActionResult ImportExcelData(IFormFile file)
{
    if (file == null || file.Length == 0)
        return BadRequest("No file uploaded");
    using var stream = file.OpenReadStream();
    WorkBook workbook = WorkBook.Load(stream);
    WorkSheet worksheet = workbook.DefaultWorkSheet;
    var records = new List<Dictionary<string, object>>();
    // Read cell range starting from row 2 (skip headers)
    for (int row = 2; row <= worksheet.RowCount; row++)
    {
        var record = new Dictionary<string, object>
        {
            ["Product"] = worksheet[$"A{row}"].StringValue,
            ["Sales"] = worksheet[$"B{row}"].DecimalValue,
            ["Date"] = worksheet[$"C{row}"].DateTimeValue
        };
        records.Add(record);
    }
    return Ok(new {
        message = "Import successful",
        recordCount = records.Count,
        data = records
    });
}

Imports Microsoft.AspNetCore.Mvc
Imports System.Collections.Generic
Imports IronXL

<HttpPost("import")>
Public Function ImportExcelData(file As IFormFile) As IActionResult
    If file Is Nothing OrElse file.Length = 0 Then
        Return BadRequest("No file uploaded")
    End If

    Using stream = file.OpenReadStream()
        Dim workbook As WorkBook = WorkBook.Load(stream)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        Dim records As New List(Of Dictionary(Of String, Object))()

        ' Read cell range starting from row 2 (skip headers)
        For row As Integer = 2 To worksheet.RowCount
            Dim record As New Dictionary(Of String, Object) From {
                {"Product", worksheet($"A{row}").StringValue},
                {"Sales", worksheet($"B{row}").DecimalValue},
                {"Date", worksheet($"C{row}").DateTimeValue}
            }
            records.Add(record)
        Next

        Return Ok(New With {
            .message = "Import successful",
            .recordCount = records.Count,
            .data = records
        })
    End Using
End Function

[HttpGet("export/{format}")]
public IActionResult ExportData(string format)
{
    WorkBook workbook = WorkBook.Load("template.xlsx");
    return format.ToLower() switch
    {
        "xlsx" => File(workbook.ToStream(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "export.xlsx"),
        "csv" => File(workbook.ToStream(FileFormat.CSV),
            "text/csv",
            "export.csv"),
        "json" => Ok(workbook.ToJson()),
        _ => BadRequest("Unsupported format")
    };
}

Imports Microsoft.AspNetCore.Mvc

<HttpGet("export/{format}")>
Public Function ExportData(format As String) As IActionResult
    Dim workbook As WorkBook = WorkBook.Load("template.xlsx")
    Select Case format.ToLower()
        Case "xlsx"
            Return File(workbook.ToStream(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx")
        Case "csv"
            Return File(workbook.ToStream(FileFormat.CSV), "text/csv", "export.csv")
        Case "json"
            Return Ok(workbook.ToJson())
        Case Else
            Return BadRequest("Unsupported format")
    End Select
End Function