Display Column Sum while generating PDF using C# in Windows Application

Last Reply 3 months ago By pandeyism

Posted 3 months ago

I have table data like this -

AdmissionNo

Month

Fees

Head

Price

R-01

05-May

1500

0

0

R-02

05-May

1500

0

0

R-03

05-May

1500

0

0

R-01

06-Jun

1500

1

120

R-01

06-Jun

1500

2

150

R-01

06-Jun

1500

3

50

R-02

06-Jun

1500

1

120

R-02

06-Jun

1500

2

150

R-02

06-Jun

1500

3

50

R-01

07-Jul

1500

0

0

R-02

07-Jul

1500

0

0

R-03

07-Jul

1500

0

0

it is not showing me correct output.

and my code is 

 private void btnGetData_Click(object sender, EventArgs e)
{
 dGVStu.AllowUserToAddRows = false;
 dGVStu.Columns.Clear();
 con = new SqlDbConnect();
 con.SqlQuery(@"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
 DECLARE @cols NVARCHAR(MAX)
 DECLARE @qry NVARCHAR(4000)
 SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID WHERE HeadName <> '' AND Month=@Month) AS Head
 IF(@cols IS NOT NULL AND @cols <> '[0]')
 SET @qry =
 N'SELECT distinct SPic,AdmissionNo,SName,FName,RollNo,ClassName,SectionName,Year,Month,IssueDate,DueDate,Fees, ' + @cols + '
 FROM (SELECT SPic,fg.AdmissionNo,SName,FName,RollNo,ClassName,SectionName,Year,Month,IssueDate,DueDate,Fees,HeadName,Price
 FROM tblFeesGenerate as fg inner join tblStdReg as sr on sr.AdmissionNo=fg.AdmissionNo inner join tblFeesHead as fh on fg.HeadID=fh.HeadID inner join tblDefClass as dc on fg.ClassID=dc.ClassID inner join tblDefSection as ds on fg.SectionID=ds.SectionID
 WHERE Month='''+@Month+''') p
 PIVOT (MAX(Price) FOR HeadName IN (' + @cols + ')) AS Pvt'
 ELSE
 BEGIN
 SET @qry = 'SELECT DISTINCT SPic,fg.AdmissionNo,SName,FName,RollNo,ClassName,SectionName,Year,Month,IssueDate,DueDate,Fees FROM tblFeesGenerate as fg inner join tblStdReg as sr on sr.AdmissionNo=fg.AdmissionNo inner join tblDefClass as dc on fg.ClassID=dc.ClassID inner join tblDefSection as ds on fg.SectionID=ds.SectionID WHERE Month='''+@Month+''''
 END
 EXEC sp_executesql @qry");
 con.Cmd.Parameters.Add(new SqlParameter("@Month", this.cmbMonth.SelectedItem.ToString()));
 paging.SelectCommand = con.Cmd;
 sBuilder = new SqlCommandBuilder(paging);
 ds = new DataSet();
 paging.Fill(ds, "tblFeesGenerate");
 sTable = ds.Tables["tblFeesGenerate"];
 dGVStu.DataSource = ds.Tables["tblFeesGenerate"].DefaultView;
 dGVStu.ReadOnly = true;
}
private void btnGP_Click(object sender, EventArgs e)
{
 DataTable dt1 = new DataTable();
 dt1.Columns.AddRange(new DataColumn[] { new DataColumn("Head", typeof(string)), new DataColumn("Amount", typeof(int)) });

 string pdfpath = @"C:\Reports\";
 if (!Directory.Exists(pdfpath))
 {
 Directory.CreateDirectory(pdfpath);
 }
 Document doc = new Document(PageSize.A4);
 PdfWriter writer = PdfWriter.GetInstance(doc, new FileStream(pdfpath + "FeesGenerated for " + cmbMonth.SelectedItem.ToString() + "-" + DateTime.Now.ToShortDateString() + ".pdf", FileMode.Create));
 doc.Open();
 for (int i = 0; i < sTable.Rows.Count; i++)
 {
 dt1.Rows.Clear();
 con = new SqlDbConnect();
 con.SqlQuery("select logo,SystemName,Address,Phone,Email FROM tblSystem");
 con.RdrEx();
 while (con.Rdr.Read())
 {
 PdfPTable table1 = new PdfPTable(2);
 table1.DefaultCell.Padding = 10f;
 table1.DefaultCell.BackgroundColor = iTextSharp.text.Color.WHITE;
 table1.DefaultCell.Border = 0;
 table1.HorizontalAlignment = Element.ALIGN_CENTER;
 table1.TotalWidth = 500f;
 table1.LockedWidth = true;
 float[] widths1 = new float[] { 0.7f, 3f };
 table1.SetWidths(widths1);
 Byte[] bytes = (Byte[])con.Rdr[0];
 iTextSharp.text.Image image = iTextSharp.text.Image.GetInstance(bytes);
 image.ScaleAbsolute(90f, 70f);
 PdfPCell bottom1 = new PdfPCell((image));
 bottom1.Padding = 5f;
 bottom1.BackgroundColor = iTextSharp.text.Color.WHITE;
 bottom1.Border = 0;
 bottom1.HorizontalAlignment = 1;
 table1.AddCell(bottom1);
 PdfPTable nested1 = new PdfPTable(1);
 float[] width = new float[] { 0.1f };
 nested1.SetWidths(width);
 nested1.DefaultCell.BackgroundColor = iTextSharp.text.Color.WHITE;
 nested1.DefaultCell.Padding = 7f;
 nested1.DefaultCell.HorizontalAlignment = 1;
 nested1.DefaultCell.Border = 0;
 nested1.AddCell(new Phrase(con.Rdr[1].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 15)));
 nested1.AddCell(new Phrase(con.Rdr[2].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 14)));
 nested1.AddCell(new Phrase("Email: " + con.Rdr[4].ToString() + "\t Phone No: " + con.Rdr[3].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 // nested1.AddCell(new Phrase("Phone No: " + con.Rdr[3].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 14)));
 PdfPCell nesthousing1 = new PdfPCell(nested1);
 nesthousing1.Colspan = 2;
 nesthousing1.Padding = 0f;
 nesthousing1.Border = 0;
 table1.AddCell(nesthousing1);
 doc.Add(table1);
 Paragraph p = new Paragraph(new Chunk(new iTextSharp.text.pdf.draw.LineSeparator(1.0F, 100.0F, iTextSharp.text.Color.BLACK, Element.ALIGN_LEFT, 1)));
 doc.Add(p);
 }
 con.conClose();
 PdfPTable tableb = new PdfPTable(4);
 float[] widthim = new float[] { 0.1f, 0.1f, 0.1f, 0.05f };
 tableb.SetWidths(widthim);
 tableb.DefaultCell.Padding = 5f;
 tableb.HorizontalAlignment = Element.ALIGN_CENTER;
 tableb.TotalWidth = 550f;
 tableb.LockedWidth = true;
 tableb.SpacingBefore = 10f;
 tableb.SpacingAfter = 10f;
 PdfPCell header = new PdfPCell(new Phrase("Student Details", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 header.Indent = 10;
 header.HorizontalAlignment = 1;
 header.Padding = 10f;
 header.Colspan = 4;
 tableb.AddCell(header);
 PdfPTable nested = new PdfPTable(3);
 float[] widthi = new float[] { 0.1f, 0.1f, 0.1f };
 nested.SetWidths(widthi);
 nested.DefaultCell.Padding = 10f;
 nested.AddCell(new Phrase("Reg No. " + sTable.Rows[i]["AdmissionNo"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("" + sTable.Rows[i]["SName"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("" + sTable.Rows[i]["FName"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Class: " + sTable.Rows[i]["ClassName"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Section: " + sTable.Rows[i]["SectionName"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Phone No. ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Month: " + sTable.Rows[i]["Month"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Issue Date: " + sTable.Rows[i]["IssueDate"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 nested.AddCell(new Phrase("Due Date: " + sTable.Rows[i]["DueDate"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 11)));
 PdfPCell nesthousing = new PdfPCell(nested);
 nesthousing.Colspan = 3;
 nesthousing.Padding = 0f;
 tableb.AddCell(nesthousing);
 Byte[] StuPic = (Byte[])sTable.Rows[i]["SPic"];
 iTextSharp.text.Image Stuimage = iTextSharp.text.Image.GetInstance(StuPic);
 Stuimage.ScaleAbsolute(40f, 40f);
 PdfPCell bottom = new PdfPCell((Stuimage));
 bottom.Padding = 5f;
 bottom.HorizontalAlignment = 1;
 tableb.AddCell(bottom);
 doc.Add(tableb);
 PdfPTable table = new PdfPTable(3);
 table.TotalWidth = 500f;
 table.LockedWidth = true;
 float[] widths = new float[] { 0.05f, 0.1f, 0.1f };
 table.SetWidths(widths);
 table.DefaultCell.Padding = 7f;
 table.HorizontalAlignment = Element.ALIGN_CENTER;
 table.SpacingBefore = 5f;
 PdfPCell headerb = new PdfPCell(new Phrase("Fees Details", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 headerb.Indent = 10;
 headerb.HorizontalAlignment = 1;
 headerb.Padding = 10f;
 headerb.Colspan = 4;
 table.AddCell(headerb);
 table.AddCell(new Phrase("S.No.", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase("Head ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase("Amount ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase(" ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase("Monthly Fees", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase(" " + sTable.Rows[i]["Fees"], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 for (int j = 12; j < sTable.Columns.Count; j++)
 {
 dt1.Rows.Add(sTable.Columns[j].ColumnName, sTable.Rows[i][j]);
 int result = i + 1;
 table.AddCell(new Phrase("" + result, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase(sTable.Columns[j].ColumnName, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 table.AddCell(new Phrase("" + sTable.Rows[i][j], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 }
 int sum = Convert.ToInt32(dt1.Compute("sum(Amount)", ""));
 PdfPCell cellP = new PdfPCell(new Phrase("Payable Amount: " + sum, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
 cellP.Colspan = 6;
 cellP.Padding = 8f;
 cellP.HorizontalAlignment = 1;
 table.AddCell(cellP);
 doc.Add(table);
 doc.NewPage();
 }
 doc.Close();
 MessageBox.Show("Fees Generated Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
 return;
}
You are viewing reply posted by: pandeyism 3 months ago.
Posted 3 months ago

Hi smile,

Refer below sample.

Namespaces

C#

using System.Data.SqlClient;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    DataTable dt = GetData();
    this.dataGridView1.DataSource = dt;
}

private DataTable GetData()
{
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(@"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                                    DECLARE @cols NVARCHAR(MAX)
                                    DECLARE @qry NVARCHAR(4000)
                                    SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Head) FROM (SELECT DISTINCT Head FROM tblFeesGenerate WHERE Head <> 0 AND Month='06-Jun') AS Head
                                    IF(@cols IS NOT NULL AND @cols <> '[0]')
                                        SET @qry =
                                                    N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                                                    FROM (SELECT AdmissionNo,Month,Fees,Head,Price
                                                    FROM tblFeesGenerate
                                                    WHERE Month=''06-Jun'') p
                                                    PIVOT (MAX(Price) FOR Head IN (' + @cols + ')) AS Pvt'
                                    ELSE
                                    BEGIN
                                        SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate WHERE Month=''06-Jun'''
                                    END
                                    EXEC sp_executesql @qry", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

private void btnGP_Click(object sender, EventArgs e)
{
    DataTable sTable = GetData();
    DataTable dt1 = new DataTable();
    dt1.Columns.AddRange(new DataColumn[] { new DataColumn("Amount", typeof(int)) });
    string pdfpath = @"C:\Users\anand\Desktop\Test\\";
    if (!Directory.Exists(pdfpath))
    {
        Directory.CreateDirectory(pdfpath);
    }
    string folderPath = @"C:\Users\anand\Desktop\Test\\";
    FileStream stream = new FileStream(folderPath + "DataGridViewExport.pdf", FileMode.Create);
    Document doc = new Document();
    PdfWriter.GetInstance(doc, stream);
    doc.Open();
    for (int i = 0; i < sTable.Rows.Count; i++)
    {
        dt1.Rows.Clear();
        PdfPTable table = new PdfPTable(3);
        table.TotalWidth = 500f;
        table.LockedWidth = true;
        float[] widths = new float[] { 0.05f, 0.1f, 0.1f };
        table.SetWidths(widths);
        table.DefaultCell.Padding = 7f;
        table.HorizontalAlignment = Element.ALIGN_CENTER;
        table.SpacingBefore = 5f;

        PdfPCell headerb = new PdfPCell(new Phrase("Fees Details", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
        headerb.Indent = 10;
        headerb.HorizontalAlignment = 1;
        headerb.Padding = 10f;
        headerb.Colspan = 4;
        table.AddCell(headerb);

        table.AddCell(new Phrase("S.No.", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
        table.AddCell(new Phrase("Head", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
        table.AddCell(new Phrase("Amount ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
        int k = 1;
        for (int j = 2; j < sTable.Columns.Count; j++)
        {
            dt1.Rows.Add(sTable.Rows[i][j]);
            table.AddCell(new Phrase("" + k, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
            table.AddCell(new Phrase("" + sTable.Columns[j].ColumnName, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
            table.AddCell(new Phrase("" + sTable.Rows[i][j], FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
            k++;
        }
        int sum = Convert.ToInt32(dt1.Compute("sum(Amount)", ""));
        PdfPCell cellP = new PdfPCell(new Phrase("Payable Amount: " + sum, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)));
        cellP.Colspan = 4;
        cellP.Padding = 8f;
        cellP.HorizontalAlignment = 1;
        table.AddCell(cellP);
        doc.Add(table);
        doc.NewPage();
    }

    doc.Close();
    MessageBox.Show("Fees Generated Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return;
}

Screenshot