Copy of RichText and difference in Richtext entries

Topics: User Forum
Mar 6, 2009 at 3:37 PM
Edited Mar 6, 2009 at 3:38 PM
Hi

I would like to copy a rich text from one Excel document to a document I’m writing.

I have tried with getValue() and then insert the field to the other spread sheet with setValue()? Bu I only get the plain value. If I test with instanceof PHPExcel_RichText, I get the object/string that I’m inserting to a RichText. Is there another way to use the setValue and inform that this is a RichText.

Is there a way to see if two RichText objects differes ie. boldness or text strings.   

 

Thanks

Daniel


Developer
Mar 6, 2009 at 11:53 PM
Edited Mar 6, 2009 at 11:53 PM
The reason why you are getting plain text could be that PHPExcel_Writer_Excel5 does not yet support Rich-Text. Which writer are you using?

To compare two Rich-Text objects you could try to compare their hash codes

if ($richText1->getHashCode() == $richText2->getHashCode()) {
  echo 'Rich-Text strings are similar';
}

Mar 7, 2009 at 6:07 AM
I'm using Excel2007 both from the document that I'm reading and writing to.
Developer
Mar 8, 2009 at 11:52 PM
I couldn't replicate the behavior you're mentioning with getting just plain text?

The straightforward method seems to be working for me:

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel0 = $reader->load('old.xlsx');
$value = $excel0->getSheet(0)->getCell('A1')->getValue();

$excel1 = new PHPExcel();
$excel1->getSheet(0)->getCell('B5')->setValue($value);

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

Mar 9, 2009 at 10:04 PM

The hashcode comparison works fine.
The problem I have is that Excel complains and says that /xl/sharedStrings.xml was repaired.
Is this error because I'm missing a field somewhere.

/daniel 
Developer
Mar 10, 2009 at 3:02 AM
I think you may need to show us some of your code to get this solved. There could be a number of reasons why you are getting that error.

Mar 10, 2009 at 10:38 PM
Hi Koyama,

I have extracted the places where I'm generating the code, this still create the problem.  I hope you are able to find the problem.

/daniel

<?php

$dirname= '../../testdata/test2';
 $mapDiff= new MappingDifferance($dirname    );
     $mapDiff->run();
/**
 * Writes the difference between two mappings.
 */
 
class MappingDifferance{
    
    /**
     * @var documenter variable for create documentation
     */
    private $documenter;
    const STARTLINE=10;
    
        /**
     * Directory for where the files are stored
     *
     * @var String
     */
    private $directory;
    
        public function __construct($dir){
            echo "MappingDifferance called ";
        set_include_path(get_include_path() . PATH_SEPARATOR . '../../lib/');
        /** PHPExcel */
        include_once("PHPExcel.php") ;
        

        /** PHPExcel_IOFactory */
        include_once 'PHPExcel/IOFactory.php' ;
        
        $this->directory = $dir;



    }
    public function run(){
        echo "running \n";
      list( $oldMap, $oldPath) = $this->prepareFile("old");

    $paths = $oldPath;
    
    $objPHPExcel = new PHPExcel();


        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
                $oldMapping;
        for( $i = 0;$i< count($paths); $i++ ) {
            $path = $paths[$i];
        
             $index = $i+ self::STARTLINE;
             $objPHPExcel->getActiveSheet()->setCellValue('A'.$index, $path);
    
            if(array_key_exists($path,$oldMap)){
                 $oldMapping= $oldMap[$path];
            }
            else{
                 $oldMapping="";
            }
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$index, $oldMapping );
            
            
            //format current line
            $objPHPExcel->getActiveSheet()->getStyle('A'.$index)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

            $objPHPExcel->getActiveSheet()->getStyle('B'.$index)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('B'.$index)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

            
         }
        
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($this->directory .'/diff.xlsx');
    
    }
    
    
   private    function prepareFile($prefix){
       
      $pathArray = array();
      $mapArray    = array();

        $objReaderOld = PHPExcel_IOFactory::createReader('Excel2007');
    
            $objExcelReaderOld = $objReaderOld->load($this->directory."/$prefix.xlsx" );
            $objExcelReaderOld->setActiveSheetIndex(0);

            for($i=10;$i<  $objExcelReaderOld->getActiveSheet()->getHighestRow( ); $i++){
                $cell = $objExcelReaderOld->getActiveSheet()->getCell("B".$i);
                $path = ($cell->getValue() instanceof PHPExcel_RichText) ?  $cell->getValue()->getPlainText() : $cell->getValue();
                $mapArray[ $path] = $objExcelReaderOld->getActiveSheet()->getCell("C".$i)->getValue();
                $pathArray[] =$path;
            }
      return  array($mapArray,$pathArray);
   }
}
    
