Codeigniter (CI) combined with PHPExcel class for data import

Codeigniter (CI) combined with PHPExcel class to complete data import 1. install PHPExcel to Codeigniter

1) decompress the contents in the Classes folder of the compressed package to 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 the class name from PHPExcel_IOFactory to IOFactory and follow the CI class naming rules.

-Change its constructor to public (_ construct)

2. after the installation is complete, write a Controller for exporting the excel file)

Public function index (){


// Determine the existence value of the uploaded file

If (! Empty ($ _ FILES )){

$ Filename = $ _ FILES ['file'] ['name']; // name of the uploaded file
$ Filetype = $ _ FILES ["file"] ["type"]; // type of the file to be uploaded
$ Filesize = $ _ FILES ["file"] ["size"]; // size of the uploaded file, in bytes
$ Filetmp = $ _ FILES ["file"] ["tmp_name"]; // name of the temporary copy of the file stored on the server
$ Fileerror = $ _ FILES ["file"] ["error"]; // error code caused by file upload


// Determine whether the upload is successful
If ($ fileerror = 0 ){
// Determine whether an excel table is used
If ($ filetype = "application/vnd. ms-excel" ||| filetype = "application/vnd. openxmlformats-officedocument.spreadsheetml.sheet "){

/* Set the 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 ');

// Set the time zone.
$ Time = date ("YmdHis"); // The current Upload time
// Obtain the extension of the uploaded file
$ Extend = strrchr ($ filename ,'.');
// Name of the uploaded file
$ Name = $ time. $ extend;
// Address of the uploaded file name
$ Uploadfile = $ filePath. $ name; // address of the uploaded file name

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



// Echo $ result;
// If the file is successfully uploaded, execute the excel import operation.

If ($ result ){

$ InputFileType = IOFactory: identify ($ uploadfile); // you can specify the format of the input file.
$ ObjReader = IOFactory: createReader ($ inputFileType); // reader corresponding to the wear
$ ObjPHPExcel = $ objReader-> load ($ uploadfile); // load the file to be read
$ Sheet = $ objPHPExcel-> getSheet (); // get the current active sheet
$ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns
// Print_r ($ highestRow );
// Print_r ($ highestColumn );
/* Method 1

// Read the excel file cyclically, read one, and insert one
For ($ j = 1; $ j <= $ highestRow; $ j ++) // read data from the first row
{
For ($ k = 'a'; $ k <= $ highestColumn; $ k ++) // read data from column
{
//
// This method is simple, but there is something wrong with it. merge it with '\' as an array and insert the \ field value into the database.
// In excel, if the value of a cell contains \ imported data
//
$ Str. = $ objPHPExcel-> getActiveSheet ()-> getCell ("$ k $ j")-> getValue (). '\'; // read a cell
}
// Echo $ str; die ();
// Explode: The function separates strings 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 successful! ";
*/

/* Method 2 */
$ ObjWorksheet = $ objPHPExcel-> getActiveSheet ();
$ HighestRow = $ objWorksheet-> getHighestRow ();

$ HighestColumn = $ objWorksheet-> getHighestColumn ();
$ HighestColumnIndex = PHPExcel_Cell: columnIndexFromString ($ highestColumn); // The total number of columns

$ Headtitle = array ();
For ($ row = 2; $ row <= $ highestRow; $ row ++ ){

$ Arr = array ();
// Note that the index of the highestColumnIndex column starts from 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 ("imported successfully", site_url ("link address"); // jump address
// Delete the uploaded excel file

}

} Else {

Show_msg ("the uploaded file is not in cvs format. please upload it again ");
}


} Else {

Switch ($ fileerror ){
Case 1:
Show_msg ("the uploaded file exceeds the limit of the upload_max_filesize option 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 ("only part of the file is uploaded ");
Break;
Case 4:
Show_msg ("no file is uploaded ");
Break;
}


}

}

}

Note: Reading Mode: The excel mode cannot be specified to read files automatically.
3. Read excel details

1. the simplest way to import an Excel file is to use PHPExel's IO Factory and call the static load method of the PHPExcel_IOFactory class. it can automatically identify the document format, including Excel2007, Excel2003XML, OOCalcSYLK, Gnumeric, and CSV. Returns an instance of PHPExcel.

// Load the factory class

Include 'phpexcel/IOFactory. php ';
// Path of the xls file to be read

$ InputFileName = './sampleData/example1.xls ';

/** Use the load method of PHPExcel_IOFactory to obtain the excel operation object **/
$ ObjPHPExcel = PHPExcel_IOFactory: load ($ inputFileName );

// Obtain the current active table and call the toArray method to obtain the two-dimensional array of the table.

$ SheetData = $ objPHPExcel-> getActiveSheet ()-> toArray (null, true );

Var_dump ($ sheetData );

1. create an ExcelReader to load an Excel file

If you know the format of this Excel document, you can create a Reader to load the Excel document to be read. However, if you load an incorrect document type, unexpected errors may occur.

$ 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 the current active sheet

$ CurSheet = $ objPHPExcel-> getActiveSheet ();

// Return the data of the table in the form of a two-dimensional array

$ SheetData = $ curSheet-> toArray (null, true );

Var_dump ($ sheetData );

You can also use the createReader method of PHPExcel_IOFactory to obtain a Reader object without knowing the format of the file to be read.

$ InputFileType = 'excel5 ';
// $ InputFileType = 'excel2007 ';
// $ InputFileType = 'excel2003xml ';
// $ InputFileType = 'oocalc ';
// $ InputFileType = 'syk ';
// $ 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 the current active sheet

$ CurSheet = $ objPHPExcel-> getActiveSheet ();

// Return the data of the table in the form of a two-dimensional array

$ SheetData = $ curSheet-> toArray (null, true );

Var_dump ($ sheetData );

If the file format is unknown before reading the file, you can use the identify () method of IOFactory to obtain the file type, and then use the createReader () method to wear the reader.

$ InputFileName = './sampleData/example1.xls ';

/** Determine the format of the input file **/
$ InputFileType = PHPExcel_IOFactory: identify ($ inputFileName );
/** Wear reader **/
$ ObjReader = PHPExcel_IOFactory: createReader ($ inputFileType );
/** Load the file to be read **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );

2. set the Excel reading option

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

2.1.ReadingOnly Data from a Spreadsheet File

SetReadDataOnly () method. when configuring the reader to ignore the data type of table data, all data types are 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 );
/** Configure cell data to be returned as a string **/
$ ObjReader-> setReadDataOnly (true );
/** Load $ inputFileName to a PHPExcel Object **/
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );

$ SheetData = $ objPHPExcel-> getActiveSheet ()-> toArray (null, true );

Var_dump ($ sheetData );

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