An example of how Excel can boost your coding productivity

During my working time in software companies, there are ton of cases which the support of Excel helps alot.
Today for an example.

I have to add rules to validate many fields with the similar name like below:

  • open1post
  • open1person
  • open2corp
  • open2post
  • open2person
  • open3corp
  • …and so on…

I need to create some code lines like below:

  • $val->add(‘open1corp’, $multi_lang_items[‘lbl’][23])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open1post’, $multi_lang_items[‘lbl’][23])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open1person’, $multi_lang_items[‘lbl’][23])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open2corp’, $multi_lang_items[‘lbl’][24])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open2post’, $multi_lang_items[‘lbl’][24])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open2person’, $multi_lang_items[‘lbl’][24])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • $val->add(‘open3corp’, $multi_lang_items[‘lbl’][25])
    ->add_rule(‘match_pattern’, ‘/^[0-9]*$/’);
  • …and so on…

So, in excel:

  1. First column, I store a list of fields, name it “Form control name”.
  2. Because, a label is provided to 3 fields, for example: “open1corp”, “open1post”, “open1person” use the same lbl 23.

So that, we have to calculate the same number for each open[x][post_fix]. In order to achieve that, I used the magic of mod like below formula.

Note that, 19 is the row number of the first open[x][post_fix] item.

=CONCATENATE("$val->add('",[@[Form control name]],"', $multi_lang_items['lbl'][",ROW()-MOD(ROW()-1,3)+4-2*(FLOOR.MATH((ROW()-19)/3)),"])
 ->add_rule('match_pattern', '/^[0-9]*$/');")

And the result we get:
Excel productivity
That’s it, now we can copy those code to our IDE.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s