Merge Excel spreadsheets Leave feedback

A spreadsheet file contains data in the form of rows and columns. A spreadsheet file can be saved in several different file formats, each having a different file extension for unique representation. Data is stored in cells either in plain form such as text string, numbers, date, currency, etc. or as formulas that change a cell’s value when referenced cell values change.

Common spreadsheet file extensions and their file formats include XLSX (Microsoft Excel Open XML Spreadsheet), ODS (OpenDocument Spreadsheet) and XLS (Microsoft Excel Binary File Format).

XLSX is well-known format for Microsoft Excel documents that was introduced by Microsoft with the release of Microsoft Office 2007. Based on structure organized according to the Open Packaging Conventions as outlined in Part 2 of the OOXML standard ECMA-376, the new format is a zip package that contains a number of XML files. The underlying structure and files can be examined by simply unzipping the .xlsx file.

GroupDocs.Merger allows developers to merge XLSX files when it’s needed to organize multiple XLSX files into single document or send fewer attachments etc. And you can do this without any third-party software or manual work involved. With GroupDocs.Merger it is possible to combine XLSX documents of any size and structure - all text, images, tables, graphs, forms and other content will be preserved.

The following example demonstrates how to merge XLSX files with several lines of C# code:

• Create an instance of Merger class and pass source XLSX file path as a constructor parameter. You may specify absolute or relative file path as per your requirements.
• Add another XLSX file to merge with Join method. Repeat this step for other XLSX documents you want to merge.
• Call Merger class Save method and specify the filename for the merged XLSX file as parameter.

// Load the source XLSX file
using (Merger merger = new Merger(@"c:\sample1.xlsx"))
{
    // Add another XLSX file to merge
    merger.Join(@"c:\sample2.xlsx");
    // Merge XLSX files and save result
    merger.Save(@"c:\merged.xlsx");
}

By default, each joined spreadsheet is added to the result as separate worksheets. To append the rows of the joined spreadsheets below the existing data instead — for example, to collect monthly reports into one table — use SpreadsheetJoinOptions with SpreadsheetJoinMode.Rows:

• Create an instance of Merger class and pass the first spreadsheet file path as a constructor parameter. This document is always taken in full.
• Create an instance of SpreadsheetJoinOptions class, set Mode to SpreadsheetJoinMode.Rows and, if the joined files have header rows, set SkipRows to the number of rows to skip.
• Add the other spreadsheets with Join method and pass the options as a parameter. The rows of each file are appended below the last row containing data of the matching worksheet.
• Call Save method and specify the filename for the merged spreadsheet.

The following code sample demonstrates how to merge rows of several spreadsheets into a single sheet:

// Load the first spreadsheet
using (Merger merger = new Merger(@"c:\january.xlsx"))
{
    // Append rows instead of adding worksheets, and skip the header row of each joined file
    SpreadsheetJoinOptions joinOptions = new SpreadsheetJoinOptions
    {
        Mode = SpreadsheetJoinMode.Rows,
        SkipRows = 1
    };
    merger.Join(@"c:\february.xlsx", joinOptions);
    merger.Join(@"c:\march.xlsx", joinOptions);
    // Save the merged spreadsheet
    merger.Save(@"c:\q1.xlsx");
}

When the spreadsheets have several worksheets, SpreadsheetSheetMatching set through the SheetMatching property controls where the rows go:

• ByIndex (default) — the rows of the n-th worksheet of a joined file are appended to the n-th worksheet of the result. Worksheets beyond the number of result worksheets are added as new worksheets.
• FirstSheetOnly — only the first worksheet of each joined file is appended, to the first worksheet of the result.

SkipRows and SheetMatching have no effect when Mode is SpreadsheetJoinMode.Worksheets.

Row-wise joining carries cell values, formulas (relative references are adjusted to the new position), cell styles, merged cells and row heights. Charts, pictures and other floating objects, pivot tables, tables, conditional formatting and data validation of the joined files are not carried over.

Row-wise joining works for XLSX, XLS, XLSM, XLSB, XLTX, XLTM, XLT, XLAM and ODS, also when a joined spreadsheet has a different format than the first one (for example, XLS joined into XLSX).

Warning

• A negative SkipRows value throws GroupDocsMergerException.
• Appending more rows than the output format allows (65,536 rows for XLS and XLT, 1,048,576 rows for the other formats) throws GroupDocsMergerException.
• ApplyPageBuilder throws GroupDocsMergerException while a spreadsheet joined in Rows mode is pending, because appended rows do not form separate pages.

Please find more use-cases and complete C# sources of our backend and frontend examples and try them for free!

GroupDocs.Merger for .NET provides an online XLSX Merger App, which allows you to try it for free and check its quality and accuracy.