Introduction
Many of us tried to generate report in *.pdf format but there are no correct tools that just give this feature for free. Here I found one library iTextSharp.dll file which is for PDF reports but there are so many versions of it and it is really very hard to get the right DLL file to generate report, formatting, page numbers and all. So in this article, you will find all these things get done with a simple few lines of codes.
Background
This project is done in ASP.NET using C# language. Here the database used is the Access database. But it really works with SQL Server too. I have tried SQL Server database too but it's easy for me to attach .mdb file in here. The article provides the easiest way to deal with multiple tables and retrieve data from multiple tables and generate the PDF file with header and footer. Also this article helps to insert the page number in each PDF page.
Using the Code
The whole project is attached in the zip file. You can just download and take benefit of this example in your project. The main gist code of the article is shown below. But before using this code, you have to add iTextSharp.dll version 4.1.2.0 file in the Reference of your project and on the top of the *.cs page, you have to import this file. The whole thing is shown in the downloadable zip file. I am getting data from multiple tables joining. The tables being used are CustomerOrder and OrderedItem which are shown in the codes below too. Using DataRelation feature for combining two tables with the total sum are automatically calculated and shown in the report. The codes for this are:
string catQuery = @"select * from CustomerOrder";
OleDbCommand objCmd = new OleDbCommand(catQuery, cnx);
OleDbDataAdapter objDAcategory = new OleDbDataAdapter(objCmd);
objDAcategory.Fill(ds, "CustomerOrder");
string totQuery = @"select OrderID,sum(Amount) _
As [TotalAmount] from OrderedItem where IsActive = '1' group by OrderID";
objCmd = new OleDbCommand(totQuery, cnx);
OleDbDataAdapter objDAtotal = new OleDbDataAdapter(objCmd);
objDAtotal.Fill(ds, "OrderedItem");
string itemQuery = @"select * from OrderedItem where IsActive='1'";
objCmd = new OleDbCommand(itemQuery, cnx);
OleDbDataAdapter objDAitem = new OleDbDataAdapter(objCmd);
objDAitem.Fill(ds, "OrderedItem");
DataRelation dRel = new DataRelation("CustomerOrder_OrderedItem", _
ds.Tables["CustomerOrder"].Columns["OrderID"], _
ds.Tables["OrderedItem"].Columns["OrderID"]);
ds.Relations.Add(dRel);
cusDptInfo.DataSource = ds;
cusDptInfo.DataBind();
The following code is for writing data to the PDF file. Document variable pdfDoc has been defined as the A4 sized paper with all those border values.
HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader
("Content-Disposition", "attachment;filename=iTextSharpExample.pdf");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/pdf";
StringWriter stringwrite = new StringWriter();
HtmlTextWriter htmlwrite = new HtmlTextWriter(stringwrite);
cusDptInfo.RenderControl(htmlwrite);
StringReader sr = new StringReader(stringwrite.ToString());
Document pdfDoc = new Document(PageSize.A4, 50f, 50f, 40f, 40f);
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
The following piece of code is for counting the pagenumber.
string pcnt;
pcnt = (writer.CurrentPageNumber - 1).ToString();
pcnt = pcnt.Substring(1);
The following code shows how header and footer can be implemented in the PDF file with alignments and border style.
HeaderFooter header = new HeaderFooter
(new Phrase("My Company \n Customer Order Details"), false)
{ Border = Rectangle.NO_BORDER, Alignment = Element.ALIGN_CENTER };
HeaderFooter footer = new HeaderFooter(new Phrase(pcnt), true)
{ Border = Rectangle.NO_BORDER, Alignment = Element.ALIGN_CENTER };
pdfDoc.Header = header;
pdfDoc.Footer = footer;
pdfDoc.Open();
This piece of code is to include current date for report generator and it is shown in all the pages of the PDF file.
_events e = new _events();
e.TimerText = DateTime.Now.ToString("MM/dd/yyyy");
writer.PageEvent = e;
htmlparser.Parse(sr);
pdfDoc.NewPage();
This aspx side code includes basically which piece of data from the tables are to be shown and how.
<asp:Repeater runat="server" ID="cusDptInfo"
onitemdatabound="cusDptInfo_ItemDataBound" >
<ItemTemplate ><font size="60%">
<b><%# DataBinder.Eval(Container.DataItem, "FirstName") %>
<%# DataBinder.Eval(Container.DataItem, "LastName") %></b><br />
<%# DataBinder.Eval(Container.DataItem, "GroupName") %><br />
<%# DataBinder.Eval(Container.DataItem, "Email") %><br />
<%# DataBinder.Eval(Container.DataItem, "CreationDate","{0:MM/dd/yyyy}") %><br />
<br /></font>
<asp:Repeater ID="rptItemList" runat="server">
<ItemTemplate>
<asp:Table ID="tblItemList"
runat="server" Font-Size="7%">
<asp:TableRow>
<asp:TableCell><%# DataBinder.Eval(Container.DataItem,"
Item")%> <%#DataBinder.Eval(Container.DataItem, "
Category","({0})")%><br /><font color="
gray" style="text-align:justify"><%#
DataBinder.Eval(Container.DataItem, "ItemDescription") %>
</font></asp:TableCell>
<asp:TableCell HorizontalAlign="Right">
<%# DataBinder.Eval(Container.DataItem,"
Amount","{0:c}") %></asp:TableCell>
</asp:TableRow>
</asp:Table>
</ItemTemplate>
</asp:Repeater>
<asp:Repeater ID="rptOrderedTotAmount" runat="server">
<ItemTemplate>
<asp:Table ID="tblItemListt" runat="server" Font-Size="7%">
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<%# DataBinder.Eval(Container.DataItem, "TotalAmount","
<strong>Total: </strong>{0:c}") %></asp:TableCell>
</asp:TableRow>
</asp:Table>
<br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:Repeater>
History
- 16th August, 2011: Initial post
