I added the purchased order data for some companies like thattblPurchaseIDInvoiceCustomer ACProductQtyNet BalPDate1Inv1ABC CompanyA315001010212Inv1ABC CompanyB515001010213Inv1ABC CompanyC220001010214Inv2SKY CompanyE1025001110215Inv3ABC CompanyD51000111021Now ABC Company wants to pay the Billed amount I added the data like this waytblPayTransactionIDInvoiceAccount TypeCustormer ACNet BalPaidRem BalDate1Inv1Cash AccountABC Company5000300020001110212Inv2Cash AccountSKY Comp

Hi alhamdCheck this example Now please take its reference and correct your codeNamespacesCusing SystemConfigurationusing SystemDatausing SystemDataSqlClientusing SystemIOusing iTextSharptextusing iTextSharptextpdfVBNetImports SystemConfigurationImports SystemDataImports SystemDataSqlClientImports SystemIOImports iTextSharptextImports iTextSharptextpdfCodeCprivate DataTable GetDatastring query string conString ConfigurationManagerConnectionStrings34constr34ConnectionString using SqlConnection con new SqlConnectionconString using SqlCommand cmd new SqlCommandquery con using SqlDataAdapter sda new SqlDataAdaptercmd using DataTable dt new DataTable sdaFilldt return dt protected void ExportPDfClickobject sender EventArgs e DataTable dtPurchase Ge

Display Report from multiple table in PDF Format using iTextSharp in ASP.Net

alhamd

SOLVED CLOSED ACTIVE ANSWERED
User: alhamd
Posted: on Oct 12, 2021 12:36 AM
Forum: ASP.Net Thirdparty controls
Answer: 1
Views: 4768

I added the purchased order data for some companies like that

tblPurchase

ID	Invoice	Customer A/C	Product	Qty	Net Bal	P_Date
1	Inv-1	ABC Company	A	3	1500	10.10.21
2	Inv-1	ABC Company	B	5	1500	10.10.21
3	Inv-1	ABC Company	C	2	2000	10.10.21
4	Inv-2	SKY Company	E	10	2500	11.10.21
5	Inv-3	ABC Company	D	5	1000	11.10.21

Now, ABC Company wants to pay the Billed amount. I added the data like this way.

tblPayTransaction

ID	Invoice	Account Type	Custormer A/C	Net Bal	Paid	Rem Bal	Date
1	Inv-1	Cash Account	ABC Company	5000	3000	2000	11.10.21
2	Inv-2	Cash Account	SKY Company	2500	1500	1000	12.10.21
3	Inv-3	Cash Account	ABC Company	1000	500	500	12.10.21

Now, I want to generate the Ledger Report for ABC Company for Dated 10.10.21 to 11.10.21 like that way in the pdf file.

Ledger Report

Account: ABC Company Dated: 10.10.21 to 11.10.21

Product Details

S.I	Product	Qty	Net Bal	P_Date
1	A	3	1500	10.10.21
2	B	5	1500	10.10.21
3	C	2	2000	10.10.21
	Total		5000
	Paid in [Cash Account]		3000	11.10.21
	Rem Balance		2000

Ledger Report

Account: SKY Company Dated: 10.10.21 to 12.10.21

Product Details

S.I	Product	Qty	Net Bal	P_Date
1	E	10	2500	11.10.21
	Total		2500
	Paid in [Cash Account]		1500	12.10.21
	Rem Balance		1000

here is my code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {        
        FillAc();       
    }
}
 
