ITextSharp Download PDF Report from database in ASPNet

In this article I will explain with an example, how to generate and download PDF report from database using iTextSharp in ASP.Net with C# and VB.Net.

Download iTextSharp Library

You can download the iTextSharp library from the following link.

iTextSharp

Note: You will need to add the reference of iTextSharp library in your project.

Database

For this article I am making use of the Microsoft’s Northwind Database. You can download it from here.

Download and install Northwind Database

HTML Markup

The HTML Markup consist of following controls:

DropDownList – For selecting name of employee whose report will be generated.

Button – For generating and downloading PDF report.

<asp:DropDownList ID="ddlEmployees" runat="server"></asp:DropDownList>

<asp:Button ID="btnReport" runat="server" Text="Generate Report" OnClick="GenerateReport" />

Namespaces

You will need to import the following namespaces.

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

using iTextSharp.text;

using iTextSharp.text.pdf;

VB.Net

Imports System.Data

Imports System.Configuration

Imports System.Data.SqlClient

Imports iTextSharp.text

Imports iTextSharp.text.pdf

Populating the Employees DropDownList

Inside the Page Load event handler, the DropDownList is populated with records from Employees Table of Northwind Database.

Note: GetData is a generic function to execute SELECT querie and returns DataTable.

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

this.BindEmployeesDropDown();

}

private void BindEmployeesDropDown()

{

ddlEmployees.DataSource = this.GetData("SELECT EmployeeId, (FirstName + ' ' + LastName) Name FROM Employees");

ddlEmployees.DataTextField = "Name";

ddlEmployees.DataValueField = "EmployeeId";

ddlEmployees.DataBind();

}

private DataTable GetData(string query)

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlDataAdapter sda = new SqlDataAdapter(query, con))

