Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

asp.net

epplus

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();
like image 927
Joao Victor Avatar asked Sep 20 '12 11:09

Joao Victor


People also ask

Is there a way to get a PDF report for epplus?

I have found a possible solution. The creator of the EPPlus Package has a PdfReport Library. 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.

How do I fetch data from a DataTable using epplus?

We fetch a DataTable object using a custom Data Provider method. We create a ExcelPackage object, which is the main container used by EPPlus for the XLSX file. We add a ExcelWorksheet inside the ExcelPackage, which is the worksheet we’ll put the data in.

Is it possible to generate a PDF from a cell value?

Based on reading through this thread, I've been able to compose a macro that, based on a cell value (controlled by a dropdown), select a page in the workbook and save and generate a PDF. So, if a specific cell value = 1, a specific page in the workbook would be selected and a PDF is saved and generated.

What is epplus library in Excel?

A library to manage Excel spreadsheets. EPPlus is a .NET library, which reads and writes Excel 2007/2010 or higher files, using Open Office XML format. It supports .XLSX, .XLSM Excel file format.


1 Answers

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.

like image 51
blfuentes Avatar answered Oct 17 '22 19:10

blfuentes