Doing simple code generation with MS Excel -

Doing simple code generation with MS Excel


Microsoft Excel has been anefficient tool for daily engineering work. Its usage can range fromsimple calculations to graph plotting for data analysis. This articleintroduces another useful aspect of Excel, which makes use of string processing functions forcodegeneration. We will explore several functions with examples of when andhow to use them.

Accessing IO ports is quite common for embedded programming. Forexample, we can output pin by pin (bit-level assignment) or output thewhole port (byte-level assignment). Figure1 below shows how we can achieve this with Excel.

Figure1: Code generation for port pin settings

Those inred color are the inputs for we need to set,and those in blue color arethe generated code we can copy out and use right away. The formulaentered at D3 is $B$ 1 & “_” & A3 & “=” & B3 & “;” . Herewe make use of the & operator for string concatenation. After entering the formula for D3,simply drag down to copy the formula for D4 to D10. Refer to [1] for the tip on how to generatethe sequence of number for the Bitindex column.

The formula entered at D13 is B1 & “=0x” & BIN2HEX ( B3 & B4 & B5 & B6 & B7 & B8 & B9 & B10,2 ) & “;” . Thistime another function is used, bin2hex [2] , which simply converts abinary number (in text format) to a hex number.

The above spreadsheet can help us automate some tedious tasks, suchas to generate the bit assignment statements, or to calculate theequivalent hex number from a string of binaries.

As another example, typically before we can use p9 as an output port, portdirection register pd9 has tobe configured. By simply entering pd9 into B1, and enter the bit values accordingly in B3 to B10, we can getthe generated code for pd9 settings.

As another example, Figure 2 below shows a description of registers U0MR and U1MR of 2 UART modules onthe Renesas R8C/23 [3] .Configurations for these registers can be done at bit level or at bytelevel.

Figure2: U0MR and U1MR register description

By applying the same concept, we can generate the bit-levelassignment as well as the byte-level assignment to configure theseregisters. Comments for individual assignments can also be generated,as illustrated in Figure 3 below .

Figure3: Code generation for register U0MR configuration

The formula used at F3 is B3 & “_” & $B$1 & “=” & C3 & “;” & $H$3 & D3 & $I$3 . The formula is thencopy for F4 to F9. At F12, the formula entered is $B$1 & “=0x” & BIN2HEX( C10 & C9 & C8 & C7 & C6 & C5 & C4 & C3,2 ) & “;” . To generate similar codefor U1MR register, simply enter “U1MR” into the cell B1.

Figure 4 below shows anotherscenario which is often encountered. Suppose we have only the function send_char() which simply sends outa character through UART. Now, we need to send a string over, e.g.”Hello world.”

Figure4: Code generation using the Excel mid function

In this case, another Excel function can be used, the mid [4] function. The function simply extracts out a substring froma string, given a specified location and the number of characters. Theformula used for D2 is $F$2 & MID ( $A$2,C2,1 ) & $G$2 , and from here it can becopied for cells D3 to D13.

In the following last example, we take a look at the referencesource code of the open-source RTOS u T-Kernel [5] . The code relies on some Perlscripts (Perl is well-known regularexpression handling ) which will read in a template code as shownin Listing 1 below.

Listing1: Code template used in u T-Kernel

The Perl scripts are based on the template code and replace those ${func} and TFN_${func} with correspondingfunction names and macro names. There are well 99 functions required tobe generated from the template above. By making use of Excel substitute [6] function, we can achieve similarresults, which is especially useful when there is no Perl interpreteravailable. This is illustrated in Figure5 below .

Figure5: Code generation with Excel substitute function

At C4, the formula used is SUBSTITUTE( $B$1 ,“${func}” ,A4 ) . And at D4, the formula usedis SUBSTITUTE ( C4 ,“TFN_${Func}” ,B4 ) . Coping C4 and D4 down willgenerate all the required code.

The use of Excel in this case can help developers generate codeefficiently and speed up the development time. Besides the functionsgiven in the example above, other functions are worth exploring aswell, such as the IFELSE which can be used for generating morecomplicated code. If you do not have Excel, other alternative such asOpen Office (Calc) or the Google Docs Spreadsheet fromGoogle couldoffer similar functionalities.

The Excel template I used for to create the above examples canaccessed by downloading thiszip file or by going sourcecode archive.

Bao Anh Tran Nguyen is a seniorengineer at Renesas Technology Singapore. Heholds a BS in Computer Engineering and is completing his MSC inEmbedded Systems (part-time) at Nanyang Technological University,Singapore. He can be reached at .

[1] Excel bin2hex function,
[2] Excel tip forautomatically numbering rows,
[3] Renesas R8C/23 hardwaremanual
[4] Excel mid function,
[5] u T-Kernel reference source codedownload (subscription required)
[6] Excel substitute function,

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.