MES_Client/TGMES/Core.Mes.Client.Comm/Tool/MultiReportExcelMerger.cs


using System;
using System.IO;
using System.Net;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

public class MultiReportExcelMerger
{

    public string MergeReportsUsingInterop(Dictionary<string, string> reportUrls, string outputPath)
    {
        // 创建临时目录存放下载的文件
        string tempDir = Path.Combine(Path.GetTempPath(), "ExcelMerge_" + Guid.NewGuid().ToString());
        Directory.CreateDirectory(tempDir);

        List<string> tempFiles = new List<string>();
        Application excelApp = null;
        Workbook newWorkbook = null;

        using (WebClient webClient = new WebClient())
        {
            try
            {
                // 下载所有报表到临时文件
                foreach (var report in reportUrls)
                {
                    string url = report.Key;
                    string sheetName = report.Value;

                    // 添加Excel格式参数
                    string excelUrl = url + "&format=excel";

                    // 下载Excel数据
                    byte[] excelData = webClient.DownloadData(excelUrl);

                    // 保存到临时文件
                    string tempFilePath = Path.Combine(tempDir, $"{sheetName}_{Guid.NewGuid().ToString()}.xlsx");
                    File.WriteAllBytes(tempFilePath, excelData);
                    tempFiles.Add(tempFilePath);
                }

                // 使用Excel Interop合并文件
                excelApp = new Application();
                excelApp.Visible = false;
                excelApp.DisplayAlerts = false;

                newWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                // 删除默认创建的空工作表 - 修复版本
                while (newWorkbook.Worksheets.Count > 1)
                {
                    Worksheet sheetToDelete = newWorkbook.Worksheets[1] as Worksheet;
                    if (sheetToDelete != null)
                    {
                        // 执行删除操作
                        sheetToDelete.Delete();
                    }
                    ReleaseComObject(sheetToDelete);
                }

                // 重命名唯一的工作表
                Worksheet firstSheet = (Worksheet)newWorkbook.Worksheets[1];
                firstSheet.Name = "合并报表";
                ReleaseComObject(firstSheet);

                // 合并所有工作表
                foreach (string tempFile in tempFiles)
                {
                    Workbook sourceWorkbook = null;
                    Worksheet sourceSheet = null;

                    try
                    {
                        sourceWorkbook = excelApp.Workbooks.Open(tempFile);
                        sourceSheet = (Worksheet)sourceWorkbook.Worksheets[1]; // 获取第一个工作表

                        // 获取新工作簿中的最后一个工作表作为复制目标
                        Worksheet lastSheetInNew = (Worksheet)newWorkbook.Worksheets[newWorkbook.Worksheets.Count];

                        // 复制工作表到新工作簿
                        sourceSheet.Copy(Type.Missing, lastSheetInNew);
                        ReleaseComObject(lastSheetInNew);

                        // 重命名复制的工作表
                        string fileName = Path.GetFileNameWithoutExtension(tempFile);
                        string sheetName = fileName.Split('_')[0];

                        Worksheet newlyCopiedSheet = (Worksheet)newWorkbook.Worksheets[newWorkbook.Worksheets.Count];
                        newlyCopiedSheet.Name = GetValidWorksheetName(sheetName, newWorkbook);
                        ReleaseComObject(newlyCopiedSheet);
                    }
                    finally
                    {
                        // 确保释放COM对象
                        if (sourceSheet != null)
                        {
                            ReleaseComObject(sourceSheet);
                        }
                        if (sourceWorkbook != null)
                        {
                            sourceWorkbook.Close(false);
                            ReleaseComObject(sourceWorkbook);
                        }
                    }
                }

                // 删除最初创建的空白工作表
                try
                {
                    Worksheet mergeSheet = (Worksheet)newWorkbook.Worksheets["合并报表"];
                    mergeSheet.Delete();
                    ReleaseComObject(mergeSheet);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"删除初始工作表时出错: {ex.Message}");
                    // 继续执行，这个错误不影响主要功能
                }

                // 保存合并后的工作簿
                newWorkbook.SaveAs(outputPath);
                newWorkbook.Close(false);
                ReleaseComObject(newWorkbook);
                newWorkbook = null;

                return "成功";
            }
            catch (Exception ex)
            {
                return $"出错: {ex.Message}";
                throw;
            }
            finally
            {
                // 确保正确释放所有COM对象
                if (newWorkbook != null)
                {
                    try
                    {
                        newWorkbook.Close(false);
                        ReleaseComObject(newWorkbook);
                    }
                    catch { }
                }

                // 确保Excel进程正确关闭
                if (excelApp != null)
                {
                    try
                    {
                        excelApp.Quit();
                        ReleaseComObject(excelApp);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"关闭Excel应用时出错: {ex.Message}");
                    }
                }

                // 清理临时文件
                try
                {
                    foreach (string tempFile in tempFiles)
                    {
                        if (File.Exists(tempFile))
                            File.Delete(tempFile);
                    }
                    if (Directory.Exists(tempDir))
                        Directory.Delete(tempDir, true);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"清理临时文件时出错: {ex.Message}");
                }
            }
        }
    }

    // 辅助方法：正确释放COM对象
    private void ReleaseComObject(object obj)
    {
        try
        {
            if (obj != null && System.Runtime.InteropServices.Marshal.IsComObject(obj))
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"释放COM对象时出错: {ex.Message}");
        }
        finally
        {
            obj = null;
            // 在.NET 4.5中谨慎使用GC，避免过度调用
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

    // 辅助方法：确保工作表名称有效
    private string GetValidWorksheetName(string proposedName, Workbook workbook)
    {
        string validName = proposedName ?? "Sheet";

        // 截断超过31个字符的名称
        if (validName.Length > 31)
        {
            validName = validName.Substring(0, 31);
        }

        // 移除无效字符
        char[] invalidChars = { '\\', '/', '?', '*', '[', ']', ':' };
        foreach (char c in invalidChars)
        {
            validName = validName.Replace(c.ToString(), "");
        }

        // 确保名称不为空
        if (string.IsNullOrEmpty(validName))
        {
            validName = "Sheet";
        }

        // 检查是否与现有工作表名称冲突
        int counter = 1;
        string originalName = validName;

        while (WorksheetExists(validName, workbook))
        {
            validName = $"{originalName}_{counter}";
            counter++;

            // 如果名称太长，截断
            if (validName.Length > 31)
            {
                validName = originalName.Substring(0, Math.Max(0, 28 - counter.ToString().Length)) + $"_{counter}";
            }
        }

        return validName;
    }

    // 辅助方法：检查工作表名称是否已存在
    private bool WorksheetExists(string sheetName, Workbook workbook)
    {
        try
        {
            foreach (Worksheet sheet in workbook.Worksheets)
            {
                if (sheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
                {
                    ReleaseComObject(sheet);
                    return true;
                }
                ReleaseComObject(sheet);
            }
            return false;
        }
        catch
        {
            return false;
        }
    }
}