Split Excel Worksheets into Multiple Files in C#

Source: Dev.to

Introduction

In modern data‑processing workflows, splitting a multi‑worksheet Excel file into individual, single‑worksheet files is a ubiquitous requirement—whether for distributing department‑specific financial reports, sharing class‑specific student grade sheets, or organizing segmented datasets. Manual splitting is tedious, time‑consuming, and prone to human error, leading to data inconsistencies.

Automating this process with C# is the ultimate solution to boost productivity, and Free Spire.XLS—a lightweight, powerful .NET library—makes it simpler than ever, with no Microsoft Office installation required.

This guide walks you through the entire process of splitting Excel worksheets using C# and Free Spire.XLS. We’ll cover:

• Project setup
• Core code implementation
• Advanced use cases
• Alternative libraries

Technology Stack Overview

• Language: C#
• Frameworks: .NET Framework 4.0+, .NET Core 3.1+, .NET 5+
• Library: Free Spire.XLS (NuGet)
• IDE: Visual Studio 2019+, Rider, VS Code, or any C# IDE

Why Choose Free Spire.XLS?

Free Spire.XLS is a free .NET Excel component designed for seamless Excel file manipulation. Compared with traditional solutions like Microsoft Office Interop, it offers three key advantages:

Prerequisites

To follow along, ensure your development environment meets these requirements:

• .NET Framework 4.0+ / .NET Core 3.1+ / .NET 5+
• Free Spire.XLS NuGet package (latest version recommended)
• Visual Studio 2019+ / Rider / VS Code (or any C# IDE)

Step‑By‑Step Implementation

1. Project Setup

1. Create a new C# Console App project in your IDE.

2. Install Free Spire.XLS via NuGet:

   • Package Manager Console

     Install-Package FreeSpire.XLS

   • GUI Method
     Right‑click the project → Manage NuGet Packages → Search “FreeSpire.XLS” → Install.

2. Core Code Implementation (With Detailed Explanations)

The core logic involves:

1. Loading the source Excel file.
2. Iterating through its worksheets.
3. Copying each worksheet to a new workbook.
4. Saving the new file.

using Spire.Xls;
using System;
using System.IO;

namespace ExcelWorksheetSplitter
{
    class Program
    {
        static void Main(string[] args)
        {
            // -------------------------------------------------
            // 1️⃣ Configure file paths (update to match your environment)
            // -------------------------------------------------
            string sourceFilePath = "source.xlsx"; // Path to your multi‑worksheet Excel file
            string outputDirectory = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                "SplitWorksheets");

            try
            {
                // -------------------------------------------------
                // 2️⃣ Load the source workbook
                // -------------------------------------------------
                using (Workbook sourceWorkbook = new Workbook())
                {
                    sourceWorkbook.LoadFromFile(sourceFilePath);

                    // -------------------------------------------------
                    // 3️⃣ Ensure the output folder exists
                    // -------------------------------------------------
                    Directory.CreateDirectory(outputDirectory);

                    // -------------------------------------------------
                    // 4️⃣ Iterate through all worksheets
                    // -------------------------------------------------
                    for (int i = 0; i  file.EndsWith(".xlsx") || file.EndsWith(".xls"))
                                   .ToArray();

    foreach (string file in excelFiles)
    {
        Console.WriteLine($"Processing file: {Path.GetFileName(file)}");
        SplitSingleExcelFile(file,
            Path.Combine(outputFolder, Path.GetFileNameWithoutExtension(file)));
    }
}

// Reusable method to split a single Excel file
static void SplitSingleExcelFile(string sourceFilePath, string outputFolder)
{
    using (Workbook wb = new Workbook())
    {
        wb.LoadFromFile(sourceFilePath);
        Directory.CreateDirectory(outputFolder);

        foreach (Worksheet sheet in wb.Worksheets)
        {
            using (Workbook newWb = new Workbook())
            {
                newWb.Worksheets.Clear();
                newWb.Worksheets.AddCopy(sheet);
                newWb.SaveToFile(
                    Path.Combine(outputFolder, $"{sheet.Name}.xlsx"),
                    ExcelVersion.Version2016);
            }
        }
    }
}

Alternative Libraries (Comparison)

Splitting Excel worksheets with C# and Free Spire.XLS is a straightforward, efficient way to automate repetitive data‑processing tasks. Whether you’re building a server‑side automation tool or a desktop app, Free Spire.XLS provides a reliable solution to streamline your Excel workflow.