PHPExcel to bold text in between HTML markups and

Question

HI folks ive been trying to figure out a problem that's been haunting me for some time now and original started this thread thread PHPExcel - How to replace text using preg_replace. I was going about this all the wrong way and Mark gave me some tips on where to start. I gave up this project as I was very clueless of how to even start a script for this.

What I am currently attempting is to bold text that are within HTML marker <B> and <Strong>. After spending some time studying Jquery and some javascript to help me with this. I am understanding that ill nee to write a script to find text with HTML markups <b></b> and <strong></strong>,and tell the javascript to bold that text using the following method:

$objRichText = new PHPExcel_RichText();
$objRichText->createText('This text is ');
$objBoldTextRun = $objRichText->createTextRun('bold');
$objBoldTextRun->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getCell('B1')->setValue($objRichText);

I would imagine that with Jquery I would use something like the event .find but not quite sure what the best way to approach something like this. This is honestly way beyond my level of comprehension but willing to give it a shot with some help.

Update updated code:

<?php
/** Error reporting */

/** PHPExcel */
require_once '../js/PHPExcel/Classes/PHPExcel.php';
// Set active sheet index to the first sheet, so Excel opens this as the first sheet

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$rows=2;
$sheet=$objPHPExcel->getActiveSheet();
$wizard = new PHPExcel_Helper_HTML();
//Font Setting for the Support group title.
$Support_team = array('font'=> array('bold'=> true,'color' => array('rgb' => '4D4D4D'),'size'  => 22,'name'  => 'Arial'),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),);
//Font settings for the header cells only.
$headers = array('font'=> array('bold'=> true,'color' => array('rgb' => '4D4D4D'),'size'  => 12,'name'  => 'Arial'),'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),);
//Border settings
$borders = array('borders' => array('inside'=> array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('argb' => '717171')),'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('argb' => '717171'))));

//  SQl database connections
$db = mysql_connect("localhost", "IMC_Admin", "IMCisgreat2014");
    mysql_select_db("imc_directory_tool",$db);
    $sql="select client,team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note from tbl_address ORDER BY team_name";
    $result=mysql_query($sql);
        $numrows=mysql_num_rows($result);
        if ($numrows>0)
        {
            while($data=mysql_fetch_array($result))
            {
//Cell Wraptext
$sheet->getStyle('C'.($rows+1).':D'.($rows+1))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+3).':D'.($rows+3))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+4).':D'.($rows+4))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+6).':D'.($rows+6))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+7).':D'.($rows+7))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+8).':D'.($rows+8))->getAlignment()->setWrapText(true);
$sheet->getStyle('C'.($rows+9).':D'.($rows+9))->getAlignment()->setWrapText(true);
$sheet->getStyle('B'.($rows+11).':D'.($rows+11))->getAlignment()->setWrapText(true);


$richTextClient = $wizard->toRichTextObject($data['client']);
$richTextteam_name = $wizard->toRichTextObject($data['team_name']);
$richTextsupport_team_prime = $wizard->toRichTextObject($data['support_team_prime']);
$richTextprime_comments = $wizard->toRichTextObject($data['prime_comments']);
$richTextsupport_team_backup = $wizard->toRichTextObject($data['support_team_backup']);
$richTextbackup_comments = $wizard->toRichTextObject($data['backup_comments']);
$richTextescalation1 = $wizard->toRichTextObject($data['escalation1']);
$richTextescalation1_comments = $wizard->toRichTextObject($data['escalation1_comments']);
$richTextescalation2 = $wizard->toRichTextObject($data['escalation2']);
$richTextescalation2_comments = $wizard->toRichTextObject($data['escalation2_comments']);
$richTextescalation3 = $wizard->toRichTextObject($data['escalation3']);
$richTextescalation3_comments = $wizard->toRichTextObject($data['escalation3_comments']);
$richTextescalation4 = $wizard->toRichTextObject($data['escalation4']);
$richTextescalation4_comments = $wizard->toRichTextObject($data['escalation4_comments']);
$richTextNote = $wizard->toRichTextObject($data['note']);

//This section is the actual data imported from the SQL database *don't touch*
$objPHPExcel->setActiveSheetIndex(0)              
->setCellValue('C'.($rows+1),$richTextClient) //this will give cell C2. 
->setCellValue('B'.$rows,$richTextteam_name) // this will give cell B2 
->setCellValue('C'.($rows+3),$richTextsupport_team_prime) //this will give C5 
->setCellValue('D'.($rows+3),$richTextprime_comments)  // This will give D5
->setCellValue('C'.($rows+4),$richTextsupport_team_backup)  //This will give C6
->setCellValue('D'.($rows+4),$richTextbackup_comments) //This will give D6  
->setCellValue('C'.($rows+6),$richTextescalation1)//THis will give you C8
->setCellValue('D'.($rows+6),$richTextescalation1_comments)//This will give you D8
->setCellValue('C'.($rows+7),$richTextescalation2)//This will give you C9
->setCellValue('D'.($rows+7),$richTextescalation2_comments)//This will give you D9
->setCellValue('C'.($rows+8),$richTextescalation3)//This will give you C10
->setCellValue('D'.($rows+8),$richTextescalation3_comments)//This will give you D10
->setCellValue('C'.($rows+9),$richTextescalation4)//This will give you C11
->setCellValue('D'.($rows+9),$richTextescalation4_comments)//This will give you D11
->setCellValue('B'.($rows+11),$richTextNote); //This will give you B13  

