MySQL database to Excel spreadsheet report

[Sport Girl]

Hi everybody.

I am new in PERL language and working in it since little hours, actually i am still a trainee and i need help please.

In fact, i need a script in PERL that enables me to retrieve data from a table from the database in MySQL and write its contents in an excel sheet file as an excel repot.

I have done a script but it is still in draft cause it's not working.

Can somebody help me please.

Thanks a lot.

[numberwhun]

Well then, Welcome to Perl!!!

Please note, when you post for assistance, be sure to always post the codd that you have tried thus far. This is not a script writing service. We are happy to help you tweak and debug your code, but won't do all of the legwork for you.

I can throw this bit of information your way though. To interact with a database, you will want to use the DBI module. Also, for writing to/creating an Excel spreadsheet, I believe you will want to read up on the Spreadsheet::SimpleExcel module. I don't have experience with that last one, but the web page is quite detailed.

Regards,

Jeff

[Sport Girl]

Hi again everybody, according to my first request of help, i am puttig the code that i have done until now. Actually this is the forst code i do in Perl so i suppose there could be several faults.

I was supposed to retreive the data from a MySQL database table. this data should be viewed in an excel sheet report.

This is what i have done until now and i am blocked, i really need help please;

//connect to MySQL database

!/usr/bin/perl -w
use strict;
use DBI;
use Spreadsheet::SimpleExcel;
 
print "Available Database Drivers:\n\n";
print join("\n", DBI->availableDrivers()), "\n\n";
 
my %attr => (RaiseError => 0);
my $dbh = DBI -> connect("DBI:mysql:dbname=bugs:localhost", "root", "root")
    or die("Error: $DBI::errstr");
 
my $excel_file = "C:/Perl/Bugs/reports";
 
//-e checks to see if the file exists
 
