PHPEXCEL download with PDO database error text become symbol

Question

I am using sample exel code and add using PDO to add data export excel

if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

i am edit some properties

// Set document properties
$objPHPExcel->getProperties()->setCreator("Team IT KSP PRIMADANA")
                         ->setLastModifiedBy("Team IT KSP PRIMADANA")
                         ->setTitle("DATA NASABAH")
                         ->setSubject("KSP PRIMADANA")
                         ->setDescription("Data Perusahaan.")
                         ->setKeywords("office 2007 openxml php")
                         ->setCategory("Penting");

i am also add some data for header excel, until this code, when try download is okay an not error.

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Tanggal Masuk')
        ->setCellValue('B1', 'Nomor Register')
        ->setCellValue('C1', 'Cabang');

then when add looping PDO to add data content table and download it, value from this database make symbol data excel

$host = 'localhost'; 
$username = 'root'; 
$password = 'server';
$dbname = 'primadana';
// Create your database query

this is my PDO for print data from database to excel which make text to symbol

try{
$conn = new PDO("mysql:host=$host;dbname=$dbname",$username,$password);

//$stmt = $conn->prepare('select * from tblpdo where age=:age');
$stmt = $conn->prepare('SELECT * FROM data_berkas_masuk where user_id = 6 order by user_id');
$result=$stmt->fetchAll();
$no=2;
if(count($result))
{   
    foreach ($result as $row) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A'.$no, ($row['in_tgl']));
    $no++;
    }
}
else{echo "no rows returned";}  
}
catch(PDOException $e){
    echo 'ERROR: '.$e->getMessage();
}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the   first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always     modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>

If the data from your database isn't UTF-8, then you need to convert it to UTF-8 before setting the values in the spreadsheet - http://stackoverflow.com/questions/279170/utf-8-all-the-way-through — Mark Baker, Apr 29 '16 at 07:05
i try change to this: $conn = new PDO("mysql:charset=utf8mb4;host=$host;dbname=$dbname",$username,$password); still error... :( — Steven Sugiarto Wijaya, Apr 29 '16 at 08:19
What charset is the data in your database? If you're not storing the data as UTF-8 then you may need to change it to UTF-8 before setting the values in cells — Mark Baker, Apr 29 '16 at 08:29
If you set up the database, you should know what charset you're using for your data_berkas_masuk table; but `show create table data_berkas_masuk` would tell you — Mark Baker, Apr 29 '16 at 10:20
storage engine: innoDB if i try show all table from phpmyadmin Type: MyISAM Collation: latin1_swedish_ci... (*Is this what you mean?) when i try running with edit $conn = new PDO("mysql:charset='lati1_swedish_ci';host=$host;dbname=$dbname",$username,$password); or $conn = new PDO("mysql:charset=lati1_swedish_ci;host=$host;dbname=$dbname",$username,$password); still failure, it says" file format or extension is not valid" — Steven Sugiarto Wijaya, Apr 30 '16 at 02:05

score 0 · Accepted Answer · answered Apr 30 '16 at 04:19

0

i find myself lol.

 $stmt = $conn->prepare('SELECT * FROM data_berkas_masuk where user_id = 6 order by user_id');
$stmt->execute();
$result=$stmt;

answered Apr 30 '16 at 04:19

Steven Sugiarto Wijaya

171
1
2
12

PHPEXCEL download with PDO database error text become symbol

1 Answers1