phpchart.jsphpexcel

Chart.js image with php excel

What im trying to do is convert chart.js to image with toDataURL then past it using ajax and using the url to set image path for php excel. I want to past the data without using any button and my php excel show nothing

script

        var canvas = document.getElementById('chart1');
        var imgData = canvas.toDataURL('image/png', 1);

        $.ajax({
            type: "POST",
            url: "?f=excel_customer",
            data: { imgData: imgData },
            success: function(response) {
            console.log(response);
            }
        });

php excel

 if(isset($_POST['imgData'])) {
   
$image= $_POST['imgData'];
         
$objDrawing = new PHPExcel_Worksheet_Drawing();    //create object for Worksheet drawing
$objDrawing->setName('chart');                     //set name to image
$objDrawing->setDescription('chart');              //set description to image
$signature = 'chart1';                             //Path to signature .jpg file
$objDrawing->setPath($image);
$objDrawing->setOffsetX(15);                        
$objDrawing->setOffsetY(15);                        
$objDrawing->setCoordinates('B2');        
$objDrawing->setWidth(90);                 
$objDrawing->setHeight(90);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());  //save image
 }

i dont know if it can be achieve or not. if not can someone suggest we another way do this but still using phpexcel

Solution

  • In my experience, setPath does not DIRECTLY accept base64 image string. So one of the ways is to apply the following trick:

    1. save the chart1 data (base64 image string) into a temporarily file, e.g. xyz1234.png (where xyz1234 is a randomly generated string)
    2. execute the statement $objDrawing->setPath($image); where $image is "xyz1234.png"
    3. unlink (delete this temp file) after you have saved / generated the xlsx file
    4. Please note that I intentionally disabled the chart.js animation -- otherwise the ajax submitted graphics may have all the plots at the animation "start" positions, which in that case will not be the chart you want) - you should know what I mean if you are well versed in chart.js

    Note 1: For convenience, I have used the save_base64_image function as in the following post. to do (1) above, but of course you may use other similar functions if you wish

    https://stackoverflow.com/questions/15153776/convert-base64-string-to-an-image-file

    Note 2: Make sure the directory is writable (so as to save the temp file and generate the excel file)

    Note 3: I trust that you will understand the reason that I am using a randomized string for the png filename, it is a good habit to do this, in particular if your system will be used by multiple users.

    So the HTML is

    <script
  src="https://code.jquery.com/jquery-3.7.1.js"
  integrity="sha256-eKhayi8LEQwp4NKxN+CfCh+3qOVUtJn3QNZ0TciWLP4="
  crossorigin="anonymous"></script>

<script
src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js">
</script>

<canvas id="chart1" style="max-width:300px;max-height:300px;"></canvas>

<script>
var xyValues = [
  {x:50, y:7},
  {x:60, y:8},
  {x:70, y:8},
  {x:80, y:9},
  {x:90, y:9},
  {x:100, y:9},
  {x:110, y:10},
  {x:120, y:11},
  {x:130, y:14},
  {x:140, y:14},
  {x:150, y:15}
];

new Chart("chart1", {
  type: "scatter",
  data: {
    datasets: [{
      pointRadius: 4,
      pointBackgroundColor: "rgb(0,0,255)",
      data: xyValues
    }]
  },
  options: {
 animation: {
            duration: 0 // general animation time
        },
    legend: {display: false},
    scales: {
      xAxes: [{ticks: {min: 40, max:160}}],
      yAxes: [{ticks: {min: 6, max:16}}],
    }
  }
});
</script>


<script>
   var canvas = document.getElementById('chart1');
   var imgData = canvas.toDataURL('image/png', 1);
     //console.log(imgData);

        $.ajax({
            type: "POST",
            url: "testso.php",
            data: { imgData: imgData },
            success: function(response) {
             //  document.getElementById('ajaxreturn').innerHTML=response;
             alert(response); 
            }
        });

</script>

    and the php (testso.php) is

    <?php

require_once('./PHPExcel1.8/Classes/PHPExcel.php');
require_once('./PHPExcel1.8/Classes/PHPExcel/IOFactory.php');

function save_base64_image($base64_image_string, $output_file_without_extension, $path_with_end_slash="" ) {
    //usage:  if( substr( $img_src, 0, 5 ) === "data:" ) {  $filename=save_base64_image($base64_image_string, $output_file_without_extentnion, getcwd() . "/application/assets/pins/$user_id/"); }      
    //
    //data is like:    data:image/png;base64,asdfasdfasdf
    $splited = explode(',', substr( $base64_image_string , 5 ) , 2);
    $mime=$splited[0];
    $data=$splited[1];

    $mime_split_without_base64=explode(';', $mime,2);
    $mime_split=explode('/', $mime_split_without_base64[0],2);
    if(count($mime_split)==2)
    {
        $extension=$mime_split[1];
        if($extension=='jpeg')$extension='jpg';
        //if($extension=='javascript')$extension='js';
        //if($extension=='text')$extension='txt';
        $output_file_with_extension=$output_file_without_extension.'.'.$extension;
    }
    file_put_contents( $path_with_end_slash . $output_file_with_extension, base64_decode($data) );
    return $output_file_with_extension;
}


$image = $_POST['imgData'];

$x1=   chr(rand(0,25)+97) ;
$x2=   chr(rand(0,25)+97) ;
$x3=   chr(rand(0,25)+97) ;
$x4=   chr(rand(0,9)+48) ;
$x5=   chr(rand(0,9)+48) ;

$path=$x1.$x2.$x3.$x4.$x5;





save_base64_image($image, $path, $path_with_end_slash="" );


$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("creater");
$objPHPExcel->getProperties()->setLastModifiedBy("test2");
$objPHPExcel->getProperties()->setSubject("testSubject");

//$objWorkSheet = $objPHPExcel->createSheet();
//$objWorkSheet2 = $objPHPExcel->createSheet();


$objPHPExcel->getActiveSheet()->setTitle('test');



$objDrawing = new PHPExcel_Worksheet_Drawing();    //create object for Worksheet drawing
$objDrawing->setName('chart');                     //set name to image
$objDrawing->setDescription('chart');              //set description to image
$signature = 'chart1';                             //Path to signature .jpg file
$objDrawing->setPath($path.'.png');
$objDrawing->setOffsetX(15);                        
$objDrawing->setOffsetY(15);                        
$objDrawing->setCoordinates('B2');        
$objDrawing->setWidth(300);                 
$objDrawing->setHeight(300);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 




// for XLSX
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//header('Content-Disposition: attachment;filename="DOWNLOAD_test'.$ry.'.xlsx"');
//header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');


$name = './newexcel.xlsx';
$objWriter->save($name);


unlink($path.'.png'); 

echo "Successful" ;

     ?>

    Please find below the screen dumps of

    1. HTML

    enter image description here

    1. excel file generated

    enter image description here