Excel report generation using php

Question

I want a excel sheet to download using PHP.

In excel report, numbers generated are not displaying properly in cell.

I want to display numbers properly in the cell.

My code:

 <?php

    include("connection.php");

    if(isset($_POST['submit']) && $_POST['submit']=="Download")
        {

        $payor_code = $_POST['payor_code'];
        $corp_code= $_POST['corp_code'];
        $pro_type = isset($_POST['product_type']);
        $submit_date = $_POST['sub_date'];

     $sql= oci_parse($conn, "select * from members");

    oci_execute($sql);
    $dat = date("d/m/Y"); 
    //echo $dat;
        $filename = "Report_$dat";

           $table = "";
            $table .= '<table border="1" cellpadding="2" cellspacing="0" width="900">';
            $table .= '<tr>';
            $table .= '<td colspan="20" style="height:30px;font-weight:bold; font-size:20px" align="center">' .$filename. '</td>';
            $table .= '</tr>';
            $table .= '<tr>'; 
            $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">ReceivedDate</td>'; 
            $table .= '<td style="height:20px;font-weight:bold;width=50px" align="center" valign="middle">ProductType</td>';
            $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">Name</td>';
             $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">SubmissionDate</td>';
            $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">ClaimNo</td>';
             $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">PatientName</td>';

              $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">PatientIC</td>';
               $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">PrincipalName</td>';
                $table .= '<td style="height:20px;font-weight:bold;width=100px" align="center" valign="middle">PrincipalIC</td>';

            $table .= '</tr>';

            $i = 1;

        while($row = oci_fetch_array($sql))
        {

        $cli_id = $row['CLAIMS_ID']. "<br>\n";
        $pat_name = $row['PATIENT_NAME']. "<br>\n";
        $sub_date = $row['SUBMISSION_DATE']. "<br>\n";
        $remarks= $row['REMARKS']. "<br>\n";
        $doc_rece_date= $row['RECEIVED_DATE']. "<br>\n";
        $group = "Group";       
            $test = "TEST";
            $board_type = "TEST";
            $board_no = "123";
            $prin_name = "Test";
            $table .= '<tr>';
            $table .= '<td  align="left">'.$doc_rece_date.'</td>';
            $table .= '<td  align="left">'.$group.'</td>';
            $table .= '<td  align="left">'.$test.'</td>';       
             $table .= '<td  align="left">'.$sub_date.'</td>';
             $table .= '<td  align="left">'.$cli_id.'</td>';
             $table .= '<td  align="left">'.$pat_name.'</td>';   
             $pat_ic = $row['NRIC_ID'];
             $table .= '<td  align="left">'.$pat_ic.'</td>'; 
             $pric_name = $row['EMP_NAME'];
             $table .= '<td  align="left">'.$pric_name.'</td>';
             $pric_ic = $row['PRINCIPAL_NRIC'];
             $table .= '<td  align="left">'.$pric_ic.'</td>';           
        }
        $table .= '</table>';

    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment; filename=$filename.xls");
    header("Content-Transfer-Encoding: binary ");

    echo $table;
    }
    ?>

Parag Tyagi · Accepted Answer · 2014-04-11 05:05:46Z

1

You don't need to add table tags to your excel. You can use fputcsv(). Check example -

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.csv");
header("Pragma: no-cache");
header("Expires: 0");

$header = array(
    "Sn.",
    "Name",
    "Email",
    "Mobile",
    "Address",
    "City",
    "State",
    "Pincode"
       );

$fp = fopen('php://output', 'w');
fputcsv($fp, $header);

$incr = 0;
while($row = mysql_fetch_assoc($execute1)) {

    $name = $row['name'];
    $email = $row['email'];
    $mobile = $row['mobile'];
    $address = $row['address'];
    $city = $row['city'];
    $state = $row['state'];
    $pincode = $row['pincode'];

        $fields = array (
        $incr++,
        $name,
        $email,
        $mobile,
        $address,
        $city,
        $state,
        $pincode
        );

       fputcsv($fp, $fields);
  }

answered Apr 11, 2014 at 5:05

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahalakshmi Over a year ago

Thank you for your reply. Your code is working fine. But still i didnt get the proper output. In excel sheet the numbers are displaying like, 7.91031E+11 . But i want the numbers should display like 791031015293.

Parag Tyagi Over a year ago

It might happen when the excel shows the column in short width. Try increasing the width of that column and you will see what you want.

Mahalakshmi Over a year ago

not because of short width. If you increase the width size also u may get the same error. This is not the width problem.

Guns · Accepted Answer · 2014-04-11 05:07:01Z

0

You should be looking at PHPExcel which has much more than you can expect.

Check the features of PHPExcel here.

answered Apr 11, 2014 at 5:07

Guns

2,7182 gold badges27 silver badges52 bronze badges

3 Comments

Mahalakshmi Over a year ago

thanks for your reply. In excel sheet the numbers are displaying like, 7.91031E+11 . But i want the numbers should display like 791031015293. Any idea..???

Mahalakshmi Over a year ago

I tried cell formatting also. Doing manual its fine. But I want result once i download the file. Through coding i have to do something. Any idea?? help me to solve this issue

Guns Over a year ago

here is what can format the cell $PHPExcelObject->getActiveSheet()->getStyle($cell)->getNumberFormat()->setFormatCode('0');. There are some discussion on phpexcel.codeplex.com/discussions/30244

Collectives™ on Stack Overflow

Excel report generation using php

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related