private void FillAc()
{
    con = new SqlDbConnect();
    con.SqlQuery("SELECT AccountID,A_Name FROM tblGroupAccount order by AccountID desc;");
    ddlAc.DataSource = con.Cmd.ExecuteReader();
    ddlAc.DataTextField = "A_Name";
    ddlAc.DataValueField = "AccountID";
    ddlAc.DataBind();
    con.conClose();
    ddlAc.Items.Insert(0, new System.Web.UI.WebControls.ListItem("---Select Account---", "0"));
}
protected void ddlAc_SelectedIndexChanged(object sender, EventArgs e)
{
    this.BindGrid();
    //ddlAc.SelectedIndex = -1;
 
}
protected void BindGrid()
{
    con = new SqlDbConnect();
    con.SqlQuery(@"select InvoiceNo,P_Date,A_Name,Name,P_Name,Quantity,NetBal,Paid from tblPurchase as p
                   inner join tblGroupAccount as ag on p.AccountID= ag.AccountID
                   inner join tblCompany as c on p.VendorID= c.CompanyID
                   inner join tblStockReg as sr on p.ProductID= sr.ProductID where P_Date between @From and @To and p.AccountID=@AId order by PurchaseID");
    con.Cmd.Parameters.Add(new SqlParameter("@From", txtDate.Text));
    con.Cmd.Parameters.Add(new SqlParameter("@To", txtTDate.Text));
    con.Cmd.Parameters.Add(new SqlParameter("@AId",ddlAc.SelectedValue.ToString()));
    adapt.SelectCommand = con.Cmd;
    adapt.Fill(sTable);
    if (sTable.Rows.Count > 0)
    {
        GridView1.Visible = true;
        GridView1.DataSource = sTable;
        GridView1.DataBind();
        //lbltotalcount.Text = GridViewStudent.Rows.Count.ToString();
        GridView1.FooterRow.Cells[4].Text = "Current Total:";
        GridView1.FooterRow.Cells[4].Font.Bold = true;
       // GridView1.FooterRow.Cells[1].BackColor = System.Drawing.Color.LightSteelBlue;
        GridView1.FooterRow.Cells[4].HorizontalAlign = HorizontalAlign.Right;
 
        decimal totalDis = sTable.AsEnumerable().Sum(row => row.Field<decimal>("NetBal"));
        GridView1.FooterRow.Cells[6].Text = totalDis.ToString();
        GridView1.FooterRow.Cells[6].HorizontalAlign = HorizontalAlign.Center;
        GridView1.FooterRow.Cells[6].Font.Bold = true;
        GridView1.FooterRow.Cells[6].BackColor = System.Drawing.Color.Yellow;
        /*decimal totalAr = sTable.AsEnumerable().Sum(row => row.Field<decimal>("Credit"));
        GridView1.FooterRow.Cells[2].Text = totalAr.ToString();
        GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
        GridView1.FooterRow.Cells[2].Font.Bold = true;
        GridView1.FooterRow.Cells[2].BackColor = System.Drawing.Color.Yellow;*/
    }
    else
    {
        GridView1.Visible = false;
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('No Record Found.');", true);
        return;
    }
    con.conClose();
}

how to get solution pls

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

dharmend...

0 8

Reply
ANSWER
User: dharmendr
Replied: on Oct 25, 2021 04:13 AM
Report

Hi alhamd,

Check this example. Now please take its reference and correct your code.

Namespaces

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.pdf

Code

private DataTable GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);

                    return dt;
                }
            }
        }
    }
}

