Creating a three column table in a pdf

The coding below reads data from a mySQL database, creates a pdf with a table in it and inserts the data into it.
I would like to modify it so that the table has three columns and each record from the query is inserted in the table, suitable for printing mailing labels. I realise that the SELECT statement would have to be changed to  create all the name and address fields. e.g:

Name1                                Name2                                     Name3
Address11                         Address21                              Address31
Address12                         Address22                              Address32

Name4                                .........
Address41
Address42

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using iTextSharp.text;
using iTextSharp.text.pdf;
using MySql.Data.MySqlClient;
 
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Document doc = new Document();
        PdfWriter.GetInstance(doc, new System.IO.FileStream("Test.pdf", System.IO.FileMode.Create));
        doc.Open();
        
        PdfPTable table = new PdfPTable(2);
        //actual width of table in points
        table.TotalWidth = 216f;
        //fix the absolute width of the table
        table.LockedWidth = true;
 
        //relative col widths in proportions - 1/3 and 2/3
        float[] widths = new float[] { 1f, 2f };
        table.SetWidths(widths);
        table.HorizontalAlignment = 0;
        //leave a gap before and after the table
        table.SpacingBefore = 20f;
        table.SpacingAfter = 30f;
 
        PdfPCell cell = new PdfPCell(new Phrase("Products 2"));
        cell.Colspan = 2;
        cell.Border = 0;
        cell.HorizontalAlignment = 1;
        table.AddCell(cell);
 
 
        String MyConString = "SERVER=xxxx;DATABASE=xxxx;UID=xxxx;PASSWORD=xxxxxx;";
        
 
        // Create the Connectionstring
     // PersistSecurityInfo = false means that the password is not displayed in the
     // connectionstring of the MySqlCommand.
     //
 
    
 
    MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
    connBuilder.Database ="lifenet";
    connBuilder.Password ="keizer0321";
    connBuilder.PersistSecurityInfo = false;
    connBuilder.Server = "MySQLB15.webcontrolcenter.com";
    connBuilder.UseCompression = true;
    connBuilder.UserID = "bogorman";
 
    // Create the connection
    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
    
    // Create the select command
    MySqlCommand comm = new MySqlCommand(
      "SELECT tblschools.fldSchool_ID, fldContact " +
      "FROM tblschools " +
      "RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID " +
      "WHERE tblselectedschools.fldUserID = @userID " +
      "AND tblselectedschools.fldSelected = @selected " +
      "ORDER BY fldName", conn);
    comm.Parameters.AddWithValue("userID", 2);
    comm.Parameters.AddWithValue("selected", 1);
 
    try {
      // Open and execute the command
      conn.Open();
      
      using (MySqlDataReader reader = comm.ExecuteReader()) {
        while (reader.Read()) {
          table.AddCell(reader[0].ToString());
          table.AddCell(reader[1].ToString());
        }
      }
      doc.Add(table);
      doc.Close();
    } finally {
      conn.Dispose();
      comm.Dispose();
    } 
 
    }
    }

Open in new window

bogormanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
oobaylyConnect With a Mentor Commented:
You should just have to construct the table, passing 3 as the number of columns required.
Change your widths array accordingly, and the colspan of the header row
Then just loop through the results as before:
using (MySqlDataReader reader = comm.ExecuteReader()) {
  while (reader.Read()) {
    string text = string.Format(
      "{0}\r\n{1}\r\n{2}\r\n{3}",
      reader["Name"],
      reader["Address1"],
      reader["Town"],
      reader["Postcode"]);
  
    table.AddCell(text);
  }
}    

Open in new window

0
 
bogormanAuthor Commented:
Thanks. Works fine now except for the top, bottom and side margins.
Assume I just juggle with TotalWidth, SpacingBefore, and SpacingAfter to adjust this.
I am hoping that the pdf produced will print with roughly the same spacing and layout on most printers and the text will fit on the labels. I suppose I could provide a dialog box to change the above parameters where the print is out of line with the labels.
Final coding is shown below.
Is there anything I should change in it? (apart from the username and password which I have changed!!!)

