Sunday, 24 September 2017

Generate Pay slip / Salary slip PDF using iTextSharp in ASP.NET

In this article, I am going to explain you how to generate employee pay slip or salary slip in PDF format using itextsharp in asp.net . I will be using visual studio 2013 professional. Before continuing this article, let's see the screen-shots.

When the page is loaded for the first time then you will see the below screen. You will select employee id and month and click on generate button to download salary slip. I have hard-coded some employee id and month in dropdownlist control in aspx file.

Generate salary slip using itextsharp in asp.net

I have shared pdf file sample as shown below in iframe.

So, below is the step by step tutorial.

Creating table and procedure

Below is the scripts to create 2 tables tbl_EmployeeDetails and tbl_SalaryDetails for employee details and salary details.

create table tbl_EmployeeDetails
(
EmpId int,
Name varchar(100),
PFNumber varchar(100),
DOJ datetime,
Designation varchar(50),
Department varchar(50),
AccountNumber varchar(50),
PAN varchar(50),
ModeofPay varchar(50),
UAN varchar(50)
)
go
insert into tbl_EmployeeDetails values(1001,'Rahul Singh','MH/0001/0001','2016-01-10',
'Software Engineer','Development','123456789','PAN1234X','ABC Bank','101010101010')
insert into tbl_EmployeeDetails values(1002,'Vijay Singh','MH/0002/0002','2016-01-10',
'Software Engineer','Testing','10101010','PAN1101X','ABC Bank','000000011111')
go
create table tbl_SalaryDetails
(
EmpId int,
PaidMonth varchar(25),
NoOfDays int,
NoOfDaysPaid int,
Basic decimal(12,2),
HRA decimal(12,2),
CityCompAllowance decimal(12,2),
EduAllowance decimal(12,2),
MedicalReimbursement decimal(12,2),
LTA decimal(12,2),
ConveyanceAllowance decimal(12,2),
FoodAllowance decimal(12,2),
Mobile decimal(12,2),
ProfessionalPersuit decimal(12,2),
ProvidentFund decimal(12,2),
ProfessionalTax decimal(12,2),
)
go
insert into tbl_SalaryDetails values(1001,'Aug/2017',30,30,15000,9167,5015,200,1250,3000,1600,2500,200,2000,1800,200)
insert into tbl_SalaryDetails values(1001,'Sept/2017',30,29,15000,9167,5015,200,1250,3000,1600,2500,200,2000,1700,200)
insert into tbl_SalaryDetails values(1002,'Aug/2017',30,30,13000,7000,5015,200,1250,3000,1600,2500,200,2000,1800,200)
insert into tbl_SalaryDetails values(1002,'Sept/2017',30,29,13000,7000,5015,200,1250,3000,1600,2500,200,2000,1700,200)

Below is script to create stored procedure that will return 5 tables i.e. Employee details, salary earning details, salary deduction details, gross earning total amount and gross deduction total amount.

create procedure [dbo].[usp_GetSalaryDetails]
@EmpId int,
@PaidMonth varchar(25)
as
begin
              --Employee details
                 select
                 e.EmpId as 'Employee ID',
                 e.Name as 'Employee Name',
                 e.PFNumber as 'PF. No.',
                 s.NoOfDays as 'No of Days Worked',
                 s.NoOfDaysPaid as 'No of Days Paid',
                 convert(varchar, e.DOJ, 103) as 'Date of Joining',
                 e.Designation as 'Designation',
                 e.Department as 'Department',
                 e.AccountNumber as 'Bank Account Number',
                 e.PAN as 'PAN Number',
                 e.ModeofPay as 'Mode of Pay',
                 e.UAN as 'UAN'
                 from tbl_EmployeeDetails as e join tbl_SalaryDetails as s
                 on e.EmpId=s.EmpId
                 where e.EmpId=@EmpId and s.PaidMonth=@PaidMonth
   
                 --Salary Earning details
                 select
                 Basic,
                 HRA,
                 CityCompAllowance as 'City Comp. Allowance',
                 EduAllowance as 'Edu. Allowance',
                 MedicalReimbursement as 'Medical Reimbursement',
                 LTA,
                 ConveyanceAllowance as 'Conveyance Allowance',
                 FoodAllowance as 'Food Allowance',
                 Mobile,
                 ProfessionalPersuit as 'Professional Persuit'
                 from
                 tbl_SalaryDetails
                 where EmpId=@EmpId and PaidMonth=@PaidMonth
   
   
                 --salary deduction details
                 select
                 ProvidentFund as 'Provident Fund',
                 ProfessionalTax as 'Professional Tax'
                 from
                 tbl_SalaryDetails
                 where EmpId=@EmpId and PaidMonth=@PaidMonth
   
               --Gross earning total amount
                 select
                 Basic+
                 HRA+
                 CityCompAllowance+
                 EduAllowance +
                 MedicalReimbursement +
                 LTA+
                 ConveyanceAllowance+
                 FoodAllowance+
                 Mobile+
                 ProfessionalPersuit as 'Gross Earning'
                 from
                 tbl_SalaryDetails
                 where EmpId=@EmpId and PaidMonth=@PaidMonth
   
              --Gross deduction total amount
               select
                 ProvidentFund+
                 ProfessionalTax as 'Gross Deduction'
                 from
                 tbl_SalaryDetails
                 where EmpId=@EmpId and PaidMonth=@PaidMonth
   
