Black Lives Matter

8 Tips for using Excel as a drawing tool

Over the past several months, I have been playing a factory-building game called Satisfactory. The game involves a lot of number crunching for calculating the input and output rates of the machines. You also have to think about how to optimally arrange the machines and conveyor belts in physical space on the factory floor. Because of this, I've found it to be much easier to design my factories outside of the game so that my in-game time can be spent actually building them!

I had decided to use Microsoft Excel to create these factory designs since I was already familiar with Microsoft Office's shape drawing and image editing tools. For the past ten months, I've been sharing many of my designs with the community via Reddit and Google Drive (I've built up a bit of a reputation for being the "floor plan guy"). I've become quite adept at using Excel for this purpose, so I thought I would share some of the things I've learned.

1. Snap to Grid

The "Snap to Grid" setting is crucial for helping to keep your shapes aligned with the worksheet grid lines.

To toggle it, go to: [ Page Layout tab > Arrange group > Align button > Snap to Grid]

2. Select Objects

The Select Objects tool allows you to select multiple objects by clicking and dragging to draw a selection box. Any object that is fully inside of the selection box will be selected. Objects that are only partially inside the box will not be selected. While this tool is activated, only objects can be selected--the cells inside of the actual spreadsheet cannot be selected.

To toggle it, go to: [ Home tab > Editing group > Find and Select button > Select Objects ]

3. Quick Access Toolbar

Located in the upper-left corner of the window, the Quick Access Toolbar serves as a holding place for your personal collection of favorite commands. Any button that's on the ribbon can be added to this toolbar. I've added buttons for "Snap to Grid" and "Select Objects" because I use these commands so much.

To customize the Quick Access Toolbar, click on the arrow on the right side of the toolbar and click "More Commands".

4. Grouping

Sometimes, you need to create multiple objects to achieve a specific goal. For example, to create the "clipboard" graphic pictured below, I needed:

• An image for the "board" part of the clipboard
• An image for the "clip" part of the clipboard
• An image for the pencil
• A textbox for the paper

To help manage complex collections of objects like this, you can group them together. Grouping allows multiple objects to be treated as a single object, allowing you to move and resize them as a whole. Even though they are grouped, you can still manipulate each individual "sub" object by clicking on them to select them.

To group a collection of objects, select them all by holding down the Shift key and clicking on each object (or by using the Select Objects tool, described above). Then, go to: [ Page Layout tab > Arrange group > Group button > Group ]. They can be ungrouped by selecting "Ungroup" from the same menu.

5. Nudging objects with the arrow keys

It can be difficult to precisely position an object using just the mouse. The arrow keys on the keyboard can be used to "nudge" an object in a particular direction. Tapping an arrow key will move the object one pixel at a time. Or, if Snap to Grid is enabled, the object will move one column/row at a time.

6. Zoom level issues

My experience has been that the sizes and positions of objects change slightly when you adjust the zoom level. To keep everything precise, I find it helpful to only move and resize objects at a specific zoom level. The zoom level that I work at is 100%.

It's also useful to note that the way Excel renders text can change when you adjust the zoom level. For example, you may have a textbox that wraps in a certain way at one zoom level, and then wraps differently at another zoom level. For example, the images below show the same textbox at two different zoom levels. Notice how the word wrapping changes starting with line 3.

To adjust the zoom level, use the slider in the bottom-right corner of the window or roll the mouse scroll wheel while holding Ctrl.

7. Paste as image

When an object, or group of objects, is copied to the clipboard, Excel will automatically generate a paste-able image when you paste into any image editing program, such as Photoshop or Paint. It even takes transparency into account! For example, if you copy a textbox that has a drop shadow, the drop shadow will render as semi-transparent pixels. This is useful for generating images out of the objects you've created in Excel (as I have done with my factory floor plans).

8. Image quality settings

By default, when you save an Excel file, Excel compresses all images to reduce the size of the file. For example, if you've inserted an image that's 800 pixels wide, and then resized it to be 400 pixels wide, Excel will down-sample the image to 400 pixels. The downside to this is that, if you later decide to make the image larger, it will look distorted because you've lost the original, 800 pixel version of the image.

To force Excel to preserve the original quality of all images, follow the steps below. These settings are applied only to the spreadsheet file you currently have open (they are not global). And if your spreadsheet already has images in it, you'll have to re-insert them to restore their original sizes.

1. Go to [ File > Options > Advanced ].
2. Scroll down to the "Image Size and Quality" section.
3. Click the "Do not compress images in file" checkbox.
4. Change "Default resolution" to "High fidelity".

Conclusion

I hope that my list of tips help you to "excel" at Excel! Happy spreadsheeting!

Fixing Swing's JOptionPane class

I maintain a handful of client-side Java programs that use Java's older GUI API called Swing. One thing that I have found awkward about Swing is the way dialog boxes are handled.

Swing provides a class called JOptionPane for creating simple dialog boxes. It alleviates the need to manually code your own, which saves a lot of time. All you have to do it make a single method call and your dialog appears.

While the class works just fine, some of its methods have up to eight parameters. When a method has more than three or four parameters, it makes it hard for someone who is not already familiar with the API to tell what the code does at a glance.

For example, the code below has a total of eight parameters. Unless you are already familiar with this method's signature, you probably don't have a clue as to what half of these parameters do.

int choice = JOptionPane.showOptionDialog(
    this,
    "The password you have chosen may not be secure.\n\n" +
      "Please keep the following guidelines in mind:\n\n" +
      "1. Eight or more characters long.\n" +
      "2. Is not a word from the dictionary." +
      "3. Completely different from all your other passwords.\n\n" +
      "Would you like to create a better password or continue?",
    "Security warning",
    JOptionPane.YES_NO_OPTION,
    JOptionPane.WARNING_MESSAGE,
    null,
    new Object[]{"Pick a new password", "Continue"},
    "Pick a new password"
);

One thing you could do to make the code more readable is assign each parameter to a variable with a descriptive name. This allows a programmer who is not familiar with the method to see at a glance what each parameter is for.

Component parentWindow = this;
String text = 
    "The password you have chosen may not be secure.\n\n" +
    "Please keep the following guidelines in mind:\n\n" +
    "1. Eight or more characters long.\n" +
    "2. Is not a word from the dictionary." +
    "3. Completely different from all your other passwords.\n\n" +
    "Would you like to create a better password or continue?";
String title = "Security warning";
int buttons = JOptionPane.YES_NO_OPTION;
int messageType = JOptionPane.WARNING_MESSAGE
Icon icon = null; 
Object[] buttonLabels = new Object[]{"Pick a new password", "Continue"}
Object defaultButton = "Pick a new password";

int choice = JOptionPane.showOptionDialog(parentWindow, text, title, buttons, messageType, icon, buttonLabels, defaultButton);

However, there are still a some problems with this:

1. Variable name conflicts: The variable names could be confused with other variable names in the same scope. For instance, the name "text" is fairly generic, so it's not unlikely that another variable a dozen or so lines down could have the same name.
2. Parameters are ordered: It is very easy for the programmer to get the parameter order wrong without the compiler noticing. For example, if the "buttons" and "messageType" variables were swapped, the compiler wouldn't notice. The programmer likely wouldn't notice either because they are next to each other in the method signature.
3. Null parameters: One of the parameters is null. The code would be more concise if we could just leave it out, but the JOptionPane class does not provide an appropriate method signature for that.
4. Misleading code formatting (1): The way the message text string is formatted in the code makes it look like each line will end with a single line break. However, some lines end in two line breaks.
5. Misleading code formatting (2): Each line of the message appears on its own line in the code. This makes it much easier to read! However, you may not have noticed that the fourth line is missing a line break. Because of the way the code is formatted, an error like this can easily be overlooked.
6. Neglecting native system settings: The code may not be using the native system's preferred line break sequence. Appending "System.lineSeparator()" (or even a concisely-named "newline" variable) onto each line would be technically correct, but it would make the code less consice.
7. Duplicate data: One of the button labels is duplicated.
8. Ambiguous types: Defining button labels as Objects will lead to problems only detectable at runtime if an Object that JOptionPane does not accept is passed into the method (also, it's just weird!).

To resolve these issues, I designed a class that acts as a fluent wrapper around JOptionPane. Using this wrapper class, the above code would look like this:

int choice = DialogBuilder.warning()
    .parent(this)
    .title("Security warning")
    .text(
        "The password you have chosen may not be secure.",
        "",
        "Please keep the following guidelines in mind:",
        "",
        "1. Eight or more characters long.",
        "2. Is not a word from the dictionary.",
        "3. Completely different from all your other passwords.",
        "", 
        "Would you like to create a better password or continue?")
    .buttons(JOptionPane.YES_NO_OPTION, "*Pick a new password", "Continue")
.show();

The above problems are addressed as follows:

1. Variable name conflicts: No variables are needed because the method name provides context. 
   
2. Parameters are ordered: Each parameter is replaced by a method call. Methods can be called in any order.
3. Null parameters: The class internally provides a default value for every optional parameter. The method call for this null parameter can be left out, making the code more concise.
4. Misleading code formatting (1): The "text()" method takes a single vararg parameter, where each parameter is a line of text. If a line ends in two line breaks, an empty string is provided, making it clearer that an empty line will appear on the dialog box.
5. Misleading code formatting (2): Line breaks are automatically added by the "text()" method.
6. Neglecting native system settings: The "text()" method uses the native system's preferred line break sequence.
7. Duplicate data: The default button is identified by an asterisk character, which is removed when the button label is passed into the JOptionPane class. This is not as obvious as I would like it to be, as it requires detailed reading of the Javadocs, but it is very concise.
8. Ambiguous types: The button labels are defined as Strings in the "buttons()" method, not Objects. This means the compiler can prevent invalid objects from being passed in. 

The full class can be viewed here: https://github.com/mangstadt/emc-shopkeeper/blob/master/src/main/java/emcshop/gui/DialogBuilder.java

Renewing my CompTIA A+ certification

CompTIA is an organization that administers certification exams for IT professionals. I took and passed their entry-level exam, CompTIA A+, three years ago. The exam tests your knowledge of broad IT-related topics, such as internal hardware, cabling, troubleshooting, operating systems, and much much (MUCH) more.

I wrote a handful of blog posts about some of these topics while I was studying for the exam if you want to get a feel for what they test you on. You can also get the exam objects from their website (the form asks your for your email, but you don't have to give a real one).

Having to pay over $400 to take a test (actually, it's two separate tests) that I might fail was stressful to me, so I'm glad that I passed it! The exam expires after three years, so since I took it three years ago, I needed to renew it. There are several ways they allow you to renew your certification. I chose to enroll in their CertMaster CE training course, which renews your certification instantly upon completion. It's all done online and costs $129.

The way it works is you read through the content it gives you, which is organized based on the exam objectives of the exam you are renewing. At the end of each section, it gives you a 20-30 question multiple choice quiz. When you complete the quiz, it shows you which questions you got wrong, but doesn't tell you the right answer. You can "reset the assessment", which resets the quiz and lets you take it again.

You can give it a date for when you want to complete the training by, and it will recommend to you how much content ("knowledge points") you have to study each day in order to finish by that date. As you progress through each section, you earn knowledge points. It tells you how many knowledge points are in each section so you can get a feel for how long a section is.

It also has various "achievements" that you can earn to help motivate you. For example, there's one for getting 100% on a quiz and one for getting twenty consecutive questions correct. My left-brain found some of them to be a little patronizing (for example, resetting a quiz gives you an achievement), but it was nice to have that extra bit of encouragement.

I found the user interface of this web app to be very clean and intuitive. It is also very responsive, which leads me to believe that it's heavily JavaScript-based and doing a lot of caching in the background. For example, going back and forth between the pages in a lesson is instantaneous, as is flipping through the questions in a quiz.

I also loved the graphics they provided for showing what the different cable connectors look like (VGA, DisplayPort, USB, etc). They all have a consistent aesthetic to them, which made the learning process that much easier. I would post one here as an example, but I don't want to violate any copyrights.

In order to renew your certification, you have to get a 100% on each quiz. Once you do that, your certification is automatically renewed. You literally don't have to do anything else. I got an email no more than a day or two after I completed all the quizzes saying that my certification was renewed. My new expiration date was based upon my current expiration date, not the date I completed the CertMaster training. I appreciate that because I completed the training several months early!

Workaround implemented for file timestamp issue

A few months ago, I wrote a blog post about a problem related to the date modified timestamp on a database file not updating immediately after the file was modified. This was causing a problem with an application I wrote that relied on this timestamp to update the data it displayed on the screen to the end user. It would take around ten seconds for the timestamp to update, which meant that the user had to wait that amount of time before the most recent information was displayed to them.

While I did discover a clue as to why this was happening to some files and not others, I wasn't able to fix the core issue itself--the delayed updating of the date modified timestamp. However, I implemented a workaround that not only solved the problem of the end user getting delayed updates, but also improved the integrity of the file (an Access database file).

The workaround involved creating a lock file whenever a user writes to the database. When the user is done writing to the database, the application deletes the lock file. The application monitors for this file deletion event and refreshes itself whenever a deletion is detected. There are no delays in the file system reporting when a file is deleted, which means the user's screen updates instantaneously whenever the database is updated.

I also use the lock file to improve the integrity of the database file. If the user tries to write to the database, but the application detects the presence of the lock file, it means another user is currently writing to the database. If this happens, the application waits until the lock file is deleted before creating its own lock file and executing its own writes. This prevents multiple users from writing to the database simultaneously and potentially corrupting the database.

There is a second kind of lock file which the application also makes use of. If the file is opened in Microsoft Access, Access creates its own lock file. My application monitors for the presence of this lock file. If the user tries to write to the database while the Access lock file exists, the application cancels the write operation and displays an error message. The error message informs the user that the database is open in Access and that it cannot write to the database while Access is open. This also helps to prevent file corruption.

Similarly to the lock file that the application creates when the user writes to the database, the application uses the Access lock file to detect changes to the database. When the Access lock file is deleted, it not only means that Access has been closed, but that changes could have been made to the database while it was open. So, when this lock file is deleted, the application refreshes itself and updates the on-screen information for the end user.

This workaround has been in production for 1-2 months and has been working quite well.

PHP, XML, and character encodings

The library where I work subscribes to an online service that keeps track of the library's ongoing public events. We link to it from our website so that patrons can discover the various programs the library has to offer.

I wrote a WordPress plugin that posts a listing of these events on the library's events page using the RSS feed the service provides. I just noticed today that a few of the event titles had empty, square boxes in them. When you see this, it usually means there is a character encoding problem, which means that it does not recognize a particular letter or symbol.

To make page loads more performant, my plugin caches the RSS file it downloads so that it does not have to query the event service every time someone loads the events page on the website. I opened the cached RSS file to see what might be causing the problem. The event in question had curly quotes (also called smart quotes) in its title. My experience has been that curly quotes frequently cause problems when people try to use them on websites, so I wasn't surprised to see this.

(table from computerhope.org)

You can't type a curly quote on the keyboard. At least, not directly. In my experience, they usually appear when someone copies and pastes something from Microsoft Word because Word will automatically insert them into your document as you're typing to make your document look more aesthetically pleasing.

It turned out that the problem was with the RSS data's character encoding. An RSS file is just XML, and every XML file has an "encoding" attribute at the top.

<?xml version="1.0" encoding="UTF-8" ?>

This tells the program that parses the XML what kind of character set the XML data uses so that all of its content will remain intact after being parsed. If this attribute does not reflect the character set that was actually used to create the XML file, the data may not be parsed correctly, and you may end up with "empty boxes" like the ones I was getting.

When I changed this encoding attribute to "UTF-8" (a widely used character encoding that supports many different languages), the empty boxes went away, and the curly quotes correctly appeared.

To prevent this from happening in the future, I modified my WordPress plugin to change the RSS feed's character encoding right before it is saved to the cache. Doing a simple str_replace function call seemed to do the trick. I thought I might have to use the mb_convert_encoding function to do a thorough conversion of the entire file, but this did not appear to be necessary.

Date modified timestamp problems on a Linux SMB share

I discovered an annoying issue recently related to our switch from a Windows-based file server to a Linux-based one.

The computer lab in the library where I work uses an Access database to record registration information of students who sign up for the adult computer classes that the lab runs. Several years ago, I wrote a Java Swing application that sits on top of this database to make the registration process faster and easier. I've gotten many complements about it, and it's been working well over the years with few hiccups.

The database file is hosted on a dedicated server that I administer, which the lab uses for file storage. This means that multiple people could be accessing the database on different computers at the same time. The app must consider the possibility of another user on another machine updating the database while the app is open. To do this, I designed the app to monitor the database file for changes. When a change occurs, it reloads the information from the database so that the user always sees the most recent information on the screen.

About a year ago, I started researching a replacement for our file server. It was over 5 years old and out of warranty, and its version of Windows (Windows Server 2008) was fast approaching end-of-life. Because we mainly used it for file storage, I recommended we get a Linux-based NAS device because they are cheaper than a full-blown Windows server.

The two major competitors in the NAS space are QNAP and Synology. I decided to go with QNAP because they offered a model that came with more RAM and an HDMI port (most NAS devices do not come with any video ports). My reasoning was that the HDMI port would allow me to administer the device just like a normal server if the network ever went down.

The library purchased it a few months ago, and I deployed it last week. It has been working well so far, but one hiccup I've encountered is that, ever since I deployed it, the class registration app I wrote has been slow to detect updates to the database file. Like, really slow. About 10 seconds slow.

I ran some tests and found that when my app updated the database, the "date modified" timestamp of the database file was slow to update. This was why my app was taking so long to detect any changes to it. But when I wrote a test program that just saved some content to a text file on the file server, the text file's timestamp would update immediately. Why, then, was the Access file behaving differently?

I thought that maybe the open-source library my app uses to interface with the Access database, called "Jackcess", was doing something different under the hood. I started a thread on their support forum and learned that they use Java's "RandomAccessFile" class to write to the database. This class allows you to update small sections of a file individually instead of re-writing an entire file from scratch, which is how most computer programs deal with updating files.

So, it looks like the culprit here is the new Linux-based NAS server. It seems to handle these kind of file updates differently than the old Windows server did. The simplest workaround I can think of would be to create some kind of dummy file and monitor that for changes instead of the database. The app would then update the dummy file whenever it makes a change to the database, which would cause the dummy file's "date modified" timestamp to update, signaling to other computers on the network that the database was just changed. That should hopefully get things back to normal!