using var in a data validated List

Topics: Developer Forum
Jun 26, 2009 at 11:33 AM

Hi,

I'm facing a problem with a data validated list. The problem is that when the xlsx is generated it gives me a warning that it contains unreadable content.

I have to click yes and then it gives me --> "Replaced Part: /xl/worksheets/sheet1.xml part with XML error.  Illegal xml character. Line 113, column 80"


Basicly i know what the error is but i don't know how to solve it.

the Var i'm trying to use looks like this
$Work_List = "Test1, Test2, Test3";

Here you can find my code:

 

$objValidation = $objPHPExcel->getActiveSheet()->getCell('C'.$excel_rows)->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
//The NEXT Line is the problem
$objValidation->setFormula1('"'.$Work_List.'"');
	
$objPHPExcel->getActiveSheet()->getCell('C'.$excel_rows)->setDataValidation($objValidation);

What did i Already tried

$objValidation->setFormula1('"$Work_List"');
$objValidation->setFormula1($Work_List); 
$objValidation->setFormula1('$Work_List'); 

can someone tell me how to use a variable in a validation object?
Thanks in advance!
Ducky

 

 

Jun 26, 2009 at 12:33 PM
Edited Jun 26, 2009 at 12:40 PM

I have found why it doesn't work. The string i supply contains chars like é, ù etc

I tried to convert them to UTF-8 but it doesn't change a thing.

 

function FixEncoding($x){ 
  if(mb_detect_encoding($x)=='UTF-8'){ 
    return $x; 
  }else{ 
    return utf8_encode($x); 
  } 
} 

 

 

anyone an idea to solve this?

Developer
Jun 26, 2009 at 9:09 PM

If your strings are UTF-8 encoded in the first place then it should really not be necessary to do any conversion with utf8_encode(). I rarely have to use that function.

The question is where are those characters é, ù coming from? Have you hard-coded them in the PHP-script? If yes, it should suffice to save the file as UTF-8 (without BOM). Notepad++ is a good text editor doing this.

Jun 29, 2009 at 7:27 AM

I found a function to check the strings encoding and it appears to be UTF8.

The strings are coming from a database and those contain é, ù etc.

if you want i can send you a sample of the database and the code i wrote.

I found it very strange. Even when i convert the chars to html code (like &) it doesn't work.

thanks for your help.

 

kind regards 

vincent

Developer
Jun 30, 2009 at 2:31 AM

>> I found a function to check the strings encoding and it appears to be UTF-8.

Hmm... Right now I still suspect that it isn't properly encoded as UTF-8.

>> The strings are coming from a database and those contain é, ù etc.

If you are retrieving from MySQL, the most common problem is with the connection collation. Can you have a look at this thread and see if the case applies to you:
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=50415

>> if you want i can send you a sample of the database and the code i wrote.

Actually, because I suspect it is the database connection character set that is the problem, I would like to see the generated xlsx file instead (erik at phpexcel dot net). That is, if the above doesn't solve the problem.