I Use Excel To Write Code. Sue Me

I Use Excel To Write Code. Sue Me

I have done some weird things in my day, some of them I would not admit to freely — but using Excel for coding tasks is one of my guilty pleasures that I will not hide from anyone.

Always ask: What if I just used excel instead?

Forget the turing test, does your great program idea pass the Excel test? What if, for example, you were to use simple spreadsheet formulas to generate the report, did you know that Excel can easily do RegEx replacements, if statements, and that you can even call online functions and get up to date prices from online services?

Excel can connect to real Databases, can produce .csv and JSON files, you can query it from the outside using Python or other programming languages. You can use spreadsheets as your database, use it for content generation and you can import them into WordPress sites to mass-create thousands of posts for news aggregator or video websites.

It’s all there for you — if you’re willing to be ridiculed by weaker developers who have subscribed to the superiority but don’t actually know they are wasting everyone’s time.

Test case generation

For a recent project at work I needed about two-hundred test cases — all based on various parameters that needed to be tested individually and together with others.

So I wrote an excel matrix, my main test cases in rows and all the individual parameters in columns and then I could simply write a formula to generate the test cases from that and drag it down as far as I needed it to.

Then I could even use a formula like this:

="[TestCase(""&A2&"")]"

 Save

That would generate decorator test cases for the automated tests I had to run.

SQL Queries

While I routinely write SQL queries using Excel let me revert to the previous example to showcase just how great this is: For each of the test cases I would have to generate a corresponding SQL query — two hundred of them.

Naturally that was no option I looked forward to, so instead I wrote the script once, then mapped the values to database columns using string comparison and if clauses. Something like this:

="union select id from tb_test test where test.customer_property "&IF(A2="larger";">";"<")&"5;"

 Save

Take particular note of that union there because it allowed me to run the script once, copy all the resulting ids from the result set and paste them right back into my spreadsheet.

From there then I could even use those ids and the parameters to manipulate test data and change any values I might want to differently for specific test cases.

Database mappings

One of the most annoying tasks I have ever come across is mapping database columns to code objects — those are the days I think about retiring headfirst out of a window.

Related  I Learned Coding Mostly From Porn

Until I started using Excel, that is. Using spreadsheets you can just copy the database table declaration and use the values to write a short formula that builds object notations like this:

string test_column = Convert.ToString("test_column");

 Save

As you can see I can construct that whole line programatically as long as I know the original column name and data type. At one point I had to map fifteen tables this way — I realize there are better options to do this using automatic mappers, but not in that project I was working on.

Todo and error tracking

I once worked with a guy who insisted that Excel was grossly inadequate for project planning and issue tracking — so I had a two-hour meeting with him where we entered it into his PowerPoint. I wish I was joking, but apparently colored boxes spread across multiple slides made everything a lot more colorful and better in every way.

Among the useful people at work Excel is a lot more established for project planning — timelines especially are super easy to set up this way when you can shift rows and columns and seamlessly enter dates.

In fact, for larger projects this stage is much, much more convenient than tracking everything in a proper issue management system like Jira from the get go — you can do all that once the timeline is established and everyone agrees on the individual tasks. I remember a grossly wasted week as we continuously had to start fresh on a Jira board as our project lead realized that the timeline he had established made no sense.

The other real benefit at this stage is that non-IT staff is immediately familiar with Excel, but trying to get them to use a new system will needlessly delay the start of the planning phase. If someone comes around to convert that list to a format he likes afterward that is their problem or benefit, but if you ask me these early stages are where Excel really shines for everyone involved.

Summary: I pity the spreadsheet averse

The older I get the more it annoys me when people hate on things without understanding them.

The same is true for Excel: it is an incredibly powerful tool if you can wrap your head around its possibilities — but you only need one guy on a team who facepalms and says “oh god, please not Excel” and immediately we waste an hour as he explains to us the superiority of whatever tool of the day he prefers. Other tools come and go, Excel is there for you when you need a strong shoulder to cry on.

Related

Comments

No Comments Yet!

You can be first to comment this post!

Post Reply