EPPlus - copy worksheet from template to another e

Question

I'm having a DataSet filled with different DataTables SQL-Results. Some of the DataTables are connected with a Excel-Template-File. So in the end in want to have an excelfile with a mixture of new worksheets and copied worksheets from some template.

That's why my code looks like this:

public void CopyResultToExcelFileWithTemplate(DataSet sourceResult, string exportFilePath, string sourceName, string templateExcelFilePath, string sheetName = null)
{
    var excelFile = new FileInfo(exportFilePath);
    var templateFile = new FileInfo(templateExcelFilePath);
    if (string.IsNullOrEmpty(sheetName))
    {
        sheetName = sourceName;
    }

    // Open and get worksheets from template
    using (var template = new ExcelPackage(templateFile))
    {
        var excelWorksheets = template.Workbook.Worksheets;
        var sheetCount = 1;
        foreach (DataTable resultTable in sourceResult.Tables)
        {
            var proposedSheetName = sourceResult.Tables.Count == 1 ? sheetName : string.Format("{0}_{1}", sheetName, sheetCount);
            var currentWorksheet = excelWorksheets.FirstOrDefault(w => string.Equals(w.Name, proposedSheetName, StringComparison.CurrentCultureIgnoreCase)) ?? excelWorksheets.Add(proposedSheetName);
            FillWorksheetWithDataTableContent(currentWorksheet, resultTable);
            using (var excelToExport = new ExcelPackage(excelFile))
            {
                excelToExport.Workbook.Worksheets.Add(currentWorksheet.Name, currentWorksheet);
                excelToExport.Save();
            }
            sheetCount++;
        }
    }
}

public void CopyResultToExcelFile(DataSet resultSet, string exportFilePath, string sourceName, string sheetName = null)
{
    if (string.IsNullOrEmpty(sheetName))
    {
        sheetName = sourceName;
    }
    var excelToExport = new FileInfo(exportFilePath);
    using (var excelPackage = new ExcelPackage(excelToExport))
    {
        var sheetCount = 1;
        foreach (DataTable resultTable in resultSet.Tables)
        {
            var proposedSheetName = resultSet.Tables.Count == 1 ? sheetName : string.Format("{0}_{1}", sourceName, sheetCount);
            var worksheet = excelPackage.Workbook.Worksheets.Add(proposedSheetName);
            FillWorksheetWithDataTableContent(worksheet, resultTable);
            sheetCount++;
        }
        excelPackage.Save();
    }
}

So I fill the temporary created excelfile with a combination of worksheet-copys from a template and with new worksheets. It works fine, it shows the content of all DataTables in the excelfile in their own worksheet, BUT when the excelfile contains copied worksheets there are two error message appearing and the copied worksheets arent formatted.

excelfilecorrupt

worksheetunreadable

Answer 1

My compromise looks like this:

    /// <summary>
    /// Creates an temporary excel-file for the report and returns it as byte-array
    /// </summary>
    /// <param name="reportResults"></param>
    /// <param name="reportDetails"></param>
    /// <returns></returns>
    private static byte[] GetReportExcelFile(Dictionary<string, DataSet> reportResults, ReportDetails reportDetails)
    {
        var tmpGuid = Guid.NewGuid();
        var tempFolderForExcelFile = $"{DefaultFolderForExcelFiles}{tmpGuid}";
        var exportFilePath = $"{tempFolderForExcelFile}\\{DefaultExcelFileName}";
        var templateFilePath = string.Empty;

        try
        {
            Cleanup.DeleteOldFiles(DefaultFolderForExcelFiles, 5, false, true);
            if (!Directory.Exists(tempFolderForExcelFile))
            {
                Directory.CreateDirectory(tempFolderForExcelFile);
            }

            var excelExportManager = new ExcelExportManager();

            if (!string.IsNullOrEmpty(reportDetails.Template))
            {
                // Create resultfile from template
                exportFilePath = $"{tempFolderForExcelFile}\\OutputReport_{reportDetails.Template}";
                templateFilePath = $"{tempFolderForExcelFile}\\Template_{reportDetails.Template}";
                File.WriteAllBytes(templateFilePath, reportDetails.TemplateContent);
            }

            excelExportManager.AddDataSetToExcelFile(reportResults, exportFilePath, templateFilePath);

            using (var filstr = File.Open(exportFilePath, FileMode.Open))
            {
                using (var ms = new MemoryStream())
                {
                    ms.SetLength(filstr.Length);
                    filstr.Read(ms.GetBuffer(), 0, (int)filstr.Length);
                    ms.Flush();
                    return ms.ToArray();
                }
            }
        }
        catch (Exception ex)
        {
            throw new ReportingException(ex.Message);
        }
        finally
        {
            if (!string.IsNullOrEmpty(tempFolderForExcelFile))
            {
                Directory.Delete(tempFolderForExcelFile, true);
            }
        }
    }

    public void AddDataSetToExcelFile(Dictionary<string, DataSet> resultSet, string exportFilePath, string templateFilePath = null)
    {
        var excelToExport = new FileInfo(exportFilePath);
        FileInfo template = null;
        if (!string.IsNullOrEmpty(templateFilePath))
        {
            template = new FileInfo(templateFilePath);
        }
        using (var excelPackage = string.IsNullOrEmpty(templateFilePath) ? new ExcelPackage(excelToExport) : new ExcelPackage(excelToExport, template))
        {
            foreach (var result in resultSet)
            {
                var sheetCount = 1;
                var sourceName = result.Key;
                foreach (DataTable resultTable in result.Value.Tables)
                {
                    var proposedSheetName = result.Value.Tables.Count == 1 ? sourceName : string.Format("{0}_{1}", sourceName, sheetCount);
                    var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault(ws => ws.Name == proposedSheetName);
                    if (worksheet == null)
                    {
                        worksheet = excelPackage.Workbook.Worksheets.Add(proposedSheetName);
                        FillWorksheetWithDataTableContent(ref worksheet, resultTable, TableStyles.Medium27);
                    }
                    else
                    {
                        FillWorksheetWithDataTableContent(ref worksheet, resultTable);
                    }
                    sheetCount++;
                }
            }
            excelPackage.SaveAs(excelToExport);
        }
    }