Document doc = new Document();
        PdfWriter.GetInstance(doc, new System.IO.FileStream("Test.pdf", System.IO.FileMode.Create));
        doc.Open();
        
        //PdfPTable table = new PdfPTable(2);
        PdfPTable table = new PdfPTable(3);
 
        //actual width of table in points
        //table.TotalWidth = 216f;
        table.TotalWidth = 550f;
        //fix the absolute width of the table
        table.LockedWidth = true;
 
        //relative col widths in proportions - 1/3 and 2/3
        //float[] widths = new float[] { 1f, 2f };
 
        //relative widths 1/3 1/3 1/3
        float[] widths = new float[] { 1f, 1f, 1f };
 
 
        table.SetWidths(widths);
        table.HorizontalAlignment = 0;
        //leave a gap before and after the table
        table.SpacingBefore = 20f;
        table.SpacingAfter = 30f;
 
        PdfPCell cell = new PdfPCell(new Phrase("Products 2"));
        //cell.Colspan = 2;
        cell.Colspan = 3;
        cell.Border = 0;
        cell.HorizontalAlignment = 1;
        table.AddCell(cell);
 
 
        String MyConString = "SERVER=xxx;DATABASE=xxx;UID=xxx;PASSWORD=xxx;";
        
 
        // Create the Connectionstring
     // PersistSecurityInfo = false means that the password is not displayed in the
     // connectionstring of the MySqlCommand.
     //
 
    
 
    MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
    connBuilder.Database ="xxx";
    connBuilder.Password ="xxx";
    connBuilder.PersistSecurityInfo = false;
    connBuilder.Server = "xxx";
    connBuilder.UseCompression = true;
    connBuilder.UserID = "xxxxx";
 
    // Create the connection
    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
    
    // Create the select command
    
    
    string cmdstring = "select fldContact, fldName, fldADDR1, fldADDR2, fldTOWN, fldCOUNTY, fldPOSTCODE, ad," +
           "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 1)," +
                             "LENGTH(SUBSTRING_INDEX(ad, char(26), 1 -1)) + 1)," +
                             "char(26), '') as addr1," +
           "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 2)," +
                             "LENGTH(SUBSTRING_INDEX(ad, char(26), 2 -1)) + 1)," +
                             "char(26), '') as addr2," +
           "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 3)," +
                             "LENGTH(SUBSTRING_INDEX(ad, char(26), 3 -1)) + 1)," +
                             "char(26), '') as addr3," +
           "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 4), " +
                             "LENGTH(SUBSTRING_INDEX(ad, char(26), 4 -1)) + 1)," +
                             "char(26), '') as addr4," +
           "REPLACE(SUBSTRING(SUBSTRING_INDEX(ad, char(26), 5)," +
                             "LENGTH(SUBSTRING_INDEX(ad, char(26), 5 -1)) + 1)," +
                             "char(26), '') as addr5 " +
      "from (" +
    "SELECT fldContact, fldName, fldADDR1, fldADDR2, fldTOWN, fldCOUNTY, fldPOSTCODE," +
           "concat_ws(char(26),nullif(trim(fldADDR1),'')," +
                              "nullif(trim(fldADDR2),'')," +
                              "nullif(trim(fldTOWN),'')," +
                              "nullif(trim(fldCOUNTY),'')," +
                              "nullif(trim(fldPOSTCODE),'')) as ad " +
      "FROM tblschools RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID " +
    "WHERE tblselectedschools.fldUserID = 2 AND tblselectedschools.fldSelected = 1 ORDER BY fldName, fldPOSTCODE) as v;";    
 
      
 
 
 
        MySqlCommand comm = new MySqlCommand(cmdstring, conn);
 
comm.Parameters.AddWithValue("userID", 2);
comm.Parameters.AddWithValue("selected", 1);
    
 
    try {
      // Open and execute the command
      conn.Open();
      
 
      using (MySqlDataReader reader = comm.ExecuteReader())
      {
          while (reader.Read())
          {
              string text = string.Format(
                "{0}\r\n{1}\r\n{2}\r\n{3}\r\n{4}\r\n{5}\r\n{6}",
                reader["fldContact"],
                reader["fldName"],
                reader["addr1"],
                reader["addr2"],
                reader["addr3"],
                reader["addr4"],
                reader["addr5"]);
 
              table.AddCell(text);
          }
      }    
 
 
 
      doc.Add(table);
      doc.Close();
    } finally {
      conn.Dispose();
      comm.Dispose();
    } 
 
    }
    }
 

Open in new window

0
 
bogormanAuthor Commented:
Thanks for your help. It is so simple when you know how!!
Was thinking on the lines of nested tables, etc, but your solution works perfectly.
Regards
Brian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.