Create PDF report from generated Excel file (EPPLUS library)

Question

I am using EPPLUS to generate Excel files ... so far so good. Now I have to create the same report, but in PDF format.

Is there a way I can do this? Preferably, I would like to use the Excel file itself, because the datasets that I use to feed Excel files are incomplete ... I make a few SQL queries to get the missing fields ...

Here is what I do to get the generated Excel file:

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

I was just looking for a similar solution and found nothing. The next best thing was to find a commercially free pdf file library in the same way as EPPlus, and the best I found was MigraDoc

Hope this helps!

Answer 2

I also watched MigraDoc. It is also open source and completely free to use. I also found Epplus_Excel in codeplex . It may be worth exploring both options.

Answer 3

I found a possible solution. The creator of the EPPlus package has the PdfReport library.

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

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

There you can see the library. Install pdfreport.core I changed the code a bit to adapt it to your requirements with a working example.

Utility class

 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 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 (modified so that GetBaseDir throws the path to the server)

 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 Method

  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 a folder with fonts and, possibly, images to your solution so that the utilities can find the necessary files.