Calculate Sum and Percentage for each column and Export to PDF in C# and VB.Net

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on May 13, 2019 07:15 PM
Forum: ASP.Net Thirdparty controls
Answer: 1
Views: 6906
Sample Code: Download

I have the following data

SessionName	Term	Exam	AdmissionNo	Subject	Max	Mark
S-19	Term-1	A1-T1	R-01	English	40	23
S-19	Term-1	A1-T1	R-02	English	40	25
S-19	Term-1	A1-T1	R-01	Math	40	28
S-19	Term-1	A1-T1	R-02	Math	40	30
S-19	Term-1	A2-T1	R-01	English	60	43
S-19	Term-1	A2-T1	R-02	English	60	25
S-19	Term-1	A2-T1	R-01	Math	60	28
S-19	Term-1	A2-T1	R-02	Math	60	30
S-19	Term-2	A1-T2	R-01	English	40	33
S-19	Term-2	A1-T2	R-02	English	40	25
S-19	Term-2	A1-T2	R-01	Math	40	28
S-19	Term-2	A1-T2	R-02	Math	40	30
S-19	Term-2	A2-T2	R-01	English	60	53
S-19	Term-2	A2-T2	R-02	English	60	25
S-19	Term-2	A2-T2	R-01	Math	60	28
S-19	Term-2	A2-T2	R-02	Math	60	30

Code :

