C＃EPPlus合并Excel文件 [英] C# EPPlus merge Excel Files

问题描述

我想在C＃中将多个Excel文件与EPPlus合并。

I want to merge multiple Excel files with EPPlus in 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.ArgumentOutOfRangeException

在
中System.ThrowHelper.ThrowArgumentOutOfRangeException（ExceptionArgument
参数，ExceptionResource资源）在
中System.Collections.Generic.List 1.get_Item（Int32索引）在$ b中$ b OfficeOpenXml.ExcelStyleCollection 1.get_Item（Int32 PositionID）

in System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) in System.Collections.Generic.List1.get_Item(Int32 index) in OfficeOpenXml.ExcelStyleCollection1.get_Item(Int32 PositionID)

我也用 Worksheet.Copy 方法，但我失去了样式。

I also tried it with the Worksheet.Copy method, but I lose the styling with it.

推荐答案

这里是合并多个示例的示例

Here is an example of merging several files into one by coping all worksheets from source excel files.

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

这篇关于C＃EPPlus合并Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！