1

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...

Improve this question
7
  • possible duplicate of Merge Excel Files Into One Commented Sep 1, 2011 at 14:28
  • @Davide Piras, I'm not using the Interop library, nor a tool. I need to do this in the same manner I am currently using to convert file types. Commented Sep 1, 2011 at 14:33
  • what type of object is excelApplication? to me it looks like the interop library... sorry about that. Commented Sep 1, 2011 at 14:35
  • he is reflecting all types. but he handles all like interop Commented Sep 1, 2011 at 14:38
  • I can't remember the reasoning for doing it this way...but I know there was one...it might have been because we instantiate excel on a different server. Commented Sep 1, 2011 at 14:45

2 Answers 2

Reset to default
2

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;
    }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.