private void tbGetData_Click(object sender, EventArgs e)
{
    DataTable dt = GetFromTable();
    this.dGV.DataSource = dt;
}
private DataTable GetFromTable()
{

    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand(@"SELECT SessionName,TermName,ExamType,AcademicName,ClassName,SectionName,SPic,fa.AdmissionNo,RollNo,SName,FName,FPhone,SubjectName,Max,Mark FROM tblSetMarks as fa
                                            inner join tblSession as ses on fa.SessionID=ses.SessionID
                                            inner join tblSetTerm as ste on fa.TermID=ste.TermID
                                            inner join tblSetExam as st on fa.SetExamID=st.SetExamID
                                            inner join tblStdReg as sr on fa.AdmissionNo=sr.AdmissionNo
                                            inner join tblAcademicYear as ay on sr.YearID=ay.YearID
                                            inner join tblDefClass as dc on sr.ClassID=dc.ClassID
                                            inner join tblDefSection as ds on sr.SectionID=ds.SectionID    
                                            inner join tblAssignSubjects as fh on fa.SubjectID=fh.ASID inner join tblDefSubject as df on fh.SubjectID=df.SubjectID
                                            group by SPic,SessionName,TermName,ExamType,AcademicName,ClassName,SectionName,fa.AdmissionNo,RollNo,SName,FName,FPhone,SubjectName,Max,Mark order by AdmissionNo,SubjectName asc;", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}
private void tbPageTwo_Click(object sender, EventArgs e)
{
    string pdfpath = @"C:\Reports\";
    if (!Directory.Exists(pdfpath))
    {
        Directory.CreateDirectory(pdfpath);
    }

    string folderPath = @"C:\Reports\";
    FileStream stream = new FileStream(folderPath + "FinalResultCard-2 on " + DateTime.Now.ToShortDateString() + ".pdf", FileMode.Create);
    Document doc = new Document(PageSize.LEGAL);
    PdfWriter.GetInstance(doc, stream);
    doc.Open();

    DataTable sTable = GetFromTable();
    var grouped = from x in sTable.AsEnumerable()
                    group x by new { a = x["AdmissionNo"] } into g
                    select new
                    {
                        Value = g.Key,
                        ColumnValues = g
                    };

    DataTable dtfinal = null;
    foreach (var key in grouped)
    {
        dtfinal = sTable.Clone();

        foreach (var columnValue in key.ColumnValues)
        {
            dtfinal.ImportRow(columnValue);
        }
        var groupbySubject = from x in dtfinal.AsEnumerable()
                                group x by new { a = x["SubjectName"] } into g
                                select new
                                {
                                    Value = g.Key,
                                    ColumnValues1 = g
                                };

        DataTable dt2 = null;
        foreach (var item in groupbySubject)
        {
            dt2 = dtfinal.Clone();
            foreach (var columnValue in item.ColumnValues1)
            {
                dt2.ImportRow(columnValue);
            }               

            for (int j = 0; j < dt2.Rows.Count; j++)
            {
                table1.AddCell(new Phrase(dt2.Rows[j]["ExamType"].ToString().Replace('-', '.'), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
            }

            for (int j = 0; j < dt2.Rows.Count + 1; j++)
            {
                if (j == 1)
                {
                    table11.AddCell(new Phrase(no1, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
                    for (int k = 0; k < dt2.Rows.Count; k++)
                    {
                        table11.AddCell(new Phrase(dt2.Rows[k]["Mark"].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
                    }
                }

            }

            doc.Add(table11);

        }
        PdfPTable table111 = new PdfPTable(5);
        table111.TotalWidth = 500f;
        table111.LockedWidth = true;
        float[] widths111 = new float[] { 0.1f, 0.1f, 0.1f, 0.1f, 0.1f };
        table111.SetWidths(widths111);
        table111.DefaultCell.Padding = 3f;
        table111.HorizontalAlignment = Element.ALIGN_CENTER;
        table111.SpacingBefore = 5f;
        table111.AddCell(new Phrase("Obt", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("Max", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("%", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("Grade", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("Position", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        table111.AddCell(new Phrase("", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        doc.Add(table111);
    }
}

sir i want output in the following format as per asked in my question

for R-01 it should like this

Subject A1-T1 A2-T1 A1-T2 A2-T2

40 60 40 60

1-English 23 43 33 53

2-Math 28 28 28 28

Obtain 51 71 61 81

Max 80 120 80 120

% 64 59.16 76.25 68

then in the next page

for R-02 it should like this

Subject A1-T1 A2-T1 A1-T2 A2-T2

40 60 40 60

1-English 23 40 33 53

2-Math 27 40 33 28

Obtain 50 80 66 81

Max 80 120 80 120

% 63 67 83 68

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

pandeyis...

0 10

Reply
ANSWER
User: pandeyism
Replied: on May 17, 2019 04:43 AM
Modified: on May 21, 2019 08:03 AM
Report

Hi smile,

Refer below sample.

Namespaces

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

Code

protected void Page_Load(object sender, EventArgs e)
{
    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();

    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[]{new DataColumn("SessionName",typeof(string)),
                        new DataColumn("Term",typeof(string)),
                        new DataColumn("Exam",typeof(string)),
                        new DataColumn("AdmissionNo",typeof(string)),
                        new DataColumn("Subject",typeof(string)),
                        new DataColumn("Max",typeof(int)),
                        new DataColumn("Mark",typeof(int))});

    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-01", "English", 40, 23);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-01", "Math", 40, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-01", "Urdu", 40, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-01", "Science", 40, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-01", "Computer", 40, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-01", "English", 60, 43);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-01", "Math", 60, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-01", "Urdu", 60, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-01", "Science", 60, 28);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-01", "Computer", 60, 28);

    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-01", "English", 40, 33);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-01", "Math", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-01", "Urdu", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-01", "Science", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-01", "Computer", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-01", "English", 40, 25);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-01", "Math", 40, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-01", "Urdu", 40, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-01", "Science", 40, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-01", "Computer", 40, 30);

    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-02", "English", 40, 25);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-02", "Math", 40, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-02", "Urdu", 40, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-02", "Science", 40, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T1", "R-02", "Computer", 40, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-02", "English", 60, 25);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-02", "Math", 60, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-02", "Urdu", 60, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-02", "Science", 60, 30);
    dt.Rows.Add("S-19", "Term-1", "A1-T2", "R-02", "Computer", 60, 30);

    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-02", "English", 40, 53);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-02", "Math", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-02", "Urdu", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-02", "Science", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T1", "R-02", "Computer", 40, 28);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-02", "English", 60, 25);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-02", "Math", 60, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-02", "Urdu", 60, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-02", "Science", 60, 30);
    dt.Rows.Add("S-19", "Term-2", "A2-T2", "R-02", "Computer", 60, 30);

    DataSet dsAdmissionNo = new DataSet();
    DataView viewAdmissionNo = new DataView(dt);
    DataTable distinctAdmissionNoTable = viewAdmissionNo.ToTable(true, "AdmissionNo");
    for (int i = 0; i < distinctAdmissionNoTable.Rows.Count; i++)
    {
        DataTable dtAdmissionNo = dt.Select("AdmissionNo='" + distinctAdmissionNoTable.Rows[i][0] + "'").CopyToDataTable();
        DataView viewExam = new DataView(dtAdmissionNo);
        DataTable distinctExamTable = viewExam.ToTable(true, "Exam");
        DataTable dtFinal = new DataTable();
        dtFinal.TableName = "tbl_" + distinctAdmissionNoTable.Rows[i][0];
        dtFinal.Columns.Add("Subject");
        for (int j = 0; j < distinctExamTable.Rows.Count; j++)
        {
            dtFinal.Columns.Add(distinctExamTable.Rows[j][0].ToString(), typeof(int));
        }

        DataView viewSubject = new DataView(dtAdmissionNo);
        DataTable distinctSubject = viewSubject.ToTable(true, "Subject");
        for (int k = 0; k < distinctSubject.Rows.Count; k++)
        {
            DataRow dr = dtFinal.NewRow();
            dr["Subject"] = distinctSubject.Rows[k][0];
            dtFinal.Rows.Add(dr);
        }
        dsAdmissionNo.Tables.Add(dtFinal);
    }

    for (int i = 0; i < dsAdmissionNo.Tables.Count; i++)
    {
        DataTable dataTable = dsAdmissionNo.Tables[i];
        string admissionNo = dsAdmissionNo.Tables[i].TableName.Split('_')[1];
        for (int row = 0; row < dataTable.Rows.Count; row++)
        {
            for (int column = 1; column < dataTable.Columns.Count; column++)
            {
                dataTable.Rows[row][column] = dt.Select("Subject='" + dataTable.Rows[row][0] + "' AND Exam='" + dataTable.Columns[column].ColumnName + "' AND AdmissionNo='" + admissionNo + "'")[0].ItemArray[6];
            }
        }
    }
    for (int k = 0; k < dsAdmissionNo.Tables.Count; k++)
    {
        PdfPTable pdfTable = new PdfPTable(dsAdmissionNo.Tables[0].Columns.Count);
        DataTable admissionNoTable = dsAdmissionNo.Tables[k];
        for (int l = 0; l < admissionNoTable.Columns.Count; l++)
        {
            pdfTable.AddCell(new Phrase(admissionNoTable.Columns[l].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }

        pdfTable.AddCell(new Phrase(" ", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        for (int l = 1; l < admissionNoTable.Columns.Count; l++)
        {
            string exam = admissionNoTable.Columns[l].ToString();
            string admission = admissionNoTable.TableName.Split('_')[1];
            int max = Convert.ToInt32(dt.Select("Exam='" + exam + "' AND AdmissionNo='" + admission + "'")[0].ItemArray[5]);
            pdfTable.AddCell(new Phrase(max.ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }

        for (int j = 0; j < admissionNoTable.Rows.Count; j++)
        {
            for (int l = 0; l < admissionNoTable.Columns.Count; l++)
            {
                if (l == 0)
                {
                    pdfTable.AddCell(new Phrase((j + 1) + "-" + admissionNoTable.Rows[j][l].ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
                }
                else
                {
                    pdfTable.AddCell(new Phrase(admissionNoTable.Rows[j][l].ToString(), FontFactory.GetFont(FontFactory.HELVETICA, 10)));
                }
            }
        }

        pdfTable.AddCell(new Phrase("Obtain", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        for (int l = 1; l < admissionNoTable.Columns.Count; l++)
        {
            int sum = Convert.ToInt32(admissionNoTable.Compute("SUM([" + admissionNoTable.Columns[l].ColumnName + "])", string.Empty));
            pdfTable.AddCell(new Phrase(sum.ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }

        pdfTable.AddCell(new Phrase("Max", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        for (int l = 1; l < admissionNoTable.Columns.Count; l++)
        {
            string exam = admissionNoTable.Columns[l].ToString();
            string admission = admissionNoTable.TableName.Split('_')[1];
            int max = Convert.ToInt32(dt.Select("Exam='" + exam + "' AND AdmissionNo='" + admission + "'")[0].ItemArray[5]);
            pdfTable.AddCell(new Phrase((max * admissionNoTable.Rows.Count).ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }

        pdfTable.AddCell(new Phrase("%", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        for (int l = 1; l < admissionNoTable.Columns.Count; l++)
        {
            string exam = admissionNoTable.Columns[l].ToString();
            string admission = admissionNoTable.TableName.Split('_')[1];
            int max = Convert.ToInt32(dt.Select("Exam='" + exam + "' AND AdmissionNo='" + admission + "'")[0].ItemArray[5]);
            max = max * admissionNoTable.Rows.Count;
            int obtained = Convert.ToInt32(admissionNoTable.Compute("SUM([" + admissionNoTable.Columns[l].ColumnName + "])", string.Empty));
            pdfTable.AddCell(new Phrase((obtained * 100 / max).ToString(), FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }
        pdfTable.AddCell(new Phrase("Grade", FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        for (int l = 1; l < admissionNoTable.Columns.Count ; l++)
        {
            string grade = "";
            string exam = admissionNoTable.Columns[l].ToString();
            string admission = admissionNoTable.TableName.Split('_')[1];
            int max = Convert.ToInt32(dt.Select("Exam='" + exam + "' AND AdmissionNo='" + admission + "'")[0].ItemArray[5]);
            max = max * admissionNoTable.Rows.Count;
            int obtained = Convert.ToInt32(admissionNoTable.Compute("SUM([" + admissionNoTable.Columns[l].ColumnName + "])", string.Empty));
            int percentage = obtained * 100 / max;
            if (percentage >= 90)
            {
                grade = "O";
            }
            else if (percentage >= 80 && percentage <= 89)
            {
                grade = "A+";
            }
            else if (percentage >= 70 && percentage <= 79)
            {
                grade = "A";
            }
            else if (percentage >= 50 && percentage <= 69)
            {
                grade = "B";
            }
            else
            {
                grade = "F";
            }
            pdfTable.AddCell(new Phrase(grade, FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 10)));
        }
        doc.Add(pdfTable);
        doc.NewPage();
    }
    doc.Close();
}

Screenshot

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

Calculate Sum and Percentage for each column and Export to PDF in C# and VB.Net

Company

Explore

Follow us

Contact Us