phpexcel class not working in unix

Sep 18, 2009 at 1:03 PM

I have downloaded phpexcel class 1.6.3 version . It is working in local windows server . But in remote unix server , the output is shown gibberish.

Please advise how to rectify the proper creation of excel sheet.

 

Regards,

Prakash.

Coordinator
Sep 18, 2009 at 2:32 PM

What are you trying to do with PHPExcel?

What do you mean by gibberish?

Are you trying to send output direct to your browser? or save it as a file?

Sep 19, 2009 at 6:19 AM

I am trying to generate MySql data into excel sheet as report and the report is not proper . It seems it is giving binary output.

The output showing in the excel file like this.

ÐÏࡱá;þÿ  þÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ




 !"#$%&'()*+,-./0123456789:þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ »

ÌAB°=¼%r8X"1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1ȐArial1ȼCalibri1ȐCalibri ¤Generalàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à À ठL ठL “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…÷ WorksheetüWellintonRx 20090910 20090916
Total Cost($)Viagra: 50mg (4 tablets))Carisoprodol soma Watson: 350MG (120 tab))Carisoprodol soma Watson: 350MG (150 tab))Carisoprodol SOMA WATSON : 350MG (60 tab)(Carisoprodol SOMA WATSON: 350MG (90 tab)Fioricet: generic (120tab)Fioricet: generic (30tab)Fioricet: generic (90tab)Tramadol: 50mg (120 tablets)Tramadol: 50mg (180 tablets)Tramadol: 50mg (30 tablets)Tramadol: 50mg (60tablets)Tramadol:  50mg (90tablets)

»

ÌA*+‚Á&CData Map&RPage &P of &Nƒ„&ffffffæ?'ffffffæ?(è?)è?¡"dXX333333Ó?333333Ó?ý



ý


ý


ý


ý


ý


ý


ð?@@×£p=
×.@×£p=

×N@ý


6@^@ ¤@)\Âõ(¼?fffff&r@ý
  c@Àb@€ÿÖ@)\Âõ(¼?=¤@ý

 

Sep 19, 2009 at 8:25 AM

I am trying to generate MySql data into excel sheet as report and the report is not proper . It seems it is giving binary output.

I am saving the file as xls file.

My server and version details:

my server is redhat linux

PHP Version is 5.1.6

mysql Server version: 5.0.27

 

Please advice me how to get proper creation of excel sheet.

 

Coordinator
Sep 19, 2009 at 10:29 AM

An xls file is a binary format, that's why you need a library like PHPExcel to actually generate it in the correct format rather than simply writing text values to a file. However, when sending it to the browser, you need to send the correct headers so that the browser will recognise that it is a binarl xls file.

Section 4.4.3 of the developer documentation (/Documentation/PHPExcel developer documentation.doc) explains how you should use PHP's header() function to send the appropriate headers to the browser.

/* Here there will be some code where you create $objPHPExcel */

// redirect output to client browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myfile.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); 
Make sure that you don't echo anything prior to sending the headers and file to the browser
Sep 22, 2009 at 6:53 AM

I have used the same headers and not getting proper output.

 

Please check my code.

<?php

    $start_date=$_GET['sdate'];
    $end_date=$_GET['edate'];
    $pharmacy=$_GET['pharm'];
    /*$conn=mysql_connect("localhost","root","mysql");
    mysql_select_db("vongims");*/
    set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/clients/vongai/ims/phpexcel/Classes');
    //echo get_include_path();
    require("PHPExcel.php");
    // Start to build the spreadsheet
    $excel = new PHPExcel();
    $excel->setActiveSheetIndex(0);
    include("db_connection.php");
    $res_pharm=mysql_query("select * from ims_pharmacies where pharm_Name='$pharmacy'");
    $num_pharm = mysql_num_rows($res_pharm);
    if($num_pharm)
    {
        $dbname = mysql_result($res_pharm, 0, "database_used");
        $pharmacy_used = mysql_result($res_pharm, 0, "pharnName_used");
        $costper_script = mysql_result($res_pharm, 0, "costperscript");
        $sitename=substr($pharmacy, 0, 3);
        $sitename = strtoupper($sitename);
        if($pharmacy=='Vongai')
            $pharm=9;
        else
            $pharm=5;
    }
  
    
     //echo "$dbname    $pharmacy_used     $costper_script    $sitename $sitename";
    // Put in the header row
    $excel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $pharmacy. " ".$start_date." ". $end_date);
    $excel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->getFont()->setBold(true);


    $excel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, "Drug Name");
    $excel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, "# Scripts");
    $excel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, "Pills");
    $excel->getActiveSheet()->setCellValueByColumnAndRow(3, 2, "Total Pills");
    $excel->getActiveSheet()->setCellValueByColumnAndRow(4, 2, "Cost/Pill($)");
    $excel->getActiveSheet()->setCellValueByColumnAndRow(5, 2, "Total Cost($)");

    $excel->getActiveSheet()->getStyleByColumnAndRow(0, 2)->getFont()->setBold(true);
    $excel->getActiveSheet()->getStyleByColumnAndRow(1, 2)->getFont()->setBold(true);
    $excel->getActiveSheet()->getStyleByColumnAndRow(2, 2)->getFont()->setBold(true);
    $excel->getActiveSheet()->getStyleByColumnAndRow(3, 2)->getFont()->setBold(true); 

   $excel->getActiveSheet()->getStyleByColumnAndRow(4, 2)->getFont()->setBold(true);
    $excel->getActiveSheet()->getStyleByColumnAndRow(5, 2)->getFont()->setBold(true);
    
    $excel->getActiveSheet()->getHeaderFooter()->setOddHeader("&amp;CData Map");
    $excel->getActiveSheet()->getHeaderFooter()->setOddFooter("&amp;RPage &amp;P of &amp;N");


       $sql_invoice="select vongims.ed_rxnums_$pharmacy_used.drug,count(*) as scripts from vongims.ed_rxnums_$pharmacy_used where vongims.ed_rxnums_$pharmacy_used.digitdate>='$start_date' and vongims.ed_rxnums_$pharmacy_used.digitdate<='$end_date' and vongims.ed_rxnums_$pharmacy_used.drug!='' and vongims.ed_rxnums_$pharmacy_used.pharm=$pharm and vongims.ed_rxnums_$pharmacy_used.ordernumber in(select ordernum from $dbname.orders_shipped) group by vongims.ed_rxnums_$pharmacy_used.drug";
    //echo $sql_invoice;
    $res_invoice=mysql_query($sql_invoice);
    if($res_invoice)
    {
        $numrows_invoice=mysql_num_rows($res_invoice);
        //echo $numrows_invoice;
        $grand_amt_total=0;
        $grand_script_total=0;
        $row_count = 3;
        while($invoice_result=mysql_fetch_array($res_invoice))
        {
            //Query to Get the Actula drugname from prods table with refname
            $pr = mysql_query("select name from $dbname.prods where $dbname.prods.refname='$invoice_result[drug]'");
            $rowspr = mysql_num_rows($pr);
            $prodname = mysql_result($pr, 0, "name");

            //Seperating name and dosase,tablets
                $res_prodname=stristr($prodname,": ");
                if($res_prodname)
                    $prod_fullname = explode(": ",$prodname);
                else
                    $prod_fullname = explode(":",$prodname);

                $disply_prodname=trim($prod_fullname[0]);
                if(stristr($disply_prodname,"("))
                {
                    $disply_prodname1=explode("(",$disply_prodname);
                    $disply_prodname=trim($disply_prodname1[0]);
                }
                $dosage_chk=stristr($prod_fullname[1],"mg");
                if(!$dosage_chk)
                    $dosage_chk=stristr($prod_fullname[1],"MG");
                if($dosage_chk)
                {
                $med_second_part = str_ireplace("mg","MG",$prod_fullname[1]);
                $prod_dosage=explode("MG",$med_second_part);
                $prod_new_dose=trim($prod_dosage[0]);
                $prod_new_dose=$prod_new_dose."MG";
                
                //echo $prod_dosage[1]."<br>";
                $num_tab=$prod_dosage[1];
                $num_tab=str_ireplace("(","",$num_tab);
                $num_tab=str_ireplace(")","",$num_tab);
                $num_tab=intval($num_tab);
                
                //echo $num_tab;

                //Query to get the product price from meds_pricelist table by comparing name and dosage
                $qry="select prod_price from vongims.meds_pricelist where prod_name like '$disply_prodname%' and prod_dosage='$prod_new_dose'";
                @$pill_cost = mysql_query("select prod_price from vongims.meds_pricelist where prod_name like '$disply_prodname%' and prod_dosage='$prod_new_dose'");
                }
                else
                {
                    @$pill_cost = mysql_query("select prod_price from vongims.meds_pricelist where prod_name like '$disply_prodname%'");
                    $qry="select prod_price from vongims.meds_pricelist where prod_name like '$disply_prodname%'";
                }
                //echo $qry;
                $rowscost = mysql_num_rows($pill_cost);
                @$prod_cost = mysql_result($pill_cost, 0, "prod_price");

                
                        
                $display_total_pills=$num_tab*$invoice_result['scripts'];//Total Pills calculation
                $display_totalcost=$display_total_pills*$prod_cost; //Total Pills cost
                $grand_amt_total+=$display_totalcost;//Adding amt to totals
                $grand_script_total+=$invoice_result['scripts'];//Adding No of script to total scripts
          

                $excel->getActiveSheet()->setCellValueByColumnAndRow(0, $row_count, $prodname);
                $excel->getActiveSheet()->setCellValueByColumnAndRow(1, $row_count, $invoice_result['scripts']);
                $excel->getActiveSheet()->setCellValueByColumnAndRow(2, $row_count, $num_tab);
                $excel->getActiveSheet()->setCellValueByColumnAndRow(3, $row_count, $display_total_pills);
                $excel->getActiveSheet()->setCellValueByColumnAndRow(4, $row_count, $prod_cost);
                $excel->getActiveSheet()->setCellValueByColumnAndRow(5, $row_count, $display_totalcost);
 
                $row_count++;
            }
            
        $grand_total_amt4scripts=$grand_script_total*$costper_script;
        $grand_due_total=$grand_amt_total+$grand_total_amt4scripts;
    }
    
  
    
     $file_name=$pharmacy_used."_".$start_date."_TO_".$end_date.".xls";
    // Output the headers
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$file_name.'"');
    header('Cache-Control: max-age=0');

    
    // Output the spreadsheet in binary format
    include 'PHPExcel/Writer/Excel5.php';
    $writer = new PHPExcel_Writer_Excel5($excel);
    $writer->save("php://output");

?>

 

And this code is working in my local system(windows), but not working in linux system

 

Please advice me on this.

Developer
Sep 22, 2009 at 4:20 PM

What you are describing can happen if one has accidentally echoed some white space like Mark is saying. For example if there are some line breaks or spaces before the opening <?php tag.

What will happen:

Try to upload the broken Excel file somewhere so we can check if that is the problem.