Playing with Excel Files in Ruby Using Roo and Write_XLSX

Because of a small tool pitch I gave to our technical writer, I had a chance to play with Excel files through code for several hours last week. I have not done that before, but I felt that it was a pretty easy enough challenge as I have worked with CSV files in the past and thought that the experience would be something similar. I knew I just have to find out what existing libraries I could take advantage in order to build what I thought the program should be capable of doing, which is basically reading two Excel files, comparing them, and creating a new file with updated data based on both files.

Some googling brought me to the ruby gems Roo and Write_XLSX.

Roo helped with reading Excel files, and we can do this by:

xlsx = Roo::Spreadsheet.open(FILE_PATH)
xlsx = Roo::Excelx.new(xlsx)
spreadsheet = xlsx.sheet(SHEET_NUMBER)

Once we have access to a desired Excel file, we can get whatever data we need from it:

spreadsheet.each(column_data_1: COLUMN_NAME_1) do |info|
    // do something with information
end

After that, it’s just a matter of what information do we want to retrieve and manipulate from desired files. I mostly like to use arrays and hashes from here on.

As for creating new Excel files, we can use write_xlsx:

workbook = WriteXLSX.new(FILE_PATH)
worksheet = workbook.add_worksheet
worksheet.write(ROW_NUMBER, COLUMN_NUMBER, INFORMATION)
workbook.close

These two libraries can actually do more. Roo has an interesting read/write access with Google spreadsheets using roo-google. Write_XLSX on the other hand have formatting features we can leverage for better looking Excel outputs. I didn’t have to use those functionalies for the current test project though so I’ll leave those for another time.

Pitching a Small Tool

Last week I told our technical writer, a music expert, a video games enthusiast, a good father to his young daughter, and a loving husband to his wife, that I could build him a small tool that could quickly update a master file of local translations, so he could focus his precious time on more important matters other than the job of laboriously cross-referencing and manually updating such a document. That was a passing comment while some of us were suddenly randomly discussing the process of how he was currently maintaining the file. However, the passing comment also felt like a pitch to a software development project that had some sort of value to a very specific customer, and an easy one at that. It was a fascinating experience; it makes you think about how caring for the well-being of someone is very much a part of building software of value, and that good software connects people for the better.

Let’s see how that tool works out this week.

Being Reminded of All the Phases I’ve So Far Had in Writing Automated Checks

I’m currently in the midst of a test code overhaul, a re-writing project of sorts. It started about a week ago and so far I’ve made considerable progress on what I’ve wanted to achieve with the rewrite, which is basically cleaner and more maintainable code, mostly in the sense of test data management and test description language. The number of tests running everyday in our Jenkins system has grown noticeably and I’ve felt that it’s been difficult to add certain tests because of how I structured the test data in the past, which I have not upgraded since then. The two possible avenues for running tests – on the UI and HTTP layers – also adds a bit of complexity and it’d be nice if I can integrate the two smoothly. It’s an interesting development because I did not plan on any re-writing to be done anytime soon but I guess at the back of my mind I knew it’ll happen eventually. And so I decided to take a step back from writing more tests and do some cleanup before it gets tougher to change things. I plan to finish everything in about a month or so.

At the moment, I’m reminded of the phases I’ve gone through in learning to code and writing automated checks in the past few years:

  • Early 2014. It all begins with Selenium IDE, with giving the self some time to study the basic Selenese commands for writing automated checks and (more importantly) understand how to properly retrieve the page elements you want to manipulate.
  • Mid 2014. Test management in Selenium IDE becomes difficult as the number of tests grow, hence the decision to switch to Selenium WebDriver. The only programming language background I had back then was C++, which was limited to only functions and logical/conditional operators, so I chose Java to work with to lessen the learning curve.
  • Late 2014. Familiarized myself with Git, which hooked me on making daily commits and appreciating version control. Along the way I learned the concepts of classes and objects.
  • All of 2015 up to Early 2016. I was in a trance, writing code daily and pushing myself to create all the automated checks that I wanted to run for our apps before every release. Tests run on the Eclipse IDE using TestNG and I was happy with what I had, except that those end-to-end tests are really slow. Running everything took overnight to finish, which was okay for my employer but annoying for me personally.
  • Mid 2016. Re-writing existing tests in Ruby with Cucumber integration started off (when I found Jeff Morgan’s “Cucumber & Cheese” book online) as a side project for fun and testing my skill level in programming. And I did have buckets of fun! The experiment told me that there’s still a lot I need to practice on if I want to write better code, and it also told me that I can be more productive if I switch programming languages. There’s a bit less code to type in when writing code in Ruby than Java and I liked that, plus all the interesting libraries I can use. I switched to Sublime Text and used both Jenkins and the command-line interface more extensively too.
  • Late 2016. As I was looking for ways to speed up end-to-end tests total execution, which by then takes about 4 hours to complete, I ended up exploring testing apps in the HTTP layer instead of in the UI. That took a lot of studying of how our apps actually behave under the hood, what data are being passed around, how images are actually sent, how to view pages without a browser, how redirections work, among other things. After years of testing apps via the user interface, this was such a refreshing and valuable period, and I completely wondered why I never knew such a thing existed until then. It wasn’t being taught extensively to testers, perhaps because it all depends on how the app was structured to run through an API.

