Generate PDF reports from SQL Server data using iText 7 Core library in ASP.NET...
source link: https://blog.medhat.ca/2021/12/generate-pdf-reports-from-sql-server.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Generate PDF reports from SQL Server data using iText 7 Core library in ASP.NET Core MVC 6.0
PDF stands for "Portable Document Format". It is, indeed, the standard for exchanging formatted documents on the internet. PDF documents are read by Adobe Acrobat Reader, most browsers, and even some popular word processors like Microsoft Word. It is a common used-case to generate PDF documents from live data. In this tutorial, I shall show you how you can easily generate a PDF report in an ASP.NET Core MVC app and display data that originates from the sample SQL Server database named Northwind. We shall use the iText 7 library to accomplish this task.
Source Code: https://github.com/medhatelmasry/MvcPdfDemo
The environment I am using is:
- Windows 11
- Docker Desktop for Windows
- .NET version 6.0.100
- Visual Studio Code
Start Northwind database in a Docker container
Project setup
dotnet new mvc -f net6.0 -o MvcPdfDemo
Change directory into the new folder and open the project inside VS Code with the following commands:
cd MvcPdfDemo
code .
We will need to install an Entity Framework command-line utility. If you have not done so already, install dotnet-ef with this command:
dotnet tool install –g dotnet-ef
It does not hurt to upgrade this tool to the latest version with:
dotnet tool update -g dotnet-ef
Also, from within the root folder of your project, add some SQL-Server and Entity Framework related packages with the following terminal-window commands:
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
Generating PDF report
private readonly NorthwindContext _northwindContext;
public HomeController(ILogger<HomeController> logger,NorthwindContext northwindContext) {_logger = logger;_northwindContext = northwindContext;
private Table GetPdfTable() {// TableTable table = new Table(4, false);// HeadingsCell cellProductId = new Cell(1, 1).SetBackgroundColor(ColorConstants.LIGHT_GRAY).SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph("Product ID"));Cell cellProductName = new Cell(1, 1).SetBackgroundColor(ColorConstants.LIGHT_GRAY).SetTextAlignment(TextAlignment.LEFT).Add(new Paragraph("Product Name"));Cell cellQuantity = new Cell(1, 1).SetBackgroundColor(ColorConstants.LIGHT_GRAY).SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph("Quantity"));Cell cellUnitPrice = new Cell(1, 1).SetBackgroundColor(ColorConstants.LIGHT_GRAY).SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph("Unit Price"));table.AddCell(cellProductId);table.AddCell(cellProductName);table.AddCell(cellQuantity);table.AddCell(cellUnitPrice);var qry = _northwindContext.Products.Select(_ => new {_.ProductId,_.ProductName,_.UnitPrice,_.UnitsInStockforeach (var item in qry) {Cell cId = new Cell(1, 1).SetTextAlignment(TextAlignment.CENTER).Add(new Paragraph(item.ProductId.ToString()));Cell cName = new Cell(1, 1).SetTextAlignment(TextAlignment.LEFT).Add(new Paragraph(item.ProductName));Cell cQty = new Cell(1, 1).SetTextAlignment(TextAlignment.RIGHT).Add(new Paragraph(item.UnitsInStock.ToString()));Cell cPrice = new Cell(1, 1).SetTextAlignment(TextAlignment.RIGHT).Add(new Paragraph(String.Format("{0:C2}", item.UnitPrice)));table.AddCell(cId);table.AddCell(cName);table.AddCell(cQty);table.AddCell(cPrice);return table;
- The heading of the table is created. There will be four columns with titles: Product ID, Product Name, Quantity and Unit Price
- Using Entity Framework, we make a query that reads all the products from the Northwind database.
- We iterate through each item in the query and add rows of data to the table
Our next task is to add an Action method that uses the above GetPdfTable() helper method and returns a PDF document. Add the following Report() action method:
public IActionResult Report() {MemoryStream ms = new MemoryStream();PdfWriter writer = new PdfWriter(ms);PdfDocument pdfDoc = new PdfDocument(writer);Document document = new Document(pdfDoc, PageSize.A4, false);writer.SetCloseStream(false);Paragraph header = new Paragraph("Northwind Products").SetTextAlignment(TextAlignment.CENTER).SetFontSize(20);document.Add(header);Paragraph subheader = new Paragraph(DateTime.Now.ToShortDateString()).SetTextAlignment(TextAlignment.CENTER).SetFontSize(15);document.Add(subheader);// empty linedocument.Add(new Paragraph(""));// Line separatorLineSeparator ls = new LineSeparator(new SolidLine());document.Add(ls);// empty linedocument.Add(new Paragraph(""));// Add table containing datadocument.Add(GetPdfTable());// Page Numbersint n = pdfDoc.GetNumberOfPages();for (int i = 1; i <= n; i++) {document.ShowTextAligned(new Paragraph(String.Format("Page " + i + " of " + n)),559, 806, i, TextAlignment.RIGHT,VerticalAlignment.TOP, 0);document.Close();byte[] byteInfo = ms.ToArray();ms.Write(byteInfo, 0, byteInfo.Length);ms.Position = 0;FileStreamResult fileStreamResult = new FileStreamResult(ms, "application/pdf");//Uncomment this to return the file as a download//fileStreamResult.FileDownloadName = "NorthwindProducts.pdf";return fileStreamResult;
What does the above code do?
- The first five lines in the Report() action method sets up all the objects that are needed to generate a PDF document.
- A header with title "Northwind Products" is placed at the top of the report - center aligned.
- A sub-header with the current date is placed under the heading - also center aligned.
- This is followed by an empty line, a solid-line, and another empty line.
- The table containing product data is then displayed.
- Paging is added to the top right-side of each page
- Finally, the report is streamed down to the browser.
Let us add a menu item to the navigation of our web app. Open Views/Shared/_Layout.cshtml and add the following markup code to the bottom of the <ul> .... </ul> navigation block:
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Report">Products PDF</a>
</li>
At this stage, let's run our web app and verify that we are indeed able to read data from the Northwind database and subsequently generate a PDF report. Run your application with:
dotnet watch run
Point your browser to https://localhost:7052
NOTE: you will need to adjust the port number to suit your environment.
This is what the home page looks like:
Click on "Products PDF". You should soon after see the PDF document being generated in your browser:Conclusion
I found the iText 7 library pretty straight forward when it comes to generating PDF documents. You can learn more at https://kb.itextpdf.com/home/it7kb/ebooks/itext-7-jump-start-tutorial-for-net.
I hope this provides you with a respectable starting point for generating PDF reports from your ASP.NET MVC web apps.
Recommend
-
20
OpenPDF is an open source Java library for PDF files OpenPDF is a Java library for creating and editing PDF files with a LGPL and MPL open source license. OpenPDF is the LGPL/MPL open source successor of iText,...
-
6
Creating PDF Reports with iText 7 in Java Java • Nov 27, 2020 I have b...
-
15
Adding an image to the Pdf file using Itext Pdf advertisements I am using iText to generate Pdf. But when I'm trying to add images in the pdf,...
-
5
Improve Article Fabric.js Itext objectCaching PropertyLast Updated : 08 Feb, 2021Fabric.js is a JavaScript library that is used to work with
-
6
V2EX › Java Java ITEXT 对 pdf 文本部分文本进行隐藏处理求助 yiqunz · 7 小时 3 分钟前 · 180...
-
50
Generate PDF reports from API data using iText 7 Core library in ASP.NET Razor Pages 6.0 PDF stands for "Portable Document Format". It is, indeed, the standard for exchanging formatted documents on the internet. PDF documents ar...
-
3
Preface The iText 7 is a powerful library for PDF manipulation. This article is the third one in the dedicated series to the iText library. The previous articles in this series a...
-
9
Generate Dynamic PDF Reports from an HTML Template Using C#Generating PDF documents from the scratch is always a difficult process and has the following disadvantages:It is time-consuming.It...
-
6
0327 - iText 使用 ChatGPT 进行翻译 ...
-
5
0809 - iText 被下回国区 App Store ...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK