CodeIgniter (CI) complete data import with Phpexcel class

1. Install Phpexcel to CodeIgniter

1) Unzip the contents of the Classes folder in the package into the Application\libraries\ directory, the directory structure is as follows:

–application\libraries\phpexcel.php

–application\libraries\phpexcel (folder)

2) Modify the application\libraries\phpexcel\iofactory.php file

– Change its class name from Phpexcel_iofactory to Iofactory and follow the CI class naming convention.

– Change its constructor to public (__construct)

2. After installation, write a controller that exports Excel

Public Function index () {


Determine the presence value of the uploaded file

if (!empty ($_files)) {

$filename = $_files[' file ' [' name '];//the names of the uploaded files
$filetype = $_files["File" ["type"];//the types of files being uploaded
$filesize = $_files["File" ["Size"];//of the uploaded file, in bytes
$filetmp = $_files["File" ["Tmp_name"];//the name of the temporary copy of the file stored on the server
$fileerror = $_files["File" ["Error"];//errors code caused by file upload


Determine if the upload was successful
if ($fileerror ==0) {
Determine if an Excel table
if ($filetype = = "Application/vnd.ms-excel" | | $filetype = = "application/ Vnd.openxmlformats-officedocument.spreadsheetml.sheet ") {

/* Set Save path */
$filePath = ' uploads/excel/';
$str = "";

/* Load phpexcel*/
$this->load->library (' phpexcel.php ');
$this->load->library (' phpexcel/iofactory.php ');
$this->load->library (' phpexcel/reader/excel5.php ');

Note Setting the time zone
$time =date ("Ymdhis");//go to the current time of uploading
Get the extension of the uploaded file
$extend =strrchr ($filename, '. ');
The file name after uploading
$name = $time. $extend;
The file name address after uploading
$uploadfile = $filePath. $name;//file name address after upload

The Move_uploaded_file () function moves the uploaded file to a new location. Returns true if successful, otherwise false is returned.
$result =move_uploaded_file ($filetmp, $uploadfile);//if uploaded to the current directory



echo $result;
If the upload file is successful, perform the import Excel operation

if ($result) {

$inputFileType = Iofactory::identify ($uploadfile);//Determine the format of the input file
$objReader = Iofactory::createreader ($inputFileType);//reader corresponding to the wearing piece
$objPHPExcel = $objReader->load ($uploadfile); Load the file to be read
$sheet = $objPHPExcel->getsheet (); Get Current Activity sheet
$highestRow = $sheet->gethighestrow (); Total number of rows obtained
$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
Print_r ($highestRow);
Print_r ($highestColumn);
/* First method

Loop through the Excel file, read a line, insert a
for ($j =1; $j <= $highestRow; $j + +)//Read data starting from the first line
{
for ($k = ' a '; $k <= $highestColumn; $k + +)//Read data from column A
{
//
This method is simple, but inappropriate, with ' \ \ ' merged into the array, then split \ \ for the field value inserted into the database
Measured in Excel, if the value of a cell contains \ \ Import the data is empty
//
$str. = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue (). ' \ \ ';//Read cells
}
Echo $str; Die ();
Explode: function splits a string into arrays.
$strs = explode ("\ \", $STR);
Print_r ($strs);
$sql = "INSERT into te (' id ', ' name ') VALUES (
' {$strs [0]} ',
' {$strs [1]} ');
Die ($sql);

if (!mysql_query ($sql))
{
return false;
echo ' SQL statement error ';
}
mysql_query ($sql);
$str = "";
}
Unlink ($uploadfile); Delete an uploaded Excel file
$msg = "Import succeeded! ";
*/

/* The second method */
$objWorksheet = $objPHPExcel->getactivesheet ();
$highestRow = $objWorksheet->gethighestrow ();

$highestColumn = $objWorksheet->gethighestcolumn ();
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn);//Total number of columns

$headtitle =array ();
for ($row = 2; $row <= $highestRow; $row + +) {

$arr =array ();
Note the number of columns in the Highestcolumnindex index starts at 0
for ($col = 0; $col < $highestColumnIndex; $col + +) {


$arr [$col] = $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue ();
}


$sql = "INSERT into admins (' account ', ' pwd ', ' username ', ' power ', ' tel ', ' sex ', ' work_numjob ', ' job ', ' sector ') VALUES (
' {$strs [0]} ',
' {$strs [1]} ',
' {$strs [2]} ',
' {$strs [3]} ',
' {$strs [4]} ',
' {$strs [5]} ',
' {$strs [6]} ',
' {$strs [7]} ',
' {$strs [8]} ',) ';

$data =array (
' Account ' = $arr [' 0 '],
' pwd ' = $arr [' 1 '],
' Username ' = $arr [' 2 '],
' Power ' = $arr [' 3 '],
' Tel ' = $arr [' 4 '],
' Sex ' = $arr [' 5 '],
' Work_num ' = $arr [' 6 '],
' Job ' = $arr [' 7 '],
' Sector ' = $arr [' 8 '],

);

$this->db->insert ("admins", $data);

}
Unlink ($uploadfile);
Show_msg ("Import Successful", Site_url ("link Address"));//Jump Address
Delete an uploaded Excel file

}

}else{

Show_msg ("Upload file non-CVS format, please re-upload");
}


}else{

Switch ($fileerror) {
Case 1:
Show_msg ("The uploaded file exceeds the value of the Upload_max_filesize option limit in php.ini.");
Break
Case 2:
Show_msg ("The size of the uploaded file exceeds the value specified by the Max_file_size option in the HTML form");
Break
Case 3:
Show_msg ("Files are only partially uploaded");
Break
Case 4:
Show_msg ("No files are uploaded");
Break
}


}

}

}

Precautions : the way to read Excel can't specify dead, let him automatically recognize files to automatically read.

3. Read Excel details

1. The simplest way to import an Excel is to use the Phpexel IO Factory, which calls the static load of the Phpexcel_iofactory class, which automatically identifies the document format, including Excel2007, Excel2003xml, Oocalcsylk, Gnumeric, CSV. Returns an instance of a phpexcel.

Load Factory class

Include ' phpexcel/iofactory.php ';
The XLS file path to read

$inputFileName = './sampledata/example1.xls ';

/** uses the Phpexcel_iofactory load method to get the Excel object **/
$objPHPExcel = Phpexcel_iofactory::load ($inputFileName);

Get the current active table, call the ToArray method, get the two-dimensional array of the table

$sheetData = $objPHPExcel->getactivesheet ()->toarray (null,true,true,true);

Var_dump ($sheetData);

1. Create a excelreader to load an Excel document

If you know the format of this Excel document, you can create a corresponding reader to load the Excel document you want to read. But if you load the wrong document type, you can produce unpredictable errors.

$inputFileName = './sampledata/example1.xls ';

/** Create a new Excel5 Reader **/
$objReader = new Phpexcel_reader_excel5 ();
$objReader = new phpexcel_reader_excel2007 ();
$objReader = new Phpexcel_reader_excel2003xml ();
$objReader = new Phpexcel_reader_oocalc ();
$objReader = new Phpexcel_reader_sylk ();
$objReader = new Phpexcel_reader_gnumeric ();
$objReader = new Phpexcel_reader_csv ();
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);

Get Current Activity sheet

$curSheet = $objPHPExcel->getactivesheet ();

Returns the table's data as a two-dimensional array

$sheetData = $curSheet->toarray (null,true,true,true);

Var_dump ($sheetData);

You can also use the Phpexcel_iofactory Createreader method to get a reader object without needing to know the format of the file to be read.

$inputFileType = ' Excel5 ';
$inputFileType = ' Excel2007 ';
$inputFileType = ' excel2003xml ';
$inputFileType = ' Oocalc ';
$inputFileType = ' SYLK ';
$inputFileType = ' Gnumeric ';
$inputFileType = ' CSV ';
$inputFileName = './sampledata/example1.xls ';

/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);

Get Current Activity sheet

$curSheet = $objPHPExcel->getactivesheet ();

Returns the table's data as a two-dimensional array

$sheetData = $curSheet->toarray (null,true,true,true);

Var_dump ($sheetData);

If the file format is unknown before reading the file, you can get the file type by Iofactory's identify () method and then go through the reader via the Createreader () method.

$inputFileName = './sampledata/example1.xls ';

/** determine the format of the input file **/
$inputFileType = Phpexcel_iofactory::identify ($inputFileName);
/** reader **/for wearing pieces
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** load the file to be read **/
$objPHPExcel = $objReader->load ($inputFileName);

2. Set reading options for Excel

Before loading a file using the load () method, you can set the Read option to control the behavior of load.

2.1.ReadingOnly Data from a Spreadsheet File

The Setreaddataonly () method, which configures the reader to not care about the data type of the tabular data, is returned in string format

$inputFileType = ' Excel5 ';
$inputFileName = './sampledata/example1.xls ';

/** Create A new Reader of the type defined in $inputFileType **/
$objReader = Phpexcel_iofactory::createreader ($inputFileType);
/** the configuration cell data is returned as a string **/
$objReader->setreaddataonly (TRUE);
/** Load $inputFileName to a phpexcel Object **/
$objPHPExcel = $objReader->load ($inputFileName);

$sheetData = $objPHPExcel->getactivesheet ()->toarray (null,true,true,true);

Var_dump ($sheetData);

Detailed information: http://blog.csdn.net/andy1219111/article/details/7673796;

