Make Spreadsheets Code For You

Have you ever come across a situation in coding, either at your job or when you are working on your personal project, when you feel that you need to keep on writing the same pattern of code again and again?

Now all you hard core programmers - do not put me on stake. I know it is a bad design in such case, but sometimes you are not at a liberty of designing the code the way you want. This is especially the case when you have to work as a team or even as multiple teams. Also, you may be thinking of writing a generic code to handle all those - but getting at this generic code takes time, which you may not be having at hand. You can even use the trusty copy-paste method, but that too becomes tedious.

Here is a technique that I use to generate the code. I use Excel. Well, I usually need this for my job, and they give me Microsoft Excel, and I work with it. You can also use OpenOffice.org Calc or any other spread sheet to do the same.

Here is a piece of java code, which needs to be written.

...
// Declare variable to hold
// response from WebService
String vAlpha = null;
String vBeta = null;
String vDelta = null;
...
String vGamma = null;
String vZeta = null

...

As you can see, a lot of the code or parts of code are repeated. It just does not make sense to type them all.

I just open up a blank spread sheet, and just type in the basic set of things that are different. In this case, they are the local variables (which I chose to name after some labels in the object. Just fill up the first column with all the different core parts of the code:

Alpha
Beta
Delta
...
Gamma
Zeta

If there are any variations, that are used more than once, then you can as well create a derivative of those in the next column. In this case, we have vAAAAAA, where AAAAAA are the lables. Go to the first row of the B column, and type in this forumula:

="v"&A1

Now, just drag and copy it to the remaining rows. Now you have all of those words handy.

Now, we will attempt at creating a chunk of code for declaration. Again go to the next column - column C, and use the following formula:

="String "&B1&" = null;"

Select the full column, and you have a nice code block that you can copy and paste into your source file. The next one is a bit more complex due to the use of double quotes, but it is not unachievable.

=B1&" = someObject.get("&""""&A1&""""&").replaceAll("&""""&"xyz"&""""&","&""""&"abc"&""""&");"

Here, you will notice that I am using both A and B columns for different parts of the code. Copy the formula to the rest of the rows, and you have the rest of the code.

This particular tip is illustrated using a specific example, and the formulae are for Microsoft Excel. You should be able to deduce the formulae for other spreadsheet applications and for your own chunks of code.