Generate a PDF report from a generated Excel file (EPPLUS Library)
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
I have found a possible solution. The creator of the EPPlus Package has a PdfReport Library.
https://github.com/VahidN/EPPlus.Core/issues/8
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.