protected void ExportPDf_Click(object sender, EventArgs e)
{
    DataTable dtPurchase = GetData("SELECT * FROM tblPurchase");
    DataTable dtPayTransaction = GetData("SELECT * FROM tblPayTransaction");
    object[] dr = dtPurchase.AsEnumerable().Select(x => x["CustomerACC"]).Distinct().ToArray();

    string pdfpath = Server.MapPath("~/Reports/");
    if (!Directory.Exists(pdfpath))
    {
        Directory.CreateDirectory(pdfpath);
    }
    Document doc = new Document(PageSize.A4);
    PdfWriter.GetInstance(doc, new FileStream(pdfpath + "Reciept.pdf", FileMode.Create));
    Font boldFont = FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12);
    doc.Open();

    PdfPTable pdfTable = null;
    Paragraph paragraph = null;
    foreach (var item in dr)
    {
        //doc.NewPage();
        paragraph = new Paragraph(new Phrase("Ledger Report", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 15)));
        paragraph.SpacingBefore = 10f;
        paragraph.Alignment = Element.ALIGN_LEFT;
        doc.Add(paragraph);

        pdfTable = new PdfPTable(4);
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT;
        pdfTable.SetWidths(new float[] { .3f, .5f, .5f, .5f });
        pdfTable.SpacingBefore = 10f;
        pdfTable.DefaultCell.Border = 0;

        pdfTable.AddCell(new Phrase("Account: ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase(item.ToString(), boldFont));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)));
        doc.Add(pdfTable);

        paragraph = new Paragraph(new Phrase("Product Details", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 15)));
        paragraph.Alignment = Element.ALIGN_LEFT;
        doc.Add(paragraph);

        pdfTable = new PdfPTable(5);
        pdfTable.SpacingBefore = 10f;
        pdfTable.DefaultCell.Border = 0;
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT;
        pdfTable.SetWidths(new float[] { 0.5f, 2f, 1.5f, 1.5f, 1.5f });

        pdfTable.AddCell(new Phrase("S.I", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase("Product", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase("Qty", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase("Net Bal", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)));
        pdfTable.AddCell(new Phrase("P_Date", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)));

        DataRow[] drProduct = dtPurchase.Select("CustomerACC='" + item.ToString() + "'");
        decimal total = 0;
        for (int i = 0; i < drProduct.Length; i++)
        {
            pdfTable.AddCell(new Phrase((i + 1).ToString(), FontFactory.GetFont("Calibri", 10)));
            pdfTable.AddCell(new Phrase(drProduct[i]["Product"].ToString(), FontFactory.GetFont("Calibri", 10)));
            pdfTable.AddCell(new Phrase(drProduct[i]["Qty"].ToString(), FontFactory.GetFont("Calibri", 10)));
            pdfTable.AddCell(new Phrase(drProduct[i]["NetBal"].ToString(), FontFactory.GetFont("Calibri", 10)));
            pdfTable.AddCell(new Phrase(drProduct[i]["P_Date"].ToString(), FontFactory.GetFont("Calibri", 10)));
            total = total + Convert.ToDecimal(drProduct[i]["NetBal"]);
        }
        doc.Add(pdfTable);

        pdfTable = new PdfPTable(5);
        pdfTable.DefaultCell.Border = 0;
        pdfTable.DefaultCell.PaddingBottom = 10f;
        pdfTable.SpacingBefore = 10f;
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT;
        pdfTable.SetWidths(new float[] { .5f, 5.5f, 1.5f, 1.5f, 1.5f });

        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase("Total", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(total.ToString(), FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));

        int netBal = Convert.ToInt32(dtPayTransaction.Compute("SUM(Paid)", "CustormerACC = '" + item.ToString() + "'"));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase("Paid in [Cash Account]", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(netBal.ToString(), FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));

        int remBal = Convert.ToInt32(dtPayTransaction.Compute("SUM(RemBal)", "CustormerACC = '" + item.ToString() + "'"));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase("Rem Balance", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(remBal.ToString(), FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));
        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont("Calibri", 10)));

        doc.Add(pdfTable);
    }

    doc.Close();
}

VB.Net

