Friday, May 30, 2008

Quick Code Generation With Excel

Example Download: Example Excel Code Generation Excel File

Often times, I have found myself in a place where I want to generate some code from a list of items that I was given or that I had created, but were only a one-time thing and creating an xslt transform, a mini code generation program, or a template in a code generation suite such as CodeSmith Studio or MyGeneration would be overkill.

In the past, I would just write all the code out by hand while copy and pasting from my list. There were a couple problems with this approach, which I'm sure many of you are familiar with: you often make copy-paste errors, and it is a lot more time consuming than it needs to be.

Recently, I have discovered the power of using Excel to help me with these kinds of tasks. All you need to know is a few functions and operators and how to copy and paste, and you can significantly cut down the time that it takes to finish such tasks.

Starting Out

Make sure you have Excel or another spreadsheet application with similar functionality (Many who do not want to pay the premium for the Microsoft Office suite are using Open Office, an open source office suite).

The Basics

The main thing to understand before you start is that anything that has carriage returns can be copied and pasted into excel as cells in a column. Once you have these values separated into cells, you can work with the values quite easily.

I feel that is easiest to learn through examples, so I will demonstrate how to generate code by using the example of making an insert script for a static company table. You get a list of companies to be inserted from your customer. Each record will have an integer id, and the company name. You also need to make an enumeration for each of the companies with no spaces, commas, or periods.

Here's your list (These are the first 40 Fortune 500 companies in alphabetical order):

3M Company
Abbott Laboratories
Advance Auto Parts, Inc.
Advanced Micro Devices, Inc.
Aetna Inc.
Affiliated Computer Services, Inc.
Aflac Incorporated
AGCO Corporation
Agilent Technologies, Inc.
Air Products and Chemicals, Inc.
AK Steel Holding Corporation
Albertson's, Inc.
Alcoa Inc.
Allied Waste Industries, Inc.
ALLTEL Corporation
Altria Group, Inc., Inc.
Ameren Corporation
American Electric Power Company, Inc.
American Express Company
American Family Insurance Group
American Financial Group, Inc.
American International Group, Inc.
American Standard Companies Inc.
AmerisourceBergen Corporation
Amgen Inc.
AMR Corporation
Anadarko Petroleum Corporation
Anheuser-Busch Companies, Inc.
Aon Corporation
Apache Corporation
Apple Computer, Inc.
Applied Materials, Inc.
ARAMARK Corporation
Arrow Electronics, Inc.
ArvinMeritor, Inc.
Asbury Automotive Group, Inc.
Ashland Inc.
Assurant, Inc.

You copy this list and single click the first cell in your Excel sheet.  It should appear like so:

Now we're going to use some formulas along with concatenating strings to achieve our code generation.  If you are building anything dynamic, you must always start with an equals ('=') sign. Example: '= A1' would make the cell in which you currently are show up as the first cell in your worksheet like so:

