An Excel file is a binary file within a binary file. It contains several interlinked checksums and changing even one byte can cause it to become corrupted. As such you cannot simply append or update an Excel file. The only way to achieve this is to read the entire file into memory, make the required changes or additions and then write the file out again.

First of all, I don't see where you are using the "ParseExcel" modules in your code. Second, as the error message says and the CPAN documentation shows, module Spreadsheet::ParseXLSX has no "SaveAs" function in spite of what the module author says about it being an adaptor of module Spreadsheet::ParseExcel.

Note the modules above are parsers, not writers. I recommend you try module Spreadsheet::WriteExcelXML in your code.

Ya I am using

 use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::ParseExcel::Workbook;
[download]

because with when I start reading I saw that Spreadsheet::ParseXLSX an adaptor from Spreadsheet::ParseExcel . When I got error I think its necessary and included it. Note the modules above are parsers, not writers Then How its working here.

Here is the program which taking the data from DB and opening template(.xls) and writing the DB data into it using Spreadsheet::ParseExcel

#!/usr/bin/perl

use DBI;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
#use Spreadsheet::Read;

$date=$ARGV[0];#yyyy-mm-dd
$month=$ARGV[1];#August
$date or $date=`date --date='1 day ago' +%Y-%m-%d`;
$month or $month=`date --date='1 day ago' +%B`;
chomp $month;
chomp $date;
print "Month:$month\n";


$Database_name="Busy_Hour_Report";
$IP_address="xxx.xx.xx.xxx";

$command = "cp /root/prac/packages/B_Mum_Busy_hour_report_fdb.xls /roo
+t/prac/packages/B_Mum_Busy_hour_report_fdb_$date.xls";
print $command;
`$command`;


my $dbh = DBI->connect("DBI:mysql:database=$Database_name;host=$IP_add
+ress;mysql_socket=/opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","root
+","",      {'RaiseError' => 1});

#---------------------------------------------------------------------
+------------------------------------#
#---------------------------------------------------------------------
+------------------------------------#
sub parser_excel
{
        my $sql_comm=$_[0];
        my $sheet_no=$_[1];
        #Selecting the data to fetch
        my $sth = $dbh->prepare("$sql_comm");
        $sth->execute() or die $DBI::errstr;
        # Open an existing file with SaveParser
        my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
        my $template = $parser->Parse("B_Mum_Busy_hour_report_fdb_$dat
+e.xls");

        # Get the first worksheet.
        my $sheet = $template->worksheet($sheet_no);
        my $row  = 5;
        my $col  = 1;

        while (my @row = $sth->fetchrow_array())
        {
                foreach my $value (@row)
                {
                        $sheet->AddCell( $row, $col, $value );
                        ++$col;
                }
                $row++;
                $col=1;
        }
        $template->SaveAs("B_Mum_Busy_hour_report_fdb_$date.xls");
}


parser_excel("Select * from Busy_Hour where monthname(Date)='$month' O
+RDER BY Date DESC",0);
parser_excel("Select * from Hourly_Data where monthname(Date)='$month'
+ and Hour in (21,22,23) ORDER BY Date DESC",1);
parser_excel("Select * from Hourly_Data where Date='$date' order by Ho
+ur",2);
[download]

You are not using Spreadsheet::ParseExcel to write - Spreadsheet::ParseExcel::SaveParser uses two different modules for read/write:

my $template = $parser->Parse("B_Mum_Busy_hour_report_fdb_$date.xls");
[download]

reads entire Excel file into memory using Spreadsheet::ParseExcel

$template->SaveAs("B_Mum_Busy_hour_report_fdb_$date.xls");
[download]

overwrites old Excel file using Spreadsheet::WriteExcel

Please read the questions carefully. I clearly mention that i need to open a template and write data into that. i dont need to open a new .xlsx file. With Spreadsheet::WriteExcelXML is it possible to open a existing file???

First, I see the author has made a new module Excel::Writer::XLSX to replace the deprecated Spreadsheet::WriteExcelXML.

I would use separate reading and writing processes. First save the current file as a new, temp name. Read it to get data into suitable form. Write the new file updated as desired. Save the new, updated file. The clear separation should help maintenance of your code.

I have used Excel readers and writers a lot but am not aware of any that can read and write the same stream, but then I have never needed an update capability either.

i need to open a template and write data into that. i dont need to open a new .xlsx file. With Spreadsheet::WriteExcelXML is it possible to open a existing file?

You said you need to open a template file. Usually, that implies making a new file based on the template. And writing back to the same file makes the template not a template any more.

But, it is possible to write the data back to the original file:

# open file in read/write mode
open(my $xlshandle, '+<', $xlsfile) or die "Can't open '$xlsfile' for 
+read/write: $!\n";
binmode($xlshandle);
# insert code to read spreadsheet
# reset handle to beginning of file
seek($xlshandle, 0, 0);
my $newxls = Spreadsheet::WriteExcelXML->new($xlshandle); # handle, NO
+T file
# insert code to put updated data into $newxls
$newxls->close();
close($xlshandle);
[download]

BUT, it's much safer to:

my $newxlsfile = $xlsfile . '.new';
my $newxls = Spreadsheet::WriteExcelXML->new($newxlsfile); # file, NOT
+ handle
# insert code to put updated data into $newxls
$newxls->close();
close($xlshandle);
rename($xlsfile, $xlsfile . '.bak');
rename($newxlsfile, $xlsfile);
[download]

You will still get a file with the same name as the original file, AND will have a back up of the original file.

Update: added a call to binmode();

"Can't locate object method "SaveAs" via package "Spreadsheet::ParseEx
+cel::Workbook" at ./parse_xlsx.pl line 43.
[download]

Where in the documentation did you find this SaveAs method?

Then how to solve this. i want to open an existing file. Cant possible with Spreadsheet::WriteExcelXML Is there any module to open existing and write the data into .xlsx file and save it.

If you need to handle both xls and xslx files, take a look at wrapper module Data::Table::Excel which I currently use for reading, but the docs say it can be used for writing, too. The module uses other modules as described from the docs:

To read and write Excel .xls (2003 and prior) format, we use Spreadsheet::WriteExcel and Spreadsheet::ParseExcel; to read and write Excel .xlsx (2007 format), we use Spreadsheet::ParseXLSX and Excel::Writer::XLSX. If this module gives incorrect results, please check if the corresponding Perl modules are updated. (We switch to Spreadsheet::ParseXLSX from Spreadsheet::XLSX from version 0.5)

Note: The only reason I haven't used Data::Table::Excel for writing is because I didn't find it until I needed to read xlsx files--I was already using module Spreadsheet::DataToExcel to write xls files, mainly because of the structure of the data I had to write to Excel.