Merging multiple excel files into one_问答_开发者

Here's the problem, I have several excel files that are generated, each has 1 tab (but that's not to say some won't have more in the future).

What I need to write, is a process that can open each file, and copy all of it's worksheets (tabs) into a new file.

In the end, that new file should contain the worksheets of all the other files.

Currently, I have created the following to accomplish converting between excel formats.

I'm not too sure where to go from here, I don't have any of my sources I used to create this... and am not too sure on the object model (in order to copy the tabs to a new file) or the issues I'll face with com and making sure I keep everything cleaned up.

        object excelApplication = null;
        object workbook = null;
        object workbooks = null;

        try
        {
            // Get the Remote Type
            var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);

            // Instantiate the type
            excelApplication = Activator.CreateInstance(excelType);

            // Turn off Prompts
            excelApplication.GetType().InvokeMember(
                "DisplayAlerts",
                BindingFlags.SetProperty,
                null,
                excelApplication,
                new Object[] { false });

            // Get a reference to the workbooks object
            workbooks = excelApplication.GetType().InvokeMember(
                "Workbooks",
                BindingFlags.GetProperty,
                null,
                excelApplication,
                null);

            // Open the input file
            workbook = workbooks.GetType().InvokeMember(
                "Open",
                BindingFlags.InvokeMethod,
                null,
                workbooks,
                new object[] { inputFilePath });

            // If overwrite is turned off, and the file exist, the save as line will throw an error
            if (File.Exists(outputFilePath) && overwriteIfExists)
            {
                File.Delete(outputFilePath);
            }

            // Save the workbook
            workbook.GetType().InvokeMember(
                "SaveAs",
                BindingFlags.InvokeMethod,
                null,
                workbook,
                new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
        }
        finally
        {
            // Cleanup all created COM objects
            if (workbook != null)
            {
                workbook.GetType().InvokeMember(
                    "Close",
                    BindingFlags.InvokeMethod,
                    null,
                    workbook,
                    null);
                Marshal.ReleaseComObject(workbook);
                workbook = null;
            }

            if (workbooks != null)
            {
                Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }

            if (excelApplication != null)
            {
                excelApplication.GetType().InvokeMember(
                    "Quit",
                    BindingFlags.InvokeMethod,
                    null,
                    excelApplication,
                    null);
                Marshal.ReleaseComObject(excelApplication);
                excelApplication = null;
            }
        }

