Is it possible to append multiple .xls files into a single Excel 2003 worksheet without Office Interop?

Question

I have an ASP.NET Web API and need to implement this feature for a future version. Specifically:

• Several .xls files will be placed in a temporary folder. They have the same width but different heights, as well as different row and column sizes.
• The files need to be appended into one .xls file
• The final excel cannot have multiple worksheets.
• The Office Interop libraries cannot be used because Office is not installed and cannot be instaled on the deployment environment.

Is there a way to do this without using the office interop libraries as specified, or any paid third party libraries (free third party libraries are more than welcome) ?

Answer 1

Install Spire.XLS from NuGet: Install-Package Spire.XLS -Version 9.6.7

Spire.XLS provides two ways to merge excel files into a single excel worksheet:

1. Merge excel with styles using CellRange.Copy()

static void Main(string[] args)
{
    string outputPath = "‪output.xls";
    List<string> files = new List<string>();
    files.Add(@"File1.xls");
    files.Add(@"File2.xls");
    CombineFiles(files, outputPath);
}
private static void CombineFiles(List<string> files, string outputPath)
{
    Spire.Xls.Workbook resultworkbook = new Spire.Xls.Workbook();
    resultworkbook.Worksheets.Clear();
    Spire.Xls.Worksheet resultworksheet = resultworkbook.Worksheets.Add("worksheet");

    Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
    for (int i = 0; i < files.Count; i++)
    {
        workbook.LoadFromFile(files[i]);
        Worksheet sheet = workbook.Worksheets[0];
        if (i == 0)
        {
            sheet.AllocatedRange.Copy(resultworksheet.Range[1, 1], true, true);

        }
        else
        {
            sheet.AllocatedRange.Copy(resultworksheet.Range[resultworksheet.LastRow + 1, 1], true, true);
        }
    }


    resultworkbook.SaveToFile(outputPath, ExcelVersion.Version97to2003);
}

Reference: How to merge multiple worksheets to a single worksheet with styles

2. Merge excel without styles using DataTable

Workbook workbook1 = new Workbook();
//load the first workbook
workbook1.LoadFromFile(FilePath1);
//load the second workbook
Workbook workbook2 = new Workbook();
workbook2.LoadFromFile(FilePath2);
//load the third workbook
Workbook workbook3 = new Workbook();
workbook3.LoadFromFile(FilePath3);

//import the second and third workbook's first worksheet into the first workbook using datatable
Worksheet sheet1 = workbook1.Worksheets[0];
Worksheet sheet2 = workbook2.Worksheets[0];
Worksheet sheet3 = workbook3.Worksheets[0];

DataTable dataTable1 = sheet2.ExportDataTable();
DataTable dataTable2 = sheet3.ExportDataTable();

sheet1.InsertDataTable(dataTable1, false, sheet1.LastRow + 1, 1);
sheet1.InsertDataTable(dataTable2, false, sheet1.LastRow + 1, 1);

workbook1.SaveToFile(OutputPath + "Merged.xls", ExcelVersion.Version97to2003);

Reference: How to merge 3 Sheets from different Excel files into one sheet with C#