Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You are doing a <code>$row++</code> when you shouldn't be.</p> <p>Try this code instead as your starting point:</p> <pre><code>my %data; my $state = ""; my $school = ""; my $student = ""; my ( $row_min, $row_max ) = $worksheet-&gt;row_range(); my $row = $row_min; while ($row &lt;= $row_max) { my $cell0 = $worksheet-&gt;get_cell( $row, 0 ); my $cell1 = $worksheet-&gt;get_cell( $row, 1 ); if (defined($cell0)) { my $key = $cell0-&gt;value(); if ($key eq 'School') { $state = 'school'; $school = $cell1-&gt;value(); } elsif ($key eq 'course') { $state = 'course'; } elsif ($key eq 'teacher') { $state = 'teacher'; } elsif ($key eq 'student') { $state = 'student'); $student = $worksheet-&gt;get_cell( $row, 2 )-&gt;value(); } else { $data{$school}{$key} = $cell1-&gt;value(); } } elsif ($state eq 'course') { # process columns for course } elsif ($state eq 'teacher') { # process columns for teacher } elsif ($state eq 'student') { # process columns for student } $row++; } use Data::Dumper; print Dumper(\%data); </code></pre> <p><strong>Update:</strong></p> <p>To process the teacher rows, I would first declare a %teacher_columns hash outside the first while loop and also the $col_min and $col_max using the col_range() method. Then inside the <code>$key eq 'teacher'</code> elsif clause, I would do something like this:</p> <pre><code>%teacher_columns = (); # clear it out in case column names are different for this school for my $col (2 .. $col_max) { my $cell = $worksheet-&gt;get_cell( $row, $col ); $teacher_columns{$cell-&gt;value()} = $col if defined($cell) and $cell-&gt;value(); } </code></pre> <p>Then in the <code>state eq 'teacher'</code> elsif clause, I would do something like this:</p> <pre><code>foreach my $key (keys %teacher_columns) { my $cell = $worksheet-&gt;get_cell($row, $teacher_columns{$key}); if (defined($cell)) { # store cell data into proper location of your data hash } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POFetching zero cells in XLS file using Spreadsheet::ParseExcel
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USimran
    1. USimran
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POFetching zero cells in XLS file using Spreadsheet::ParseExcel
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COgreat thanks to @imran. it works pretty well. But i am a little too stupid to understand the code. with `$teacher_columns{$cell->value()} = $col if defined($cell) and $cell->value(), ` we basically construct a two-layer hash, with the column value as first layer and column number as the second one. My question is that this block will only be triggered while reaching 'teacher' and thus only fetching the cells values related to 'teacher', right? And in `my $cell = $worksheet->get_cell($row, $teacher_columns{$key});` ,how does the `get_cell` method fetch the +1 row value?
      singulars
      1. PO
      1. USuser2198367
    2. CO@user2198367 You are approximately right. %teacher_columns is a single level hash that will map the column name like gender to a column number. Yes, this gets re-populated every time we are at the 'teacher' row. About your last question, you do not need to worry about doing +1 on the row, you are already in the 'teacher' state and subsequent rows will be processed as a teacher row until another state is encountered.
      singulars
      1. PO
      1. USimran
    3. COjust fixed a typo in the updated answer. The last line where it said `$state eq 'course'` should have been `$state eq 'teacher'`
      singulars
      1. PO
      1. USimran
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload