I have table data like this AdmissionNoMonthFeesHeadPriceR0105May150000R0205May150000R0305May150000R0106Jun15001120R0106Jun15002150R0106Jun1500350R0206Jun15001120R0206Jun15002150R0206Jun1500350R0107Jul150000R0207Jul150000R0307Jul150000it is not showing me correct outputand my code is private void btnGetDataClickobject sender EventArgs e dGVStuAllowUserToAddRows false dGVStuColumnsClear con new SqlDbConnect conSqlQuery34DECLARE DynamicPivotQuer

Hi smileRefer below sampleNamespacesCusing SystemDataSqlClientusing SystemIOusing iTextSharptextusing iTextSharptextpdfCodeCprivate void Form1Loadobject sender EventArgs e DataTable dt GetData thisdataGridView1DataSource dtprivate DataTable GetData SqlConnection con new SqlConnectionconstr SqlCommand cmd new SqlCommand34DECLARE DynamicPivotQuery AS NVARCHARMAX DECLARE cols NVARCHARMAX DECLARE qry NVARCHAR4000 SELECT cols ISNULLcols 39393939 QUOTENAMEHead FROM SELECT DISTINCT Head FROM tblFeesGenerate WHERE Head ltgt 0 AND Month3906Jun39 AS Head IFcols IS NOT NULL AND cols ltgt 39039 SET qry N39SELECT distinct AdmissionNoMonthFees

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

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on May 09, 2019 11:29 AM
Forum: Windows Basics
Answer: 1
Views: 3498
Sample Code: Download

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;
}

Download FREE API for Word, Excel and PDF in ASP.Net

pandeyis...

0 5

Reply
ANSWER
User: pandeyism
Replied: on May 10, 2019 05:10 AM
Report

Hi smile,

Refer below sample.

Namespaces

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

Code

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us