How to merge multiple worksheets to a single works

Question

I know, there is a lot of question about this topic, but I didn't find an usable solution.

I have multiple excel files, Each file have a worksheet, I want to merge worksheets to a single workbook and worksheet with all styles(Column widths,Cell borders, fill color, fontsize etc.).

How can I do it, I tried Spire.Xls, but styles didn'nt copy.

private void CombineFiles(List<string> files,string outputPath)
        {

            Spire.Xls.Workbook resultworkbook = new Spire.Xls.Workbook();
            Spire.Xls.Worksheet resultworksheet = resultworkbook.Worksheets.Add("worksheet");

            foreach (var file in files)
            {
                Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
                workbook.LoadFromFile(file);

                Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
                System.Data.DataTable dataTable = sheet.ExportDataTable();
                resultworksheet.InsertDataTable(dataTable, false, sheet.LastRow + 1, 1);
            }

            resultworkbook.SaveToFile(outputPath);
            System.Diagnostics.Process.Start(outputPath);
        }

Answer 1

If you want to copy styles when merging multiple worksheets to a single worksheet, you should use the CellRange.Copy() / Worksheet.Copy() instead of Worksheet.InsertDataTable(). Also note that it's impossible to copy column width, if all of the column widths are the same, you can get the column widths and then set to the copied columns.

Try the following code:

    static void Main(string[] args)
    {
        string outputPath = "‪output.xlsx";
        List<string> files = new List<string>();
        files.Add(@"File1.xlsx");
        files.Add(@"File2.xlsx");
        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.Version2013);
        System.Diagnostics.Process.Start(outputPath);
    }