PHPExcel Reader Wont return background Color

Topics: Developer Forum, User Forum
Mar 6, 2013 at 8:22 AM
OS:Window 7
PHP Version 5.4.7
PHPExcel_1.7.8
php-excel-reader-2.21


I am using PHPExcel reader to read .xls format, on the given example (example.xls) of course it works fine all the content of the excel file was there. And then I tried to pass all the attribute and content using jquery and json and resaving the file again, I look at the content using openoffice calc all the content and other attribute is still there even the background color, but upon reloading the webpage the background color has gone but there's no error., by the way I use PHPExcel 1.7.8 to re write it unto the file.

Anyone knows why's that happen?
another question:
is this the only option to merge cells " $objPHPExcel->getActiveSheet()->mergeCells('A1:A3') ?"
nothing like $objPHPExcel->getActiveSheet()->getStyle($content->tdid)->setRowspan(4); ??

Thanks.
Coordinator
Mar 6, 2013 at 8:46 AM
Edited Mar 6, 2013 at 8:48 AM
lilmer wrote:
I am using PHPExcel reader to read .xls format, on the given example (example.xls) of course it works fine all the content of the excel file was there. And then I tried to pass all the attribute and content using jquery and json and resaving the file again, I look at the content using openoffice calc all the content and other attribute is still there even the background color, but upon reloading the webpage the background color has gone but there's no error., by the way I use PHPExcel 1.7.8 to re write it unto the file.
Can you please provide some code to show what you are doing; I don't understand what json and jquery have to do with PHPExcel
is this the only option to merge cells " $objPHPExcel->getActiveSheet()->mergeCells('A1:A3') ?"
nothing like $objPHPExcel->getActiveSheet()->getStyle($content->tdid)->setRowspan(4); ??
mergeCells() is the only option to merge cells.... merged cells aren't a style feature, they're a structural feature, so using getStyle would be meaningless in the context of merging cells
Mar 6, 2013 at 8:58 AM
Edited Mar 6, 2013 at 9:04 AM
Ok. I just going think solutions to merge cells. . Thansk btw!

this is my code passing the content of the table from a web page using json and jquery:
  $('#sheet tbody tr').each(function(){
          var trid = $(this).attr('id');   

          $(this).find('td').each(function(){                  
          var tdcontent =  $(this).html(); //do whatever you want with the text
            var tdID =  $(this).attr('id'); 
            var tdStyle =  $(this).attr('style');
            var tdrowspan =  $(this).attr('rowspan');
            var tdcolspan =  $(this).attr('colspan');

            data[i] = {'tdid': tdID, 'content': tdcontent,'style':tdStyle , 'rowspan' : tdrowspan , 'colspan' : tdcolspan}; 

            newArray.push(data[i]);       

          });


      });

       $.ajax({
                type: "POST",
                url: "json.php",
                dataType: 'html',
                data: { json_1 : JSON.stringify(newArray), json_2 : JSON.stringify(newArray1) ,'key' : fileKey},
                success: function(response){                           

                          $('#output').html(response);
                  }
              }); 


Upon getting the value I do this:
  $jsonData1 = json_decode($jsonData_1);     
  foreach($jsonData1 as $row => $content) {  
  $worksheet->setCellValue($content->tdid,$content->content);    

  $style = explode(';', $content->style);  

  foreach($style as $att => $con){  

      $newvar = array();

      list($attr ,$value) = explode(':', $con);

      if($attr){
         // echo $content->tdid;
          $newvar[$attr] = $value;

          echo $content->tdid.$att.'<br>';

          foreach($newvar as $att => $theval){

              //Setting up the background color

              if($att == 'background-color' ){

                  if(($theval =='red') || ($theval =='FF0000') || ($theval =='#FF0000')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FFFF0000');
                  }    

                   if(($theval =='green') || ($theval =='008000') || ($theval =='#008000')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FF008000');
                  }

                  if(($theval =='blue') || ($theval =='0066CC') || ($theval =='#0066CC')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FF0000FF');
                  }

                  if(($theval =='yellow') || ($theval =='FFFF00') || ($theval =='#FFFF00')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FFFFFF00');
                  }                     


              }


          }

      }

  }

}


Okay. . again I tested the file. and I change the background color using openoffice calc and the PHPExcel reader wont retrieve the background color. . I think the problem is on the PHPExcel_reader
Mar 6, 2013 at 9:04 AM
Edited Mar 6, 2013 at 9:09 AM
q
Mar 6, 2013 at 9:09 AM
Ok. I just going think solutions to merge cells. . Thansk btw!

this is my code passing the content of the table from a web page using json and jquery:
  $('#sheet tbody tr').each(function(){
          var trid = $(this).attr('id');   

          $(this).find('td').each(function(){                  
          var tdcontent =  $(this).html(); //do whatever you want with the text
            var tdID =  $(this).attr('id'); 
            var tdStyle =  $(this).attr('style');
            var tdrowspan =  $(this).attr('rowspan');
            var tdcolspan =  $(this).attr('colspan');

            data[i] = {'tdid': tdID, 'content': tdcontent,'style':tdStyle , 'rowspan' : tdrowspan , 'colspan' : tdcolspan}; 

            newArray.push(data[i]);       

          });


      });

       $.ajax({
                type: "POST",
                url: "json.php",
                dataType: 'html',
                data: { json_1 : JSON.stringify(newArray), json_2 : JSON.stringify(newArray1) ,'key' : fileKey},
                success: function(response){                           

                          $('#output').html(response);
                  }
              }); 


Upon getting the value I'll do this:
  $jsonData1 = json_decode($jsonData_1);     
  foreach($jsonData1 as $row => $content) {  
  $worksheet->setCellValue($content->tdid,$content->content);    

  $style = explode(';', $content->style);  

  foreach($style as $att => $con){  

      $newvar = array();

      list($attr ,$value) = explode(':', $con);

      if($attr){
         // echo $content->tdid;
          $newvar[$attr] = $value;

          echo $content->tdid.$att.'<br>';

          foreach($newvar as $att => $theval){

              //Setting up the background color

              if($att == 'background-color' ){

                  if(($theval =='red') || ($theval =='FF0000') || ($theval =='#FF0000')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FFFF0000');
                  }    

                   if(($theval =='green') || ($theval =='008000') || ($theval =='#008000')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FF008000');
                  }

                  if(($theval =='blue') || ($theval =='0066CC') || ($theval =='#0066CC')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FF0000FF');
                  }

                  if(($theval =='yellow') || ($theval =='FFFF00') || ($theval =='#FFFF00')){
                     $worksheet->getStyle($content->tdid)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                     ->getStartColor()->setARGB('FFFFFF00');
                  }                     


              }


          }

      }

  }

}


Okay. . again I tested the file. and I change the background color using openoffice calc and the PHPExcel reader wont retrieve the background color