A Practical Guide to Exporting Excel Data Using OpenXML SDK in C#

Introduction to Excel Data Export

In C# development, exporting data to Excel files is a common requirement. Users often need to transfer specific rows from an existing Excel file to a new one, such as when filtering data or generating reports. This article addresses this task by presenting a detailed guide using the OpenXML SDK, Microsoft's official library for reading and writing Office documents without requiring Office installation.

Exporting with OpenXML SDK

The OpenXML SDK provides a robust way to manipulate Excel files in the OpenXML format. To export specific rows, you first need to read the source file, identify the rows based on indexes, and then write them to a new file. Below is a simplified code example that demonstrates this process.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void ExportRows(string sourcePath, string targetPath, List<int> rowIndexes)
{
    // Open the source workbook
    using (SpreadsheetDocument sourceDoc = SpreadsheetDocument.Open(sourcePath, false))
    {
        WorkbookPart workbookPart = sourceDoc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

        // Create a new workbook
        using (SpreadsheetDocument targetDoc = SpreadsheetDocument.Create(targetPath, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart targetWorkbookPart = targetDoc.AddWorkbookPart();
            targetWorkbookPart.Workbook = new Workbook();
            WorksheetPart targetWorksheetPart = targetWorkbookPart.AddNewPart<WorksheetPart>();
            targetWorksheetPart.Worksheet = new Worksheet(new SheetData());

            SheetData targetSheetData = targetWorksheetPart.Worksheet.Elements<SheetData>().First();

            // Iterate through the row indexes and copy rows
            foreach (int index in rowIndexes)
            {
                Row sourceRow = sheetData.Elements<Row>().ElementAt(index);
                Row targetRow = new Row();
                foreach (Cell cell in sourceRow.Elements<Cell>())
                {
                    Cell newCell = new Cell();
                    newCell.CellReference = cell.CellReference;
                    newCell.DataType = cell.DataType;
                    newCell.CellValue = cell.CellValue;
                    targetRow.AppendChild(newCell);
                }
                targetSheetData.AppendChild(targetRow);
            }

            targetWorkbookPart.Workbook.Save();
        }
    }
}

This code opens the source Excel file, extracts the specified rows, and writes them to a new file. The OpenXML SDK handles the underlying XML structure, making it efficient and scalable.

Alternative Export Methods

Besides OpenXML SDK, other methods are available. Excel interop uses Microsoft Office COM components, but it requires Office installation and can be slower. NPOI is a third-party library that supports Excel without Office, offering similar functionality to OpenXML SDK.

For example, using NPOI:

// Example using NPOI to write data
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

public void ExportWithNPOI(List<string[]> data, string filePath)
{
    IWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");
    for (int i = 0; i < data.Count; i++)
    {
        IRow row = sheet.CreateRow(i);
        for (int j = 0; j < data[i].Length; j++)
        {
            row.CreateCell(j).SetCellValue(data[i][j]);
        }
    }
    using (FileStream fs = new FileStream(filePath, FileMode.Create))
    {
        workbook.Write(fs);
    }
}

This method is useful if you prefer a library with a simpler API, but OpenXML SDK is recommended for standard compliance and performance.

Conclusion

Exporting specific rows from an Excel file in C# can be efficiently achieved using the OpenXML SDK. It provides a high-performance, Office-independent solution. While alternatives like Excel interop and NPOI exist, OpenXML SDK stands out for its official support and flexibility. Developers should choose based on their specific needs, such as compatibility or ease of use.