Problem with MySQL to Excel special characters

Topics: Developer Forum
Feb 13, 2010 at 3:52 PM

Hi

I created a php file that exports data from mysql table and writes it into an excel file, it works grteat but I have a problem with the following characters:

äöëéè

My field I create is below:

$objPHPExcel->getActiveSheet()->setCellValue('G'.$xlsRow, $row_Recordset1['School']);

The out put would be as follows:

MySQL field data is (Hoërskool Rynfield), the once it has written the excel file it displays only (Ho)

my colation is st to utf8_unicode_ci

Any help would be appreciated

 

Thanks

Coordinator
Feb 13, 2010 at 9:49 PM

Ensure that you're using UTF-8 for the PHP MySQL Connection:

In php versions 5.2 and later, use

mysql_set_charset('utf8',$conn); 
Feb 15, 2010 at 11:56 AM

Hello,

I'm having a similar problem (see my post http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=83263)

I'm reading in utf-8 xml and entering it into cells and getting the same result as you.

 

If you find a solution I'd love to hear from you.

 

Thanks

 

Peter

Feb 15, 2010 at 12:31 PM

Hi Mark

The mysql_set_charset definitely solved my problem,

Thanks

 

See my full code below:

<?php require_once('../Connections/CMS.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_Recordset1 = "-1";
if (isset($_GET['id'])) {
  $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}


mysql_select_db($database_CMS, $CMS);
mysql_set_charset('utf8',$CMS); 
$query_Recordset1 = sprintf("SELECT a.intEvent as 'eID', b.intRider as 'rID', b.intHID as 'hID',d.strRegion as Province,
e.strRegion as Region, i.strSchool as 'School', i.id as sID , h.strType as Discipline, b.strClass as Class,
b.intLevel as Level ,CONCAT(f.strName,' ',strLast) as Rider, g.strHorse as Horse, c.strSchool as 'SchoolType' ,
i.id as sID , b.intType , b.intJType, b.intTest,b.intLevel, j.strType as JType, b.id as 'EntryID',b.decFee1 as 'Entry Fee', b.decFee2 as 'Grading',
b.decFee3 as 'Stabling'
FROM ss_entry a left join ss_enterh b on b.intEvent = a.intEvent
left join ss_dates c on c.id = a.intEvent
left join ss_region d on c.intRegion = d.id
left join ss_subregion e on c.intSubR = e.id
left join ss_rider f on f.id = b.intRider
left join ss_horse g on g.id = b.intHID
left join ss_classtype h on h.id = b.intType
left join ss_schools i on i.id = f.intSchool
left join cr_jumpingtype j on j.id = b.intJType
WHERE a.intEvent = %s GROUP BY b.id ORDER BY h.strType ASC, b.strClass ASC; ", GetSQLValueString($colname_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $CMS) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


$colname_Recordset2 = "-1";
if (isset($_GET['id'])) {
  $colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_CMS, $CMS);
$query_Recordset2 = sprintf("SELECT b.strRegion as Province, c.strRegion as Region FROM ss_dates a left join ss_region b on a.intRegion = b.id left join ss_subregion c on a.intSubR = c.id WHERE a.id = %s", GetSQLValueString($colname_Recordset2, "int"));
$Recordset2 = mysql_query($query_Recordset2, $CMS) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

$colname_dateID = "-1";
if (isset($_GET['id'])) {
  $colname_dateID = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_CMS, $CMS);
$query_dateID = sprintf("SELECT * FROM ss_dates WHERE id = %s", GetSQLValueString($colname_dateID, "int"));
$dateID = mysql_query($query_dateID, $CMS) or die(mysql_error());
$row_dateID = mysql_fetch_assoc($dateID);
$totalRows_dateID = mysql_num_rows($dateID);


$vClass = $row_Recordset1['Class'];
$vLevel = $row_Recordset1['intLevel'];
$vQualifier = trim($row_dateID['strQ']);
$vRegion = trim($row_Recordset2['Province'])." ".trim($row_Recordset2['Region']);
$vSchoolType = $row_Recordset1['School Type'];
//$vJumpType = $row_Recordset1['JType'];



/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2009 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.1, 2009-11-02
 */

/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once '../Classes/PHPExcel.php';

require_once '../Classes/PHPExcel/Cell/AdvancedValueBinder.php';

/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';

/** PHPExcel_RichText */
require_once '../Classes/PHPExcel/RichText.php';


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

// Set properties
$objPHPExcel->getProperties()->setCreator("c-Roach designs")
							 ->setLastModifiedBy("Roche De Kock")
							 ->setTitle("SANEF Schools Export")
							 ->setSubject("Running Order")
							 ->setDescription("Running Order Export")
							 ->setKeywords("office 2003 openxml php")
							 ->setCategory("Results");


$styleThinBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN,
			'color' => array('argb' => 'FF000000'),
		),
	),
);

$styleThickBlackBorderOutline = array(
	'borders' => array(
		'outline' => array(
			'style' => PHPExcel_Style_Border::BORDER_THICK,
			'color' => array('argb' => 'FF000000'),
		),
	),
);

// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Running Order');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Entry ID');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Discipline');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Class');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Level');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Rider ID');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Rider');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'Horse ID');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'Horse');
$objPHPExcel->getActiveSheet()->setCellValue('J1', 'School ID');
$objPHPExcel->getActiveSheet()->setCellValue('K1', 'School');
$objPHPExcel->getActiveSheet()->setCellValue('L1', 'Entry Fee');
$objPHPExcel->getActiveSheet()->setCellValue('M1', 'Grading');
$objPHPExcel->getActiveSheet()->setCellValue('N1', 'Stabling');

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('H1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('I1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('M1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('N1')->getFont()->setBold(true);

$xlsRow = 2;
$xlsNr = 1;

do {
$objPHPExcel->getActiveSheet()->setCellValue('A'.$xlsRow, $xlsNr);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$xlsRow, $row_Recordset1['EntryID']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$xlsRow, $row_Recordset1['Discipline']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$xlsRow, $row_Recordset1['Class']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$xlsRow, $row_Recordset1['Level']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$xlsRow, $row_Recordset1['rID']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$xlsRow, $row_Recordset1['Rider']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$xlsRow, $row_Recordset1['hID']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$xlsRow, $row_Recordset1['Horse']);
$objPHPExcel->getActiveSheet()->setCellValue('J'.$xlsRow, $row_Recordset1['sID']);
$objPHPExcel->getActiveSheet()->setCellValue('K'.$xlsRow, $row_Recordset1['School']);
$objPHPExcel->getActiveSheet()->setCellValue('L'.$xlsRow, $row_Recordset1['Entry Fee']);
$objPHPExcel->getActiveSheet()->setCellValue('M'.$xlsRow, $row_Recordset1['Grading']);
$objPHPExcel->getActiveSheet()->setCellValue('N'.$xlsRow, $row_Recordset1['Stabling']);


$xlsRow++;
$xlsNr++;

} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
 

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Dressage');
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// 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="'.$vQualifier.'"RunningOrder.xls"');
header('Cache-Control: max-age=0');

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


mysql_free_result($Recordset1);

mysql_free_result($Recordset2);

mysql_free_result($dateID);
?>
Jun 29, 2011 at 10:29 AM

Hi,

I am also encountering the same proble. But I am using mssql. Does anyone know of a fix for it?