end

Creating asp.net application

Create an asp.net empty web application as shown below.
1) Add a new Web Form.
2) Add 3 folders BLL, DAL and images.
3) In images folder, add logo.png of company.
4) In BLL folder add Code.cs class file.
5) In DAL folder add Employee.cs class file.

Asp.net folder structure

In Employee.cs file

Add below lines of code in Employee.cs file.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace WebApplication1.DAL
{
 public class Employee
 {
  public DataSet GetData(int EmpId, string PaidMonth)
  {
   string MyConnString = @"server=###;database=###;uid=###;password=###;";
   SqlConnection con = new SqlConnection(MyConnString);
   SqlCommand cmd = new SqlCommand("usp_GetSalaryDetails", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.AddWithValue("@EmpId", EmpId);
   cmd.Parameters.AddWithValue("@PaidMonth", PaidMonth);
   con.Open();
   SqlDataAdapter sda = new SqlDataAdapter(cmd);
   DataSet ds = new DataSet();
   sda.Fill(ds);
   if (ds.Tables[0].Rows.Count > 0)
   {
   }
   return ds;
  }
 }
}

In Code.cs file

Add below lines of code in Code.cs file. Below code is to convert salary in words.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebApplication1.BLL
{
 public class Code
 {
   public string GenerateWordsinRs(string inputRs)
   {
     string input = inputRs;
     string a = "";
     string b = "";
     // take decimal part of input. convert it to word. add it at the end of method.
     string decimals = "";
     if (input.Contains("."))
     {
         decimals = input.Substring(input.IndexOf(".") + 1);
         // remove decimal part from input
         input = input.Remove(input.IndexOf("."));
     }
     string strWords = NumbersToWords(Convert.ToInt32(input));
     if (!inputRs.Contains("."))
     {
         a = strWords + " Rupees Only";
     }
     else
     {
         a = strWords + " Rupees";
     }
     if (decimals.Length > 0)
     {
         // if there is any decimal part convert it to words and add it to strWords.
         string strwords2 = NumbersToWords(Convert.ToInt32(decimals));
         b = " and " + strwords2 + " Paisa Only ";
     }
     string final2 = "";
     final2 = a + b;
     return final2;
   }
   public static string NumbersToWords(int inputNumber)
   {
     int inputNo = inputNumber;
     if (inputNo == 0)
         return "Zero";
     int[] numbers = new int[4];
     int first = 0;
     int u, h, t;
     System.Text.StringBuilder sb = new System.Text.StringBuilder();
     if (inputNo < 0)
     {
         sb.Append("Minus ");
         inputNo = -inputNo;
     }
     string[] words0 = {"" ,"One ", "Two ", "Three ", "Four ",
     "Five " ,"Six ", "Seven ", "Eight ", "Nine "};
     string[] words1 = {"Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ",
     "Fifteen ","Sixteen ","Seventeen ","Eighteen ", "Nineteen "};
     string[] words2 = {"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ",
     "Seventy ","Eighty ", "Ninety "};
     string[] words3 = { "Thousand ", "Lakh ", "Crore " };
     numbers[0] = inputNo % 1000; // units
     numbers[1] = inputNo / 1000;
     numbers[2] = inputNo / 100000;
     numbers[1] = numbers[1] - 100 * numbers[2]; // thousands
     numbers[3] = inputNo / 10000000; // crores
     numbers[2] = numbers[2] - 100 * numbers[3]; // lakhs
     for (int i = 3; i > 0; i--)
     {
         if (numbers[i] != 0)
         {
             first = i;
             break;
         }
     }
     for (int i = first; i >= 0; i--)
     {
         if (numbers[i] == 0) continue;
         u = numbers[i] % 10; // ones
         t = numbers[i] / 10;
         h = numbers[i] / 100; // hundreds
         t = t - 10 * h; // tens
         if (h > 0) sb.Append(words0[h] + "Hundred ");
         if (u > 0 || t > 0)
         {
             if (h > 0 || i == 0) sb.Append("");
             if (t == 0)
                 sb.Append(words0[u]);
             else if (t == 1)
                 sb.Append(words1[u]);
             else
                 sb.Append(words2[t - 2] + words0[u]);
         }
         if (i != 0) sb.Append(words3[i - 1]);
     }
     return sb.ToString().TrimEnd();
   }
 }
}

Installing itextsharp

Next step is to install itextsharp dll. Right click on References and go to Manage NuGet Packages as shown below.

Add itextsharp service reference

After that a new window will open as shown below. Search for iTextSharp and install it.

Installing itextsharp from nuget

In aspx file

Add below lines of code in designer file.

<table border="1" width="500px">
  <tr>
     <td style="width: 200px;">Select Employee ID</td>
     <td>
       <asp:DropDownList ID="ddlEmployeeID" runat="server">
           <asp:ListItem Text="1001" Value="1001">
           </asp:ListItem>
           <asp:ListItem Text="1002" Value="1002">
           </asp:ListItem>
           <asp:ListItem Text="1003" Value="1003">
           </asp:ListItem>
       </asp:DropDownList>
     </td>
  </tr>
  <tr>
    <td>Select Month</td>
    <td>
      <asp:DropDownList ID="ddlMonth" runat="server">
          <asp:ListItem Text="Aug/2017" Value="Aug/2017">
          </asp:ListItem>
          <asp:ListItem Text="Sept/2017" Value="Sept/2017">
          </asp:ListItem>
          <asp:ListItem Text="Oct/2017" Value="Oct/2017">
          </asp:ListItem>
      </asp:DropDownList>
    </td>
  </tr>
  <tr>
    <td>
        <asp:Button ID="btnGenerate" Text="Generate" runat="server" OnClick="btnGenerate_Click" /></td>
    <td>
        <asp:Label ID="lblMessage" runat="server" ForeColor="Red">
        </asp:Label>
    </td>
  </tr>
</table>

Complete C# Code

Below is the complete C# code to generate PDF file.

using iTextSharp.text;
using iTextSharp.text.pdf;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using WebApplication1.BLL;
using WebApplication1.DAL;
namespace WebApplication1
{
  public partial class WebForm1 : System.Web.UI.Page
  {
    Employee emp = new Employee();
    Code code = new Code();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnGenerate_Click(object sender, EventArgs e)
    {
       GeneratePay();
    }
    private void GeneratePay()
    {
      DataSet ds = new DataSet();
      int EmployeeID = Convert.ToInt32(ddlEmployeeID.SelectedValue);
      string Month = ddlMonth.SelectedValue;
      ds = emp.GetData(EmployeeID, Month);
      if (ds != null && ds.Tables.Count == 5 && ds.Tables[0].Rows.Count > 0 && ds.Tables[1].Rows.Count > 0)
      {
        Panel pnlPrintControl = new Panel();
        Document doc = new Document(PageSize.A4, 36f, 36f, 36f, 36f);//36f, 36f, 90f, 100f);
        PdfWriter.GetInstance(doc, Response.OutputStream);
        doc.Open();
        //var fontFamily = FontFactory.GetFont("TIMES ROMAN", 15, BaseColor.BLUE);
        // 1) Adding logo to right side top
        string imagePath = Server.MapPath("Images") + "\\logo.png";
        iTextSharp.text.Image image = iTextSharp.text.Image.GetInstance(imagePath);
        image.Alignment = Element.ALIGN_RIGHT;
        // set width and height
        image.ScaleToFit(180f, 250f);
        doc.Add(image);
        // 2) Addling blank paragraph
        doc.Add(new Paragraph("  "));
        // 3) Adding title table
        Paragraph title = new Paragraph();
        title.Add(new Chunk("Pay Slip for the month of " + ddlMonth.SelectedValue, new Font(Font.FontFamily.COURIER, 10, 1, BaseColor.BLACK)));
        title.Alignment = 1;
        doc.Add(title);
        // 4) Addling blank paragraph
        doc.Add(new Paragraph("  "));
        // 5) Creating 1st table with 4 column
        PdfPTable table1 = new PdfPTable(4);
        int[] columnwidth = { 20, 25, 20, 25 };
        table1.SetWidths(columnwidth);
        table1.WidthPercentage = 100;
        table1.HorizontalAlignment = 0;
        // 6) Adding employee data to table1
        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
        {
            string columnName = (ds.Tables[0].Columns[i].ColumnName);
            string columnValue = (ds.Tables[0].Rows[0][i].ToString());
            PdfPCell cellColumnName = new PdfPCell(new Phrase(columnName, new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_CENTER, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) };
            cellColumnName.HorizontalAlignment = 0; //0=Left, 1=Centre, 2=Right
            //Cellcolumnname.Border = 15;
            table1.AddCell(cellColumnName);
            PdfPCell cellColumnValue = new PdfPCell(new Phrase(columnValue, new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111))));
            cellColumnValue.HorizontalAlignment = 0; //0=Left, 1=Centre, 2=Right
            //cellcolumnvalue.Border = 15;
            table1.AddCell(cellColumnValue);
        }
        doc.Add(table1);
        // 6) Addling blank paragraph
        doc.Add(new Paragraph("  "));
        // 7) Creating 2nd table with 4 columns [which is main table]
        PdfPTable mainTable = new PdfPTable(4);//earnedTable1.TotalWidth = 500f;//earnedTable1.LockedWidth = true;
        int[] columnwidth1 = { 30, 20, 30, 20 }; //23, 20, 25, 32 };
        mainTable.SetWidths(columnwidth1);
        mainTable.WidthPercentage = 100;
        mainTable.HorizontalAlignment = 0;
        // a. adding 4 cells for header
        mainTable.AddCell(new PdfPCell(new Phrase("EARNINGS", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) }); //{ HorizontalAlignment = Element.ALIGN_CENTER, Padding = 5, BackgroundColor = new BaseColor(System.Drawing.Color.Silver) };;
        mainTable.AddCell(new PdfPCell(new Phrase("RUPEES", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) }); //{ HorizontalAlignment = Element.ALIGN_CENTER, Padding = 5, BackgroundColor = new BaseColor(System.Drawing.Color.Silver) };;
        mainTable.AddCell(new PdfPCell(new Phrase("DEDUCTIONS", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) }); //{ HorizontalAlignment = Element.ALIGN_CENTER, Padding = 5, BackgroundColor = new BaseColor(System.Drawing.Color.Silver) };;
        mainTable.AddCell(new PdfPCell(new Phrase("RUPEES", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) }); //{ HorizontalAlignment = Element.ALIGN_CENTER, Padding = 5, BackgroundColor = new BaseColor(System.Drawing.Color.Silver) };;
        // b. creating earning table with 2 columns [left side]
        PdfPTable earning = new PdfPTable(2);
        int[] columnwidth3 = { 30, 20 };
        earning.SetWidths(columnwidth3);
        earning.WidthPercentage = 80;
        earning.HorizontalAlignment = 0;
        // c. adding earning data
        for (int i = 0; i < ds.Tables[1].Columns.Count; i++)
        {
            string columnName = (ds.Tables[1].Columns[i].ColumnName);
            string columnValue = (ds.Tables[1].Rows[0][i].ToString());
            PdfPCell cellColumnName = new PdfPCell(new Phrase(columnName, new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111))));
            cellColumnName.HorizontalAlignment = 0; //0=Left, 1=Centre, 2=Right
            earning.AddCell(cellColumnName);
            PdfPCell cellColumnValue = new PdfPCell(new Phrase(AppendComma(columnValue), new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT };
            earning.AddCell(cellColumnValue);
        }
        // d. creating deduction table with 2 columns [Right side]
        PdfPTable deduction = new PdfPTable(2);
        int[] columnwidth4 = { 30, 20 };
        deduction.SetWidths(columnwidth3);
        deduction.WidthPercentage = 80;
        deduction.HorizontalAlignment = 0;
        // e. adding deduction data
        for (int i = 0; i < ds.Tables[2].Columns.Count; i++)
        {
            string columnName = (ds.Tables[2].Columns[i].ColumnName);
            string columnValue = (ds.Tables[2].Rows[0][i].ToString());
            PdfPCell cellColumnName = new PdfPCell(new Phrase(columnName, new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111))));
            cellColumnName.HorizontalAlignment = 0; //0=Left, 1=Centre, 2=Right
            deduction.AddCell(cellColumnName);
            PdfPCell cellColumnValue = new PdfPCell(new Phrase(AppendComma(columnValue), new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT };
            deduction.AddCell(cellColumnValue);
        }
        // f. creating a new cell [cell1] with colspan=2
        //    adding earning table into cell1
        PdfPCell cell1 = new PdfPCell(earning);
        cell1.Colspan = 2;
        // adding cell1 into mainTable
        mainTable.AddCell(cell1);
        // g. creating a new cell [cell2] with colspan=2
        //    adding deduction table into cell2
        PdfPCell cell2 = new PdfPCell(deduction);
        cell2.Colspan = 2;
        // adding cell2 into mainTable
        mainTable.AddCell(cell2);
        mainTable.AddCell(new PdfPCell(new Phrase("Gross Earning", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, BackgroundColor = new BaseColor(236, 236, 236) });
        mainTable.AddCell(new PdfPCell(new Phrase(AppendComma(ds.Tables[3].Rows[0][0].ToString()), new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT, Padding = 5 });
        mainTable.AddCell(new PdfPCell(new Phrase("Gross Deductions", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, Padding = 5, BackgroundColor = new BaseColor(236, 236, 236) });
        mainTable.AddCell(new PdfPCell(new Phrase(AppendComma(ds.Tables[4].Rows[0][0].ToString()), new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT, Padding = 5 });
        PdfPCell netEarning = new PdfPCell(new Phrase("Net Salary", new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, BackgroundColor = new BaseColor(236, 236, 236) };
        mainTable.AddCell(netEarning);
        string NetSalary = (Convert.ToDecimal(ds.Tables[3].Rows[0][0].ToString()) - Convert.ToDecimal(ds.Tables[4].Rows[0][0].ToString())).ToString();
        PdfPCell netSalary = new PdfPCell(new Phrase(AppendComma(NetSalary), new Font(Font.FontFamily.COURIER, 10, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_RIGHT, Padding = 5 };
        mainTable.AddCell(netSalary);
        PdfPCell blankCell = new PdfPCell();
        blankCell.Colspan = 2;
        mainTable.AddCell(blankCell);
        PdfPCell NetSalaryInWords = new PdfPCell(new Phrase("Net Salary In Word : " + code.GenerateWordsinRs(NetSalary), new Font(Font.FontFamily.COURIER, 8, 1, new BaseColor(50, 50, 111)))) { HorizontalAlignment = Element.ALIGN_LEFT, Padding = 5 };
        NetSalaryInWords.Colspan = 4;
        mainTable.AddCell(NetSalaryInWords);
        // adding mainTable to document object
        doc.Add(mainTable);
        Paragraph Note = new Paragraph();
        Note.Add(new Chunk("This is computer generated payslip and does not require signature or company seal.", new Font(Font.FontFamily.COURIER, 10, 1, BaseColor.BLACK)));
        Note.Alignment = 1;
        doc.Add(Note);
        Paragraph address = new Paragraph();
        address.Add(new Chunk(@"(ABC Infotech Pvt Ltd.)
        Address: 1st Floor, Steet Name, Bullding Number, Mumbai - 400001", new Font(Font.FontFamily.COURIER, 10, 1, BaseColor.BLACK)));
        address.Alignment = 1;
        doc.Add(address);
        doc.Close();
        string fileName = ddlEmployeeID.SelectedValue + " " + ddlMonth.SelectedValue + ".pdf";
        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;" + "filename=" + fileName);
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.Write(doc);
        Response.End();
      }
      else
      {
          lblMessage.Text = "Salary slip not generated.";
      }
    }
    private string AppendComma(string value)
    {
      return String.Format("{0:#,0.00}", Convert.ToDecimal(value));
    }
  }
}
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON