Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Creating a three column table in a pdf

Posted on 2009-04-14
3
373 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:bogorman
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
ID: 24142187
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
 

Author Comment

by:bogorman
ID: 24148715
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
 

Author Closing Comment

by:bogorman
ID: 31570069
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question