Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

Perhaps the following will work for you:

use Modern::Perl; use Excel::Writer::XLSX; my ( $rowNumber, %headingsSeen ) = 1; my $workbook = Excel::Writer::XLSX->new('data.xlsx'); my $worksheet = $workbook->add_worksheet(); while (<DATA>) { next if /^[-#+]/ or ( /S.No/ and $headingsSeen{/S.No/}++ ); $worksheet->write( 'A' . $rowNumber++ , [ grep s/\A\s*(.*?)\s*\z/$1/, ( split /\|/ )[ 0 .. 11 ] ] ); } __DATA__ ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| S.No | TimeStamp| UE-Name| Test-Config | UL/DL | Status +| Jitter ms | Throughput Mb/s | lost % | Bandwidth Mb/ +s | Duration Sec | Fail-Reason | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 1 | 18:24:38 | -NA- | Sanity_001_0001_10_00 | System | Passed +| -NA- | -NA- | -NA- | -NA- + | -NA- | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 2 | 18:31:34 | ueh1 | Sanity_002_0002_00_11 | UL | Passed +| 0.14 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | DL | Passed +| 3.37 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 4 | 18:38:30 | ueh1 | Sanity_004_0004_24_00 | DL | Passed +| 1.34 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 5 | -NA- | -NA- | Sanity_001_0001_10_00 | System | -NA- +| -NA- | -NA- | -NA- | -NA- + | -NA- | Micro-Step | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 6 | 18:31:34 | ueh2 | Sanity_002_0002_00_11 | UL | Passed +| 0.15 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:49 | ueh2 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:48 | ueh2 | Sanity_003_0003_06_09 | DL | Passed +| 3.38 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 8 | 18:38:30 | ueh2 | Sanity_004_0004_24_00 | DL | Passed +| 1.32 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| # Total Test-Cases = '4' # Total Passed-Cases = '4' # Total Failed-Cases = '0' ++++++ SUMMARY ++++++ ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| S.No | TimeStamp| UE-Name| Test-Config | UL/DL | Status +| Jitter ms | Throughput Mb/s | lost % | Bandwidth Mb/ +s | Duration Sec | Fail-Reason | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 1 | 18:24:38 | -NA- | Sanity_001_0001_10_00 | System | Passed +| -NA- | -NA- | -NA- | -NA- + | -NA- | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 2 | 18:31:34 | ueh1 | Sanity_002_0002_00_11 | UL | Passed +| 0.14 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 3 | 18:23:48 | ueh1 | Sanity_003_0003_06_09 | DL | Passed +| 3.37 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 4 | 18:38:30 | ueh1 | Sanity_004_0004_24_00 | DL | Passed +| 1.34 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 5 | -NA- | -NA- | Sanity_001_0001_10_00 | System | -NA- +| -NA- | -NA- | -NA- | -NA- + | -NA- | Micro-Step | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 6 | 18:31:34 | ueh2 | Sanity_002_0002_00_11 | UL | Passed +| 0.15 of 10.00 | 4.38 of 3.97 | 0.00 of 10.00 | 4.38 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:49 | ueh2 | Sanity_003_0003_06_09 | UL | Passed +| 0.16 of 10.00 | 4.00 of 3.62 | 0.00 of 10.00 | 4.00 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 7 | 18:23:48 | ueh2 | Sanity_003_0003_06_09 | DL | Passed +| 3.38 of 10.00 | 5.18 of 4.67 | 0.00 of 10.00 | 5.17 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| 8 | 18:38:30 | ueh2 | Sanity_004_0004_24_00 | DL | Passed +| 1.32 of 10.00 | 13.72 of 12.40 | 0.00 of 10.00 | 13.70 + | 360 | -NA- | ------|----------|--------|-------------------------|--------|-------- +|-----------------|-----------------|-----------------|-------------- +---|---------------|-------------| # Total Test-Cases = '4' # Total Passed-Cases = '4' # Total Failed-Cases = '0'

Output (copied from generated data.xlsx spreadsheet):

S.No TimeStamp UE-Name Test-Config UL/DL Status Jitt +er ms Throughput Mb/s lost % Bandwidth Mb/s Duration Sec + Fail-Reason 1 18:24:38 -NA- Sanity_001_0001_10_00 System Passed +-NA- -NA- -NA- -NA- -NA- -NA- 2 18:31:34 ueh1 Sanity_002_0002_00_11 UL Passed 0.14 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 DL Passed 3.37 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 4 18:38:30 ueh1 Sanity_004_0004_24_00 DL Passed 1.34 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 5 -NA- -NA- Sanity_001_0001_10_00 System -NA- -NA- + -NA- -NA- -NA- -NA- Micro-Step 6 18:31:34 ueh2 Sanity_002_0002_00_11 UL Passed 0.15 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 7 18:23:49 ueh2 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 7 18:23:48 ueh2 Sanity_003_0003_06_09 DL Passed 3.38 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 8 18:38:30 ueh2 Sanity_004_0004_24_00 DL Passed 1.32 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 1 18:24:38 -NA- Sanity_001_0001_10_00 System Passed +-NA- -NA- -NA- -NA- -NA- -NA- 2 18:31:34 ueh1 Sanity_002_0002_00_11 UL Passed 0.14 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 3 18:23:48 ueh1 Sanity_003_0003_06_09 DL Passed 3.37 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 4 18:38:30 ueh1 Sanity_004_0004_24_00 DL Passed 1.34 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA- 5 -NA- -NA- Sanity_001_0001_10_00 System -NA- -NA- + -NA- -NA- -NA- -NA- Micro-Step 6 18:31:34 ueh2 Sanity_002_0002_00_11 UL Passed 0.15 + of 10.00 4.38 of 3.97 0.00 of 10.00 4.38 360 -NA- 7 18:23:49 ueh2 Sanity_003_0003_06_09 UL Passed 0.16 + of 10.00 4.00 of 3.62 0.00 of 10.00 4 360 -NA- 7 18:23:48 ueh2 Sanity_003_0003_06_09 DL Passed 3.38 + of 10.00 5.18 of 4.67 0.00 of 10.00 5.17 360 -NA- 8 18:38:30 ueh2 Sanity_004_0004_24_00 DL Passed 1.32 + of 10.00 13.72 of 12.40 0.00 of 10.00 13.7 360 -NA-

Hope this helps!


In reply to Re: Parsing Data in xlsx file by Kenosis
in thread Parsing Data in xlsx file by Rahul Gupta

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!
  • 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, 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, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (13)
    As of 2014-10-01 12:40 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      What is your favourite meta-syntactic variable name?














      Results (17 votes), past polls