Home>

Import Data:

<?php
error_reporting (e_all);//Enable error
set_time_limit (0);//Script does not time out
date_default_timezone_set ("europe/london");//Set the time
/** include path ** /
/** phpexcel_iofactory * /
include "phpexcel/iofactory.php";
//$inputfiletype="excel5";//This is read from xls
 $inputfiletype="excel2007";//This is calculated by xlsx
//$inputfilename="./sampledata/example2.xls";
$inputfilename="./sampledata/book.xlsx";
  echo "loading file", pathinfo ($inputfilename, pathinfo_basename), "using iofactory with a defined reader type of", $inputfiletype, "<br />";
  $objreader=phpexcel_iofactory ::createreader ($inputfiletype);
  $objphpexcel=$objreader->load ($inputfilename);
  /*
  $sheet=$objphpexcel->getsheet (0);
  $highestrow=$sheet->gethighestrow ();//Get the total number of rows
  $highestcolumn=$sheet->gethighestcolumn ();//Get the total column
  * /
  $objworksheet=$objphpexcel->getactivesheet ();//Get the total number of rows
  $highestrow=$objworksheet->gethighestrow ();//Get the total number of columns
  echo "highestrow =". $highestrow;
  echo "<br>";
  $highestcolumn=$objworksheet->gethighestcolumn ();
  $highestcolumnindex=phpexcel_cell ::columnindexfromstring ($highestcolumn);//Total number of columns
  echo "highestcolumnindex =". $highestcolumnindex;
  echo "<br />";
  $headtitle=array ();
  for ($row=1;$row<= $highestrow;$row ++)
  {
   $strs=array ();
   //Note that the column index of highestcolumnindex starts from 0
   for ($col=0;$col<$highestcolumnindex;$col ++)
   {
    $strs [$col]=$objworksheet->getcellbycolumnandrow ($col, $row)->getvalue ();
   }
    $info=array (
     "word1" =>"$strs [0]",     "word2" =>"$strs [1]",     "word3" =>"$strs [2]",     "word4" =>"$strs [3]",    );
    //here you can connect,Your database,Written to the database
    print_r ($info);
    echo "<br />";
  }
?>

export data:

(If there is a special string=trouble str_replace (array ("="), "", $val ["rolename"]););

private function _export_data ($data=array ())
{
 error_reporting (e_all);//Enable error
 set_time_limit (0);//Script does not time out
 date_default_timezone_set ("europe/london");//Set the time
 /** include path ** /
 set_include_path (fcpath.apppath. "/libraries/classes /");//Set environment variables
 //create new phpexcel object
 include "phpexcel.php";
 $objphpexcel=new phpexcel ();
 //set document properties
 $objphpexcel->getproperties ()->setcreator ("maarten balliauw")
    ->Setlastmodifiedby ("maarten balliauw")
    ->Settitle ("office 2007 xlsx test document")
    ->Setsubject ("office 2007 xlsx test document")
    ->Setdescription ("test document for office 2007 xlsx, generated using php classes.")
    ->Setkeywords ("office 2007 openxml php")
    ->Setcategory ("test result file");
 //add some data
 $letter=array ("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", " l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x " , "y", "z");
 if ($data) {
  $i=1;
  foreach ($data as $key =>$value) {
  $newobj=$objphpexcel->setactivesheetindex (0);
  $j=0;
  foreach ($value as $k =>$val) {
   $index=$letter [$j]. "$i";
   $objphpexcel->setactivesheetindex (0)->setcellvalue ($index, $val);
   $j ++;
  }
   $i ++;
  }
 }
 $date=date ("y-m-d", time ());
 //rename worksheet
 $objphpexcel->getactivesheet ()->settitle ($date);
 $objphpexcel->setactivesheetindex (0);
 //redirect output to a client "s web browser (excel2007)
 header ("content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 header ("content-disposition:attachment;filename =" ". $date.". xlsx "");
 header ("cache-control:max-age=0");
 $objwriter=phpexcel_iofactory ::createwriter ($objphpexcel, "excel2007");
 $objwriter->save ("php://output");
 exit;
}

Directly on the code:

public function export_data ($data=array ())
{
 #code ...
 include_once (app_path. "tools/phpexcel/classes/phpexcel/writer/iwriter.php");
 include_once (app_path. "tools/phpexcel/classes/phpexcel/writer/excel5.php");
 include_once (app_path. "tools/phpexcel/classes/phpexcel.php");
 include_once (app_path. "tools/phpexcel/classes/phpexcel/iofactory.php");
 $obj_phpexcel=new phpexcel ();
 $obj_phpexcel->getactivesheet ()->setcellvalue ("a1", "key");
 $obj_phpexcel->getactivesheet ()->setcellvalue ("b1", "value");
 if ($data) {
  $i=2;
  foreach ($data as $key =>$value) {
  #code ...
  $obj_phpexcel->getactivesheet ()->setcellvalue ("a". $i, $value);
  $i ++;
  }
 }
 $obj_writer=phpexcel_iofactory ::createwriter ($obj_phpexcel, "excel5");
 $filename="outexcel.xls";
 header ("content-type:application/force-download");
 header ("content-type:application/octet-stream");
 header ("content-type:application/download");
 header ("content-disposition:inline;filename =" ". $filename." "");
 header ("content-transfer-encoding:binary");
 header ("last-modified:". gmdate ("d, d m y h:i:s"). "gmt");
 header ("cache-control:must-revalidate, post-check=0, pre-check=0");
 header ("pragma:no-cache");
 $obj_writer->save ("php://output");
}
php
  • Previous The difference and connection between JavaScript and HTML DOM and the relationship between Javascript and DOM
  • Next Analysis of JS using eval to parse JSON