EDIT: This code is nearly working, the issue is on the line that should actually perform the copy...

    public byte[] MergeFiles(FileFormat saveAsFileFormat, List<byte[]> inputFileBytesList)
    {
        var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + ".target.xls");

        Impersonate(
            Properties.Settings.Default.ImpersonationUser.Decrypt(),
            Properties.Settings.Default.ImpersonationDomain,
            Properties.Settings.Default.ImpersonationPassword.Decrypt()
        );

        var convertedFileList = new List<string>();
        foreach (var inputFileBytes in inputFileBytesList)
        {
            var inputFileExtension = GetExtension(inputFileBytes);
            var inputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
            var convertedFileBytes = SaveAs(saveAsFileFormat, inputFileBytes);
            File.WriteAllBytes(inputFilePath, convertedFileBytes);
            convertedFileList.Add(inputFilePath);
        }

        // Target Excel File
        object targetExcelApplication = null;
        object targetWorkbook = null;
        object targetWorkbooks = null;
        object targetWorksheets = null;
        object targetWorksheet = null;

        try
        {
            // Get the Remote Type
            var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);

            // Instantiate the type
            targetExcelApplication = Activator.CreateInstance(excelType);

            // Turn off Prompts
            targetExcelApplication.GetType().InvokeMember(
                "DisplayAlerts",
                BindingFlags.SetProperty,
                null,
                targetExcelApplication,
                new Object[] { false });

            // Get a reference to the workbooks object
            targetWorkbooks = targetExcelApplication.GetType().InvokeMember(
                "Workbooks",
                BindingFlags.GetProperty,
                null,
                targetExcelApplication,
                null);

            // Create a workbook to add the sheets to
            targetWorkbook = targetWorkbooks.GetType().InvokeMember(
                "Add",
                BindingFlags.InvokeMethod,
                null,
                targetWorkbooks,
                new object[] { 1 });

            // Get a reference to the worksheets object
            targetWorksheets = targetWorkbook.GetType().InvokeMember(
                "Sheets",
                BindingFlags.GetProperty,
                null,
                targetExcelApplication,
                null
                );

            foreach (var inputFilePath in convertedFileList)
            {
                // Open each File, grabbing all tabs
                object sourceExcelApplication = null;
                object sourceWorkbooks = null;
                object sourceWorkbook = null;
                object sourceWorksheets = null;

                try
                {
                    // Instantiate the type
                    sourceExcelApplication = Activator.CreateInstance(excelType);

                    // Turn off Prompts
                    sourceExcelApplication.GetType().InvokeMember(
                        "DisplayAlerts",
                        BindingFlags.SetProperty,
                        null,
                        sourceExcelApplication,
                        new Object[] {false});

                    // Get a reference to the workbooks object
                    sourceWorkbooks = sourceExcelApplication.GetType().InvokeMember(
                        "Workbooks",
                        BindingFlags.GetProperty,
                        null,
                        sourceExcelApplication,
                        null);

                    // Open the input file
                    sourceWorkbook = sourceWorkbooks.GetType().InvokeMember(
                        "Open",
                        BindingFlags.InvokeMethod,
                        null,
                        sourceWorkbooks,
                        new object[] {inputFilePath});

                    // Get a reference to the worksheets object
                    sourceWorksheets = sourceWorkbook.GetType().InvokeMember(
                        "Sheets",
                        BindingFlags.GetProperty,
                        null,
                        sourceExcelApplication,
                        null);

                    var sourceSheetCount = (int)(sourceWorksheets.GetType().InvokeMember(
                        "Count",
                        BindingFlags.GetProperty,
                        null,
                        sourceWorksheets,
                        null));

                    for (var i = 1; i <= sourceSheetCount; i++)
                    {
                        var targetSheetCount = (int)(targetWorksheets.GetType().InvokeMember(
                            "Count",
                            BindingFlags.GetProperty,
                            null,
                            targetWorksheets,
                            null));

                        var sourceWorksheet = sourceWorksheets.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            sourceWorksheets,
                            new Object[] { i });

                        targetWorksheet = targetWorksheets.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            targetWorksheets,
                            new Object[] {开发者_运维技巧 targetSheetCount });

                        // TODO: Copy into target file

                        sourceWorksheet.GetType().InvokeMember(
                            "Copy",
                            BindingFlags.InvokeMethod,
                            null,
                            sourceWorksheet,
                            new[] { Type.Missing, targetWorksheet }
                            );

                        if (sourceWorksheet != null)
                        {
                            Marshal.ReleaseComObject(sourceWorksheet);
                            sourceWorksheet = null;
                        }
                    }
                }
                finally
                {
                    // Cleanup all created COM objects
                    if (sourceWorksheets != null)
                    {
                        Marshal.ReleaseComObject(sourceWorksheets);
                        sourceWorksheets = null;
                    }

                    if (sourceWorkbook != null)
                    {
                        sourceWorkbook.GetType().InvokeMember(
                            "Close",
                            BindingFlags.InvokeMethod,
                            null,
                            sourceWorkbook,
                            null);
                        Marshal.ReleaseComObject(sourceWorkbook);
                        sourceWorkbook = null;
                    }

                    if (sourceWorkbooks != null)
                    {
                        Marshal.ReleaseComObject(sourceWorkbooks);
                        sourceWorkbooks = null;
                    }

                    if (sourceExcelApplication != null)
                    {
                        sourceExcelApplication.GetType().InvokeMember(
                            "Quit",
                            BindingFlags.InvokeMethod,
                            null,
                            sourceExcelApplication,
                            null);
                        Marshal.ReleaseComObject(sourceExcelApplication);
                        sourceExcelApplication = null;
                    }
                }
            }

            // If overwrite is turned off, and the file exist, the save as line will throw an error
            if (File.Exists(outputFilePath))
            {
                File.Delete(outputFilePath);
            }

            // Save the workbook
            targetWorkbook.GetType().InvokeMember(
                "SaveAs",
                BindingFlags.InvokeMethod,
                null,
                targetWorkbook,
                new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
        }
        finally
        {
            // Cleanup all created COM objects
            if (targetWorksheets != null)
            {
                Marshal.ReleaseComObject(targetWorksheets);
                targetWorksheets = null;
            }

            if (targetWorkbook != null)
            {
                targetWorkbook.GetType().InvokeMember(
                    "Close",
                    BindingFlags.InvokeMethod,
                    null,
                    targetWorkbook,
                    null);
                Marshal.ReleaseComObject(targetWorkbook);
                targetWorkbook = null;
            }

            if (targetWorkbooks != null)
            {
                Marshal.ReleaseComObject(targetWorkbooks);
                targetWorkbooks = null;
            }

            if (targetExcelApplication != null)
            {
                targetExcelApplication.GetType().InvokeMember(
                    "Quit",
                    BindingFlags.InvokeMethod,
                    null,
                    targetExcelApplication,
                    null);
                Marshal.ReleaseComObject(targetExcelApplication);
                targetExcelApplication = null;
            }
        }

        // Read target file bytes
        var resultBytes = (File.Exists(outputFilePath))
            ? File.ReadAllBytes(outputFilePath)
            : new byte[] { };

        // Delete working files
        if (File.Exists(outputFilePath))
            File.Delete(outputFilePath);
        foreach (var inputFilePath in convertedFileList.Where(File.Exists))
        {
            File.Delete(inputFilePath);
        }

        Repersonate();

        // Return result
        return resultBytes;
    }

