HTML Markup
Below is the HTML markup where I have an ASP.Net DropDownList which I’ll fill with the records of the employees from the Employees table and a Button which when clicked will generate the PDF report for the selected Employee.
<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
C#
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Database Connection String
I have set the following connection string in the Connection Strings section of the Web.Config file
<connectionStrings>
<addname="constr"connectionString="Data Source=.\SQL2005;Initial Catalog=northwind;User id = sa;password=pass@123"/>
</connectionStrings>
Populating the Employees DropDownList
Below is the code to bind the Employees DropDownList in the Page Load event of the ASP.Net Web Page.
Note:GetData is a Generic function to execute SELECT queries and returns DataTable
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeesDropDown();
}
}
private void BindEmployeesDropDown()
{
ddlEmployees.DataSource = GetData("SELECT EmployeeId, (FirstName + ' ' + LastName) Name FROM Employees");
ddlEmployees.DataTextField = "Name";
ddlEmployees.DataValueField = "EmployeeId";
ddlEmployees.DataBind();
}
private DataTable GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
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 IsPostBack Then
BindEmployeesDropDown()
End If
End Sub
Private Sub BindEmployeesDropDown()
ddlEmployees.DataSource = 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 conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As New SqlCommand(query)
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Generation of PDF Report using iTextSharp
Below is the code to generate the PDF with Employee details from database.
First I have fetched the details of the Employee based on the Employee ID from the DropDownList. Then I have created the Header of the report with Logo and name of the company. Below that a Horizontal line is drawn which separates out the Header with the Body.
The Body of the report contains the Employee Details like his Photo, Name, Designation and other details.
To reduce the size of code I have created three generic methods.
C#
protected void GenerateReport(object sender, EventArgs e)
{
DataRow dr = GetData("SELECT * FROM Employees where EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows[0]; ;
Document document = new Document(PageSize.A4, 88f, 88f, 10f, 10f);
Font NormalFont = FontFactory.GetFont("Arial", 12, Font.NORMAL, Color.BLACK);
using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(document, memoryStream);
Phrase phrase = null;
PdfPCell cell = null;
PdfPTable table = null;
Color 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 = 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, Color.RED)));
phrase.Add(new Chunk("107, Park site,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL,Color.BLACK)));
phrase.Add(new Chunk("Salt Lake Road,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL,Color.BLACK)));
phrase.Add(new Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT);
cell.VerticalAlignment = PdfCell.ALIGN_TOP;
table.AddCell(cell);
//Separater Line
color = new Color(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"));
DrawLine(writer, 25f, document.Top - 79f, document.PageSize.Width - 25f, document.Top - 79f, color);
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 = PhraseCell(new Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE,Color.BLACK)), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
table.AddCell(cell);
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
cell.PaddingBottom = 30f;
table.AddCell(cell);
//Photo
cell = 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, Color.BLACK)));
phrase.Add(new Chunk("(" + dr["Title"].ToString() + ")", FontFactory.GetFont("Arial", 8,Font.BOLD, Color.BLACK)));
cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT);
cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
table.AddCell(cell);
document.Add(table);
DrawLine(writer, 160f, 80f, 160f, 690f, Color.BLACK);
DrawLine(writer, 115f, document.Top - 200f, document.PageSize.Width - 100f, document.Top - 200f, Color.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(PhraseCell(new Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD,Color.BLACK)), PdfPCell.ALIGN_LEFT));
table.AddCell(PhraseCell(new Phrase("000" + dr["EmployeeId"], FontFactory.GetFont("Arial", 8,Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
cell.PaddingBottom = 10f;
table.AddCell(cell);
//Address
table.AddCell(PhraseCell(new Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD,Color.BLACK)), PdfPCell.ALIGN_LEFT));
phrase = new Phrase(new Chunk(dr["Address"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL,Color.BLACK)));
phrase.Add(new Chunk(dr["City"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL,Color.BLACK)));
phrase.Add(new Chunk(dr["Region"] + " " + dr["Country"] + " " + dr["PostalCode"],FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
cell.PaddingBottom = 10f;
table.AddCell(cell);
//Date of Birth
table.AddCell(PhraseCell(new Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8, Font.BOLD,Color.BLACK)), PdfPCell.ALIGN_LEFT));
table.AddCell(PhraseCell(new Phrase(Convert.ToDateTime(dr["BirthDate"]).ToString("dd MMMM, yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
cell.PaddingBottom = 10f;
table.AddCell(cell);
//Phone
table.AddCell(PhraseCell(new Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD,Color.BLACK)), PdfPCell.ALIGN_LEFT));
table.AddCell(PhraseCell(new Phrase(dr["HomePhone"] + " Ext: " + dr["Extension"],FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
cell.Colspan = 2;
cell.PaddingBottom = 10f;
table.AddCell(cell);
//Addtional Information
table.AddCell(PhraseCell(new Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8,Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
table.AddCell(PhraseCell(new Phrase(dr["Notes"].ToString(), FontFactory.GetFont("Arial", 8,Font.NORMAL, Color.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.ContentType = "application/pdf";
Response.Buffer = true;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(bytes);
Response.End();
Response.Close();
}
}
private static void DrawLine(PdfWriter writer, float x1, float y1, float x2, float y2, Color color)
{
PdfContentByte contentByte = writer.DirectContent;
contentByte.SetColorStroke(color);
contentByte.MoveTo(x1, y1);
contentByte.LineTo(x2, y2);
contentByte.Stroke();
}
private static PdfPCell PhraseCell(Phrase phrase, int align)
{
PdfPCell cell = new PdfPCell(phrase);
cell.BorderColor = Color.WHITE;
cell.VerticalAlignment = PdfCell.ALIGN_TOP;
cell.HorizontalAlignment = align;
cell.PaddingBottom = 2f;
cell.PaddingTop = 0f;
return cell;
}
private static PdfPCell ImageCell(string path, float scale, int align)
{
iTextSharp.text.Image image = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath(path));
image.ScalePercent(scale);
PdfPCell cell = new PdfPCell(image);
cell.BorderColor = Color.WHITE;
cell.VerticalAlignment = PdfCell.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 = GetData("SELECT * FROM Employees where EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows(0)
Dim document As New Document(PageSize.A4, 88.0F, 88.0F, 10.0F, 10.0F)
Dim NormalFont As Font = FontFactory.GetFont("Arial", 12, Font.NORMAL, Color.BLACK)
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 Color = 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 = 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, Color.RED)))
phrase.Add(New Chunk("107, Park site," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
phrase.Add(New Chunk("Salt Lake Road," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
phrase.Add(New Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT)
cell.VerticalAlignment = PdfCell.ALIGN_TOP
table.AddCell(cell)
'Separater Line
color__1 = New Color(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"))
DrawLine(writer, 25.0F, document.Top - 79.0F, document.PageSize.Width - 25.0F, document.Top - 79.0F, color__1)
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 = PhraseCell(New Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE, Color.BLACK)), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
table.AddCell(cell)
cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
cell.PaddingBottom = 30.0F
table.AddCell(cell)
'Photo
cell = 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, Color.BLACK)))
phrase.Add(New Chunk("(" + dr("Title").ToString() + ")", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)))
cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT)
cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE
table.AddCell(cell)
document.Add(table)
DrawLine(writer, 160.0F, 80.0F, 160.0F, 690.0F, Color.BLACK)
DrawLine(writer, 115.0F, document.Top - 200.0F, document.PageSize.Width - 100.0F, document.Top - 200.0F, Color.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(PhraseCell(New Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
table.AddCell(PhraseCell(New Phrase("000" + dr("EmployeeId"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
cell.PaddingBottom = 10.0F
table.AddCell(cell)
'Address
table.AddCell(PhraseCell(New Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
phrase = New Phrase(New Chunk(dr("Address").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
phrase.Add(New Chunk(dr("City").ToString + vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
phrase.Add(New Chunk(dr("Region").ToString + " " + dr("Country").ToString + " " + dr("PostalCode").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT))
cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
cell.PaddingBottom = 10.0F
table.AddCell(cell)
'Date of Birth
table.AddCell(PhraseCell(New Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
table.AddCell(PhraseCell(New Phrase(Convert.ToDateTime(dr("BirthDate")).ToString("dd MMMM, yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
cell.PaddingBottom = 10.0F
table.AddCell(cell)
'Phone
table.AddCell(PhraseCell(New Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
table.AddCell(PhraseCell(New Phrase(dr("HomePhone") + " Ext: " + dr("Extension"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
cell.Colspan = 2
cell.PaddingBottom = 10.0F
table.AddCell(cell)
'Addtional Information
table.AddCell(PhraseCell(New Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
table.AddCell(PhraseCell(New Phrase(dr("Notes").ToString(), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.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.ContentType = "application/pdf"
Response.Buffer = True
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.BinaryWrite(bytes)
Response.[End]()
Response.Close()
End Using
End Sub
Private Shared Sub DrawLine(writer As PdfWriter, x1 As Single, y1 As Single, x2 As Single, y2 AsSingle, color As Color)
Dim contentByte As PdfContentByte = writer.DirectContent
contentByte.SetColorStroke(color)
contentByte.MoveTo(x1, y1)
contentByte.LineTo(x2, y2)
contentByte.Stroke()
End Sub
Private Shared Function PhraseCell(phrase As Phrase, align As Integer) As PdfPCell
Dim cell As New PdfPCell(phrase)
cell.BorderColor = Color.WHITE
cell.VerticalAlignment = PdfCell.ALIGN_TOP
cell.HorizontalAlignment = align
cell.PaddingBottom = 2.0F
cell.PaddingTop = 0.0F
Return cell
End Function
Private Shared Function ImageCell(path As String, scale As Single, align As Integer) As PdfPCell
Dim image As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath(path))
image.ScalePercent(scale)
Dim cell As New PdfPCell(image)
cell.BorderColor = Color.WHITE
cell.VerticalAlignment = PdfCell.ALIGN_TOP
cell.HorizontalAlignment = align
cell.PaddingBottom = 0.0F
cell.PaddingTop = 0.0F
Return cell
End Function
Demo
Downloads
The download sample contains the iTextSharp DLL.
No comments:
Post a Comment