The functions and operators that I find most useful:
  • & - you use this to concatenate different values together
    • Example: ="first value" & "second value"
  • SUBSTITUTE - Will replace one value with another in a given text
    • Example: =SUBSTITUTE(A1," ","") will replace all empty spaces with nothing

    • LEFT, RIGHT, and MID - Will find the position of a string within a given text

    • Alt + Enter - will put a carriage return in your cell (Enter will take you to the next cell)

    • ROW - will give you the row number of a reference

    LET'S DO IT!

    According to the example description, you need to do a couple things:

    • You need to create the insert statement for company.

    • You need to remove the spaces, commas and periods from the name for the enumeration.

    Let's do the first one. The insert statement will most likely look similar to this:

    INSERT INTO Company (Id, Description) Values (<number>, '<description>')

    First we know that we're going to need an Id. We can use the 'ROW' function described above. Next, we know we can just use the company name as the description, we just need to add quotes.

    Our formula will look like this:

    ="INSERT INTO Company (Id, Description) Values (" & ROW(A1) & ", '" & A1 & "')"


    Now we can copy and paste or just drag our formula down using the little square in the bottom right corner of the cell of our formula like so:


    Now for the enumeration. We will need to remove all spaces, commas and periods in the company name and put a comma at the end. We will use the SUBSTITUTE function to replace the necessary characters and concatenate a comma to the end of each line with the following formula:

    = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""),".",""),",","") & ","


    This may look a little hairy, but all we're doing is removing the spaces, removing the periods from that text, and then removing the commas from that text then concatenating a comma to the end.

    As you can see, it is really easy to get some nice, quick code generation out of excel in a matter of minutes, sometimes seconds if you just know a few tricks. Download the excel file below to take a closer look:


    Example Excel Code Generation Excel File

    Tuesday, May 27, 2008

    Creating Pdf's with Adobe Acrobat, C# and ItextSharp

    Recently I have been creating a large number of dynamic pdf's for a client. I have learned a lot of ways to poorly create pdf files and have also found some pretty good ones. I'm hoping that this post will help others avoid some of the pitfalls and blocks that I've run into while generating dynamic pdf files.

    This post will most likely not cover a solution to all scenarios, but will likely be a solution or at least a starting point to most pdf generation problems.

    I have included with this post a sample solution from which I have drawn the example code snippets and screen shots in this post. I've included links to these example files at the end of the post also.


    Zipped Example Solution
    Sample Pdf Form
    Example Excel File

    Things you will want to do before you begin:

    ItextSharp - An Open Source PDF Creation/Manipulation Library

    ItextSharp is a port from another open source project called itext, which is a long running java, open-source pdf library ( Between the different projects, there is a pretty good following, and as a result, there are quite a few examples of people accomplishing different tasks between the two projects. Luckily, java code is a lot like C# and is pretty easy to convert over with just a few tweaks.

    Adobe Acrobat Professional- A PDF Creation/Manipulation Desktop Application

    Acrobat does about anything that you could ever want to do with a pdf file in a desktop application. For this post, It will primarily be used to create pdf forms that we will fill in with our code. To see an example of how to create a form, go to

    Starting Out

    According to my understanding, most people who have to generate some sort of Pdf will want to do one or more of the following actions:

    • Put text into certain areas of a pdf page (like a gift card)
    • Put dynamic table-style information on a pdf (like an invoice)
    • Put images in certain regions of a pdf page (like a graph)

    In this post I cover how to put text into certain areas of a pdf. I will try to cover the other two actions in subsequent posts.

    In almost all cases, in order to do any of these things, you will want a "template" pdf to start out with. There are ways to build the entire pdf in code, but having a base to start out with can save a lot of time. Most pdf desktop applications allow you to export documents into a pdf (Word, Excel, etc.). I usually have created my "template" pdf in excel and then have used the print to pdf option to create my pdf.

    Creating a Form from a PDF Page

    In order to put text in certain regions of a pdf file, I've found it best to create a pdf form (as in the forms you often find online that allow you to type the information directly onto the pdf) which allows you to choose the format and alignment of the text. I will go through the steps you would take if using Adobe Acrobat Professional as your pdf form creation software.

    First, open your "template" pdf file to which you want to add text. For my example, I will use a pdf that represents a template for an address change notice that a company might want to send out that I created from excel.

    Second, open up the Form toolbar


    Third, add the form text fields to your pdf and give each of the fields unique, intuitive names according to what text will be put in the field.


    Fourth, make the appropriate tweaks to the formatting and alignment with the 'Appearance' and 'Options' tabs of the 'Text Field Properties' window.

    Note: To have more direct control over the size of the text, use an absolute font size instead of the default 'Auto' size. Also, the options tab will give you the ability to set the alignment of your text and set the field to 'Multiline' (like a textbox in windows forms).

    image image

    Fifth, make sure that the size and formatting of the text is going to be appropriate for your pdf. Click 'Preview' on the Forms toolbar and type something in each of your form text fields to ensure that the characters are showing up as expected.

    Note: Make sure that you delete the text that you have typed into the fields before you save it for good.


    Show Me Some Code Already!!!

    Now you should have a pdf with form text fields in it for each of the areas on the pdf in which you want text displayed. Now let's show you how to put any text from our code into the text fields that we created in our form. Here are the steps you would take to fill the form:

    First, you need to add a reference to the ITextSharp library in your project (you can download the library at For my example, I have made a windows form application with fields that correspond with the fields on the pdf form I created.

    Second, you create a PdfReader that your code will use to read the pdf form that you created:

    PdfReader reader = new PdfReader(<unfilled pdf file path goes here>); 

    Third, you make a PdfStamper which allows you to put text into your form fields:

    PdfStamper formFiller = new PdfStamper(reader, new FileStream(<ouput file path goes here>, FileMode.<file mode>));

    Fourth, you get the form text fields (AcroFields) from the pdf file:

    AcroFields formFields= formFiller.AcroFields;

    Fifth, you use the SetField method of the AcroFields object to put your text into the field:

    formFields.SetField("<field name from pdf>", <value to insert>);

    Sixth, you 'flatten' your pdf by removing the fields while leaving the values of the fields in tact:

    formFiller.FormFlattening = true;

    Seventh, close your PdfStamper and PdfReader objects to make sure that all resources are disposed of:


    Your done! After following these steps, you should now have a pdf filled with any values you specified.

    Here is an example windows form code beside:

        1 using System;

        2 using System.Collections.Generic;

        3 using System.ComponentModel;

        4 using System.Data;

        5 using System.Drawing;

        6 using System.Linq;

        7 using System.Text;

        8 using System.Windows.Forms;

        9 using iTextSharp.text.pdf;

       10 using System.IO;

       11 using System.Diagnostics;


       13 namespace PdfStamperSample

       14 {

       15     public partial class PdfTestForm : Form

       16     {

       17         public PdfTestForm()

       18         {

       19             InitializeComponent();

       20         }


       22         private void btnCreatePdf_Click(object sender, EventArgs e)

       23         {

       24             // Grab each of the values from our windows form

       25             string accountNumber = txtAccountNumber.Text;

       26             DateTime pdfDate = datePdfDate.Value;

       27             string oldAddress = txtOldAddress.Text;

       28             string newAddress = txtNewAddress.Text;


       30             string outputFilePath = @"..\..\AddressChangeNotice_filled.pdf";


       32             // Get pdf from project directory

       33             PdfReader reader = null;

       34             try

       35             {

       36                 reader = new PdfReader(@"..\..\AddressChangeNotice.pdf");


       38                 // Create the form filler

       39                 using (FileStream pdfOutputFile = new FileStream(outputFilePath, FileMode.Create))

       40                 {

       41                     PdfStamper formFiller = null;

       42                     try

       43                     {

       44                         formFiller = new PdfStamper(reader, pdfOutputFile);


       46                         // Get the form fields

       47                         AcroFields addressChangeForm = formFiller.AcroFields;


       49                         // Fill the form

       50                         addressChangeForm.SetField("AccountNumber", accountNumber);

       51                         addressChangeForm.SetField("Date", pdfDate.ToShortDateString());

       52                         addressChangeForm.SetField("OldAddress", oldAddress);

       53                         addressChangeForm.SetField("NewAddress", newAddress);


       55                         // 'Flatten' (make the text go directly onto the pdf) and close the form

       56                         formFiller.FormFlattening = true;

       57                     }

       58                     finally

       59                     {

       60                         if (formFiller != null)

       61                         {

       62                             formFiller.Close();

       63                         }

       64                     }

       65                 }

       66             }

       67             finally

       68             {

       69                 reader.Close();

       70             }


       72             // Open the created/filled pdf

       73             if (MessageBox.Show(String.Format("Pdf filled - {0}.\nWould you like to open the filled pdf?",

       74                 Path.GetFullPath(outputFilePath)),

       75                 "PDF Created", MessageBoxButtons.YesNo) == DialogResult.Yes)

       76             {

       77                 Process.Start(outputFilePath);

       78             }


       80         }

       81     }

       82 }


    Zipped Example Solution
    Sample Pdf Form
    Example Excel File