//Cell Merging 
$sheet
->mergeCells('B'.$rows.':D'.$rows)  
->mergeCells('B'.($rows+2).':D'.($rows+2))  
->mergeCells('B'.($rows+5).':D'.($rows+5))  
->mergeCells('B'.($rows+10).':D'.($rows+10))  
->mergeCells('C'.($rows+1).':D'.($rows+1))
->mergeCells('B'.($rows+11).':D'.($rows+11)); 

// Add some data  
$objPHPExcel->setActiveSheetIndex(0)  
->setCellValue('B'.($rows+1), 'Client:')
->setCellValue('B'.($rows+2), 'Support group contacts')     
->setCellValue('B'.($rows+3), 'Prime:')
->setCellValue('B'.($rows+4), 'Backup:')
->setCellValue('B'.($rows+5), 'Escalations') 
->setCellValue('B'.($rows+6), 'Escalation 1:') 
->setCellValue('B'.($rows+7), 'Escalation 2:') 
->setCellValue('B'.($rows+8), 'Escalation 3:') 
->setCellValue('B'.($rows+9), 'Escalation 4:') 
->setCellValue('B'.($rows+10), 'Notes'); 

//Format the hardcoded text 
$sheet->getStyle('B'.$rows)->applyFromArray($Support_team);
$sheet->getStyle('B'.($rows+2))->applyFromArray($headers);      
$sheet->getStyle('B'.($rows+5))->applyFromArray($headers);  
$sheet->getStyle('B'.($rows+10))->applyFromArray($headers); 

//Row height adjustments
$sheet->getRowDimension($rows+3)->setRowHeight(60);
$sheet->getRowDimension($rows+4)->setRowHeight(60);
$sheet->getRowDimension($rows+6)->setRowHeight(60);
$sheet->getRowDimension($rows+7)->setRowHeight(60);
$sheet->getRowDimension($rows+8)->setRowHeight(60);
$sheet->getRowDimension($rows+9)->setRowHeight(60);
$sheet->getRowDimension($rows+11)->setRowHeight(100);   


//Background color on cells 
$sheet->getStyle('B'.$rows.':D'.$rows)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$sheet->getStyle('B'.($rows+2).':D'.($rows+2))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$sheet->getStyle('B'.($rows+5).':D'.($rows+5))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$sheet->getStyle('B'.($rows+10).':D'.($rows+10))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF9BC2E6');
$sheet->getStyle('B'.($rows+1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+3))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+4))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+6))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+7))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+8))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');
$sheet->getStyle('B'.($rows+9))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE6F1FA');         

//Border range  
$sheet->getStyle('B'.$rows.':D'.($rows+11))->applyFromArray($borders);  
$rows+=14;
}
}

//This is the hard coded *non dynamic* cell formatting
    $sheet->getColumnDimension('A')->setWidth(5);
    $sheet->getColumnDimension('B')->setWidth(15);
    $sheet->getColumnDimension('C')->setWidth(50);
    $sheet->getColumnDimension('D')->setWidth(50);
    $sheet->getSheetView()->setZoomScale(90);
    $sheet->getStyle('A:D') ->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// Rename sheet  
$sheet->setTitle('Directory Tool Full dump');

// 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 (Excel2007) 
$today=date("F.d.Y");
$filename = "Directory_Export-$today.xlsx";
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=$filename");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');   
$objWriter->save('php://output');  
exit;  
?>

Answer 1

If you're using the latest develop branch from the PHPExcel github repo, there is actually an html to rich text helper class:

The file 42richText.php in the Examples folder demonstrates its use:

$html1='<font color="#0000ff">
<h1 align="center">My very first example of rich text<br />generated from html markup</h1>
<p>
<font size="14" COLOR="rgb(0,255,128)">
<b>This block</b> contains an <i>italicized</i> word;
while this block uses an <u>underline</u>.
</font>
</p>
<p align="right"><font size="9" color="red">
I want to eat <ins><del>healthy food</del><strong>pizza</strong></ins>.
</font>
';

$html2='<p>
<font color="#ff0000">
    100&deg;C is a hot temperature
</font>
<br>
<font color="#0080ff">
    10&deg;F is cold
</font>
</p>';

$html3='2<sup>3</sup> equals 8';

$html4='H<sub>2</sub>SO<sub>4</sub> is the chemical formula for Sulphuric acid';


$wizard = new PHPExcel_Helper_HTML;
$richText = $wizard->toRichTextObject($html1);

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', $richText);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(48);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getAlignment()
    ->setWrapText(true);

$richText = $wizard->toRichTextObject($html2);

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A2', $richText);

$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A2')
    ->getAlignment()
    ->setWrapText(true);

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', $wizard->toRichTextObject($html3));

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A4', $wizard->toRichTextObject($html4));