当前位置： 文江博客话题详情

将多个Excel文件合并为一个

发布于 2024-12-02 18:02:33 字数 13458 浏览 0 评论 0原文

问题是，我生成了几个 excel 文件，每个文件都有 1 个选项卡（但这并不是说有些文件将来不会有更多）。

我需要编写的是一个可以打开每个文件并将其所有工作表（选项卡）复制到新文件中的过程。

最后，该新文件应包含所有其他文件的工作表。

目前，我创建了以下内容来完成 Excel 格式之间的转换。

我不太确定从这里去哪里，我没有任何用来创建这个的来源......并且不太确定对象模型（为了将选项卡复制到新文件）或者我将面临的问题，并确保我清理所有内容。

        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;
            }
        }

编辑：这段代码几乎可以工作，问题出在应该实际执行复制的行上...

    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;
    }

我收到错误System.Runtime.InteropServices.COMException：Worksheet类的复制方法失败，其中没有多大帮助...我不知道为什么失败...

原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

枕花眠 2024-12-09 18:02:33

这似乎有效，只需要添加一些清理来删除最初创建的空白工作表，然后在保存之前激活文件中的第一个工作表

    [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;
    }

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;
    }
回复 原文
素衣风尘叹 2024-12-09 18:02:33

使用 ADO 连接选择值并使用 copyFromRecordset 方法从范围粘贴到其他工作簿中。

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

回复 原文
~没有更多了~

关于作者

深爱成瘾

暂无简介

0 文章
0 评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

已经忘了多久

文章 0 评论 0

15867725375

文章 0 评论 0

LonelySnow

文章 0 评论 0

走过海棠暮

文章 0 评论 0

轻许诺言

文章 0 评论 0

信馬由缰

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文