XMLWRITER 2007: how to store HTML code in Excel 2007 cell?

Topics: Developer Forum
Aug 28, 2009 at 10:01 PM
Edited Aug 28, 2009 at 10:16 PM

Hi, I'm trying to store HTML code in exce 2007 cell and get those naughty message about incorrect data. After correction all text data is missed, only numbers left.

After some researches I defined that problem is only for XMLWRITER 2007 and only for those cell with html code. It seems that html tags break excel 2007 xml markup.

So question is: How to store HTML code in Excel 2007 cell?

 

PS: addslashes() and replacing of <,>,&," signs do not help. htmlentities() + specialchars() solve problem, but make html code totaly unreadable.

Update: removing all tags and htmlentities with:

$row['description'] = htmlentities(preg_replace('%<.*?>%i','',$row['description']));

helped, but it is a workaround

Developer
Aug 28, 2009 at 11:17 PM

Do you have some code example how you are inserting HTML code into the cell?

Aug 29, 2009 at 12:21 AM
foreach($records as $index=>$row)
{			
	foreach(array_values($row) as $i=>$value)
		$xlsObject->setActiveSheetIndex(0)->setCellValueByColumnAndRow($i,$index+2,$value);
}

$records - indexed array with date from database
$row - associated array with single item data. It contains field with key "description" where html code stored

Ssimple html code example:

I am offering a used DSR-1500AP DVCAM recorder, 
<br><br>fittet with all options:<br><br>DSBK-1505 Analog Input Board
<br><br>DSBK-1501 Digital IN/OUT Board with SDI
<br><br><br><br>Machine has only 750 hours on drum, in perfect cosmetic and mechanical condition - see attached pictures
<
br><br>machine records miniDV, DVCAM and plays back all incl. DVCPro 

Here is only <br> tags but they also throw those exception

Advanced html code example:

<span style="font-weight: bold;">bold</span><br>
<
span style="font-style: italic;">italic</span><br>
<span style="text-decoration: line-through;">striked</span><br>
<span style="text-decoration: underline;">underlined</span><br>
<
h1>h1</h1><h2>h2</h2><h3>h3</h3>
<
div style="text-align: right;">right<br></div>
<div style="text-align: center;">center<br>
</
div><ol><li>li</li></ol><ul><li>ul</li></ul>

I've just thought that reason may be in unclosed tags like <br>

Developer
Aug 29, 2009 at 12:33 AM

sway, can you try to run the following code:

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$htmlA = 'I am offering a used DSR-1500AP DVCAM recorder,
<br><br>fittet with all options:<br><br>DSBK-1505 Analog Input Board
<br><br>DSBK-1501 Digital IN/OUT Board with SDI
<br><br><br><br>Machine has only 750 hours on drum, in perfect cosmetic and mechanical condition - see attached pictures
<br><br>machine records miniDV, DVCAM and plays back all incl. DVCPro ';

$htmlB = '<span style="font-weight: bold;">bold</span><br>
<span style="font-style: italic;">italic</span><br>
<span style="text-decoration: line-through;">striked</span><br>
<span style="text-decoration: underline;">underlined</span><br>
<h1>h1</h1><h2>h2</h2><h3>h3</h3>
<div style="text-align: right;">right<br></div>
<div style="text-align: center;">center<br>
</div><ol><li>li</li></ol><ul><li>ul</li></ul>';

$sheet->getCell('A1')->setValue($htmlA);
$sheet->getCell('A2')->setValue($htmlB);

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');


It runs fine when I try? Look:

Aug 29, 2009 at 1:09 AM

I think I should make such test myself...

But problem remins on some set of records. Not all of them.

I've localized error inside 11 records.

Error is: Invalid xml sign

Developer
Aug 29, 2009 at 1:17 AM
  1. Are you retrieving data from MySQL?
  2. If yes, are you sure that data is UTF-8?
Aug 29, 2009 at 1:33 AM

1. from MySQL

2. Sure. That was the first thing I tried:

 

SET NAMES utf8
SET CHARACTER SET utf8

 

And I found those html with those invalid sign.

The winner is: 



I wish to know how this piece of unicode got into mysql...

Any ideas how to remove such signs?

Developer
Aug 29, 2009 at 1:54 AM

Ah,  I see. This is the control character 0x1A (SUB). Control characters are permitted in OOXML, but need to be represented in a special way.

PHPExcel is supposed to handle control characters so there must be a bug in PHPExcel_Writer_Excel2007. Need to do some more testing over the weekend and file a bug report. Will post back here.

Aug 29, 2009 at 1:59 AM

Thanks.

PS: first day working with lib and already found exception... I'm lucky =)

Developer
Aug 29, 2009 at 2:44 AM

Luckily, the fix was easy :) Look at patch at work item 10558:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10558

Or just wait for tomorrows source code release, and download from here:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx