EPPlus - 将工作表从模板复制到另一个 excelpackage 不起作用 (C#)

本文关键字：excelpackage 另一个 不起作用 复制 工作 EPPlus | 更新日期: 2023-09-27 18:34:52

我有一个数据集，里面装满了不同的DataTables SQL-Results。某些数据表与 Excel 模板文件连接。所以最后想要有一个 excelfile ，其中包含新工作表和从某个模板复制的工作表的混合

。

这就是为什么我的代码看起来像这样：

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。它工作正常，它在自己的工作表中显示excel文件中所有DataTables的内容，但是当excel文件包含复制的工作表时，会出现两条错误消息，并且复制的工作表未格式化。

Excelfilecorrupt

工作表不可读

EPPlus - 将工作表从模板复制到另一个 excelpackage 不起作用 (C#)

我的折衷方案如下所示：

    /// <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);
        }
    }
相关文章：