?>
Developer
Mar 11, 2009 at 11:23 PM
If I understand your code correctly, then you're extracting values from your old spreadsheet's column C and using those for the new one. So there are Rich-Text strings in column C?

It would be helpful if you could check where the Rich-Text is lost. For example by using:

var_dump($oldMapping);

in your run() function. Can you try that?

Mar 12, 2009 at 2:58 PM
Hi,

The var_dump command returnd a quite large file with references to the excel document. The first element looked like this but the full dump was  80 Mb when I stoped the execution.
/daniel 
object(PHPExcel_RichText)#723 (3) {
  ["_richTextElements:private"]=>
  array(1) {
    [0]=>
    object(PHPExcel_RichText_Run)#27 (2) {
      ["_font:private"]=>
      object(PHPExcel_Style_Font)#28 (12) {
        ["_name:private"]=>
        string(7) "Calibri"
        ["_bold:private"]=>
        bool(false)
        ["_italic:private"]=>
        bool(false)
        ["_superScript:private"]=>
        bool(false)
        ["_subScript:private"]=>
        bool(false)
        ["_underline:private"]=>
        string(4) "none"
        ["_striketrough:private"]=>
        bool(false)
        ["_color:private"]=>
        object(PHPExcel_Style_Color)#29 (2) {
          ["_argb:private"]=>
          string(8) "FF000000"
          ["_hashIndex:private"]=>
          NULL
        }
        ["_parent:private"]=>
        NULL
        ["_parentPropertyName:private"]=>
        NULL
        ["_hashIndex:private"]=>
        NULL
        ["_size"]=>
        int(10)
      }
      ["_text:private"]=>
      string(42) "/ns0:Messages/ns0:Message1/ns1:PackingList"
    }
  }
  ["_parent:private"]=>
  object(PHPExcel_Cell)#741 (8) {
    ["_column:private"]=>
    string(1) "C"
    ["_row:private"]=>
    string(2) "10"
    ["_value:private"]=>
    object(PHPExcel_RichText)#723 (3) {
      ["_richTextElements:private"]=>
      array(1) {
        [0]=>
        object(PHPExcel_RichText_Run)#27 (2) {
          ["_font:private"]=>
          object(PHPExcel_Style_Font)#28 (12) {
            ["_name:private"]=>
            string(7) "Calibri"
            ["_bold:private"]=>
            bool(false)
            ["_italic:private"]=>
            bool(false)
            ["_superScript:private"]=>
            bool(false)
            ["_subScript:private"]=>
            bool(false)
            ["_underline:private"]=>
            string(4) "none"
            ["_striketrough:private"]=>

Developer
Mar 13, 2009 at 1:02 PM
This is strange, I tried your script as is, and it seems to be working as expected.

The original cells from column C go into the new column B and survive as Rich-Text.

The original cells from column B go into the new column A and are converted to plain text because you have this:

$path = ($cell->getValue() instanceof PHPExcel_RichText) ?  $cell->getValue()->getPlainText() : $cell->getValue();

Not sure if you were expecting Rich-Text here?

Mar 15, 2009 at 8:50 PM
Hi Koyama,

Thanks for the help, i appreciate it.
No it works as expected the B column should be a plain text one.

I manage to get it solved. I first generatede the document with one script and the opened the documents again with this script. When I instead just read the document from memory it worked without any problems.


/daniel
Oct 8, 2009 at 4:54 PM

Hi,

I am a new guy to the PHPExcel, the one of the best excel report generator, I ever seen.

But I have faced one problem .

The problem is:-

I have some data in MySql database in rich text format like -  <b>test test test</b><b>test test test</b><b>test test test</b>

But when I want them to write in an excel file through the PHPExcel API , the rich text format is not rendered properly as in the HTML format.

Please give me an example or any suggesstions about how to write the excel which will be rendered from the MySql database containing the

rich text data properly.

I am really unable to do this.

Please help

Thanks

Sanjoy

Developer
Oct 9, 2009 at 11:35 AM

@sanjoyde: Sorry, but we do not have a solution for inserting Rich-Text as HTML. MS Office Excel is capable of reading HTML of the type you are showing so maybe it will be implemented one day in PHPExcel. But right now there is no battle plan.