Generating Excel Files in C# without Excel: An Intro to Open XML

by Tom Vaidyanin .NET, C#, Microsoft, Web Developmenton Posted on

As a .NET developer, especially when building web APIs or backend services, you’ve likely encountered the need to generate Microsoft Excel (.xlsx) files programmatically. Maybe you need to export report data, provide users with downloadable templates, or integrate with systems that consume spreadsheet data.

Historically, one might have reached for Office COM Interop, but that requires having Microsoft Office installed on the server – a big no-no for scalability, licensing, and stability in web environments. Thankfully, there’s a much better way: the Open XML SDK.

What is Open XML?

The Office Open XML File Formats (commonly called Open XML) are the default file formats for Microsoft Office applications (Word, Excel, PowerPoint) since Office 2007. Under the hood, an .xlsx file (or .docx, .pptx) is essentially a ZIP archive containing multiple XML files and other resources (like images) that define the document’s structure and content.

This standardized, XML-based format means we can manipulate these files programmatically without relying on the Office applications themselves.

Why Use the Open XML SDK?

  1. No Server-Side Office Dependency: This is the biggest win. You don’t need Excel installed on your server, making it perfect for web applications, APIs, containers, and cloud environments.
  2. Performance: Generally faster and more lightweight than COM Interop.
  3. Fine-Grained Control: You have direct access to the underlying XML structure, allowing for precise control over every aspect of the document.
  4. Free and Open Standard: Open XML is an ECMA and ISO standard. The SDK is provided by Microsoft and is open-source.
  5. .NET Native: The SDK is designed specifically for .NET developers.

Getting Started: The Open XML SDK

The easiest way to work with Open XML in .NET is by using the official NuGet package provided by Microsoft.

dotnet add package DocumentFormat.OpenXml

Understanding the Basic Excel Structure (in Open XML terms)

Before diving into code, let’s grasp the key components within an .xlsx file that the SDK interacts with:

  1. SpreadsheetDocument: The main object representing the Excel file package (the zip archive).
  2. WorkbookPart: Contains the Workbook definition, which lists all the sheets and defines shared resources.
  3. WorksheetPart: Contains the actual data for a single sheet (Worksheet).
  4. Worksheet: Defines the structure of a sheet, including dimensions, columns, and a reference to the SheetData.
  5. SheetData: Contains the rows (Row) and cells (Cell) holding the actual data.
  6. Row: Represents a single row in the SheetData.
  7. Cell: Represents a single cell within a Row. Cells have types (e.g., number, string, boolean) and values.
  8. SharedStringTablePart (Optional but Recommended): To save space, especially with repeated text, Excel often stores unique strings in a central table. Cells then reference the index of the string in this table instead of storing the string directly.

Creating a Simple Excel File

Let’s create a basic spreadsheet with one sheet and a few cells containing text and numbers.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;

public class SimpleExcelGenerator
{
    public void CreateSimpleExcel(string filePath)
    {
        // Ensure the directory exists
        string? directory = Path.GetDirectoryName(filePath);
        if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
        {
            Directory.CreateDirectory(directory);
        }

        // Use SpreadsheetDocument.Create to make a new file
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            // 1. Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // 2. Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // 3. Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // 4. Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "My First Sheet" // Sheet name
            };
            sheets.Append(sheet);

            // Get the sheetData object for easy access.
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>() ?? worksheetPart.Worksheet.AppendChild(new SheetData());

            // 5. Add Rows and Cells

            // Row 1: Header
            Row headerRow = new Row() { RowIndex = 1 };
            headerRow.Append(
                CreateTextCell("A1", "Product Name"), // Column A, Row 1
                CreateTextCell("B1", "Quantity")     // Column B, Row 1
            );
            sheetData.Append(headerRow);

            // Row 2: Data
            Row dataRow1 = new Row() { RowIndex = 2 };
            dataRow1.Append(
                CreateTextCell("A2", "Widget"),
                CreateNumberCell("B2", "10")          // Numeric value
            );
            sheetData.Append(dataRow1);

             // Row 3: More Data
            Row dataRow2 = new Row() { RowIndex = 3 };
            dataRow2.Append(
                CreateTextCell("A3", "Gadget"),
                CreateNumberCell("B3", "25")
            );
            sheetData.Append(dataRow2);


