thanos1983:

I don't really see anything major, but I have some minor comments.

First, in _writeIntoCell you're creating a new format for each cell entry, rather than reusing them. When I generate spreadsheets, I generally build a few handy styles once when I build the excel object, then reuse them as needed, something like:

my $workbook = Excel::Writer::XLSX->new( $ARGV[2] );
 
# Normal cell
my $fmtNRM = $workbook->add_format({
    -font   => 'Arial',
    -size   => 8,
    -border => 7,
    -align  => 'center',
});

# Row/Column header
my $fmtHDR = $workbook->add_format({
    -font   => 'Calibri',
    -size   => 11,
    -bg_color => '#09c8eb',
    -bold   => 1,
    -border => 7,
    -align  => 'center',
});

[download]

It may turn out that the module recognizes duplicate styles and doesn't bloat the spreadsheet, but it still feels better to me to create a format once and only once.

Some of your variable names could use improvement. For example, in _getDataFromSpreadsheet, you're building %HoH and %hash. I can tell from the usage that these are in fact a hash of hashes and a hash, but the name doesn't really indicate anything useful. It looks like %WANinterfaces would be a good name for HoH, but without digging into your data and studying your program a little more intently, I can't really tell what I would use for hash.

Finally, I wouldn't worry about optimizing a program unless it's either proven to be a problem, or the general trend indicates that it will become a problem in the immediate future. So if your data files are continuously increasing in size, then write a quickie perl script to generate some bogus input spreadsheets of varying sizes to see how it performs as the data volume increases. Then run it while watching the time and memory consumption. That way, you'll be able to make an educated guess as to what the performance should be for the future. You'll likely find that it can handle quite a lot of volume before the time becomes worth worrying about.

It's surprising to me how fast computers are nowadays, so I rarely find it worthwhile to optimize code except when doing some extremely CPU-intensive work.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

In reply to Re: Reading and Writing from / to XLSX file (optimization) by roboticus
in thread Reading and Writing from / to XLSX file (optimization) by thanos1983

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.