This blog post is for all developers of all levels that are looking for ways to improve efficiency and time-saving ideas. It begins by providing some background on me and how my experience with Microsoft Excel has evolved and aided me as a developer. Next, we cover a scenario where Excel can be leveraged to save time. Finally, we go over a step-by-step example using Excel to solve the problem.
Background
As a teenager growing up in the 80s, I was fortunate enough to have access to a computer. One of my favorite applications to use as a kid was Microsoft Excel. With Excel, I was able to create a budget and a paycheck calculator to determine my meager earnings from my fast food job. As my career grew into software development, leveraging all of the tools at my disposal as a solution against repetitive and mundane tasks made me more efficient. Over the years, colleagues have seen solutions I have used and have asked me to share how I came up with and implemented them. In this two-part blog post, I will share the techniques that I have used to generate C#, XML, JSON, and more. I will use data-loading in Microsoft Power Apps and Dynamics as a real-word example; however, we will need to start with the basics.
The Basics
Before going into the data-loading example, I wanted to provide a very simple example. Keep in mind that there may be more effective solutions to this specific example that do not use Excel; however, I am using it to illustrate this simple example. Let’s say you had a data model and a contact model that, for the most part, were the same with the exception of some property names, and you needed to write methods to map them. You know the drill:
var contact = new Contact();
contact.FirstName = datamodel.firstName;
contact.LastName = datamodel.lastName;
contact.PhoneNumber = datamodel.phoneNumber;
contact.CellPhone = datamodel.mobileNumber;
Not a big deal, right? Now let’s say you have a hundred of these to do and each model may possibly have 50+ properties! This would very quickly turn into a time consuming and mundane task; not to mention you would likely make a typo along the way that another developer would be sure to let you know about in the next code review. Let us see how Excel could help in this situation.
In this scenario, the first thing you will need is the row data for the contact and data models. One way would be using the properties. Consider the classes below:
- Create 3 Excel worksheets called Primary, Secondary, and Generator
- Copy/paste the property statements from Contact into Primary worksheet and ContactDataModel into a Secondary worksheet.
- Select Column A in the Primary worksheet
- In Excel, select the Data tab and then Text to Columns
- Choose Delimited, then Next
- Uncheck all boxes and then check the Space checkbox, then Finish
- Your worksheet should look like the following:
- Repeat 3-7 with the Secondary worksheet
- Select cell A1 and then press the = key
- Select the Primary worksheet and then cell D1
- Press the Enter key, you should return to the Generator worksheet and the text “FirstName” should be in cell A1
- Select cell B1 and then press the = key
- Select the Secondary worksheet and then cell D1
- Press the Enter key, you should return to the Generator worksheet and the text “firstName” should be in cell A1
- Drag and select A1:B1. Click the little square in the lower-right corner of your selection and drag it down to row 25 or so. (Note: you would need to keep dragging these cells down is you added more classes.)
You will notice that by dragging the cells down, it incremented the rows in the formula.
Press CTRL+~ to switch back to values. - Select cell C1 and enter the following formula:
=IF(A1=0,””,A1 & “=” &B1&”;”)
As a developer, you probably already understand this, but the if statement is checking to see if A1 has a value of 0 and simply returns an empty string if so. Otherwise, string concatenation is built. - Similar to an earlier step, select cell C1 and drag the formula down to row 25. Your worksheet should look like:
- You can now copy/paste the values in column C into the code:
As you continue on, Excel keeps track of the most recent Text to Columns settings used; so, if you pasted another set into the Primary and Secondary worksheets, you should be able to skip steps 1-5 for remaining classes. In the sample class file and workbook, I have included Address models as an illustration.
Next Steps
This example has covered the basic concepts of code generation with Microsoft Excel: extracting your data and writing the formulas that generate the necessary code. Depending on what you are trying to accomplish, these requirements may grow in complexity. Be sure to consider the time investment and payoff of using code generation and use where it makes sense. One such investment that has paid off for me is data loading in Microsoft Power Apps which we will cover in the next post: Code Generation with Microsoft Excel: A data-loading exercise in Microsoft Power Apps.