            // 6. Save the workbook.
            workbookpart.Workbook.Save();
        } // The using statement automatically closes the document and saves the parts.

        Console.WriteLine($"Successfully created: {filePath}");
    }

    // Helper function to create a Cell with inline text (simplest for basic text)
    private Cell CreateTextCell(string cellReference, string text)
    {
        return new Cell()
        {
            CellReference = cellReference,
            // DataType = CellValues.InlineString, // Use InlineString for simple text without Shared Strings
            // InlineString = new InlineString() { Text = new Text(text) }
            // --- OR --- Forcing Excel to treat it as text even if it looks like a number
             DataType = CellValues.String, // Use String for explicit text type
             CellValue = new CellValue(text)
        };
    }

    // Helper function to create a Cell with a number
    private Cell CreateNumberCell(string cellReference, string value)
    {
        return new Cell()
        {
            CellReference = cellReference,
            DataType = CellValues.Number, // Explicitly set the type to Number
            CellValue = new CellValue(value) // Value is stored as text but interpreted as number by Excel
        };
    }
}

// --- How to use it: ---
// var generator = new SimpleExcelGenerator();
// generator.CreateSimpleExcel("C:\\temp\\SimpleReport.xlsx"); // Or use Path.Combine for better path handling

Explanation:

  1. We create the SpreadsheetDocument.
  2. We add the essential WorkbookPart and WorksheetPart.
  3. We define a Sheet in the Workbook and link it to our WorksheetPart.
  4. We get the SheetData element where rows will live.
  5. We create Row objects. It’s crucial to set the RowIndex.
  6. We create Cell objects within each row. We use helper functions CreateTextCell and CreateNumberCell.
    • CellReference (like “A1”, “B2”) tells Excel where the cell belongs.
    • DataType tells Excel how to interpret the value. CellValues.Number for numbers, CellValues.String for text. (We initially used InlineString, which is simple but less efficient for repeated text).
    • CellValue holds the actual data (as a string, even for numbers, but the DataType guides Excel).
  7. The using statement handles closing and saving the document correctly.

Run this code, and you’ll find a SimpleReport.xlsx file in the specified location!

Using Shared Strings (The Better Way for Text)

Storing every string directly inside its cell (like InlineString does, or even String when duplicated) can bloat the file size if you have lots of repeated text (e.g., status names, categories). The standard approach is to use a SharedStringTable.

Let’s modify the example to use shared strings.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

public class SharedStringExcelGenerator
{
    public void CreateExcelWithSharedStrings(string filePath)
    {
        // Ensure the directory exists
        string? directory = Path.GetDirectoryName(filePath);
        if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
        {
            Directory.CreateDirectory(directory);
        }

        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // *** Add SharedStringTablePart ***
            SharedStringTablePart sharedStringTablePart = workbookpart.AddNewPart<SharedStringTablePart>();
            sharedStringTablePart.SharedStringTable = new SharedStringTable();

            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Shared String Sheet"
            };
            sheets.Append(sheet);

            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>() ?? worksheetPart.Worksheet.AppendChild(new SheetData());

            // --- Data ---
            List<Tuple<string, int>> productData = new List<Tuple<string, int>>
            {
                Tuple.Create("Widget", 10),
                Tuple.Create("Gadget", 25),
                Tuple.Create("Doohickey", 15),
                Tuple.Create("Widget", 5) // Repeated product name
            };

            // --- Build Header Row ---
            Row headerRow = new Row() { RowIndex = 1 };
            headerRow.Append(
                CreateSharedStringCell("A1", "Product Name", sharedStringTablePart),
                CreateSharedStringCell("B1", "Quantity", sharedStringTablePart)
            );
            sheetData.Append(headerRow);

            // --- Build Data Rows ---
            uint rowIndex = 2; // Start data from row 2
            foreach (var item in productData)
            {
                Row dataRow = new Row() { RowIndex = rowIndex };
                dataRow.Append(
                    CreateSharedStringCell($"A{rowIndex}", item.Item1, sharedStringTablePart), // Use shared string for product name
                    CreateNumberCell($"B{rowIndex}", item.Item2.ToString())                   // Number cell remains the same
                );
                sheetData.Append(dataRow);
                rowIndex++;
            }