{

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

return dt;

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

If Not Me.IsPostBack Then

Me.BindEmployeesDropDown()

End If

End Sub

Private Sub BindEmployeesDropDown()

ddlEmployees.DataSource = Me.GetData("SELECT EmployeeId, (FirstName + ' ' + LastName) Name FROM Employees")

ddlEmployees.DataTextField = "Name"

ddlEmployees.DataValueField = "EmployeeId"

ddlEmployees.DataBind()

End Sub

Private Function GetData(query As String) As DataTable

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using sda As New SqlDataAdapter(query, con)

Using dt As New DataTable()

sda.Fill(dt)

Return dt

End Using

End Function

Generating PDF Report using iTextSharp

Inside the GenerateReport event handler, the record of the Employee report to be generated is fetched based on the Employee ID from the DropDownList.

Then, Header of the report with Logo and name of the company is created and a Horizontal line is drawn which separates out the Header with the Body.

Now, using the Employee record PDF Table is generated and added to an iTextSharp PDF document. The Body of the report contains the Employee details like Photo, Name, Designation and other details.

Finally, the iTextSharp PDF document is downloaded as PDF using Response Stream.

Note: For more details on how to download file in ASP.Net, please refer my article Download File in ASP.Net using C# and VB.Net.

protected void GenerateReport(object sender, EventArgs e)

{

DataRow dr = this.GetData("SELECT * FROM EmployeesWHERE EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows[0];

Document document = new Document(PageSize.A4, 88f, 88f, 10f, 10f);

using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())

{

PdfWriter writer = PdfWriter.GetInstance(document,memoryStream);

Phrase phrase = null;

PdfPCell cell = null;

PdfPTable table = null;

BaseColor color = null;

document.Open();

//Header Table

table = new PdfPTable(2);

table.TotalWidth = 500f;

table.LockedWidth = true;

table.SetWidths(new float[] { 0.3f, 0.7f });

//Company Logo

cell = this.ImageCell("~/images/northwindlogo.gif", 30f, PdfPCell.ALIGN_CENTER);

table.AddCell(cell);

//Company Name and Address

phrase = new Phrase();

phrase.Add(new Chunk("Microsoft Northwind Traders Company\n\n", FontFactory.GetFont("Arial", 16, Font.BOLD, BaseColor.RED)));

phrase.Add(new Chunk("107, Park site,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

phrase.Add(new Chunk("Salt Lake Road,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

phrase.Add(new Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

cell = this.PhraseCell(phrase, PdfPCell.ALIGN_LEFT);

cell.VerticalAlignment = PdfPCell.ALIGN_TOP;

table.AddCell(cell);

//Separater Line

color = new BaseColor(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"));

this.DrawLine(writer, 25f, document.Top - 79f, document.PageSize.Width - 25f, document.Top - 79f, color);

this.DrawLine(writer, 25f, document.Top - 80f, document.PageSize.Width - 25f, document.Top - 80f, color);

document.Add(table);

table = new PdfPTable(2);

table.HorizontalAlignment = Element.ALIGN_LEFT;

table.SetWidths(new float[] { 0.3f, 1f });

table.SpacingBefore = 20f;

//Employee Details

cell = this.PhraseCell(new Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE, BaseColor.BLACK)), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 30f;

table.AddCell(cell);

cell = this.PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 30f;

table.AddCell(cell);

//Photo

cell = this.ImageCell(string.Format("~/photos/{0}.jpg", dr["EmployeeId"]), 25f, PdfPCell.ALIGN_CENTER);

table.AddCell(cell);

//Name

phrase = new Phrase();

phrase.Add(new Chunk(dr["TitleOfCourtesy"] + " " + dr["FirstName"] + " " + dr["LastName"] + "\n", FontFactory.GetFont("Arial", 10, Font.BOLD, BaseColor.BLACK)));

phrase.Add(new Chunk("(" + dr["Title"].ToString() + ")", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)));

cell = this.PhraseCell(phrase, PdfPCell.ALIGN_LEFT);

cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;

table.AddCell(cell);

document.Add(table);

this.DrawLine(writer, 160f, 80f, 160f, 690f, BaseColor.BLACK);

this.DrawLine(writer, 115f, document.Top - 200f, document.PageSize.Width - 100f, document.Top - 200f, BaseColor.BLACK);

table = new PdfPTable(2);

table.SetWidths(new float[] { 0.5f, 2f });

table.TotalWidth = 340f;

table.LockedWidth = true;

table.SpacingBefore = 20f;

table.HorizontalAlignment = Element.ALIGN_RIGHT;

//Employee Id

table.AddCell(this.PhraseCell(new Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

table.AddCell(this.PhraseCell(new Phrase("000" + dr["EmployeeId"], FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

cell = this.PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 10f;

table.AddCell(cell);

//Address

table.AddCell(this.PhraseCell(new Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

phrase = new Phrase(new Chunk(dr["Address"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

phrase.Add(new Chunk(dr["City"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

phrase.Add(new Chunk(dr["Region"] + " " + dr["Country"] + " " + dr["PostalCode"], FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)));

table.AddCell(this.PhraseCell(phrase, PdfPCell.ALIGN_LEFT));

cell = this.PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 10f;

table.AddCell(cell);

//Date of Birth

table.AddCell(this.PhraseCell(new Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8,Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

table.AddCell(this.PhraseCell(new Phrase(Convert.ToDateTime(dr["BirthDate"]).ToString("dd MMMM,yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

cell = this.PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 10f;

table.AddCell(cell);

//Phone

table.AddCell(this.PhraseCell(new Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

table.AddCell(this.PhraseCell(new Phrase(dr["HomePhone"] + " Ext: " + dr["Extension"], FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

cell = this.PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);

cell.Colspan = 2;

cell.PaddingBottom = 10f;

table.AddCell(cell);

//Addtional Information

table.AddCell(this.PhraseCell(new Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT));

table.AddCell(this.PhraseCell(new Phrase(dr["Notes"].ToString(), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_JUSTIFIED));

document.Add(table);

document.Close();

byte[] bytes = memoryStream.ToArray();

memoryStream.Close();

Response.Clear();

Response.ContentType = "application/pdf";

Response.AddHeader("Content-Disposition", "attachment; filename=Employee.pdf");

Response.Buffer = true;

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.BinaryWrite(bytes);

Response.End();

Response.Close();

}

private void DrawLine(PdfWriter writer, float x1, float y1, float x2, float y2, BaseColor color)

{

PdfContentByte contentByte = writer.DirectContent;

contentByte.SetColorStroke(color);

contentByte.MoveTo(x1, y1);

contentByte.LineTo(x2, y2);

contentByte.Stroke();

}

private PdfPCell PhraseCell(Phrase phrase, int align)

{

PdfPCell cell = new PdfPCell(phrase);

cell.BorderColor = BaseColor.WHITE;

cell.VerticalAlignment = PdfPCell.ALIGN_TOP;

cell.HorizontalAlignment = align;

cell.PaddingBottom = 2f;

cell.PaddingTop = 0f;

return cell;

}

private PdfPCell ImageCell(string path, float scale, int align)

{

Image image = Image.GetInstance(HttpContext.Current.Server.MapPath(path));

image.ScalePercent(scale);

PdfPCell cell = new PdfPCell(image);

cell.BorderColor = BaseColor.WHITE;

cell.VerticalAlignment = PdfPCell.ALIGN_TOP;

cell.HorizontalAlignment = align;

cell.PaddingBottom = 0f;

cell.PaddingTop = 0f;

return cell;

}

VB.Net

Protected Sub GenerateReport(sender As Object, e As EventArgs)

Dim dr As DataRow = Me.GetData("SELECT * FROM EmployeesWHERE EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows(0)

Dim document As New Document(PageSize.A4, 88.0F, 88.0F, 10.0F, 10.0F)

Using memoryStream As New System.IO.MemoryStream()

Dim writer As PdfWriter = PdfWriter.GetInstance(document, memoryStream)

Dim phrase As Phrase = Nothing

Dim cell As PdfPCell = Nothing

Dim table As PdfPTable = Nothing

Dim color__1 As BaseColor = Nothing

document.Open()

'Header Table

table = New PdfPTable(2)

table.TotalWidth = 500.0F

table.LockedWidth = True

table.SetWidths(New Single() {0.3F, 0.7F})

'Company Logo

cell = Me.ImageCell("~/images/northwindlogo.gif", 30.0F, PdfPCell.ALIGN_CENTER)

table.AddCell(cell)

'Company Name and Address

phrase = New Phrase()

phrase.Add(New Chunk("Microsoft Northwind Traders Company" & vbLf & vbLf, FontFactory.GetFont("Arial", 16, Font.BOLD, BaseColor.RED)))

phrase.Add(New Chunk("107, Park site," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

phrase.Add(New Chunk("Salt Lake Road," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

phrase.Add(New Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

cell = Me.PhraseCell(phrase, PdfPCell.ALIGN_LEFT)

cell.VerticalAlignment = PdfPCell.ALIGN_TOP

table.AddCell(cell)

'Separater Line

color__1 = New BaseColor(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"))

Me.DrawLine(writer, 25.0F, document.Top - 79.0F, document.PageSize.Width - 25.0F, document.Top - 79.0F, color__1)

Me.DrawLine(writer, 25.0F, document.Top - 80.0F, document.PageSize.Width - 25.0F, document.Top - 80.0F, color__1)

document.Add(table)

table = New PdfPTable(2)

table.HorizontalAlignment = Element.ALIGN_LEFT

table.SetWidths(New Single() {0.3F, 1.0F})

table.SpacingBefore = 20.0F

'Employee Details

cell = Me.PhraseCell(New Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE, BaseColor.BLACK)), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 30.0F

table.AddCell(cell)

cell = Me.PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 30.0F

table.AddCell(cell)

'Photo

cell = Me.ImageCell(String.Format("~/photos/{0}.jpg", dr("EmployeeId")), 25.0F, PdfPCell.ALIGN_CENTER)

table.AddCell(cell)

'Name

phrase = New Phrase()

phrase.Add(New Chunk(dr("TitleOfCourtesy").ToString & " " + dr("FirstName").ToString & " " + dr("LastName").ToString, FontFactory.GetFont("Arial", 10, Font.BOLD, BaseColor.BLACK)))

phrase.Add(New Chunk("(" + dr("Title").ToString() + ")", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)))

cell = Me.PhraseCell(phrase, PdfPCell.ALIGN_LEFT)

cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE

table.AddCell(cell)

document.Add(table)

Me.DrawLine(writer, 160.0F, 80.0F, 160.0F, 690.0F, BaseColor.BLACK)

Me.DrawLine(writer, 115.0F, document.Top - 200.0F, document.PageSize.Width - 100.0F, document.Top - 200.0F, BaseColor.BLACK)

table = New PdfPTable(2)

table.SetWidths(New Single() {0.5F, 2.0F})

table.TotalWidth = 340.0F

table.LockedWidth = True

table.SpacingBefore = 20.0F

table.HorizontalAlignment = Element.ALIGN_RIGHT

'Employee Id

table.AddCell(Me.PhraseCell(New Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

table.AddCell(Me.PhraseCell(New Phrase("000" + dr("EmployeeId"), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

cell = Me.PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 10.0F

table.AddCell(cell)

'Address

table.AddCell(Me.PhraseCell(New Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

phrase = New Phrase(New Chunk(dr("Address").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

phrase.Add(New Chunk(dr("City").ToString + vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

phrase.Add(New Chunk(dr("Region").ToString + " " + dr("Country").ToString + " " + dr("PostalCode").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)))

table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT))

cell = Me.PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 10.0F

table.AddCell(cell)

'Date of Birth

table.AddCell(Me.PhraseCell(New Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

table.AddCell(Me.PhraseCell(New Phrase(Convert.ToDateTime(dr("BirthDate")).ToString("dd MMMM,yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

cell = Me.PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 10.0F

table.AddCell(cell)

'Phone

table.AddCell(Me.PhraseCell(New Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

table.AddCell(Me.PhraseCell(New Phrase(dr("HomePhone") + " Ext: " + dr("Extension"), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

cell = Me.PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)

cell.Colspan = 2

cell.PaddingBottom = 10.0F

table.AddCell(cell)

'Addtional Information

table.AddCell(Me.PhraseCell(New Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8, Font.BOLD, BaseColor.BLACK)), PdfPCell.ALIGN_LEFT))

table.AddCell(Me.PhraseCell(New Phrase(dr("Notes").ToString(), FontFactory.GetFont("Arial", 8, Font.NORMAL, BaseColor.BLACK)), PdfPCell.ALIGN_JUSTIFIED))

document.Add(table)

document.Close()

Dim bytes As Byte() = memoryStream.ToArray()

memoryStream.Close()

Response.Clear()

Response.ContentType = "application/pdf"

Response.AddHeader("Content-Disposition","attachment; filename=Employee.pdf")

Response.Buffer = True

Response.Cache.SetCacheability(HttpCacheability.NoCache)

Response.BinaryWrite(bytes)

Response.End()

Response.Close()

End Using

End Sub

Private Sub DrawLine(writer As PdfWriter, x1 As Single, y1 As Single, x2 As Single, y2 As Single, color As BaseColor)

Dim contentByte As PdfContentByte = writer.DirectContent

contentByte.SetColorStroke(color)

contentByte.MoveTo(x1, y1)

contentByte.LineTo(x2, y2)

contentByte.Stroke()

End Sub

Private Function PhraseCell(phrase As Phrase, align As Integer) As PdfPCell

Dim cell As New PdfPCell(phrase)

cell.BorderColor = BaseColor.WHITE

cell.VerticalAlignment = PdfPCell.ALIGN_TOP

cell.HorizontalAlignment = align

cell.PaddingBottom = 2.0F

cell.PaddingTop = 0.0F

Return cell

End Function

Private Function ImageCell(path As String, scale As Single, align As Integer) As PdfPCell

Dim image As Image = Image.GetInstance(HttpContext.Current.Server.MapPath(path))

image.ScalePercent(scale)

Dim cell As New PdfPCell(image)

cell.BorderColor = BaseColor.WHITE

cell.VerticalAlignment = PdfPCell.ALIGN_TOP

cell.HorizontalAlignment = align

cell.PaddingBottom = 0.0F

cell.PaddingTop = 0.0F

Return cell

End Function

Screenshot

ITextSharp: Download PDF Report from database in ASP.Net

Demo

View Demo

Downloads

Download Code

ITextSharp: Download PDF Report from database in ASP.Net

Related

Download iTextSharp Library

Database

HTML Markup

Namespaces

Populating the Employees DropDownList

Generating PDF Report using iTextSharp

Screenshot

Demo

Downloads

Ask Question

Company

Explore

Follow us

Contact Us