title: read and write excel file in perl
body: <p>I am a recent Perl user. I need some help regarding the reading and writing of files in perl.</p>
  
  <p>Let me explain the scenario: For example, I have a input.xls file which contains string,numeric,drop down list. 
  Some cells in the sheets are locked.</p>
  
  <p>I want to read the data from input.xls file and want to write it in a new file file called output.xls. </p>
  
  <p>The problem I am facing here is I am unable to retain the formatting of the file from which I am reading. </p>
  
  <p>I.e the output file which is generated does not display the drop down as well as the the cells which are locked in input.xls file does not appear in the output.xls file. </p>
  
  <p>Secondly even the formatting of the input file is disturbed in the output file. For e.g if cells are merged in input file then the formatting does not appear the same in output file. Kindly guide. </p>
  
  <p>Here is my code for your reference:</p>
  
  <pre><code>#!/usr/bin/perl -w
  
  use strict;
  use Spreadsheet::ParseExcel;
  use Spreadsheet::WriteExcel;
  use Spreadsheet::ParseExcel::SaveParser;
  use CGI;
  use CGI::Carp qw(fatalsToBrowser);
  print qq[Content-type:text/html \n\n];
  my $cs='Book2.xls';
   # Open the template with SaveParser
  my $parser   = new Spreadsheet::ParseExcel::SaveParser;
  #my $formatter=Spreadsheet::ParseExcel::FmtJapan-&gt;new();
  my $template = $parser-&gt;Parse('e.xls');
  
  my $sheet    = 0;
  my $row      = 0;
  my $col      = 2;
  
  # Get the format from the cell
  my $format   = $template-&gt;{Worksheet}[$sheet]
                          -&gt;{Cells}[$row][$col]
                          -&gt;{FormatNo};
  
  
  
  # Write data to some cells
  $template-&gt;AddCell(0, $row,   $col,   1,     $format);
  $template-&gt;AddCell(0, $row+1, $col, "This is a hello world eg", $format);
  #$format-&gt;{Lock};
  # Add a new worksheet
  # $template-&gt;AddWorksheet('New Data');
  
  # The SaveParser SaveAs() method returns a reference to a
  # Spreadsheet::WriteExcel object. If you wish you can then
  # use this to access any of the methods that aren't
  # available from the SaveParser object. If you don't need
  # to do this just use SaveAs().
  #
  my $workbook;
  
  {
      # SaveAs generates a lot of harmless warnings about unset
      # Worksheet properties. You can ignore them if you wish.
      local $^W = 0;
  
      # Rewrite the file or save as a new file
      $workbook = $template-&gt;SaveAs('new.xls');
  
  }
  
  # Use Spreadsheet::WriteExcel methods
  
  my $worksheet  = $workbook-&gt;sheets(0);
  # $worksheet-&gt;protect();
  #$worksheet-&gt;write('A1:B1','=1+2');
  #my $locked  = $workbook-&gt;add_format();
  # $locked-&gt;set_locked(1); # A non-op
  
  #my $unlocked = $workbook-&gt;add_format();
  #$locked-&gt;set_locked(0);
  
  # Enable worksheet protection
  #$worksheet-&gt;protect();
  
  # This cell cannot be edited.
  #$worksheet-&gt;write('A1:B1', '=1+2', $locked);
  
  $worksheet-&gt;write($row+2, $col, "World2");
  
  $workbook-&gt;close();
  print qq[
  &lt;head&gt;
  &lt;script&gt; 
  
  &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
  
  &lt;p&gt;The download should start shortly. If it doesn't, click
  &lt;a id="downloadLink" href="http://128.9.45.168/~mint/MINT_Portal/macro             /963/cgi/$cs"     download="$cs" target="_blank"&gt;here&lt;/a&gt;.&lt;/p&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  
  ];
  </code></pre>