if (-e "$excel_file") {
    #open Excel file
    my $Book = $Excel->Workbooks->Open("$excel_file");
 
} else {
 
    //paste code here to create a new file
    #create a new instance
    my $excel = Spreadsheet::SimpleExcel->new();
    $excel->{Visible}=1;
    $excel->{SheetsInNewWorkBook} = 1;
    $workbook = $excel->Workbooks->Add();
    $worksheet = $Workbook->Worksheets(1);
    $worksheet->{Name} = "Report";
    my $sth = $dbh->prepare("select * from bugs");
    $sth->execute() or die $dbh ->errstr;
    while ($sth->fetch){
        Print "\t \n";
    }
    $sth->finish();
 
    #sort data of worksheet - ASC or DESC
    $excel->sort_data("Name of worksheet",0, "ASC");
 
    #create the spreadsheet
    $excel->output();
 
    #print sheet-names
    print join(", ", $excel->sheets()), "\n";
 
    #get the result as a string
    my $spreadsheet = $excel->output_as_string();
 
    #print result into a file and handle error
    $excel->output_to_file("report.xls") or die $excel->errstr();
    $workbook->SaveAs($excel_file);
    $dbh->disconnect();
 
 

[Sport Girl]

Hi again everybody, according to my first request of help, i am putting the code that i have done until now. Actually this is the first code i do in Perl so please excuse me for my faults.

I was supposed to retreive the data from a MySQL database table. this data should be viewed in an excel sheet report.

This is what i have done until now and i am blocked, i really need help please;

 
# //connect to MySQL database
 
#!/usr/bin/perl -w
use strict;
use DBI;
use Spreadsheet::SimpleExcel;
 
print "Available Database Drivers:\n\n";
print join( "\n", DBI->availableDrivers() ), "\n\n";
 
my %attr => ( RaiseError => 0 );
my $dbh = DBI->connect( "DBI:mysql:dbname=bugs:localhost", "root", "root" )
  or die("Error: $DBI::errstr");
 
my $excel_file = "C:/Perl/Bugs/reports";
 
# //-e checks to see if the file exists
 
if ( -e "$excel_file" ) {
 
    #open Excel file
    my $Book = $Excel->Workbooks->Open("$excel_file");
 
}
else {
 
    #  //paste code here to create a new file
    #create a new instance
    my $excel = Spreadsheet::SimpleExcel->new();
    $excel->{Visible}             = 1;
    $excel->{SheetsInNewWorkBook} = 1;
    $workbook                     = $excel->Workbooks->Add();
    $worksheet                    = $Workbook->Worksheets(1);
    $worksheet->{Name}            = "Report";
    my $sth = $dbh->prepare("select * from bugs");
    $sth->execute() or die $dbh->errstr;
 
    while ( $sth->fetch ) {
        Print "\t \n";
    }
    $sth->finish();
 
    #sort data of worksheet - ASC or DESC
    $excel->sort_data( "Name of worksheet", 0, "ASC" );
 
    #create the spreadsheet
    $excel->output();
 
    #print sheet-names
    print join( ", ", $excel->sheets() ), "\n";
 
    #get the result as a string
    my $spreadsheet = $excel->output_as_string();
 
    #print result into a file and handle error
    $excel->output_to_file("report.xls") or die $excel->errstr();
    $workbook->SaveAs($excel_file);
    $dbh->disconnect();
}
 

[numberwhun]

Hi again everybody, according to my first request of help, i am putting the code that i have done until now. Actually this is the first code i do in Perl so please excuse me for my faults.

I was supposed to retreive the data from a MySQL database table. this data should be viewed in an excel sheet report.

This is what i have done until now and i am blocked, i really need help please;

 
# //connect to MySQL database
 
#!/usr/bin/perl -w
use strict;
use DBI;
use Spreadsheet::SimpleExcel;
 
print "Available Database Drivers:\n\n";
print join( "\n", DBI->availableDrivers() ), "\n\n";
 
my %attr => ( RaiseError => 0 );
my $dbh = DBI->connect( "DBI:mysql:dbname=bugs:localhost", "root", "root" )
  or die("Error: $DBI::errstr");
 
my $excel_file = "C:/Perl/Bugs/reports";
 
# //-e checks to see if the file exists
 
if ( -e "$excel_file" ) {
 
    #open Excel file
    my $Book = $Excel->Workbooks->Open("$excel_file");
 
}
else {
 
    #  //paste code here to create a new file
    #create a new instance
    my $excel = Spreadsheet::SimpleExcel->new();
    $excel->{Visible}             = 1;
    $excel->{SheetsInNewWorkBook} = 1;
    $workbook                     = $excel->Workbooks->Add();
    $worksheet                    = $Workbook->Worksheets(1);
    $worksheet->{Name}            = "Report";
    my $sth = $dbh->prepare("select * from bugs");
    $sth->execute() or die $dbh->errstr;
 
    while ( $sth->fetch ) {
        Print "\t \n";
    }
    $sth->finish();
 
    #sort data of worksheet - ASC or DESC
    $excel->sort_data( "Name of worksheet", 0, "ASC" );
 
    #create the spreadsheet
    $excel->output();
 
    #print sheet-names
    print join( ", ", $excel->sheets() ), "\n";
 
    #get the result as a string
    my $spreadsheet = $excel->output_as_string();
 
    #print result into a file and handle error
    $excel->output_to_file("report.xls") or die $excel->errstr();
    $workbook->SaveAs($excel_file);
    $dbh->disconnect();
}
 

Ok, so have you run this to see if it works? If so, what were the results? Were there any errors? Please provide any information you can.

Regards,

Jeff

[archulu]

hay i am sending sample code. i think its help to u(even now also ur waiting for this)


package test::excel_back;
use DBI;
use Apache::File;
use Apache::Request;
use Spreadsheet::WriteExcel;
use MIME::Types;

my($dbh,$sth,@res);

sub prepare_excel {
my $name= 'archulu';
my $workbook = Spreadsheet::WriteExcel->new("/tmp/rekha.xls");
my $worksheet = $workbook->add_worksheet("$name");
my $format = $workbook->add_format();
my $format1= $workbook->add_format();
my $row=1;

$worksheet->set_column('E:E',16);
$worksheet->set_column('F:F',12);

$format->set_align('center');
$format->set_color('pink');
$format->set_bold();
$format1->set_color('green');
$format1->set_align('center');

$worksheet->write($row,4,"BankId",$format);
$worksheet->write($row,5,"Name",$format);
$worksheet->write($row,6,"place",$format);


$row++;
while(@res = $sth->fetchrow_array)
{
$worksheet->write($row,4,"$res[0]",$format1);
$worksheet->write($row,5,"$res[1]",$format1);
$worksheet->write($row,6,"$res[2]",$format1);
$row++;
}
$workbook->close();
}

sub handler {

my $r=Apache::Request->new(shift);
my $value=$r->param('cb');
$dbh=DBI->connect("dbi:Pg:dbname=emp;host=localhost","archa na","");
$sth=$dbh->prepare("select * from bank");
$sth->execute();

if($value eq 'getExcel')
{
&prepare_excel();
my $fh = Apache::File->new("/tmp/rekha.xls");
$r->send_http_header('application/vnd.ms-excel');
my $fd=$r->send_fd($fh);
return;
}
return OK;
}
1;