C# EPPlus合并Excel文件

Question

我想在EPPlus中合并多个C#文件。

我做了以下工作：

using (MemoryStream protocolStream = new MemoryStream())
{
    ExcelPackage pck = new ExcelPackage();
    HashSet<string> wsNames = new HashSet<string>();

    foreach (var file in files)
    {
        ExcelPackage copyPck = new ExcelPackage(new FileInfo(file));
        foreach (var ws in copyPck.Workbook.Worksheets)
        {
            string name = ws.Name;
            int i = 1;
            while (!wsNames.Add(ws.Name))
                name = ws.Name + i++;
            ws.Name = name;
            var copiedws = pck.Workbook.Worksheets.Add(name);
            copiedws.WorksheetXml.LoadXml(ws.WorksheetXml.DocumentElement.OuterXml);
        }
    }
    pck.SaveAs(protocolStream);
    protocolStream.Position = 0;
    using (FileStream fs = new FileStream(resultFile, FileMode.Create))
        protocolStream.CopyTo(fs);
}

但是，我在pck.SaveAs(protocolStream)中得到了以下错误

System.ArgumentOutOfRangeException (在System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument参数中，ExceptionResource资源)在System.Collections.Generic.List1.get_Item(Int32 index) in OfficeOpenXml.ExcelStyleCollection1.get_Item(Int32 PositionID中)

我也尝试过用Worksheet.Copy方法，但我失去了它的样式。

Answer 1

下面是一个示例，通过处理excel源文件中的所有工作表将多个文件合并为一个文件。

var files = new string[] { @"P:\second.xlsx", @"P:\second.xlsx" };

        var resultFile = @"P:\result.xlsx";

        ExcelPackage masterPackage = new ExcelPackage(new FileInfo(@"P:\first.xlsx"));
        foreach (var file in files)
        {
            ExcelPackage pckg = new ExcelPackage(new FileInfo(file));

            foreach (var sheet in pckg.Workbook.Worksheets)
            {
                //check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus

                string workSheetName = sheet.Name;
                foreach (var masterSheet in masterPackage.Workbook.Worksheets)
                {
                    if (sheet.Name == masterSheet.Name)
                    {
                        workSheetName = string.Format("{0}_{1}", workSheetName, DateTime.Now.ToString("yyyyMMddhhssmmm"));
                    }
                }

                //add new sheet
                masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);
            }
        }

        masterPackage.SaveAs(new FileInfo(resultFile));