Generate Separate Files per Worksheet in an Excel XLSX using C# .NET

Most business reports created using Excel are not single-paged documents but rather contain several contextually related information stored in multiple worksheets. Some examples are regional sales reports, department-wise employee records, inventory listings per store, etc.

However, as the number of worksheets increases in the Excel file, it becomes challenging to navigate and manage data within a single document. Additionally, sharing such Excel documents among disparate stakeholders becomes difficult because they contain information not intended for all. Thus, it is necessary to divide these multi-sheet Excel documents into several smaller ones for easier manageability, improved efficiency, and data isolation.

Despite its supremacy in functionality, Excel doesn’t offer native functions to split the worksheets. You’ll either need to do this manually or use VBA. While the manual approach is inefficient and error-prone, the VBA approach makes the file vulnerable.

Document Solutions for Excel C# .NET (DsExcel) offers several programmatic approaches to split multiple worksheets in an Excel file to separate Excel documents, such as:

• Split worksheets by copying from source to target workbook
• Split worksheets by moving from source to target workbook
• Split worksheets by deleting undesired worksheets from the source file and saving it as a new document
• Splitting worksheets by copying and pasting content from source to target workbook and more

In this blog, we’ll see the programmatic approach to splitting worksheets by copying them from the source to the target workbook.

Ready to Get Started? Download Document Solutions for Excel Today!

Use-Case

Suppose, as a Chief Sales Officer of your company, you have prepared an Excel report that contains the region-wise sales analysis information on separate sheets. You want to share this report with the regional sales managers. However, you want to separate the sheets into separate Excel files to isolate the analysis details of one region from the other.

Let’s see how to generate separate Excel files for each worksheet using DsExcel .NET in 3 simple steps.

Follow the documentation to get started with DsExcel in Visual Studio.

Step 1 - Initialize the Workbook

The first step is to load the source workbook whose sheets you want to split. With DsExcel, you can load your Excel file using the Open method in the IWorkbook interface, as shown below:

var sourceWorkbook = new Workbook();
sourceWorkbook.Open("SourceWorkbook.xlsx");

Step 2 - Add the logic to split Excel worksheets

The next step is to add the logic to split the worksheets in the source workbook that includes,

• Creating a loop to iterate through each of the worksheets in the source workbook and split it as desired
• Initializing a temporary workbook
• Copy the current worksheet from the source workbook to the end of the temporary workbook using the Copy method of the IWorksheets interface
• Delete the default sheet in the temporary workbook

foreach(IWorksheet wrkSheet in sourceWorkbook.Worksheets)
{
  var tempWorkBook = new Workbook();                
  wrkSheet.Copy(tempWorkBook);
  tempWorkBook.Worksheets["Sheet1"].Delete();
}

Step 3 - Save the split Excel worksheets

Finally, save the split worksheet as a separate Excel or any other format using the Save method of the IWorkbook interface as shown below:

tempWorkBook.Save(wrkSheet.Name + ".xlsx");
or 
tempWorkBook.Save(wrkSheet.Name + ".pdf", SaveFileFormat.Pdf);

The complete code snippet is as described below:

var sourceWorkbook = new Workbook();
sourceWorkbook.Open("SourceWorkbook.xlsx");

foreach(IWorksheet wrkSheet in sourceWorkbook.Worksheets)
{
    var tempWorkBook = new Workbook();          
    //Copy the current worksheet to the end of temp workbook      
    wrkSheet.Copy(tempWorkBook); 
    //Delete the default worksheet in temp workbook
    tempWorkBook.Worksheets["Sheet1"].Delete(); 
    //Save the temp workbook
    tempWorkBook.Save(wrkSheet.Name + ".xlsx");                
}   

DsExcel offers some more methods from the IWorksheets interface, such as CopyAfter and CopyBefore, to split worksheets for specific Excel requirements. Further, it provides Move, MoveAfter, and MoveBefore methods to split the worksheets by moving them from the source to target Excel files.

Conclusion

Document Solutions for Excel (DsExcel) is a powerful tool designed to streamline the creation, reading, and editing of Excel documents using C#. It provides a simple and efficient way to programmatically handle Excel documents. With ready-to-use API, you can perform the desired Excel operation with just a few lines of code, just like splitting the worksheets. The programmatic approach to working with Excel documents saves valuable time and ensures data precision.

Ready to Get Started? Download Document Solutions for Excel Today!