c# - 从生成的 Excel 文件生成 PDF 报告(EPPLUS 库)

Question

我正在使用 EPPLUS 生成 Excel 文件……到目前为止，还不错。现在我必须生成相同的报告，但格式为 PDF。

有什么方法可以完成这个吗？最好，我想使用 Excel 文件本身，因为我用来提供 Excel 文件的数据集不完整...我执行一些 SQL 查询以获取缺少的字段...

这是我获取生成的 Excel 文件的方法:

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=RelatorioTempoMediano.xlsx");
Response.BinaryWrite(p.GetAsByteArray());
Response.End();

Answer 1

我找到了一个可能的解决方案。EPPlus 包的创建者有一个 PdfReport 库。

https://github.com/VahidN/EPPlus.Core/issues/8

https://github.com/VahidN/PdfReport.Core/blob/master/src/PdfRpt.Core.FunctionalTests/ExcelToPdfReport.cs

在那里你可以看到图书馆。安装 pdfreport.core我对代码进行了一些修改，以通过运行示例使其适应您的要求。

实用类

public class Utilities
{
    public class ExcelDataReaderDataSource : IDataSource
    {
        private readonly string _filePath;
        private readonly string _worksheet;

        public ExcelDataReaderDataSource(string filePath, string worksheet)
        {
            _filePath = filePath;
            _worksheet = worksheet;
        }

        public IEnumerable<IList<CellData>> Rows()
        {
            var fileInfo = new FileInfo(_filePath);
            if (!fileInfo.Exists)
            {
                throw new FileNotFoundException($"{_filePath} file not found.");
            }

            using (var package = new ExcelPackage(fileInfo))
            {
                var worksheet = package.Workbook.Worksheets[_worksheet];
                var startCell = worksheet.Dimension.Start;
                var endCell = worksheet.Dimension.End;

                for (var row = startCell.Row + 1; row < endCell.Row + 1; row++)
                {
                    var i = 0;
                    var result = new List<CellData>();
                    for (var col = startCell.Column; col <= endCell.Column; col++)
                    {
                        var pdfCellData = new CellData
                        {
                            PropertyName = worksheet.Cells[1, col].Value.ToString(),
                            PropertyValue = worksheet.Cells[row, col].Value,
                            PropertyIndex = i++
                        };
                        result.Add(pdfCellData);
                    }
                    yield return result;
                }
            }
        }
    }

    public static class ExcelUtils
    {
        public static IList<string> GetColumns(string filePath, string excelWorksheet)
        {
            var fileInfo = new FileInfo(filePath);
            if (!fileInfo.Exists)
            {
                throw new FileNotFoundException($"{filePath} file not found.");
            }

            var columns = new List<string>();
            using (var package = new ExcelPackage(fileInfo))
            {
                var worksheet = package.Workbook.Worksheets[excelWorksheet];
                var startCell = worksheet.Dimension.Start;
                var endCell = worksheet.Dimension.End;

                for (int col = startCell.Column; col <= endCell.Column; col++)
                {
                    var colHeader = worksheet.Cells[1, col].Value.ToString();
                    columns.Add(colHeader);
                }
            }
            return columns;
        }
    }

    public static IPdfReportData CreateExcelToPdfReport(string filePath, string excelWorksheet)
    {
        return new PdfReport().DocumentPreferences(doc =>
        {
            doc.RunDirection(PdfRunDirection.LeftToRight);
            doc.Orientation(PageOrientation.Portrait);
            doc.PageSize(PdfPageSize.A4);
            doc.DocumentMetadata(new DocumentMetadata { Author = "Vahid", Application = "PdfRpt", Keywords = "Test", Subject = "Test Rpt", Title = "Test" });
            doc.Compression(new CompressionSettings
            {
                EnableCompression = true,
                EnableFullCompression = true
            });
        })
            .DefaultFonts(fonts =>
            {
                fonts.Path(TestUtils.GetVerdanaFontPath(),
                    TestUtils.GetTahomaFontPath());
                fonts.Size(9);
                fonts.Color(System.Drawing.Color.Black);
            })
            .PagesFooter(footer =>
            {
                footer.DefaultFooter(DateTime.Now.ToString("MM/dd/yyyy"));
            })
            .PagesHeader(header =>
            {
                header.CacheHeader(cache: true); // It's a default setting to improve the performance.
                header.DefaultHeader(defaultHeader =>
                {
                    defaultHeader.RunDirection(PdfRunDirection.LeftToRight);
                    defaultHeader.ImagePath(TestUtils.GetImagePath("01.png"));
                    defaultHeader.Message("Excel To Pdf Report");
                });
            })
            .MainTableTemplate(template =>
            {
                template.BasicTemplate(BasicTemplate.ClassicTemplate);
            })
            .MainTablePreferences(table =>
            {
                table.ColumnsWidthsType(TableColumnWidthType.Relative);
                table.MultipleColumnsPerPage(new MultipleColumnsPerPage
                {
                    ColumnsGap = 7,
                    ColumnsPerPage = 3,
                    ColumnsWidth = 170,
                    IsRightToLeft = false,
                    TopMargin = 7
                });
            })
            .MainTableDataSource(dataSource =>
            {
                dataSource.CustomDataSource(() => new ExcelDataReaderDataSource(filePath, excelWorksheet));
            })
            .MainTableColumns(columns =>
            {
                columns.AddColumn(column =>
                {
                    column.PropertyName("rowNo");
                    column.IsRowNumber(true);
                    column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                    column.IsVisible(true);
                    column.Order(0);
                    column.Width(1);
                    column.HeaderCell("#");
                });

                var order = 1;
                foreach (var columnInfo in ExcelUtils.GetColumns(filePath, excelWorksheet))
                {
                    columns.AddColumn(column =>
                    {
                        column.PropertyName(columnInfo);
                        column.CellsHorizontalAlignment(HorizontalAlignment.Center);
                        column.IsVisible(true);
                        column.Order(order++);
                        column.Width(1);
                        column.HeaderCell(columnInfo);
                    });
                }
            })
            .MainTableEvents(events =>
            {
                events.DataSourceIsEmpty(message: "There is no data available to display.");
            })
            .Generate(data => data.AsPdfFile(TestUtils.GetOutputFileName()));
    }
}

