title: Fetching zero cells in XLS file using Spreadsheet::ParseExcel
body: <p>First refer to Stack Overflow question <em><a href="https://stackoverflow.com/questions/15835021">Using Spreadsheet::WriteExcel</a></em>.</p>
  
  <p>The data structure looks alike the following:</p>
  
  <pre><code>      col1      col2    col3   col4   col5
  row1  School    1
  row2  Dean      John
  row3  No.stu.   55
  row4  some irrelevant stuff
  row5  School2   2
  row6  Dean      Tony
  row7  No. stu.  60
  row8  some irrelevant stuff
  row9  School    3
  row10 Dean      James
  row11 No.stu.   56
  row12 No. teacher 20
  row13 School    4
  row14 Dean      Tom
  row15 No.stu.   79
  row16 No. teacher 21
  row17 course
  row18           math    2
  row19           eng     4
  row20 teacher   name    age   gender   race
  row21           Jane    20    female   white
  row22 student   name    Lee
  row23           SAT     1434
  row24           gender  male
  </code></pre>
  
  <p>The output I would like to achieve is:</p>
  
  <pre><code>      col1  col2  col3     col4          col5         col6          col7        col8       col9
  row1 School Dean No.stu.  No. teacher  course_math  course_eng  teacher_name  teacher_age  teacher_gender    teacher_race    student_name   student_SAT   student_gender
  row2 1      John  55
  row3 2      Tony  60
  row4 3      James 56       20
  row5 4      Tome  79       21              2           4            Jane        20          female                white         Lee         1434          male
  </code></pre>
  
  <p>And thanks to gangabass, the code I have been given is that:</p>
  
  <pre><code>use strict;
  use warnings;
  
  use Spreadsheet::ParseExcel;
  use FindBin qw($Bin);
  
  my ($infile) = @ARGV;
  
  my $parser   = Spreadsheet::ParseExcel-&gt;new();
  my $workbook = $parser-&gt;parse("$Bin/Test.xls");
  die $parser-&gt;error unless defined $workbook;
  my ($worksheet) = $workbook-&gt;worksheets();
  
  my %data;
  my $row    = 0;
  my $school = "";
  while (1) {
      my $cell = $worksheet-&gt;get_cell($row, 0);
      last unless defined($cell);
  
      my $key = $cell-&gt;value();
      my $value = $worksheet-&gt;get_cell($row++, 1)-&gt;value();
  
      if ($key eq "School") {
  
          $school = $value;
          next;
      }
  
      $data{$school}-&gt;{$key} = $value;
  }
  sleep 1;
  </code></pre>
  
  <p>I start from parsing row17-row19. The first problem I came across was the empty cell in (row17, col3). The code errors out when it arrives here. Knowing that an Excel cell differentiates between 'empty' and 'blank', I can play around with it by setting the cell format of the original XLS file to others except 'general'. However, it is just a temporary solution. I wonder if there are any commands that I could use to fetch the empty cell. I already tried <code>unformatted()</code> by adding:</p>
  
  <pre><code>my $unformattedvalue = $worksheet-&gt;get_cell( $row++, 1 )-&gt;unformatted();
  </code></pre>
  
  <p>It, however, does not work out.</p>
  
  <p>I then tried to specify the data structure under 'course' state using the following code:</p>
  
  <pre><code>my %data;
  my $row    = 0;
  my $school = "";
  my $course = ""; #Initial value for the state of course
  while (1) {
      my $cell = $worksheet-&gt;get_cell($row, 0);
      last unless defined($cell);
  
      my $key = $cell-&gt;value();
      my $value  = $worksheet-&gt;get_cell( $row++, 1 )-&gt;value();
      my $value1 = $worksheet-&gt;get_cell( $row++, 2 )-&gt;value(); #Fetching the value in column 3
  
      if ($key eq "School") {
  
          $school = $value;
          next;
      }
  
      if ($key eq "course") { #Just mimicking the how we construct the structure of 'School'
  
           $course = $value1;
           next;
      }
  
      $data{$school}-&gt;{$key} = $value;  #Must be something wrong here, but can not figure out
  }
  </code></pre>
  
  <p>The code does not go through and gives <code>Can't call method "value" on an undefined value at xxx line of 'my $value1 = $worksheet-&gt;get_cell( $row++, 2 )-&gt;value()';</code></p>
  
  <p>So in short, my questions are:</p>
  
  <ol>
  <li>In general, how do I fetch empty cells in XLS without intervening the process?</li>
  <li><p>How do I parse a structure like following by using Spreadsheet::ParseExcel</p>
  
  <pre><code>row17 course
  row18           math    2
  row19           eng     4
  </code></pre></li>
  </ol>