1

I'm using this example in order to write to Excel from my SQL DB. Most of my data is in cyrilllic, so i am using utf8_unicode_ci(can be seen in code) encoding.

The issue is as follows: module writes into file, but its contents are unreadable due to some mistake. Or maybe it's just Excel '03(i know it's old, yes) i am using? Could anyone help me with this? 

<?php
//$host='localhost'; $user='me'; $pass='mypassword'; $DataBase='mydatabase';//define the correct values
require 'connection_data.php';
// open the connexion to the databases server
$Link = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE) or die('Can\'t connect !');
$Link->set_charset('utf8_unicode_ci');//if not by default
//your request
$request='SELECT * FROM Reports';
$result= $Link->query($request);//get the result (ressource)
/** Include PHPExcel */
require_once 'PHPExcel.php';//change if necessary

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$F = $objPHPExcel->getActiveSheet();
$Line=1;

while($row = $result->fetch_assoc()){//extract each record
    $F->setCellValue('A'.$Line, $row['Rep_ID']);
    $F->setCellValue('B'.$Line, $row['TownName']);
 $F->setCellValue('C'.$Line, $row['ShopAdress']);
 $F->setCellValue('D'.$Line, $row['ShopName']);
 $F->setCellValue('E'.$Line, $row['ProductName']);
 $F->setCellValue('F'.$Line, $row['ProductPrice']);
 $F->setCellValue('G'.$Line, $row['AddDate']);
 $F->setCellValue('C'.$Line, $row['UserName']);//write in the sheet
    ++$Line;
}
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="report.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
Max Steelcrow
  • 11
  • 4
  • Have you tried saving the file to server disk rather than php://output and then checking it? – Mark Baker Mar 27 '15 at 17:45
  • Have you opened the file in a text editor and looked for spurious whitespace at the beginning/end of the file, and BOM header, any plaintext error messages or markup text visible in the body of the file? – Mark Baker Mar 27 '15 at 17:46
  • Nah, never tried to save to the disk, i'm just scratching the surface of this module. As for opening the file - as i've said, Excel '03 shows mumbo-jumbo, and opening it with NPP shows clear sings of wrong encoding, clearly non-utf8. The only text that is readable is in french and says 'Spreadsheet', and that's it, everything else is xFF-like adress symbols. – Max Steelcrow Mar 27 '15 at 22:56
  • Then it's very hard to help diagnose any problem.... almost always, this is caused by some output being injected into the php://output stream, either before or after the call to save().... sometimes (if the output is before the save() call) an ob_end_clean() immediately before the save() can act as a workround – Mark Baker Mar 27 '15 at 22:59

0 Answers0