Generate a PDF report from a generated Excel file (EPPLUS Library)

Question

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

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

This 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 have found a possible solution. The creator of the EPPlus Package has a 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 the pdfreport.core I have modified the code a little bit to adapt it to your requirements with a running sample.

Utilities 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'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 (modified so the GetBaseDir throws a server mapped path)

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 the fonts and maybe images folder to your solution, so the utilites can find the required files.