1

I'm working on a project where we are tasked to gather and output various data to a spreadsheet.

We are having tons of problems with the file that holds the code to write the spreadsheet. The cell that the data belongs to is hardcoded, so anytime you need to add anything to the middle of the spreadsheet, you have to increment the location for all the fields after that in the code. There are random blank rows, to add padding between sections, and subsections within the sections, so there's no real pattern that we can replicate. Essentially, anytime we have to add or change anything to the spreadsheet it requires a many long and tedious hours.

The code is all in this one large file, hacked together overtime in Perl. I've come up with a few OO solutions, but I'm not too familiar with OO programming in Perl and all my attempts at it haven't been great, so I've shied away from it so far. I've suggested we handle this section of the program with a more OO friendly language, but we can't apparently. I've also suggested that we scrap the entire spreadsheet idea, and just move to a webpage, but we can't do that either.

We've been working on this project for a few months, and every time we have to change that file, we all dread it. I'm thinking it's time to start some refactoring. However, I don't even know what could make this file easier to work with. The way the output is formatted makes it so that it has to be somewhat hardcoded.

I'm wondering if anyone has insight on any design patterns or techniques they have used to tackle a similar problem. I'm open to any ideas. Perl specific answers are welcome, but I am also interested in language-agnostic solutions.

  • Is your spreadsheet just data? or are you writing formulas into the spreadsheet? – user40980 May 27 '14 at 14:48
  • @MichaelT No formulas – Austin Moore May 27 '14 at 14:55
  • 1
    Then its 'easy' and you've hinted at it. You've got a Model and a View. (note: if you come into Software Engineering Chat, it would be possible to work through this a bit more interactively) – user40980 May 27 '14 at 15:03
1

This problem is not so bad because the bad code only produces a single output.

The cell that the data belongs to is hardcoded, so anytime you need to add anything to the middle of the spreadsheet, you have to increment the location for all the fields after that in the code.

Ah.. the root of the problem. This is a huge WTF. The current code was written by a novice and is unmaintainable. You have to eliminate the hard-coding and use some variables to keep track of where each datum goes. If this were my problem, I would just rewrite the entire function from scratch using the existing code as a reference. My first thought is to just fill the spreadsheet row by row keeping track of the current row.

1

Spreadsheet::WriteExcel writes XLS files, which I use. Spreadsheet::WriteXLSX writes XLSX files. Spreadsheet::WriteExcel is not being developed anymore but is still a real solid tool. (Some of our customers are way behind the tech curve so XLS files seemed like a good common denominator. And I don't need a Ferrari to get the groceries, nor do we have money for a Ferrari.)

In both of these you can specify which cell to write. In my case, all the spreadsheets I output are output in sequence, line by line, by not addressing cells. My spreadsheets are summary reports for executives and are divided into sections, and there is a variable number of rows with each section.

The nice thing about both Perl modules is you can add formatting to individual cells when you write each cell.

Now, this might be a management problem too. If it takes you 200 hours to keep Perl and do the same thing, and 30 hours for you to use a tool you already know to give the same output, and you told management this, that is a management problem. The manager doesn't understand how efficiency, and saving money, works. In my case, Perl can do a LOT of things our other tools cannot.

IMO, learning Perl OO is worth it as you can use LOTS of handy modules, most modules are written this way.

My answer: rewrite sections of the existing code to use Spreadsheet::WriteExcel. Test the subroutine thoroughly, then put it into production. Then repeat with a new section of code/subroutine. When you get a tool that works, it's really worth it to stick with it. Perl is one of those tools.

0

Depending on the library that you are using, you can name the specific ranges/cells that you output data to, then in your code just reference those names. That way if the design of the template spreadsheet changes, you don't need to change any code.

I know this works with PHPExcel, it may work in Perl also. If not, you could always just write a PHP script that Perl works with.

0

Dreading changes to a particular code base or module is usually a sign that refactoring needs to be seriously considered.

I'm not sure why you feel your output format requires that some hardcoding is required. It has always been my experience that templating (preferably as part of a larger effort to properly separate output format from code) is always possible, and that complications usually arise from bad code structuring and coding practices in the past, which again warrants fixing and/or refactoring.

A common problem with major refactoring jobs is that they often represent a considerable investment of resources that do not immediately translate in improved bottom lines for the client or employer. However, unless the projected future of your legacy code is limited, there is usually a case to be made for future savings on maintenance and expansion of the existing code base. YMMV of course, but in general it does pay to restructure and modularize major chunks of legacy code (even if that requires considerable refactoring) so as to make it future-proof in terms of maintenance and continued development.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.