EPPlus-将工作表从模板复制到另一个excelpackage不起作用（C＃） [英] EPPlus - copy worksheet from template to another excelpackage doesnt work (C#)

问题描述

我有一个充满不同DataTables SQL结果的DataSet。某些数据表与Excel模板文件连接。所以最终想要有一个包含新工作表和从某些模板复制的工作表的excelfile。

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

所以我将临时创建的excelfile填充为工作表-从模板复制并包含新的工作表。它工作正常，可以在其自己的工作表中显示excelfile中所有数据表的内容，但是当excelfile包含复制的工作表时，会出现两个错误消息，并且复制的工作表没有格式。

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

工作表不可读

推荐答案

我的妥协如下：

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

这篇关于EPPlus-将工作表从模板复制到另一个excelpackage不起作用（C＃）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！