Php batch import excel to database code-PHP source code

Php batch import excel to database code

Script ec (2); script

We used the extended ExcelFileParser to process it,

Excel Data Acquisition demonstration

Excel Data Acquisition demonstration

/**
* CopyRight (c) 2009,
* All rights reserved.
* File Name:
* Abstract:
*
* @ Author listen 8 [email = ixqbar@hotmail.com] ixqbar@hotmail.com [/email]
* @ Version
*/

Class IndexAction extends Action
{
/**
* Constructor
*/
Public function _ construct ()
{
Parent: :__ construct ();
}
/**
* Default index page
*/
Public function index ()
{
$ This-> display ();
}
/**
* Submit for processing
*/
Public function parse ()
{
/**
* $ _ FILES array description
* Array (n ){
* ["Form file box name"] => array (5 ){
* ["Name"] => name of the submitted file
* ["Type"] => the submitted file type is "application/vnd. ms-Excel"
* ["Tmp_name"] => temporary file name
* ["Error"] => error (0 Success 1. The file size exceeds the value of upload_max_filesize2. The file size exceeds that of MAX_FILE3. The file size is incomplete. 4. The file is not uploaded)
* ["Size"] => file size (unit: KB)
*}
*}
*/
$ Return = array (0 ,'');
/**
* Determine whether to submit
* Is_uploaded_file (file name) is used to determine whether the specified file is uploaded using the POST method to prevent unauthorized submission. It is usually used together with move_upload_file to save the uploaded file to the specified path.
*/
If (! Isset ($ _ FILES) |! Is_uploaded_file ($ _ FILES ['excel '] ['tmp _ name'])
{
$ Return = array (1, 'illegal submission ');
}
// Process
If (0 = $ return [0])
{
Import ('@. Util. ExcelParser ');
$ Excel = new ExcelParser ($ _ FILES ['excel '] ['tmp _ name']);
$ Return = $ excel-> main ();
}
// Output Processing
Print_r ($ return );
}
}
?>

/**
* CopyRight (c) 2009,
* All rights reserved.
* File name: excel Data Acquisition
* Abstract:
*
* @ Author listen 8 [email = ixqbar@hotmail.com] ixqbar@hotmail.com [/email]
* @ Version 0.1
*/
Class ExcelParser
{
Private $ _ data = array (0 ,'');
Private $ _ excel_handle;
Private $ _ excel = array ();
/**
* Constructor
* @ Param $ Filename: name of the temporary file to be uploaded
*/
Public function _ construct ($ filename)
{
/**
* Introduce the excelparser class
* The common method is
* Requires path. 'excelparser. php ';
* Import is a built-in import class method for ThinkPHP.
*/
Import ('@. Util. PHPExcelParser. excelparser', '', '. php ');
$ This-> _ excel_handle = new ExcelFileParser ();
// Error Retrieval
$ This-> checkErrors ($ filename );
}
/**
* Error Verification
*/
Private function checkErrors ($ filename)
{
/**
* Method 1
*/
$ Error_code = $ this-> _ excel_handle-> ParseFromFile ($ filename );
/**
* Method 2
* $ File_handle = fopen ($ this-> _ filename, 'rb ');
* $ Content = fread ($ file_handle, filesize ($ this-> _ filename ));
* Fclose ($ file_handle );
* $ Error_code = $ this-> _ excel-> ParseFromString ($ content );
* Unset ($ content, $ file_handle );
*/
Switch ($ error_code)
{
Case 0:
// Do not handle no errors
Break;
Case 1:
$ This-> _ data = array (1, 'file read error (Linux read/write permission )');
Break;
Case 2:
$ This-> _ data = array (1, 'file is too small ');
Break;
Case 3:
$ This-> _ data = array (1, 'failed to read the Excel header ');
Break;
Case 4:
$ This-> _ data = array (1, 'file read error ');
Break;
Case 5:
$ This-> _ data = array (1, 'file may be null ');
Break;
Case 6:
$ This-> _ data = array (1, 'Incomplete files ');
Break;
Case 7:
$ This-> _ data = array (1, 'Data reading error ');
Break;
Case 8:
$ This-> _ data = array (1, 'version error ');
Break;
}
Unset ($ error_code );
}
/**
* Obtain Excel Information
*/
Private function getExcelInfo ()
{
If (1 = $ this-> _ data [0]) return;
/**
* Get the number of sheet
* Obtain the rows and columns corresponding to the sheet unit.
*/
$ This-> _ excel ['sheet _ number'] = count ($ this-> _ excel_handle-> worksheet ['name']);
For ($ I = 0; $ I <$ this-> _ excel ['sheet _ number']; $ I ++)
{
/**
* Row-on-Column
* Note: The count starts from 0.
*/
$ Row = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['max _ row'];
$ Col = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['max _ col'];
$ This-> _ excel ['row _ number'] [$ I] = ($ row = NULL )? 0: ++ $ row;
$ This-> _ excel ['col _ number'] [$ I] = ($ col = NULL )? 0: ++ $ col;
Unset ($ row, $ col );
}
}
/**
* Chinese processing functions
* @ Return
*/
Private function uc2html ($ str)
{
$ Ret = '';
For ($ I = 0; $ I {
$ Charcode = ord ($ str [$ I * 2]) + 256 * ord ($ str [$ I * 2 + 1]);
$ Ret. = '& #'. $ charcode .';';
}
Return mb_convert_encoding ($ ret, 'utf-8', 'html-ENTITIES ');
}
/**
* Excel Data Acquisition
*/
Private function getExcelData ()
{
If (1 = $ this-> _ data [0]) return;

// Modify the tag
$ This-> _ data [0] = 1;
// Obtain data
For ($ I = 0; $ I <$ this-> _ excel ['sheet _ number']; $ I ++)
{
/**
* Loop over rows
*/
For ($ j = 0; $ j <$ this-> _ excel ['row _ number'] [$ I]; $ j ++)
{
/**
* Column Loop
*/
For ($ k = 0; $ k <$ this-> _ excel ['col _ number'] [$ I]; $ k ++)
{
/**
* Array (4 ){
* ["Type"] => type [0 character type 1 integer 2 floating point 3 date]
* ["Font"] => font
* ["Data"] => data
*...
*}
*/
$ Data = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['cell '] [$ j] [$ k];
Switch ($ data ['type'])
{
Case 0:
// Character type
If ($ this-> _ excel_handle-> sst ['unicode '] [$ data ['data'])
{
// Chinese Processing
$ Data ['data'] = $ this-> uc2html ($ this-> _ excel_handle-> sst ['data'] [$ data ['data']);
}
Else
{
$ Data ['data'] = $ this-> _ excel_handle-> sst ['data'] [$ data ['data'];
}
Break;
Case 1:
// Integer
// TODO
Break;
Case 2:
// Floating point number
// TODO
Break;
Case 3:
// Date
// TODO
Break;
}
$ This-> _ data [1] [$ I] [$ j] [$ k] = $ data ['data'];
Unset ($ data );
}
}
}
}
/**
* Main Function
* @ Return array (identifier, content s)
*/
Public function main ()
{
// Obtain Excel Information
$ This-> getExcelInfo ();
// Obtain Excel Data
$ This-> getExcelData ();
Return $ this-> _ data;
}
}

?>