如何在c#中使用EPPlus将一个excel工作表添加到其他工作簿中

Question

  private static ExcelPackage MergeExcelPackages(ExcelPackage aExcelPackage,
                ExcelPackage bExcelPackage)
     {
     var excelPackage = new ExcelPackage();
     excelPackage.Workbook.Worksheets.Add("AInfo", aExcelPackage.Workbook.Worksheets[1]);
     excelPackage.Workbook.Worksheets.Add("BInfo", bExcelPackage.Workbook.Worksheets[1]);
     return excelPackage;
     }

上面的代码可以在EPPlus 3.1.3.3中工作，但在4.0.4版本中不能工作它抛出以下错误:

System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at OfficeOpenXml.ExcelStyleCollection`1.get_Item(Int32 PositionID)
   at OfficeOpenXml.Style.ExcelStyle.get_XfId()
   at OfficeOpenXml.ExcelStyles.CreateNamedStyle(String name, ExcelStyle Template)
   at OfficeOpenXml.ExcelStyles.CloneStyle(ExcelStyles style, Int32 styleID, Boolean isNamedStyle, Boolean allwaysAdd)
   at OfficeOpenXml.ExcelStyles.CloneStyle(ExcelStyles style, Int32 styleID)
   at OfficeOpenXml.ExcelWorksheets.CloneCells(ExcelWorksheet Copy, ExcelWorksheet added)
   at OfficeOpenXml.ExcelWorksheets.Add(String Name, ExcelWorksheet Copy)

Answer 1

这里有一个完整的方法，我认为它可以实现您的目标。我用的是EPPlus 4.0.4

public bool CreateJobReports(int jobId, string jobName, string destination,
    Dictionary<string, DataTable> reportTables)
{
    try
    {
        var tables = reportTables.Count;
        var worksheets = 0;
        var workBookName = String.Format("JobReports_JobId_{0}", jobId);
        var fullPath = String.Format(@"{0}{1}.xlsx", destination, workBookName);
        var excelWorkbook = new FileInfo(fullPath);
        using (var package = new ExcelPackage(excelWorkbook))
        {
            foreach (var table in reportTables)
            {
                var tableName = table.Key;
                var reportTable = table.Value;
                // Add a new worksheet.
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(tableName);
                // Load the data into the worksheet starting from top left (A1).
                worksheet.Cells["A1"].LoadFromDataTable(reportTable, true, TableStyles.None);
            }
            package.Save();
            worksheets = package.Workbook.Worksheets.Count;
        }
        return worksheets == tables;
    }
    catch (Exception ex)
    {
        var jobInfo = string.Format("JobId: {0}, JobName: {1}. ", jobId, jobName);
        var exception = jobInfo + ex.ToString();
        if (ex.InnerException != null)
        {
            exception = exception + String.Format(". Inner exception: {0}", ex.InnerException.ToString());
        }
        _logger.Fatal(mapInfo + exception);
        throw;
    }
}