I get the error System.Runtime.InteropServices.COMException: Copy method of Worksheet class failed, which doesn't help much...I don't know why it failed...

This seems to work, just need to add a little cleanup to remove the blank worksheets that are initially created, then to active the first sheet in the file before saving

    [WebMethod]
    public byte[] MergeFiles(FileFormat saveAsFileFormat, List<byte[]> inputFileBytesList)
    {
        //var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + ".xls");
        var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, "target.xls");

        Impersonate(
            Properties.Settings.Default.ImpersonationUser.Decrypt(),
            Properties.Settings.Default.ImpersonationDomain,
            Properties.Settings.Default.ImpersonationPassword.Decrypt()
        );

        var convertedFileList = new List<string>();
        foreach (var inputFileBytes in inputFileBytesList)
        {
            var inputFileExtension = GetExtension(inputFileBytes);
            var inputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
            File.WriteAllBytes(inputFilePath, inputFileBytes);

            var convertedFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
            SaveAsInternal(saveAsFileFormat, inputFilePath, convertedFilePath, true);
            convertedFileList.Add(convertedFilePath);
        }

        // Target Excel File
        object excelApplication = null;
        object excelWorkbooks = null;
        object targetWorkbook = null;
        object targetWorksheets = null;
        object targetWorksheet = null;

        try
        {
            // Get the Remote Type
            var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);

            // Instantiate the type
            excelApplication = Activator.CreateInstance(excelType);

            // Turn off Prompts
            excelApplication.GetType().InvokeMember(
                "DisplayAlerts",
                BindingFlags.SetProperty,
                null,
                excelApplication,
                new Object[] { false });

            // Get a reference to the workbooks object
            excelWorkbooks = excelApplication.GetType().InvokeMember(
                "Workbooks",
                BindingFlags.GetProperty,
                null,
                excelApplication,
                null);

            // Create a workbook to add the sheets to
            targetWorkbook = excelWorkbooks.GetType().InvokeMember(
                "Add",
                BindingFlags.InvokeMethod,
                null,
                excelWorkbooks,
                new object[] { 1 });

            // Get a reference to the worksheets object
            targetWorksheets = targetWorkbook.GetType().InvokeMember(
                "Sheets",
                BindingFlags.GetProperty,
                null,
                excelApplication,
                null
                );

            // Open each File, grabbing all tabs
            foreach (var inputFilePath in convertedFileList)
            {
                object sourceWorkbook = null;
                object sourceWorksheets = null;

                try
                {
                    // Open the input file
                    sourceWorkbook = excelWorkbooks.GetType().InvokeMember(
                        "Open",
                        BindingFlags.InvokeMethod,
                        null,
                        excelWorkbooks,
                        new object[] {inputFilePath});

                    // Get a reference to the worksheets object
                    sourceWorksheets = sourceWorkbook.GetType().InvokeMember(
                        "Sheets",
                        BindingFlags.GetProperty,
                        null,
                        excelApplication,
                        null);

                    var sourceSheetCount = (int)(sourceWorksheets.GetType().InvokeMember(
                        "Count",
                        BindingFlags.GetProperty,
                        null,
                        sourceWorksheets,
                        null));

                    for (var i = 1; i <= sourceSheetCount; i++)
                    {
                        var targetSheetCount = (int)(targetWorksheets.GetType().InvokeMember(
                            "Count",
                            BindingFlags.GetProperty,
                            null,
                            targetWorksheets,
                            null));

                        var sourceWorksheet = sourceWorksheets.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            sourceWorksheets,
                            new Object[] { i });

                        targetWorksheet = targetWorksheets.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            targetWorksheets,
                            new Object[] { targetSheetCount });

                        // TODO: Copy into target file

                        sourceWorksheet.GetType().InvokeMember(
                            "Copy",
                            BindingFlags.InvokeMethod,
                            null,
                            sourceWorksheet,
                            new[] { Type.Missing, targetWorksheet }
                            );

                        if (sourceWorksheet != null)
                        {
                            Marshal.ReleaseComObject(sourceWorksheet);
                            sourceWorksheet = null;
                        }
                    }
                }
                finally
                {
                    // Cleanup all created COM objects
                    if (sourceWorksheets != null)
                    {
                        Marshal.ReleaseComObject(sourceWorksheets);
                        sourceWorksheets = null;
                    }

                    if (sourceWorkbook != null)
                    {
                        sourceWorkbook.GetType().InvokeMember(
                            "Close",
                            BindingFlags.InvokeMethod,
                            null,
                            sourceWorkbook,
                            null);
                        Marshal.ReleaseComObject(sourceWorkbook);
                        sourceWorkbook = null;
                    }
                }
            }

            // If overwrite is turned off, and the file exist, the save as line will throw an error
            if (File.Exists(outputFilePath))
            {
                File.Delete(outputFilePath);
            }

            // Save the workbook
            targetWorkbook.GetType().InvokeMember(
                "SaveAs",
                BindingFlags.InvokeMethod,
                null,
                targetWorkbook,
                new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
        }
        finally
        {
            // Cleanup all created COM objects
            if (targetWorksheets != null)
            {
                Marshal.ReleaseComObject(targetWorksheets);
                targetWorksheets = null;
            }

            if (targetWorkbook != null)
            {
                targetWorkbook.GetType().InvokeMember(
                    "Close",
                    BindingFlags.InvokeMethod,
                    null,
                    targetWorkbook,
                    null);
                Marshal.ReleaseComObject(excelWorkbooks);
                excelWorkbooks = null;
            }

            if (excelWorkbooks != null)
            {
                Marshal.ReleaseComObject(excelWorkbooks);
                excelWorkbooks = null;
            }

            if (excelApplication != null)
            {
                excelApplication.GetType().InvokeMember(
                    "Quit",
                    BindingFlags.InvokeMethod,
                    null,
                    excelApplication,
                    null);
                Marshal.ReleaseComObject(excelApplication);
                excelApplication = null;
            }
        }

        // Read target file bytes
        var resultBytes = (File.Exists(outputFilePath))
            ? File.ReadAllBytes(outputFilePath)
            : new byte[] { };

        // Delete working files
        if (File.Exists(outputFilePath))
            File.Delete(outputFilePath);
        foreach (var inputFilePath in convertedFileList.Where(File.Exists))
        {
            File.Delete(inputFilePath);
        }

        Repersonate();

        // Return result
        return resultBytes;
    }

Use ADO connection to select values and use copyFromRecordset Method from range to paste in other workbook.