            // *** Important: Save the SharedStringTable ***
            sharedStringTablePart.SharedStringTable.Save();
            workbookpart.Workbook.Save();
        }

        Console.WriteLine($"Successfully created with shared strings: {filePath}");
    }

    // Helper to insert text into SharedStringTable and return its index
    private int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
    {
        // If the SharedStringTable is null, create it.
        if (shareStringPart.SharedStringTable == null)
        {
            shareStringPart.SharedStringTable = new SharedStringTable();
        }

        int i = 0;
        // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
        {
            if (item.InnerText == text)
            {
                return i;
            }
            i++;
        }

        // The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
        shareStringPart.SharedStringTable.Save(); // Save changes to the part

        // Return the index of the newly added item (which is the current count minus 1)
        return i;
    }

    // Helper function to create a Cell using Shared String Table
    private Cell CreateSharedStringCell(string cellReference, string text, SharedStringTablePart sharedStringPart)
    {
        int sharedStringIndex = InsertSharedStringItem(text, sharedStringPart);

        return new Cell()
        {
            CellReference = cellReference,
            DataType = CellValues.SharedString, // Set type to SharedString
            CellValue = new CellValue(sharedStringIndex.ToString()) // Value is the index
        };
    }

     // Helper function to create a Cell with a number (same as before)
    private Cell CreateNumberCell(string cellReference, string value)
    {
        return new Cell()
        {
            CellReference = cellReference,
            DataType = CellValues.Number,
            CellValue = new CellValue(value)
        };
    }
}

// --- How to use it: ---
// var generator = new SharedStringExcelGenerator();
// generator.CreateExcelWithSharedStrings("C:\\temp\\SharedStringReport.xlsx");

Explanation of Changes:

  1. We added a SharedStringTablePart to the WorkbookPart.
  2. We created a helper function InsertSharedStringItem which checks if a string already exists in the table. If yes, it returns the existing index; otherwise, it adds the string and returns the new index.
  3. The CreateSharedStringCell helper now calls InsertSharedStringItem to get the index.
  4. Crucially, the Cell‘s DataType is set to CellValues.SharedString, and its CellValue is the index (as a string) obtained from the shared string table.
  5. We made sure to save the SharedStringTable before closing.

Notice how “Widget” is only stored once in the shared string table, even though it appears twice in the data.

Integrating with a Web API

Generating the file is one thing; serving it from a web API endpoint is the next step. Here’s a conceptual example using ASP.NET Core:

using Microsoft.AspNetCore.Mvc;
using System.IO;
using DocumentFormat.OpenXml.Packaging; // etc. (include necessary usings)

[ApiController]
[Route("api/[controller]")]
public class ReportsController : ControllerBase
{
    [HttpGet("download-simple-report")]
    public IActionResult DownloadSimpleReport()
    {
        string tempFilePath = Path.Combine(Path.GetTempPath(), $"SimpleReport_{Guid.NewGuid()}.xlsx");
        string downloadFileName = "SimpleProductReport.xlsx";

        try
        {
            var generator = new SimpleExcelGenerator(); // Or SharedStringExcelGenerator
            generator.CreateSimpleExcel(tempFilePath); // Generate the file

            // Read the generated file into a memory stream
            var memoryStream = new MemoryStream();
            using (var fileStream = new FileStream(tempFilePath, FileMode.Open, FileAccess.Read))
            {
                fileStream.CopyTo(memoryStream);
            }
            memoryStream.Position = 0; // Reset stream position

            // Return the file
            return File(memoryStream,
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", // Correct MIME type for .xlsx
                        downloadFileName); // Filename user will see when downloading
        }
        finally
        {
            // Clean up the temporary file
            if (System.IO.File.Exists(tempFilePath))
            {
                System.IO.File.Delete(tempFilePath);
            }
        }
    }
}

Key points for API integration:

  1. Generate to a Stream or Temp File: You can generate the document directly into a MemoryStream or use a temporary file path. Using a temp file can be better for very large files to avoid excessive memory usage.
  2. Set Correct MIME Type: Use "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" for .xlsx files.
  3. Set Content-Disposition Header: The File method in ASP.NET Core handles setting the Content-Disposition header (usually attachment; filename="yourfile.xlsx"), prompting the browser to download the file with the specified name.
  4. Cleanup: If using temporary files, ensure they are deleted afterwards (a try...finally block is good practice).

Beyond the Basics

The Open XML SDK is powerful but can be verbose. We’ve only scratched the surface. You can also:

  • Apply cell formatting (fonts, colors, borders, number formats).
  • Create formulas.
  • Add charts and images.
  • Work with multiple sheets.
  • Read and modify existing Excel files.

For more complex scenarios or if you prefer a slightly higher-level abstraction, you might also investigate libraries like ClosedXML, which provides a more intuitive, object-oriented wrapper around the Open XML SDK. However, understanding the fundamentals of the SDK itself is invaluable.

Conclusion

The Open XML SDK provides a robust, server-friendly way for C# .NET developers to generate Excel spreadsheets without relying on installed Office applications. While the syntax can seem a bit verbose initially due to its direct mapping to the underlying XML structure, it offers complete control and is essential for building scalable applications that need to produce .xlsx files, especially in web API contexts.

Leave a Comment

Your email address will not be published. Required fields are marked *