And these phases brings me to now, where there’s a healthy dose of API and UI layer tests all checking major app features. It’s all good, just several pieces needing a cleanup, a little parallelization, better test description language, and great documentation. It’s all good, because the lessons in both programming and testing keep piling. The two practices differ in mindset but I think they complement each other, and I think that there’s no reason anyone can’t do both.

Lessons from Richard Bach’s “Illusions: The Adventures of a Reluctant Messiah”

Richard Bach’sIllusions: The Adventures of a Reluctant Messiah” is a most treasured book from all the books I have read so far. It is a short book, something that can definitely be digested for an hour or a few, but it doesn’t fall short of inspiring and thought-provoking passages, and questions. I have always kept a copy close because it’s what I often choose to rummage through whenever I feel totally sluggish or disheartened, never failing to provide a good pick-me-up or occasionally giving a disapproving eye when needed.

Here are some favorite lessons from the book, which I remind myself every now and again:

  • Learning is finding out what you already know. Doing is demonstrating that you know it. Teaching is reminding others that they know just as well as you. You are all learners, doers, teachers.
  • The simplest questions are the most profound. Where were you born? Where is your home? Where are you going? What are you doing? Think about these once in a while, and watch your answers change.
  • You are led through your lifetime by the inner learning creature, the playful spiritual being that is your real self. Don’t turn away from possible futures before you’re certain you don’t have anything to learn from them. You’re always free to change your mind and choose a different future, or a different past.
  • There is no such thing as a problem without a gift for you in its hands. You seek problems because you need their gifts.
  • The bond that links your true family is not one of blood, but of respect and joy in each other’s life. Rarely do members of one family grow up under the same roof.
  • If you learn what this world is, how it works, you automatically start getting miracles, what will be called miracles. But of course nothing is miraculous. Learn what the magician knows and it’s not magic anymore.
  • Isn’t it strange how much we know if only we ask ourselves instead of somebody else?
  • If you will practice being fictional for a while, you will understand that fictional characters are sometimes more real than people with bodies and heartbeats.
  • Like attracts like. Just be who you are, calm and clear and bright. Automatically, as we shine who we are, asking ourselves every minute is this what I really want to do, doing it only when we answer yes, automatically that turns away those who have nothing to learn from who we are and attracts those who do, and from whom we have to learn, as well.
  • The world is your exercise-book, the pages on which you do your sums. It is not reality, although you can express reality there if you wish. You are also free to write nonsense, or lies, or to tear the pages.
  • Within each of us lies the power of our consent to health and to sickness, to riches and to poverty, to freedom and to slavery. It is we who control these, and not another.
  • There is no good and there is no evil, outside of what makes as happy and what makes us unhappy.
  • If you want freedom and joy so much, can’t you see it’s not anywhere outside of you? Say you have it, and you have it! Act as if it’s yours, and it is!
  • We are game-playing, fun-having creatures, we are the otters of the universe. We cannot die, we cannot hurt ourselves any more than illusions on the screen can be hurt. But we can believe we’re hurt, in whatever agonizing detail we want. We can believe we’re victims, killed and killing, shuddered around by good luck and bad luck.
  • Why are we here? For fun and learning. It’s the same reason why people see films, for fun or for learning or for both together.

Fun fact: One of Richard Bach’s sons is James Marcus Bach, a software tester.

It’s About Communication

One big motivation for learning programming and testing applications technically is understanding what feelings and experiences do programmers face in their day-to-day work. Why do many of  them have a strong dislike towards bug fixing and other distractions? What sort of things go through their heads when they talk about databases and algorithms? Why do they say that a task is difficult when the changes needed from a user’s point of view are only minimal? Is working with legacy code really that terrible? How do programmers test their code? These are some of the questions that’s tough to answer, so I sought to learn how to write code myself. I wanted to put myself in their shoes and discover how I would feel in situations they often go through in building and maintaining applications, sort of. I thought that if I knew how they generally feel during such circumstances and knew how to code, it would greatly help improve my communication with them in the long run.

Based on my experience so far, it does.

Evolving

To me, part of the appeal of working in the software testing field is due to the nature of software systems. Apps change. Multiple times a year, some of their requirements are likely to get overhauled. Other programs are being built from scratch. Needs change. Technologies change. Business processes and flow change. As such, digital apps keep changing too and are evolving alongside people’s wishes. That’ what makes software testing work rarely boring and often challenging: because we get to have multiple chances at exploring systems, seeing what they’re made of and thinking about how they help us do the things we need to do. We are trained to be curious. We always look for patterns or their lack of. We keep a good eye out for details and we’re usually asking ourselves why a particular feature needs to perform a certain way. We have a hand in making things better.

And it gets better over time, when we start to realize that testing doesn’t have to be limited to client work. It extends to testing our own life systems, our beliefs, and the places where we want to go.

When Do We Learn The Most?

People learn the most when they themselves are the ones looking for the answers to their questions, when such problems can’t wait to be solved, when they’re excited to see how the solutions work, right now. That’s because it is when we are most curious where we wholeheartedly give whatever it is our all, our time and attention fully spent, excited in our hearts and with sparkles in our eyes, and which, in turn, where the universe rewards us with something equivalent in exchange – the finding of knowledge and the satisfaction of seeing miracles first-hand.