TestUtils(修改后 GetBaseDir 抛出服务器映射路径)

public static class TestUtils
{
    public static string GetBaseDir()
    {
        return HttpContext.Current.Server.MapPath("~/");
    }

    public static string GetImagePath(string fileName)
    {

        return Path.Combine(GetBaseDir(), "Images", fileName);
    }

    public static string GetDataFilePath(string fileName)
    {

        return Path.Combine(GetBaseDir(), "Data", fileName);
    }

    [MethodImpl(MethodImplOptions.NoInlining)]
    public static string GetOutputFileName([CallerMemberName] string methodName = null)
    {
        return Path.Combine(GetOutputFolder(), $"{methodName}.pdf");
    }

    public static string GetOutputFolder()
    {
        var dir = Path.Combine(GetBaseDir(), "App_Data", "out");
        if (!Directory.Exists(dir))
        {
            Directory.CreateDirectory(dir);
        }
        return dir;
    }

    public static string GetWingdingFontPath()
    {
        return Path.Combine(GetBaseDir(), "fonts", "wingding.ttf");
    }

    public static string GetTahomaFontPath()
    {
        return Path.Combine(GetBaseDir(), "fonts", "tahoma.ttf");
    }

    public static string GetVerdanaFontPath()
    {
        return Path.Combine(GetBaseDir(), "fonts", "verdana.ttf");
    }

    public static Font GetUnicodeFont(
                string fontName, string fontFilePath, float size, int style, BaseColor color)
    {
        if (!FontFactory.IsRegistered(fontName))
        {
            FontFactory.Register(fontFilePath);
        }
        return FontFactory.GetFont(fontName, BaseFont.IDENTITY_H, BaseFont.EMBEDDED, size, style, color);
    }

    public static void VerifyPdfFileIsReadable(byte[] file)
    {
        PdfReader reader = null;
        try
        {
            reader = new PdfReader(file);
            var author = reader.Info["Author"] as string;
            if (string.IsNullOrWhiteSpace(author) || !author.Equals("Vahid"))
            {
                throw new InvalidPdfException("This is not a valid PDF file.");
            }
        }
        finally
        {
            reader?.Close();
        }
    }

    public static void VerifyPdfFileIsReadable(string filePath)
    {
        VerifyPdfFileIsReadable(File.ReadAllBytes(filePath));
    }
}

Controller 方法

    public ActionResult DownloadFile()
    {
        var report = Utilities.Utilities.CreateExcelToPdfReport(
            filePath: Server.MapPath("~/App_Data/Financial Sample.xlsx"),
            excelWorksheet: "Sheet1");

        Utilities.TestUtils.VerifyPdfFileIsReadable(report.FileName);

        string filename = Path.GetFileName(report.FileName);
        string filepath = report.FileName;
        byte[] filedata = System.IO.File.ReadAllBytes(filepath);
        string contentType = MimeMapping.GetMimeMapping(filepath);

        var cd = new System.Net.Mime.ContentDisposition
        {
            FileName = filename,
            Inline = true,
        };

        Response.AppendHeader("Content-Disposition", cd.ToString());

        return File(filedata, contentType);
    }

You will need to add the fonts and maybe images folder to your solution, so the utilites can find the required files.