It's been known long ago that there is a class called Phpexcel (Official web site) can be used to operate Excel, has not had the opportunity to try, today, the trial found extremely powerful, download the source package has a detailed document, can almost achieve manual operation Excel can achieve all the functions.
A simple example of reading Excel is as follows:
Copy Code code as follows:
$inputFileType = ' Excel2007 ';
$inputFileName = './public/files/import_user_template.xlsx ';
$sheetname = ' Sheet1 ';
Specify the Excel type to create a reader
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
Set read-only data, excluding formulas and formatting
$objReader->setreaddataonly (TRUE);
Read only the specified sheet
$objReader->setloadsheetsonly ($sheetname);
$objPHPExcel = $objReader->load ($inputFileName);
$curSheet = $objPHPExcel->getsheet (0);
Maximum columns that contain data
$allColumn = $curSheet->gethighestcolumn ();
Maximum rows that contain data
$allRow = $curSheet->gethighestrow ();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow + +) {
for ($currentCol = ' A '; $currentCol <= $allColumn; $currentCol + +) {
echo $curSheet->getcell ($currentCol. $currentRow)->getvalue (). \ t ";
}
echo "\ r \ n";
}
To use in thinkphp, copy the classes directory in the source package to the vendor directory of thinkphp, rename Phpexcel, and then invoke the vendor method load
Copy Code code as follows:
Vendor (' Phpexcel.phpexcel ');
But then I found out that when I read Excel, I would call the M or D method to instantiate the model class. The error of the models class was found to be an automatic load mechanism conflict, which requires the use of the spl_autoload_ before the M or D method calls to resolve the conflict. Register function Re-register Autoloader class
Copy Code code as follows:
Spl_autoload_register (Array (' Do ', ' autoload '));
to invoke the Phpexcel problem solution in thinkphp
When you call Phpexcel in thinkphp, the data is fully readable, but there is an error when you next d,m or call the template. (I don't know if I'm the one who's having this problem?) )
After research, finally found a solution. Share it with you. Oh!
1, first download the Phpexcel package and place it under the thinkphp/vendor/(that is, the third party class Library Directory of the I-AM).
2, call the function.
Copy Code code as follows:
protected function Import_execl ($file) {
if (!file_exists ($file)) {
Return Array ("error" =>1);
}
Vendor ("Phpexcel.phpexcel");
$PHPExcel = new Phpexcel ();
$PHPReader = new phpexcel_reader_excel2007 ();
if (! $PHPReader->canread ($file)) {
$PHPReader = new Phpexcel_reader_excel5 ();
if (! $PHPReader->canread ($file)) {
Return Array ("error" =>2);
}
}
$PHPExcel = $PHPReader->load ($file);
$SheetCount = $PHPExcel->getsheetcount ();
for ($i =0; $i < $SheetCount; $i + +) {
$currentSheet = $PHPExcel->getsheet ($i);
$allColumn = $this->excelchange ($currentSheet->gethighestcolumn ());
$allRow = $currentSheet->gethighestrow ();
$array [$i] ["Title"] = $currentSheet->gettitle ();
$array [$i] ["Cols"] = $allColumn;
$array [$i] ["Rows"] = $allRow;
$arr = Array ();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow + +) {
$row = Array ();
for ($currentColumn =0; $currentColumn < $allColumn; $currentColumn + +) {
$row [$currentColumn] = $currentSheet->getcellbycolumnandrow ($currentColumn, $currentRow)->getvalue ();
}
$arr [$currentRow] = $row;
}
$array [$i] ["Content"] = $arr;
}
Spl_autoload_register (' Do ', ' autoload ');/must, or thinkphp and phpexcel will conflict.
Unset ($currentSheet);
Unset ($PHPReader);
Unset ($PHPExcel);
Unlink ($file);
Return Array ("Error" =>0, "data" => $array);
}
protected function Excelchange ($STR) {//in conjunction with EXECL Bulk Import functions
$len = strlen ($str)-1;
$num = 0;
for ($i = $len; $i >=0; $i-) {
$num + = (ord ($str [$i])-*pow ($len-$i);
}
return $num;
}
3, call.
Copy Code code as follows:
Public Function Import () {
if (Isset ($_files["import"]) && ($_files["Import" ["error"] = = 0)) {
$result = $this->import_execl ($_files["Import"] ["tmp_name"]);
if ($this->execl_error[$result ["Error"]] = = 0) {
$execl _data = $result ["Data"][0]["Content"];
Unset ($execl _data[1]);
$data = D ("Data");
foreach ($execl _data as $k => $v) {
$d ["serial_no"] = $v [0];
$d ["check_no"] = $v [1];
$d ["work_no"] = $v [2];
$d ["class_name"] = $v [3];
$d ["user_name"] = $v [4];
$d ["new_class"] = $v [5];
$d ["error_level"] = $v [6];
$data->data ($d)->add ();
}
$this->success ($this->execl_error[$result ["Error"]]);
}else{
$this->error ($this->execl_error[$result ["error"]]);
}
}else{
$this->error ("Upload file failed");
}
}
4, Error data:
Copy Code code as follows:
protected $Execl _error = Array ("Data import succeeded", "File not Found", "EXECL file format is incorrect");