Private Function GetData(ByVal query As String) As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)

                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Sub ExportPDf_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dtPurchase As DataTable = GetData("SELECT * FROM tblPurchase")
    Dim dtPayTransaction As DataTable = GetData("SELECT * FROM tblPayTransaction")
    Dim dr As Object() = dtPurchase.AsEnumerable().Select(Function(x) x("CustomerACC")).Distinct().ToArray()
    Dim pdfpath As String = Server.MapPath("~/Reports/")
    If Not Directory.Exists(pdfpath) Then
        Directory.CreateDirectory(pdfpath)
    End If

    Dim doc As Document = New Document(PageSize.A4)
    PdfWriter.GetInstance(doc, New FileStream(pdfpath & "Reciept.pdf", FileMode.Create))
    Dim boldFont As Font = FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 12)
    doc.Open()

    Dim pdfTable As PdfPTable = Nothing
    Dim paragraph As Paragraph = Nothing
    For Each item In dr
        'doc.NewPage()
        paragraph = New Paragraph(New Phrase("Ledger Report", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 15)))
        paragraph.SpacingBefore = 10.0F
        paragraph.Alignment = Element.ALIGN_LEFT
        doc.Add(paragraph)

        pdfTable = New PdfPTable(4)
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT
        pdfTable.SetWidths(New Single() {0.3F, 0.5F, 0.5F, 0.5F})
        pdfTable.SpacingBefore = 10.0F
        pdfTable.DefaultCell.Border = 0
        pdfTable.AddCell(New Phrase("Account: ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase(item.ToString(), boldFont))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Arial", 12, BaseColor.BLACK)))
        doc.Add(pdfTable)

        paragraph = New Paragraph(New Phrase("Product Details", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 15)))
        paragraph.Alignment = Element.ALIGN_LEFT
        doc.Add(paragraph)

        pdfTable = New PdfPTable(5)
        pdfTable.SpacingBefore = 10.0F
        pdfTable.DefaultCell.Border = 0
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT
        pdfTable.SetWidths(New Single() {0.5F, 2.0F, 1.5F, 1.5F, 1.5F})

        pdfTable.AddCell(New Phrase("S.I", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase("Product", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase("Qty", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase("Net Bal", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)))
        pdfTable.AddCell(New Phrase("P_Date", FontFactory.GetFont("Calibri", 10, Font.BOLD, BaseColor.BLACK)))

        Dim drProduct As DataRow() = dtPurchase.[Select]("CustomerACC='" & item.ToString() & "'")
        Dim total As Decimal = 0
        For i As Integer = 0 To drProduct.Length - 1
            pdfTable.AddCell(New Phrase((i + 1).ToString(), FontFactory.GetFont("Calibri", 10)))
            pdfTable.AddCell(New Phrase(drProduct(i)("Product").ToString(), FontFactory.GetFont("Calibri", 10)))
            pdfTable.AddCell(New Phrase(drProduct(i)("Qty").ToString(), FontFactory.GetFont("Calibri", 10)))
            pdfTable.AddCell(New Phrase(drProduct(i)("NetBal").ToString(), FontFactory.GetFont("Calibri", 10)))
            pdfTable.AddCell(New Phrase(drProduct(i)("P_Date").ToString(), FontFactory.GetFont("Calibri", 10)))
            total = total + Convert.ToDecimal(drProduct(i)("NetBal"))
        Next
        doc.Add(pdfTable)

        pdfTable = New PdfPTable(5)
        pdfTable.DefaultCell.Border = 0
        pdfTable.DefaultCell.PaddingBottom = 10.0F
        pdfTable.SpacingBefore = 10.0F
        pdfTable.HorizontalAlignment = Element.ALIGN_LEFT
        pdfTable.SetWidths(New Single() {0.5F, 5.5F, 1.5F, 1.5F, 1.5F})

        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase("Total", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(total.ToString(), FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))

        Dim netBal As Integer = Convert.ToInt32(dtPayTransaction.Compute("SUM(Paid)", "CustormerACC = '" & item.ToString() & "'"))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase("Paid in [Cash Account]", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(netBal.ToString(), FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))

        Dim remBal As Integer = Convert.ToInt32(dtPayTransaction.Compute("SUM(RemBal)", "CustormerACC = '" & item.ToString() & "'"))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase("Rem Balance", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(remBal.ToString(), FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        pdfTable.AddCell(New Phrase(" ", FontFactory.GetFont("Calibri", 10)))
        doc.Add(pdfTable)
    Next

    doc.Close()
End Sub

Screenshot

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

Display Report from multiple table in PDF Format using iTextSharp in ASP.Net

Company

Explore

Follow us

Contact Us